You can get accurate distances in meters simply by casting to geography. http://www.postgis.org/docs/ST_Distance.html
This works with ST_DWithin as well in the where clause. Just makes queries a bit slower. To cast geometry to geography use the :: operator. Duncan On Wed, Dec 19, 2012 at 11:08 AM, <[email protected]> wrote: > 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 > > -- Dr Duncan Golicher Investigador Titular, El Colegio de la Frontera Sur, Chiapas,Mexico Mexico tel +52 1 967 137 94 20 Skype name duncangolicher Publications: http://www.mendeley.com/profiles/duncan-golicher Senior lecturer, Bournemouth University, UK Centre for Conservation Ecology & Environmental Change School of Applied Sciences Christchurch House rm C218a Bournemouth University Fern Barrow Poole (Dorset) BH12 5BB UK Tel. +44 (0)1202 961682 For list of publications see Researcher ID: http://www.researcherid.com/rid/B-4240-2009 [email protected] [email protected] Researcher ID: http://www.researcherid.com/rid/B-4240-2009
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
