[postgis-users] Using PostGIS for latitude & longitude

[pcreso at pcreso.com]

Hi Neville,

Obviously you start by installing Postgis in your database.

Follow the instructions for your Postgis version & operating system.

To create your geometry:

1. add a geometry column to your table. This will be a point type, with a SRID of 4326 (ie: lat long degrees - see http://www.spatialreference.org/ref/epsg/4326/ 
See here for the command to use: 
http://postgis.refractions.net/docs/AddGeometryColumn.html

2. Populate this column (called geom, for example), from your lat, lon columns with:
update table set geom=ST_SetSRID(ST_MakePoint(lon,lat),4326);
as described at http://www.postgis.org/docs/ST_MakePoint.html


You then need to make your query to retrieve the points with 7 miles of a specified location. This can get complicated depending on how accurate you want to be. The distance of 1 degree for your latitudes is 60 nautical miles. For longitude, it is 60 nm at the equator & decreases proportional to the cosine of the latitude for non-equatorial values. Postgis allows you to reproject your coordinates, or calculate against a sphere, or a selected spheroid (given the Earth is not spherical). I'm assuming here that a spherical calculation will work in your case, using 1/60th of a degree = 1 mile.

given a user location of $x,$y, you need to construct a point geometry & check the distance (measured on a sphere in units degrees) against each of your points, eg:
select * from table where ST_Distance_Sphere(ST_MakePoint($x,$y),geom) <= 7/60;

see: http://postgis.refractions.net/documentation/manual-1.4/ST_Distance_Sphere.html

HTH,

  Brent Wood



--- On Wed, 12/19/12, nevillekb <nevillekb at gmail.com>
 wrote:

From: nevillekb <nevillekb at gmail.com>
Subject: [postgis-users] Using PostGIS for latitude & longitude
To: postgis-users at postgis.refractions.net
Date: Wednesday, December 19, 2012, 10:51 PM


I have a table in postgresql 9.2 that stores the latitude and longitude of
locations as integer values.

I intend to do something like when a user searches for a location, he also
gets information on other locations that are within a 7 mile radius of that
searched location.

How do i use postGIS for this since i am new to it. Any idea.?




--
View this message in context: http://postgis.17.n6.nabble.com/Using-PostGIS-for-latitude-longitude-tp5002097.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121219/c00f9ea6/attachment.html>