There are two tables with polygon features in my PostgreSQL database. I want to create linestring features where the boundries of the polygons are not intersecting.
CREATE TABLE schema.poly_a
(
gid SERIAL NOT NULL,
geom geometry(polygon, SRID),
CONSTRAINT poly_a_pkey PRIMARY KEY (gid)
);
CREATE TABLE schema.poly_b
(
gid SERIAL NOT NULL,
geom geometry(polygon, SRID),
CONSTRAINT poly_b_pkey PRIMARY KEY (gid)
);
I've tried the following SQL but it's not working. There are no features in the view.
CREATE VIEW line_difference AS
SELECT
row_number() over() AS gid,
g.*
FROM
(SELECT
ST_Difference(ST_Exteriorring(poly_b.geom), poly_a.geom)::geometry(linestring, SRID) AS geom
FROM schema.poly_a, schema.poly_b)
AS g;
A geometry collection instead of linestrings will also do the job.
EDIT #1:
I've tried the SQL query postet by chill. Unfortunately it's not possible to load the layer in QGIS ('There is no entry in geometry_columns!'). DB Manager says there are two linestrings, one multilinestring and one geometrycollection. The preview in the DB Manager is not available.
EDIT #2:
I've already solved it. Just the boundaries for 'ST_Intersects(poly_a.geom, poly_b.geom) IS FALSE' are missing.
CREATE VIEW line_difference AS
SELECT
row_number() over() AS gid,
g.geom
FROM
(SELECT
(ST_Dump(COALESCE(ST_Difference(ST_Exteriorring(poly_a.geom), ST_Exteriorring(poly_b.geom))))).geom::geometry(linestring, your_SRID) AS geom
FROM schema.poly_a, schema.poly_b
WHERE ST_Intersects(poly_a.geom, poly_b.geom))
AS g;