Find the points using Oracle spatial directly in front of a polgon

Question

I have many polygons and many points and want to find the point such that when a line is drawn between that point and the polygon it does not intersect with any other polygon. So basically I need a point that is very close to the polygon and no other polygon is between them.

I tried the following query and it gives me all the points whether or not they are being intersected by a polygon or not.

SELECT P.POINTLOC from pointTable P WHERE NOT MDSYS.SDO_OVERLAPBDYINTERSECT(P.POINTLOC," +
            "MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1)," +
"MDSYS.SDO_ORDINATE_ARRAY(4, 226, 150, 254, 164, 240, 191, 212, 176,4,226)))    = 'TRUE'";

Then I tried this query and it gives some correct points but miss a few correct points:

SELECT P.POINTLOC from pointTable P WHERE MDSYS.SDO_WITHIN_DISTANCE(P.POINTLOC," +
            "MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1)," +
"MDSYS.SDO_ORDINATE_ARRAY(4, 226, 150, 254, 164, 240, 191, 212, 176,4,226)),'distance = 40')    = 'TRUE'";

Can some one point out which Oracle spatial operator would be best suited for this situation?

Answer 1

It sounds like you're looking for a Nearest Neighbour implementation - Oracle provides the Spatial operator SDO_NN.

You can find more details here: http://docs.oracle.com/database/121/SPATL/sdo_operat.htm#SPATL1032

This will only get you so far, in that it'll find the nearest point to a polygon, but it won't guarantee that there are no polygons between your point and your target polygon. I suspect if you want to ensure this, you'll have have to get creative.

My approach would be:

1. use SDO_NN to get the closest point or points
2. use SDO_GEOM.SDO_CENTROID to find the polygon centre of gravity
3. create an in-query/in-memory SDO_GEOMETRY line that joins the two points
4. use this as the basis of a NOT EXISTS clause to exclude points where a polygon intersects that line

Something like the following untested / not-quite finished example, perhaps:

SELECT *
FROM points pnt
WHERE sdo_nn(pnt.point, sdo_geometry(your polygon here)) = 'TRUE' -- fill this with your polygon
AND NOT EXISTS (
  SELECT 1
  FROM polygons plg
  WHERE sdo_geom.sdo_intersection(
    plg.polygon
  , sdo_geometry(
      2002
    , NULL -- oracle srid
    , NULL -- point
    , sdo_elem_info_array(1, 2, 1) -- line
    , sdo_ordinate_array(
      , sdo_geom.sdo_centroid(
          sdo_geometry(your polygon here) -- fill this with your polygon
        , 0.05
        ).x
      , sdo_geom.sdo_centroid(
          sdo_geometry(your polygon here) -- fill this with your polygon
        , 0.05 -- tolerance
        ).t
      , pnt.point.sdo_point.x
      , pnt.point.sdo_point.y
      ) -- line between point and polygon centroid
    )
  , 0.05 -- tolerance
  ) = 'TRUE'
) 

Depending on your dataset/performance, you might want to do some of this in PL/SQL using collections or loops.

The example above is a bit rough and ready, but I hope you get the gist.