[postgis-users] Join on closest point

[Kevin Neufeld]

Or to find the closest street if there is one...

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
   LEFT JOIN streets_prov_roma b
     ON ST_DWithin (a.the_geom, b.the_geom, 0.0001)
ORDER BY a.gid, dist ASC;

Kevin Neufeld wrote:
> 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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users