← ClaudeAtlas

spatial-sqllisted

Write correct, fast, read-only spatial SQL for PostGIS, Oracle Spatial (SDO_RELATE, SDO_WITHIN_DISTANCE) and SQL Server (STIntersects, geography), covering intersects, within a distance, nearest, bounding-box filters, areas and lengths. Use when asked to query a spatial database, find features inside or near something, write or fix a spatial SQL query, or when a query is slow, gives distances in the wrong units, or is refused by sql_select.
wiatrek-kevin-s/columbus · ★ 2 · API & Backend · score 68
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