← Back to Data Exploration with rakoon-ds
Practical Work 9

Geospatial data: maps, Haversine distances, and joins by territory

Coordinates are two numbers that a linear model cannot use and a tree can. Build the distance that makes them speak, and join by territory without multiplying rows

Duration 75 min
Level Intermediate
Session Session 9
Mission dep-9

Objectives

By the end of this session you will be able to:

  • Draw a map from latitude and longitude, and know what the map is not showing you
  • Compute a great-circle distance in one click, and check the unit
  • Compare what raw coordinates and a derived distance bring to different algorithms
  • Join a point dataset to a territory table without exploding the row count
  • Aggregate a rate by territory and map it

Prerequisites

  • A rakoon-ds account on https://rakoon-ds.apps.way-up.io (free, browser only, nothing to install), and the group join code your instructor gives you
  • A recent Chrome, Edge or Firefox. Computation runs either in your own tab (browser engine) or on the server; the header pill tells you which
  • The mission for this session, assigned to your group. Open a project, then the Mission button in the workshop header: the panel opens next to Report. Click Check after each step
  • Practical works 3 and 4 finished (joins, imports from a URL)
  • Transformation, chart and algorithm names come from the rakoon-ds registry and are served in French even when the interface is in English. Every step below gives you the French label you will click and, in code font, the registry key the mission checks against.

Data

Two sources, both available without a data pack.

DatasetWhereRows x colsCoordinates
california_housing (Prix immobilier Californie)built in20 640 x 9Latitude, Longitude, target price
World Bank country listhttps://api.worldbank.org/v2/country, imported in Practical work 3295 x 8latitude, longitude, plus region and income level

With the SNCF pack, run the same steps on liste-des-gares.csv (4 144 rows for 3 471 stations: deduplicate on code_uic first), join the lost-property counts by station, and map the return rate. Reference figures from the exam trial: 156 stations on the map, correlation between traffic and return rate 0.07, between traffic and item volume 0.67.

Timing

The steps below add up to the announced duration. If you fall behind, Step 1 to Step 3 are the ones that must be finished.

#What you doTime
Step 1Map the raw coordinates12 min
Step 2A distance that means something15 min
Step 3Does geography pay? Three models, one answer15 min
Step 4Join by territory, without exploding15 min
Step 5Aggregate a rate, and map it10 min
Step 6Write the geography down8 min
Total75 min

Instructions

Step 1: Map the raw coordinates12 min

  1. New project PW9 Geography. Add the built-in Prix immobilier Californie.
  2. Explore, chart Carte (géographique) (map). The form asks for three things: Latitude, Longitude, and Colorer par. Use Latitude, Longitude, and colour by price.
  3. Pin it.

Read the map out loud. Where is the money? Is it a north-south story, an inland-coast story, or a "two cities" story?

The map draws at most 2 000 points and does not currently tell you when it has sampled. You are looking at fewer than one district in ten. It is enough to see the structure and not enough to count anything: never take a number off this map.

Add a second reading with Densité 2D (heatmap) (heatmap): X = Longitude, Y = Latitude. That one bins every row, so it does show you where the mass is.

Step 2: A distance that means something15 min

Two coordinates are not a feature. "How far from the expensive city" is.

  1. Family Colonnes, card Colonne par formule (expression): new column sf_lat, formula 37.7749. A bare number is a valid formula and fills the whole column.
  2. Again for sf_lon = -122.4194. That is San Francisco.
  3. Family Colonnes, card Distance géographique (lat/lon) (distance). Point 1: Latitude / Longitude. Point 2: sf_lat / sf_lon. Unit km. Result column dist_sf_km.
  4. Repeat for Los Angeles (34.0522, -118.2437) into dist_la_km.
  5. Add dist_ville_km = the smaller of the two, with Colonne par formule (expression) if your formula language allows it, or keep both columns and let the model choose.

The card computes the Haversine formula, which is the great-circle distance on a sphere. It is correct at every scale, unlike a Pythagoras on degrees, which is wrong as soon as you leave the equator. The unit selector offers km, miles and m: check it, a factor of 1.6 in a feature is not visible in any chart.

