CODEWITHSUNDEEP
postgresqltriggerspostgisplpgsql
LyessD
LyessD

Reputation: 221

How to use st_Line_Locate_Point() with a MULTILINESTRING convertion in PostGIS?

I am trying to get the index position of a POINT in a MULTILINESTRING.

Here is the whole query I'm stuck with : 

SELECT req.id, (dp).geom, netgeo_point_tech.id, ST_Line_Locate_Point(st_lineMerge(geom_cable), (dp).geom)
FROM (SELECT id, ST_DumpPoints(geom) as dp, geom as geom_cable FROM netgeo_cable_test ) as req
JOIN netgeo_point_tech ON ST_dwithin(netgeo_point_tech.geom, (dp).geom, 1) 
ORDER BY req.id, (dp).path [ 1] ASC

The error I get is : line_locate_point : 1st arg isnt a line.

The error is due to the return of st_lineMerge() function that is returning LINESTRING but also MULTILINESTRING. I don't get this. st_lineMerge() is supposed to return only LINESTRING.ST_LineMerge()

When I jsut try a simple query like this : 

select st_astext(st_linemerge(geom)) from netgeo_cable_test

The output is :

img)

I want to learn from this, so, if possible, explain to me what I'm doing wrong here, or if my approach is lacking insight.

Upvotes: 0

Views: 1762

Answers (1)

τεκ
τεκ

Reputation: 3114

Thanks to @JGH for the suggestion to use ST_Dump I came up with this function:

create or replace function MultiLineLocatePoint(line geometry, point geometry) returns numeric as $$
select (base + extra) / ST_Length(line)
from (
    select 
        sum(ST_Length(l.geom)) over (order by l.path) - ST_Length(l.geom) base,
        ST_LineLocatePoint(l.geom, point) * ST_Length(l.geom) extra,
        ST_Distance(l.geom, point) dist
    from ST_Dump(line) l
) points
order by dist
limit 1;
$$ language SQL;

Upvotes: 1

Related Questions