[postgis-users] Join on closest point

[totom]

Hi, I'm asking help for a (maybe) simple query.
I have 2 tables, p filled with points and l filled with lines.
I want an outher join p and l on the closest line to each point...
data_crash_feature are points, streets_prov_roma are lines, and my query
obviously doesn't work...

SELECT
data_crash_feature.id,data_crash_feature.description,data_crash_feature._fid
,streets_prov_roma.gid,streets_prov_roma.st_name,
ST_Distance(streets_prov_roma.the_geom,data_crash_feature.the_geom) as dist
FROM data_crash_feature
RIGHT OUTER JOIN   streets_prov_roma
ON  streets_prov_roma.gid IN (
SELECT s2.gid
FROM streets_prov_roma s2
WHERE ST_DWithin(streets_prov_roma.the_geom, s2.the_geom, 0.0001) AND
s2.st_name != ''
ORDER BY Distance(streets_prov_roma.the_geom, s2.the_geom) ASC
LIMIT 1
)

Can anyone help me?
-- 
View this message in context: http://www.nabble.com/Join-on-closest-point-tp23402603p23402603.html
Sent from the PostGIS - User mailing list archive at Nabble.com.