# 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.