[postgis-users] Latitude and longitude distances

[Mark Fenbers]

Actually, I do this sort of thing all the time, and PostGIS is perfect 
for the job!  First, you want to convert your lat/lon coordinate into a 
POINT geometry.  Sometimes I do this by duplicating the original 
location table with 'SELECT INTO TEMP ..." and adding a geometry column 
(see AddGeometryColumn() function) for my many locations.  You will also 
need to convert your given lat/lon to a POINT geometry (see MakePoint() 
function).  Next you will want to use the buffer() function to create a 
polygon that is a certain radius from your given location, and finally 
test which locations are in the polygon (contains() function).

So let's say your many locations were in a table called location.lat and 
location.lon, and your reference location was (80.5W, 40.5N).  Then to 
retrieve all rows in a 3-degree radius of you reference, you could so 
something like the following (although it is probably not the most 
efficient method):

Begin;
SELECT INTO TEMP gislocation * from location;
SELECT addGeometryColumn( 
'public','gislocation','the_geom',4269,'POINT',2 );  -- 4269 might 
differ if your lat,lon is not referenced to NAD83.
UPDATE gislocation set the_geom = makePoint( lon,lat );
SELECT * from gislocation where contains( buffer( makePoint( 80.5,40.5 
), 3 ), the_geom );

I haven't tested any of this, nor looked up the function syntax, so 
there probably are errors, but this will get you in the ballpark.  Also, 
if you want to compute a 3km radius instead of a 3-degree raduis with 
buffer(), then you have to first convert all your geometries into an 
equal-area projection using the transform() function, such as SRID = 2792.

Hope this helps!

Mark

Vacuum Joe wrote:

>Hello,
>
>I'm new to PostGIS.  I have what I hope is a basic
>question.  I have a database with many locations in
>it.  These locations are all specified by latitude and
>longitude.  I need to be able to do things like select
>all the rows that are within a certain radius of a
>given latitude/longitude.  The results don't have to
>be precise, and the radius will never be more than a
>few miles.
>
>I assume this type of use is perfectly within what
>PostGIS can do, but I can't figure out how to store
>lon/lat data and do searches on it.
>
>If there are any docs or examples you could point me
>to, I would greatly appreciate it.
>
>Thanks
>
>
>__________________________________________________
>Do You Yahoo!?
>Tired of spam?  Yahoo! Mail has the best spam protection around 
>http://mail.yahoo.com 
>_______________________________________________
>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: Mark.Fenbers.vcf
Type: text/x-vcard
Size: 156 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20050728/47a17929/attachment.vcf>