utilisation Restauration

But

Constitution de la liste des restaurants.

Écriture de la requête

Création d’indexes supplémentaires

Certains indexes existent déjà quand la base est générée, mais ici de nouveaux vont être nécessaires pour accélérer le traitement :

CREATE INDEX IF NOT EXISTS tag_key_value ON tag (key, value);
CREATE INDEX IF NOT EXISTS tag_value ON tag (value);

Sélection des enregistrements

Les POI peuvent être définis dans les 2 tables node, sous la forme de simples points ou way pour préciser la surface occupée.
La requête sera donc double (pour chacune des tables) avec une union des résultats.

SELECT * FROM node WHERE ...
UNION
SELECT * FROM way WHERE ...

Voici le résultat :

select 
	'node' as 'element', n.id as 'id', X(n.coord) as lon, Y(n.coord) as lat, 
	(select name from user where user.id=n.uid) as 'user',
	n.timestamp as 'timestamp', t1.value as 'amenity',
	(select value from tag as t join node_tags as nt on t.key='name' and nt.id_tag=t.id and nt.id_node=n.id) as 'name',
	(select value from tag as t join node_tags as nt on t.key='cuisine' and nt.id_tag=t.id and nt.id_node=n.id) as 'cuisine',
	(select value from tag as t join node_tags as nt on t.key='operator' and nt.id_tag=t.id and nt.id_node=n.id) as 'operator',
	(select value from tag as t join node_tags as nt on t.key='source' and nt.id_tag=t.id and nt.id_node=n.id) as 'source',
	(select value from tag as t join node_tags as nt on t.key='description' and nt.id_tag=t.id and nt.id_node=n.id) as 'description',
	(select value from tag as t join node_tags as nt on t.key='opening_hours' and nt.id_tag=t.id and nt.id_node=n.id) as 'opening_hours',
	(select value from tag as t join node_tags as nt on t.key in ('url','web','contact:website') and nt.id_tag=t.id and nt.id_node=n.id) as 'website',
	(select value from tag as t join node_tags as nt on t.key in ('phone','contact:phone') and nt.id_tag=t.id and nt.id_node=n.id) as 'phone',
	(select value from tag as t join node_tags as nt on t.key='addr:housenumber' and nt.id_tag=t.id and nt.id_node=n.id) as 'addr_housenumber',
	(select value from tag as t join node_tags as nt on t.key='addr:street' and nt.id_tag=t.id and nt.id_node=n.id) as 'addr_street',
	(select value from tag as t join node_tags as nt on t.key='addr:postcode' and nt.id_tag=t.id and nt.id_node=n.id) as 'addr_postcode',
	(select value from tag as t join node_tags as nt on t.key='addr:city' and nt.id_tag=t.id and nt.id_node=n.id) as 'addr_city',
	(select value from tag as t join node_tags as nt on t.key='addr:country' and nt.id_tag=t.id and nt.id_node=n.id) as 'addr_country'
from node as n
join node_tags as nt1 on n.id = nt1.id_node
join tag as t1 on nt1.id_tag = t1.id and t1.key='amenity' and t1.value in ('restaurant','cafe','fast_food','pub','bar','biergarten')


UNION          -- l'union peut être supprimée lorsque l'on recherche simplement 2 listes consécutives (sans réutilisation SQL ultérieure).


select 
	'way' as 'element', w.id as id, 
	(select X(Centroid(PolyFromText('POLYGON((' || group_concat(X(n.coord) || ' ' || Y(n.coord),',') || '))', 4326)))
	 from node as n
	 join way_nodes as wn on n.id = wn.id_node and wn.id_way = w.id
	 order by wn.rang) as lon,
	(select Y(Centroid(PolyFromText('POLYGON((' || group_concat(X(n.coord) || ' ' || Y(n.coord),',') || '))', 4326)))
	 from node as n
	 join way_nodes as wn on n.id = wn.id_node and wn.id_way = w.id
	 order by wn.rang) as lat,
	(select name from user where user.id=w.uid) as 'user',
	w.timestamp as 'timestamp', t1.value as 'amenity',
	(select value from tag as t join way_tags as wt on t.key='name' and wt.id_tag=t.id and wt.id_way=w.id) as 'name',
	(select value from tag as t join way_tags as wt on t.key='cuisine' and wt.id_tag=t.id and wt.id_way=w.id) as 'cuisine',
	(select value from tag as t join way_tags as wt on t.key='operator' and wt.id_tag=t.id and wt.id_way=w.id) as 'operator',
	(select value from tag as t join way_tags as wt on t.key='source' and wt.id_tag=t.id and wt.id_way=w.id) as 'source',
	(select value from tag as t join way_tags as wt on t.key='description' and wt.id_tag=t.id and wt.id_way=w.id) as 'description',
	(select value from tag as t join way_tags as wt on t.key='opening_hours' and wt.id_tag=t.id and wt.id_way=w.id) as 'opening_hours',
	(select value from tag as t join way_tags as wt on t.key in ('url','web','contact:website') and wt.id_tag=t.id and wt.id_way=w.id) as 'website',
	(select value from tag as t join way_tags as wt on t.key in ('phone','contact:phone') and wt.id_tag=t.id and wt.id_way=w.id) as 'phone',
	(select value from tag as t join way_tags as wt on t.key='addr:housenumber' and wt.id_tag=t.id and wt.id_way=w.id) as 'addr_housenumber',
	(select value from tag as t join way_tags as wt on t.key='addr:street' and wt.id_tag=t.id and wt.id_way=w.id) as 'addr_street',
	(select value from tag as t join way_tags as wt on t.key='addr:postcode' and wt.id_tag=t.id and wt.id_way=w.id) as 'addr_postcode',
	(select value from tag as t join way_tags as wt on t.key='addr:city' and wt.id_tag=t.id and wt.id_way=w.id) as 'addr_city',
	(select value from tag as t join way_tags as wt on t.key='addr:country' and wt.id_tag=t.id and wt.id_way=w.id) as 'addr_country'
from way as w
join way_tags as wt1 on w.id = wt1.id_way
join tag as t1 on wt1.id_tag = t1.id and t1.key='amenity' and t1.value in ('restaurant','cafe','fast_food','pub','bar','biergarten');