[postgis-users] query help - streets near/around a state

[Charles Galpin]

Hi All

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

SELECT *
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.

thanks,
charles