# [postgis-users] Latitude and longitude distances

Mark Fenbers Mark.Fenbers at noaa.gov
Thu Jul 28 09:00:38 PDT 2005

```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;
> 'public','gislocation','the_geom',4269,'POINT',2 );  -- 4269 might
> 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.
> 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>
```