geospatial-rule-grounding

name

geospatial rule grounding

description

Use this skill when the user wants SQL tasks involving geography, spatial topology, and geometric analysis in PostGIS or similar GIS extensions. Trigger it for requests using terms like 'intersect', 'within X kilometers', 'calculate area', 'find the nearest', or 'show me things inside a region'. Layman examples include: 'Which stores are in this city?', 'Find the average distance between points', or 'Tell me the area of a specific state'. Professional triggers include 'topological relationship predicates', 'coordinate reference system (CRS) conversion', or 'geodesic distance calculations'.

Skill: geospatial rule grounding

1. Capability Definition & Real Case

• Professional Definition: The ability to translate operational and geographic natural language into precise PostGIS SQL predicates. This involves identifying spatial entities and their geometry types (Point, Line, Polygon), applying topological operators (ST_Intersects, ST_Within), managing Coordinate Reference Systems (ST_Transform), and differentiating between planar geometry and geodesic geography for accurate area and distance measurements.
• Dimension Hierarchy: Query Reasoning->Domain-Constrained Semantics->geospatial rule grounding

Real Case

[Case 1]

• Initial Environment: A global historical climatology network (ghcn) table contains Point geometries (geom) representing weather stations, while an administrative 'states' table contains Polygon geometries (geom) for U.S. states.
• Real Question: Find all GHCN stations that intersect Pennsylvania.
• Real Trajectory: 1. Identify 'GHCN stations' as the source point table and 'Pennsylvania' as the target polygon within the states table. 2. Verify that both tables contain compatible geometry columns and identify ST_Intersects as the appropriate topological predicate. 3. Formulate an INNER JOIN between ghcn and states where states.name matches 'Pennsylvania'. 4. Project the station identifiers to produce the final list.
• Real Answer: SELECT ghcn.station_id, ghcn.name FROM ghcn JOIN states ON ST_Intersects(ghcn.geom, states.geom) WHERE states.name = 'Pennsylvania';
• Why this demonstrates the capability: This case demonstrates Point-in-Polygon spatial grounding. The agent must successfully navigate the join between two distinct spatial layers and apply a topological relationship (intersection) rather than a simple attribute match to find stations located within a specific geographic boundary.

---

[Case 2]

• Initial Environment: A database of protected areas (ne_protected_areas) contains a column 'unit_name' and a geometry column 'geom' in EPSG:4326. The user needs a precise metric in square kilometers.
• Real Question: What is the area of the protected area called 'Everglades' in square kilometers?
• Real Trajectory: 1. Search the protected areas table for the keyword 'Everglades' using case-insensitive matching. 2. Identify the geometry type as Polygon, making ST_Area a valid operation. 3. Recognize that the source CRS is degree-based (4326), necessitating a cast to the 'geography' type for a geodesic calculation in meters. 4. Apply a conversion factor (divide by 1,000,000) to transform the meter-based result into square kilometers.
• Real Answer: SELECT SUM(ST_Area(geom::geography)) / 1000000.0 AS area_sq_km FROM ne_protected_areas WHERE unit_name ILIKE '%Everglades%';
• Why this demonstrates the capability: This illustrates the critical distinction between planar and geodesic reasoning. A failure to cast to geography in a degrees-based CRS would yield a meaningless area value in square degrees, but the agent correctly uses geodesic arithmetic for metric accuracy.

---

[Case 3]

• Initial Environment: A census database contains a 'counties' table with per-county geometries and associated 'state' labels. The user requires a summary statistic based on the complexity of county boundaries.
• Real Question: Group counties by state and calculate the average perimeter in kilometers.
• Real Trajectory: 1. Map the 'perimeter' request to the ST_Perimeter function in PostGIS. 2. Use the 'geography' cast to obtain the perimeter in meters for the underlying EPSG:4326 geometries. 3. Group the dataset by the 'state' column to perform a categorical aggregation. 4. Calculate the average of the converted perimeters (meters / 1000) for each state grouping.
• Real Answer: SELECT state, AVG(ST_Perimeter(geom::geography) / 1000.0) AS avg_perimeter_km FROM counties GROUP BY state;
• Why this demonstrates the capability: This demonstrates advanced spatial aggregation. The agent must combine standard SQL grouping logic with spatial analysis functions, ensuring that the perimeter of individual polygons is correctly calculated and unit-converted before being passed to the AVG function.

Pipeline Execution Instructions

To synthesize data for this capability, you must strictly follow a 3-phase pipeline. Do not hallucinate steps. Read the corresponding reference file for each phase sequentially:

1. Phase 1: Environment Exploration Read the exploration guidelines to discover raw knowledge seeds: references/EXPLORATION.md

2. Phase 2: Trajectory Selection Once Phase 1 is complete, read the selection criteria to evaluate the trajectory: references/SELECTION.md

3. Phase 3: Data Synthesis Once a trajectory passes Phase 2, read the synthesis instructions to generate the final data: references/SYNTHESIS.md