I drew 2 shapefiles - point.shp and line.shp. Line.shp has some lines intersecting points in point.shp. I converted the shapefile to PostgreSQL, but how can I make a spatial query to select a line from one point to another point ?

Thanks all.

  • "to select a line from one point to another point" isn't a very clear question. – mrg Apr 14 '12 at 7:42
  • sorry, I mean how to find the rout between two location with spatial query. – newToAndroid Apr 14 '12 at 11:14
  • Do you need to simply create a "straight line" between the 2 points. As in, to determine the distance, or do you need to follow road segments or other linear features? – RyanDalton Nov 15 '12 at 21:53

Doing this from memory, but it should be as simple as this. Replace the_geom with the name of your geometry column.

SELECT a.line FROM a where ST_Intersects(a.the_geom, b.the_geom)


You need to use a topology. In this case a topology in the form of a graph. You can use pgRouting (http://pgrouting.org/) in order to achieve that. Take a look at http://underdark.wordpress.com/2011/02/07/a-beginners-guide-to-pgrouting/.

Also, you will need to break those lines at the points. pgRouting needs the begin point and the endpoint of each line. I think you will find out this by yourself.

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.