PostGIS same query and same geom produce different output when writing hex or text formatted geom

Question

the same geom(etry) once in hex code and the other one in text formatted produce in text formatted a linestring (which is correct!) and once a multilinestring when using the raw hex geom (which is wrong!). I really want to use the hex version instead.

Any suggestions what I did wrong here? Is it a postgis failure?

Ubuntu Server running Postgres 10.3 and PostGIS 2.4.3

produce linestring

SELECT St_astext(St_intersection( 
'LINESTRING(11.5736637510182 48.1390218606209,11.573592882129 48.1390114094508,11.5735928821289 48.1390114094507,11.5733608120585 48.1389746616955,11.5733364502145 48.1389754622118,11.5730989637368 48.1390299902221,11.5730989637382 48.1390299902218,11.5730186788732 48.1390475214072,11.5730150087315 48.1390484461904,11.5729041184079 48.1390802434127,11.5729041184126 48.1390802434113,11.5723768546286 48.1392312330258,11.5723768546392 48.1392312330228,11.5723407813035 48.139241525414,11.5723398565708 48.1392417977218,11.5722914565656 48.1392564977218,11.5722914264936 48.1392565068644,11.571292826386 48.1395604068644,11.5712928143527 48.1395604105279,11.5712219781421 48.1395819850059)'
:: geometry, 
'POLYGON((11.5731042349299 48.1390367333675,11.5731031978305 48.1390314903596,11.5731006524699 48.1390264835186,11.5730966966651 48.1390219052542,11.5730914824357 48.1390179315066,11.5730852101618 48.1390147149844,11.5730781208831 48.1390123792967,11.5730704870364 48.1390110142024,11.5730626019857 48.1390106721615,11.5730547687486 48.1390113663181,11.5730472883516 48.1390130699965,11.5730404482617 48.1390157177252,11.5730345113396 48.1390192077537,11.5730297057378 48.1390234059623,11.573026216133 48.1390281510163,11.573024176629 48.1390332605662,11.5730236656034 48.1390385382547,11.5730247026949 48.1390437812634,11.5730272480491 48.1390487881062,11.57303120385 48.139053366373,11.5730364180785 48.1390573401234,11.5730426903548 48.1390605566482,11.5730497796386 48.139062892338,11.5730574134926 48.1390642574335,11.5730652985515 48.1390645994747,11.5730731317965 48.1390639053171,11.5730806122 48.139062201637,11.5730874522938 48.1390595539058,11.5730933892167 48.1390560638746,11.5730981948162 48.1390518656634,11.5731016844158 48.1390471206073,11.5731037239125 48.1390420110562,11.5731042349299 48.1390367333675))'
:: geometry));

produce multilinestring

SELECT St_astext(St_intersection( 
'0102000020E610000014000000510C5341B7252740E465E477CB114840D7EC5BF7AD25274052AE3820CB11484099EC5BF7AD2527404FAE3820CB114840B932618C8F252740337AF5EBC91148404D8DEE5A8C252740B992ACF2C9114840053E353A6D2527406A9516BCCB1148402A41353A6D2527403D9516BCCB114840EE834BB4622527405684264FCC114840DF3C2539622527404479E856CC114840939C48B053252740127DA461CD114840ECA648B053252740557CA461CD114840EF3B3F940E252740BB363C54D211484047533F940E25274010353C54D211484036BBD3D909252740F5F292AAD21148407956CCBA09252740C2B9DBACD21148407B75C362032527403ABC2B28D31148405B24C161032527406A5E3F28D3114840C9884B7E8024274041A68B1DDD1148403C2BE47D802427404A84931DDD114840EEC0053577242740055B8ED2DD114840'
:: geometry, 
'0103000020E61000000100000021000000457214EB6D252740C360A7F4CB1148408FD447C86D252740BB1AACC8CB1148403E59DF726D252740BBFEAB9ECB114840813E23EE6C252740483F4478CB114840EF5A2D3F6C2527406AB0EE56CB114840BAEEB66C6B252740A642F33BCB114840157ED67E6A252740BD685B28CB114840A73FB07E6925274040E4E71CCB114840C62D1C76682527403D5D091ACB114840382F456F67252740E80DDC1FCB1148402A10457466252740FEAC262ECB1148407423C18E6525274040A15C44CB114840A65D8BC764252740B368A361CB1148407C8F4B266425274049FEDA84CB1148405C1534B16325274053EBA8ACCB114840C4E0C46C632527400C9885D7CB1148406E339F5B632527400158CB03CC114840C3BF6B7E63252740839EC62FCC114840012DD4D3632527407DBBC659CC1148401E3F905864252740487C2E80CC114840D520860765252740A70C84A1CC1148403A92FCD965252740D97B7FBCCC114840520EDDC766252740E75617D0CC114840B75C03C86725274013DC8ADBCC114840A38097D068252740346369DECC11484096906ED76925274010B296D8CC114840B5BD6ED26A252740FE114CCACC1148400AB3F2B76B252740631C16B4CC114840B47A287F6C2527406E53CF96CC114840AE4368206D2527406CBC9773CC1148405BB27F956D2527403DCEC94BCC114840FDD6EED96D252740D820ED20CC114840457214EB6D252740C360A7F4CB114840'
:: geometry));

