Skill: geospatial rule grounding

[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