Answers to: spatial data distancehttps://sqlanywhere-forum.sap.com/questions/24363/spatial-data-distance<p>Is it possible to get the distance between a Polygon and a point?</p>
<p>When you use ST_Distance you can only get the distance between two points (round-Earth).</p>
<p>I wonder if there is something similar you can use between a point and polygon?</p>enTue, 24 Mar 2015 15:32:03 -0400Answer by Phil Mitchellhttps://sqlanywhere-forum.sap.com/questions/24363/spatial-data-distance/24399<p>Distance between a point and a polygon is equivalent to distance between point and a set of lines (unless the point and polygon intersect, then it's 0). To do that you need to compute the path of the line. Computing the length of a line is not too hard using an iterative algorithm (we use Vincenty's Formula, which fails for nearly-antipodal points, but is otherwise pretty simple and effective). This is why we can compute distance between two points.</p>
<p>Unfortunately, computing the exact path of a line on a round-earth projection is very hard, and occasionally there are several equivalent paths that all have the shortest length. Furthermore, the line is most definitely not "straight" in the Euclidean sense of the word, nor is it a nice curve (search for "geodesic" if you want to know more).</p>
<p>This is usually the job of projections to sort out, and is way too complicated for mere mortals to understand. So doing this directly in round-earth is not currently supported, and we would need some strong justification for doing this.</p>
<p>Internally we project round-earth geometries into the special (planar) 1000004326 SRID in order to do various operations on them, but this projection is <em>not</em> distance preserving. Therefore, anything that requires distance calculations (even Centroid()) cannot be trusted to give the right answer.</p>
<p>Volker's "silly example" of transforming to 1000004326 will definitely fall apart if your polygon crosses the equator anywhere other than between 0 and 90 degrees longitude. He happened to pick a special case where the Centroid is correct. But the distance will be wrong. If you get the centroid right, you could project back to 4326 and do the distance calculation in round-earth to get a reasonable distance measurement.</p>
<p>Your best bet is to know the context of your data and choose a local planar SRS that has an acceptable error margin for all data involved, project everything so it's all planar, and then do the distance calculation on the planar data. Other products often do this transparently in order to support round-earth distance measurements.</p>Phil MitchellTue, 24 Mar 2015 15:32:03 -0400https://sqlanywhere-forum.sap.com/questions/24363/spatial-data-distance/24399Answer by Volker Barthhttps://sqlanywhere-forum.sap.com/questions/24363/spatial-data-distance/24364<p>I do assume you want to calculate that in a round-Earth SRS, right?</p>
<p>Just a <a href="http://dcx.sybase.com/index.html#1201/en/dbspatial/flat-earth-round-earth-representations.html">hint from the docs</a>:</p>
<blockquote>
<p>You can project round-Earth data to a flat-Earth spatial reference system to perform distance computations with reasonable accuracy provided you are working within distances of a few hundred kilometers. To project the data to a planar projected spatial reference system, you use the ST_Transform method.</p>
</blockquote>
<p>Just a silly sample - it tries to calculate the distance between the point (6,8) and the rectangle with length 8 around the origin.</p>
<pre>begin
declare stp ST_Point;
declare stpg ST_Polygon;
declare nRE_SRID int = 4326; -- a ROUND-earth SRID
declare nPL_SRID int = 1000004326; -- according planar SRID
set stp = new ST_Point(6, 8, nRE_SRID);
set stpg = new ST_Polygon(new ST_Point(-4.0, -4.0), new ST_Point(4.0, 4.0)).ST_SRID(nRE_SRID);
-- select stp.ST_Distance(stpg); -- will fail with SQLCODE -1436
-- select stp.ST_Distance(stpg.ST_Centroid()); -- will fail with -1435
-- returns 10
select stp.ST_Transform(nPL_SRID).ST_Distance(
TREAT(stpg.ST_Transform(nPL_SRID) as ST_Polygon).ST_Centroid());
end;
</pre>Volker BarthFri, 20 Mar 2015 13:10:01 -0400https://sqlanywhere-forum.sap.com/questions/24363/spatial-data-distance/24364