What about something like this? SELECT DISTINCT ON (a.gid) a.id, a.description, a._fid, b.gid, b.st_name, ST_Distance(a.the_geom, b.the_geom) AS dist FROM data_crash_feature a, streets_prov_roma b WHERE ST_DWithin (a.the_geom, b.the_geom, 0.0001) ORDER BY a.gid, dist ASC;
Cheers, Kevin totom wrote:
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?
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
