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