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

[Santosh Gaikwad]

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 :Santosh.Gaikwad at saama.com
http://www.saama.com

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] 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 :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
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Santosh Gaikwad.vcf
Type: text/x-vcard
Size: 145 bytes
Desc: Santosh Gaikwad.vcf
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070706/9d444806/attachment.vcf>