2

I am having issues similar to this question asked previously

(Please don't mark this as duplicate, as I cannot comment on that question as I don't have enough reputation.)

The fix being used returns error messages when run:

SELECT osm_id, st_length(st_transform(way,3857)::geography) 
FROM   planet_osm_roads;
ERROR: Only lon/lat coordinate systems are supported in geography.

or

SELECT osm_id, st_length(st_transform(way,3857))::geography 
FROM   planet_osm_roads
ERROR: cannot cast double precision to geography

I'm new to both SQL and PostGIS so not exactly seeing any obvious mistakes. The datatype for column way is geometry(linestring, 3857) directly from OSM data loaded into database with osm2pgsql.

When ran without ST_Transform, value is overestimated due to the geometry geography mismatch.

Improve this question
4
  • 1
    Transform to 4326 instead, and cast the transform result, not the result of ST_Length!
    – geozelot
    Feb 8 at 18:18
  • Ah so the linestring, 3857 means it was already in srid 3857 and I needed to cast to srid 4326 to get lat/lon value for ::geography. The :: 'cast' command is sql or postgis?
    – evan
    Feb 8 at 18:28
  • That is PostgreSQL SQL dialect (I believe the ANSI standard does not define the :: notation, but not 100% sure).
    – geozelot
    Feb 8 at 18:40
  • 1
    @evan You haven't shown the definition of your table so we don't know how your data is stored. When you get data from OSM (a .osm file), the data is stored in WGS 84 lat/lon (EPSG:4326). See my answer. If you have stored it in EPSG:3857, then why would you be trying to transform it into EPSG:3857?
    – hgb
    Feb 8 at 18:42

1 Answer 1

Reset to default
5

The data you get from OSM is already stored in a geographic (lat/lon) coordinate system (EPSG:4326). https://gis.stackexchange.com/a/48952/110158

Mercator projections, EPSG:3857 being just one of them, are not appropriate for measuring distances. The following should work if your data is stored in the OSM provided EPSG:4326.

SELECT osm_id, ST_Length(way::geography) FROM planet_osm_roads;

This says to cast the geometry type containing the lat/lon coordinates (EPSG:4326) provided by OSM to geography data type and then calculate length with that data. With geography data type ST_Length does geodesic calculations and returns length in metres.

If your data is projected into EPSG:3857 by your process that imports data to your PostGIS database (as @geozelot suggested in a comment), then the following should work:

SELECT osm_id, ST_Length(ST_Transform(way, 4326)::geography)
  FROM planet_osm_roads;

This says to reproject the data back to lat/lon (EPSG:4326), then cast to geography before running ST_Length as above.

Improve this answer
3
  • osm2psql defaults to projecting geometries to EPSG:3857 during import.
    – geozelot
    Feb 8 at 19:03
  • 1
    @geozelot Ah, okay, default reprojection. I've updated my answer to include that case.
    – hgb
    Feb 8 at 19:22
  • The second part is correct. as @geozelot noted, the data imported with osm2pgsql seems to be imported in EPSG:3857 as the result of get_srid is 3857.
    – evan
    Feb 9 at 10:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.