Imagine a table of points of interest along with lat/long. The user identifies two of these points. I want to do the following:

  1. Calculate a great-circle path between these points
  2. Find all POIs that lie within "n" miles of the great-circle path from step 1.

I'm totally new to this stuff, what might be a good tool for accomplishing this? And how would you go about solving it using that tool?

  • 1
    Great tutorial question which shows the spatial abilities of postgis +1 – nickves Oct 29 '12 at 0:17

One-liner, assuming the POI points are stored in a "geography" column, you supply the ids of the two points and the search radius in meters:

WITH line AS (
  SELECT ST_MakeLine(p.geog::geometry, q.geog::geometry)::geography AS geog
  FROM pois p, pois q
  WHERE p.id = :id1 and q.id = :id2
SELECT p.name, p.id
FROM pois p
JOIN line
ON ST_DWithin(p.geog, line.geog, :radius);

Only complication is that the geography points have to be cast to geometry to access the line building function, and the line has to then be cast back to geography for use in the distance query.

  • Paul, would you mind explain the query a little bit? They didn't teach us SQL in the university and the query is above my current understanding level. – nickves Oct 29 '12 at 0:21
  • 1
    The "WITH" block pulls out the two points of interest and create a line out of them. So now you have a line in a relation named "line". The next block joins the "line" relation to the pois table, using the condition that only points within the radius will be in the final output relation, which is the condition the questioner was looking for: "only points within N meters of the line between points A and B" – Paul Ramsey Oct 29 '12 at 17:21
  • 2
    Reading up on "WITH" queries will make your life brighter and happier. postgresql.org/docs/current/static/queries-with.html – Paul Ramsey Oct 31 '12 at 18:40

ArcMap has a select-features-within-a-certain radius-of-another-feature tool. It's on the Select by Location dialog box. I wouldn't be surprised if other programs like QGIS have similar functionality.

Just tell the software the distance and go from there.

  • Using software as big black boxes doesn't help you understand what are you doing. "Just telling the software to do something" for you it's big trap produsing something that you don't know if it's correct or not. Yes the issue here is trivial, but imo this way you make the software doing what you want, instead doing something the software is able to do. – nickves Oct 29 '12 at 0:28

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.