Importer du GPX dans PostGIS

Lucidiot — Informatique — 2021-06-24
Et c'est reparti pour un tour !

Comme je le disais précédemment, tous mes efforts pour importer du GPX dans SpatiaLite étaient vains. Je me suis assez rapidement heurté à moult limites :

La quantité de données de scans Wi-Fi à importer est importante et atteint les limites du raisonnable pour SQLite ;
Il n'est pas facile ni rapide de déterminer les coordonnées GPS les plus proches d'un scan Wi-Fi donné (ce qui fera l'objet d'un autre article) ;
Je n'ai pas trouvé d'interface graphique vraiment pratique pour naviguer dans les données, notamment afficher une carte des réseaux Wi-Fi.

À l'inverse, avec mon expérience passée notamment dans mon boulot avec PostgreSQL et PostGIS, je sais que ça règle tous mes problèmes :

Des millions de lignes, c'est de la rigolade ;
Il existe un opérateur de proximité <-> qui peut permettre de rechercher les coordonnées les plus proches d'un point de vue temporel d'un scan Wi-Fi ;
pgAdmin prend en charge l'affichage d'une carte OpenStreetMap directement à partir d'une requête renvoyant des données géographiques ;
On peut utiliser la base de données à plusieurs.

Ce dernier point est probablement celui qui justifie le plus le passage à PostgreSQL ; je ne suis pas seul dans ce coup et envoyer des fichiers par mail ou avec des hébergeurs temporaires comme 0x0.st ou n'avoir qu'une seule personne pouvant explorer les données est assez limitant. Mon meilleur ami, qui a été motivé par Brainshit pour créer son propre blog, m'a du coup gentiment donné des identifiants vers une instance de pgAdmin sur son serveur.

Il me faut donc tout reprendre à zéro, et importer du GPX dans PostgreSQL. La bonne nouvelle, c'est que cette fois on a un support natif pour XML, et même qu'il est plutôt performant. Rappelons que PostgreSQL bat les bases NoSQL lorsqu'il s'agit d'indexer du JSON…

Nous allons d'abord faire comme pour SQLite et envoyer tout notre XML d'un seul coup dans une table temporaire. Cette fois-ci, pas d'extension à activer, rien, on va juste utiliser la fonction xml :

CREATE TEMPORARY TABLE gpx_data AS SELECT xml $$
<gpx>…</gpx>
$$ AS data;

Ensuite, on pourrait exploiter ce XML avec la fonction xpath qui renverra un tableau de xml avec tous les résultats. On aurait alors besoin de faire 4 jointures comme on l'a fait dans SQLite, et d'utiliser unnest pour transformer le tableau en des lignes de résultats :

-- Don't do this.
SELECT UNNEST(XPATH('//gpx:trkpt/gpx:time', data))::timestamptz AS timestamp FROM gpx_data
LEFT JOIN SELECT UNNEST(XPATH('//gpx:trkpt/@lon', data))::double AS lon FROM gpx_data
LEFT JOIN SELECT UNNEST(XPATH('//gpx:trkpt/@lat', data))::double AS lat FROM gpx_data
LEFT JOIN SELECT UNNEST(XPATH('//gpx:trkpt/gpx:hdop', data))::integer AS precision FROM gpx_data;

Mais pourquoi faire ça quand on peut utiliser la fonction xmltable, dont l'exact but est de nous éviter ce genre de monstres ? Cette fonction reçoit une liste d'espaces de noms, histoire de nous éviter les problèmes de dflt de la dernière fois, une première expression XPath qui permet de sélectionner un nœud, puis des déclarations de colonnes avec des types, et une expression XPath par colonne pour indiquer comment récupérer cette colonne particulière dans chaque nœud. Voyons ce que ça donne avec nos 4 colonnes :

SELECT * FROM gpx_data, XMLTABLE(
    XMLNAMESPACES('http://www.topografix.com/GPX/1/1' AS gpx),
    '//gpx:trkpt' PASSING data
    COLUMNS
        timestamp TIMESTAMP WITH TIME ZONE PATH 'gpx:time',
        lon DOUBLE PRECISION PATH '@lon',
        lat DOUBLE PRECISION PATH '@lat',
        precision INTEGER PATH 'gpx:hdop'
);

La syntaxe de cette fonction est vraiment particulière, avec moult mots-clés bien spécifiques à cette fonction, et peut-être qu'un jour je m'amuserai à essayer de voir comment fonctionne leur interpréteur de requêtes. Le mot-clé PASSING est suivi de la colonne qui contient le ou les documents XML qu'on veut utiliser, et précédé de l'expression XPath qui trouve l'ensemble des nœuds qui feront chacun une ligne. Ensuite, pour chaque colonne on indique un PATH pour dire comment récupérer la valeur dans chaque nœud. On remarque qu'en définissant mes types de colonnes, PostgreSQL s'occupera automatiquement de faire les conversions, donc j'ai déjà une sorte de validation des données. C'est bien plus élégant que mes hacks précédents.

On a déjà fait une grande partie du travail ! Il ne reste plus qu'à transformer tout ça en points compatibles avec PostGIS et à les sauvegarder dans une table qui a quasiment la même structure qu'avant ; un horodatage, un point géographique, et une précision en mètres.

INSERT INTO trip (timestamp, position, precision)
SELECT timestamp, ST_SetSRID(ST_MakePoint(lon, lat), 4326), precision
FROM gpx_data, XMLTABLE(
    XMLNAMESPACES('http://www.topografix.com/GPX/1/1' AS gpx),
    '//gpx:trkpt' PASSING data
    COLUMNS
        timestamp TIMESTAMP WITH TIME ZONE PATH 'gpx:time',
        lon DOUBLE PRECISION PATH '@lon',
        lat DOUBLE PRECISION PATH '@lat',
        precision INTEGER PATH 'gpx:hdop'
);

Voilà, c'est fini. Ça fera un article 4 fois plus court que SpatiaLite. Dommage, mais comment je vais faire pour avoir de la matière à publier ensuite ? Ah oui, il me suffit de commettre un peu plus de crimes. Quoi de mieux pour ça que des expressions régulières ?

Commentaires

Il n'y a pour l'instant aucun commentaire. Soyez le premier !