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.
