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
