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

i have many polygons , many points , want find point such when line drawn between point , polygon not intersect other polygon. need point close polygon , no other polygon between them.

i tried following query , gives me points whether or not being intersected polygon or not.

select p.pointloc pointtable p 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'";  tried query , gives correct points miss few correct points:  select p.pointloc pointtable p 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 1 point out oracle spatial operator best suited situation?

it sounds you're looking nearest neighbour implementation - oracle provides spatial operator sdo_nn.

you can find more details here: http://docs.oracle.com/database/121/spatl/sdo_operat.htm#spatl1032

this far, in it'll find nearest point polygon, won't guarantee there no polygons between point , target polygon. suspect if want ensure this, you'll have have creative.

my approach be:

1. use sdo_nn closest point or points
2. use sdo_geom.sdo_centroid find polygon centre of gravity
3. create in-query/in-memory sdo_geometry line joins 2 points
4. use basis of not exists clause exclude points polygon intersects line

something following untested / not-quite finished example, perhaps:

select * points pnt sdo_nn(pnt.point, sdo_geometry(your polygon here)) = 'true' -- fill polygon , not exists (   select 1   polygons plg   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 polygon         , 0.05         ).x       , sdo_geom.sdo_centroid(           sdo_geometry(your polygon here) -- fill polygon         , 0.05 -- tolerance         ).t       , pnt.point.sdo_point.x       , pnt.point.sdo_point.y       ) -- line between point , polygon centroid     )   , 0.05 -- tolerance   ) = 'true' )  

depending on dataset/performance, might want of in pl/sql using collections or loops.

the example above bit rough , ready, hope gist.