Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a problem with finding routes that are near (in range X km) the passed points (for e.x. A and B) in PostgreSQL PostGIS.


The point A denotes Source point and The point B denotes the Destination.

Tn system there are routes represented as LineString. I am also able to consider another ways to represent the routes.

Phere is no problem to find routes contains the points A and B in specific range (for e.x. 5 km):

SELECT * FROM routes
    ST_Distance_Spheroid(route.lineString, 'POINT (long_A lat_A)', 'SPHEROID["GRS 1980",6378137,298.257222101]') < 5000
    ST_Distance_Spheroid(route.lineString, 'POINT (long_B lat_B)', 'SPHEROID["GRS 1980",6378137,298.257222101]') < 5000

But there is the problem, that this query considers the path regardless its direction. It is insensitive to order the points that describes the linestring.

So there is a question. How select the routes contains points in specific range and consider order of points.

BTW: There is possible to represent routes as points and store in database the POINT and order number. So I can look for nearest points.

share|improve this question
Why not use the pgrouting package to find routes? Pre-built route finding algorithms working through PostGIS/PostgreSQL seems an easy solution here. – Michalis Avraam Apr 1 '13 at 20:36
I have no predefined routes or fragments. The routes are builded from the points returned from Google Maps. So pgrouting cannot be usefull for me. – Athlan Apr 1 '13 at 22:23
up vote 1 down vote accepted

Finally i solved problem of ordering points based on St_Line_Locate_Point returned float value funtion.

Complete query:

        St_Line_Locate_Point(lineString, ST_GeomFromText('POINT(startPointLongitude startPointLatitude)'))
        < St_Line_Locate_Point(lineString, ST_GeomFromText('POINT(endPointLongitude endPointLatitude)'))
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.