I try to remove the overlap of line geometries in one table based on a class attribute. My table consists of two columns containing line geometries and a number "class" from 1 to 3.
CREATE TABLE lines(class INT, geom geometry );
INSERT INTO lines VALUES
(1, 'SRID=4326;LINESTRING(1 1, 1 4)'),
(2, 'SRID=4326;LINESTRING(1 3, 1 6)'),
(3, 'SRID=4326;LINESTRING(1 5, 1 9)');
Now I'd like to extract the part of geometry 2 wich is not intersecting with geometry 1 and the part of geometry 3 wich is not intersecting with geometry 2. I did this already in Python:
class_1 = shp[(shp.Class == 1)]
class_2 = shp[(shp.Class == 2)]
class_3 = shp[(shp.Class == 3)]
part_1 = gpd.overlay(class_2,class_1, how='difference')
part_2 = gpd.overlay(class_3,class_2, how='difference')
part_3 = class_1
but running following code, the query comes to no end:
SELECT
CASE WHEN lines.class = 2 THEN ST_Difference(lines.geom, (SELECT ST_Union(lines.geom) FROM lines WHERE class = 1 ))
WHEN lines.class = 3 THEN ST_Difference(lines.geom, (SELECT ST_Union(lines.geom) FROM lines WHERE class = 2 ))
ELSE lines.geom
END
FROM lines;
How can I write the code most clever?