Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

Example:

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
WHERE
    ST_Distance_Spheroid(route.lineString, 'POINT (long_A lat_A)', 'SPHEROID["GRS 1980",6378137,298.257222101]') < 5000
AND
    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
add comment

1 Answer

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:

SELECT ...
WHERE
        St_Line_Locate_Point(lineString, ST_GeomFromText('POINT(startPointLongitude startPointLatitude)'))
        < St_Line_Locate_Point(lineString, ST_GeomFromText('POINT(endPointLongitude endPointLatitude)'))
share|improve this answer
add comment

Your Answer

 
discard

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.