[postgis-users] Latitude and longitude distances

[Mark Fenbers]

OK.  Then use transform() to convert degrees to meters... as I stated in 
the last sentence of my original reply...  There's a lot of info and 
easy to miss... ;-)

Mark

Mark Fenbers wrote:

> 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
>>
>>  
>>
>_______________________________________________
>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/e060cc13/attachment.vcf>