Suite

Géocodage inversé à l'aide de données OSM importées dans PostGIS

Géocodage inversé à l'aide de données OSM importées dans PostGIS


J'essaie d'inverser le géocodage de certains points à l'aide d'une importation OSM dans PostGIS. J'ai importé les données OSM (pour le Danemark, toutes les adresses doivent donc être disponibles) dans PostGIS en utilisant osm2pgsql en activant "addr:street" et "addr:housenumber". Toutes les adresses devraient maintenant être dans letable planet_osm_point. Ma table de points que je veux inverser géocoder est créée en utilisant

tour de mise à jour définie the_geom = st_transform(st_setsrid(st_makepoint(xcoord, ycoord),4326),4326)

Lorsque j'essaie d'utiliser le code suivant, j'obtiens des résultats très étranges où, pour autant que je sache, les adresses avec lesquelles je me retrouve sont soit aléatoires mais toujours dans la zone générale ou peuvent être déplacées de quelques kilomètres :

mettre à jour l'ensemble de la tour le plus proche = t."addr:housenumber", le plus proche_street = t."addr:street" de ( sélectionnez "addr:housenumber", "addr:street", pk from planet_osm_point, tour où st_dwithin(st_transform(planet_osm_point.way , 4326), tower.the_geom, 0.01) order by st_transform(planet_osm_point.way,4326) <-> tower.the_geom ASC ) t où tower.pk=t.pk;

Dans l'image ci-jointe, j'ai étiqueté les points avec le résultat du géocodage inversé et, à titre d'exemple, j'ai mis en évidence un point dont l'adresse est située plus au nord, un peu au-delà de la limite. Quelqu'un a-t-il une idée de ce que je fais mal?


Il est facile d'obtenir des effets inattendus en exécutant unMETTRE À JOURqui implique une jointure. Au fond de la documentation PostgreSQL pour UPDATE, vous pouvez trouver l'avertissement suivant :

Lorsqu'une clause FROM est présente, ce qui se passe essentiellement, c'est que la table cible est jointe aux tables mentionnées dans from_list, et chaque ligne de sortie de la jointure représente une opération de mise à jour pour la table cible. Lorsque vous utilisez FROM, vous devez vous assurer que la jointure produit au plus une ligne de sortie pour chaque ligne à modifier. En d'autres termes, une ligne cible ne doit pas être jointe à plus d'une ligne des autres tables. Si tel est le cas, une seule des lignes de jointure sera utilisée pour mettre à jour la ligne cible, mais celle qui sera utilisée n'est pas facilement prévisible.

Étant donné que votre requête d'origine génère potentiellement de nombreuses correspondances de rue pour chaque point, vous mettez à jour le point avec une correspondance aléatoire de ces correspondances. Le fait que vous commandiez la sous-requête n'aide malheureusement pas.

Vous pouvez résoudre le problème en mettant unDISTINCTdans tonDEclause pour s'assurer que vous n'obtenez qu'une seule correspondance ou, peut-être plus directement, en mettant unLIMITE 1après votreCOMMANDÉ PAR.

Un bon moyen d'éviter le problème (ce n'est pas très utile ici) est de retirer votre jointure de laDEet le transformer en une expression aprèsENSEMBLE, c'est à dire:

MISE À JOUR tower SET near_id = (SELECT id FROM planet_osm_point.way WHERE ST_DWithin(tower.geom, way.geom, 0.01) ORDER BY ST_Distance(tower.geom, way.geom) ASC LIMIT 1);

Cette méthode présente l'avantage que votre requête échouera si votre sous-requête renvoie plusieurs lignes. Mais si vous devez extraire plus d'une colonne de votre sous-requête, je ne pense pas qu'il existe un moyen d'en tirer parti.


Je ne sais pas vraiment pourquoi cela fonctionne, et j'aimerais que quelqu'un fournisse une vraie réponse avec le raisonnement sous-jacent, mais l'utilisation du code suivant semble fonctionner :

mettre à jour l'ensemble de la tour la plus proche = t."addr:housenumber", la plus proche_street = t."addr:street" à partir de ( sélectionnez distinct st_transform(planet_osm_point.way,4326) <-> tower.the_geom, "addr:housenumber", "addr : street", pk de planet_osm_point, tour où st_dwithin(st_transform(planet_osm_point.way, 4326), tower.the_geom, 0.01) ordre par st_transform(planet_osm_point.way,4326) <-> tower.the_geom ASC ) t où tower.pk= t.pk;

ÉDITER

La déclaration ci-dessous a beaucoup plus de sens car elle utilise lesélectionner distinct surexpression, sur la colonnepaquet, qui est un entier unique sérialisé. Donc pour chaque uniquepaquetvaleur dont j'obtiens le point le plus procheplanet_osm_point. Inspiré de Bostongis.

mettre à jour la tour set le plus proche = t."addr:housenumber", le plus proche_street = t."addr:street" de ( sélectionnez distinct on(tower.pk) tower.pk, "addr:housenumber", "addr:street"de planet_osm_point, tour où st_dwithin(st_transform(planet_osm_point.way, 4326), tower.the_geom, 0.01) ordre par st_transform(planet_osm_point.way,4326) <-> tower.the_geom ASC ) t où tower.pk=t.pk;


Voir la vidéo: Loading PostGIS with osm2pgsql - 2. 6 in webinar series