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'm trying to join two tables, one which contains road names and the other just a point. After exhausting research I'm now stuck.

The idea is to join up the two tables using only distance, ie, select all road names which is within 500 meteres of a postcode

My current query does nothing except waste time and I'm wondering where I'm going wrong?

SELECT "R"."name", "P"."Postcode"

FROM "planet_osm_roads" AS "R" JOIN "gb_postcodes" AS "P" 

ON st_dwithin(st_transform("P"."Geom", 900913), "R"."way" , 500)

ORDER BY "P"."Geom" <-> st_setsrid(st_makepoint(0.06703376770019531,  51.51646108300825), 900913)


LIMIT 10;
share|improve this question
add comment

2 Answers 2

Get rid of the index traversal operation (<->) on the ORDER BY clause, it's causing the database to try to first sort the whole table in terms of distance from road before getting to the clause you want.

SELECT "R"."name", "P"."Postcode", ST_Distance("P"."Geom", "R"."way") AS dist
FROM "planet_osm_roads" AS "R" JOIN "gb_postcodes" AS "P" 
ON ST_DWithin(st_transform("P"."Geom", 900913), "R"."way" , 500)
ORDER BY dist ASC;

Incidentally, because you are transforming one of the clauses in your join condition, you may not be getting the best assist from the indexes you could. Things work best when they are all in the same projected space.

share|improve this answer
    
+1 for suggesting that all geometry should share the same srid.. it also makes the query more easy to read. I have had to modify the query slightly to make it work. See below –  PowerAktar Nov 24 '12 at 22:39
add comment
up vote 0 down vote accepted

The && operator has worked for me. I suspect there are many ways of achieving the same but it certainly is quite elegant:

SELECT 
    "R"."name",
    "P".*,
    st_distance("R"."way", "P"."Geom") AS "Distance"


FROM "planet_osm_line" AS "R"

JOIN "gb_postcodes" AS "P" ON "R"."way" && "P"."Geom"


ORDER BY    "P"."Geom" <-> st_transform(st_setsrid( st_makepoint(-0.12391805648803711, 51.51400408502046),4326), 900913)


LIMIT 10
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.