Archive pour la catégorie : SQL_DB2

Préambule

Cet article est une suite à l’article de Pierre-Louis BERTHOIN qui présente les fonctions géospatiales intégrées à DB2.

Choix de l’API

Sur le site https://adresse.data.gouv.fr/ En cliquant sur l’item « Outils et API », on accède librement à la documentation des API en rapport avec les adresses. Nous choisissons donc celle sobrement intitulée « API Adresse ». La documentation montre différentes manières d’utiliser cette API. Le retour est un geojson FeatureCollection respectant la spec GeoCodeJSON.

Récupération et manipulation des données

But du programme

Nous allons réaliser un programme qui permettra, en écrivant partiellement une adresse, de récupérer une adresse complète d’après une liste déroulante de 50 occurrences.

Nous choisirons pour notre programme une interrogation relativement simple et nous n’extrairons qu’une partie des données du geojson.

Nous écrirons les adresses dans un fichier, sous forme d’une fiche client contenant les éléments suivants :

• Identifiant (integer auto incrémenté)
• Raison Sociale (varchar)
• Adresse (varchar)
• Code Postal (varchar)
• Ville (varchar)
• Coordonnées géographiques (ST_POINT)

Préparation du fichier

create table GGEOLOC.MESCLIENTS
(ID int GENERATED ALWAYS AS IDENTITY (
START WITH 1 INCREMENT BY 1 
NO MINVALUE NO MAXVALUE     
NO CYCLE NO ORDER           
CACHE 20 ),
RAISOC varchar(64),
ADRESSE varchar(128),
CODEPOS varchar(16),
VILLE varchar(64),
COORDGEO QSYS2.ST_POINT);

Programme de saisie

**free
  ctl-opt dftactgrp(*no) ;

  // Fichiers
  dcl-f FORMCLIE workstn indds(DS_Ind) usropn;

  // Procédures
  dcl-pr  Touche_F4 EXTPGM('TOUCHE_F4');
    p_Sql char(1024) ;
    p_Titre char(35);
    p_Ret    char(116);
  end-pr;

  // Variables
  dcl-s reqSqlDelete varchar(2000);
  dcl-s reqSqlCreate varchar(2000);
  dcl-s reqSqlDrop   varchar(2000);

  dcl-s coordonees varchar(64) ;
  dcl-s queryapi varchar(64);
  dcl-s httpText varchar(256);

  dcl-s pIndicators Pointer Inz(%Addr(*In));


  // Pour F4 : liste des fichiers
  dcl-s w_Sql   char(1024) ;
  dcl-s w_Titre  char(35);
  dcl-s w_Ret    char(116);

  // DS Informations Programme
  dcl-ds *N PSDS;
    nom_du_pgm CHAR(10) POS(1);
    nom_du_prf CHAR(10) POS(358);
  end-ds;

  // Déclaration des indicateurs de l'écran
  Dcl-DS DS_Ind based(pIndicators);
    Ind_Sortie                        ind pos(3);
    Ind_Liste                         ind pos(4);
    Ind_Annuler                       ind pos(12);
    Ind_Valider                       ind pos(17);
    Ind_SFLCLR                        ind pos(40);
    Ind_SFLDSP                        ind pos(41);
    Ind_SFLDSPCTL                     ind pos(42);
    Ind_SFLEnd                        ind pos(43);
    Ind_DisplayCoord                  ind pos(80);
    Ind_RaisonS                       ind pos(81);
    Ind_5Lettres                      ind pos(82);
    Ind_CodePos                       ind pos(84);
    Ind_Ville                         ind pos(85);
    Indicators char(99)               pos(1);
  End-DS;

  // Paramètres en entrée
  dcl-pi *N;
  end-pi;

// SQL options --------------------------------------------- //
   Exec SQL
      Set Option
          Naming=*Sys,
          Commit=*None,
          UsrPrf=*User,
          DynUsrPrf=*User,
          Datfmt=*iso,
          CloSqlCsr=*EndMod;

//‚--------------------------------------------------------- //
// Contrôle taille écran
Monitor;
  Open FORMCLIE;
  On-Error;
    Dsply 'Nécessite un écran 27 * 132';
    *inlr=*on;
  Return;
