[postgis-users] Fwd: Hello everyone, That is my first message.I am using PostGIS not so long and so I ask you to help me.

[Nicolas Ribot]

2011/5/21 Александр Дымчишин <dymchishin at gmail.com>:
> I'm trying to solve the transport problem and make the visualization using
> Geotools. In attached pic that is what I mean. My car is between V3 and V4.
> Its position can I get with a query like "SELECT
> ST_Line_Interpolate_Point(linestring1, 0.6);" Ok, I got it's position, but
> how can I receive nearest real vertices v3 and v4? I hope, there is a method
> with only one query. Thanks.
>

Hi Alexander,

You can use st_pointN() to extract vertices from a linestring, and
generate_series() to iterate through linestring vertices:
something like:

select
	st_distance(point,st_pointN(geom, generate_series(1,
st_numpoints(geom)))) as dist,
	generate_series(1, st_numpoints(geom)) as vertexId
from (
	select st_line_interpolate_point(geom, 0.6) as point, geom
	from (
		select 'LINESTRING (92 73, 146 112, 100 203, 166 240, 270
179)'::geometry as geom
	) as foo
) as bar order by dist;

Nicolas
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Screen shot 2011-05-22 at 12.35.16 AM.png
Type: image/png
Size: 9628 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110522/2f6260ee/attachment.png>