PostGIS Land Parcel Spatial Indexing Script

Role: Senior Geospatial Database Engineer specializing in cadastral architectures and spatial indexing.

Context

• Database engine target: {{target_database_engine}}
• Primary parcel boundary table: {{parcel_table_name}}
• Coordinate reference system SRID: {{spatial_reference_system}}
• Overlay zoning boundary dataset: {{zoning_layer_table}}
• Migration batch limit: {{staging_batch_size}}
• Index page storage density parameter: {{index_fill_factor}}

Task

Generate a complete, idempotent spatial migration SQL script that validates geometries, builds performant R-Tree/GiST spatial indexes, and prepares {{parcel_table_name}} for high-throughput topological spatial joins against {{zoning_layer_table}}.

Method

1. Inspect table schemas and inject safety checks ensuring PostGIS or equivalent spatial extensions are loaded on {{target_database_engine}}.
2. Write pre-migration schema guards with transactional DDL wrappers.
3. Implement geometry validation routines to catch and correct invalid polygon rings, self-intersections, and unclosed boundaries conforming to {{spatial_reference_system}}.
4. Define spatial index creation commands on {{parcel_table_name}} utilizing GiST/SP-GiST with configurable fill factor settings via {{index_fill_factor}}.
5. Construct batch update statements processing unindexed parcel batches limited by {{staging_batch_size}} to prevent locking live read replicas.
6. Generate analytical ST_Intersects and ST_Contains sample verification queries linking {{parcel_table_name}} and {{zoning_layer_table}}.
7. Include comprehensive rollback operations that drop spatial indexes and revert staging columns safely.

Constraints

• MUST wrap all non-concurrent schema alterations in explicit transactional blocks.
• MUST NOT leave invalid polygon topologies unlogged or silently dropped during execution.
• Scripts MUST include execution timing checkpoints and detailed comment headers per section.
• Geometry column transformations must strictly enforce {{spatial_reference_system}}.

Output format

Executable SQL script file structured in four sequential sections:

1. Environment Prerequisites and Schema Verifications (max 30 lines)
2. Geometry Sanitization & Transformation Procedures (max 60 lines)
3. Spatial Index DDL and Vacuum/Analyze Directives (max 40 lines)
4. Post-Migration Verification Queries and Rollback DDL (max 50 lines)

Self-review

• Confirm that no raw SQL statements drop data columns without backup tables.
• Ensure all spatial function calls match the specified {{spatial_reference_system}}.
• Verify script idempotency so repeated execution does not produce duplicate index errors.