Le portail francophone de la géomatique

Bonjour,

Pour la recherche de plus proches voisins (les n plus proches voisins "points" de chaque batiment), Postgis dispose d'un opérateur: ORDER by geom <-> geom (http://postgis.net/docs/geometry_distance_knn.html) et avec les versions récentes de PG et la clause LATERAL, vous pouvez faire des requêtes vraiment performantes avec !

Nicolas

Bonjour,

Une réponse qui n'a rien à voir avec postgis.

L'utilisation des données cadastrales au format Edigéo vous permettrait de simplifier le problème, car il existe une relation entre le numéro de voirie et la parcelle, et une relation entre parcelle et bâtiment.

@Nicolas : Merci pour le lien je ne connaissais pas ces fonctions.

Je suis en train de tester votre méthode, meonais. Je vais laisser tourner la requête pendant le week-end.
Par contre, je me pose quelques questions par rapport à votre requête :

- À quoi sert le rank() OVER()? Je ne l'ai jamais utilisé...Est-ce obligatoire dans cette requête?
- Pour lier vos deux tables, vous utilisez un st_dwithin(geom,geom,1500). Comment avez-vous choisi cette valeur de 1500?

Nicolas, pouvez-vous m'en dire un peu plus sur ce <->? Je ne sais pas comment l'utiliser malgré de nombreuses recherches sur le net où, je précise, je n'ai pas trouvé la moindre requête de ce genre pour rapprocher les objets de deux tables distinctes (donc se pose la question suivante: comment lier mes tables entre elles pour que la requête ne tourne pas pendant un mois, sachant que je ne peux pas utiliser le st_intersects, et que le geom&&geom me semble trop restrictif?... =P Meonais utilise un st_dwithin, ce n'est pas bête, on rapproche les objets qui sont distants de moins de 1500m). En effet, j'ai testé la requête suivante :

WITH tempa AS
(SELECT bati.id, sirene.gid, st_distance(bati.geom,sirene.geom) as distance
FROM a_detc_c17ll0108_jura.l_bati_groupe_bdt_039_2015 as bati, a_detc_c17ll0108_jura.base_sirene_39_062017 as sirene
WHERE bati.geom&&sirene.geom
ORDER BY bati.geom <-> sirene.geom)
UPDATE a_detc_c17ll0108_jura.l_bati_groupe_bdt_039_2015 as bat
SET sirene='oui'
FROM tempa
WHERE bat.id=tempa.id;

Certains bâtiments sont corrigés, mais la plupart qui n'avaient pas été croisés ne le sont toujours pas.

Je suis sûre que j'utilise mal votre proposition, malheureusement je ne trouve pas comment faire.
Le order by geom <-> geom permet de connaitre les n points les plus proches de mon bâti, mais à quoi correspond le n? Moi je voudrais que n soit égal à 1, cad l'unique point le plus proche de mon bâtiment...

En vous remerciant par avance.

Lucie D.

J'y suis allé à la truelle pour l'update

Pour etre exact, il faut aller un coup plus loin, et pour chaque batiment, ne garder que l'adresse la plus proche:
dans la capture précédente, l'update a ete fait sans tenir compte de la dist min entre un batiment et plusieurs adresses => ca a pris une adresse au pif.

La requete en update plus juste:

with tmp as (
    SELECT
      a.id AS adrid,
      t.batid,
      t.dist
    FROM adresse a
      CROSS JOIN LATERAL
                 (SELECT
                    b.id                        AS batid,
                    st_distance(b.geom, a.geom) AS dist
                  FROM batiment b
                  ORDER BY a.geom <-> b.geom
                  LIMIT 1) AS t
), tmp1 as (
    SELECT
      t.*,
      row_number()
      OVER (
        PARTITION BY t.batid
        ORDER BY t.dist ) AS rn
    FROM tmp t
) update batiment b set adrid = t.adrid
from tmp1 t
where t.batid = b.id
and t.rn = 1;

La distance est donc bien utile

Nico

Bonjour,

