Fixing Administrative Boundary Attribute Errors Using PostGIS (Village → Circle → Block)

In many GIS datasets, especially administrative boundary datasets, attribute fields often contain errors such as:

• Missing values (NULL)

• Spelling mismatches

• Incorrect hierarchy relationships

• Villages assigned to the wrong circle or block

This is common when working with shapefiles collected from multiple sources.

In this tutorial, we will learn how to automatically fix these errors using PostGIS spatial relationships instead of relying on unreliable text attributes.

This method is widely used in professional GIS systems and government WebGIS portals.

Dataset Structure

Assume we have four administrative boundary shapefiles:

Problem:

The village_boundary layer contains many incorrect values:

• wrong circlename

• wrong blockname

• NULL values

• spelling mismatch

Example:

Instead of manually correcting thousands of records, we will derive correct values using spatial joins.

Why Spatial Relationship Is Better Than Text Attributes

Text attributes are unreliable because:

District A
district a
District-A
Distrct A

All represent the same district but appear different to the database.

However geometry is always correct.

So we use spatial relationships like:

• ST_Intersects

• ST_Intersection

• ST_Area

Step 1: Load Shapefiles Into PostGIS

Use shp2pgsql or QGIS to load the shapefiles.

Example command:

shp2pgsql -I village_boundary.shp village_boundary | psql -d gisdb

After loading, ensure your tables look like:

district_boundary
circle_boundary
block_boundary
village_boundary

Each table must contain a geometry column (usually geom).

Step 2: Create Spatial Indexes (Important)

Spatial operations can be slow without indexes.

Create indexes first:

CREATE INDEX idx_village_geom
ON village_boundary USING GIST (geom);

CREATE INDEX idx_circle_geom
ON circle_boundary USING GIST (geom);

CREATE INDEX idx_block_geom
ON block_boundary USING GIST (geom);

CREATE INDEX idx_district_geom
ON district_boundary USING GIST (geom);

This makes spatial queries 10–100× faster.

Step 3: Fix Circle Name Using Spatial Intersection

Villages may overlap multiple circles.

So we assign the circle with maximum overlap area.

UPDATE village_boundary v
SET circlename = c.circlename
FROM (
    SELECT v.gid AS vid,
           c.circlename,
           ROW_NUMBER() OVER (
               PARTITION BY v.gid
               ORDER BY ST_Area(ST_Intersection(v.geom,c.geom)) DESC
           ) AS rn
    FROM village_boundary v
    JOIN circle_boundary c
    ON ST_Intersects(v.geom,c.geom)
) c
WHERE v.gid = c.vid
AND c.rn = 1;

Explanation:

1. Find all circles intersecting a village

2. Calculate intersection area

3. Select the circle with the largest overlap

This ensures correct circle assignment.

Step 4: Fix Block Name Using Same Logic

Block boundaries may also overlap.

Use the same approach:

UPDATE village_boundary v
SET blockname = b.blockname
FROM (
    SELECT v.gid AS vid,
           b.blockname,
           ROW_NUMBER() OVER (
               PARTITION BY v.gid
               ORDER BY ST_Area(ST_Intersection(v.geom,b.geom)) DESC
           ) AS rn
    FROM village_boundary v
    JOIN block_boundary b
    ON ST_Intersects(v.geom,b.geom)
) b
WHERE v.gid = b.vid
AND b.rn = 1;

Now every village gets the correct block based on spatial overlap.

Step 5: Detect Villages That Do Not Match Any Circle

Sometimes villages fall outside circle polygons due to topology issues.

Check them using:

SELECT v.villagename
FROM village_boundary v
LEFT JOIN circle_boundary c
ON ST_Intersects(v.geom, c.geom)
WHERE c.circlename IS NULL;

Example output:

Village Alpha
Village Beta
Village Gamma

These require manual inspection in QGIS.

Step 6: Verify Results

After running the update queries, your table should look like:

All attributes are now consistent and correct.

Final Administrative Hierarchy

The cleaned dataset follows the correct hierarchy:

District
   ↓
Circle
   ↓
Block
   ↓
Village

This is essential for:

• WebGIS filtering systems

• administrative analysis

• spatial queries

• data integrity

Why This Method Is Used in Professional GIS Systems

This approach is used in:

• Government GIS portals

• National Spatial Data Infrastructure (NSDI)

• Large-scale WebGIS platforms

Because it:

• eliminates attribute errors

• guarantees spatial correctness

• works for large datasets automatically

Conclusion

Instead of manually correcting thousands of records, PostGIS spatial operations allow us to:

• automatically detect hierarchy

• fix attribute mismatches

• maintain clean administrative datasets

Using geometry instead of text attributes ensures reliable and scalable GIS data management.