4

I'm looking to calculate the approximate distance in miles from a linestring.

This post recommends using ST_Transform in conjunction with the State Plane coordinate system for my area. In my case, I need to do this for many areas across the US, not just one. Is there a simpler way to calculate mileage without a need for the state-specific SRID?

For some context, I'm calculating the distance in mileage for dog walks. That means that the calculation doesn't need to 100% accurate, but being fairly close is a must. I do have the address for each walk available, which means I could use it to get the state-specific SRID, but I'd prefer to do it an automated fashion rather than having to build a lookup system on my own.

UPDATE

I'm running Postgres v9.5 and PostGIS v2.2.1.

The coordinates column is of type GEOMETRY and is in the 4326 coordinate system.

I've started with the following query:

select 
    appointment_id, 
    (
        ST_Length(
            ST_Transform(
                ST_Makeline(coordinates ORDER BY captured_at),
                2877
            )
        ) / 5280
    ) as distance
from path 
where appointment_id = '38f6f1e3-d865-5a57-b87b-597d3f68f405' 
group by appointment_id;

As mentioned above, I'm using some code from the example in the referenced post.

2
  • Welcome to GIS SE. As a new user, please take the Tour. It is policy here that coding questions should contain code. Please edit the question to contain details about the versions of PostgreSQL and PostGIS, the coordinate system of the data, and the query you have developed so far to attempt to meet your goal.
    – Vince
    Oct 9, 2016 at 3:44
  • You could try epsg.io/2163 and see how much difference that makes for your specific scenario.
    – BradHards
    Oct 9, 2016 at 4:10

1 Answer 1

3

If you have data covering a large area (across multiple projected coordinate systems), it could be helpful to use the PostGIS geography type instead of geometry. Especially if your base data ist already in WGS84 GCS (SRID 4326).

The ST_Distance function uses geodetic calculation and returns distance in meters.

Some additional explanation can be found here: Pros and Cons of geography / geometry types and Difference between geography / geometry types

Your Answer

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

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