EndMon;


  Dou Ind_Sortie or Ind_Annuler;

    znompgm = nom_du_pgm;
    znomprf = nom_du_prf;

    Exfmt FMT01;

    select ;
      when Ind_Sortie ;
        leave;
      when Ind_Annuler;
        leave;
      when Ind_Liste;
       if %len(%trim(zadresse)) <= 4 ;
         Ind_5Lettres = *on;
         iter;
       endif;
       traitementListe();
      when Ind_Valider;
       if zraisoc = *blanks;
         Ind_RaisonS = *on ;
       endif;
       if zcodpos = *blanks;
         Ind_CodePos= *on ;
       endif;
       if zville = *blanks;
         Ind_Ville = *on ;
       endif;
       if %subst(indicators:81:4) <> '0000';
         iter ;
       endif;

       Exec SQL
         insert into MESCLIENTS (RAISOC, ADRESSE, CODEPOS, VILLE, COORDGEO)
           values (:zraisoc, :zadresse, :zcodpos, :zville,
           QSYS2.ST_POINT(:zlongit, :zlatit)) ;

       Ind_DisplayCoord = *off;
       clear FMT01;

    endsl ;

  Enddo;

  *inlr = *on;

  //======================================================================== //
  // Procédures                                                              //
  //======================================================================== //

  //‚------------------------------------------------------------------------ //
  // Nom    : rechercheAdresse                                               //
  // But    : lister des adresses recueillies via une API                    //
  //          à partir d'une chaine de plus de 4 caractères                  //
  // Retour : N/A                                                            //
  //‚------------------------------------------------------------------------ //
  dcl-proc rechercheAdresse ;
    dcl-pi *n ;
      l_httpText varchar(256) value;
    end-pi;


    reqSqlDrop = 'drop table QTEMP/WADRESSE' ;
    Exec sql Execute immediate :reqSqlDrop ;

    reqSqlCreate = 'create table QTEMP/WADRESSE' +
       ' (address varchar(128), numero varchar(8), street varchar(128), ' +
       'postcode varchar(16), city varchar(64), coordinates blob)' ;
    Exec sql Execute immediate :reqSqlCreate ;

    reqSqlDelete = 'delete from QTEMP/WADRESSE' ;
    Exec sql Execute immediate :reqSqlDelete ;

    Exec sql
     insert into QTEMP/WADRESSE
     (select ltrim(ifnull(numero, '') || ' ' ||
             coalesce(street, locality, '') || ' ' ||
             postcode || ' ' || city),
             ifnull(numero, ''), coalesce(street, locality, ''),
             postcode,
             city,
             QSYS2.ST_POINT(longitude, latitude)
       from json_table(QSYS2.HTTP_GET(:l_httpText, ''), '$.features[*]'
        COLUMNS
        (numero varchar(8) PATH '$.properties.housenumber',
        street varchar(128) PATH '$.properties.street',
        locality varchar(128) PATH '$.properties.locality',
        name varchar(128) PATH '$.properties.name',
        municipality varchar(128) PATH '$.properties.municipality',
        postcode varchar(8) PATH '$.properties.postcode',
        city varchar(64) PATH '$.properties.city',
        longitude float PATH '$.geometry.coordinates[0]',
        latitude float PATH '$.geometry.coordinates[1]'))
        );

  end-proc ;

  //‚------------------------------------------------------------------------ //
  // Nom    : traitementListe                                                //
  // But    : Affichage d'une liste de 50 adresses maximum                   //
  // Retour : N/A                                                            //
  //‚------------------------------------------------------------------------ //
  dcl-proc traitementListe ;

    queryapi = %scanrpl(' ':'+':%trim(zadresse)) ;
    httpText ='https://api-adresse.data.gouv.fr/search/?q=' +
              queryapi + '&limit=50' ;
    rechercheAdresse(httpText);
    clear w_ret ;
    w_sql =
      'select address from QTEMP/WADRESSE' ;

    w_titre = 'Adresses proposées';

    touche_f4(W_Sql: W_titre : w_ret) ;
    if (w_ret  <> ' ') ;
      clear zadresse;
      clear zcodpos;
      clear zville;

      Exec SQL
        select
          ltrim(ifnull(numero, '') || ' ' || street), postcode, city,
          REPLACE(
             REPLACE(QSYS2.ST_asText(COORDINATES), 'POINT (', ''), ')', '')
            into :zadresse, :zcodpos, :zville, :coordonees
          from QTEMP.WADRESSE
          where address = :w_ret ;

      if sqlcode = 0 ;
        Ind_DisplayCoord = *on ;
        zlongit = %dec(%subst(coordonees: 1 : %scan(' ':coordonees)):15:12) ;
        zlatit =  %dec(%subst(coordonees: %scan(' ':coordonees) + 1
                        : %len(%trim(coordonees)) - %scan(' ':coordonees)):15:12) ;
      endif;

    endif ;

  end-proc ; 

Quelques explications sur les fonctions SQL utilisées

Tout d’abord nous choisissons de ne pas utiliser la propriété « label » proposée par l’API Adresse. En effet, si celle-ci semble pratique de prime abord, elle n’est pas toujours significative (voir photo du milieu qui où elle ne contient que le nom de la municipalité)

Nous, préférerons donc reconstituer cette adresse en concaténant des zones que l’on retrouve dans chaque occurrence du fichier JSON.

QSYS2.ST_POINT : Cette fonction est utilisée lors de la collecte des données fournie par l’API Adresse.

Elle permet de transformer les coordonnées longitude, latitude en une variable de type BLOB qui représente un point précis et qui est utilisable par les fonctions Géospatiales du SQL.

QSYS2.ST_ASTEXT : Cette fonction permet de transformer un champ géométrique (ST_POINT, ST_LINESTRING, ST_POLYGON, …) en un champ WKT (well-known-text) qui nous sera plus compréhensible.

Cinématique du programme

Ce programme est un simple écran qui nous permet la saisie d’un formulaire avec la possibilité de rechercher ou compléter une adresse en utilisant la touche F4 (la fonction externe appelée n’est pas décrite dans cet article). Une fois le formulaire validé, on l’efface.

Tout d’abord on commence à remplir le formulaire mais on ne connait pas précisément l’adresse.

Donc, après avoir tapé un morceau d’adresse on presse F4

On valide, le formulaire est alors complétement rempli

On presse F17 pour valider celui-ci (et réinitialiser l’écran).

Vérification des données enregistrées

Version BLOB

Version WKT (well-known-text)

Conclusion

Nous avons montré ici un exemple simple de l’utilisation d’une API couplée avec les fonctions géospatiales proposées par IBM. Il est possible d’envisager des requêtes plus complexes incluant le code postal, la ville ou encore le type de donnée (rue, lieu-dit ou municipalité). On peut aussi envisager des requêtes d’après les coordonnées géographiques pour retrouver une adresse. Le champ des possibles, comme le monde, est vaste …