[postgis-users] query help - streets near/around a state
cgalpin at lhsw.com
Wed Dec 22 06:27:58 PST 2010
I have a need to identify roads (in say streets_ny) neighboring the state of new jersey which I have in streets_nj. I used to do this with a function that looked for the new york streets near/touching the new jersey roads, then looped over these roads finding roads near them. I have an updated data set and this runs much slower than it did before and I was concerned about getting duplicates with this method so I'm looking for alternatives. I tried the following but it seems to actually be worse
FROM streets_ny ns, streets_ny s
WHERE (( st_dWithin(ns.the_geom, s.the_geom, 0.08) AND s.func_class = '1' )
OR ( st_dWithin(ns.the_geom, s.the_geom, 0.032) AND s.func_class = '2' )
OR ( st_dWithin(ns.the_geom, s.the_geom, 0.008) AND ( s.func_class = '3' OR s.func_class = '4') ))
AND ns.gid in (
SELECT nns.gid from streets_nj s, streets_ny nns
WHERE s.the_geom && nns.the_geom
AND st_dWithin(s.the_geom, nns.the_geom, 0.001)
Basically I want longer stretches of the major roads and smaller stretches of secondary/side roads in the neighboring states.
Is there a faster way to accomplish this? I think it would be simpler if I had a polygon defining the state of nj but not sure it should be necessary to do this faster.
More information about the postgis-users