Geospatial Rule Grounding

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