Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
banniere

Le portail francophone de la géomatique


Toujours pas inscrit ? Mot de passe oublié ?
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

Annonce

Suite à un problème technique intervenu entre le 22 et le 23 mars, nous avons du procéder dans la soirée du 25 mars, à la restauration de la base de données du 24 mars (matinée).

En clair, nous avons perdu vos contributions et inscriptions du dimanche 24 et du lundi 25 mars.
Nous vous prions de nous excuser.

#1 Thu 03 August 2017 10:13

Lucie D.
Participant actif
Date d'inscription: 21 Oct 2013
Messages: 137

Postgresql : distance minimum entre un point et un objet surfacique

Bonjour,

Je cherche à joindre des informations ponctuelles (points géolocalisés à partir de la BAN) à des objets surfaciques (en l’occurrence les bâtiments de la BDTOPO). Dans le meilleur des cas, les points intersectent les bâtiments donc un simple st_intersects suffit pour faire ma jointure. Mais souvent, même quand le point se situe au numéro de rue, le point n'intersecte pas le bâtiment (normal, vous me direz!). Visuellement, je vois bien que tel point correspond à tel bâtiment.

J'ai essayé le st_intersects entre mes points et mon bâti affecté d'un tampon de 5m, mais le résultat n'est pas satisfaisant car un même point peut être affecté à deux bâtiments (point situé proche de deux bâtiments) ou un bâtiment peut être affecté des informations de plusieurs points à la fois (ce n'est pas forcément faux dans le cas d'un bâtiment "multiactivités" mais ce peut être source d'erreurs, particulièrement dans les zones denses).

Alors j'essaye de raisonner en termes de distances minimales. Voici la requête que je lance (il s'agit ici de la base sirène, mais je suis également bloquée avec la base finess (établissements sensibles)) :

Code:

