[postgis-users] Join on closest point

[Kevin Neufeld]

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?