I am trying to improve the performance of a line/polygon intersection query where all intersected lines and 'CoveredBy' lines are produced in a table. Question: The improved version produces many attributes, 800 attributes vs 70 in the first version, and the resulting table has no SRS.

This query will be performed on a 4,829,485 polygon table and a 315,511 line segment table. A subset of the data set illustrates a small area and cases where line segments don't intersect with the polygon.

I'm not interested in those cases when the line 'touches' the polygon, so 'CoveredBy' seems to be the correct choice for my case. I require the lines to be segments within the polygon because I will then create a midpoint of each line segment in the polygon for a point-to-point spatial join in the polygon.

The SRS is 4326, but NAD83(CSRS) / Alberta 10-TM (Resource) EPSG 3400 is the projected coordinate system for the region.

First Attempt:

CREATE TABLE split_lines AS -- table of all lines split by polygon cells
SELECT p.descriptor, l.lic_li_no, (ST_Dump(ST_intersection(p.geom, l.geom))).geom as geom
FROM   public.pipeline_test AS l,
       public.lsd_test AS p

Improved Attempt:

CREATE INDEX lsd_test_geom_idx ON public.lsd_test USING GIST (geom);
CREATE INDEX pipeline_test_geom_idx ON public.pipeline_test USING GIST (geom);

CREATE TABLE split_lines AS
SELECT p.lic_li_no, l.descriptor
   WHEN ST_CoveredBy(p.geom, l.geom)
   THEN p.geom
     END As geom
 FROM public.pipeline_test As p,
      public.lsd_test As l

enter image description here


1 Answer 1


You are CROSS JOIN'ing two relations, resulting in the cartesian product of rows in both tables.

Following best practices, prefer an explicit JOIN notation over the implicit cross join notation (table_1, table_2) - and add conditions to your query!

You want to limit both calculations and result set to those Lines that actually cross a Polygon:

  p.lic_li_no, l.descriptor,
    WHEN ST_CoveredBy(p.geom, l.geom) THEN
  END AS geom
  public.pipeline_test AS p
  public.lsd_test AS l
    ON ST_Crosses(p.geom, l.geom)
  • I also want the lines that don't cross a polygon as well.
    – MJM
    Mar 2, 2023 at 18: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.