CODEWITHSUNDEEP
sqloracle-databasegeospatial
bjaba
bjaba

Reputation: 11

Using Oracle SQL spatial functions to calculate the distance between a point and the perimeter of the polygon that surrounds that point?

I have a polygon that represents Long Island. I want to calculate the distance from an address within Long Island (represented by a point) to the nearest point on the coast (represented by the perimeter of that polygon).

Below is the query that I wrote, but it returns a distance of 0 because the point falls within the polygon. Is there a different function that I need to use for this scenario or any other way to work around the issue?

select /*+ ordered */ 
 sdo_nn_distance (1) distance 
from ABPROD.ABT_COASTLINE_SHAPE_DATA CSD 
where sdo_nn (CSD_LOC_GEO,sdo_geometry(2001,8307,sdo_point_type(-73.1,40.8, null),null, null),'unit=mile',1) = 'TRUE' 
and CSD_LOC_ID = '166'
and rownum = 1

Upvotes: 1

Views: 547

Answers (1)

bjaba
bjaba

Reputation: 11

I was hoping for a simple solution along the lines of the sdo_nn_distance function, but i had no such luck so I had to rely on somewhat of a work around. I tested using the island of New York polygon and it performed reasonably fast. I still need to see how fast it returns when I switch over to the mainland North America polygon.

select * from 
(select 
sdo_geom.sdo_distance(sdo_geometry(2001,4326,null,sdo_elem_info_array(1, 1, 1),sdo_ordinate_array(/*selected coordinates*/-72.883398, 40.895885)),
                      sdo_geometry(2001,4326,null,sdo_elem_info_array(1, 1, 1),sdo_ordinate_array(X,Y)),1,'unit=Mile') distance_mile
from 
 ABPROD.ABT_COASTLINE_SHAPE_DATA             CSD,
    --Above line identifies the table that contains all of the polygons
table(SDO_UTIL.GETVERTICES(CSD.CSD_LOC_GEO)) t
    --Above line creates a list of all of the coordinates (X,Y) that make up the polygon that the selected coordinates fall within

where 
    SDO_RELATE(CSD_LOC_GEO, sdo_geometry(2001,8307,sdo_point_type(/*selected coordinates*/-72.883398, 40.895885, null),null, null), 'mask=touch+contains') = 'TRUE'
    --Above line finds the polygon that the selected coordinates fall within

and CSD_LEVEL_NBR = 1
    --Above line limits results to land shapes, rivers and lakes are excluded

order by 1 asc
    --Above line orders results by distance_mile so that row #1 is the closest distance
)
where rownum = 1
    --Above line limits results to only the closest distance

Upvotes: 0

Related Questions