<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">
Thank you for your response.<div><br></div><div>Let me try to clarify what I am trying to, now that I have found some actual data.</div><div><br></div><div>I have a table with the following structure.</div><div><br></div><div>countries</div><div>___________________________________</div><div>country_name<span class="Apple-tab-span" style="white-space:pre"> </span>|<span class="Apple-tab-span" style="white-space:pre"> </span>character varying(40)</div><div>the_geom<span class="Apple-tab-span" style="white-space:pre"> </span>|<span class="Apple-tab-span" style="white-space:pre"> </span>geometry</div><div>___________________________________</div><div><br></div><div>This table contains all of the countries. What I need to do is determine the distance, in meters, between a point (collected by a GPS) and say the 5 closest countries' borders.</div><div><br></div><div> countries.the_geom SRID = 4269</div><div><br></div><div>When I run the following query, I get back a result that seems to be correct to me, however the distances are in degrees.</div><div><br></div><div>select country_name, distance(the_geom, GeomFromText('Point(-79.68433 40.58954)', 4269) as distance</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>from countries<br></div><div><span class="Apple-tab-span" style="white-space:pre"> </span>order by distance<br></div><div><span class="Apple-tab-span" style="white-space:pre"> </span>limit 5;<br></div><div><br></div><div>Result:</div><div>__________________________________</div><div>United States<span class="Apple-tab-span" style="white-space:pre"> </span>|<span class="Apple-tab-span" style="white-space:pre"> </span>0</div><div>Canada<span class="Apple-tab-span" style="white-space:pre"> </span>|<span class="Apple-tab-span" style="white-space:pre"> </span>1.8533</div><div>Bahamas, The<span class="Apple-tab-span" style="white-space:pre"> </span>|<span class="Apple-tab-span" style="white-space:pre"> </span>13.7850</div><div>Bermuda<span class="Apple-tab-span" style="white-space:pre"> </span>|<span class="Apple-tab-span" style="white-space:pre"> </span>16.9754</div><div>Cuba<span class="Apple-tab-span" style="white-space:pre"> </span>|<span class="Apple-tab-span" style="white-space:pre"> </span>17.4070</div><div><br></div><div>Is it reasonable to simply multiply the distance by 69 (the approximate distance in miles between degrees)? How accurate is this? I don't need to be all that precise, but I do need to at least be in the ballpark.</div><div><br></div><div>Is there some way to determine the closest point in the border of the country to the point collected by the GPS in order to use the distance_sphere() or distance_spheroid() functions for a more precise measurement?</div><div><br></div><div>Thank you very much for any help.</div><div><br></div><div><br><div><div>On Sep 10, 2008, at 9:20 AM, Randall, Eric wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"> <div><font face="Arial" color="#0000ff" size="2"><span class="893345117-09092008">Hi Doug,</span></font></div> <div><font face="Arial" color="#0000ff" size="2"></font> </div> <div><font face="Arial" color="#0000ff" size="2"><span class="893345117-09092008">If your point is in a table points and your states are in a table states and your distances are in feet</span></font></div> <div><font face="Arial" color="#0000ff" size="2"><span class="893345117-09092008"></span></font><font face="Arial" color="#0000ff" size="2"><span class="893345117-09092008">then one way might be something like:</span></font></div> <div><font face="Arial" color="#0000ff" size="2"></font> </div> <div><font face="Arial" color="#0000ff" size="2"></font> </div> <div><font face="Arial"><font color="#0000ff"><font size="2">select <span class="893345117-09092008">states</span>.<span class="893345117-09092008">stateabbrev</span>,<span class="893345117-09092008"> st_</span>distance(<span class="893345117-09092008">states</span>.geom,<span class="893345117-09092008">points</span>.geom)/5280<br>from <span class="893345117-09092008">points</span>, <span class="893345117-09092008">states</span></font></font></font></div> <div><font><font><span class="893345117-09092008"></span><font face="Arial"><font color="#0000ff"><font size="2">where <span class="893345117-09092008">points</span>.<span class="893345117-09092008">point_id</span> = <span class="893345117-09092008"><em>your_PA_point_id</em></span></font></font></font></font></font></div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008"></span><span class="893345117-09092008">and stateabbrev in ('OH','MD',''WV','VA')</span></font></font></font></font></font></div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008">order by st_distance(<span class="893345117-09092008">states</span>.geom,<span class="893345117-09092008">points</span>.geom)</span></font></font></font></font></font></div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008"></span></font></font></font></font></font> </div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008"></span></font></font></font></font></font> </div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008">If your point is an xy location then similarly (using example point in Greene County)</span></font></font></font></font></font><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008"></span></font></font></font></font></font></div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008"></span></font></font></font></font></font> </div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008"> <div><font face="Arial"><font color="#0000ff"><font size="2">select <span class="893345117-09092008">states</span>.<span class="893345117-09092008">stateabbrev</span>,<span class="893345117-09092008"> st_</span>distance(<span class="893345117-09092008">states</span>.geom,<span class="893345117-09092008">makepoint(1263197,192349)</span>)/5280<br>from <span class="893345117-09092008">states</span></font></font></font></div> <div><font size="+0"><font size="+0"><span class="893345117-09092008"></span><font face="Arial"><font color="#0000ff"><font size="2">where </font></font></font></font></font><font size="+0"><font size="+0"><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008">stateabbrev in ('OH','MD',''WV','VA')</span></font></font></font></font></font></div> <div><font size="+0"><font size="+0"><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008">order by st_distance(<span class="893345117-09092008">states</span>.geom,<span class="893345117-09092008">makepoint(1263197,192349)</span>)</span></font></font></font></font></font></div> <div><font size="+0"><font size="+0"><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008"></span></font></font></font></font></font> </div></span></font></font></font></font></font></div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008"></span></font></font></font></font></font><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008"></span></font></font></font></font></font> </div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008"></span></font></font></font></font></font> </div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008">If you are talking about the SW corner point on the PA Boundary then you'll need to get the </span></font></font></font></font></font></div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008">point on PA Boundary nearest the SW point (always the first point I believe) on the exteriorring of the envelope of the PA Boundary which would be:</span></font></font></font></font></font></div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008"></span></font></font></font></font></font> </div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008"></span></font></font></font></font></font><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008"></span></font></font></font></font></font> </div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008">select st_line_interpolate_point(st_exteriorring(st_geometryn(geom,1)),st_line_locate_point(st_exteriorring(st_geometryn(geom,1)),st_pointn(st_exteriorring(st_envelope(geom)),1)))<br>from states where stateabbrev = 'PA'</span></font></font></font></font></font></div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008"></span></font></font></font></font></font> </div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008"></span></font></font></font></font></font> </div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008">and use that point in the query(s) above. There are other ways, probably better ways too. </span></font></font></font></font></font></div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008"></span></font></font></font></font></font> </div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008"></span></font></font></font></font></font> </div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008"></span></font></font></font></font></font> </div> <div><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008">-</span></font></font></font></font></font><font><font><font face="Arial"><font color="#0000ff"><font size="2"><span class="893345117-09092008">Eric</span></font></font></font></font></font></div> <div><font face="Arial" color="#0000ff" size="2"></font> </div> <div><font face="Arial" color="#0000ff" size="2"></font> </div> <div><font face="Arial" color="#0000ff" size="2"></font> </div> <div><font face="Arial" color="#0000ff" size="2"></font> </div> <div><font face="Arial" color="#0000ff" size="2"></font><font face="Arial" color="#0000ff" size="2"></font><br><font face="Tahoma" size="2">-----Original Message-----<br><b>From:</b> postgis-users-bounces@postgis.refractions.net [<a href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</a>]<b>On Behalf Of </b>Doug Fischer<br><b>Sent:</b> Tuesday, September 09, 2008 10:55 AM<br><b>To:</b> PostGIS Users Discussion<br><b>Subject:</b> Re: [postgis-users] list of the closest borders<br><br></font></div><font face="Tahoma" size="2"></font> <blockquote dir="ltr" style="MARGIN-RIGHT: 0px">Sorry but I just noticed that I made an error in my post. What I meant to say was the SW corner of PA and in the table the distance to PA should be 0 because the Point is in PA. <div><br></div> <div>Thanks,</div> <div>Doug</div> <div><br> <div> <div>On Sep 9, 2008, at 8:52 AM, Doug Fischer wrote:</div><br class="Apple-interchange-newline"> <blockquote type="cite"><p style="MARGIN: 0px 0px 12px"><font style="FONT: 12px Helvetica" face="Helvetica" size="3">I am new to GIS / PostGIS so please forgive me if this is a simple question.</font></p><p style="MARGIN: 0px 0px 12px"><font style="FONT: 12px Helvetica" face="Helvetica" size="3">Assuming that I have a database populated with all of the proper data, I need to do determine a list of the closest borders from a point including distances.</font></p><p style="MARGIN: 0px 0px 12px"><font style="FONT: 12px Helvetica" face="Helvetica" size="3">ex. <br>If I have a point located in the SE corner of PA, I would like to run a query that will return a list containing something like the following:</font></p> <div style="MIN-HEIGHT: 14px; MARGIN: 0px; FONT: 12px Helvetica"><br></div> <table style="BORDER-RIGHT: #808080 1px solid; PADDING-RIGHT: 0px; BORDER-TOP: #808080 1px solid; PADDING-LEFT: 0px; PADDING-BOTTOM: 2px; BORDER-LEFT: #808080 1px solid; PADDING-TOP: 2px; BORDER-BOTTOM: #808080 1px solid" cellspacing="0" cellpadding="0"> <tbody> <tr> <td style="BORDER-RIGHT: #808080 1px solid; PADDING-RIGHT: 2px; BORDER-TOP: #808080 1px solid; PADDING-LEFT: 2px; PADDING-BOTTOM: 0px; MARGIN: 0px; BORDER-LEFT: #808080 1px solid; WIDTH: 40px; PADDING-TOP: 0px; BORDER-BOTTOM: #808080 1px solid" valign="center"> <div style="MARGIN: 0px; TEXT-ALIGN: center"><font style="FONT: 12px Helvetica" face="Helvetica" size="3"><b>Border</b></font></div></td> <td style="BORDER-RIGHT: #808080 1px solid; PADDING-RIGHT: 2px; BORDER-TOP: #808080 1px solid; PADDING-LEFT: 2px; PADDING-BOTTOM: 0px; MARGIN: 0px; BORDER-LEFT: #808080 1px solid; WIDTH: 98px; PADDING-TOP: 0px; BORDER-BOTTOM: #808080 1px solid" valign="center"> <div style="MARGIN: 0px; TEXT-ALIGN: center"><font style="FONT: 12px Helvetica" face="Helvetica" size="3"><b>Distance in miles</b></font></div></td></tr> <tr> <td style="BORDER-RIGHT: #808080 1px solid; PADDING-RIGHT: 2px; BORDER-TOP: #808080 1px solid; PADDING-LEFT: 2px; PADDING-BOTTOM: 0px; MARGIN: 0px; BORDER-LEFT: #808080 1px solid; WIDTH: 40px; PADDING-TOP: 0px; BORDER-BOTTOM: #808080 1px solid" valign="center"> <div style="MARGIN: 0px"><font style="FONT: 12px Helvetica" face="Helvetica" size="3">PA</font></div></td> <td style="BORDER-RIGHT: #808080 1px solid; PADDING-RIGHT: 2px; BORDER-TOP: #808080 1px solid; PADDING-LEFT: 2px; PADDING-BOTTOM: 0px; MARGIN: 0px; BORDER-LEFT: #808080 1px solid; WIDTH: 98px; PADDING-TOP: 0px; BORDER-BOTTOM: #808080 1px solid" valign="center"> <div style="MARGIN: 0px"><font style="FONT: 12px Helvetica" face="Helvetica" size="3">A</font></div></td></tr> <tr> <td style="BORDER-RIGHT: #808080 1px solid; PADDING-RIGHT: 2px; BORDER-TOP: #808080 1px solid; PADDING-LEFT: 2px; PADDING-BOTTOM: 0px; MARGIN: 0px; BORDER-LEFT: #808080 1px solid; WIDTH: 40px; PADDING-TOP: 0px; BORDER-BOTTOM: #808080 1px solid" valign="center"> <div style="MARGIN: 0px"><font style="FONT: 12px Helvetica" face="Helvetica" size="3">OH</font></div></td> <td style="BORDER-RIGHT: #808080 1px solid; PADDING-RIGHT: 2px; BORDER-TOP: #808080 1px solid; PADDING-LEFT: 2px; PADDING-BOTTOM: 0px; MARGIN: 0px; BORDER-LEFT: #808080 1px solid; WIDTH: 98px; PADDING-TOP: 0px; BORDER-BOTTOM: #808080 1px solid" valign="center"> <div style="MARGIN: 0px"><font style="FONT: 12px Helvetica" face="Helvetica" size="3">B</font></div></td></tr> <tr> <td style="BORDER-RIGHT: #808080 1px solid; PADDING-RIGHT: 2px; BORDER-TOP: #808080 1px solid; PADDING-LEFT: 2px; PADDING-BOTTOM: 0px; MARGIN: 0px; BORDER-LEFT: #808080 1px solid; WIDTH: 40px; PADDING-TOP: 0px; BORDER-BOTTOM: #808080 1px solid" valign="center"> <div style="MARGIN: 0px"><font style="FONT: 12px Helvetica" face="Helvetica" size="3">MD</font></div></td> <td style="BORDER-RIGHT: #808080 1px solid; PADDING-RIGHT: 2px; BORDER-TOP: #808080 1px solid; PADDING-LEFT: 2px; PADDING-BOTTOM: 0px; MARGIN: 0px; BORDER-LEFT: #808080 1px solid; WIDTH: 98px; PADDING-TOP: 0px; BORDER-BOTTOM: #808080 1px solid" valign="center"> <div style="MARGIN: 0px"><font style="FONT: 12px Helvetica" face="Helvetica" size="3">C</font></div></td></tr> <tr> <td style="BORDER-RIGHT: #808080 1px solid; PADDING-RIGHT: 2px; BORDER-TOP: #808080 1px solid; PADDING-LEFT: 2px; PADDING-BOTTOM: 0px; MARGIN: 0px; BORDER-LEFT: #808080 1px solid; WIDTH: 40px; PADDING-TOP: 0px; BORDER-BOTTOM: #808080 1px solid" valign="center"> <div style="MARGIN: 0px"><font style="FONT: 12px Helvetica" face="Helvetica" size="3">WV</font></div></td> <td style="BORDER-RIGHT: #808080 1px solid; PADDING-RIGHT: 2px; BORDER-TOP: #808080 1px solid; PADDING-LEFT: 2px; PADDING-BOTTOM: 0px; MARGIN: 0px; BORDER-LEFT: #808080 1px solid; WIDTH: 98px; PADDING-TOP: 0px; BORDER-BOTTOM: #808080 1px solid" valign="center"> <div style="MARGIN: 0px"><font style="FONT: 12px Helvetica" face="Helvetica" size="3">D</font></div></td></tr> <tr> <td style="BORDER-RIGHT: #808080 1px solid; PADDING-RIGHT: 2px; BORDER-TOP: #808080 1px solid; PADDING-LEFT: 2px; PADDING-BOTTOM: 0px; MARGIN: 0px; BORDER-LEFT: #808080 1px solid; WIDTH: 40px; PADDING-TOP: 0px; BORDER-BOTTOM: #808080 1px solid" valign="center"> <div style="MARGIN: 0px"><font style="FONT: 12px Helvetica" face="Helvetica" size="3">VA</font></div></td> <td style="BORDER-RIGHT: #808080 1px solid; PADDING-RIGHT: 2px; BORDER-TOP: #808080 1px solid; PADDING-LEFT: 2px; PADDING-BOTTOM: 0px; MARGIN: 0px; BORDER-LEFT: #808080 1px solid; WIDTH: 98px; PADDING-TOP: 0px; BORDER-BOTTOM: #808080 1px solid" valign="center"> <div style="MARGIN: 0px"><font style="FONT: 12px Helvetica" face="Helvetica" size="3">E</font></div></td></tr></tbody></table> <div style="MIN-HEIGHT: 14px; MARGIN: 0px; FONT: 12px Helvetica"><br></div><p style="MARGIN: 0px 0px 12px"><font style="FONT: 12px Helvetica" face="Helvetica" size="3">Is there a function in PostGIS that can do this type of thing? Does anyone have an example?</font></p> <div style="MARGIN: 0px"><font style="FONT: 12px Helvetica" face="Helvetica" size="3">Thank you very much. </font></div> <div style="MARGIN: 0px">_______________________________________________</div> <div style="MARGIN: 0px">postgis-users mailing list</div> <div style="MARGIN: 0px"><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a></div> <div style="MARGIN: 0px"><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a></div></blockquote></div><br></div></blockquote><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">_______________________________________________</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">postgis-users mailing list</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a></div> </blockquote></div><br></div></body></html>