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 <[email protected]>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 <[email protected]> > 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 <[email protected]> > 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 <[email protected]> > 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 : [email protected] / > [email protected] > > > > > Web Site : axonactive.vn> > > > > > >Address : 87 Thang Long Street,Gate 2 Ward 4, > > > > Tan Binh District, HCM city, Vietnam. > > > > > > > > > > > >_______________________________________________ > > > postgis-users mailing list > > >[email protected] > > > > > >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 : [email protected] / > [email protected] > > > > > > Web Site : axonactive.vn > > >Address : 87 Thang Long Street,Gate 2 Ward 4, > > > Tan Binh District, HCM city, Vietnam. > > > > > > > > >_______________________________________________ > > postgis-users mailing list > >[email protected] > > > 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 : [email protected] / [email protected] > > > 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 : [email protected] / [email protected] > > Web Site : axonactive.vn > >Address : 87 Thang Long Street,Gate 2 Ward 4, > > Tan Binh District, HCM city, Vietnam. > > > > > > _______________________________________________ > postgis-users mailing list > [email protected] > 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 : [email protected] / [email protected] Web Site : http://axonactive.vn Address : 87 Thang Long Street,Gate 2 Ward 4, Tan Binh District, HCM city, Vietnam.
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
