How far inside, is inside? Measuring actual distance.
One issue that confuses a lot of spatial database practitioners is that the result of measuring the distance between a geometry object inside a polygon and that polygon’s boundary is zero (0).
Here is an example.
SELECT sdo_geometry(2003,NULL,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(0,0,100,100)) AS outerPoly, sdo_geometry(2003,NULL,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(10,10,90,90)) AS innerPoly FROM dual;
This looks like:
Standard distance determination shows the problem:
SELECT sdo_geom.sdo_distance( sdo_geometry(2003,NULL,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(0,0,100,100)), sdo_geometry(2003,NULL,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(10,10,90,90)), 0.005) AS dist FROM dual; -- -- Results -- DIST ---------- 0
Here is a trick that will allow for the calculation of a distance between the inner polygon and the one that contains it. Firstly, turn the outer polygon into a linestring, and then calculate the distance.
SELECT sdo_geom.sdo_distance( sdo_util.polygonToLine(sdo_geometry(2003,NULL,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(0,0,100,100))), sdo_geometry(2003,NULL,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(10,10,90,90)), 0.005) AS dist FROM dual; -- -- Results -- DIST ---------- 10
The zero (0) distance problem also affects SDO_NN and other distance operators. The same trick can be used with those operators or functions.
UPDATE
In response to a comment in respect of whether this trick works for non-rectangular polygons, the answer is yes.
WITH tGeom AS ( SELECT SDO_GEOMETRY(2003,3112,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1,65,2003,1),SDO_ORDINATE_ARRAY( 1030522.796,-1739721.758, 1038890.414,-1751951.354, 1134796.194,-1713331.577, 1174059.635,-1723630.184, 1055625.651,-1777697.872, 1100682.058,-1776410.546, 1165692.016,-1772548.569, 1214610.401,-1766755.602, 1245506.222,-1723630.184, 1246793.548,-1699814.655, 1236494.941,-1664413.192, 1195944.175,-1647034.292, 1172772.309,-1657332.9, 1158611.724,-1672780.811, 1137370.846,-1694665.351, 1119348.283,-1698527.329, 1098751.069,-1694665.351, 1091670.776,-1684366.744, 1091670.776,-1673424.474, 1094889.091,-1661838.54, 1091670.776,-1642528.652, 1079441.18,-1629655.393, 1052407.336,-1634161.033, 1029235.47,-1643172.315, 1019580.525,-1648965.281, 1015718.548,-1659907.552, 1020224.188,-1679861.103, 1045327.044,-1690803.373, 1071073.562,-1699170.992, 1082659.495,-1723630.184, 1057556.64,-1731354.139, 1030522.796,-1739721.758, /* hole */ 1040177.74,-1658620.226, 1068498.91,-1647034.292, 1070429.899,-1667631.507, 1053694.662,-1674711.799,1057556.64,-1661194.877, 1040177.74,-1658620.226)) AS geom FROM dual ), tPoints AS ( SELECT p.column_value AS pointId, sdo_util.EXTRACT(SDO_GEOMETRY(2005,3112,NULL,SDO_ELEM_INFO_ARRAY(1,1,11),SDO_ORDINATE_ARRAY( 1065924.258,-1656689.237, 1040821.403,-1744227.399, 1215254.064,-1711400.588, 1181139.927,-1663769.529, 1251942.852,-1786709.154, 1259666.807,-1638666.674, 998339.648,-1701745.644, 1134796.194,-1641241.326, 1129646.891,-1726848.499, 1119348.283,-1786065.491, 1075579.203,-1754526.006)),1,p.column_value) AS point FROM TABLE(codesys.geom.generate_series(1,11,1)) p ) SELECT b.pointid, round(sdo_geom.sdo_distance(a.geom,b.point,0.005),2) AS distPoly, round(sdo_geom.sdo_distance(sdo_util.polygonToLine(a.geom),b.point,0.005),2) AS distLine FROM tGeom a, tpoints b; -- Result -- POINTID DISTPOLY DISTLINE ------- -------- --------- 1 3464.61 3464.61 2 0 5955.25 3 0 30868.15 4 0 9280.28 5 41968.69 41968.69 6 34638.40 34638.40 7 30949.41 30949.41 8 38867.72 38867.72 9 10615.05 10615.05 10 10744.89 10744.89 11 12792.61 12792.61 -- 11 ROWS selected
Rendering points by pointid, poly and line distance “(id) distPoly / distLine” looks like this:
I hope this is of use to someone out there.