<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6001.18226" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=594192304-09062009>Charles,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=594192304-09062009></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=594192304-09062009>If you just need the 1 nearest neighbor for each
result. Use distinct ON as described here <SPAN
class=594192304-09062009><A
href="http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor">http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor</A></SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=594192304-09062009></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=594192304-09062009><SPAN id=ctl00_cphMain_BodyContent><PRE><SPAN class=syntax0><SPAN class=syntax8>SELECT DISTINCT ON</SPAN>(g1.gid) g1.gid <SPAN class=syntax8>As</SPAN> gref_gid, g1.description <SPAN class=syntax8>As</SPAN> gref_description, g2.gid <SPAN class=syntax8>As</SPAN> gnn_gid,
g2.description <SPAN class=syntax8>As</SPAN> gnn_description
<SPAN class=syntax8>FROM</SPAN> sometable <SPAN class=syntax8>As</SPAN> g1, sometable <SPAN class=syntax8>As</SPAN> g2
<SPAN class=syntax8>WHERE</SPAN> g1.gid <SPAN class=syntax18><</SPAN><SPAN class=syntax18>></SPAN> g2.gid <SPAN class=syntax8>AND</SPAN> <SPAN class=syntax6>ST_DWithin</SPAN>(g1.the_geom, g2.the_geom, <SPAN class=syntax5>300</SPAN>)
<SPAN class=syntax8>ORDER</SPAN> <SPAN class=syntax8>BY</SPAN> <SPAN class=syntax6>g1.gid, ST_Distance</SPAN>(g1.the_geom,g2.the_geom) </SPAN></PRE></SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=594192304-09062009>For the 300 you want to change that to the max distance
you expect the nearest neighbor for any record will be. If you are using
census data, you wil also want to transform that from long lat 4269 to some
other projection like 2163 (National Atlas US).</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=594192304-09062009></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=594192304-09062009>You can also use the array approach we describe in the
above article to have nearest neighbors in columns instead of as separate rows
(a sort of distance cross tab if you will).</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=594192304-09062009></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=594192304-09062009>The generic solution we described below is more for
returning multiple near neighbors per record, and there is a bit of a bug in it
that it doesn't handle non-point geomtries quite accurately. We are
working on fixing this and still maintaining performance and also updating it to
use the new PostGIS functions.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=594192304-09062009></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=594192304-09062009></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=594192304-09062009>Leo</SPAN></FONT><BR></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>charles.blankenship@gmail.com<BR><B>Sent:</B> Monday, June 08, 2009 9:02
PM<BR><B>To:</B> postgis-users@postgis.refractions.net<BR><B>Subject:</B>
[postgis-users] nearest polygon<BR></FONT><BR></DIV>
<DIV></DIV>Hi,<BR><BR>I'm very new to GIS and SQL so this is a naive
question.<BR><BR>I have some boundary files from the US census containing school
districts and urban areas.<BR><BR>I would like to find the nearest, or if
possible several nearest, urban areas for each district.<BR><BR>I know this type
of problem can get really complicated, but I need a fairly quick and easy
solution if one exists. I could limit this to centroid to centroid
distances if that makes a big difference.<BR><BR><BR>I found the following link
which seems like it might do what I need.<BR><A
href="http://www.bostongis.com/?content_name=postgis_nearest_neighbor_generic#130">http://www.bostongis.com/?content_name=postgis_nearest_neighbor_generic#130</A><BR><BR><BR>Can
anyone tell me if the solution above is the best way to go about this? Or,
if there's a simpler "quick and easy" way, that would be helpful as
well.<BR><BR><BR>Thanks,<BR>Charles<BR></BODY></HTML>