IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Extensions PostgreSQL Discussion :

[POSTGIS] Supprimer une ligne d'une multiligne [11]


Sujet :

Extensions PostgreSQL

  1. #1
    Nouveau membre du Club
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    mars 2016
    Messages
    53
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Morbihan (Bretagne)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : Service public

    Informations forums :
    Inscription : mars 2016
    Messages : 53
    Points : 33
    Points
    33
    Par défaut [POSTGIS] Supprimer une ligne d'une multiligne
    Bonjour,
    J'ai un soucis avec une multiligne cf image
    Nom : ml.png
Affichages : 33
Taille : 12,0 Ko
    après un dump, ou j'isole st_startpoint et st_endpoint voici ce que j'ai
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    "idlin","startpt","endpt"
    1,"POINT Z (477969.399999933 6758795.99999823 33.1)","POINT Z (475644.399999933 6760065.69999823 32.8)"
    2,"POINT Z (478043.799999933 6758632.49999823 32.8)","POINT Z (477969.399999933 6758795.99999823 33.1)"
    3,"POINT Z (478043.799999933 6758632.49999823 32.8)","POINT Z (477969.399999933 6758795.99999823 33.1)"
    4,"POINT Z (481522.099999934 6758363.19999823 34.1)","POINT Z (478043.799999933 6758632.49999823 32.8)"
    L'idée est d'écarter la ligne 2 ou 3.
    J'ai utilisé le CTE 'WITH RECURSIVE'
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    WITH RECURSIVE t(id,g,explored_paths) AS (with ml as (select 'MULTILINESTRING Z ((477969.399999933 6758795.99999823 33.1,477967.799999933 6758802.29999823 33.1,477944.799999933 6758894.59999823 33.1,477943.499999933 6758900.09999823 33.1,477933.499999933 6758944.89999823 33.1,477930.699999933 6758980.29999823 33.1,477930.099999933 6759079.29999823 33.1,477934.499999933 6759171.79999823 33.1,477930.899999933 6759239.49999823 33.1,477922.399999933 6759332.29999823 33.1,477907.899999933 6759427.69999823 33.1,477884.299999933 6759552.59999823 33.1,477864.899999933 6759611.29999823 33.1,477831.899999933 6759694.49999823 33.1,477795.899999933 6759771.69999823 33.1,477763.999999933 6759825.49999823 33.1,477726.899999933 6759879.99999823 33.1,477675.999999933 6759941.09999823 33.1,477607.199999933 6760000.59999823 33.1,477567.099999933 6760030.09999823 33.1,477511.399999933 6760059.39999823 33.1,477441.399999933 6760085.89999823 33.1,477339.999999933 6760113.09999823 33.1,477307.399999933 6760117.79999823 33.1,477282.999999933 6760117.79999823 33.1,477232.299999933 6760107.89999823 33.1,477112.399999933 6760074.79999823 33.1,477085.399999933 6760069.39999823 33.1,477082.399999933 6760068.69999823 33.1,477059.399999933 6760066.09999823 33.1,477030.799999933 6760065.89999823 33.1,477007.199999933 6760071.69999823 33.1,476960.599999933 6760086.99999823 32.9,476945.199999933 6760092.09999823 32.9,476914.199999933 6760096.79999823 32.7,476892.199999933 6760098.99999823 32.7,476870.199999933 6760098.79999823 32.7,476836.999999933 6760098.69999823 32.7,476741.999999933 6760086.79999823 32.7,476677.699999933 6760071.59999823 32.7,476594.399999933 6760043.99999823 32.7,476590.699999933 6760042.79999823 32.7,476471.099999933 6760006.09999823 32.7,476372.999999933 6759980.39999823 32.7,476302.599999933 6759967.89999823 32.7,476198.299999933 6759954.19999823 32.7,476070.699999933 6759945.99999823 32.7,475965.899999933 6759950.59999823 32.7,475881.299999933 6759967.29999823 32.7,475807.699999933 6759986.49999823 32.7,475728.899999933 6760017.39999823 32.8,475726.899999933 6760018.49999823 32.8,475671.599999933 6760048.59999823 32.8,475644.399999933 6760065.69999823 32.8),(478043.799999933 6758632.49999823 32.8,478010.599999933 6758689.99999823 33.2,478008.499999933 6758695.19999823 33,478003.999999933 6758706.99999823 32.8,477969.399999933 6758795.99999823 33.1),(478043.799999933 6758632.49999823 32.8,478025.199999933 6758735.99999823 33.2,478018.999999933 6758748.89999823 32.8,477992.899999933 6758771.19999823 33,477969.399999933 6758795.99999823 33.1),(481522.099999934 6758363.19999823 34.1,481510.099999934 6758407.69999823 34,481503.799999934 6758433.49999823 33.9,481493.999999934 6758464.49999822 33.9,481483.099999934 6758495.89999823 33.9,481468.299999934 6758530.19999823 33.9,481453.999999934 6758557.09999823 33.9,481435.799999934 6758585.59999823 33.9,481411.599999934 6758612.99999823 33.9,481384.899999934 6758635.79999823 33.9,481360.599999934 6758652.19999823 33.9,481333.799999934 6758666.49999823 33.9,481307.999999934 6758676.39999823 33.9,481267.999999934 6758685.69999823 33.9,481230.999999934 6758692.49999823 34.1,481186.499999934 6758699.39999823 33.9,481143.299999934 6758705.99999823 34.1,481123.199999934 6758707.99999823 34,481096.199999934 6758708.99999823 34,481063.499999934 6758707.89999823 34,481030.399999934 6758705.79999823 34.1,481006.699999934 6758703.59999823 34.1,480975.599999934 6758700.19999823 34,480936.999999934 6758696.69999823 34,480891.499999934 6758690.69999823 34,480851.699999934 6758685.49999822 34,480795.799999934 6758676.49999823 34,480767.299999934 6758672.39999823 34,480752.599999934 6758670.59999823 34,480722.799999934 6758668.99999823 34,480656.199999934 6758667.39999823 34,480591.199999934 6758665.99999823 34,480548.999999934 6758664.99999823 34,480542.499999934 6758664.59999823 34,480537.499999934 6758664.39999823 34,480483.799999934 6758663.19999823 33.8,480451.999999934 6758660.89999823 33.8,480407.499999934 6758655.49999823 33.8,480352.899999934 6758642.79999823 33.6,480301.799999934 6758627.89999823 33.6,480242.999999934 6758611.89999823 33.6,480177.899999934 6758597.19999823 33.6,480109.999999934 6758579.39999823 33.6,480079.799999934 6758573.19999823 33.6,480027.799999934 6758553.59999822 33.6,479975.499999934 6758533.09999823 33.6,479914.299999934 6758502.39999823 33.6,479905.599999934 6758498.39999823 33.6,479831.299999934 6758460.29999822 33.6,479763.299999934 6758424.29999823 33.6,479731.699999934 6758403.29999822 33.6,479709.799999934 6758386.69999823 33.6,479672.899999934 6758357.19999823 33.6,479671.199999934 6758356.09999823 33.6,479648.199999934 6758341.09999823 33.6,479642.599999934 6758337.29999823 33.6,479627.499999934 6758325.19999823 33.6,479597.599999934 6758300.09999823 33.6,479568.699999934 6758275.39999823 33.6,479545.399999934 6758254.09999823 33.6,479530.399999934 6758240.99999823 33.6,479523.399999934 6758234.89999823 33.6,479456.099999934 6758174.79999822 34,479436.099999934 6758161.39999823 34,479419.999999934 6758148.59999822 34,479401.499999934 6758137.89999822 34,479388.799999934 6758130.59999822 34,479353.299999934 6758110.99999823 34,479309.399999934 6758090.79999822 34,479259.499999934 6758073.59999823 34,479161.599999934 6758041.79999822 34.3,479113.699999934 6758027.99999822 34.3,479109.999999934 6758026.49999822 34.3,479052.099999933 6758013.69999822 34.3,478997.299999934 6758001.39999823 34.3,478961.399999933 6757993.39999823 34.3,478912.799999933 6757986.19999823 34.3,478851.999999933 6757981.79999822 34.3,478826.099999933 6757981.59999822 34.3,478798.599999933 6757981.29999822 34.3,478762.799999933 6757981.09999822 34.3,478704.199999933 6757979.49999822 34.3,478649.199999933 6757973.49999822 34.3,478591.099999933 6757965.79999822 34.3,478537.999999933 6757958.99999822 34.3,478510.399999933 6757958.79999822 34.3,478490.199999933 6757959.69999822 34.3,478459.999999933 6757963.19999823 33.2,478427.899999933 6757972.39999823 33.2,478393.299999933 6757983.69999823 33.2,478368.299999933 6757996.89999822 33.2,478350.099999933 6758007.69999823 33.2,478326.099999933 6758030.29999822 33.2,478274.999999933 6758088.29999822 33.2,478223.599999933 6758156.59999822 33.2,478194.899999933 6758201.69999823 33.2,478173.699999933 6758244.79999822 33.2,478154.299999933 6758289.89999823 33.2,478128.999999933 6758345.29999823 33.2,478112.199999933 6758389.69999823 33.2,478105.499999933 6758412.79999822 33.2,478088.699999933 6758475.19999823 33.2,478069.499999933 6758552.59999823 33.2,478045.499999933 6758626.99999823 32.8,478043.799999933 6758632.49999823 32.8))'::geometry as geom)
    	, l as 
    	(select (st_dump(geom)).path[1] as idln, (st_dump(geom)).geom
    	from ml)
    	SELECT l.idln,l.geom, ARRAY[idln]
        FROM l
    	WHERE idln=1
      UNION ALL								 
    	SELECT idln, geom, explored_paths || idln
        FROM l, t
        WHERE ST_touches(geom, t.g)
    	AND NOT (idln= ANY(explored_paths))
    )
    SELECT *, row_number () over () FROM t;
    Cependant je n'arrive pas à filtrer .
    avez vous une idée me permettant d'avancer?

  2. #2
    ced
    ced est déconnecté
    Rédacteur/Modérateur

    Avatar de ced
    Homme Profil pro
    Gestion de bases de données techniques
    Inscrit en
    avril 2002
    Messages
    5 881
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Loiret (Centre)

    Informations professionnelles :
    Activité : Gestion de bases de données techniques
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : avril 2002
    Messages : 5 881
    Points : 22 765
    Points
    22 765
    Par défaut
    Bonjour,

    Selon quel critère souhaitez-vous faire le choix entre la ligne 2 et la ligne 3 ? Le hasard ? La longueur la moins grande ? Autre chose ?

    ced
    Rédacteur / Modérateur SGBD et R
    Mes tutoriels et la FAQ MySQL

    ----------------------------------------------------
    Pensez aux balises code et au tag
    Une réponse vous a plu ? N'hésitez pas à y mettre un
    Je ne réponds pas aux questions techniques par message privé, les forums sont là pour ça

  3. #3
    Nouveau membre du Club
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    mars 2016
    Messages
    53
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Morbihan (Bretagne)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : Service public

    Informations forums :
    Inscription : mars 2016
    Messages : 53
    Points : 33
    Points
    33
    Par défaut
    eh bien la longueur la plus courte.

  4. #4
    ced
    ced est déconnecté
    Rédacteur/Modérateur

    Avatar de ced
    Homme Profil pro
    Gestion de bases de données techniques
    Inscrit en
    avril 2002
    Messages
    5 881
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Loiret (Centre)

    Informations professionnelles :
    Activité : Gestion de bases de données techniques
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : avril 2002
    Messages : 5 881
    Points : 22 765
    Points
    22 765
    Par défaut
    Quelque chose comme ça ?
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    WITH ml AS (
        SELECT 'MULTILINESTRING Z ((477969.399999933 6758795.99999823 33.1,477967.799999933 6758802.29999823 33.1,477944.799999933 6758894.59999823 33.1,477943.499999933 6758900.09999823 33.1,477933.499999933 6758944.89999823 33.1,477930.699999933 6758980.29999823 33.1,477930.099999933 6759079.29999823 33.1,477934.499999933 6759171.79999823 33.1,477930.899999933 6759239.49999823 33.1,477922.399999933 6759332.29999823 33.1,477907.899999933 6759427.69999823 33.1,477884.299999933 6759552.59999823 33.1,477864.899999933 6759611.29999823 33.1,477831.899999933 6759694.49999823 33.1,477795.899999933 6759771.69999823 33.1,477763.999999933 6759825.49999823 33.1,477726.899999933 6759879.99999823 33.1,477675.999999933 6759941.09999823 33.1,477607.199999933 6760000.59999823 33.1,477567.099999933 6760030.09999823 33.1,477511.399999933 6760059.39999823 33.1,477441.399999933 6760085.89999823 33.1,477339.999999933 6760113.09999823 33.1,477307.399999933 6760117.79999823 33.1,477282.999999933 6760117.79999823 33.1,477232.299999933 6760107.89999823 33.1,477112.399999933 6760074.79999823 33.1,477085.399999933 6760069.39999823 33.1,477082.399999933 6760068.69999823 33.1,477059.399999933 6760066.09999823 33.1,477030.799999933 6760065.89999823 33.1,477007.199999933 6760071.69999823 33.1,476960.599999933 6760086.99999823 32.9,476945.199999933 6760092.09999823 32.9,476914.199999933 6760096.79999823 32.7,476892.199999933 6760098.99999823 32.7,476870.199999933 6760098.79999823 32.7,476836.999999933 6760098.69999823 32.7,476741.999999933 6760086.79999823 32.7,476677.699999933 6760071.59999823 32.7,476594.399999933 6760043.99999823 32.7,476590.699999933 6760042.79999823 32.7,476471.099999933 6760006.09999823 32.7,476372.999999933 6759980.39999823 32.7,476302.599999933 6759967.89999823 32.7,476198.299999933 6759954.19999823 32.7,476070.699999933 6759945.99999823 32.7,475965.899999933 6759950.59999823 32.7,475881.299999933 6759967.29999823 32.7,475807.699999933 6759986.49999823 32.7,475728.899999933 6760017.39999823 32.8,475726.899999933 6760018.49999823 32.8,475671.599999933 6760048.59999823 32.8,475644.399999933 6760065.69999823 32.8),(478043.799999933 6758632.49999823 32.8,478010.599999933 6758689.99999823 33.2,478008.499999933 6758695.19999823 33,478003.999999933 6758706.99999823 32.8,477969.399999933 6758795.99999823 33.1),(478043.799999933 6758632.49999823 32.8,478025.199999933 6758735.99999823 33.2,478018.999999933 6758748.89999823 32.8,477992.899999933 6758771.19999823 33,477969.399999933 6758795.99999823 33.1),(481522.099999934 6758363.19999823 34.1,481510.099999934 6758407.69999823 34,481503.799999934 6758433.49999823 33.9,481493.999999934 6758464.49999822 33.9,481483.099999934 6758495.89999823 33.9,481468.299999934 6758530.19999823 33.9,481453.999999934 6758557.09999823 33.9,481435.799999934 6758585.59999823 33.9,481411.599999934 6758612.99999823 33.9,481384.899999934 6758635.79999823 33.9,481360.599999934 6758652.19999823 33.9,481333.799999934 6758666.49999823 33.9,481307.999999934 6758676.39999823 33.9,481267.999999934 6758685.69999823 33.9,481230.999999934 6758692.49999823 34.1,481186.499999934 6758699.39999823 33.9,481143.299999934 6758705.99999823 34.1,481123.199999934 6758707.99999823 34,481096.199999934 6758708.99999823 34,481063.499999934 6758707.89999823 34,481030.399999934 6758705.79999823 34.1,481006.699999934 6758703.59999823 34.1,480975.599999934 6758700.19999823 34,480936.999999934 6758696.69999823 34,480891.499999934 6758690.69999823 34,480851.699999934 6758685.49999822 34,480795.799999934 6758676.49999823 34,480767.299999934 6758672.39999823 34,480752.599999934 6758670.59999823 34,480722.799999934 6758668.99999823 34,480656.199999934 6758667.39999823 34,480591.199999934 6758665.99999823 34,480548.999999934 6758664.99999823 34,480542.499999934 6758664.59999823 34,480537.499999934 6758664.39999823 34,480483.799999934 6758663.19999823 33.8,480451.999999934 6758660.89999823 33.8,480407.499999934 6758655.49999823 33.8,480352.899999934 6758642.79999823 33.6,480301.799999934 6758627.89999823 33.6,480242.999999934 6758611.89999823 33.6,480177.899999934 6758597.19999823 33.6,480109.999999934 6758579.39999823 33.6,480079.799999934 6758573.19999823 33.6,480027.799999934 6758553.59999822 33.6,479975.499999934 6758533.09999823 33.6,479914.299999934 6758502.39999823 33.6,479905.599999934 6758498.39999823 33.6,479831.299999934 6758460.29999822 33.6,479763.299999934 6758424.29999823 33.6,479731.699999934 6758403.29999822 33.6,479709.799999934 6758386.69999823 33.6,479672.899999934 6758357.19999823 33.6,479671.199999934 6758356.09999823 33.6,479648.199999934 6758341.09999823 33.6,479642.599999934 6758337.29999823 33.6,479627.499999934 6758325.19999823 33.6,479597.599999934 6758300.09999823 33.6,479568.699999934 6758275.39999823 33.6,479545.399999934 6758254.09999823 33.6,479530.399999934 6758240.99999823 33.6,479523.399999934 6758234.89999823 33.6,479456.099999934 6758174.79999822 34,479436.099999934 6758161.39999823 34,479419.999999934 6758148.59999822 34,479401.499999934 6758137.89999822 34,479388.799999934 6758130.59999822 34,479353.299999934 6758110.99999823 34,479309.399999934 6758090.79999822 34,479259.499999934 6758073.59999823 34,479161.599999934 6758041.79999822 34.3,479113.699999934 6758027.99999822 34.3,479109.999999934 6758026.49999822 34.3,479052.099999933 6758013.69999822 34.3,478997.299999934 6758001.39999823 34.3,478961.399999933 6757993.39999823 34.3,478912.799999933 6757986.19999823 34.3,478851.999999933 6757981.79999822 34.3,478826.099999933 6757981.59999822 34.3,478798.599999933 6757981.29999822 34.3,478762.799999933 6757981.09999822 34.3,478704.199999933 6757979.49999822 34.3,478649.199999933 6757973.49999822 34.3,478591.099999933 6757965.79999822 34.3,478537.999999933 6757958.99999822 34.3,478510.399999933 6757958.79999822 34.3,478490.199999933 6757959.69999822 34.3,478459.999999933 6757963.19999823 33.2,478427.899999933 6757972.39999823 33.2,478393.299999933 6757983.69999823 33.2,478368.299999933 6757996.89999822 33.2,478350.099999933 6758007.69999823 33.2,478326.099999933 6758030.29999822 33.2,478274.999999933 6758088.29999822 33.2,478223.599999933 6758156.59999822 33.2,478194.899999933 6758201.69999823 33.2,478173.699999933 6758244.79999822 33.2,478154.299999933 6758289.89999823 33.2,478128.999999933 6758345.29999823 33.2,478112.199999933 6758389.69999823 33.2,478105.499999933 6758412.79999822 33.2,478088.699999933 6758475.19999823 33.2,478069.499999933 6758552.59999823 33.2,478045.499999933 6758626.99999823 32.8,478043.799999933 6758632.49999823 32.8))'::geometry AS geom
    )
    , l AS (
        SELECT (st_dump(geom)).PATH[1] AS idln,
               (st_dump(geom)).geom
        FROM ml
    )
    , diff_l AS (
        SELECT idln, geom, count(*) over(PARTITION BY st_startpoint(geom), st_endpoint(geom) ORDER BY st_length(geom)) AS ordre
        FROM l
    )
    SELECT st_collect(geom)
    FROM diff_l
    WHERE ordre = 1;
    Rédacteur / Modérateur SGBD et R
    Mes tutoriels et la FAQ MySQL

    ----------------------------------------------------
    Pensez aux balises code et au tag
    Une réponse vous a plu ? N'hésitez pas à y mettre un
    Je ne réponds pas aux questions techniques par message privé, les forums sont là pour ça

  5. #5
    Nouveau membre du Club
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    mars 2016
    Messages
    53
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Morbihan (Bretagne)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : Service public

    Informations forums :
    Inscription : mars 2016
    Messages : 53
    Points : 33
    Points
    33
    Par défaut
    Parfait! Merci! j'ai absolument pas pensé à utiliser le fenêtrage :/

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [langage] supprimer une ligne
    Par helene22500 dans le forum Langage
    Réponses: 2
    Dernier message: 25/05/2005, 11h27
  2. [JTable] Supprimer une ligne d'un jtable
    Par Orionmel dans le forum Composants
    Réponses: 5
    Dernier message: 05/11/2004, 23h29
  3. [C#] Comment supprimer une ligne dans DataGrid ?
    Par BAUDIER dans le forum ASP.NET
    Réponses: 2
    Dernier message: 20/07/2004, 17h03
  4. supprimer une ligne avec cle etrangere
    Par BaBas dans le forum Langage SQL
    Réponses: 4
    Dernier message: 15/07/2003, 12h24
  5. Supprimer une ligne dans un fichier
    Par sbeu dans le forum Langage
    Réponses: 3
    Dernier message: 13/05/2003, 11h30

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo