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.

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
;

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
;

I am trying to improve the performance of a line/polygon intersection query which produces 70 attributes with an improved version that doesn't perform line/polygon intersections when lines are 'CoveredBy' the polygon grid cells (yellow line segments).

QUESTION: The improved query ends up sending back 800 attribute rows and has no SRS. This query will be performed on a 4,829,485 polygon table and 315,511 line 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 spatial join with point in the polygon.

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
;

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.

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
;