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 <[email protected]> wrote: From: nevillekb <[email protected]> Subject: [postgis-users] Using PostGIS for latitude & longitude To: [email protected] 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 [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