Je reviens sur le sujet car je me pose encore quelques questions : P
En effet, la méthode citée dans les deux derniers posts est très intéressante pour les points localisés au numéro de rue ou éventuellement à la rue. Mais pour ceux situés au centre de la commune, le résultat est complètement faux, ce qui est logique (comment croiser géographiquement un bâtiment BD TOPO avec un point situé au centre de la commune?) .
Je le savais et je me disais que ce n'était pas trop grave mais après analyse il s'avère que si. Du coup, je me demande si je ne vais pas tout bêtement travailler sans les points mal géocodés et trouver une autre source pour les bâtiments restants concernés par de l'activité
(PAI de la BD TOPO®? Mais je perds l'information précieuse du nombre de salariés pour les activités SIREN et la capacité pour les établissements de santé FINESS).

Qu'en pensez-vous?

Par ailleurs, certains points dont l'adressage se fait au numéro de rue se superposent (parfois jusqu'à près de 40 superpositions dans mon département de travail). Pour un bâtiment "multiactivités", je pense que c'est normal...?
Mais quand je fais le update sur mes bâtiments pour affecter les informations du point le plus proche, il me choisit aléatoirement les infos d'un des 40 points qui se superposent...Logique, là encore!

Je ne sais pas encore comment procéder, mais j'envisage deux solutions :

- choisir le point dont le nombre de salariés est le plus important (bof bof... : /)
- agréger les identifiants sous forme de liste et sommer les effectifs (=nbr de salariés) par bâtiment ce qui me semblerait le plus juste...
car au final, je travaille sur des problématiques d'inondations, et le nombre d'occupants/habitants/salariés à un endroit donné est un enjeu très important.

Du coup, voici la requête que j'ai essayé de lancer (uniquement pour obtenir une liste de mes identifiants SIREN plutôt qu'un identifiant choisi aléatoirement):

ALTER TABLE a_detc_c17ll0108_jura.l_bati_groupe_bdt_039_2015
ADD COLUMN sirene_gid numeric[];

WITH tmp AS
-- requête qui permet de prendre le premier point sirène le plus proche de chaque bâtiment
(
SELECT sirene.gid AS s_gid, t.bat_id, t.dist
FROM a_detc_c17ll0108_jura.base_sirene_39_062017 as sirene
CROSS JOIN LATERAL
-- requête qui permet de récupérer la distance la plus proche entre Sirène et bâti
(SELECT bati.id AS bat_id, st_distance(bati.geom, sirene.geom) AS dist
FROM a_detc_c17ll0108_jura.l_bati_groupe_bdt_039_2015 as bati
ORDER BY sirene.geom <-> bati.geom
LIMIT 1) AS t
WHERE sirene.geo_type LIKE 'housenumber' OR sirene.geo_type LIKE 'interpolation' OR sirene.geo_type LIKE 'street'
),
-- requête qui permet de créer un identifiant partitionné par identifiant bâti de la distance la plus petite à la plus grande
tmp1 AS 
(
SELECT t.*, ROW_NUMBER() OVER (PARTITION BY t.bat_id ORDER BY t.dist ) AS rn
FROM tmp AS t
)
-- requête qui permet de mettre à jour l'identifiant de la base Sirène dans la couche sur l'ensemble bâti
UPDATE a_detc_c17ll0108_jura.l_bati_groupe_bdt_039_2015 as bati
SET sirene_gid = 
(SELECT array_agg(t.s_gid)
FROM tmp1 AS t, a_detc_c17ll0108_jura.l_bati_groupe_bdt_039_2015 as bati
WHERE t.bat_id = bati.id
AND t.rn = 1);

Mais elle tourne très longtemps (toujours pas terminée au bout d'une heure alors que je ne pensais pas avoir changé grand chose par rapport à la requête de Nicolas qui tourne en 5,5 secondes!).

Dans un premier temps, je me demande si le

ORDER BY sirene.geom <-> bati.geom
LIMIT 1

ramène TOUS LES POINTS qui se superposent et qui sont les plus proches de mon bâti ou bien, au hasard, UN SEUL POINT parmis ceux qui se superposent et qui sont les plus proches de mon bâti (dans ce dernier cas, ma requête ci-dessus, même si elle fonctionnait, ne servirait à rien!)

Comment aggréger des données sous forme de liste dans un UPDATE? Je n'ai rien trouvé de concluant sur le net... : (

En vous remerciant à nouveau.

Bien cordialement,

Lucie D.