0

I have this sample of a tree with the table 't_cheminement' : My tree 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'.

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.