Query to obtain all ends of a tree

Question

I have this sample of a tree with the table 't_cheminement' : My schema is a table with : letter, startPointNumber(cm_ndcode1), endPointNumber(cm_ndcode2) and the geom.

I have some issues when I try to extract all ends of my tree : C,G,E,I

Some end are well oriented but sometimes not. The blue arrow is the same way as start and point numbers, they aren't inverted.

I try to use ST_Startpoint and ST_Endpoint :

select t1.cm_code, t1.cm_ndcode1, t1.cm_ndcode2 from t_cheminement as t1
inner join t_cheminement as t2 on ST_Equals(ST_StartPoint(t1.geom), ST_StartPoint(t2.geom)) is false 
inner join t_cheminement as t3 on ST_Equals(ST_StartPoint(t1.geom), ST_EndPoint(t3.geom)) is false 
inner join t_cheminement as t4 on ST_Equals(ST_EndPoint(t1.geom), ST_EndPoint(t4.geom)) is true or ST_Equals(ST_EndPoint(t1.geom), 
ST_StartPoint(t4.geom)) is true
UNION
select t1.cm_code, t1.cm_ndcode1, t1.cm_ndcode2 from t_cheminement as t1
inner join t_cheminement as t2 on ST_Equals(ST_EndPoint(t1.geom), ST_StartPoint(t2.geom)) is false 
inner join t_cheminement as t3 on ST_Equals(ST_EndPoint(t1.geom), 
ST_EndPoint(t3.geom)) is false 
inner join t_cheminement as t4 on ST_Equals(ST_StartPoint(t1.geom), 
ST_EndPoint(t4.geom)) is true or ST_Equals(ST_StartPoint(t1.geom), 
ST_StartPoint(t4.geom)) is true

I also tried to use start and endpoint numbers :

select t1.cm_code, t1.cm_ndcode1, t1.cm_ndcode2 from t_cheminement as t1
inner join t_cheminement as t2 on t1.cm_ndcode1 <> t2.cm_ndcode1
inner join t_cheminement as t3 on t1.cm_ndcode1 <> t3.cm_ndcode2
inner join t_cheminement as t4 on t1.cm_ndcode2 = t4.cm_ndcode2 or t1.cm_ndcode2 = t4.cm_ndcode1
UNION
select t1.cm_code, t1.cm_ndcode1, t1.cm_ndcode2 from t_cheminement as t1
inner join t_cheminement as t2 on t1.cm_ndcode2 <> t2.cm_ndcode1
inner join t_cheminement as t3 on t1.cm_ndcode2 <> t3.cm_ndcode2
inner join t_cheminement as t4 on t1.cm_ndcode1 = t4.cm_ndcode2 or t1.cm_ndcode1 = t4.cm_ndcode1

Is it possible to help me to fix my query because it's taking a lot of time to execute and maybe I'm wrong about all 'inner join'.

Answer 1

It is wrong to join tables on field inequality, as you it is causing (almost) a cross product.

Since you know the start and end point ID, your query should just look for IDs that are end point of a segment but not the start point of another segment. You will join the table on ID equality and dismiss the entry that didn't match:

in pseudo code, it would be:

select *
from segments as t1
left join segments  as t2 
on t1.EndNodeID = t2.StartNodeID
where t2.StartNodeIDis null;

Using your field names:

select t1.cm_code, t1.cm_ndcode1, t1.cm_ndcode2 
from t_cheminement as t1
left join t_cheminement as t2 
on t1.cm_ndcode2 = t2.cm_ndcode1
where t2.cm_ndcode1 is null;