2

I have a a large line network (white lines) with 33,000 features that I would like to intersect with each polygon grid cell (black lines) and join the 'description' attribute of the polygon cells to each split line segment.

CREATE INDEX lsd_poly_geom_idx ON public.lsd_poly USING GIST (geom);
CREATE INDEX pipelines_geom_idx ON public.pipelines USING GIST (geom);

CREATE TABLE split_lines AS -- table of all lines split by polygon cells
    SELECT p.descriptor, l.*, (ST_Dump(ST_intersection(p.geom, l.geom))).geom as split
    FROM   public.pipelines AS l,
           public.lsd_poly AS p
    ;

The resulting table split_lines when imported into qgis doesn't have split lines. However, I see two other generated layers not shown in the database: split_lines.geom and split_lines.split. The *.split feature layer is the correctly split one.

How do I save the result into the split_lines table?

enter image description here

1
  • 1
    I tihnk you aready have. But your output table has multiple geometries: the one from l.geom (since you selected l.*) and another geometry called split. Feb 28, 2023 at 22:05

1 Answer 1

1

Using l.* and split is bringing in both the original geometry of the lines (geom) as well as the split version (split).

What information do you need to retain from the lines?
If just the geometry after split then this is what will work;
(if other fields are required then specify them instead of using l.*.

CREATE TABLE split_lines AS -- table of all lines split by polygon cells
SELECT p.descriptor, (ST_Dump(ST_intersection(p.geom, l.geom))).geom as geom
FROM   public.pipelines AS l,
       public.lsd_poly AS p;
3
  • Is there a performance fix for this operation? I have 315,000 line features across 4,829,485 polygons (400mx400m). Would converting the polygon grid to lines improve the performance? I just need to have the lines cut so that I can place a midpoint point on each line segment within the bounds of the polygon. I only need one attribute from the line and one from the polygon.
    – MJM
    Mar 1, 2023 at 18:31
  • Have you added spatial indexes, they will massively increase performance? : postgis.net/workshops/postgis-intro/indexing.html Getting middle point of a line can be done with ST_LineInterpolatePoint(geom, 0.5); run that on the geometry generated by the split.
    – Cushen
    Mar 1, 2023 at 22:04
  • I have continued this task in gis.stackexchange.com/questions/453204/…
    – MJM
    Mar 1, 2023 at 23:35

Your Answer

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

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