<!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><FONT color=#0000ff size=2 face=Arial><SPAN class=096321521-20102009>That's
not what I'm trying to do. Think geocoding a point to the appropriate side
of a street that curves all over the map like a mountain
road.</SPAN></FONT></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:14
PM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> Re:
[postgis-users] st_distance accuracy?<BR><BR></FONT></DIV>
<DIV align=left>To find the last two vertexes why not use:</DIV>
<DIV align=left> </DIV>
<DIV align=left>p1:=st_pointn(m_geom,<SPAN
style="FONT-FAMILY: Courier New"><STRONG>ST_NPoints</STRONG>(</SPAN>m_geom<CODE>)-1</CODE>);<BR>p2:=st_pointn(m_geom,<SPAN
style="FONT-FAMILY: Courier New"><STRONG>ST_NPoints</STRONG>(</SPAN>m_geom<CODE>)</CODE>);</DIV>
<P><SPAN><STRONG>/Nicklas</STRONG><SPAN class=096321521-20102009><FONT
color=#0000ff size=2 face=Arial> </FONT></SPAN></SPAN></P>
<DIV> </DIV>
<DIV> </DIV>
<DIV align=left><BR><BR>2009-10-20 nicklas.aven@jordogskog.no
wrote:<BR><BR>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>
<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>>
>postgis.refractions.net/mailman/listinfo/postgis-users<BR>>
><BR>> ></DIV></BLOCKQUOTE></BODY></HTML>