<html>
<head>
</head>
<body>Hallo Charles<br />
<br />
I don't really get why you do that test :<br />
AND ns.gid in (<br />
SELECT nns.gid from streets_nj s, streets_ny nns<br />
WHERE s.the_geom && nns.the_geom<br />
AND st_dWithin(s.the_geom, nns.the_geom, 0.001)<br />
<br />
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.<br />
<br />
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?<br />
<br />
If you need that construction with "gid in(" it is also important to have an index on gid,<br />
<br />
HTH<br />
<br />
Nicklas<br />
<br />
2010-12-22 skrev Charles Galpin :<br />
<br />
Hi All<br />
><br />
>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<br />
><br />
>SELECT *<br />
>FROM streets_ny ns, streets_ny s<br />
>WHERE (( st_dWithin(ns.the_geom, s.the_geom, 0.08) AND s.func_class = '1' )<br />
> OR ( st_dWithin(ns.the_geom, s.the_geom, 0.032) AND s.func_class = '2' )<br />
> OR ( st_dWithin(ns.the_geom, s.the_geom, 0.008) AND ( s.func_class = '3' OR s.func_class = '4') ))<br />
>AND ns.gid in (<br />
> SELECT nns.gid from streets_nj s, streets_ny nns<br />
> WHERE s.the_geom && nns.the_geom<br />
> AND st_dWithin(s.the_geom, nns.the_geom, 0.001)<br />
> )<br />
><br />
>Basically I want longer stretches of the major roads and smaller stretches of secondary/side roads in the neighboring states.<br />
><br />
>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.<br />
><br />
>thanks,<br />
>charles<br />
><br />
>_______________________________________________<br />
>postgis-users mailing list<br />
>postgis-users@postgis.refractions.net<br />
>http://postgis.refractions.net/mailman/listinfo/postgis-users<br />
><br />
>
</body>
</html>