SQL et MAPINFO

 A 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 :

  • le caractère * équivaut à la sélection de tous les champs de la table.

        Select * from Deparmt

  • une énumération des colonnes séparées par des virgules :

      Select Nom, Pop90 from Departmt

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 :

  • Certains signes sont interdits (Espace, -, %, =, <, >, +, /, *, ….)
  • La chaîne ne doit pas commencer par un chiffre.
  • La chaîne ne doit pas être le nom d’une table ouverte.

 

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 :

  1. Les conditions de comparaison,
  2. Les sélections avec agrégation,
  3. Les conditions de jointure,
  4. Les conditions avec sous-requête.

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.

  • Les opérateurs de base

    Les opérateurs de comparaisons les plus courants offerts par SQL sont :

    = égal à
    <> différent de
    < inférieur à
    > supérieur à
    <= inférieur ou égal à
    >= supérieur ou égal à
    Like comme
    Between compris entre
    In dans

    La comparaison concerne aussi bien les valeurs numériques que les chaînes de caractères et les dates.
    Les chaînes de caractères doivent être obligatoirement encadrées par des guillemets.

    Select * from Departmt where Nom = "Vienne"

  • L’opérateur LIKE

    L’utilisation de l’opérateur = ne permet pas ‘d'à peu près’ dans la valeur qui suit.Il est souvent préférable d’utiliser Like sur les chaînes de caractères associées à des jokers.

    Select * from Commune where Nom Like "S%Etienne"

    Le Joker % remplace ici une chaîne de caractères qui peut aussi bien être aint- ou aint ou t- ou t. Il est possible de remplacer un nombre défini de caractères en utilisant _.

    Select * from Commune where Nom Like "Saint_Etienne"

    Ici le _ entre les deux mots permet de remplacer un espace ,un – ou tout autre caractères.

     

  • L’opérateur BETWEEN…AND…

    L’opérateur BETWEEN val1 AND val2 permet de comparer la valeur de l’expression située à gauche du mot clé BETWEEN à des valeurs dans l’intervalle défini par les deux expressions val1 et val2.

    Cet opérateur peut être précédé par la négation (NOT) pour inverser le résultat. Les expressions peuvent avoir un type numérique, caractères ou date.

    Select * from Commune where Pop90 Between 5 000 And 10 000

    Cette sélection peut être formulée en utilisant uniquement les opérateurs de comparaison suivants :

    Select * from Commune where Pop90 >= 5000 And Pop90 <= 1 0000

    Les expressions avec des valeurs de type Date se mettent entre guillemets.

    Select * from Departmt where Date between "26/10/1998" and "13/12/1998

 

  • L’opérateur IN

    L’opérateur IN permet de comparer la valeur de l’expression située à gauche du mot clef IN à la liste de valeurs comprises entre parenthèses. La condition de recherche est satisfaite quand l’expression est comprise dans la liste des valeurs .

    Select * from Commune where Nom In ("Paris", "Nice", "Lyon")

    Cette sélection peut être formulée en utilisant uniquement les opérateurs de comparaison suivants :

    Select * from Commune where Nom = "Paris" Or Nom = "Nice" Or Nom = "Lyon"

  • La colonne Obj

    Le mot clé Obj, dans une sélection, est utilisé comme une colonne, et fait référence à l’objet géographique sur la carte.

    Il permet, par exemple, de sélectionner tous les enregistrements d’une table qui n’ont pas d’objet sur la carte.

    Select * from Commune where Not Obj

    Ou alors sélectionner tous les objets de type polygone, polyligne, point, ligne ou texte

  • Select * from Departmt where Str$(obj) = "Region"
  • Select * from Departmt where Str$(obj) = "Polyline"
  • Select * from Departmt where Str$(obj) = "Point"
  • Select * from Departmt where Str$(obj) = "Line"
  • Select * from Departmt where Str$(obj) = "Text"

    Dans ces cinq exemples, on utilise une expression qui convertit la colonne Obj en caractères (Str$()) pour la comparer à une chaîne qui désigne le type d’objet.

    Select ObjectNodeX(Obj,1,1), ObjectNodeY(Obj,1,1), ObjectNodeX(Obj,1,ObjectInfo(Obj,20)), ObjectNodeY(Obj,1,ObjectInfo(Obj,20))

    from F_hydro

    La sélection ci-dessus permet de récupérer les coordonnées X et Y des points de départ et d’arrivé de chaque tronçon de rivière.

    Cette sélection fait appel à des références MapBasic pour le passage des différents paramètres aux fonctions.

    ObjectInfo(Obj, ExpNum) est une fonction qui renvoie une valeur numérique qui varie suivant le ExpNum

    ExpNum

  1. Pour le nombre de nœuds d’un polygone ou d’une polyligne
  2. Pour le nombre de section d’un polygone ou d’une polyligne (Objets composés)

        21+n Le nombre de nœuds de l’énième polygone ou polyligne d’un objet composé.

    ObjectNodeX(Obj, ExpNum1,ExpNum2) est une fonction qui renvoie une valeur numérique qui représente la coordonnée X d’un nœud du polygone ou d’une polyligne.

    ExpNum1 représente le numéro du polygone ou la polyligne (1 pour un objet simple)

    ExpNum2 représente le numéro du nœud (dans la sélection de départ on récupère le numéro du dernier nœud avec la fonction ObjectInfo).

    La fonction ObjectNodeY() renvoie la coordonnée Y d’un nœud.

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)).

     

  • Les sélections multicritères

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 :

  • Avg(Col)Moyenne des valeurs d’une colonne
  • Count(*)Compter les enregistrements
  • Min(Col)Plus petite valeur d’une colonne
  • Max(Col)Plus grande valeur d’une colonne
  • Sum(Col)Somme des valeurs d’une colonne

    Si aucune colonne n’est spécifiée dans la clause Order By, l’utilisation des fonctions d’agrégations dans la clause Select permet d’afficher des totaux sur la table.

    Select Sum(Pop90), Avg(Pop90), Min(Pop90), Max(Pop90), Count(*) from Departmt

    Le résultat de la sélection ci-dessus sera :

 

 

Si dans la clause Order By est spécifiée une colonne qui permet le regroupement d’informations, l’utilisation des fonctions d’agrégations dans la clause Select permet d’afficher des sous totaux sur la table.

  • Select Region, Sum(Pop90), Count(*) from Departmt Group By Region Order by Region

    Le résultat de la sélection ci-dessus sera un tableau avec un enregistrement par région avec ses calculs. La région, sa population totale, le nombre de département qui le compose et classé par ordre alphabétique de la région.

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
  • Les jointures géographiques.

 

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
Contains Contient
Entirely Withi Entièrement à l’intérieur
Contains Entirely Contient Entièrement
Intersects Intersecte 

 

 

 

 

 

 

 

A contient entièrement D et C
A contient B (si le centre de B est à l’intérieur de A)
A intersecte aussi B
A intersecte E
D et C sont entièrement à l’intérieur de A
B est à l’intérieur de A (si le centre de B est à l’intérieur de A)
E intersecte A
D intersecte E (si D est accroché sur un nœud de la polyligne E)

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.
Ordonner par la population (ordre croissant).
Nommer POP1M.

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.
Ordonner par la population (ordre décroissant).
Choisir les colonnes Nom, Code et calculer la densité.

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.
Ordonner par la population (ordre décroissant).
Choisir les colonnes Nom, Code et calculer la densité (nommer la colonne).

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)