Top 5 Recent Articles

ARTICLES CATEGORIES

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.