5

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

enter image description here enter image description here 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.

enter image description here

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;
  • Could you describe the images a little better? Which parts are the ones you want to get? It seems the lower dotted line is intersecting with polygons to it's right. – Jakub Kania Jan 14 '16 at 23:02
  • Please see answer below posted by chill for more information. – Lunar Sea Jan 18 '16 at 17:49
  • Can you provide a SQL dump of these example features? – Richard Law Jan 21 '16 at 7:43
  • @ Richard Law I've exported the geometries as shape files: workupload.com/file/bD5hKMQT – Lunar Sea Jan 26 '16 at 20:26
3

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):

enter image description here

  • Seems there is something wrong. I can not load the layer in QGIS. – Lunar Sea Jan 18 '16 at 17:46
  • If you can get the test query above to work and open in QGIS then you are winning; it should produce two multilinestings and should open in QGIS regardless of being registered in geometry_columns or not (you can optionally merge these into linestrings with ST_LineMerge()). Where queries returns mixed geometries this usually means there is something complex going on within your source geometries that will need attention before this query (perhaps where several polygons from within one table overlap?). – chill Jan 19 '16 at 8:10
  • Well, your query is working when having two polygons like pictured above. It's also possible to register the multilinestrings in the geometry_columns using '::geometry(multilinestring, enter_your_SRID_here)'. Unfortunately the polygon intersection in my project is more complex (see question). – Lunar Sea Jan 19 '16 at 18:58
  • @LunarSea is your source data all lines? – Mapperz Jan 19 '16 at 19:10
  • I want to select all boundries where the features of table 'poly_a' are not intersecting with the features of 'poly_b'. Both tables 'poly_a' and 'poly_b' contain polygon geometries. – Lunar Sea Jan 20 '16 at 19:44
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.