Your spatial join appears to be joining streets_prov_roma to itself, and doing the LIMIT before joining to data_crash_feature. Thus you may only be getting on street to match to all the points. I think you can simplify your query a bit (see here: http://postgis.refractions.net/documentation/manual-svn/ST_DWithin.html) rather than using a subquery in the join clause put the ST_DWithin spatial join directly into your join statement.
Mark On Wed, May 6, 2009 at 4:54 AM, totom <[email protected]> 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? > -- > 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. > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
