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 …
