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'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)

share|improve this question

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)

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
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:

    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)

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.