5

I'm having a weird issue when computing the distance between two geographies using ST_Length (PostgreSQL 10.4, PostGIS 2.4.4). Basically if the number of decimals is above six, ST_Length returns 0 in some cases.

Using ST_Length as it is shown in the documentation:

# SELECT ST_Distance(gg1, gg2) As spheroid_dist, ST_Distance(gg1, gg2, false) As sphere_dist
    FROM (SELECT
        ST_GeogFromText('SRID=4326;LINESTRING(-122.4367398 37.7370600, -122.4376093 37.7371769)') As gg1,
        ST_GeogFromText('SRID=4326;LINESTRING(-122.4367539 37.7371343, -122.4375453 37.7372407)') As gg2
    ) As foo ;
 spheroid_dist | sphere_dist 
---------------+-------------
             0 |           0

# -- Remove 7th decimal
# SELECT ST_Distance(gg1, gg2) As spheroid_dist, ST_Distance(gg1, gg2, false) As sphere_dist
    FROM (SELECT
        ST_GeogFromText('SRID=4326;LINESTRING(-122.436739 37.737060, -122.437609 37.737176)') As gg1,
        ST_GeogFromText('SRID=4326;LINESTRING(-122.436753 37.737134, -122.437545 37.737240)') As gg2
    ) As foo  ;
 spheroid_dist | sphere_dist 
---------------+-------------
     7.8956756 |  7.90923387

What's weird is that the issue doesn't occur every time:

# SELECT ST_Distance(gg1, gg2) As spheroid_dist, ST_Distance(gg1, gg2, false) As sphere_dist
    FROM (SELECT
        ST_GeogFromText('SRID=4326;LINESTRING(-122.4363981 37.7371789,-122.4366807 37.7370670)') As gg1,
        ST_GeogFromText('SRID=4326;LINESTRING(-122.4363905 37.7372617,-122.4366961 37.7371407)') As gg2
    ) As foo ;
 spheroid_dist | sphere_dist 
---------------+-------------
     7.9257293 |  7.93355247

What could be the reason for this? Feels like it's a float/double issue but I don't see why the problem would only occur in some cases.

  • adding an extra (8th) decimal makes it return the expected distance, so it might be more than just a rounding issue – JGH Jul 29 '18 at 1:37
  • Good call, definitely looks like a bug then. Interestingly enough, in the original data there's actually 12 decimals, and if I try randomly dropping some of them the results are sometimes right and sometimes wrong... I'll reach out to the postgis support – Jukurrpa Jul 30 '18 at 22:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.