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

[Charles Galpin]

Hi Nicklas

Maybe I am misunderstanding but the verbal description is " find new york roads near roads that meet new jersey roads" and this check is the part that satisfies the "roads that meet new jersey roads" part :)

I have spacial indexes on the_geom for both as well as on func_class, but it looks like I don't on the gid (oops). I'll try this a little later, thanks!

charles

On Dec 22, 2010, at 9:50 AM, Nicklas Avén wrote:

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