Find Topology Error With PostGIS

Khairu Aqsara Sudirman

Khairu Aqsara Sudirman

Dec 25, 2019 — 2 mins read
Photo by <a href="https://unsplash.com/@dead____artist" target="_blank">Capturing the human heart.</a> on <a href="https://unsplash.com" target="_blank">Unsplash</a>

Photo by Capturing the human heart. on Unsplash

Topology errors can be fixed quickly using the Fix Topology Error tool, This tool allows you to select a topology error and choose from a number of fixes that have been predefined for that error type. You can also use the tool to get more information about the rule that has been violated or mark the error as an exception. There are several Open sources App available to dicovser those error.

But can we find these topology error with Postgis ? sometime we need to find these error for more case and need. Here is my simple implementation for finding Topology Error using Postgis based on Dimensionally Extended nine-Intersection Model (DE-9IM).

The Dimensionally Extended nine-Intersection Model (DE-9IM) is a topological model and a standard used to describe the spatial relations of two regions (two geometries in two-dimensions, R2), in geometry, point-set topology, geospatial topology, and fields related to computer spatial analysis. The spatial relations expressed by the model are invariant to rotation, translation and scaling transformations, you can read more about this on Wikipedia.

Validate Geometry

SELECT a.id,
       Reason(St_isvaliddetail(St_astext(a.geog, 4326))),
       St_astext(Location(St_isvaliddetail(St_astext(a.geog, 4326))))       AS
       location,
       St_x(St_astext(Location(St_isvaliddetail(St_astext(a.geog, 4326))))) AS
       longitude,
       St_y(St_astext(Location(St_isvaliddetail(St_astext(a.geog, 4326))))) AS
       latitude
FROM   provinsi_bali a
WHERE  St_isvalid(St_astext(a.geog, 4326)) = false 

This Query will find invalid Geometry and the reason, this quite importance, some time we couldn't step over to next process when we have invalid geometry.

Finding Overlaps (Overlaping Polygon)

SELECT St_force2d(St_multi(St_collectionextract(St_intersection(a.geog::geometry, b.geog::geometry),3))) AS ints
FROM   provinsi_bali a
join   provinsi_bali b
ON     st_intersects(a.geog::geometry, b.geog::geometry)
WHERE  st_relate(a.geog::geometry, b.geog::geometry, '2********')
AND    a.id > b.id

We can modify the intersection metrix to find more results, like interior ring, exterior ring and much more, just follow the DE-9IM metrix intersection.

Finding Gaps/Sliver Holes

WITH unions AS (
    SELECT 'gid' as gid,ST_Force2D(ST_Union(f.geog::geometry)) as geom
        FROM provinsi_bali As f 
),
dumps as (
    SELECT 'gid' as gid,ST_NumInteriorRings((ST_DUMP(geom)).geom::geometry(Polygon,4326)) as num,
    (ST_DUMP(geom)).geom::geometry(Polygon,4326) AS geom 
    FROM unions
),
filters as (
    select 'gid' as gid ,geom,num from dumps where num > 0
)
SELECT 'gid' as gid,ST_Force2D(
ST_Multi(ST_CollectionExtract(ST_BuildArea(ST_InteriorRingN(geom,i)),3))) as gaps
FROM filters
CROSS JOIN generate_series(1,(SELECT num FROM filters)) as i


postgis gis
Read More

Finding Rings in Polygon Using Postgis

This article shows how to create a function to filter the inner rings of a (multi)polygon in PostGIS.

Read More

Geospatial Toolkit With MAUP

maup is the geospatial toolkit for redistricting data. The package streamlines the basic workflows that arise when working with blocks, precincts, and districts