Step 3: Does geography pay? Three models, one answer15 min

Train four models on price, all with a 75 / 25 random split, and fill this table yourself. The reference column is what the instance produced on 2026-09-09.

AlgorithmFeaturesR² (reference)
Régression linéaire (linreg)6 columns, no geography0.5249
Régression linéaire (linreg)6 columns + dist_sf_km0.5246
Forêt aléatoire (rf_reg)6 columns + dist_sf_km0.7325
Forêt aléatoire (rf_reg)6 columns + Latitude + Longitude0.7795

Three things to explain in writing, and they are the point of the session:

  • The distance bought the linear model nothing (0.5249 to 0.5246). Why?
  • The same distance bought the forest a lot. Why the difference?
  • Raw coordinates beat the distance, for the forest. What can a tree do with two coordinates that a single distance cannot express?

The answer is worth remembering: a tree splits on thresholds, so a pair of coordinates lets it carve out arbitrary rectangles of the map; a single distance forces every explanation to be a circle around one point. A derived feature is a hypothesis, and here the hypothesis was too narrow.

Step 4: Join by territory, without exploding15 min

Switch to the country dataset of Practical work 3, or re-import it (https://api.worldbank.org/v2/country, XML, .//country, pagination on page, 295 rows).

  1. Import a second source keyed by country: https://date.nager.at/api/v3/AvailableCountries (204 rows) is the easy one.
  2. Family Combiner, card Fusion (join) (join), type left, left key iso2Code, right key countryCode.
  3. Preview: rows_before: 295, rows_after: 295. Apply.
  4. Now do it wrong once: join on region instead. The preview turns red: rows_multiplied: 52.4, 15 459 rows. Undo.

A territory join is the classic place where rows multiply. The right-hand table must have one row per territory. If it does not, apply Supprimer les doublons (drop_duplicates) on the join key first, or add a second key. On the SNCF station list, 4 144 rows collapse to 3 471 stations that way, and forgetting it multiplies the whole dataset.

Step 5: Aggregate a rate, and map it10 min

A count per territory is easy and misleading. A rate is what you want.

  1. Family Agréger, card Agréger (group by) (aggregate): group by region, aggregate name with count.
  2. To get a rate rather than a count, build the 0 / 1 column first with Colonne par formule (expression) (for instance incomeLevel == "High income"), then aggregate it with mean.
  3. Pin a Comptage / moyenne (barres) (bar) of the result, and a Carte (géographique) (map) of the countries coloured by incomeLevel.

The aggregation menu has mean, sum, count, min, max, median, and no "rate of". Building the 0 / 1 column first is the click-only way to get one, and it is a two-step habit worth having.

Step 6: Write the geography down8 min

  1. Pour your map and your bar chart into the Exploration section of the report, each with a caption that says what it shows and what it does not.
  2. Write four lines in Préparation: the distances you built, the reference points you chose, and the unit.
  3. Write four lines in Évaluation: the four R² values of Step 3 and the sentence that explains the difference between the linear model and the forest.
  4. Check the mission.

One line to add to the caption of any map you will ever publish: how many points are drawn out of how many rows.

What you should have

  • A pinned map and a pinned 2D density of the same coordinates, and the difference between them understood
  • Two Haversine distances built with an explicit unit
  • Four models comparing raw coordinates and derived distance on two algorithm families
  • One territory join checked at 295 rows in and out, and one deliberate explosion
  • One rate per territory, built as a 0 / 1 column then averaged
  • The dep-9 mission at 5 / 5

Deliverables

  • Mission: dep-9 validated
  • Report: Exploration and Évaluation sections, with the four-model table
  • The map caption stating how many points are drawn out of how many rows

Bonus

  • Build a distance to the nearest of five cities rather than to one. The click-only route is five distance columns plus a formula; the Python node does it in three lines. Compare the effort and the result.
  • On california_housing, colour the map by the residual of your best model instead of by the price. Where the model is wrong, geographically, is a better question than how wrong it is on average.
  • Import the French departments feed (https://api.sandre.eaufrance.fr/referentiels/v1/dep.xml, selector .//Departement, 101 rows, checked on 2026-09-09) and practise a join on a department code.

Resources