Skip to content
Kharita Challenges
All paths
A slate database cylinder with a teal branching tree growing from it, one branch lit amber, and a blue dot inside a dashed ring beside it

PostGIS that stays fast when the table grows

Four spatial-SQL patterns on real tables — the outer join that keeps empty rows, the DWithin an index can answer, the KNN that finds the nearest, and a containment audit — each written the way the planner can use.

PostGIS / SQL

Steps
4
Each assumes the one before it
Time
~2.5 h
At the problems' own estimates
XP
920
Before hint costs and speed bonuses
Progress
0/4
Start the pathBegins with Docks and bikes per borough, in one query

What you will be able to do

Write the four spatial joins that cover most production PostGIS work — aggregate-per-area, within-a-distance, nearest-neighbour and point-in-polygon — in the index-assisted form, in metres on geography, with NULL-safe logic that keeps the rows a report is about.

The sequence

Each step assumes the one before it.

  1. Up nextDocks and bikes per borough, in one queryUp nextA point-in-polygon join with GROUP BY — and the outer join that keeps the boroughs with nothing to count.beginnerDifficulty: beginner150 XP~25 min
  2. 2Which ports are within 25 km of a capital?ST_DWithin on geography: a proximity join in true metres that the index can still answer.intermediateDifficulty: intermediate220 XP~35 min
  3. 3Redirect the rider at an empty dockFor every station with no bikes, the nearest station that has one, and the walk in metres — with the KNN operator so it stays fast at 799 stations or 79,000.intermediateDifficulty: intermediate250 XP~40 min
  4. 4Are these country polygons fit to assign cities to?A point-in-polygon audit that exposes what 1:50m generalisation does to a coastline — and which places the attribute and the geometry disagree about.advancedDifficulty: advanced300 XP~45 min

Where it leads

PostGIS / SQL is examined in the Open-Data Analyst — Associate, which issues the Kharita Certified Open-Data Analyst (Associate) (KODA-A1). The problems here are the practice; the assessment is the same kind of problem, under time.