1

I'm new to GIS and wanted to start play around with some data about the appalachian trail (AT) and PostGIS.

However I already failed at the first and most simple task to calculate the length of the AT. I downloaded the data here. And imported it using the pgShapefileLoader. After loading I noticed that the srid of at_centerline wasn't set. Inspecting the table with select st_astext(geom) from at_centerline limit 1 showed that it was in lat/lon format. Therefore I updated the table with select updateGeometrySRID('at_centerline','geom',4326).

So, time to calculate the length

select sum( st_length( geography( geom ) ) ) / 1000 * 0.621371 
from at_centerline

yielded 2122.61779398273 miles. However the AT currently is 2190 miles long.

I also tried to directly calculate the length in miles with this query

select sum( st_length( st_transform( geom, 2877 ) ) ) / 5280 
from at_centerline;

which yielded 2126.50802521765 miles.

So how to explain the discrepancy? Is it simple the data provided by the ATC isn't accurate enough? I would rather doubt that. So what am I doing wrong here?

Improve this question
17
  • You are measuring distances on flat ground. Maybe the 2190 miles is considering the slope?
    – JGH
    Commented Jul 20, 2017 at 17:33
  • There seems to be an elevation of around 88 Miles. Using Pythagorean Theorem I get 2124.4 miles considering elevation. This doesn't seem to be the error.
    – Angelo.Hannes
    Commented Jul 20, 2017 at 17:49
  • the SRID of 2877 is a colorado state plane CS..why would you use that?
    – ziggy
    Commented Jul 20, 2017 at 18:00
  • 1
    well...assuming the SRIDs are correct, I suspect that there is not much more you can do. theoretically adding the 80 miles in elevation gain/loss gets you closer, but as I said, who knows how they measured the lenght. could be with different data, different projections or by car. I thought you could get closer with the right CRS, but apparently no. nevertheless: you played a lot with PostGIS, can't say you didn't learn ,)
    – geozelot
    Commented Jul 20, 2017 at 19:19
  • 1
    @ThingumaBob Thank you very much for your help, appreciate it! I wasn't expecting to get to the exact length the ATC uses. However, starting from the same dataset, arriving at a difference of 70 Miles is way to big.
    – Angelo.Hannes
    Commented Jul 20, 2017 at 19:32

1 Answer 1

Reset to default
2

You should find out how the data was collected - was it :

  1. Traced via satellite image - if so, it will not include the ups and downs and curves of the earth, etc. - you'll be calculating the length of the linestring as it was digitized in 2D space
  2. Collected via GPS - this would give the length including the ups and downs as it was walked and collected in 3D space

Also:

I don't think simply converting this data to 'geography' (versus geometry) are going to help you here based on the first set of issues above.

Also: check this State Plane Map, the trail crosses multiple zones, so transforming the geom to State Plane won't work - so you'll likely have to convert to SRID/EPSG 2163 (US National Atlas Equal Area) - which is in meters - unless someone has a better equal area coordinate system to use.

(sorry you got hung up using the Colorado State Plane value - I posted that answer you referenced!)

Improve this answer
9
  • 1
    Thanks for your answer, and no worries for my confusion with the colorado state plane. That was entirely my fault. As far as I know the use GPS devices to collect the data every once in a while. However some data is "corrected" via satellite images. I'll try to reach out to someone who created the shape file.
    – Angelo.Hannes
    Commented Jul 20, 2017 at 19:30
  • @Angelo.Hannes good stuff!
    – Inactivated Account
    Commented Jul 20, 2017 at 19:32
  • @DPSSpatial I tried your suggestion with 2163 with this query select sum(st_length(st_transform(geom,2163))) from at_centerline
    – ziggy
    Commented Jul 20, 2017 at 20:04
  • @ziggy how did the results compare?
    – Inactivated Account
    Commented Jul 20, 2017 at 20:05
  • i got this huge number..3419158.44452333. is 2163 measured in degrees?
    – ziggy
    Commented Jul 20, 2017 at 20:05

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.