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.
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.
4326instead, and cast the transform result, not the result of
::notation, but not 100% sure).