2

I'm trying a simple intersection between line and polygon

SELECT p.id,
st_length(st_intersection (l.geometry,p.geometry)) as partial_length,
st_length(l.geometry) as line_length
FROM line l ,  polygon p
WHERE st_intersects (l.geometry,p.geometry)

the query returns:

id  partial_length  line_length
17020   10389   32248
17125   5047    32248
17159   3151    32248
17195   2568    32248
17196   3905    32248
17200   2009    32248

Sum of partials lengths are not equal to line_length.

Polygons are adjacent. I cannot post the geometry because I exceeded character limit

Here's an image of line and polygon with id: enter image description here

original image

Line maybe has some overlaps. It may be the cause?

l.geometry is
"0102000020E610000070010000060609B999282440DED829D9F7A9464077DC9E9586282440A19857E0EFA94640F82F4670BD282440A9B1655BECA94640D6412066E628244085A6661AE9A94640B10A36891B2924403A877B1EE4A946402132FE11612924403E48F25FDEA94640F6B62143A3292440D688E448DBA94640FDF4B644E9292440B9FDAD94D9A9464008D64513962A24401C6CF454D3A94640E9AB75DC9D2A24403D7A2192F6A946408ED159039C2A24403FDAE07203AA464047F220F0B22A2440B9D4997739AA46405880BE9E46292440FD72BA913EAA46404D95287AD227244070F3CF3954AA46402CD45096712824406AFEDB6E92AA4640B44AA6EB84282440493A50929CAA464055D6F56F132824407BDF41049EAA4640A2EF1190DB25244034E1F6F6A2AA4640160BEDABA02524404F1D03E8A1AA4640F4DDB5AC5B25244091F1F79B9FAA46405B0E0BF537252440703FAB0C9EAA46408761B63317252440C4C83EEC9BAA4640A2253932F32424406CDB31D598AA46406925331FDA242440A795E37A96AA4640D76F4AC6BC242440CC5BF2F592AA46405C4FCBD766242440486CFCA187AA46408E60AE5D3B24244002997EBA80AA4640BF098994B5222440B23ED08A3AAA4640D20F08173F222440832FF59D24AA4640E1B2AC6B342224403A49B34723AA4640552B23AEA22124402BBC79AE09AA464011EF8F7C972124400515380909AA464056C6E226D3202440194E8A9BE4A946401F07CB53B320244025973FDEE1A94640D2D63C3A932024408BDA1862E1A9464082762C8C102024401251D080E9A946407EF31A24FF1F244039334B15EAA946401DAC7848F91E2440B75BA156FCA94640A02E10CDA21E2440D6FAD42A03AA4640D1280B58201E2440592DDE8F0CAA46409D8FFCC44B1D24407E7B2CDA1AAA4640CADE2C0CAB1C2440BEB44A3C21AA464045079E9C591C24403DDDDCFA20AA46402BA3CC929D1C24404AC4CFAD53AA4640BB6B487C511C2440FE0A00965BAA4640E73EEC031A1C24403AF1FBEA5FAA4640964B6640F11B2440D0056DAF5FAA464020F46318C11B24404C26EDAB60AA46404AAC71DE981B24401F7394D562AA4640A0159CEA7E1B244015DFDF4366AA464042FDA54B641B24405F86FBDA6BAA46403F89848A3C1B2440BC9E7D7C80AA4640503348F4301B24404BAE2E7683AA4640C67CA6760C1B24406626E64C8AAA4640BFB2D583B71A2440767C864193AA46405715A93E971A2440F579100E95AA46400B0565C3731A24408D7FAC4D96AA4640C73D2D4D331A24406AC09DBF97AA4640069D6CC60D1A244071E47AAD78AA464083F3A23C7E1924401EC118B17CAA46409F630D4DBC182440A106795D80AA46403EE9B198871824405D2D418182AA46404633F9AFF8172440337FAEF589AA46404ECEB76FD3172440334A11958AAA464000591B26921724408888F4928DAA4640DC3F6B426B1724401B529EF08FAA4640D14CB6FE23172440DD5B853C93AA4640E1CB38FA211624401CD9BA729BAA46402E923061C7152440658F2A169DAA4640A31228674D152440B43ECD81A1AA4640EFF9704EF91424402A62E11AA5AA464053D67E6CC61424405983B9DFA7AA4640959D55114514244063F7ED15B0AA4640BA10FD3AA7122440444D3D0FC7AA464003119F8C43122440DF7FF7C3D0AA4640A3ACD3D8A4102440D7748538F4AA46401692DD4A94102440E251200FF6AA4640ABEBC7887E10244036FA3CB4F7AA4640AFB248EE4A102440411DA96BFAAA464034059236161024403E4678C9FBAA4640A0D94789F90D244013A9E6A9F9AA464010CC2868C20D2440B0581A79FAAA4640C086C5BBA70D2440D24CBC89FBAA4640F86A378D250D2440C380547904AB464066079531150D2440846BCA4A01AB46406614CBC6690B244081F8756E6EAA46404384178F200B24400DD6682255AA4640C4C37549780A2440195973C118AA4640293147A07C092440981019FDC1A94640F01A8AD36A09244005446CA7BCA94640114A0F56D207244090143F042FA946407AA26374800724408F052EB914A94640CDB77655DD062440D46CB28FDDA846407A9F995C6C062440973798C5B9A8464046862716F80524401110B05593A846403346E477870524404051DDAB72A84640E817D6E8550524401111284569A84640DB6D14131A0524402AC0043D56A846401A149622000524409F135C304BA846405C5EF6B7F4042440FEFBFEF344A84640FC136A2108052440B89F0A8A40A84640C46CE6554A052440CC75740A35A84640E8BA0BB6520524408D29652133A84640B0A2FBCA58052440FEA3558730A84640B00DD6D65D0524409EA4126D2CA84640D1333F125F052440176C9AB128A84640973DE58F490524404DCB5FCE15A84640BB751550380524400B4503990DA84640D6B8A4A92805244035321BD108A846403AED12B00D0524409276342304A84640F3FC04ADEE042440B840386500A8464087CEE43EE004244014340090FFA746403B4D3346C1042440C97C800AFFA74640607A98429B0424400E19826BFFA746403E3C67109F042440503677F302A8464096EA7E9AAB0424405D1D77DF05A84640A131CC37BC042440C84A9D4F07A846401E631EFCC404244090C7E62407A84640999B5798CD04244028A40CB707A846401EA88EDFD9042440D1F46BE609A846400A385B8EDB042440DC8350FE0CA84640507619ECD1042440DF6B58AB0FA8464055C76689C4042440D383298D0FA846409B66FD8ABC0424402B29E9D40EA84640479E0A66B6042440758259AA0DA84640C31A56DAB10424407E4B9A760BA846403CAD0E3BB6042440843A485B08A846409A36B749B10424403ACC447306A8464052C7EABBA6042440AC1AB1FF04A846403E3C67109F042440503677F302A84640607A98429B0424400E19826BFFA746403B4D3346C1042440C97C800AFFA7464087CEE43EE004244014340090FFA74640F3FC04ADEE042440B840386500A846403AED12B00D0524409276342304A84640D43067AD2B0524401D2AF28209A84640BB751550380524400B4503990DA84640973DE58F490524404DCB5FCE15A84640D1333F125F052440176C9AB128A84640B00DD6D65D0524409EA4126D2CA84640B0A2FBCA58052440FEA3558730A84640E8BA0BB6520524408D29652133A84640C46CE6554A052440CC75740A35A84640FC136A2108052440B89F0A8A40A846405C5EF6B7F4042440FEFBFEF344A846401A149622000524409F135C304BA84640DB6D14131A0524402AC0043D56A84640E817D6E8550524401111284569A846403346E477870524404051DDAB72A8464046862716F80524401110B05593A846407A9F995C6C062440973798C5B9A84640CDB77655DD062440D46CB28FDDA846407AA26374800724408F052EB914A94640114A0F56D207244090143F042FA94640F01A8AD36A09244005446CA7BCA94640293147A07C092440981019FDC1A94640C4C37549780A2440195973C118AA46404384178F200B24400DD6682255AA46406614CBC6690B244081F8756E6EAA464066079531150D2440846BCA4A01AB4640F86A378D250D2440C380547904AB4640521949F2280D2440DF75A7D905AB464087AB4B3C8D082440B9985F6240AB46407E1E28664E082440D6959E1B42AB4640CAB849678B052440C0CA7A9959AB4640017641DF18052440AB2C5473B8AB4640B257FFC4140524404D676F59BDAB4640EBBD91B8130524405C4EFEF4C6AB4640456CAEF72F0524402A5B393B08AC4640CEBB03942E05244091B8917615AC46400A75F2CE5505244084676F6215AC4640E2871136FE052440D395998911AC4640B700362527062440DBC5C6DD0FAC4640D177F7FC26062440608A03EE24AC464036C9476C23062440984A757627AC4640E8B4424E1C062440BB922EC429AC464016A16F2352052440E132F09A2AAC4640D6308426370524404129FF212BAC4640828A1092300524400FC2083527AC4640AF7BBED52C0524403AA757D41CAC4640456CAEF72F0524402A5B393B08AC4640EBBD91B8130524405C4EFEF4C6AB4640B257FFC4140524404D676F59BDAB4640017641DF18052440AB2C5473B8AB4640CAB849678B052440C0CA7A9959AB464035A10600800524405CE21F9659AB46403855AF12E201244088D2DAF174AB46405014DDDEED002440153699E17CAB4640A299A3407D0024409991DEC681AB4640F1A1E13FB8FF2340058BA8B38CAB4640216C097A5FFC2340817D9942B9AB4640AAA912930FFC2340B3193F9ABCAB464043F480C561FB2340F0E24CAFC7AB4640C40522122CFB234059058E46CCAB46405EAB05C601FB2340B3952AE8CEAB4640954637E123FA2340888E4FCCD5AB464069B612BEB8F7234075F79BA2DFAB46409386470B7FF72340A0AB8AE0E0AB46404252733E4BF7234021CF84B9E3AB4640B3300B2722F72340DD1BFDC3E6AB46404D8DB8810EF523401233927011AC4640018E456A53F823408953B52DDDAB46409386470B7FF72340A0AB8AE0E0AB46404252733E4BF7234021CF84B9E3AB464014FEF7678FF32340CBF7823030AC46400B01B47F14F323404059A2B83AAC46400C198889BFF22340E031132C43AC464004E046E09AF223402EB02E1248AC46406A814AB621F223402E49635454AC464028325705FEF123405B877E8958AC4640B30AFC48E9F1234088A929B25BAC46407A2CAF36B4F123401735B0F267AC4640CB723A6665F123404CC5AAD76EAC4640D6E23A6E45F123406CC256B870AC4640D205BEB5D0F023400FE7CDA175AC4640A2533C09CEF02340C650046E7FAC464085C9C387CFF023404F7F087984AC4640A1C87573D6F023406A62AA9589AC464077AE8B0F05F123403D86B26D9EAC4640E582179DC7F023407B7355ECA4AC4640EDC9FB9F68F02340649F82E8ADAC46400190586005F023402B15E585B6AC464074204809FEEF23408BE11A89B7AC4640990662C4F8EF234019685F13B9AC4640C5C9EB6CEEEF23408CAA2DC5BFAC46408570FDCEE3EF23405E79FA50C2AC4640A67186DABCEF23406A82D215C5AC464066164F647CEF2340D5FFA523C8AC464029130F3561EF2340F1F1EA5ECAAC46408375C5C8EDEE2340CABC4166D7AC4640F9AABC0BBBEE2340CF9A106EDBAC4640B21FF5452EEE2340E0EA28B0E2AC464073C2F6B3FFED2340EB7FA6D3E6AC46407429BB80D7ED2340EAC55492EBAC4640F059448FBEED23409B33DBC1EDAC46406E859CED76ED2340A8720071F5AC46401792ED755DED234055629B47F7AC4640BB92ED223AED2340D02708B7F8AC46406ABB68701BED234097E6F30FF9AC4640AA800EE9E8EC234023942B83F7AC46406E4551BBB7EC23405540E176F5AC46403FD46D1AB0EC2340FC57F1BCF2AC4640BF5B00FCA5EC234089B2D9CEF0AC4640122BCAAD95EC2340639BED62EEAC464095DABACD89EC23400E36C4B2EDAC4640A5DCFA645AEC2340C65D43EBEBAC4640F2AD9BCD1CEC234026A1D2C4EAAC4640E282AFC205EC2340CB50B3E4EAAC464078A8C13DE5EB2340AF1154DBEBAC46400AB35595BCEA23403296ECBCF8AC4640B60E6A236AEA234083E9D14201AD4640ECD210E630EA234032D31CF905AD4640C834BCBB10EA23402F1109B407AD464090CAC86DD0E923400DCF8E7A0AAD464059958F9362E92340B7708C8E0BAD46400CEB08BC21E92340F2719D910DAD46403D197D0F89E82340AA11181D18AD46403F52BF1267E82340501347E619AD46408A245F5915E82340B936A74A1CAD464045FD4F36A6E7234017194A0C1EAD4640C43F4D2308E723406ADE95671FAD46402B76CA9630E523402C9C13FA27AD46404DEAACF4D4E42340E3D8BAC128AD4640246DA2B39FE32340EE9151C02CAD46409C3227863EE32340E21B7EF32CAD4640DB0BEE2182E2234050E64E582CAD46404314CE0976DE2340EF56FC542CAD4640292781D566DE23401E6EA2165CAD46403B981E18BDDD23400B9493C45AAD4640F8DD10CA5ADD2340D0A4E77A59AD4640DBE9B8F36DDD234035BD8D902CAD4640DB0BEE2182E2234050E64E582CAD46409C3227863EE32340E21B7EF32CAD4640246DA2B39FE32340EE9151C02CAD46404DEAACF4D4E42340E3D8BAC128AD46402B76CA9630E523402C9C13FA27AD4640C43F4D2308E723406ADE95671FAD464045FD4F36A6E7234017194A0C1EAD46408A245F5915E82340B936A74A1CAD46403F52BF1267E82340501347E619AD46403D197D0F89E82340AA11181D18AD46400CEB08BC21E92340F2719D910DAD464059958F9362E92340B7708C8E0BAD464090CAC86DD0E923400DCF8E7A0AAD4640C834BCBB10EA23402F1109B407AD4640ECD210E630EA234032D31CF905AD4640B60E6A236AEA234083E9D14201AD46400AB35595BCEA23403296ECBCF8AC464078A8C13DE5EB2340AF1154DBEBAC4640E282AFC205EC2340CB50B3E4EAAC4640F2AD9BCD1CEC234026A1D2C4EAAC4640A5DCFA645AEC2340C65D43EBEBAC4640122BCAAD95EC2340639BED62EEAC46403FD46D1AB0EC2340FC57F1BCF2AC46406E4551BBB7EC23405540E176F5AC4640F4EBE8D7ABEC23408545144BFCAC4640EA313185A3EC23404100443304AD46401F0D0FDBA1EC2340D6A28FB90FAD4640C7FA7F8C9FEC2340075B7B1E14AD4640562F4A7D9AEC234001DE40BF16AD46404321B16C93EC2340F625C11D19AD4640AC6FC9AD84EC2340CEBC5ED91BAD4640D071062A11EC23402C20A2BF36AD46404CA32CC0AFEB2340B298556353AD46403B73B80478EB23406614A20167AD464011E7B70D57EB234089F15BC274AD464003CBCC3041EB234051FEE72280AD46409F8B9B05F9EA23407C54879EACAD464088ED5092EFEA23400B1A5086B1AD4640D5DD97E4E3EA2340BBE6478DB5AD4640AC24A112ABEA234032B427B0C3AD464047C42C3A31EA2340E3A1C764DFAD4640EC811817FCE9234071316D23EAAD4640EDAB81D2D7E9234003EB3D58F0AD4640B4CDF2273DE92340D9D5D27D0EAE4640200AC169C7E8234074C3663527AE464093BB0944C1E82340300DB64728AE46401701736EB8E823404DDE47B728AE4640D951C58738E823404D4A654049AE4640EF797B5BCFE7234085CA8B8668AE4640CAE39DF83DE72340E330E66997AE46404DB1A41128E72340019D34E6ADAE46404F54FAAD26E72340DD35FE47BCAE46402842B47829E723406ED25171C6AE4640DB290D8C23E72340BC611050DBAE4640520D51D11AE7234091156FB2E5AE46405F2A545403E72340ADCA171FFAAE46408D3BBF8DF5E623404C6B98A60EAF4640B963A71DF3E62340DAA8160627AF464067FADEE3FDE62340551FA26B38AF46401CCB3CB9FCE623405B57C5F43BAF46402110D23B86E623404094D7287BAF4640A6C75F9675E6234054283E2280AF46407EC1A70462E62340A747555883AF464087B7B19A29E623409844C3D580AF4640C763644492E523401ECADC6F7DAF4640E4BE70379CE42340108D94C57CAF46406D0B9A9869E4234030F5BB417DAF46405D083B9B3AE42340D6516F0D7FAF464080F327F8DFE323401505301298AF4640A32FCDC4EEE22340BE2E3E94DDAF46402F53BE0FF5E12340E76C15F921B04640504B2907EDE023409F2F53B26EB046405081F23AA8DE2340FDE4512F1CB146406C6A4DEF84DC2340A690B898B4B14640DFD27FFFF2DB2340D64A49D0DBB14640A8AA3711E1DB2340675E3FB6E1B146403515A6EBC2DB23406BE0DBCFF5B14640133A1113ACDB23406EB5EDA2FEB1464011C788A097DB2340D44EE15404B2464074D5BB528ADB2340515B615D09B246405414505F73DB2340B063126816B24640E955F75964DB2340D1757CD12FB246402D54485F51DB234061A022DD58B246401BA0C8DD41DB23404CF2989972B246408361329F39DB2340AA71D07378B246400D9FFD9D30DB2340C6F787A07CB24640E675311426DB234003C41AD47FB24640F7C7F7A400DB2340503CB5FE86B2464064AB6F9748DA2340E7262A31A3B246404324847819DA2340AC32E9A3AAB246400C42DFC204DA234052D18FC6AEB246400A81E85FE7D92340ED36848BB6B2464027906339DBD923402CC2512ABBB24640688D7B86B9D92340CB17161DCBB2464084CC16CDA1D923409B60676DD8B246400B2C88BB61D92340AFAC3670D6B246401710F3884CD92340C2AEBAFEE3B24640"
21
  • If the line is not totally inside your polygon the intersection is shorter than the original line. Or perhaps I do not understand what you are doing. Could you add a screenshot of your geometries, or one line and one polygon as WKT?
    – user30184
    Nov 17 '16 at 13:53
  • Please update your question to include what your final goal to achieve is.
    – kttii
    Nov 17 '16 at 14:44
  • Your query columns (l.id,p.id,partial_length,line_length) don't match the result columns (id,partial_length,line_length). I only noticed because I was wondering if you would please turn on labels on your image to show the polygon IDs.
    – kttii
    Nov 17 '16 at 14:55
  • sorry. I edit query and image
    – franco_b
    Nov 17 '16 at 15:05
  • Does ST_3DIntersection give a different result than st_intersection?
    – kttii
    Nov 17 '16 at 15:16
