spatial-sqllisted
Install: claude install-skill wiatrek-kevin-s/columbus
# Spatial SQL
The aim is spatial SQL that gives the right answer and does not hurt a shared database. Sections 1 to 6 apply to any database. Section 7 covers Columbus.
## 1. Know the table before you query it
Answer these five questions first. Most wrong answers come from guessing one of them.
1. What is the geometry column called, and what type is it (point, line, polygon, multi-, with M or Z)?
2. What is its SRID, and what are that CRS's units: feet, metres or degrees?
3. Does the geometry column have a spatial index?
4. Roughly how many rows are there?
5. What do real values look like (codes, padding, nulls, case)?
```sql
-- PostGIS
SELECT ST_SRID(geom), GeometryType(geom), COUNT(*) FROM transportation.roadway_inventory GROUP BY 1, 2;
SELECT * FROM transportation.roadway_inventory LIMIT 20;
-- Oracle
SELECT t.geom.SDO_SRID, t.geom.SDO_GTYPE, COUNT(*) FROM roads.roadway_inventory t
GROUP BY t.geom.SDO_SRID, t.geom.SDO_GTYPE;
-- SQL Server
SELECT geom.STSrid, geom.STGeometryType(), COUNT(*) FROM roads.roadway_inventory
GROUP BY geom.STSrid, geom.STGeometryType();
```
Without Columbus, the catalogs tell you about indexes: `pg_indexes` (PostgreSQL), `USER_SDO_INDEX_INFO` and `USER_SDO_GEOM_METADATA` (Oracle), `sys.spatial_indexes` (SQL Server).
## 2. Read-only etiquette
- **Count before you fetch.** Run `SELECT COUNT(*) ... WHERE ...` first. If it says 2 million rows, rethink the query instead of pulling them.
- **Sample first.** Look at 20 to 50 rows before you writ