Rechercher les restaurants à 5 minutes à pied de chez soi avec DuckDB, la BAN, OvertureMaps et Isochrone IGN

restaurants.sql

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;