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;
Improve this question
4
  • 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. Commented Jan 14, 2016 at 23:02
  • Please see answer below posted by chill for more information. Commented Jan 18, 2016 at 17:49
  • Can you provide a SQL dump of these example features? Commented Jan 21, 2016 at 7:43
  • @ Richard Law I've exported the geometries as shape files: workupload.com/file/bD5hKMQT Commented Jan 26, 2016 at 20:26

2 Answers 2

Reset to default
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

Improve this answer
5
  • Seems there is something wrong. I can not load the layer in QGIS. Commented Jan 18, 2016 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?). Commented Jan 19, 2016 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). Commented Jan 19, 2016 at 18:58
  • @LunarSea is your source data all lines? Commented Jan 19, 2016 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. Commented Jan 20, 2016 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;
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.