[postgis-users] Lat/Long,geometry and buffer

[Paul Ramsey]

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 :Santosh.Gaikwad at saama.com
> 
> http://www.saama.com
> 
>  
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users


-- 

   Paul Ramsey
   Refractions Research
   http://www.refractions.net
   pramsey at refractions.net
   Phone: 250-383-3022
   Cell: 250-885-0632