Dear Sir, I am using the following query to find out the locations with a given radius in meters from a point.
SELECT street,city,state,zip,county,latitude,longitude, distance_sphere(GeometryFromText ( 'POINT(-121.962459 37.388364)', 4326 ), the_geom) from california where distance_sphere ( GeometryFromText ( 'POINT(-121.962459 37.388364)', 4326 ), the_geom) < 500; As you have suggested to use ST_dwithin which will be available in newer install. But yet I did not found newer install of PostGIS. I would like to know am I correct in using the above query? I also used distance_shperoid() for the same but it took much longer time to get the result. SELECT street,city,state,zip,county,latitude,longitude,the_geom, distance_spheroid ( GeometryFromText ( 'POINT(-121.962459 37.388364)', 4326 ), the_geom, 'SPHEROID["WGS 84",6378137,298.257223563]' ) FROM california WHERE distance_spheroid ( GeometryFromText ( 'POINT(-121.962459 37.388364)', 4326 ), the_geom, 'SPHEROID["WGS 84",6378137,298.257223563]' ) < 500 ; Also I found there is a noticeable difference of 300 meters between both the queries but I could able to find out the locations within the radius. If you use distance_spheroid ( ), you can give the projection system. I do not know whether distance_sphere ( ) works with the projection 4326. The performance of distance_spehere() is much faster than distance_spheroid(). How I can make distance_spheroid() faster? I am looking forward for the help. Thanks & regards, Santosh Gaikwad Senior Software Developer Saama Technologies (India) Pvt Ltd. 6th Floor West Wing, Marisoft III, Marigold Premises, Kalayani Nagar, Pune - 411014. India Phone : +91 20 66071319 Extn: 397 Mobile: +91-9422005927 E-mail :[EMAIL PROTECTED] http://www.saama.com -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul Ramsey Sent: Wednesday, July 04, 2007 9:40 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Lat/Long,geometry and buffer Do NOT use buffer() to do radius queries... you're building a whole bunch of new geometry and then throwing it away. Use Distance(), or even better, st_dwithin() on a newer install. Select * from california where st_dwithin( transform(the_geom,2163), transform(GeomFromText('POINT(-121.962459 37.388364)',4326),2163), 100 ); This is still inefficient, unless you build a functional index on transform(). It would be better to put your polygons into your working planar projection to start with and not use transform() on them. 2163 is planar, it should return results in meters, and whether it is "good enough" depends a lot on your requirement for accuracy. Compare some distances in 2163 with the distances returned for the same points using distance_spheroid() if you want a feel for the error. Paul Santosh Gaikwad wrote: > Hi, > > > > I have US address which have been geocoded to get latitude and longitude. > > > > I am following the below steps > > > > 1. Add column > > > > SELECT AddGeometryColumn( 'public', 'california', 'the_geom', 4326, > 'POINT', 2 ); > > > > 2. Populate geometry > > > > UPDATE california > > SET the_geom = PointFromText('POINT(' || longitude || ' ' || > latitude || ')',4326) > > > > 3. Spatial query (I would like to build the spatial query for listing > out the locations for a given radius of 100 meters from a particular > location.) > > > > > > Select * > > from california > > where setSRID( > > buffer( > > transform(GeomFromText('POINT(-121.962459 > 37.388364)',4326),2163), > > 100 > > ), > > 2163)&& transform(the_geom, 2163) > > > > This returns back me the locations with specified 100 meters radius. > This seems to be working fine but I am not confidant about it. When I > checked out SRID 2163 in spatial_ref_sys table, it says that the > projection is US national atlas equal area and unit is meters. I would > like to know whether this projection covers whole USA satisfactorily or > not. When I am using openjump by bringing postgis data into it, I can > not see the distance in meters on map. Why it is so? Thus this means > SRID 2163 has been used only to transform the projection. > > > > Next I want to work on whole USA data. Please kindly let me know whether > I am following the correct steps or not? > > > > > > Santosh Gaikwad > > Senior Software Developer > > Saama Technologies (India) Pvt Ltd. > 6th Floor West Wing, Marisoft III, > Marigold Premises, Kalayani Nagar, > Pune - 411014. India > Phone : +91 20 66071319 Extn: 397 > > Mobile: +91-9422005927 > E-mail :[EMAIL PROTECTED] > > http://www.saama.com > > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users -- Paul Ramsey Refractions Research http://www.refractions.net [EMAIL PROTECTED] Phone: 250-383-3022 Cell: 250-885-0632 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
BEGIN:VCARD VERSION:2.1 N:Gaikwad;Santosh FN:Santosh Gaikwad EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:20070529T045803Z END:VCARD
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
