Le portail francophone de la géomatique

Bonjour,

Pour découper rapidement des lignes avec des points, regardez du coté des fn de référencement linéaire (st_lineLocatePoint par ex)
Pour construire une ligne avec des points, ordonnez ces points et appeler st_makeLine.

Sans plus de précision, ou de schéma, pas facile de savoir votre contexte.

Nicolas

Hello Christophe: joue un peu avec: c'est tres rapide, super précis (abscisse curviligne) et ca permet de faire du calage auto de points sur des lignes ou PG (snapping), car st_lineLocatePoint "projète" le point sur la ligne.

Nicolas

Bonjour,

Je travaille actuellement sur la correction de bases de données reseau d'eau et d'assainissement. Je suis tombé sur le même type de problème et je ne résiste pas à partager mes recherches et ma requête, pour la projection des points sur la canalisation principales. Je suis parti de cette requête que j'ai adaptée :

SELECT
st_line_interpolate_point(géométrie de la ligne, st_line_locate_point(géométrie de la ligne, géométrie du point)) AS géométrie projetée,
FROM table des lignes
JOIN  table des points ON st_distance(géométrie de ligne, géométrie du point) < distance_tampon_maximum_pour_l'accrochage

Source : http://blog.martzluff.net/le-map-matchi … stgisqgis/

Pour ne prendre que la projection sur la canalisation la plus proche (et non uniquement celles prises par la distance tampon), il faut utiliser une table intermédiaire avec les fonctions de fenètrage d'agrégats Rank() Over(Partition BY) qui permettent de hiérachiser les valeurs prises par le select en fonction d'un champ (ici la distance à la canalisation).

https://docs.postgresql.fr/8.4/tutorial-window.html
http://www.bortzmeyer.org/postgresql-quantiles.html


J'y ai aussi associé une partie de requête me permettant d'exclure tous les points de piquages déjà présents sur une canalisation (j'ai une table présentant des piquages situés sur la canalisation et d'autres à côté, on ne s'en rend compte qu'en zoomant, merci les dessinateurs autocad...)

J'ai aussi du créer une table dump avec de passer mes géometries en linestring simple au lieu d'une polyline (je n'ai pas trouvé comment l'intégrer dans la requête) avec SELECT (ST_Dump(geom)).geom

Ma requête, à laquelle il faut ensuite appliquer le st_makeline comme recommandé par Nicolas Ribot (et ajuster le select).

SELECT idappareil, geom, geom_projetee
FROM
(
SELECT
st_distance(canalisation_dump.geom, piquages.geom), 
st_lineinterpolatepoint(canalisation_dump.geom, st_linelocatepoint(canalisation_dump.geom, piquages.geom)) AS geom_projetee, 
piquages.idappareil,
RANK() OVER(PARTITION BY piquages.idappareil ORDER BY st_distance(canalisation_dump.geom, piquages.geom)) as rang
FROM canalisation_dump
JOIN piquages ON st_distance(canalisation_dump.geom, piquages.geom) < distance_tampon
WHERE piquages.idappareil IN (
SELECT 
piquages.idappareil
FROM piquages
EXCEPT
SELECT piquages.idappareil
FROM piquages, canalisations
WHERE ST_Intersects(piquages.geom, canalisations.geom)
GROUP BY piquages.idappareil)
) AS temp
WHERE 
rang = 1 (la distance la plus courte)

Bon après la requête pourrait être mieux formulée à mon avis, mais elle marche. Bon courage!

PS : les recommendations topologiques du standard COVADIS en aep n'imposent pas le découpage des canalisations principales pour les noeuds de branchements individuels, seulements pour les autres appareillages/ouvrages)

Dernière modification par Rlucas (Fri 16 November 2018 19:15)

Rlucas, cela donnerait quelque chose comme ça :

WITH l AS
    (SELECT id, (st_dump(geom)).geom FROM lignes)
SELECT DISTINCT ON (p.id) p.id as idpoint, st_makeline(st_geometryn(p.geom,1),st_closestpoint(l.geom,p.geom)) as geom
FROM points as p
JOIN l ON st_dwithin(l.geom,p.geom,distance_tampon)
ORDER BY idpoint, st_distance(l.geom,p.geom);

La clause DISTINCT ON (colonnes ou ensemble de colonnes) https://www.postgresql.org/docs/9.5/sql … L-DISTINCT ne garde pour chaque colonne ou ensemble de colonnes que le premier résultat, les suivants sont retirés.
En l'occurrence, couplé à un tri sur la distance, cette clause conserve le point projeté sur la ligne le plus proche du point...
Par ailleurs, dans le cas où il existerait plusieurs lignes situées à une même distance d'un point, cette clause ne conserve tout de même qu'un résultat, à la différence de l'utilisation de la fonction de fenêtrage RANK qui en a été faite, qui aurait retourné plusieurs points projetés.


En outre, il a été intégré dans la requête le dump des lignes.
Enfin, grâce à la fonction ST_GeometryN(geom,Nième_Point) https://postgis.net/docs/ST_GeometryN.html, la requête est capable de travailler avec des Points contenus dans une table dont le type géométrique a été renseigné comme étant MultiPoint.


Pour ne pas créer de liens pour les points qui se trouvent déjà sur une ligne, la méthode la moins coûteuse en temps que j'ai pu trouver est de supprimer à la fin de la requête ces points-là :

WITH l AS
    (SELECT id, (st_dump(geom)).geom FROM lignes)
,proj AS
    (SELECT DISTINCT ON (p.id) p.id as idpoint, st_makeline(st_geometryn(p.geom,1),st_closestpoint(l.geom,p.geom)) as geom, 
    st_distance(l.geom,p.geom) as dist
    FROM points as p
    JOIN l ON st_dwithin(l.geom,p.geom,distance_tampon)
    ORDER BY idpoint, st_distance(l.geom,p.geom))
SELECT idpoint, geom
FROM proj
WHERE dist != 0;

Sam