Use of PostGIS ST_Intersection & ST_CoveredBy for line x polygon intersection

Question

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
 , CASE
   WHEN ST_CoveredBy(p.geom, l.geom)
   THEN p.geom
   ELSE
     ST_Intersection(p.geom,l.geom)
     END As geom
 FROM public.pipeline_test As p,
      public.lsd_test As l
;

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:

SELECT
  p.lic_li_no, l.descriptor,
  CASE
    WHEN ST_CoveredBy(p.geom, l.geom) THEN
      p.geom
    ELSE
      ST_Intersection(p.geom,l.geom)
  END AS geom
FROM
  public.pipeline_test AS p
  JOIN
  public.lsd_test AS l
    ON ST_Crosses(p.geom, l.geom)
;