You can get accurate distances in meters simply by casting to geography. <br><br><a href="http://www.postgis.org/docs/ST_Distance.html">http://www.postgis.org/docs/ST_Distance.html</a><br><br>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.<br>
<br>Duncan<br><br><br><br><br><br><div class="gmail_quote">On Wed, Dec 19, 2012 at 11:08 AM, <span dir="ltr"><<a href="mailto:pcreso@pcreso.com" target="_blank">pcreso@pcreso.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td style="font:inherit" valign="top"><div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td style="font:inherit;font-family:arial;font-size:10pt">
Hi Neville,<br><br>Obviously you start by installing Postgis in your database.<br><br>Follow the instructions for your Postgis version & operating system.<br><br>To create your geometry:<br><br>1. add a geometry column to your table. This will be a point type, with a SRID of 4326 (ie: lat long degrees - see <a rel="nofollow" href="http://www.spatialreference.org/ref/epsg/4326/" target="_blank">http://www.spatialreference.org/ref/epsg/4326/</a> <br>
See here for the command to use: <br><a rel="nofollow" href="http://postgis.refractions.net/docs/AddGeometryColumn.html" target="_blank">http://postgis.refractions.net/docs/AddGeometryColumn.html</a><br><br>2. Populate this column (called geom, for example), from your lat, lon columns with:<br>
update table set geom=ST_SetSRID(ST_MakePoint(lon,lat),4326);<br>as described at <a href="http://www.postgis.org/docs/ST_MakePoint.html" target="_blank">http://www.postgis.org/docs/ST_MakePoint.html</a><br><br><br>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.<br><br>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:<br>
select * from table where ST_Distance_Sphere(ST_MakePoint($x,$y),geom) <= 7/60;<br><br>see: <a href="http://postgis.refractions.net/documentation/manual-1.4/ST_Distance_Sphere.html" target="_blank">http://postgis.refractions.net/documentation/manual-1.4/ST_Distance_Sphere.html</a><br>
<br>HTH,<br><br> Brent Wood<br><br><br><br>--- On <b>Wed, 12/19/12, nevillekb <i><<a href="mailto:nevillekb@gmail.com" target="_blank">nevillekb@gmail.com</a>></i></b>
wrote:<br><blockquote style="border-left:2px solid rgb(16,16,255);margin-left:5px;padding-left:5px"><br>From: nevillekb <<a href="mailto:nevillekb@gmail.com" target="_blank">nevillekb@gmail.com</a>><br>Subject: [postgis-users] Using PostGIS for latitude & longitude<br>
To: <a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>Date: Wednesday, December 19, 2012, 10:51 PM<div class="im"><br><br><div><br>I have a table in postgresql 9.2 that stores the latitude and longitude of<br>
locations as integer values.<br><br>I intend to do something like when a user searches for a location, he also<br>gets information on other locations that are within a 7 mile radius of that<br>searched location.<br><br>How do i use postGIS for this since i am new to it. Any idea.?<br>
<br><br><br><br>--<br>View this message in context: <a rel="nofollow" href="http://postgis.17.n6.nabble.com/Using-PostGIS-for-latitude-longitude-tp5002097.html" target="_blank">http://postgis.17.n6.nabble.com/Using-PostGIS-for-latitude-longitude-tp5002097.html</a><br>
Sent from the PostGIS - User mailing list archive at Nabble.com.<br>_______________________________________________<br>postgis-users mailing list<br><a rel="nofollow">postgis-users@lists.osgeo.org</a><br><a rel="nofollow" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></td></tr></tbody></table></div></td></tr></tbody></table><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br><br clear="all"><br>-- <br>Dr Duncan Golicher<br>Investigador Titular, <br>El Colegio de la Frontera Sur, Chiapas,Mexico<br>Mexico tel +52 1 967 137 94 20<br>Skype name duncangolicher<br><br>Publications: <a href="http://www.mendeley.com/profiles/duncan-golicher" target="_blank">http://www.mendeley.com/profiles/duncan-golicher</a><br>
<br>Senior lecturer, Bournemouth University, UK<br>Centre for Conservation Ecology & Environmental Change<br>School of Applied Sciences<br>Christchurch House rm C218a<br>Bournemouth University<br>Fern Barrow<br>Poole (Dorset) BH12 5BB UK<br>
Tel. +44 (0)1202 961682<br><br>For list of publications see Researcher ID:<br><a href="http://www.researcherid.com/rid/B-4240-2009" target="_blank">http://www.researcherid.com/rid/B-4240-2009</a><br><br><a href="mailto:dgolicher@bournemouth.ac.uk" target="_blank">dgolicher@bournemouth.ac.uk</a><br>
<a href="mailto:dgoliche@ecosur.mx" target="_blank">dgoliche@ecosur.mx</a><br><br>Researcher ID:<br><a href="http://www.researcherid.com/rid/B-4240-2009" target="_blank">http://www.researcherid.com/rid/B-4240-2009</a><br>
<br>