[GIS] Checking for overlapping geometries in one table using PostGIS

overlapping-featurespostgis

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.

Best Answer

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.

Related Solutions

PostGIS – How to Perform Nearest Neighbor Calculation in PostGIS?

a and b are alias table names to the same table. This is effectively a T1 CROSS JOIN T2 in DB-speak. This allows a self-join to say "how close one part is to another" in a single table.

SELECT 
  a.hgt AS a_hgt,
  b.hgt AS b_hgt,
  ST_Distance(a.the_geom, b.the_geom) AS distance_between_a_and_b
FROM 
  public."TestArea" AS a, public."TestArea" AS b
WHERE
  a.gid < b.gid AND a.area > 100 AND b.area > 100

You might want to add another WHERE clause to limit the number of rows, e.g., add AND ST_Distance(a.the_geom, b.the_geom) < 1000.0 so that all distances are less than a kilometer (if you have projected UTM).

[GIS] Improve performance of a PostGIS st_dwithin query

The explain doesn't show an index coming into play, which could be for two reasons:

You don't have one. So make one with CREATE INDEX tree_gix ON trees USING GIST (geom)
Your data is in geographic coordinates, so your spatial join isn't really doing anything selective (it's joining every tree to all other trees, every time). In that case, either (a) change to using the geography type or (b) move your data to an appropriate planar projection (I recommend (b)).

Finally, yeah, the LEFT JOIN is not doing anything useful for you unless there are lonely trees with no partners in the radius you need to keep in the result set. I'd remove the geometric and species GROUP BY as well, since you already have a unique id in there, the other variables are just noise.

SELECT 
  a.tree_id, a.species, avg(b.age) as age_avg, 
  count(*) as samples, a.geom
FROM trees a 
JOIN trees b
ON ST_DWithin(a.geom, b.geom, 100) AND a.species = b.species
WHERE a.age IS NULL
GROUP BY a.tree_id;

Other than the first two possible errors above, the query itself looks pretty neat and clean.

Best Answer

Related Solutions

PostGIS – How to Perform Nearest Neighbor Calculation in PostGIS?

[GIS] Improve performance of a PostGIS st_dwithin query

Related Question