Spatial Database Query Optimization for GIS Analysis
Optimize PostGIS queries for faster urban planning GIS analysis
What You Can Do
You can identify slow spatial queries, analyze PostGIS execution plans, design optimal spatial indexes (GIST, BRIN, SP-GIST), and restructure database schemas for efficient land use analysis, zoning verification, and infrastructure mapping. This skill helps you troubleshoot why intersection queries timeout, optimize buffer operations on large feature classes, and accelerate web mapping service delivery without requiring application code changes.
Features
Interpret EXPLAIN output to pinpoint spatial operation bottlenecks
Select and implement appropriate index types (GIST, BRIN, SP-GIST) for parcel, utility, and zoning datasets
Restructure queries to handle 100k+ geometry buffer operations efficiently
Optimize multi-dataset joins using statistics and query reordering
Recommend partitioning, materialized views, and geometry column restructuring
Identify database vs. application bottlenecks affecting layer load times
Maintain query optimizer accuracy for large spatial tables
Example Output
Example 1: Query Optimization
Original query (45 seconds):
SELECT * FROM parcels p
JOIN zoning z ON ST_Intersects(p.geom, z.geom)
WHERE z.zone = 'Commercial'
Optimized query (0.8 seconds):
SELECT * FROM parcels p
JOIN zoning z ON p.geom && z.geom AND ST_Intersects(p.geom, z.geom)
WHERE z.zone = 'Commercial'
With recommendations: Add GIST index on parcels.geom, cluster parcels by tile, use statistics refresh.
Example 2: Index Recommendation
For 5M building footprint table with frequent distance queries: BRIN index reduces storage overhead 95% vs GIST while maintaining sub-second response times for "buildings within 500m" queries.
What's Included
- SKILL.md instruction file with PostGIS optimization methodology:
- Query execution plan analysis template:
- Spatial index selection decision tree (GIST vs BRIN vs SP-GIST):
- Schema redesign checklist for GIS consolidation projects:
- Web service performance diagnostic workflow:
- Common query pattern library with optimized variants:
Who It's For
- GIS Analysts — Optimize database performance for daily land use and zoning analysis
- Municipal IT/Database Administrators — Troubleshoot spatial database bottlenecks during planning review cycles
- Urban Planners — Reduce analysis turnaround time when working with large departmental datasets
- Web GIS Developers — Diagnose and fix slow web mapping service layer performance
- GIS Consultants — Advise clients on schema design and indexing for new municipal GIS implementations
Best For
- Identifying and fixing timeout errors in spatial intersection/buffer queries
- Designing indexes for parcel, zoning, and infrastructure datasets
- Optimizing spatial joins between multiple large feature classes
- Accelerating web map service layer load times
- Planning database consolidation and schema restructuring for multi-department GIS
You might also like
Transform violation complaints and site inspection data into standardized, defensible enforcement documentation. You can rapidly assess violation severity, establish reasonable remediation timelines, prioritize enforcement actions across multiple violations, and generate compliance agreements that protect jurisdictional authority. The skill structures your analysis to create documentation suitable for both negotiated compliance and formal enforcement proceedings.
You can conduct comprehensive land use compatibility assessments that identify vertical and horizontal conflicts between proposed developments and surrounding uses. The skill helps you evaluate zoning compliance, assess adjacency impacts, document regulatory alignment, and develop evidence-based mitigation strategies that withstand planning commission and city council scrutiny.
Conduct rigorous corridor-level capacity assessments that balance competing modal demands within constrained right-of-way. You analyze existing conditions across all transportation modes, identify true capacity constraints versus perceived limitations, and generate defensible recommendations for modal reallocation—all grounded in traffic engineering principles, transit operations data, and urban design standards. This skill moves beyond single-mode optimization to deliver integrated corridor function recommendations that stakeholders can act upon.
This skill structures the variance analysis process to ensure consistency, reduce bias, and create audit trails that justify approvals or denials based on established legal standards. You'll generate comprehensive reports that document substantial difficulty claims, unique circumstances, hardship justifications, and condition frameworks that withstand administrative review and potential legal challenge.
You can systematically design spatial analysis workflows that integrate vector data (zoning boundaries, infrastructure networks, parcel geometries) with raster datasets (elevation models, land use classifications, accessibility surfaces). The skill provides validation protocols to identify data resolution conflicts, projection misalignments, overlay logic errors, and output specification issues before analysis execution. You'll communicate complete analysis specifications to technical teams and stakeholders with confidence that the workflow methodology is sound.
This skill structures your conditional use permit analysis to ensure legally defensible findings and consistent decision-making across your jurisdiction. You'll evaluate applications against specific approval criteria, identify required mitigations and conditions with justifiable nexus to impacts, and generate comprehensive staff reports that document your discretionary reasoning and withstand appeals.
You can synthesize coastal datasets (tide gauges, LIDAR, bathymetry, historical shorelines) to quantify erosion trends and sea-level rise impacts. Claude builds deterministic and probabilistic inundation projections, assesses infrastructure vulnerability, and translates technical analysis into policy-relevant findings that satisfy state coastal zone management and NEPA/CEQA compliance requirements for development approvals and adaptation investments.
You can review and verify nautical and aeronautical chart proofs, validate spatial data accuracy against source materials, analyze symbol placement for standards compliance, and identify cartographic errors before publication. The skill provides structured analysis frameworks to catch discrepancies between chart layers, interpret complex features like traffic separation schemes and airspace boundaries, and document quality assurance findings for correction tracking.