<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.6000.16711" name=GENERATOR></HEAD>
<BODY
style="WORD-WRAP: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space">
<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
[mailto:postgis-users-bounces@postgis.refractions.net]<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></DIV></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></BODY></HTML>