PostGIS et la collection Hard Rock Cafe

PGDay France 2024

Julien RIOU

12 Juin 2024

Inventaire

collection.csv

Dublin
Los Angeles
Prague
London
...

Coordonées géographiques

Les boutiques

  • Novimatim API
  • Overpass API
  • Site officiel
curl -sL https://cafe.hardrock.com/locations.aspx | \
    grep 'var currentMapPoint=' | \
    sed "s/.*{'title':/{'title':/g;s/,'description.*/}/g;s/'/\"/g" | \
    sed 's/{"title"://g;s/"lat":/"/g;s/,"lng":/","/g;s/}/"/g'
"Hard Rock Cafe Amsterdam","52.36211","4.88298"
"Hard Rock Cafe Andorra","42.507707","1.531977"
"Hard Rock Cafe Angkor","13.35314","103.85676"
"Hard Rock Cafe Asuncion","-25.2896910","-57.5737599"
"Hard Rock Cafe Athens","37.97642","23.72584"
"Hard Rock Cafe Atlanta","33.75978","-84.3874"
"Hard Rock Cafe Atlantic City","39.359131","-74.417321"
"Hard Rock Cafe Ayia Napa","34.989260","34.000232"
"Hard Rock Cafe Bali","-8.72188","115.16989"
...

(lat, lon)

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

Merci

// reveal.js plugins