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 want to be able to identify the closest roads to two points that are generated through geocoding (a start and end address). The points will likely be near a linestring. Two approaches that I have been experimenting with are:

-- Method 1:
SELECT 
    id
FROM 
    routing
WHERE
    ST_DWithin(ST_Transform(ST_Setsrid(ST_Makepoint(-6.17706680000, 53.300791),4326), 900913), geom, 50.0) 
ORDER BY , 
    ST_distance(geom_way, ST_Transform(ST_Setsrid(ST_Makepoint(-6.17706680000, 53.300791),4326), 900913))
LIMIT 1;
--Time: 228  ms 

-- Method 2:
SELECT  
        id
FROM 
        routing
WHERE
        ST_Intersects(geom, ST_buffer(ST_Transform(ST_Setsrid(ST_Makepoint(-6.17706680000, 53.300791),4326), 900913), 50))
ORDER BY , 
        ST_Distance(geom, ST_Transform(ST_Setsrid(ST_Makepoint(-6.17706680000, 53.300791),4326), 900913))  
LIMIT 1;
--Time: 248 ms 

While both of these are quite fast, I'm unsure if this is the best way of performing this calculation. Both methods assume that the geo-coded point will be <50m away from a road. I also want to figure out the best way of finding two roads and cannot think of an alternative, other than to run to separate queries sequentially, which would mean that it takes ~0.5 seconds in total using the current set-up.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Instead of use ST_distance use the operator <-> It makes use of the GIST index so it should be faster than use st_distance.

share|improve this answer
    
Does it make sense to still use ST_DWithin() when using the <-> operator? –  djq Oct 28 '12 at 23:01
    
If you need, to discard those results where the distance is greater than 50m yes. If you not need that, probably it will be faster skip that. –  Francisco Puga Oct 29 '12 at 8:56

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.