user30184 · Accepted Answer · 2018-05-24 12:43:09Z

There may be something odd in your linestring. OpenJUMP finds a topology error at or near POINT ( 11.573098963737486 48.139029990221985) from the ::geometry version. PostGIS ST_IsValidReason does not complain but there are indeed very close vertices even in the WKT version

POINT (11.5730989637368 48.1390299902221) POINT (11.5730989637382 48.1390299902218)

Generally it is a wrong assumption that geometries expressed as binary or WKT format are equal at precision of 13 desimals. Conversion between binary and desimals does not round-trip with computers. You can try by playing with the decimal to binary/binary to desimal converter at http://www.exploringbinary.com/binary-converter/

PostGIS returns LINESTRING with a little bit simplified (tolerance=0.00000000001) linestring

SELECT St_astext(St_intersection(
ST_Simplify( 
'0102000020E610000014000000510C5341B7252740E465E477CB114840D7EC5BF7AD25274052AE3820CB11484099EC5BF7AD2527404FAE3820CB114840B932618C8F252740337AF5EBC91148404D8DEE5A8C252740B992ACF2C9114840053E353A6D2527406A9516BCCB1148402A41353A6D2527403D9516BCCB114840EE834BB4622527405684264FCC114840DF3C2539622527404479E856CC114840939C48B053252740127DA461CD114840ECA648B053252740557CA461CD114840EF3B3F940E252740BB363C54D211484047533F940E25274010353C54D211484036BBD3D909252740F5F292AAD21148407956CCBA09252740C2B9DBACD21148407B75C362032527403ABC2B28D31148405B24C161032527406A5E3F28D3114840C9884B7E8024274041A68B1DDD1148403C2BE47D802427404A84931DDD114840EEC0053577242740055B8ED2DD114840'
:: geometry,0.00000000001), 
'0103000020E61000000100000021000000457214EB6D252740C360A7F4CB1148408FD447C86D252740BB1AACC8CB1148403E59DF726D252740BBFEAB9ECB114840813E23EE6C252740483F4478CB114840EF5A2D3F6C2527406AB0EE56CB114840BAEEB66C6B252740A642F33BCB114840157ED67E6A252740BD685B28CB114840A73FB07E6925274040E4E71CCB114840C62D1C76682527403D5D091ACB114840382F456F67252740E80DDC1FCB1148402A10457466252740FEAC262ECB1148407423C18E6525274040A15C44CB114840A65D8BC764252740B368A361CB1148407C8F4B266425274049FEDA84CB1148405C1534B16325274053EBA8ACCB114840C4E0C46C632527400C9885D7CB1148406E339F5B632527400158CB03CC114840C3BF6B7E63252740839EC62FCC114840012DD4D3632527407DBBC659CC1148401E3F905864252740487C2E80CC114840D520860765252740A70C84A1CC1148403A92FCD965252740D97B7FBCCC114840520EDDC766252740E75617D0CC114840B75C03C86725274013DC8ADBCC114840A38097D068252740346369DECC11484096906ED76925274010B296D8CC114840B5BD6ED26A252740FE114CCACC1148400AB3F2B76B252740631C16B4CC114840B47A287F6C2527406E53CF96CC114840AE4368206D2527406CBC9773CC1148405BB27F956D2527403DCEC94BCC114840FDD6EED96D252740D820ED20CC114840457214EB6D252740C360A7F4CB114840'
:: geometry));

This image shows what is the problem with the original linestring. The linestring meets three times at the same place.

What PostGIS ST_Intersection does is to model the linestring as a three part multilinestring with no self-intersections:

MULTILINESTRING ((
        11.573102072341849 48.13902927647183, 
        11.573098963737486 48.139029990221985
    ), (
        11.573098963737486 48.139029990221985, 
        11.57309896373682 48.13902999022214, 
        11.57309896373825 48.13902999022182, 
        11.573098963737486 48.139029990221985
    ), (
        11.573098963737486 48.139029990221985, 
        11.57302581221862 48.13904596375373
    ))

This is probably a case that can't be done right. You would like to get the geometry as original, self-intersecting version while other users (including me) may like more the current behavior that returns multilinestring with simple parts. Self-intersecting linestrings are not invalid like self-intersecting polygons so both options are principally right.

Have you considered to fix your source data?

But is it not strange that when cutting a polygon against a linestring that the hex version is producing (in this special case - 99% it worked correctly) a multilinestring? But thy for sharing, fixes not 100% my issue as i want to have the exact linestring segment as it is in the input geom, except of course for the start and endpoint which are cutting points. — blackgis
– blackgis, Commented May 24, 2018 at 12:07
Yes I know that there is an issue and because PostGIS and also QGIS are not complaining at all about this linestrings we assumed it is fine to do it like this but thx for your very detailed explanation we have to fix that ;-) - THY so much! — blackgis
– blackgis, Commented May 25, 2018 at 8:11

Stack Exchange Network

PostGIS same query and same geom produce different output when writing hex or text formatted geom

1 Answer 1

Hot Network Questions

PostGIS same query and same geom produce different output when writing hex or text formatted geom

1 Answer 1

Related

Hot Network Questions