<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:st1="urn:schemas-microsoft-com:office:smarttags" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]--><o:SmartTagType
namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="country-region"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PlaceType"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PlaceName"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="place"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PersonName"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:blue;
text-decoration:underline;}
pre
{margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.EmailStyle23
{mso-style-type:personal-reply;
font-family:Arial;
color:navy;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=blue>
<div class=Section1>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Here's another option for calculating nearby
regions:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>1. Buffer out the polygon you're
interested in to make a bigger polygon<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>2. Find all the polygons that overlap with
the buffered polygon (ignoring the original polygon- everything is near
itself!)<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>3. Order the results by the distance
between the centroids of the polygons<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>I do this in Java/Hibernate with the
following SQL:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>First, calculate the buffered polygon and
the centroid of that polygon:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>SELECT<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> ST_AsText(ST_Buffer(ST_Transform(ST_Buffer(ST_Transform(polygon_data,
2163), 100), 4326), 0)) AS buffered_poly,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> ST_Centroid(polygon_data)
AS centroid<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>FROM ...<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>where 2136 is an SRID in meters, 4326 is
the SRID that our polygons are stored in, and 100 is the number of meters to
buffer by.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Second, find the polygons that overlap
with that buffered polygon:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>SELECT <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> [stuff],<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> st_distance_sphere(st_centroid(poly_table.polygon),
ST_GeomFromText(:centroid, 4326)) AS dist<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>FROM<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> [tables]<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>WHERE ST_Intersects(<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> ST_GeomFromText(:bufferedPoly,
4326),<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> poly_table.polygon<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> )<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> AND
[other constraints]<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>ORDER BY<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> dist
ASC<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>where poly_table.polygon are the polygons
we're looking for, :centroid is the centroid we got from the previous query,
:bufferedPoly is the buffered polygon we got from the previous query, and 4326
is again the SRID that our polygons are stored in. Note that [other
constraints] should include a clause to ignore the source polygon.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>These two SQL statements can be combined
into a single SQL statement, but in our case it was more performant to
calculate the buffered polygon one time and use it multiple times.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<div>
<div class=MsoNormal align=center style='text-align:center'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'>
<hr size=2 width="100%" align=center tabindex=-1>
</span></font></div>
<p class=MsoNormal><b><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>From:</span></font></b><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'> postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b><span
style='font-weight:bold'>On Behalf Of </span></b>Paragon Corporation<br>
<b><span style='font-weight:bold'>Sent:</span></b> Monday, June 08, 2009 9:31
PM<br>
<b><span style='font-weight:bold'>To:</span></b> '<st1:PersonName w:st="on">PostGIS
Users Discussion</st1:PersonName>'<br>
<b><span style='font-weight:bold'>Subject:</span></b> RE: [postgis-users]
nearest polygon</span></font><o:p></o:p></p>
</div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:blue'>Charles,</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:blue'>If you just need the 1 nearest neighbor
for each result. Use distinct ON as described here <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></font><o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
<pre><span class=syntax8><span id="ctl00_cphMain_BodyContent"><font size=2
color=blue face="Courier New"><span style='font-size:10.0pt;color:blue'>SELECT DISTINCT ON</span></font></span><span
class=syntax0><font color=blue><span style='color:blue'>(g1.gid) g1.gid </span></font></span><span
class=syntax8><font color=blue><span style='color:blue'>As</span></font></span><span
class=syntax0><font color=blue><span style='color:blue'> gref_gid, g1.description </span></font></span><span
class=syntax8><font color=blue><span style='color:blue'>As</span></font></span><span
class=syntax0><font color=blue><span style='color:blue'> gref_description, g2.gid </span></font></span><span
class=syntax8><font color=blue><span style='color:blue'>As</span></font></span><span
class=syntax0><font color=blue><span style='color:blue'> gnn_gid, <o:p></o:p></span></font></span></pre><pre><span
class=syntax0><font size=2 color=blue face="Courier New"><span
style='font-size:10.0pt;color:blue'> g2.description </span></font></span><span
class=syntax8><font color=blue><span style='color:blue'>As</span></font></span><span
class=syntax0><font color=blue><span style='color:blue'> gnn_description <o:p></o:p></span></font></span></pre><pre><span
class=syntax0><font size=2 color=blue face="Courier New"><span
style='font-size:10.0pt;color:blue'> </span></font></span><span
class=syntax8><font color=blue><span style='color:blue'>FROM</span></font></span><span
class=syntax0><font color=blue><span style='color:blue'> sometable </span></font></span><span
class=syntax8><font color=blue><span style='color:blue'>As</span></font></span><span
class=syntax0><font color=blue><span style='color:blue'> g1, sometable </span></font></span><span
class=syntax8><font color=blue><span style='color:blue'>As</span></font></span><span
class=syntax0><font color=blue><span style='color:blue'> g2 <o:p></o:p></span></font></span></pre><pre><span
class=syntax0><font size=2 color=blue face="Courier New"><span
style='font-size:10.0pt;color:blue'> </span></font></span><span
class=syntax8><font color=blue><span style='color:blue'>WHERE</span></font></span><span
class=syntax0><font color=blue><span style='color:blue'> g1.gid </span></font></span><span
class=syntax18><font color=blue><span style='color:blue'><></span></font></span><span
class=syntax0><font color=blue><span style='color:blue'> g2.gid </span></font></span><span
class=syntax8><font color=blue><span style='color:blue'>AND</span></font></span><span
class=syntax0><font color=blue><span style='color:blue'> </span></font></span><span
class=syntax6><font color=blue><span style='color:blue'>ST_DWithin</span></font></span><span
class=syntax0><font color=blue><span style='color:blue'>(g1.the_geom, g2.the_geom, </span></font></span><span
class=syntax5><font color=blue><span style='color:blue'>300</span></font></span><span
class=syntax0><font color=blue><span style='color:blue'>) <o:p></o:p></span></font></span></pre><pre><span
class=syntax0><font size=2 color=blue face="Courier New"><span
style='font-size:10.0pt;color:blue'> </span></font></span><span
class=syntax8><font color=blue><span style='color:blue'>ORDER</span></font></span><span
class=syntax0><font color=blue><span style='color:blue'> </span></font></span><span
class=syntax8><font color=blue><span style='color:blue'>BY</span></font></span><span
class=syntax0><font color=blue><span style='color:blue'> </span></font></span><span
class=syntax6><font color=blue><span style='color:blue'>g1.gid, ST_Distance</span></font></span><span
class=syntax0><font color=blue><span style='color:blue'>(g1.the_geom,g2.the_geom) </span></font></span><font
color=blue><span style='color:blue'><o:p></o:p></span></font></pre></span>
<p class=MsoNormal><font size=2 color=blue face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:blue'>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 <st1:country-region
w:st="on"><st1:place w:st="on">US</st1:place></st1:country-region>).</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:blue'>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><o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:blue'>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 <st1:place w:st="on"><st1:PlaceType w:st="on">non-point</st1:PlaceType>
<st1:PlaceName w:st="on">geomtries</st1:PlaceName></st1:place> quite
accurately. We are working on fixing this and still maintaining
performance and also updating it to use the new PostGIS functions.</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:blue'>Leo</span></font><o:p></o:p></p>
<div class=MsoNormal align=center style='text-align:center'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'>
<hr size=2 width="100%" align=center tabIndex=-1>
</span></font></div>
<p class=MsoNormal style='margin-bottom:12.0pt'><b><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma;font-weight:bold'>From:</span></font></b><font
size=2 face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b><span
style='font-weight:bold'>On Behalf Of </span></b>charles.blankenship@gmail.com<br>
<b><span style='font-weight:bold'>Sent:</span></b> Monday, June 08, 2009 9:02
PM<br>
<b><span style='font-weight:bold'>To:</span></b>
postgis-users@postgis.refractions.net<br>
<b><span style='font-weight:bold'>Subject:</span></b> [postgis-users] nearest
polygon</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>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 <st1:country-region w:st="on"><st1:place
w:st="on">US</st1:place></st1:country-region> 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<o:p></o:p></span></font></p>
</div>
</body>
</html>