Last active
July 16, 2024 07:33
-
-
Save datagistips/6cd4705f405b2b992dfcd3b1428a3710 to your computer and use it in GitHub Desktop.
Rechercher les restaurants à 5 minutes à pied de chez soi avec DuckDB, la BAN, OvertureMaps et Isochrone IGN
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| load spatial; | |
| -- point (adresse) | |
| -- Ex. 34+bis+avenue+philippe+solari | |
| -- Utilisation de la BAN | |
| create or replace table point as ( | |
| with a as (select unnest(features, recursive:=true) from read_json_auto(format('https://api-adresse.data.gouv.fr/search/?q={}', '34+bis+avenue+philippe+solari'))) | |
| select st_point(coordinates[1], coordinates[2]) as geom from a | |
| ); | |
| from point; | |
| -- coordonnées | |
| select st_x(geom), st_y(geom) from point; -- 5.444444, 43.537894 | |
| -- isochrone | |
| -- Mettre les coordonnées ci-dessus dans la requête isochrone IGN | |
| create or replace table isochrone as ( | |
| from st_read(format('https://wxs.ign.fr/calcul/geoportail/isochrone/rest/1.0.0/isochrone?resource=bdtopo-pgr&profile=pedestrian&costType=time&costValue=300&direction=departure&point={},{}&constraints=&geometryFormat=geojson', 5.444444, 43.537894)) | |
| ); | |
| from isochrone; | |
| -- bb (boundig box de l'isochrone) | |
| create or replace table bb as ( | |
| select st_xmin(geom) xmin, | |
| st_ymin(geom) ymin, | |
| st_xmax(geom) xmax, | |
| st_ymax(geom) ymax | |
| from isochrone | |
| ); | |
| from bb; | |
| -- xmin = 5.441055173 | |
| -- xmax = 5.445709348 | |
| -- ymin = 43.535444759 | |
| -- ymax = 43.540039408 | |
| -- restos_bb (restos OvertureMaps dans la BBOX) | |
| -- reprendre les coordonnées ci-dessus (xmin, xmax, ymin, ymax) et les mettre en dur | |
| -- cela rendra la requête plus rapide car cela s'appuiera sur les méta-données du parquet associées à la variable bbox | |
| create or replace table restos_bb as ( | |
| SELECT | |
| id, | |
| names.primary as primary_name, | |
| ST_GeomFromWKB(geometry) as geometry, -- Utiliser ST_GeomFromWKB pour passer de BLOB à geometry | |
| categories.main as main_category, | |
| sources[1].dataset AS primary_source, | |
| confidence | |
| FROM read_parquet('s3://overturemaps-us-west-2/release/2024-06-13-beta.0/theme=places/type=*/*', | |
| filename=true, | |
| hive_partitioning=1), | |
| bb | |
| WHERE | |
| bbox.xmin > 5.441055173 | |
| AND bbox.xmax < 5.445709348 | |
| AND bbox.ymin > 43.535444759 | |
| AND bbox.ymax < 43.540039408 | |
| AND main_category LIKE '%restaurant%' | |
| ); | |
| from restos_bb; | |
| -- restos_iso (restos qui sont dans l'isochrone) | |
| create or replace table restos_iso as ( | |
| select restos_bb.* | |
| from isochrone, restos_bb | |
| where st_within(restos_bb.geometry, isochrone.geom) | |
| ); | |
| from restos_iso; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment