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.
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 ;
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 ;