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

[Nicklas Avén]

Hallo Charles

I don't really get why you do that test :
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)

It also looks from your distances that you are working in a lat lon projection. That means that your distance will mean different things in east-west compared to south-north. You should consider transforming toi a planar projection or use the geography format.

To make this work fast the key is to get the indexes working. Do you have spatial indexes on your geometries? Are the indexes used?

If you need that construction with "gid in(" it is also important to have an index on gid,

HTH

Nicklas

2010-12-22 skrev 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
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20101222/8225d672/attachment.html>