PostGIS Geometry Quality: Invalid Geometries, Mixed SRIDs, and Complexity

PostGIS Geometry Quality: Invalid Geometries, Mixed SRIDs, and Complexity

The Problem

Spatial data quality in PostGIS degrades in three distinct ways, and each one causes different failures at different times.

Invalid geometries -- self-intersecting polygons, duplicate points, unclosed rings -- are the most immediately dangerous. Functions like ST_Intersection, ST_Union, and ST_Buffer rely on the OGC Simple Features specification, which requires valid geometry input. When they encounter invalid geometries, the result is unpredictable: some functions silently return NULL or empty geometries, others throw errors like TopologyException, and a few produce geometrically wrong results that look plausible but are not. A polygon with a self-intersection might report a negative area, or a spatial join might silently drop matching rows because the intersection computation fails internally.

Mixed SRIDs within a table produce wrong calculations without any error or warning. If one row stores a geometry in SRID 4326 (WGS 84 geographic coordinates) and another in SRID 3857 (Web Mercator projected coordinates), spatial operations between them compute distances and areas using mismatched coordinate systems. A distance calculation between a point in SRID 4326 and a point in SRID 3857 returns a meaningless number -- not an error, just a silently wrong result. This typically happens during data imports when different source datasets use different coordinate systems and nobody normalizes them.

Overly complex geometries -- polygons with tens of thousands or hundreds of thousands of vertices -- are a performance problem that compounds over time. Every spatial operation on a geometry scales with its vertex count. ST_Intersects on a polygon with 100,000 vertices is orders of magnitude slower than on a simplified version with 5,000 vertices that represents the same shape to within a meter of accuracy. These geometries also consume significant memory: a 500,000-vertex polygon can occupy several megabytes in ST_MemSize, and spatial joins involving such geometries dominate query time.

These three issues interact. Invalid complex geometries are the hardest to fix because ST_MakeValid on a million-vertex polygon is expensive and may produce unexpected topology changes. Mixed SRIDs combined with invalid geometries can cause ST_Transform to fail outright when it encounters geometry that cannot be reprojected.

How to Detect It

Check each quality dimension separately:

-- Count invalid geometries
SELECT count(*) AS invalid_geometries
FROM points_of_interest
WHERE NOT ST_IsValid(geom);

-- Show the specific validation failures
SELECT id, ST_IsValidReason(geom) AS reason
FROM points_of_interest
WHERE NOT ST_IsValid(geom)
LIMIT 20;

-- Check for mixed SRIDs
SELECT DISTINCT ST_SRID(geom) AS srid, count(*)
FROM points_of_interest
GROUP BY ST_SRID(geom);

-- Find overly complex geometries
SELECT id,
       GeometryType(geom),
       ST_NPoints(geom) AS vertex_count,
       pg_size_pretty(ST_MemSize(geom)) AS memory_size
FROM regions
WHERE ST_NPoints(geom) > 10000
ORDER BY ST_NPoints(geom) DESC
LIMIT 20;

The ST_IsValidReason function tells you exactly what is wrong -- "Self-intersection at (12.5, 45.3)" is far more actionable than knowing a geometry is invalid. Mixed SRIDs should return exactly one row. Anything more means your data has inconsistent coordinate systems. For vertex counts, the threshold depends on your use case, but geometries above 10,000 vertices are worth reviewing for simplification potential.

How myDBA.dev Shows It

myDBA.dev's PostGIS monitoring page surfaces geometry quality metrics across all your spatial tables in a single view. You can see invalid geometry counts, SRID distribution, and vertex count statistics without writing any detection queries. The page tracks these metrics over time, so you can identify whether quality is degrading after specific data imports or ETL runs.

The Health Check includes dedicated PostGIS checks that flag invalid geometries (G14), mixed SRIDs (G15), and high-complexity geometries (G16) as warnings or critical findings. Each finding includes the affected table, the count of problematic rows, and a specific fix suggestion. This converts geometry quality from an invisible problem into a scored, trackable metric -- you can see immediately whether a data import introduced new quality issues.

How to Fix It

Address each issue in order -- fix validity first, then normalize SRIDs, then simplify complexity:

-- Fix invalid geometries
UPDATE points_of_interest
SET geom = ST_MakeValid(geom)
WHERE NOT ST_IsValid(geom);

-- Normalize all geometries to a single SRID (WGS 84)
UPDATE points_of_interest
SET geom = ST_Transform(geom, 4326)
WHERE ST_SRID(geom) != 4326;

-- Review overly complex geometries for simplification
-- tolerance is in the geometry's units (degrees for SRID 4326)
SELECT id,
       ST_NPoints(geom) AS original_vertices,
       ST_NPoints(ST_Simplify(geom, 0.001)) AS simplified_vertices,
       ST_Simplify(geom, 0.001) AS simplified_geom
FROM regions
WHERE ST_NPoints(geom) > 10000;

A few cautions. ST_MakeValid can change the geometry type -- a self-intersecting polygon may become a multipolygon or a geometry collection. Test on a sample before running a bulk update. For simplification, ST_Simplify uses the Douglas-Peucker algorithm and the tolerance value depends on your SRID: 0.001 degrees in SRID 4326 is roughly 111 meters at the equator. If you need topology preservation (no new intersections between adjacent polygons), use ST_SimplifyPreserveTopology instead.

How to Prevent It

Add database-level constraints that reject bad data at insert time:

-- Reject invalid geometries
ALTER TABLE points_of_interest
ADD CONSTRAINT enforce_valid_geom CHECK (ST_IsValid(geom));

-- Enforce a single SRID
ALTER TABLE points_of_interest
ADD CONSTRAINT enforce_srid_geom CHECK (ST_SRID(geom) = 4326);

These constraints prevent the problem entirely. Any INSERT or UPDATE that would store an invalid geometry or a geometry with the wrong SRID will fail with a clear constraint violation error. This is far better than silently accumulating bad data and discovering it months later when a spatial query returns wrong results.

For vertex complexity, constraints are less practical because the acceptable threshold varies by use case. Instead, add validation to your data ingestion pipeline: check ST_NPoints on incoming geometries and simplify anything above your threshold before inserting. Log the simplification so you have an audit trail.

Combine constraints with continuous monitoring. myDBA.dev's PostGIS health checks run on every collection cycle, catching any data that bypasses your application layer -- direct SQL imports, ETL jobs, or third-party integrations that insert data without going through your validation code. The constraint catches inserts through your application; the monitoring catches everything else.