<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE>Message</TITLE>
<META name=GENERATOR content="MSHTML 8.00.6001.18828"></META>
<META name=SKYPE_FRAMEID content=HBROAMAKDT></META>
<META id=skype_v3_tb_marker_id name=SKYPE_PARSING_HAS_FINISHED
content=metacontent></META></HEAD>
<BODY>
<DIV><SPAN class=187310921-20102009><FONT color=#0000ff size=2 face=Arial>I'm
walking each vertex in the line segment to find the linear distance along the
line that matches with the percentage along the line .</FONT></SPAN></DIV>
<DIV><SPAN class=187310921-20102009><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=187310921-20102009><FONT color=#0000ff size=2 face=Arial>Then
extracting the last point index that matches and the subsequent point to get the
vertex that matches the requested percentage.</FONT></SPAN></DIV>
<DIV><SPAN class=187310921-20102009><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=187310921-20102009><FONT color=#0000ff size=2 face=Arial>Then
calculating the delta x/y of the vertex to offset the interpolated point to the
left or right side of the matching vertex by the requested
offset.</FONT></SPAN></DIV>
<DIV><SPAN class=187310921-20102009></SPAN><SPAN class=187310921-20102009><FONT
color=#0000ff size=2 face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=187310921-20102009><FONT color=#0000ff size=2 face=Arial>The
function should perform the same as st_line_interpolate_point(geom, double) but
with the added function of translating the point by the appropriate angle to the
left or right side of the line segment.</FONT></SPAN></DIV>
<BLOCKQUOTE
style="BORDER-LEFT: #0000ff 2px solid; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px"
dir=ltr>
<DIV></DIV>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left><FONT size=2
face=Tahoma>-----Original Message-----<BR><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>nicklas.aven@jordogskog.no<BR><B>Sent:</B> Tuesday, October 20, 2009 2:00
PM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> Re:
[postgis-users] st_distance accuracy?<BR><BR></FONT></DIV>I'm not sure I
understand what you are trying to do. But as I read it you are comparing the
length of the line with the
<DIV align=left>straight distance from the first to one and each of the
following vertexes. </DIV>
<DIV align=left> </DIV>
<DIV align=left>Is that really what you want?</DIV>
<DIV align=left> </DIV>
<DIV align=left>Nothing says that the last vertex is the one most far away
from the first and the only case when that distance is the same as the length
of the line is if the line is straight.<BR></DIV>
<DIV align=left> </DIV>
<DIV align=left>Or am I missunderstanding you?</DIV>
<DIV align=left> </DIV>
<DIV align=left>/Nicklas</DIV>
<DIV align=left> </DIV>
<DIV align=left> </DIV>
<DIV align=left><BR>2009-10-20 Sufficool Stanley wrote:<BR><BR>I am attempting
to get the last vertex index of a line for a given<BR>>measure using the
following:<BR>><BR>>idx := max(np) from
generate_series(1,st_npoints(m_geom)) as np
where<BR>>st_distance(st_pointn(m_geom,1), st_pointn(m_geom,np)) <=
m_distance;<BR>><BR>>However, some of my queries are returning the last
point on the<BR>>linestring when "m_distance" is less than the st_length()
of the given<BR>>geometry.<BR>><BR>>Is this an issue with
st_distance? I don't get the same issue when using<BR>>Lat/Lon and
st_distance_spheroid().<BR>><BR>>select postgis_version()<BR>>1.4
USE_GEOS=1 USE_PROJ=1 USE_STATS=1<BR>><BR>>select
version()<BR>>PostgreSQL 8.4.0, compiled by Visual C++ build 1400,
32-bit<BR>><BR>><BR>>FULL FUNCTION
TEXT<BR>>------------------------<BR>>CREATE OR REPLACE FUNCTION
st_line_interpolate_point(m_geom geometry,<BR>>m_percent double precision,
m_offset double precision)<BR>> RETURNS geometry
AS<BR>>$BODY$<BR>><BR>>declare idx int;<BR>>declare m_distance
double precision;<BR>>declare p1 geometry;<BR>>declare p2
geometry;<BR>><BR>>BEGIN<BR>><BR>>IF (m_distance > 1 or
m_distance < 0) THEN<BR>> raise exception 'Percentage must be between 0
and 1';<BR>>END IF;<BR>><BR>>/* Convert percentage to actual distance
measure */<BR>>m_distance := st_length(m_geom) *
m_percent;<BR>><BR>>/* Get the last point number on the line that is
less than the requested<BR>>distance */<BR>><BR>>/* For Feet
(NAD1983) */<BR>>idx := max(np) from generate_series(1,st_npoints(m_geom))
as np where<BR>>st_distance(st_pointn(m_geom,1), st_pointn(m_geom,np))
<= m_distance;<BR>><BR>>/* For Lat/Long (TIGER Lines) */<BR>>--idx
:= max(np) from generate_series(1,st_npoints(m_geom)) as np
where<BR>>st_distance_sphere(st_pointn(m_geom,1), st_pointn(m_geom,np))
<<BR>>m_distance;<BR>><BR>>/* Past the end of the line segment
return exception (this should never<BR>>happen) */<BR>>if idx =
st_npoints(m_geom) then <BR>> raise exception
'st_line_interpolate_point(geom, dbl, dbl): Attempt to<BR>>seek beyond end
of line.';<BR>>end if;<BR>><BR>>/* Grab the 2 points of the matching
line segment
*/<BR>>p1:=st_pointn(m_geom,idx);<BR>>p2:=st_pointn(m_geom,idx +
1);<BR>><BR>>/* get the delta of the line segment and offset the
interpolated point<BR>>*/<BR>>/* Rotate deltas by 90 degrees (invert
dx/dy ) */<BR>>/* spheroid projections will need the offset to vary based
on lat/lon */<BR>>return st_translate(<BR>>
line_interpolate_point(m_geom, m_percent), <BR>> ((st_y(p2) - st_y(p1)) /
st_distance(p1,p2)) * m_offset,<BR>> ((st_x(p2) - st_x(p1)) /
st_distance(p1,p2)) *
(-m_offset)<BR>>);<BR>><BR>>END;<BR>><BR>>$BODY$<BR>>
LANGUAGE 'plpgsql' VOLATILE<BR>> COST 100;<BR>>ALTER FUNCTION
st_line_interpolate_point(geometry, double precision,<BR>>double precision)
OWNER TO
postgres;<BR>><BR>>_______________________________________________<BR>>postgis-users
mailing
list<BR>>postgis-users@postgis.refractions.net<BR>>http://postgis.refractions.net/mailman/listinfo/postgis-users<BR>><BR>></DIV></BLOCKQUOTE></BODY></HTML>