[postgis-users] Latitude and longitude distances
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 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):
> 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!
> Vacuum Joe wrote:
>> 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.
>> 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
>postgis-users mailing list
>postgis-users at postgis.refractions.net
-------------- next part --------------
A non-text attachment was scrubbed...
Size: 156 bytes
Desc: not available
More information about the postgis-users