I am playing around with a map of bike lanes (so, lines) and trying to calculate the total number of miles of lane in each of a couple of regions. But I'm not even sure how to use ST_Length to calculate miles. I'm getting values of .001 and I don't even know what that means.

The documentation: http://postgis.org/docs/ST_Length.html is confusing to me -- I think this is supposed to be giving me meters, but I'm not entirely sure, and the segments tend to be one or tow blocks long, so ... more than .001 meters.

How can I use ST_Length to calculate miles?

  • 1
    With a value that small seems like it is showing degrees. – Michael B Apr 21 '15 at 18:19
  • I agree. What coordinate system/projection is the data in? – Barbarossa Apr 21 '15 at 18:22
  • I'm not actually sure what the projection is. How do I look that up? But ... pretty sure it's WGS84 – Amanda Apr 21 '15 at 18:24

Are you in the USA?

If so, determine the State Plane coordinate system that is used by your area, and what the SRID is, for example, What is my State Plane zone in Colorado?

I suggest using State Plane as the units are FEET, which can easily be converted to miles.

I use SRID/EPSG:2877

So now use an ST_Transform statement which will project your data 'on the fly' into feet:

ST_Length(ST_Transform(geom, 2877)) 

Now wrap that in a conversion to miles (divide by 5280):

(ST_Length(ST_Transform(geom, 2877))/5280)

and you should have length in miles!

  • 1
    Boom. Thanks. The data included a shape_leng column that seems to be consistent with the feet calculation. – Amanda Apr 21 '15 at 18:30
  • @Amanda make sure you throw that SHAPE_LENG column in the nearest trash receptacle... it's now 'disconnected' from your data, where as the GEOM column is LIVE!!! – DPSSpatial Apr 21 '15 at 19:08
  • it is a handy sanity check. :) – Amanda Apr 22 '15 at 14:10

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.