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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
