I have a query in my lines and points table.

My lines table includes MULTILINESTRING, MULTILINESTRING Z, LINESTRING geometry type data.

select *
from sde.line h2
left join sde.points b
on st_intersects(st_startpoint(ST_LineMerge((ST_Dump(h2.shape)).geom)), b.shape);

I am using st_dump and st_linemerge because st_startpoint(line2.geometry) is coming null. So, I am converting to linestring.

But this throws exception "[42804] ERROR: argument of JOIN/ON must not return a set"

How can I solve this issue?


There are 2 problems here.
You need to move the set returning function to a subquery instead of in the join condition.
You should also move the dump after the linemerge. Linemerge returns linestring only if the geometry can be merged to linestring or else it returns multilinestring.
In case you have multilinestrings after the linemerge, you should instead just select the first point of the first geometry - I assumed your lines table has an id column. Add any other columns you require into the subquery select.

    (select id, st_pointn(st_geometryn(shape,1),1) as shape from sde.line) h2
left join
    sde.points b
    st_intersects(h2.shape, b.shape);

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.