[postgis-users] ST_DWithin with radius 0.000001

nguyen liem liemnguyendl at gmail.com
Tue May 25 02:43:31 PDT 2010


HI Nicklas

If i use ST_Intersect that take 30s (got 15356 rows)
If ST_DWithin: 5s  (got 15482 rows)

I see ST_DWithin is really faster than ST_Intersect. It got more neighbours
but it's not a problem.

One more question, if i would like to use ST_DWithin in projection WGS84 and
the radius is in 1 meter, how to do that?

Cheers

On Tue, May 25, 2010 at 4:37 PM, Nicklas Avén <nicklas.aven at jordogskog.no>wrote:

> The polygon you are testing against, is it very complex?
>
> How long time does it take?
>
> did you analyse the table after adding the index?
>
>
> /Nicklas
>
>
>
> 2010-05-25 nguyen liem wrote:
>
> Hi Nicklas
> >
> >sr for the mistake  ... field but it was not improve the perofrmance for
> ST_intersect
> >
> >Cheers
> >
> >>
> On Tue, May 25, 2010 at 3:53 PM, nguyen liem <liemnguyendl at gmail.com>
> wrote:
> >Hi Nicklas
> >
> >my dataset about 100.000 rows and after run my query i get about 15.000
> rows. I has created index for "geom" field but it was improve the
> perofrmance for ST_intersect
> >
> >I used SRID 4326, but I don't know how to convert from degree to meter.
> >
> >Cheers
> >
> >>
> >
> >
>
> >
> On Tue, May 25, 2010 at 3:43 PM, Nicklas Avén <nicklas.aven at jordogskog.no>
> wrote:
> >
> >
> >
>
> >
> >
> Ok
> >
> > The distance of 0.00001 degree in east west direction will vary depending
> on how far from the equator you are.
> >
> > How many points is there in your dataset?
> >
> > Do you see if the index is used?
> >
> > /Nicklas>
> >
>
> >
>
> >
> > 2010-05-25 nguyen liem wrote:
> >
> > Hi Nicklas
> > >
> > >Actually the points can be outside the polygon and returning true but it
> can be tolerance about 1 meters outside.
> > >I'm using SRID: 4326. I would like to know 0.00001 with the projection
> 4326 is equal 1 meter or not ?
> > >
> > >I used ST_Intersects (geom, geometry); but it's really slow, I don't
> know why.
> > >I also used ST_Dwithin (geom, geometry, 0) and the result is empty, i
> couldn't found any neighbour.
> > >
> > >Cheers
> > >
> > >>
> >
> >
> >
>
> >
> On Tue, May 25, 2010 at 3:22 PM, Nicklas Avén <nicklas.aven at jordogskog.no>
> wrote:
> > > > >
> Hallo
> > >
> > > What you are doing with ST_Dwithin is to check if your geom and
> geometry have parts closer to (or exactly) 0.00001 mapunits from eachother.
> If you are using a meter based SRID then you are checking if they are within
> 0.00001 meters from eachother. So actually in this case your points can be
> just outside the polygon and returning true. If you want to use st_dwithin
> you should use 0 instead of 0.00001 . But I don't think that is any faster
> than ST_Intersects. The important thing in both cases is to have working
> spatial indexes.
> > >
> > > put an index on geom column in my_neighbour table and analyze the
> table.
> > >
> > > Create index idx_mn_geom
> > > on my_neighbour
> > > using gist(geom);
> > > analyse my_neighbour;
> > >
> > > Then try:> >
>
> > > SELECT id, geom
> > > FROM my_neighbour
> > >
> WHERE ST_Intersects (geom, geometry);
> > >
> > > I guess that is the best solution.
> > >
> > > Hope that helps
> > >
> > > Nicklas> >
>
> > >
> > > 2010-05-25 nguyen liem wrote:
> > >
> > > Hi all
> > > >
> > > >At the first time i used ST_Contains but it take a long time, so I
> used ST_Dwithin to improve the performance for the query statement but i
> don't know it is correct or not.
> > > >I would like to know my points are within in polygon or not:
> > > >
> > > >SELECT id, geom
> > > >FROM my_neighbour
> > > >WHERE ST_Dwithin (geom, geometry, 0.00001)
> > > >
> > > >geometry: is a polygon(input parameter).
> > > >0.00001: I means the radius of geometry is 1 meters, is it right?
> > > >
> > > >
> > > >Cheers.
> > > >
> > > >--
> > > >Liem Nguyen
> > > >Developer
> > > >Axon Active Corp.
> > >
> >----------------------------------------------------------------------------
> > > >Direct Line      : +84 38 948 4645
> > > >Fax                  : +84 38 811 3489
> > > > Mobile Phone  : +84 168 994 8897
> > > >Skype ID         : liemnguyendl2204
> > > >Email               : liem.nguyen at axonactive.net /
> liemnguyendl at gmail.com
> > >
> > Web Site          : axonactive.vn> >
>
> > > >Address           : 87 Thang Long Street,Gate 2 Ward 4,
> > > >                          Tan Binh District, HCM city, Vietnam.
> > > >
> > > >
>
> > >_______________________________________________
> > > postgis-users mailing list
> > >postgis-users at postgis.refractions.net
> >
> >
> >postgis.refractions.net/mailman/listinfo/postgis-users
> > >
> > >
> >
>
> > >
> > >
> > >--
> > >Liem Nguyen
> > >Developer
> > >Axon Active Corp.
> >
> >----------------------------------------------------------------------------
> > >Direct Line      : +84 38 948 4645
> > > Fax                  : +84 38 811 3489
> > >Mobile Phone  : +84 168 994 8897
> > >Skype ID         : liemnguyendl2204
> > >Email               : liem.nguyen at axonactive.net /
> liemnguyendl at gmail.com
> >
> >
> > Web Site          : axonactive.vn
> > >Address           : 87 Thang Long Street,Gate 2 Ward 4,
> > >                          Tan Binh District, HCM city, Vietnam.
> > >
> > >
>
> >_______________________________________________
> > postgis-users mailing list
> >postgis-users at postgis.refractions.net
> >
> postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> >
>
> >
> >
> >--
> >Liem Nguyen
> >Developer
> >Axon Active Corp.
>
> >----------------------------------------------------------------------------
> >Direct Line      : +84 38 948 4645
> > Fax                  : +84 38 811 3489
> >Mobile Phone  : +84 168 994 8897
> >Skype ID         : liemnguyendl2204
> >Email               : liem.nguyen at axonactive.net / liemnguyendl at gmail.com
> >
> Web Site          : axonactive.vn>
> >
>
> >
>
> >Address           : 87 Thang Long Street,Gate 2 Ward 4,
> >                          Tan Binh District, HCM city, Vietnam.
> >
> >
>
> >
> >
> >--
> >Liem Nguyen
> >Developer
> >Axon Active Corp.
>
> >----------------------------------------------------------------------------
> >Direct Line      : +84 38 948 4645
> > Fax                  : +84 38 811 3489
> >Mobile Phone  : +84 168 994 8897
> >Skype ID         : liemnguyendl2204
> >Email               : liem.nguyen at axonactive.net / liemnguyendl at gmail.com
> > Web Site          : axonactive.vn
> >Address           : 87 Thang Long Street,Gate 2 Ward 4,
> >                          Tan Binh District, HCM city, Vietnam.
> >
> >
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>


-- 
Liem Nguyen
Developer
Axon Active Corp.
----------------------------------------------------------------------------
Direct Line      : +84 38 948 4645
Fax                  : +84 38 811 3489
Mobile Phone  : +84 168 994 8897
Skype ID         : liemnguyendl2204
Email               : liem.nguyen at axonactive.net / liemnguyendl at gmail.com
Web Site          : http://axonactive.vn
Address           : 87 Thang Long Street,Gate 2 Ward 4,
                         Tan Binh District, HCM city, Vietnam.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100525/e8c2256c/attachment.html>


More information about the postgis-users mailing list