2

UPDATE

The line has some self-intersections that are being dissolved by ST_Intersection.

You'll need to dissolve those on the line_length yourself with ST_UnaryUnion:

SELECT SUM(ST_Length(ST_Intersection(l.geometry,p.geometry))) as sum_partial_length,
    ST_Length(ST_UnaryUnion(l.geometry::geometry)::geography) as line_length
FROM l,p
WHERE ST_Intersects(l.geometry,p.geometry)
GROUP BY l.geometry

The sum of a partial line is always going to be less than or equal to the length of a line.

WITH l as (SELECT 1234 as id,ST_MakeLine(ST_MakePoint(2,0), ST_MakePoint(2,3)) as geometry),
p as (SELECT 2345 as id,ST_MakePolygon(ST_GeomFromText('LINESTRING(0 2,4 2,4 4,0 4,0 2)')) as geometry)
SELECT l.id,p.id,
    ST_Length(ST_Intersection (l.geometry,p.geometry)) as partial_length,
    ST_Length(l.geometry) as line_length
FROM l,p
WHERE ST_Intersects (l.geometry,p.geometry)

enter image description here

enter image description here

and with the line contained within the polygon:

WITH l as (SELECT 1234 as id,ST_MakeLine(ST_MakePoint(2,0), ST_MakePoint(2,3)) as geometry),
p as (SELECT 2345 as id,ST_MakePolygon(ST_GeomFromText('LINESTRING(0 -2,4 -2,4 4,0 4,0 -2)')) as geometry)
SELECT l.id,p.id,
    ST_Length(ST_Intersection (l.geometry,p.geometry)) as partial_length,
    ST_Length(l.geometry) as line_length,l.geometry,p.geometry
FROM l,p
WHERE ST_Intersects (l.geometry,p.geometry)

enter image description here

enter image description here

5
  • If polygons contains all the line this is not true
    – franco_b
    Nov 17 '16 at 14:18
  • @franco_b I updated the answer to include the results from when the line is enclosed within the poly which proves my statement.
    – kttii
    Nov 17 '16 at 14:25
  • @franco_b Perhaps I am misunderstanding but, the sum of a partial line (3 or 27) is always going to be less than or equal to the length of a line (3 or 32).
    – kttii
    Nov 17 '16 at 14:43
  • I have line completely enclosed in 6 polygon. Line length = 32 km. Sum of 6 partial line length after intersection beween line an polygon = 27 km. And I don't understand where is the error because I expect the same length.
    – franco_b
    Nov 17 '16 at 14:46
  • @franco_b I updated the answer.
    – kttii
    Nov 19 '16 at 3: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.