3

I have the following sql code that intersects each linestring with a polygon. The problem is that when the linestring is wholly covered in the polygon the descriptor attribute of the polygon is put into the ra column instead of the descriptor column. When the linestrings are intersected it is correctly performed. How can I get the descriptor attribute to not be switched around.

Current Output
lic_li_no orig_licli ra descriptor
xxxxx xxxxx LSD-10 Sec 12 NULL
Desired Output
lic_li_no orig_licli ra descriptor
xxxxx xxxxx NULL LSD-10 Sec 12
WITH split_lines AS (
    SELECT
  p.lic_li_no, p.orig_licli,l.ra,
  l.descriptor,
  (ST_Dump(ST_Intersection(p.geom, l.geom))).geom AS geom
    FROM
  public.linestring p, public.grid_concave_hull l
    WHERE
  ST_Intersects(p.geom, l.geom) AND NOT ST_CoveredBy(p.geom, l.geom)
    UNION ALL
    SELECT
  p.lic_li_no, p.orig_licli,
  l.descriptor, l.ra,
  p.geom AS geom
    FROM
  public.linestring p, public.grid_concave_hull l
    WHERE
  ST_CoveredBy(p.geom, l.geom)
)
SELECT * FROM split_lines;

enter image description here

1 Answer 1

4

In the first part of union the order of attributes is:

p.lic_li_no, p.orig_licli, l.ra, l.descriptor

In the second part order is reversed:

p.lic_li_no, p.orig_licli, l.descriptor, l.ra

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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