[postgis-users] ST_Distance provides unexpected results when using Geography type

Paul Ramsey pramsey at cleverelephant.ca
Thu Mar 5 15:05:57 PST 2020

If you would like to see what your “boxes” actually look like, try wrapping them in ST_Segmentize() before viewing them on a flat map.

select st_asgeojson(st_segmentize('SRID=4326;POLYGON ((-167.51953125 8.841651120809145, 1.23046875 8.841651120809145, 1.23046875 63.35212928507874, -167.51953125 63.35212928507874, -167.51953125 8.841651120809145))'::geography,500000))

Past that into GeoJSON.io <http://geojson.io/> and see what it looks like. All will become clear.


> On Mar 5, 2020, at 2:41 PM, Wayne Rowcliffe <wayne at priority5.com <mailto:wayne at priority5.com>> wrote:
> Hi,
> I've attached a file containing sample queries that reproduce what I am seeing.
> Basically, we have an application that shows data on the globe. We are filtering the results to those within the current window viewport. What I am seeing is that depending on where the map is, I'll either get results or I won't, even when there should be data visible.
> While investigating, I switched to calling ST_Distance and noticed that when using geometry the results were always accurate. However, when using geography (like we do in our application), we will sometimes get the correct result, and other times it will say a point within the bounding geometry is actually hundreds of thousands of meters away.
> This is my version string using the query on the postgis webpage:
> PostgreSQL 10.9 (Ubuntu 10.9-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="100" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" LIBPROTOBUF="1.2.1" TOPOLOGY RASTER
> The closest bug I could find was this: https://trac.osgeo.org/postgis/ticket/4480 <https://trac.osgeo.org/postgis/ticket/4480> 
> It seems potentially different though since the bounding area of my query is less than 180 degrees.
> Wayne Rowcliffe
> Software Engineer, Priority5 Holdings
> E-mail: wayne at priority5.com
> <postgis_distanct.txt>_______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200305/c088a93a/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: screenshot_351.jpg
Type: image/jpeg
Size: 78967 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200305/c088a93a/attachment.jpg>

More information about the postgis-users mailing list