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
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.
You can analyze GIS database execution plans to identify performance bottlenecks in spatial queries, implement optimal indexing strategies for geometry columns and topology analysis, and refactor queries to leverage spatial operators efficiently. Claude helps you benchmark improvements on municipal datasets (parcels, utilities, infrastructure), design partitioning schemes for tables with millions of features, and create documentation that justifies infrastructure investments to stakeholders.
by tchr-dev
You can systematically extract elevation data from LIDAR, GPS surveys, and government sources, then validate contour line accuracy against field observations and reference benchmarks. The skill helps you select appropriate contour intervals for terrain complexity, identify data anomalies and interpolation artifacts, and generate slope analysis and aspect maps that communicate terrain complexity to planners and engineers with confidence in accuracy.
You can generate detailed cartographic specifications that standardize design decisions across your GIS projects. This skill produces complete style guides covering color schemes, symbology, typography, layer hierarchies, accessibility standards, and generalization rules—eliminating manual documentation and ensuring compliance with cartographic best practices and WCAG 2.1 accessibility guidelines.
You can rapidly validate chart elements—symbols, legends, color schemes, abbreviations, and metadata—against authoritative international standards including IHO S-101/S-57 for nautical charts and ICAO Annexes 4 and 15 for aeronautical charts. Claude identifies symbol inconsistencies, flags compliance risks, detects obsolete symbols, and generates correction documentation to support chart approval workflows and ensure navigation safety.
You can rapidly audit environmental assessment documents against 40 CFR 1502 and CEQA Guidelines, extracting compliance requirements and flagging statutory violations before agency submittal. The skill evaluates mitigation measure enforceability, specificity, and monitoring language while cross-referencing regulatory citations and identifying missing NEPA/CEQA-required elements. This accelerates quality assurance workflows and helps junior planners learn the statutory framework through structured, document-specific critique.
You can analyze reported or observed zoning violations using a structured assessment framework that matches conditions to specific municipal code sections, scores violations by enforcement priority, and generates violation notices with correct legal citations and remediation language. This skill helps you build case files with evidence documentation that meets discovery standards and prepares you to defend enforcement decisions during administrative hearings or appeals.
You can conduct structured rezoning analyses that evaluate petitions across multiple planning dimensions: consistency with adopted comprehensive plans, compliance with zoning code criteria, infrastructure capacity, neighborhood character impacts, and public benefit analysis. This framework produces staff reports that withstand legal scrutiny and provide decision-makers with clearly documented alternatives and planning rationale for approval with conditions, approval as-is, or denial recommendations.