SQL et MAPINFOA Introduction Sous MapInfo, il est souvent fait référence aux sélections pour extraire des informations d’une table. Il est alors possible d’utiliser le menu Sélection ou Sélection SQL du menu table. Dans ce document, nous allons développer le terme de SQL et essayer d’entrevoir toute la puissance de ce langage. B Le Langage SQL Le terme de SQL vient de l’anglais Strutured Query Language qui pourrait se traduire par langage de requêtes structurées. Ce langage est devenu universel et incontournable puisqu’on le retrouve dans tous les systèmes actuels de gestion de bases de données du marché. Son utilisation, sous MapInfo, passe par une boîte de dialogue. qui va traduire les choix de l’utilisateur en SQL. Il est aussi possible de passer par la saisie complète de la sélection dans la fenêtre MapBasic (Affichage > Afficher Fenêtre Mapbasic).
La sélection ci-dessus est la sélection la plus simple possible. En syntaxe SQL, elle équivaut à : Select * from Deparmt Ce qui veut dire en clair et en français : "Sélection de tous les enregistrements de la table Departmt ", tout en conservant les champs de cette même table. Dans la boîte de dialogue ci-dessus, trois mots clefs ont été ajoutés en rouge pour mieux comprendre la construction d’une sélection. Deux mots clefs du langage ont été ici utilisés ; Select et From. Ces clauses Select et From sont nécessaires et permettent de spécifier respectivement la liste des colonnes à extraire et la liste des tables à partir desquelles les données seront extraites. Toutes les autres clauses sont optionnelles et servent à affiner la sélection et à offrir d’autres services que nous détaillerons au fur et à mesure de l’avancement du document. I Clause SELECT : Travailler dans la dimension horizontale du tableau
Les informations saisies dans la clause Select permettent de spécifier les colonnes à visualiser. Si * est utilisé, il doit être seul. Cette condition doit être obligatoirement respectée. Il est possible de saisir :
Il est possible d’ajouter dans une clause la colonne Obj qui désigne le type d’objet sur la carte pour chaque enregistrement. Select Nom, Obj from Departmt : Sélection du Nom et du type d’objet de chaque enregistrement de la table Departmt Il est également possible de créer des colonnes qui seront le résultat d’un calcul sur plusieurs colonnes de la table d’origine. Select Nom, Pop90/Sup_km2 from Departmt : cette Sélection affichera dans un tableau une colonne pour le nom et une autre avec la densité de population. On peut modifier cette clause pour changer le nom de la colonne de calcul et l’appeler "densité": Select Nom, Pop90/Sup_km2 "Densite" from Departmt
II Clause FROM : Les informations saisies dans la clause From permettent de spécifier le nom de la table qui contient les données à sélectionner. Cette clause est obligatoirement remplie par au moins un nom de table. Nous verrons plus loin que l’on peut faire référence à plusieurs tables à la fois.
III Clause ORDER BY : La clause ORDER BY dans une sélection permet de spécifier un ordre de tri dans le tableau résultat. Trier le résultat sur la colonne Nom pour obtenir un classement par ordre alphabétique. Select Nom, Pop90 from Departmt Order By Nom Il est possible de spécifier plusieurs colonnes de tri. Dans un premier temps trier le résultat sur la colonne Region et ensuite pour les départements d’une même région les classer par ordre alphabétique du Nom. Select Region, Nom, Pop90 from Departmt Order By Region, Nom Il est aussi possible d’utiliser des alias pour faire référence à une colonne. Pour cela spécifier le terme Coln où n indique la position du champs dans la liste de Select Trier le résultat sur le calcul de la densité en deuxième position dans la liste de Select. Select Nom, Pop90/Sup_km2 "Densite" from Departmt Order By Col2 La clause ORDER BY peut aussi ici faire directement référence à la chaîne "Densite". Attention de bien respecter l’orthographe de la chaîne. Select Nom, Pop90/Sup_km2 "Densite" from Departmt Order By "Densite" Par défaut le résultat est trié par ordre croissant (asc). Pour spécifier l’ordre décroissant, ajouter le mot clé desc après le nom de la colonne. Select Nom, Pop90 from Departmt Order By Pop90 Desc IV Clause INTO : La clause INTO dans une sélection permet de spécifier un nom au tableau résultat. Par défaut dans la boîte de dialogue de sélection SQL de MapInfo, il est précisé le terme Selection. Ce terme Selection laisse à MapInfo le soin de nommer par QueryN le résultat, où N est un numéro d’ordre. Nous pouvons donc entrer ici notre propre nom de sélection. Select Nom, Pop90 from Departmt Order By Nom Into Dpt_par_Nom Quelques restrictions :
V Clause WHERE : Travailler dans la dimension verticale du tableau La clause Where d’une sélection sert à spécifier la condition de sélection des enregistrements. Dans la boîte de dialogue de sélection SQL de Mapinfo le terme est critère. C’est une clause optionnelle. La condition se fait à l’aide de la clause Where suivie d’une ou plusieurs conditions. Plusieurs conditions peuvent être combinées à l’aide des opérateurs logiques And et Or. L’opérateur logique Not peut être utilisé avant un critère ou une partie d’un critère pour spécifier la négation. Nous avons groupé les conditions de SQL en quatre familles :
a) Les conditions de comparaison Une condition de comparaison permet de comparer une colonne ou une expression à une autre colonne ou une valeur. Sélectionner tous les départements dont la population est supérieure à 1 000 000 d’habitants : Select * from Departmt where Pop90 > 1 000 000 Pop90 représente la colonne, > représente l’opérateur, 1 000 000 représente la valeur.
Pour un objet de type Ligne ou Point ObjectGeography(Obj,ExpNum) Pour ExpNum 1La fonction retourne la coordonnée X du point ou le X de départ de la ligne. 2 La fonction retourne la coordonnée Y du point ou le Y de départ de la ligne. Select ObjectGeography(Obj,1), ObjectGeography(Obj,2) from Ville La sélection ci-dessus permet de récupérer les coordonnées X et Y des points des villes (sur un objet de type point, on peut aussi utiliser la fonction CentroidX(Obj)).
Il est possible de spécifier plusieurs conditions. Select * from Commune where Pop90 > 5 000 And Superficie > 1 500 Les critères sont séparés par un opérateur de liaison And ou Or. Il est possible de spécifier plus de deux critères. Si des And et Or sont utilisés dans le même critère, il peut être important de placer des parenthèses pour donner des priorités.
b) Les sélections avec agrégation GROUPE BY Cette clause vous permet de créer des sous totaux dans votre table de requête. Si vous indiquez un nom de colonne (ou plusieurs noms de colonnes séparés par des virgules) dans cette clause, MapInfo calcule les sous totaux des résultats de votre requête, et ne vous présente que ces sous totaux, plutôt que toutes les lignes de votre table. Cette clause est à associer avec une liste d’agrégats dans la liste de Select. Les différentes fonctions d’agrégations :
c) Les conditions de jointure Une jointure est un lien entre deux tables disposant d’au moins une colonne commune sémantique ou d’une relation géographique. L’opération de jointure consiste à créer une table temporaire composée de lignes satisfaisant la condition de jointure. Nous avons groupé les conditions de jointure en deux familles :
Les jointures sémantiques
. Ici la colonne Dept de la table Ville_10 correspond bien à la colonne Code de la table Departmt. Pour sélectionner les Villes du département de l’Allier Select Ville_10.Code, Ville_10.Nom from Ville_10, Departmt where Ville_10.Dept=Departmt.Code And Departmt.Nom Like "ALLIER" Nous remarquons dans la sélection ci-dessus que dans ce type de sélection, il faut indiquer devant le nom de la colonne, le nom de la table séparé par un ".". Pour pouvoir placer une jointure il faut énumérer dans la clause from la liste des tables, ici Ville_10, Departmt. Ville_10 est placé en premier pour indiquer que le résultat doit être une liste de villes. La jointure est indiquée dans la clause Where par Ville_10.Dept=Departmt.Code. Le deuxième critère est un critère de sélection classique. Pour connaître dans quel département est la ville de Poitiers Select Departmt.Nom, Ville_10.Nom From Departmt, Ville_10 Where Departmt.Code = Ville_10.Dept And Ville_10.Nom Like "Poitiers" Nous remarquons ci-dessus que par rapport à la sélection précédente, l’ordre d’énumération dans la clause From est très important. Le résultat sera un département. Les jointures géographiques
Une jointure est un lien entre deux tables. Si la liaison par les colonnes est impossible, il est possible de chercher une relation géographique entre les objets de la carte. Ici on voit que les points représentant les villes sont à l’intérieur des polygones représentant les départements ou que les polygones contiennent les points. Les deux sélections précédentes pourraient se traduire de la façon suivante Pour sélectionner les Villes du département de l’Allier Select Ville_10.Code, Ville_10.Nom From Ville_10, Departmt Where Ville_10.obj Within Departmt.obj And Departmt.Nom Like "ALLIER" La jointure Ville_10.obj Within Departmt.obj indique que l’objet de la ville (le point) est à l’intérieur de l’objet de département (le polygone) Pour connaître dans quel département est la ville de Poitiers Select Departmt.Nom From Departmt, Ville_10 Where Departmt.obj Contains Ville_10.obj And Ville_10.Nom Like "Poitiers" La jointure Departmt.obj Contains Ville_10.obj indique que l’objet de département (le polygone) contient l’objet de ville (le point)
Les opérateurs géographiques Within A l’intérieur
A contient entièrement D et C d) Les conditions avec sous-requête Le SQL permet de comparer une expression ou une colonne au résultat d’une autre sélection SELECT. Cette condition est dite condition de sous-requête et les deux requêtes sont dites requêtes imbriquées. Sous MapInfo seule deux niveaux de sélection sont autorisés. A partir d’une sélection avec jointure vue précédemment. Pour sélectionner les Villes du département de l’Allier Select Ville_10.Code, Ville_10.Nom from Ville_10, Departmt where Ville_10.Dept=Departmt.Code And Departmt.Nom Like "ALLIER" Le même résultat pourrait être obtenu avec une sous-requête Select * from Ville_10 Where Dept = (Select Code from Departmt Where Nom Like "ALLIER") La sous-requête, entre parenthèses, renvoie le Code du département dont le nom est Allier à la sélection principale qui va le comparer aux valeurs de la colonne Dept de Ville_10. L’utilisation d’une sélection avec requêtes imbriquées est beaucoup plus performante qu’une requêtes classique avec jointure. Les temps de réponse peuvent être divisés dans des proportions très importantes sur des tables ayant de très nombreux enregistrements. La sous-requête peut renvoyer un objet. La requête principale utilise alors un opérateur spatial : Select * from Ville_10 Where Obj Within (Select Obj from Departmt Where Nom Like "ALLIER") La sous-requête peut utiliser des fonctions d’agrégations : Select Nom from Departmt Where Pop90 = (Select Max(Pop90) from Departmt) La sous-requête, entre parenthèses, renvoie la valeur de la population du département le plus peuplé. La sélection principale trouve alors le nom du département concerné. On remarque ci-dessus que la sélection principale et la sous-requête font appel à la même table. Si la sous-requête renvoie plus d’une valeur (message d’erreur de MapInfo), il est indispensable d’ajouter le mot clé Any devant la clause (Select . Select Nom from Departmt Where Pop90 > Any (Select Avg(Pop90) from Departmt) Il est possible dans la sous-requête d’inclure la construction d’un objet. Select * from Ville_10 Where Obj Within Any (Select Buffer(Obj,12,2, "km") From F_Hydro Where Nom Like "Loire") La sous-requête renvoie la construction d’un objet Tampon de 2 km autour de la Loire et la sélection principale recherche tous les objets de Ville_10 à l’intérieur de cette zone.
DES EXEMPLES
1°) Sélectionner les départements
dont la population est supérieure à 1000000 hab. Select * From Departmt Where Pop90 > 1000000 Order By Pop90 Into POP1M 2°) Sélectionner les départements
dont la population est supérieure à 1000000 hab. Select Nom, Code, Pop90/Sup_km2 From Departmt Where Pop90 > 1000000 Order By Pop90 Desc Into POP1M 3°) Sélectionner les départements
dont la population est supérieure à 1000000 hab. Select Nom, Code, Pop90/Sup_km2 "Densite" From Departmt Where Pop90 > 1000000 Order By Pop90 Desc Into POP1M 4°) Sélectionner les départements dont la population est supérieure à 1000000 hab et la superficie supérieure à 6000 km2. Select * From Departmt Where Pop90 > 1000000 And Sup_km2 > 6000 5°) Sélectionner les Villes dont le nom comporte le mot SAINT. Select * From Ville_10 Where Nom Like "%Saint%" 6°) Sélectionner les départements 86, 44, 75. Select * From Departmt Where Code in ("86", "44", "75") 7°) Sélectionner les départements dont la population est comprise entre 500000 et 1000000 hab. Select * From Departmt Where Pop90 Between 500000 And 1000000 8°) Calculer la somme et la moyenne de la population de tous les départements. Select Sum(Pop90), Avg(Pop90) From Departmt 9°) Regrouper les villes par département et calculer la somme des populations et compter les villes par département. Select Sum(Pop90), Count(*) From Ville_10 Group By Dept 10°) Sélectionner toutes les villes qui sont dans des départements de plus de 1000000 hab. Select * From Ville_10, Departmt Where Ville_10.Obj Within Departmt.Obj And Departmt.Pop90 > 1000000 11°) Sélectionner les départements traversés par la LOIRE Select * From Departmt, F_Hydro Where Departmt.Obj Intersects F_Hydro.Obj And F_Hydro.Nom = "LOIRE" 12°) Sélectionner toutes les villes qui sont dans des départements traversés par la LOIRE Select * From Ville_10, Departmt, F_Hydro Where Ville_10.Obj Within Departmt.Obj And Departmt.Obj Intersects F_Hydro.Obj And F_Hydro.Nom = "LOIRE" 13°) Refaire la 10°) avec une sous-sélection. Select * From Ville_10 Where Obj Within (Select Obj From Departmt Where Pop90 > 1000000) 14°) Refaire la 12°) avec une sous-sélection. Select * From Ville_10, Departmt Where Ville_10.Obj Within Departmt.Obj And Departmt.Obj Intersects (Select Obj From F_Hydro Where Nom = "LOIRE") 15°) Sélectionner les départements dont la population est supérieure à la moyenne des populations. Select * From Departmt Where Pop90 > (Select Avg(Pop90) From Departmt) 16°) Sélectionner les villes du département le plus peuplé Select * From Departmt Where Pop90 = (Select Max(Pop90) From Departmt) 17°) Sélectionner les villes à moins de 20 km d’une rivière sélectionnée manuellement sur la carte. Select * From Ville_10 Where Obj Within (Select Buffer(Obj,12 ,20, "km") From selection) Une petite dernière pour nos amis de l'environnement 18°) Calculer la surface d'intersection entre les communes et les zones Natura 2000 Select Natura2000.Nom, Commune.Nom, AreaOverlap(Natura2000.obj, Commune.obj) From Natura2000, Commune Where Natura2000.obj intersects Commune.obj Attention c'est long!!!!et il y a des problèmes d'unités (vois la rubrique Astuce) |