Re: [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 Александр Дымчишин <dymchis...@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

<<attachment: Screen shot 2011-05-22 at 12.35.16 AM.png>>

_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users