Join multiple spatial tables by distance

Question

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;

Answer 1

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.

Answer 2

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