2

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.

4
  • ST_Intersection should return the overlapping geometries between two polygon tables. Nov 9, 2018 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, 2018 at 16:22
  • What is the SQL you're trying? Nov 12, 2018 at 16:30
  • replace select * by select st_intersection(a.geom, b.geom) (and eventually other fields from the tables)
    – JGH
    Mar 25, 2019 at 11:15

2 Answers 2

4

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.

2
  • 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, 2018 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'); Nov 15, 2018 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 and acknowledge you have read our privacy policy.

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