How to use ST_Difference to create linestrings from non-intersecting polygon boundaries?

Question

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;

Answer 1

Your logic seems sound, but you have some surplus text in your query which throws an error: "::geometry(linestring, SRID)" is not required. Also note that ST_ExteriorRing() does NOT work on multi-polygons, only polygons (null will be returned from multipolys, which may explain your lack of results). Also if you are using more than one polygon in each table, you will have to add an st_intersects() to the where clause, thus:

--create two pairs of overlapping test polygons
with poly3 as (select ST_MakeEnvelope(10, 10, 20, 20, 27700) as geom 
    union select ST_MakeEnvelope(30, 30, 50, 50, 27700) as geom) ,
poly4 as (select ST_MakeEnvelope(15, 15, 25, 25, 27700) as geom
    union select ST_MakeEnvelope(35, 35, 55, 55, 27700) as geom)
--here's a working query    
SELECT
row_number() over() AS gid, g.*
FROM
(SELECT ST_Difference(ST_Exteriorring(a.geom), b.geom) AS geom
FROM poly3 a, poly4 b
where st_intersects(a.geom,b.geom)) g;

...which, I think, produces your desired result (the green lines):

Answer 2

There is a solution but it's more complex than the SQL posted by chill. I use 'UNION' to consider non intersecting features also:

CREATE VIEW line_difference AS SELECT
row_number() over() AS gid,
g.geom::geometry(linestring, your_SRID)
FROM
    (SELECT
            (ST_Dump(COALESCE(ST_Difference(ST_Exteriorring(poly_a.geom), ST_Exteriorring(poly_b.geom))))).geom
        FROM schema.poly_a, schema.poly_b
            WHERE ST_Intersects(poly_a.geom, poly_b.geom)
    UNION
    SELECT
        ST_Exteriorring(poly_a.geom) AS geom
        FROM schema.poly_a LEFT JOIN schema.poly_b
        ON ST_Intersects(poly_a.geom, poly_b.geom)
    WHERE poly_b.gid IS NULL)
AS g;