skills.rest
timescale
timescaleOFFICIAL
Explore all skills by this author →
Website𝕏@timescaledbGitHub

design-postgis-tables

Design PostGIS spatial tables with geometry types, SRIDs, and spatial indexes.

1.8k|106|Updated Jul 23, 2025
Search Tags:#Geometry#geography#table design#PostGIS#postgreSQL#spatial index#srid
One-click install
npx skills add https://github.com/timescale/pg-aiguide --skill design-postgis-tables
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: design-postgis-tables
Source: https://github.com/timescale/pg-aiguide/tree/main/skills/design-postgis-tables
Command: npx skills add https://github.com/timescale/pg-aiguide --skill design-postgis-tables

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

AI coding agents often generate spatial database schemas that misuse PostgreSQL's built-in geometric types, omit SRIDs, skip spatial indexes, or write distance queries that cannot use indexes, producing slow and incorrect location-based applications.

Core Features & Use Cases

  • Geometry vs Geography guidance: Decision rules and comparison tables for choosing GEOMETRY or GEOGRAPHY types based on data scope, accuracy needs, and performance.
  • Spatial indexing patterns: GiST, BRIN, and SP-GiST index selection guidance with concrete CREATE INDEX statements for different data distributions.
  • Ready-to-use table designs: Complete schema examples for POIs, property parcels, GPS tracking, and service zones, including generated columns, constraints, and validity checks.
  • Use Case: When building a store locator feature, use this Skill to create a pois table with a GEOGRAPHY(POINT, 4326) column, a GiST index, and an index-supported ST_DWithin query for radius searches.

Quick Start

Design a PostGIS table for storing delivery driver GPS locations with an index-supported query to find drivers within 2 kilometers of a given point.

Frequently Asked Questions about design-postgis-tables

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I design a PostGIS table for location-based queries?▼

Create a table with a GEOGRAPHY(POINT, 4326) column for GPS data or GEOMETRY with a projected SRID for regional data, then add a GiST index on the spatial column. Use ST_DWithin for radius searches so the spatial index is used.

PostGIS geometry vs geography: which type should I use?▼

Use GEOGRAPHY for global GPS data needing accurate spherical distance in meters, and GEOMETRY for regional data in a projected CRS where Cartesian math is faster and more functions are available. GEOGRAPHY supports only SRID 4326 and GiST indexes.

Which spatial index should I use in PostGIS?▼

Use GiST as the default for all geometry and geography columns since it supports all spatial operators. Choose BRIN only for very large append-only GEOMETRY tables, and SP-GiST for point-only data with uniform distributions.

Why is my PostGIS distance query slow?▼

Queries using ST_Distance in the WHERE clause calculate distance for every row and cannot use the spatial index. Replace them with ST_DWithin, which leverages the GiST index, and verify index usage with EXPLAIN ANALYZE.

Does PostGIS work with PostgreSQL built-in geometric types?▼

No, you should not mix them. PostgreSQL's built-in POINT, LINE, and POLYGON types lack true spatial capabilities; always use PostGIS geometry or geography types with an explicit SRID for spatial applications.

Related Skills

Explore More Software Engineering Skills→
ni1o1ni1o1
4

transbigdata-coordinates

Convert geospatial coordinates between WGS84, GCJ02, and BD09 using TransBigData.

Community
Intermediate
daemon-blockint-techdaemon-blockint-tech
7

geospatial-telematics-developer

Guide CRS handling, data modeling, and map-facing APIs for geospatial telematics fleets.

Community
Advanced
troykellytroykelly
10

postgis

Enforce PostGIS 3.6.1 best practices for spatial queries and geometry operations.

Community
Advanced
hung-phanhung-phan

geo-spatial-search

Design geospatial search systems using Geohash, S2, R-tree, and Elasticsearch.

Community
Advanced