[GIS] Locate start point, end point intersecting line in a polyline

linepostgissql

I edit my question to be more clear. the last answers were not helpful for me, maybe I did not ask well my question.

I have a file of nodes and a file of polylines. In my file polylines, I have 2 fields, "NODEA" and "NODEB". In my node file, I have a unique "identifier" field for each node. I want to retrieve with a small line of code the identifier of the node at each end of my polyline.

I try this query but no results.

select id, 
       (select id_arret 
        from noeuds n 
        where st_within(st_startpoint(p.geometry), st_buffer(n.geometry, 1))) n_deb, 
       (select id_arret 
        from noeuds n 
        where st_within(st_endpoint(p.geometry), st_buffer(n.geometry, 1))) n_fin
from polylignes p;

"My purpose is to get for each line of the multiline the start and the end node of a line and to fill in 2 colomuns of my multiline table the good node."

Best Answer

You need to get a geometry dump ('split' mulit geometries into their single parts) of your polyline first. Use ST_StartPoint/ST_EndPoint in conjunction with ST_Dump. A simple example:

SELECT ST_StartPoint(dmp.geom) AS sp,
       ST_EndPoint(dmp.geom) AS ep
FROM   <your_polyline_table>,
       LATERAL ST_Dump(<your_geometry_column>) AS dmp
;

EDIT (as above answer is useless after clarifying the question):

You cannot use ST_StartPoint/ST_EndPoint on MULTILINESTRINGs, these functions only work on LINESTRING geometries and that is supposedly why your query has no effect.
Getting the start/end point of a MULTILINESTRING unfortunately is no trivial task and a solution or workaround strongly depends on the actual topology of your MULTILINESTRINGs.

There is the possibility that your MULTILINESTRINGs can safely be convertet into LINESTRINGs wich would make your task way easier. Try the steps mentioned in this post and update your question with the result (e.g. if your table has actual MULTI geometries or not). Or, in case your geometries can be converted to LINESTRINGs, do that and run your query on these and see if you get any useful results.

Related Question