2

I have a lot of LineStrings that I would like to calculate the distance of in meters. After some research, I used the following in my SELECT:

ST_LengthSpheroid(geometry,'SPHEROID["WGS 84",6378137,298.257223563]')::text AS length

However, when viewing the data for this I am getting some massive numbers for my length field:

enter image description here

That would be over 55,000 km, which is not correct.

Does anyone have any idea what I could be doing wrong here? Again, my goal is to get the length of a LineString in meters.

3

A spheroid is a representation of a curved surface. A 'lat/lon' or geographic coordinate system is also a representation of a curved surface. When using these coordinate systems, measurements between points are represented in degrees along the curve of the spheroid. To convert this into meters, you need to use the spheroid to calculate the accurate length using a curved line, rather than the shorter straight line that would connect the points on a planar surface.

A projected coordinate system is a flat plane. Lines between points are always straight lines. So calculations done in projected coordinate systems almost always use meters.

  • If you don't know what coordinate system your geometry is using, use select st_srid(geom) to find it.
  • If you want a detailed explanation of the srid returned by st_srid, use select * from spatial_ref_sys where srid = 4326 but replace 4326 with your srid
  • If the srid is 0, it means it was never set. You will need to find out what the srid should be and set it using alter table your_table alter column geom type geometry(linestring,4326) using st_setsrid(geom,4326)

Based on your last comment, your geometry is not using the WGS84 projection, so you are using the incorrect spheroid.

If your geometry is not using a lat/lon coordinate system, it must be using a projected coordinate system. If so, you can simply use select st_length(geom) to get the length in meters.

If your geometry is, in fact, using a lat/lon coordinate system, use this instead:

select st_length(geometry::geography)

This will use the spheroid by default, and returns the length in meters.

8
  • Is there a way to check if the geometry is valid? I am getting Error getting 4/2/5: error: Only lon/lat coordinate systems are supported in geography. on a tile. Oct 1 at 23:33
  • geometry validity isn't about coordinate system issues, so checking geometry validity is not helpful here. If it was, you can use st_isvalid(geom). It sounds like you don't know what coordinate system your geometry is using. select st_srid(geom) will tell you that. When you're trying to measure using a spheroid, the spheroid has to match the coordinate system.
    – jbalk
    Oct 2 at 0:05
  • Also - only 'lat/lon' or 'geographic' coordinate systems use spheroids because they are spherical (round, or at least curved). Projected coordinate systems do not use a spheroid because they are planar (flat/straight/no curves).
    – jbalk
    Oct 2 at 0:07
  • I actually changed to using ST_Length(ST_Transform(geometry, 900913))::text and it seemed to work quite well. I am using openMapTiles and the srid is 900913. Oct 2 at 0:30
  • Sweet! Please consider upvoting or accepting my answer if it helped you, so others will see it as well.
    – jbalk
    Oct 2 at 0:39

Your Answer

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

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