0

I have a table with geometries where some geometries overlaps with other geometries in the same table.

I already found an answer for this question here: Check whether table has overlapping polygons, in PostGIS?

But when I try to create a view of the query as result I do not get any geometries for the query only a table, this also helps a lot but with geometries it would help even more.

The query is

SELECT *
FROM schema.table1 a
INNER JOIN schema.table1 b ON 
(a.geom && b.geom AND ST_Overlaps(a.geom, b.geom))
WHERE a.id != b.id;

With this query I get the table and if I substitute st_overlaps with st_intersection I get the query error that the intersection must be type boolean and not type geometry.

  • ST_Intersection should return the overlapping geometries between two polygon tables. – DPSSpatial Nov 9 '18 at 17:53
  • When I try this query with st_intersection, I get the query error that the intersection must be type boolean and not type geometry. – AndreasR Nov 12 '18 at 16:22
  • What is the SQL you're trying? – DPSSpatial Nov 12 '18 at 16:30
  • replace select * by select st_intersection(a.geom, b.geom) (and eventually other fields from the tables) – JGH Mar 25 at 11:15
1

Download the PostGIS Addons from this link: https://github.com/pedrogit/postgisaddons

Install by running the postgis_addons.sql file to get the ST_GeoTableSummary() function.

Test by running the postgis_addons_test.sql file.

Search for ST_GeoTableSummary() in the file. The function is documented.

This query identifies overlapping parts in a table:

CREATE TABLE test_geotable_summary AS
SELECT * FROM ST_GeoTableSummary('public', 'test_geotable', 'geom', 'id');

If you want to remove the overlaps tou can use the ST_RemoveOverlaps() function.

  • Thaks a lot Pierre for the add-ons, they help me a lot to get more Information about my data. The row summary2 overlapping geometries is skiped by default, is there any chance to get information for this row. – AndreasR Nov 14 '18 at 12:10
  • Oups... If you did not have any invalid geometries, call it like this: ST_GeoTableSummary('public', 'test_geotable', 'geom', 'id', 10, 'ALL'); – Pierre Racine Nov 15 '18 at 22:32
0

The query should be OK, so the problem is probably when you try to create a view from it.

My guess is that you try to view it from another tool like QGIS, and if I remember correctly it does not show correctly views because they are not written as geometries (view behave differently, you can not create index on it for example, which is recommended to work and display fastly when you have a lot of geometries ).

The simplest solution would be, like you said, to create a table from it.

Or if it is actually QGIS maybe look at this? spatial view in QGIS

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.