<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.18812"><!-- converted from rtf -->
<STYLE>.EmailQuote {
BORDER-LEFT: #800000 2px solid; PADDING-LEFT: 4pt; MARGIN-LEFT: 1pt
}
</STYLE>
</HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial>Fred,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial>Still kind of unclear to me what you are trying to
do.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial>It sounds like you either want to use ST_DWithin or one of the
linear referencing functions or maybe both. Here are some links that might
be useful.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial><A
href="http://postgis.refractions.net/documentation/manual-1.4/ST_DWithin.html">http://postgis.refractions.net/documentation/manual-1.4/ST_DWithin.html</A>
(ST_DWithin works with lines, polygons, points etc. and returns if the minimum
distance is within the distance specified)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial><A
href="http://postgis.refractions.net/documentation/manual-1.4/ST_Line_Interpolate_Point.html">http://postgis.refractions.net/documentation/manual-1.4/ST_Line_Interpolate_Point.html</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial>Here is an interesting use of the interpolate to find closest
point on a line</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial><A
href="http://selectoid.wordpress.com/2009/02/13/reprojecting-snapping-point-geometries-onto-a-given-line-geometry-using-postgis/">http://selectoid.wordpress.com/2009/02/13/reprojecting-snapping-point-geometries-onto-a-given-line-geometry-using-postgis/</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial>combo use of interpolate and dwithin</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial><A
href="http://blog.cleverelephant.ca/2008/04/snapping-points-in-postgis.html">http://blog.cleverelephant.ca/2008/04/snapping-points-in-postgis.html</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial>Hope that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial>Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=562264507-30072009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Thurber, Fred<BR><B>Sent:</B> Wednesday, July 29, 2009 9:15 AM<BR><B>To:</B>
postgis-users@postgis.refractions.net<BR><B>Subject:</B> [postgis-users] Closest
points to positions along a line<BR></FONT><BR></DIV>
<DIV></DIV><FONT size=2 face="Arial, sans-serif">
<DIV>I am a newbie to PostGIS and have a spatial question.</DIV>
<DIV><FONT size=3 face="Times New Roman, serif"></FONT> </DIV>
<DIV>We have a geographical straight line with equidistant positions along the
line. We need to find the closest point for each position on the
line.</DIV>
<DIV><FONT size=3 face="Times New Roman, serif"></FONT> </DIV>
<DIV>I suppose that the obvious way to do this is to solve the closest point
problem for each position on the line, but I cannot find an easy way to do this
in PostGIS. I could “ORDER BY distance” and limit the results to 1. To
prevent a full table search I suppose I could limit the search to an overlapping
BOX2D (if there are no matches, I could use the expand function on the box until
I find a match). Is there a better way to do this?</DIV>
<DIV><FONT face="Courier New, monospace"></FONT> </DIV>
<DIV>Is there a way to solve this problem in one step? I am tempted to
draw a box around the whole line and return all the points in a subquery then do
the distance queries on that result, but I am not sure I can do such a query in
SQL.</DIV>
<DIV><FONT face="Courier New, monospace"></FONT> </DIV>
<DIV><FONT
face="Courier New, monospace"></FONT> </DIV></FONT></BODY></HTML>