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?

Angelo.Hannes
  • 111
  • 3
  • 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
  • Because I didn't read this answer thoroughly and only copied the query: https://gis.stackexchange.com/questions/143436/how-do-i-calculate-st-length-in-miles – Angelo.Hannes Jul 20 '17 at 18:03
  • up-down, up-down, up-down... that adds up much more than going straight from start to the highest point – JGH Jul 20 '17 at 18:04
  • @JGH that isn't the highest point. it is the "Approximate Gain/Loss in Elevation" – Angelo.Hannes Jul 20 '17 at 18:06
  • I just read that the ATC also distributes its data in NAD83 (also lat/lon), which might make a little difference. Maybe check the metadata of the shapefiles. A cast to geography usually results in more precise/realistic measures, as it calculates the distance based on the spheroid. Depending on your PostGIS version, this defaults to WGS84. If your data is in NAD83, you'll need to transform to WGS84 first. But in general, I doubt that you will get exactly to the 2190 miles...who knows how they measured that lenght? – geozelot Jul 20 '17 at 18:23
  • @ThingumaBob The projection is probably GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]] – Angelo.Hannes Jul 20 '17 at 18:40
  • that should be NAD83 (EPSG:4269). update your geom column to that and transform to WGS84 to use the geography type. – geozelot Jul 20 '17 at 18:49
  • @ThingumaBob So I updated my table with select updateGeometrySRID('at_centerline','geom',4269) and queried with select sum(st_length(st_transform(geom, 4326)::geography))/1000*0.621371 from at_centerline and still get 2122 miles – Angelo.Hannes Jul 20 '17 at 18:55
  • sorry, yes, using updateGeometrySRID actually transforms the datas SRID from its current to the one given (both 4326 in your case, so nothing changed...). what you want is to make sure that you either imported your data with the correct one or, if none is set, set it to the right one. – geozelot Jul 20 '17 at 19:00
  • @ThingumaBob Ok, so I reimported the dataset and set the srid from 0 to 4269. Still no difference – Angelo.Hannes Jul 20 '17 at 19:09
  • 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 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
  • what about st_distance_spheroid? take the two end points of the linestring and compute the distance??...https://gis.stackexchange.com/questions/109562/does-postgis-have-a-vincenty-distance-calculation – ziggy Jul 20 '17 at 19:43
  • @ziggy wouldn't that leave out all the turns, since a geom data contains a multilinestring? – Angelo.Hannes Jul 20 '17 at 20:16
  • @Angelo.Hannes good point, didnt think of that. just trying to brainstorm – ziggy Jul 20 '17 at 20:17

1 Answers1

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

DPSSpatial_BoycottingGISSE
  • 18,790
  • 4
  • 66
  • 110