2

In PostGIS, I have a LINESTRING M and I want to remove a segment of the line that falls between two given M values. I tried the following:

SELECT
    ST_AsText(line) as line,
    ST_AsText(segment_to_delete) as segment_to_delete,
    ST_AsText(ST_Difference(line, segment_to_delete)) as result
FROM
    (
        SELECT 
            line,
            ST_FilterByM(t.line, 2, 4, TRUE) as segment_to_delete
        FROM
            (SELECT 'LINESTRING M(1 1 1, 2 2 2, 3 3 3, 4 4 4, 5 5 5)'::geometry as line) as t   
    ) as t
;

line and segment_to_delete both have the expected points and M values (segment_to_delete is LINESTRING M (2 2 2,3 3 3,4 4 4)). However, the ST_Difference drops the M value and result is MULTILINESTRING((1 1,2 2),(4 4,5 5)) instead of the expected MULTILINESTRING((1 1 1,2 2 2),(4 4 4,5 5 5)). I'm ignoring the question what exactly should happen to the boundary points.

How do I remove a section between two M values from a LINESTRING M and retain the remaining M values?

(EDIT: I should probably add that in reality I have more than one segment I want to remove. That is, not only a single LINESTRING that I obtained with ST_FilterByM, but multiple LINESTRINGS which I aggregated with ST_Collect into a MULTILINESTRING. Therefore, I cannot simply use two ST_FilterByM(line, start_m, x) and ST_FilterByM(line, y, end_m) because I don't know how I could combine the individual results into a MULTILINESTRING with the correct holes.)

5
  • Documentation postgis.net/docs/ST_Difference.html says that Z is supported poorly and M is not discussed at all This function supports 3d and will not drop the z-index. However, the result is computed using XY only. The result Z values are copied, averaged or interpolated..
    – user30184
    Mar 28 at 21:21
  • 1
    ST_FilterByM is really only intended for use with ST_SetEffectiveArea, for doing dynamic VW simplification.
    – dr_jts
    Mar 29 at 0:20
  • 1
    There is the ST_LocateBetween function, but it sounds like you want an ST_LocateNotBetween function. Unfortunately not available at this time.
    – dr_jts
    Mar 29 at 0:26
  • Thanks everybody. Yes, I'd need a ST_LocateNotBetween, too bad it doesn't exist. One question though: when you say ST_FilterByM is intended for use with ST_SetEffectiveArea, is there a problem to use it as a replacement for ST_LocateBetween? It sounds like both functions do the same, with the exception of the offset, so I didn't see a reason to prefer one over the other. Is there anything specific to ST_SetEffectiveArea that ST_FilterByM does that makes it a bad choice?
    – Christoph
    Mar 29 at 16:50
  • ST_FilterByM is very different to ST_LocateBetween. The first just removes vertices, whereas the second clips out the portion(s) of a linestring between two measures.
    – dr_jts
    Mar 31 at 21:27

1 Answer 1

0

Here's an example of how it could be done. This method allows you to keep M and Z values intact. It works by merging the substring of the route from the start point to the first measure with the substring of the route from the second measure to the end point. It only works with linestringm/zm:

select 
    st_multi(
        st_collect(
            st_linesubstring(
                geom,
                0,
                st_linelocatepoint(geom,(st_dump(st_locatealong(geom,2))).geom)
            ), 
            st_linesubstring(
                geom, 
                st_linelocatepoint(geom,(st_dump(st_locatealong(geom,3))).geom),
                1
            )
        )
    )::geometry(multilinestringzm,4326) 
from 
    test_table 
where route = 'I-80';

Result:

MULTILINESTRING ZM((-87.52451930000001 41.57673001 0.0367314494360471 0.0365019382315417, -87.52339307 41.57639197 0.0995599048619624 0.1000941815086624, -87.52224574 41.57606255 0.16318998967472 0.1646499946718939, -87.52151775 41.57585352 0.2035642477858346 0.2056113261047147, -87.52038178000001 41.57554553 0.2661281860200688 0.2692638054543043, -87.51901236 41.5751585 0.341925440079649 0.3462245479359993, -87.51858498 41.57504134 0.3654939401312731 0.3701904855073645, -87.51839788 41.574990050000004 0.375812160957139 0.3806823718949862, -87.51779679 41.57483189 0.4088060974900145 0.4142964627387385, -87.51731502 41.574712500000004 0.435087374469731 0.441139242797425, -87.51689533 41.57461788 0.4577870750508737 0.464406234106276, -87.51643706 41.57451982 0.4824719904863741 0.4897510226107606, -87.5162885 41.57449211 0.4904016030923231 0.4979238796807487, -87.51568853 41.57438019 0.5224263889249414 0.530930648546923, -87.51504591 41.574269040000004 0.5565877553308383 0.5662002776908921, -87.51464559 41.57421007 0.577720920133288 0.5880836630793942, -87.51432668 41.57422222 0.5942627823824296 0.6053431845216892, -87.51377391 41.57414962 0.6233325812645489 0.6354943376038646, -87.51255714 41.57403579 0.6868528078630334 0.7015859432694932, -87.51095841 41.57395626 0.7698534794762963 0.7881539611415789, -87.5109265 41.57395597 0.7715069449041039 0.789879760846555, -87.5093418 41.57394199 0.8536053873540368 0.8755854416109238, -87.50916608 41.57394277 0.8627085192420054 0.8850886690929116, -87.50910458 41.57394304 0.8658945936622331 0.8884146898342308, -87.50889308 41.57394398 0.87685083715769 0.899852959023971, -87.50869935 41.57394601 0.8868878880894044 0.9103306689721495, -87.50663371 41.57396747 0.9939051296096295 1.02075069774396, -87.50432994 41.57397309 1.1132489839219488 1.13800627230337, -87.50061263 41.57402802 1.3058548407570925 1.3272272482624674, -87.49981505 41.57403951 1.3471796706144232 1.3678259741628358, -87.49595764 41.57409508 1.5470426313695498 1.5641773510589028, -87.49131808 41.574133010000004 1.7874010795057984 1.8003254189475082, -87.48904574000001 41.57412469 1.9051168202713598 1.9159817238099535, -87.48789686 41.57412506 1.96463250395027 1.974456393871057, -87.48740194431393 41.57412625807111 1.9902706859621775 2.0000000000002114), (-87.46821618619559 41.5740364298294 2.9842097766977767 3.0000000000001363, -87.4667803 41.57402795 3.0585954968701117 3.0752755513975534, -87.4666989 41.574027470000004 3.0628125483053736 3.079549241184025, -87.46634144000001 41.57402536 3.081330926215742 3.0983167252711357, -87.46320691 41.57401028 3.2437123545241775 3.2628859018000447, -87.46205884 41.57399761 3.3031925579707604 3.3231648760063313, -87.46152000000001 41.57399545 3.3311065466259606 3.3514549677729315, -87.46149746 41.57399536 3.332274349304498 3.3526383591849447, -87.46129505 41.57399617 3.3427599672140786 3.3632652564417995, -87.45790958 41.5740052 3.518138777159038 3.541007866803585, -87.45708303 41.574008490000004 3.5609573803667445 3.5844032582280168, -87.45470653 41.573959370000004 3.6841138985473663 3.7091995284121198, -87.45297792 41.57396619 3.7736626288242405 3.7999547121951682, -87.45181769 41.57396449 3.833766190611641 3.8608684986466852, -87.45077020000001 41.573943560000004 3.8880486344423844 3.915874186827682, -87.45016075 41.57392807 3.9196382748923497 3.9478815096992483, -87.44947495 41.57388788 3.9552725366229424 3.9839487568776617, -87.44887758 41.57383302 3.986448841780657 4.015446713961073, -87.44804775 41.57375204 4.029798343486618 4.059230015212957, -87.44737684 41.57366012 4.065127574707731 4.0947901859209805, -87.44669497 41.57356265 4.101084700261708 4.130960738582003, -87.44596577 41.57344397 4.139737204372068 4.169756565048037, -87.44525077 41.573307310000004 4.1779575457039755 4.207982542446133, -87.44451404 41.57313177 4.217997941741487 4.24775297235326, -87.4436414 41.572909960000004 4.265723814125522 4.295034489077289, -87.44269214 41.57263119 4.318525595503161 4.34698739429437, -87.44168921 41.57229831 4.375330366368871 4.402478728257148, -87.44023942 41.57176906 4.458840561332181 4.483524771423513, -87.43941868 41.5714755 4.505935556822806 4.52929767866706, -87.43891253 41.571319450000004 4.534280651947483 4.557111342326999, -87.43759183 41.570939100000004 4.60755868582055 4.629283121308593, -87.4367906 41.57071692 4.6518061913666315 4.672945263279567, -87.43613255 41.57055143 4.687757538180449 4.70857698445701, -87.43531257000001 41.57036507 4.732140502383118 4.75273411271177, -87.43461669 41.570235430000004 4.769284071720904 4.789905105946697, -87.43409365000001 41.57017178 4.79673402129265 4.8175737779431, -87.43367983 41.57012143 4.818452143765171 4.839464686480262, -87.43311692 41.57004699 4.848063031618949 4.8692817460716125, -87.43275479 41.56999831 4.867121814982966 4.888469082745461, -87.43275008 41.56999769 4.86736949304759 4.88871854945269, -87.43251805 41.56996721 4.87957245321013 4.901007662243753, -87.43234732 41.56995002 4.88849656842649 4.9100184205467166, -87.43217358 41.56993258 4.897577082461794 4.919187761384147, -87.4321658 41.56993428 4.897997066407697 4.919605946804267, -87.43058878000001 41.5698503 4.979901889964822 5.002578583393728, -87.43023048 41.56984588 4.998466497811023 5.021709093176545, -87.43020164 41.56984579 4.999960706132697 5.02324882137403, -87.42347751 41.56982392 5.348315923387418 5.382241086049161, -87.4234072 41.56982369 5.351958580431528 5.385994842694748, -87.42298354 41.5698223 5.373906982611516 5.408613482874807, -87.42293337 41.56982214 5.376506297383457 5.411291991209626, -87.42269368 41.56982135 5.38892387872329 5.424088720030123, -87.41733447 41.56980361 5.666567320004106 5.71020979430627, -87.41308856 41.56981092 5.88653296562552 5.936892360439135, -87.4113062 41.5697993 5.978873821775778 6.031170612365836, -87.41023351 41.56981101 6.034451821949915 6.086868827003922, -87.40810367 41.569788190000004 6.144802154012723 6.197458117709406, -87.40678911 41.569770330000004 6.212915857962798 6.265717390764319, -87.40325576000001 41.56976679 6.39596541778883 6.449171753295459, -87.40008369 41.56973531 6.560312922752928 6.613876196268593, -87.39790526 41.56973837 6.673169674788369 6.726982081807237, -87.39582975 41.56971378 6.780707624333445 6.834751732489844, -87.39385414 41.56968768 6.883072358352365 6.937335885860068, -87.39383828 41.56968769 6.883893933700165 6.93815934951601, -87.38900638 41.56968775 7.134217167447787 7.187366361866419, -87.38561947000001 41.56968114 7.309680918668164 7.361665109180063, -87.38185581 41.56966218 7.504666498134611 7.555354430098831, -87.37809248 41.569638590000004 7.699637164711021 7.749028115630313, -87.37575444000001 41.56961603 7.820772490726085 7.869354796179333, -87.37572659 41.56961624 7.822215311753098 7.870788063926965, -87.37500010000001 41.56962169 7.859854175068904 7.908176013067675, -87.37273605 41.56959753 7.977157947563683 8.02511702993715, -87.36879469 41.5695652 8.18135751131922 8.23141346212935, -87.3636367 41.569554610000004 8.448574861933594 8.501381539266054, -87.36260968 41.56951984 8.501834903654526 8.555166226778404, -87.36194982 41.56949751 8.536054865238839 8.589722852977973, -87.3608704 41.5694651 8.592020289579523 8.646244803156044, -87.36015981 41.56942328 8.628946130877011 8.68350120497541, -87.35919069 41.56933813 8.67949532190687 8.73442004312584, -87.35819808000001 41.56924422 8.731325777785969 8.786604919971507, -87.35634753000001 41.56899866 8.828681524377316 8.884311117740827, -87.35607435 41.568954330000004 8.843160438394989 8.898796301699202, -87.35525461 41.568821310000004 8.886608675777097 8.942262437857224, -87.35161147000001 41.568179210000004 9.080477591312956 9.13491321593775, -87.34984798 41.56790851 9.173728568013757 9.227628921655647, -87.34915365 41.56781893 9.21022715534491 9.264009840734447, -87.34845014 41.56772811 9.24720891994366 9.300872135739851, -87.34693895 41.56757703 9.326191316358745 9.379794787160264, -87.34634943 41.56753357 9.356879597558873 9.410513181788886, -87.34613438 41.567517710000004 9.368074384983629 9.421718916893203, -87.34490965 41.567441540000004 9.431742621891317 9.485486753222396, -87.34361519000001 41.56739137 9.498895066528348 9.552805743713972, -87.34265597 41.56739919 9.548593415689538 9.602654654773444, -87.34134871 41.56738912 9.616323423702852 9.67059032683711, -87.34125443 41.56738804 9.621208261421998 9.67549004483713, -87.34124945 41.56738801 9.621466129945475 9.675748842435308, -87.33674257 41.5673585 9.85496769855672 9.909960400378996, -87.33665447 41.56735792 9.85953197152412 9.914538743508961, -87.3360199 41.56735077 9.892411451713997 9.947517184528513, -87.33220406 41.56732479 10.09011061588535 10.153175962393885, -87.33219914 41.56732476 10.090365377560374 10.153444544693382, -87.33084805 41.5673146 10.160366306314245 10.227201124644694, -87.32947621 41.56730472 10.231441894400632 10.302090280749722, -87.32701668 41.56727584 10.358881018546526 10.436362532974172, -87.32138427 41.56723948 10.650695485033793 10.743835912950344, -87.32010339 41.5672386 10.717055372297182 10.813757738578287, -87.31847254 41.56722637 10.801550997857703 10.902786522536976, -87.31847248 41.56722637 10.801554104706156 10.902789797870259, -87.31408451 41.5672306 11.028886028958368 11.13663506552879, -87.3137469 41.56723688 11.046382439715671 11.154331174019726, -87.31324982 41.56724612 11.072143008670537 11.180386028786263, -87.31235185 41.56728383 11.1187378634786 11.227487209067927, -87.31189997 41.5673088 11.142212039572769 11.251204905507375, -87.31151263 41.56733019 11.162333738378948 11.271535069962493, -87.31040939 41.56742132 11.219834567862563 11.329549220392256, -87.30920076 41.56755685 11.283145268505905 11.393286594946366, -87.30802156 41.5677143 11.345195333793527 11.455633095733313, -87.30638167000001 41.567950440000004 11.431701463967329 11.542460861558137, -87.30531982000001 41.56813445 11.488158889042097 11.598938362773588, -87.30465957 41.5682533 11.523333336997894 11.634096052523788, -87.3045894 41.56826542 11.527063420813647 11.637827886414291, -87.30306907 41.56852772 11.607879107119516 11.718680580042738, -87.30282184000001 41.56858372 11.62125738854229 11.731965325091426, -87.30262648 41.56862797 11.631828569094068 11.742462858162227, -87.30117913000001 41.56892 11.709470297704684 11.819842356271907, -87.3009463 41.56896698 11.721960020091501 11.832290141037213, -87.30065124000001 41.56902651 11.737788301092223 11.848064857862184, -87.30050066 41.56905689 11.745865675475216 11.856115273965816, -87.3003993 41.56907556 11.751272526089451 11.861516583322906, -87.29767352 41.56957766 11.89667123016261 12.006868767710458, -87.2968886 41.56970453 11.938266045268392 12.049150671891294, -87.29639094 41.56979156 11.96473783426336 12.076016670045915, -87.29581936 41.56989797 11.995245358950342 12.106934174206026, -87.29462234 41.57011245 12.05899822646461 12.171602585114202, -87.2922267 41.57057343 12.187115389489918 12.301334104284946, -87.29072531 41.57086998 12.267540358385304 12.382716949766314, -87.28996645000001 41.571012 12.308055294648511 12.423771935992951, -87.28992912 41.57101899 12.310048400904634 12.425791557894856, -87.28953028000001 41.57109367 12.331343121899408 12.447369515185414, -87.28908267 41.57116685 12.355074607286952 12.471488365307941, -87.28843073 41.571305360000004 12.390173993757344 12.506930789527969, -87.28695864000001 41.571618040000004 12.469426623967593 12.586959441374455, -87.28574803000001 41.57183714 12.533937127853278 12.652382637858944, -87.2842172 41.57210669 12.615391582468874 12.7350408706573, -87.2837706 41.572193410000004 12.639288290127297 12.759233584212568, -87.28313854 41.57231109 12.67302258896234 12.793422632759707, -87.28305971 41.57232577 12.677230195564334 12.797686692262612, -87.28299619 41.57233773 12.680622564803343 12.80112388873034, -87.28293836 41.572348590000004 12.683710462777526 12.80425290620616, -87.28013694 41.57289138 12.833587317931233 12.955996286573454, -87.27936132 41.57303739 12.875010075702448 12.99796637511554, -87.27871908 41.57316014 12.90934095182456 13.032859038134044, -87.27511295000001 41.57384928 13.102103580298717 13.228820268373825, -87.27390008 41.57406385 13.166653292617411 13.294562757759213, -87.27059352 41.57467967 13.343130311812274 13.474085971413144, -87.26934995 41.57490327 13.409370523222606 13.54152625394685, -87.26519996 41.57572358 13.63166910232394 13.76731885774557, -87.26181187 41.57652399 13.815656185135595 13.953136872906528, -87.26110765 41.57672224 13.854614465410123 13.992185851331762, -87.25760243 41.57770895 14.048523956400459 14.187864838163357, -87.25482571 41.57854381 14.203458390125888 14.343718435115285, -87.25017748 41.57994115 14.462808389405836 14.60461405629675, -87.25000295 41.57999107 14.472482373865205 14.614371537468495, -87.24838066 41.58049674 14.56346559881058 14.705710287942587, -87.24631759 41.58113975 14.67916735529434 14.821865163918831, -87.24476493 41.58162365 14.766242846759269 14.90928252190603, -87.24139751 41.5826336 14.954062042321311 15.09829582646401, -87.24107486 41.58273034 14.972057102466351 15.116409566040957, -87.24086164 41.58280037 14.984111306665 15.128478154594843, -87.24074691 41.58283804 14.990596979783732 15.134971853469294, -87.24035708 41.58296604 15.01263504565577 15.157036246011547, -87.2401852 41.58301861 15.022247825181694 15.166701815487423, -87.24017919 41.58302045 15.022584234873648 15.167039812959429, -87.23978067 41.58312036 15.044346039649099 15.189133594140442, -87.23959366 41.58317511 15.054742366686696 15.199612250567856, -87.23942295 41.58322505 15.06423174214433 15.209177002741432, -87.23906266 41.58333051 15.08426110536675 15.22936468077985, -87.23635554 41.58412276 15.234750693445676 15.38104711585397, -87.23594245 41.58424521 15.257753374011372 15.404216594029522, -87.23555742 41.58438075 15.279778826065012 15.426167178299753, -87.23535456 41.58444908 15.291294799608295 15.437678273665757, -87.23494748 41.58459358 15.31461636723543 15.46090739846745, -87.23159466 41.58589775 15.510192745248787 15.654366271937967, -87.23143798 41.58596956 15.519700451128301 15.663634581532065, -87.23138999 41.58599155 15.522612501401454 15.666473290722811, -87.23138709 41.58599288 15.522788286951254 15.666644857816856))

enter image description here

1
  • Awesome, thanks. This worked for me!
    – Christoph
    Apr 28 at 9:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.