WITH tempa AS
(SELECT bati.id, sirene.gid, st_distance(bati.geomloc,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),
tempb AS 
(SELECT gid, min(distance) as min_dist
FROM tempa
GROUP BY gid),
tempc AS
(SELECT tempa.id, tempb.gid, tempb.min_dist
FROM tempa, tempb
WHERE tempa.gid=tempb.gid AND tempa.distance=tempb.min_dist)
UPDATE a_detc_c17ll0108_jura.l_bati_groupe_bdt_039_2015 as bat
SET sirene='oui'
FROM tempc
WHERE bat.id=tempc.id;

Je ne suis pas sûre pour le bati.geom&&sirene.geom mais je ne sais pas comment lier mes tables autrement.

Certains bâtiments ne sont pas croisés alors que, visuellement sur QGIS, il n'y a aucune raison... En pièce jointe, une impression d'écran avec, en vert, les bâtiments qui ont été affectés des infos Sirène, et en rouge, ceux qui ne l'ont pas été, soit parce qu'ils ne sont pas concernés, là ok, soit...il s'agit d'une erreur pour certains (voir ceux entourés en bleu).

J'ai l'impression de ne pas lancer la bonne requête, qu'en pensez-vous?

En vous remerciant par avance pour votre aide précieuse!

Bien cordialement,

Lucie D.


Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

Hors ligne

 

#2 Thu 03 August 2017 10:38

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1534

Re: Postgresql : distance minimum entre un point et un objet surfacique

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

Hors ligne

 

#3 Thu 03 August 2017 10:44

meonais
Participant occasionnel
Date d'inscription: 17 Jan 2017
Messages: 35

Re: Postgresql : distance minimum entre un point et un objet surfacique

Bonjour Lucie,

J'ai récemment résolu le même type de problématique avec cette requête qui cherchait à rapprocher des points sur des lignes selon les plus courtes distances (uniques pour chaque point) :

Code:

DROP TABLE IF EXISTS pt_snap CASCADE;
CREATE TEMP TABLE pt_snap AS (
    SELECT s.ident_uniq, 
min(st_distance(s.geom, r.geom)) as dist, rank() OVER (PARTITION BY ident_uniq ORDER BY min(st_distance(s.geom, r.geom)) ASC) as rang, s.cd, s.code, r.code_hydro as cdcourseau, r.toponyme, st_endpoint(ST_ShortestLine(st_closestpoint(s.geom,r.geom), r.geom)) as geom -- création de la ligne la plus courte entre le point et le troncon (base de recherche 1000m) + conservation uniquement du point final de cette ligne (point snap)
    FROM station_paca_corse s
    INNER JOIN cours_d_eau r
    ON ST_Dwithin(r.geom, s.geom, 1500) 
    GROUP BY s.ident_uniq, s.geom, r.geom, c.cdcourseau, r.toponyme, s.cd, s.code
    ORDER BY ident_uniq, dist
    );
ALTER TABLE pt_snap ADD COLUMN idsnap SERIAL PRIMARY KEY;
-- indexation 
CREATE INDEX indexptsnap ON pt_snap USING GIST ( geom );

Parfois, si le point à rapprocher est proche de deux lignes (polygone pour vous) distinctes, la nouvelle table créera un point final sur chaque ligne avec l'identifiant du point initial... (donc double le point)
Moi je pouvais distinguer le bon point final en joignant les données du point initial (nom du cours d'eau pour moi) avec celles de la couche de lignes cours d'eau pour filtrer la table pt_snap et sélectionner les bons points finaux rapprochés sur les bons cours d'eau selon les plus courtes distances.

J'espère que cela t'aidera...  et que je n'ai pas été trop "brouillon" dans la réponse smile

Bon courage,

Hors ligne

 

#4 Thu 03 August 2017 11:32

ChristopheV
Membre
Lieu: Ajaccio
Date d'inscription: 7 Sep 2005
Messages: 3163
Site web

Re: Postgresql : distance minimum entre un point et un objet surfacique

Bonjour,

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

Je cherche à joindre des informations ponctuelles (points géolocalisés à partir de la BAN) à des objets surfaciques (en l’occurrence les bâtiments de la BDTOPO). Dans le meilleur des cas, les points intersectent les bâtiments donc un simple st_intersects suffit pour faire ma jointure. Mais souvent, même quand le point se situe au numéro de rue, le point n'intersecte pas le bâtiment (normal, vous me direz!). Visuellement, je vois bien que tel point correspond à tel bâtiment.


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.


Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close

Hors ligne

 

#5 Fri 04 August 2017 13:31

Lucie D.
Participant actif
Date d'inscription: 21 Oct 2013
Messages: 137

Re: Postgresql : distance minimum entre un point et un objet surfacique

Bonjour,

Je n'ai pas encore testé la méthode de Nicolas, ni celle de meonais, mais je pense, après discussion avec des collègues, passer par l'intermédiaire des parcelles cadastrales, en effet. Par contre, désolée pour mon ignorance, mais les données cadastrales au format Edigéo, s'agit-il du PCI Vecteur(=cadastre DGFIP)? Le problème, c'est que je souhaite travailler avec le Bâti de la BD TOPO car j'ai absolument besoin des hauteurs... Je vais tester tout ça.

Merci encore!

Lucie D.

Hors ligne

 

#6 Fri 04 August 2017 15:32

Lucie D.
Participant actif
Date d'inscription: 21 Oct 2013
Messages: 137

Re: Postgresql : distance minimum entre un point et un objet surfacique

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 :

Code:

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.

Hors ligne

 

#7 Fri 04 August 2017 17:42

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1534

Re: Postgresql : distance minimum entre un point et un objet surfacique

Bonjour

L'operateur <-> utilise les index spatiaux des colonnes géo. C'est ca sa force.

Sur PG 9.6, l'opérateur renvoie une réponse exacte pour les objets eux-memes et non plus sur leur bbox comme dans les versions précédentes.

Ici par exemple, si l'on veut attribuer une adresse à chaque batiment en prenant le point d'adresse le plus proche (et un seul point), on peut utiliser LIMIT 1 dans la requête pour limiter un seul point proche par batiment.

Le gros "trick" de cette requête est d'utiliser le mot clé LATERAL dans la jointure entre les tables. Dans une jointure LATERAL, on peut référencer, dans la sous requête, des colonnes de la requête principale ! (chose impossible normalement).
En combinant les deux, on obtient ce qu'on veut: pour chaque ligne de la table adresse, on classe les batiments par proximités avec l'opérateur <-> et on ne garde que le premier = le plus proche.
(attention, après se pose la question métier d'attribuer les bonnes adresses en fonctions des conditions: points trop lointains, batiments sans adresse a traiter, etc.).

(le calcul de la distance ici ne sert pas a grand chose wink )

Code:

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
ORDER BY adrid, t.dist;

En faisant un update sur la table batiment pour y mettre l'id de l'adresse, on peut afficher l'adresse de chaque batiment.

Nicolas


Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

Hors ligne

 

#8 Fri 04 August 2017 17:55

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1534

Re: Postgresql : distance minimum entre un point et un objet surfacique

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

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:

Code:

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 wink

Nico


Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

Hors ligne

 

#9 Mon 07 August 2017 09:36

Lucie D.
Participant actif
Date d'inscription: 21 Oct 2013
Messages: 137

Re: Postgresql : distance minimum entre un point et un objet surfacique

Bonjour Nicolas,

Ce n'est pas la première fois que vous me sortez d'une impasse et je vous en remercie grandement!
Voilà qui est fait, ça fonctionne nickel, en 5,6 secondes à l'échelle d'un département!

Attention, après se pose la question métier d'attribuer les bonnes adresses en fonctions des conditions: points trop lointains, batiments sans adresse a traiter, etc.


Oui, en effet, mais comme j'ai une multitude de bases de données à traiter (il s'agit d'une assez grosse étude), je vais me contenter de ça pour l'instant (ce qui est déjà bien plus satisfaisant que ce que j'espérais)!

Du coup, je laisse tomber la méthode des parcelles!

En vous remerciant à nouveau!

Bonne journée!

Bien cordialement,

Lucie D.

Dernière modification par Lucie D. (Mon 07 August 2017 09:38)

Hors ligne

 

#10 Tue 08 August 2017 14:37

Lucie D.
Participant actif
Date d'inscription: 21 Oct 2013
Messages: 137

Re: Postgresql : distance minimum entre un point et un objet surfacique

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):

Code:

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

Code:

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.

Hors ligne

 

#11 Tue 08 August 2017 14:55

Lucie D.
Participant actif
Date d'inscription: 21 Oct 2013
Messages: 137

Re: Postgresql : distance minimum entre un point et un objet surfacique

Ah...bah en fait je me suis répondu toute seule!
Je suis passée par une autre requête temporaire :

Code:

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 d'aggréger les identifiants sirene sous forme de liste
tmp2 AS
(
SELECT tmp1.bat_id, array_agg(tmp1.s_gid) as s_gid_agg
FROM tmp1
GROUP BY tmp1.bat_id
)
-- 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 = tmp2.s_gid_agg
FROM tmp2, tmp1
WHERE tmp2.bat_id = bati.id AND tmp1.bat_id=bati.id AND tmp1.rn = 1;
--> Query returned successfully: 18948 rows affected, 5.1 secs execution time.

Le tout qui s'effectue en 5,1 secondes (j'ai retiré les points situés au centre de la commune ou à la mairie).

Hors ligne

 

Pied de page des forums

Powered by FluxBB