5

I found a problem while calculating the geometry between two measures using line string geometry with M coordinates. Query execution results into [XX000] ERROR: MultiLineString cannot contain MultiLineString element error. The query with the geometry is below.

with binary_geometry as (
    select ST_SetSRID( ST_GeomFromText(
        'LINESTRING M (2738744.228 1240475.434 0,2738758.481 1240467.362 0.024972904817183,2738759.124 1240467.161 0.026,2738763.417 1240465.823 0.03285528442067,2738766.777 1240465.509 0.038,2738767.307 1240465.459 0.038811603855104,2738777.022 1240464.657 0.053673075335167,2738785.265 1240465.056 0.066254727146783,2738790.759 1240464.835 0.074637427007032,2738800.598 1240460.846 0.090823477697674,2738821.896 1240450.294 0.127060207313606,2738833.894 1240445.375 0.146829483135458,2738851.433 1240432.029 0.180429743437941,2738857.883 1240420.667 0.200348297758888,2738861.162 1240408.636 0.219359295440775,2738864.365 1240394.97 0.240758516950914,2738867.29 1240389.728 0.249910212293249,2738870.129 1240386.137 0.256889162566623,2738873.865 1240383.385 0.263963378231884,2738877.022 1240382.14 0.26913715533888,2738881.21 1240381.763 0.27554782501068,2738881.502 1240381.815 0.276,2738895.291 1240384.25 0.285387998519214,2738912.862 1240395.031 0.299209374679181,2738923.68 1240397.63 0.306668781168628,2738925.042 1240397.594 0.307582265096594,2738942.218 1240393.766 0.319380599644065,2738953.435 1240385.246 0.328824590491518,2738948.222 1240371.921 0.33841780038892,2738937.757 1240360.475 0.348815905275836,2738934.733 1240348.012 0.357414287426862,2738935.261 1240342.953 0.360824562218592,2738946.154 1240334.604 0.370026308130365,2738956.031 1240331.708 0.376927214266352,2738965.987 1240333.218 0.383678639877944,2738977.821 1240340.414 0.392964579447893,2738984.323 1240344.955 0.398281816562783,2738990.612 1240348.423 0.403096931882737,2739003.426 1240347.838 0.411697140364525,2739023.233 1240342.558 0.42544065865944,2739029.551 1240340.767 0.429843527229099,2739030.025 1240340.598 0.430180919966023,2739045.349 1240336.047 0.440898548135432,2739063.327 1240325.519 0.454866755150619,2739071.709 1240321.855 0.461,2739077.49 1240319.327 0.46732241600786,2739096.564 1240321.266 0.486533735604159,2739108.612 1240318.671 0.498883114575,2739110.396 1240305.443 0.512258035891824,2739123.662 1240296.698 0.528179416954789,2739138.578 1240301.587 0.543908155951774,2739146.037 1240310.149 0.555286639067593,2739153.906 1240323.618 0.570917586947127,2739165.781 1240328.927 0.583951790975074,2739174.518 1240328.703 0.592709448108322,2739177.772 1240328.756 0.595970502656933,2739180.253 1240328.723 0.598456771539189,2739183.213 1240328.725 0.601422795985917,2739184.527 1240328.668 0.602740708286117,2739192.757 1240329.572 0.611037057286578,2739201.56 1240339.942 0.624667301870796,2739215.185 1240341.267 0.638384435329364,2739222.165 1240336.799 0.646688847280844,2739231.872 1240323.514 0.663175813184277,2739237.712 1240318.691 0.670765322823181,2739247.008 1240319.251 0.680097127206118,2739255.548 1240324.362 0.690069964918916,2739262.178 1240336.13 0.703604588754964,2739267.307 1240358.582 0.726681848691037,2739270.395 1240364.18 0.733088086002326,2739274.053 1240366.812 0.737603739839025,2739281.794 1240372.053 0.746971088235724,2739289.834 1240371.99 0.755027697423146,2739317.713 1240371.774 0.782964271235921,2739328.63 1240376.783 0.795,2739331.443 1240378.072 0.798092866536854,2739340.474 1240385.587 0.809836343064673,2739346.564 1240391.287 0.818173915833018,2739359.255 1240393.903 0.831125863706403,2739369.693 1240389.092 0.8426140312556,2739371.533 1240383.241 0.848744752951345,2739379.38 1240381.563 0.856765526468744,2739391.514 1240390.555 0.871861339090203,2739397.503 1240427.622 0.909392052740726,2739409.415 1240449.861 0.934608966698231,2739452 1240475.994 0.984550494725186,2739471.759 1240497.849 1.014,2739485.378 1240488.685 1.03,2739490.336 1240485.347 1.035951132537481,2739511.29 1240479.096 1.057723186197797,2739540.507 1240488.212 1.088197069618145,2739557.891 1240492.395 1.106,2739571.143 1240479.651 1.124360121526421,2739580.871 1240462.087 1.144410503013604,2739586.757 1240446.08 1.161441884136858,2739599.406 1240443.203 1.17439606779781,2739612.446 1240437.1 1.188773734596491,2739630.542 1240425.985 1.209981428678008,2739638.016 1240422.064 1.218409875991373,2739656.435 1240418.8 1.237090067032316,2739678.036 1240412.862 1.259461495912897,2739690.268 1240415.721 1.272005861549392,2739696.977 1240404.529 1.285036693781303,2739700.074 1240393.346 1.296624620554127,2739712.966 1240394.518 1.309551944801937,2739728.019 1240388.431 1.325766699109185,2739781.173 1240388.775 1.378848562730825,2739784.546 1240386.345 1.383,2739801.57 1240374.084 1.404,2739815.254 1240364.228 1.421,2739821.333 1240359.848 1.428501046955826,2739843.466 1240367.91 1.45208327369484,2739866.868 1240346.561 1.483796119362881,2739917.035 1240365.528 1.537489560336322,2739938.638 1240348.172 1.565232279365154,2739973.199 1240352.192 1.600065643406928,2739999.92 1240349.06 1.627,2740002.446 1240293.271 1.683042576361286,2740003.634 1240290.574 1.686,2740011.795 1240269.498 1.708696340051799,2740024.018 1240243.869 1.737210768424174,2740030.428 1240227.355 1.755,2740035.784 1240212.19 1.771137145620048,2740052.136 1240190.272 1.798574839319903,2740050.576 1240180.823 1.808183971168482,2740061.622 1240176.2 1.820198659293747,2740066.057 1240145.824 1.851,2740058.991 1240103.679 1.893715083495595,2740043.761 1240044.335 1.954956205808798,2740040.207 1239993.919 2.005475849404279,2740051.775 1239974.82 2.027795503237285,2740044.27 1239944.106 2.05939970781849,2740030.7 1239919.426 2.08755239073518,2740031.047 1239874.934 2.132026844639725,2740044.57 1239831.018 2.177958241242629,2740043.276 1239817.927 2.191107451792092,2740031.561 1239810.736 2.204847583518736,2740033.776 1239776.978 2.238663803007072,2740056.451 1239748.715 2.274883102248511,2740060.13 1239727.306 2.296596681650665,2740070.657 1239694.611 2.330930028519856,2740067.448 1239678.748 2.347107481276164,2740078.262 1239643.301 2.384151588502601,2740086.558 1239635.238 2.395715407584129,2740083.276 1239623.387 2.408007245860928,2740083.123 1239620.925 2.410472947552221,2740088.004 1239580.217 2.451455110709447,2740099.105 1239541.383 2.491827455233601,2740089.936 1239514.696 2.5200336641427,2740098.503 1239497.239 2.539471235094416,2740095.323 1239470.436 2.56645075411014,2740108.78 1239446.205 2.594155983074649,2740114.883 1239413.905 2.627013537080521,2740132.159 1239399.252 2.649657183508832,2740131.752 1239378.331 2.670573253856958,2740153.092 1239358.979 2.699368924230741,2740188.382 1239303.848 2.764799589492652,2740193.375 1239297.41 2.772943391733392,2740198.32 1239290.837 2.781165306913184,2740210.061 1239275.053 2.800828904007246,2740223.135 1239255.623 2.824238059188016,2740252.183 1239225.732 2.865900784072448,2740273.563 1239201.823 2.89796121009445,2740283.245 1239196.511 2.909,2740293.639 1239190.812 2.920886879982361,2740299.663 1239175.945 2.936972653536336,2740326.814 1239151.048 2.97391327873227,2740351.246 1239122.053 3.011935045615343,2740368.728 1239115.757 3.030567987378582,2740372.354 1239094.804 3.051891665874147,2740396.064 1239079.104 3.080407739518663,2740396.539 1239078.753 3.081,2740409.131 1239069.444 3.096313190564761,2740413.228 1239066.957 3.101,2740418.271 1239051.498 3.117239754133656,2740439.635 1239031.915 3.146183580465029,2740457.69 1239013.177 3.172171017610121,2740479.62 1238982.285 3.21000663143551,2740493.948 1238934.411 3.259914163404554,2740494.634 1238929.365 3.265,2740496.323 1238916.948 3.277552309091779,2740504.102 1238903.225 3.293353157989519,2740525.121 1238895.237 3.315876470421428,2740519.284 1238886.734 3.326207384171272,2740520.025 1238880.726 3.332271034612092,2740534.283 1238873.002 3.348513918263366,2740531.765 1238859.776 3.362,2740539.762 1238853.684 3.371681399484725,2740549.667 1238854.761 3.381276413331972,2740559.358 1238844.066 3.395175362304375,2740560.966 1238840.111 3.399286903897435,2740560.434 1238835.094 3.40414550273594,2740562.588 1238829.064 3.410311936540857,2740569.55 1238824.112 3.418539585713539,2740580.748 1238820.595 3.429842944997265,2740590.629 1238815.971 3.440349025233161,2740591.084 1238804.804 3.451112081215823,2740590.51 1238797.124 3.458528764152964,2740594.96 1238778.318 3.477139590635853,2740614.557 1238764.922 3.5,2740628.162 1238761.564 3.514,2740652.682 1238764.522 3.538716523598598,2740665.003 1238749.34 3.558283862788175,2740707.843 1238763.101 3.603313914095822,2740717.49 1238748.589 3.620753063289489,2740738.301 1238750.6 3.641676870969206,2740764.961 1238762.934 3.671074041134284,2740790.803 1238778.805 3.701423577318686,2740799.32 1238795.63 3.72029577890597,2740818.554 1238808.454 3.743430456904559,2740832.947 1238832.674 3.771625710760941,2740842.633 1238841.567 3.784784990140087,2740947.618 1238887.738 3.899561241215451,2740988.32 1238913.313 3.947667818021599,2740990.679 1238928.108 3.962661076104406,2741024.241 1238947.132 4.001269115774258,2741062.337 1238965.656 4.043662145952224,2741084.074 1238968.231 4.065567748974238,2741099.554 1238976.218 4.083)'
    ), 4326) as geom
)
select ST_LocateBetween(bg.geom, 0.59, 0.6, 10) as geom
from binary_geometry bg

This problem happens within measures <0.59, 0.6> with given offset 10. I realized that after adding a small noise (3E-5, 1E-7) to the frommeasure returns a multi line string geometry with one line string.

How to prevent such situations without changing the inputs? Is it a postgis bug?

Version information (reproducible on both):
| Postgres_version | Postgis_version | | ---------------- | --------------- | | PostgreSQL 12.3 (Ubuntu 12.3-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit | POSTGIS="2.5.4" [EXTENSION] PGSQL="120" GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.3, released 2017/11/20" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" RASTER | | PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit | POSTGIS="3.1.1 aaf4c79" [EXTENSION] PGSQL="120" GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.3, released 2017/11/20" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" RASTER |

2
  • 2
    This is probably a PostGIS bug. Filed as trac.osgeo.org/postgis/ticket/5001
    – dr_jts
    Sep 22 at 16:49
  • Thanks for the hint. I'll track the status. Sorry for wrong table format. It's happens on postgis versions 2.5.4 and 3.1.1
    – milan
    Sep 23 at 7:16

Your Answer

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

Browse other questions tagged or ask your own question.