Corrigé Phase 1 — Reconnaissance
Solutions des exercices. Cliquez pour révéler.
T0a — SQL : GROUP BY, HAVING, ORDER BY
Solution
```sql -- Combien d'entrées par catégorie ? SELECT categorie, count(*) AS nb FROM zone_d_activite_ou_d_interet GROUP BY categorie ORDER BY nb DESC; -- Quelles catégories ont plus de 10 000 entrées ? SELECT categorie, count(*) AS nb FROM zone_d_activite_ou_d_interet GROUP BY categorie HAVING count(*) > 10000 ORDER BY nb DESC; -- Quelles natures dans la catégorie 'Santé' ? SELECT nature, count(*) AS nb FROM zone_d_activite_ou_d_interet WHERE categorie = 'Santé' GROUP BY nature ORDER BY nb DESC; ```Exercice : gendarmeries et casernes
Solution
```sql SELECT nature, count(*) AS nb FROM zone_d_activite_ou_d_interet WHERE categorie = 'Administratif ou militaire' AND nature IN ('Gendarmerie', 'Caserne', 'Camp militaire non clos') GROUP BY nature; ```T0b — SQL : Jointures spatiales
Solution — Équipements dans zones industrielles
```sql SELECT DISTINCT e.nature, e.nature_detaillee, z.nature AS zone_nature FROM equipement_de_transport e JOIN zone_d_activite_ou_d_interet z ON ST_Intersects(e.geometrie, z.geometrie) WHERE z.nature = 'Zone industrielle' LIMIT 20; ```Exercice : aérodromes et hôpitaux
Solution
```sql SELECT a.toponyme AS aero, z.toponyme AS hopital, ST_Distance(a.geometrie, z.geometrie) AS distance_m FROM aerodrome a JOIN zone_d_activite_ou_d_interet z ON ST_DWithin(a.geometrie, z.geometrie, 2000) WHERE z.nature IN ('Hôpital', 'Établissement hospitalier'); ```T0c — Cypher : premiers pas
Solution — Lister l'ontologie
```cypher -- Lister les niveaux de l'ontologie MATCH (n:ClasseOntologie) RETURN n.obj_type, n.name LIMIT 20; -- Trouver les enfants directs de "Tronçon de route" MATCH (d)-[:EST_SOUS_TYPE_DE]->(o:Object {name: 'Tronçon de route'}) RETURN d.obj_type, d.name; -- Compter les nœuds par label MATCH (n) RETURN labels(n)[0] AS label, count(*) ORDER BY count DESC; ```Exercice : sous-types de “Construction ponctuelle”
Solution
```cypher MATCH (d:Detail)-[:EST_SOUS_TYPE_DE]->(o:Object {name: 'Construction ponctuelle'}) RETURN d.obj_type, d.name; // Compter : MATCH (d:Detail)-[:EST_SOUS_TYPE_DE]->(o:Object {name: 'Construction ponctuelle'}) RETURN count(d) AS nb_sous_types; ```T1 — Explorer l’ontologie BDTOPO
Solution — Hiérarchie récursive
```sql WITH RECURSIVE hierarchy AS ( SELECT id, name, obj_type, parent_id, 0 AS depth FROM bdtopo_ontology WHERE name = 'Tronçon de route' UNION ALL SELECT child.id, child.name, child.obj_type, child.parent_id, h.depth + 1 FROM bdtopo_ontology child JOIN hierarchy h ON child.parent_id = h.id ) SELECT depth, obj_type, name FROM hierarchy ORDER BY depth; ```T2 — Sélectionner les POIs de votre rôle
Solution — Template d'insertion
```sql INSERT INTO mission_pois (role, source, cleabs, categorie, nature, nom, geom) SELECT 'votre_role', 'source_table', cleabs, categorie, nature, toponyme, ST_Force2D(geometrie) FROM votre_table WHERE vos_filtres; ``` Consultez votre page rôle pour les tables et filtres spécifiques : [Attaque](/cours-manticore/roles/attaque/), [Défense](/cours-manticore/roles/defense/), [Ravitaillement](/cours-manticore/roles/ravitaillement/), [Énergie](/cours-manticore/roles/energie/).T3 — Ajouter des critères de criticité
Solution — Critères par type
```sql -- Postes électriques critiques UPDATE mission_pois SET categorie = 'critique' WHERE role = 'energie' AND source = 'poste_ht' AND CAST(nature AS INTEGER) <= 3; -- Aérodromes majeurs (déjà filtré à l'insertion) -- Zones militaires (déjà filtré à l'insertion) -- Vérification SELECT role, source, categorie, count(*) FROM mission_pois GROUP BY role, source, categorie ORDER BY role, source; ```T4 — Clusteriser les POIs (ST_ClusterDBSCAN)
Solution
```sql WITH clustered AS ( SELECT *, ST_ClusterDBSCAN(geom, eps := 2000, minpoints := 2) OVER () AS cid FROM mission_pois ) SELECT cid, COUNT(*) FROM clustered WHERE cid IS NOT NULL GROUP BY cid ORDER BY count DESC; ``` **Variante eps** : ```sql -- Tester plusieurs seuils SELECT 1000 AS eps, count(DISTINCT cid) AS nb_clusters FROM (SELECT *, ST_ClusterDBSCAN(geom, eps := 1000, minpoints := 2) OVER () AS cid FROM mission_pois) t WHERE cid IS NOT NULL UNION ALL SELECT 3000, count(DISTINCT cid) FROM (SELECT *, ST_ClusterDBSCAN(geom, eps := 3000, minpoints := 2) OVER () AS cid FROM mission_pois) t WHERE cid IS NOT NULL UNION ALL SELECT 5000, count(DISTINCT cid) FROM (SELECT *, ST_ClusterDBSCAN(geom, eps := 5000, minpoints := 2) OVER () AS cid FROM mission_pois) t WHERE cid IS NOT NULL; ```T5 — Partager vos POIs
Solution — Vérification
```sql SELECT role, source, count(*) FROM mission_pois GROUP BY role, source ORDER BY role, count DESC; ``` Chaque rôle doit apparaître avec au moins 1 source. Si un rôle est manquant → l'agent concerné n'a pas encore inséré ses POIs.T6 — Questions bonus par rôle
→ Voir Corrigé Rôles bonus