[postgis-users] Join on closest point

[MarkW]

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 <tommaso.lavoro at gmail.com> 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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090506/6d63abed/attachment.html>