Inventaire

collection.csv

Dublin
Los Angeles
Prague
London
...

Coordonées géographiques

Les boutiques

• Novimatim API
• Overpass API
• Site officiel

Exploration

Démarrage de l’instance

docker run -d --name postgres -v "$(pwd):/mnt:ro" \
    -e POSTGRES_USER=hrc -e POSTGRES_PASSWORD=insecurepassword \
    -e POSTGRES_DB=hrc \
    postgis/postgis:16-3.4
docker exec -ti postgres psql -U hrc -W

Import

create table collection (
    name text primary key,
    lat numeric,
    lon numeric
);
\copy collection (name, lat, lon) from '/mnt/collection_with_coordinates.csv' csv;

create table nodes (
    name text primary key,
    lat numeric,
    lon numeric
);
\copy nodes (name, lat, lon) from '/mnt/nodes.csv' delimiter ',' csv;

Corrélation

 select c.name as tshirt, n.name as restaurant,
        round((ST_Distance(ST_Point(c.lon, c.lat), ST_Point(n.lon, n.lat), true)/1000)::numeric, 2)
            as distance_km
   from collection c
        left join nodes n
            on ST_DWithin(ST_Point(c.lon, c.lat), ST_Point(n.lon, n.lat), 50000, true)
order by c.name, distance_km;

• ST_Point
• ST_Distance
• ST_DWithin

Analyse

• Correspondance parfaite
• Boutiques fermés
• Boutiques multiples
• Multiples t-shirts
• Mauvaise correspondance
• Opportunités ratées

Prochaine boutique

curl -A "Hard Rock Cafe Lightning Talk" \
    -sL "https://nominatim.openstreetmap.org/search?limit=1&format=json&q=Belgium" | \
    jq -r '.[0].name,.[0].lon,.[0].lat'
België / Belgique / Belgien
4.6667145
50.6402809

  select n.name,
         round((ST_Distance(ST_Point(n.lon, n.lat), ST_Point(4.6667145, 50.6402809), true)/1000)::numeric, 2)
           as distance_km
    from nodes n
        left join collection c
            on n.name = c.name
   where c.name is null
     and ST_Distance(ST_Point(n.lon, n.lat), ST_Point(4.6667145, 50.6402809), true)/1000 < 1000
order by distance_km;

                  name                   | distance_km 
-----------------------------------------+-------------
 Hard Rock Cafe Cologne                  |      164.87
 Hard Rock Cafe London Piccadilly Circus |      349.99
 Hard Rock Cafe Manchester               |      569.37
 Hard Rock Cafe Munich                   |      573.42
 Hard Rock Cafe Innsbruck                |      618.90
 Hard Rock Cafe Newcastle                |      640.64
 Hard Rock Cafe Milan                    |      666.41
 Hard Rock Cafe Copenhagen               |      769.51
 Hard Rock Cafe Edinburgh                |      789.31
 Hard Rock Cafe Venice                   |      812.96
 Hard Rock Cafe Wroclaw                  |      870.89
...

Conclusion

Julien RIOU

• https://julien.riou.xyz
• @jriou@hachyderm.io

Merci