0

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?

user16666
  • 47
  • 1
  • 2
  • 6

1 Answers1

0

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.

Ben
  • 1,902
  • 17
  • 17
  • One small remark: "pnt.point.x" will not extract the X of a point. You need to use "pt.point.sdo_point.x". Same for the centroid: should be "...).sdo_point.x". – Albert Godfrind Nov 27 '14 at 15:54
  • The other concern I have is about using centroids. The line from the point to the centroid of the polygon is not the same as the line to the boundary of the polygon (you can get that one using SDO_GEOM.SDO_CLOSEST_POINTS). It is definitely possible that the centroid of the polygon is visible from the point but not the "polygon" (defined by the shortest line from the point to the polygon). The OP says "... a line is drawn between that point and the polygon" but does not specify what that line is: any line ? Or the shortest line to the boundary of the polygon ? – Albert Godfrind Nov 27 '14 at 16:06
  • Another consideration is that the OP does not specify that he only wants the *closest* visible point to a polygon. If he does, then your solution is fine. If not (i.e. he wants *all* the points visible from a polygon), then the problem becomes somewhat more computationally intensive as you can no longer use SDO_NN to minimise the result set. You essentially end up with a cartesian product of all points and all polygons, construct the line between each pair and use each line to search for any other buildings. The OP talks about "many" - but how many ? 10000 of each means 100 million cases! – Albert Godfrind Nov 27 '14 at 16:27
  • The whole thing is a bit ambiguous, which is why I'm not really giving a definitive (or syntactically correct) answer. – Ben Nov 29 '14 at 00:03
  • I agree that the OP needs to clarify hist/her need. If the problem can be limited to only find the points within some distance of the base polygon, or only the N closest ones (like Ben proposed) then it can be solved with reasonable efficiency. If no such limit exists, then it becomes hard to solve efficiently. – Albert Godfrind Dec 07 '14 at 11:04