3

I have a postgreSQL database that contains latitude, longitude, and elevation for a region of interest. The same data is available in a geoTIFF, if that would make things easier.

Given two Latitude/Longitude pairs, I need to find the highest elevation along the straight line path connecting the two points. Using Python as a programing language, how would I do this?

Please keep in mind that while I am reasonably fluent in Python and PostgreSQL I am completely unfamiliar with any specialized tools or terms for solving this issue. I've heard of PostGIS, for example, but know nothing about it or how to use it, if it would help here.

Edit: At the moment, I've managed to figure out a query to find the elevation closest to a given point (since it is unlikely that any given lat/lng pair will match exactly with the data in the database), and of course if there was some query I could do to get all the data points that lie along the path, I could easily get the maximum elevation of those points.

It's finding the point that lie along the path that is baffling me.

EDIT 2: After some additional searching, it would appear that the PostGIS function LINESTRING may be of use, when combined with something else to select all points "along" that line. Still looking for what to combine with it, however.

|improve this question
  • Welcome to gis.stackexchange! Please note that a good question on this site is expected to show some degree of research on your part, i.e. what you have tried and - if applicable - code so far. For more info, you can check our faq. – underdark♦ Jan 3 '17 at 21:35
3

Here's one way of doing it using a query that uses ST_DWithin to find points close to the linestring.

I have set the distance to 100 meters (units does depend on the unit of your table), but you can modify it to match your dataset.

Also note that I have set the SRID of your input coordinates to 4326.

SELECT Max(p.elevation) 
  FROM points s
  Where ST_DWithin(s.the_geom
                  , ST_SetSRID(
                         ST_MakeLine( 
                                 ST_MakePoint(-71.160281 42.258729)
                               , ST_MakePoint(-71.161144 42.25932))
                         , 4326)
                  , 100)

The query only returns the maximum elevation of the points within the specified distance of the line. You don't get the actual elevation points selected.

|improve this answer
  • 2
    You should add ::geography after s.the_geom if you want to compute the distance in meters and not in degrees. – thibautg Jan 4 '17 at 13:19
  • Thanks, missed that even though it was in the documentation: "For geometry units are in those of spatial reference and For geography units are in meters" – Peter Horsbøll Møller Jan 4 '17 at 14:37
  • 1
    While I marked this answer as accepted, because it does work, it assumes a column "the_geom" in the table, which I didn't have (as mentioned, I have latitude and longitude). After some digging, I managed to figure out how to create and populate a "geography" column, however, so this answer does work. – ibrewster Jan 4 '17 at 20:29
  • @ibrewster see thibautg's comment above to cast s.the_geom to geography for distances in meters. Where ST_DWithin(s.the_geom::geography and you don't have to create a geography column. – kttii Jan 4 '17 at 21:34
  • 1
    @kttii There IS no s.the_geom column. It's not that it's a geometry rather than a geography, it's that it simply doesn't exist. I have a numeric column for latitude, and a numeric column for longitude. No geometry OR geography columns. As Peter mentions, I can create it on the fly, but the more logical option (which I did) was to simply create an (indexed) geography column from the latitude/longitude data I had, whereupon this answer works. – ibrewster Jan 5 '17 at 18:09

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.