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?

  • You are measuring distances on flat ground. Maybe the 2190 miles is considering the slope? – JGH Jul 20 '17 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 Jul 20 '17 at 17:49
  • the SRID of 2877 is a colorado state plane CS..why would you use that? – ziggy Jul 20 '17 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 ,) – ThingumaBob Jul 20 '17 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 Jul 20 '17 at 19:32

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


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!)

  • 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 Jul 20 '17 at 19:30
  • @Angelo.Hannes good stuff! – DPSSpatial Jul 20 '17 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 Jul 20 '17 at 20:04
  • @ziggy how did the results compare? – DPSSpatial Jul 20 '17 at 20:05
  • i got this huge number..3419158.44452333. is 2163 measured in degrees? – ziggy Jul 20 '17 at 20:05

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.