0

I had first run this code on several of my fields of my T1 table at the same time.

UPDATE table_1
SET field_1 = liste.my_bool
FROM(
    SELECT T1.id AS name,
    bool_or((ST_Contains(T2.geom, T1.geom) OR ST_Overlaps(T1.geom,T2.geom))) AS my_bool
    FROM table_1 AS T1
    CROSS JOIN table_2 AS T2
    GROUP BY T1.id
 ) AS liste
WHERE table_1.id = liste.name;

But some fields as field_1 were returning a null value. This is due to the fact that table_2 was empty. I need to make additional operations on these fields and I want to be False instead of null. Therefore I went with these lines of code:

UPDATE table_1
SET field_1 = CASE WHEN liste.my_bool IS NOT NULL THEN liste.my_bool ELSE False END
FROM(
    SELECT T1.id AS name,
    bool_or((ST_Contains(T2.geom, T1.geom) OR ST_Overlaps(T1.geom,T2.geom))) AS my_bool
    FROM table_1 AS T1
    CROSS JOIN table_2 AS T2
    GROUP BY T1.id
     ) AS liste
WHERE table_1.id = liste.name

but nothing changed. I do not understand why, how can I correct my fields and avoid null values ?

UPDATE 1: here some data from my table_1, table_2 is empty and contains no row. The geometry is in 2154 and describes polygons.

id, geom, field_1
(110853, '01060000206A0800000200000001030000000100000007000000824137554C6F294144ECA1CD9BCE594113FFFFFF316F29417E6B668692CE594146323333B36E2941170500C065CE5941E0CBCCCCEC6D2941B29E99196ACE59414EF6F7FCCE6D29411813E5DD68CE594178EC67312C6E294134D8D42293CE5941824137554C6F294144ECA1CD9BCE59410103000000010000000800000042690FC17A6D2941B050198849CE5941AD989999396D2941190500A00BCE5941BF6E8411216E29416EA20354AECD5941FED7B969AC6C294189450CE4ABCD59413336741B236B29411EC4CE73B9CD5941CC7D72FC326B2941AAD6C282E2CD594139EFFF13346C29410E2F88054DCE594142690FC17A6D2941B050198849CE5941', NULL)
(111016, '01060000206A0800000100000001030000000100000011000000F88898C21B802B41EFC858F327805941A052256AC27F2B41E63BF898168059410F9887D4827F2B41465C00F70780594111AB3F22737F2B41B0C56EBAF97F5941AD13979B727F2B41CD565E00F17F5941B950F9A76A7F2B4108AD874CE87F59416490BBC0047E2B4155DE8ED408805941147AFD39A97D2B41B439CE0E0D8059414416694A637E2B41545568D92E8059418B36C7A9847E2B4182CAF8CB34805941A43506753B7F2B41CD052E6C25805941ECF6598DF97F2B41CDCB611F4E805941D0F1D19A8F802B41562DE96343805941A88C7F7F71802B4117EFC7C239805941344C6D392E802B4159BE2E392A8059417C9BFEF40C802B416A6B442326805941F88898C21B802B41EFC858F327805941', NULL)
(111018, '01060000206A080000010000000103000000010000001600000076A911FAD7812B41E2AC889DF0815941205D6CFAD7812B41CE35CC2BF1815941F581E4DDC1812B41292158D4F6815941E8A4F74DB0812B41DE5850E5FB81594126C63275AE812B41F5691526228259417C0C561CE5812B41802903302C82594116DF50C004822B41E013EBF52E82594194DC61E3A6822B412D944C2C328259411B63276C4D832B41958103CB34825941AAF3A8A80A842B41D107CB17398259415E2EE2AB1C842B41F030ED153982594190F469ED32842B41EF1B5FD13682594128B7ED4B5F842B41BDFC4EE42E8259412A5778F78B842B41C6A700C6298259419065C1E4CE842B41130F28632282594186AE440809852B41D731AE281C8259412A57787F50852B41A3AD4AE715825941C440D786AE852B41C9E8800C0F825941408523D052852B41058BC3F5F6815941C843DFBDC1822B41689599CBEF815941AED51EA6BD822B417558E1DCF481594176A911FAD7812B41E2AC889DF0815941', NULL)
(111020, '01060000206A0800000100000001030000000100000008000000B5E04567FA5E2B413DBB7C95FA7F59413332C8A5AE602B41D07D39562180594163635E6716612B41802BD95E0B805941AB5AD2C11C612B41711E4EC8FE7F594166A19DF35D612B416F2D9312E47F5941EA25C65A3A602B4166498087C87F59412104E47BFA5E2B4131975447FA7F5941B5E04567FA5E2B413DBB7C95FA7F5941', NULL)
(111021, '01060000206A080000010000000103000000010000001A000000287FF7AEB45C2B41CDCD37230D805941E65AB4B0FC5C2B418A027D931A8059416B2BF6E7395D2B41D2FC314621805941780AB912595D2B41C9CA2F8B2A805941F50F2211605D2B41F7CDFD992F8059416155BD74C15D2B4120240B8724805941B0E2545B0B5E2B41FC36C4691C805941B4C9E12B5F5E2B41D21C5973138059419A0645E38A5E2B41E02EFB920F805941BA1116C5FF5E2B41ED630554FA7F59417920B2903D602B4137DDB243C77F59414FAE2918725F2B41DE770C97BE7F594181423D850B5F2B41DAFF0038AE7F5941B0592ECB765D2B41DF6C73198E7F59419E5E296B6A5D2B412060AD9B9E7F59419E07771F7F5D2B41707A1780A47F5941D8B628CB515D2B41543A58E0C07F59418DF0F648525D2B41E4874AA1C77F5941047289EB485D2B41D656ECC2D47F5941897D02486C5D2B41ABCDFF45EA7F594141D312CB735D2B41F702B3D5F47F59419F56D157595D2B4115579501FF7F59418FFCC198235D2B41C87BD57302805941A699EE0DEA5C2B414511527E028059413D804586B45C2B4187342A710B805941287FF7AEB45C2B41CDCD37230D805941', NULL)
(111041, '01060000206A0800000100000001030000000100000009000000DB4C8560D0872B41363FFEFCF3815941AD31E8942B872B41FD2FD703FA815941185A9D7C8F862B41F853E3B0038259418B34F18631852B41F29716F81C82594184B9DD9B18862B415AF10D183E82594120B3B3A8C5862B411EDFDE603482594195F430ECC1872B41BB5E9A7B558259412F6B625175882B41E1F1EDFA2B825941DB4C8560D0872B41363FFEFCF3815941', NULL)
(119661, '01060000206A0800000100000001030000000100000004000000A8ABD0CE42892841FB2A6C34CFCC59411C81B3A98C89284197181DCED0CC5941139A24A6888928419B03047BCDCC5941A8ABD0CE42892841FB2A6C34CFCC5941', NULL)
(120113, '01060000206A080000010000000103000000010000001B00000046B1DCE2E90A2E41E0D8B33FAB975941956247C3A00B2E4190BFB456A8975941842A3533160C2E41F702B302A69759412CF52C20460C2E417E36726FA59759417D586F4C570C2E41A8E50797A1975941A9177CAA5F0C2E41A04FE4B09C9759412CF2EB7F740C2E41CFD72C7F8E975941A01683876F0C2E41F294D5CA859759412C2D233D6F0C2E419F5BE86C81975941CC09DA24550C2E410D535B8C82975941FB743C26370C2E4124810620899759417590D703190C2E41F3565DAC8F975941F9A3A803E50B2E411AFA270193975941E7A90EE9BD0B2E412769FEC59597594156D63665780B2E415D4F741C99975941706072632E0B2E417E906532999759415513444DF10A2E41BB287A1497975941D4D68890D20A2E410D5531F3949759410839EF57B80A2E41AB26886E94975941FF791A709E0A2E411A19E4A396975941C4CC3EC79E0A2E41BA675D109C9759417C293C089F0A2E41FA97A4589F9759413D27BD07A80A2E41C214E53CA497594113D55BEBCA0A2E4193533B32A4975941D9E90775E90A2E4121E4BC44A5975941425C39ABF60A2E41A9C0C96EA797594146B1DCE2E90A2E41E0D8B33FAB975941', NULL)
(120114, '01060000206A0800000100000001030000000100000014000000EE76BD64E3392E4178F17EF2DDA559415B9544D6F7382E4138DBDC18E4A55941518880AB5A382E414B92E738E6A559410531D0C503382E417829756FE0A5594156F4879E9D372E414016A288E0A55941CC62621380372E413563D113FDA55941577A6D46D0372E416F47389713A6594115FDA1792F382E417F2F85521EA65941C440D7B650382E416EA46CD437A659419F3BC11615382E41FC50690B69A65941FDF9B6887D382E41C79DD26188A65941ECC1A4289F382E4155849BD0A7A65941A8A8FA0DB1382E415648F942C6A65941B5368D4D56392E41F756240DC8A659418733BFB20B3A2E41D02A33C0CEA65941E65DF548673A2E413048FA52A6A65941D102B445803A2E41F1F09EE14AA659410D8E9227723A2E41AD855973F0A559411AFB922D593A2E413563D1DBDAA55941EE76BD64E3392E4178F17EF2DDA55941', NULL)
(120116, '01060000206A080000010000000103000000010000000900000016A24398B8482E419BAE2748B3B159414B3E76EF51482E41FA4674EBC5B159411822A7D7F7482E41354069EBE2B15941C53A554EB7492E4126A60BEBD4B15941C520B08267492E416D718DCCC6B15941C03FA5127A492E41529D0E0CC2B15941944BE33F80492E4109C03F40C1B15941F4C308B927492E41C9C7EECFBDB1594116A24398B8482E419BAE2748B3B15941', NULL)
(120117, '01060000206A0800000100000001030000000100000009000000306821790D692E41BD1DE1728DA75941CFF92916C1682E41B4C9E1E98AA7594185B01A6387682E41BAF3C4EF9EA75941B709F76A70682E41EAB46E97B1A7594119CBF4CBCE682E41DF701F78BBA75941035DFB3A3A692E4144FCC3EABAA759417D5BB04C73692E4103EACDAF9EA759415D177EB875692E414EF04D5A90A75941306821790D692E41BD1DE1728DA75941', NULL)
(120118, '01060000206A0800000100000001030000000100000011000000BFB8544D781D2E41994A3F3C9AAD5941B3B11213211E2E41213F1BF0A8AD5941D9EBDD6F541E2E414BE5ED30A7AD5941A051BADCBA1E2E41219221B2A3AD594112DC48E1291F2E41C11F7E41A3AD59415133A46A651F2E410D1824DEA4AD59411BD65486AE1F2E41E690D472A4AD594145D8F09C38202E4155A18157A6AD5941C91CCB9B71202E41B189CC4CA8AD59414BE7C3EBBE202E413BFC350098AD5941E92ADDDDAB202E411286015496AD59417F677BDCBB202E416B7D914194AD5941348463C6101F2E41D271358D71AD5941D89AADA4731F2E41DD239B2F5FAD59414FCAA4E6141F2E41B0A9F34E60AD59418E1EBF1F8B1E2E41F984EC7961AD5941BFB8544D781D2E41994A3F3C9AAD5941', NULL)
(120119, '01060000206A080000010000000103000000010000000D000000B1C22D1F78472E410917F212A4AD594128637CF849472E41075E2D73B7AD59411BA19FA97D482E4194F8DC5AC0AD59412E20B4AE3F492E41A0FCDD4AD2AD59418AC74545D1492E41D89AADE3D6AD5941A52E190F3D4A2E41C5758CAABEAD594168226C20E0492E414EB9C2B3BCAD5941970167C96F492E4100ADF98EB6AD59416D57E86BC6482E4104745FFBB1AD5941E8DB825D3E482E413EB48F78ACAD5941397F13F2C1472E41508D9763A3AD5941B9196ED87C472E4122AB5B28A3AD5941B1C22D1F78472E410917F212A4AD5941', NULL)
(120121, '01060000206A080000010000000103000000010000000D000000B22D039E955C2E41BEF5610AEC9D59410470B358035D2E411D908454EA9D59412ECA6C38815D2E4180441375DC9D5941732CEF429A5D2E4177DB8522D79D5941378B1793935D2E419FE6E431B39D5941378E586B1B5D2E41BBEEADFA999D5941EA9271CC255C2E41B24B546C949D59413B8E1F8A685B2E41F3734360A29D5941DCA0F6BB3F5B2E412B8A579EB89D5941252026D9455B2E413E416204D39D59417D3F356EBD5B2E4164ADA1CDE39D5941D0251C021F5C2E4135626686E99D5941B22D039E955C2E41BEF5610AEC9D5941', NULL)
(120164, '01060000206A0800000100000001030000000100000009000000AD35941A06C12B41C7A013506E1B5941DE205A5BF3C12B41E179A9B9781B5941D595CFDA7DC22B41D05FE891511B5941C4211B58F9C12B41F8FBC51B421B5941FF04176B83C12B41438EAD59461B59417BC03C5C28C12B4158A83567541B5941F94B8B4222C12B416B2920C4591B5941E869C08026C12B41096F0F89681B5941AD35941A06C12B41C7A013506E1B5941', NULL)
(120182, '01060000206A080000010000000103000000010000000B0000007CB60EB638412C417A54FC11C1885941C07B47250B412C4119C8B337C5885941B3EA736DC3402C41E886A653CE8859414BE999DE95402C41B4AB90F2D4885941B340BBFB40402C41067FBF9ADB8859413A77BB3606402C4177F69569E1885941B5368D8DCB3F2C41122F4FDCE8885941FF3EE342F5402C418CA207F30689594136053283C6412C4177D66E19F78859410F26C5E759412C4168B3EA4FC48859417CB60EB638412C417A54FC11C1885941', NULL)
(120441, '01060000206A08000004000000010300000001000000110000005275464F07CD294126E006565B8E594106DA1D821ECD2941041DAD155C8E5941EAAF57B82BCD2941003B37AE5B8E5941925CFE2B49CD2941429770D35A8E5941F8FF71C273CD29418602B6F4598E594103603C03B2CD2941C07ADCC8558E59418F1B7EFFECCD2941DC12B9A8538E5941795BE9D51ACE2941357EE161528E5941070B27614FCE2941249C1652528E594126016ADABECE2941664F021B4E8E59411EC2F8E1F0CD2941DA04189CFC8D59413258719A35CC29410D1CD0C1008E59411D075AA78FCC2941392F2972568E5941FCED5448AFCC29410B22D07E588E59419D5559CBF3CC2941F74ACEB45A8E5941E1D2319F14CD2941190280B15B8E59415275464F07CD294126E006565B8E5941010300000001000000040000001D075AA78FCC2941392F2972568E5941E5B853628ACC29414DDBBF1A568E59413F22710790CC294102D798CD568E59411D075AA78FCC2941392F2972568E5941010300000001000000060000003F22710790CC294102D798CD568E5941A227650291CC2941693865BC578E5941B0C343A0B3CC2941ABA2DCC6588E5941FCED5448AFCC29410B22D07E588E5941B8567B8097CC294100E65ABA578E59413F22710790CC294102D798CD568E594101030000000100000005000000B0C343A0B3CC2941ABA2DCC6588E5941020CCBDFBBCC29410936AE4F598E59415275464F07CD294126E006565B8E59419D5559CBF3CC2941F74ACEB45A8E5941B0C343A0B3CC2941ABA2DCC6588E5941', NULL)
(121257, '01060000206A0800000100000001030000000100000005000000A1681E40ADA026416E6B0B4A04C35941A6B8AA6C73A22641C2A222CD19C3594175779DB5AFA22641D506270504C35941B56B4212D7A02641EA5E2713EDC25941A1681E40ADA026416E6B0B4A04C35941', NULL)
(126155, '01060000206A0800000100000001030000000100000010000000FC00A426C5DC2C41280B5FFD39CF59419544F6C9D4DC2C41E7C75F0B3BCF594139D043CDEEDC2C41E44867033BCF59416EBE1155FEDC2C41F81741B439CF5941849B8CEA06DD2C41CC29012A38CF5941CB845FD220DD2C415E2EE2C435CF5941FCDF112D24DD2C41BC04A7AC34CF5941DCD440CB25DD2C41FC8BA08332CF59415BB0547720DD2C4144C3629130CF59418C4D2B5D14DD2C41F089758F30CF5941B5C6A0EB04DD2C41BA69336633CF59415EA10FFEF8DC2C414353767336CF594193A7AC4EE4DC2C412332AC6D38CF5941005647A6CDDC2C41E7374CCF37CF594146274BA5C1DC2C41DC0DA2313ACF5941FC00A426C5DC2C41280B5FFD39CF5941', NULL)
(127342, '01060000206A080000010000000103000000010000001600000023BE13EBAAE12B41B5C18977127A59415A9C31A4E9E12B41B5FCC01B137A59416EA301DC2CE32B411B4AEDD2FE7959414DD6A8BF3DE32B4148533DF4FB795941F48AA7F65FE32B410B4790A2FC7959412122356D26E42B4143E4F452F0795941779D0D31A3E42B41DDEC0F5AE8795941253E773285E52B41EACDA85ACC7959410305DEE939E62B4173309B12B6795941F5F6E78AE2E52B4169C537B399795941787DE67443E52B41F7CC9244A0795941C47B0E5493E42B41158BDF97A77959416C787A250BE42B41990F0820AE795941FF41248B66E32B41CE4F7172B57959416B9C4D3F39E32B41A418200DB9795941B937BF39A6E22B41F0A31ADFC8795941685E0E3B62E22B413A4030EECD7959410D17B90FC9E12B419E094DA3D6795941191BBA4196E12B41207F6914DD79594161FC34E67FE12B41FAD1703BE4795941649291B365E12B41FA7ABE03057A594123BE13EBAAE12B41B5C18977127A5941', NULL)
(127421, '01060000206A0800000100000001030000000100000025000000F4DE18FA2FF22741770FD06E133F59410C91D377DBF127415073F2F91A3F59414390832271F227416DE7FB2E653F59410B7F867F69F227411D5BCF14743F5941A0E238C894F2274171AB20997D3F5941516B9AF725F3274104914512883F59415AF278E215F32741F9872DE6953F5941F129004EE3F22741E6E8F1559C3F59415D8940D538F22741371B2B11953F5941868F88F165F2274139B35D45BB3F5941EF54C0BDC6F22741A27DAC01EB3F59411C0B0A13E2F22741363E939906405941B8020A7DF4F2274183C2A0D11B4059414BCB48C586F3274104AF963B364059412F4E7C8DF4F32741B0FF3A7C2B4059413D81B06B5BF42741B9A6402238405941DD9A74437DF527417E552E7242405941919BE176F1F52741BBB88DF1194059413B55BECF4EF6274193C83EAA16405941144207153BF7274104C8D0ECFC3F5941912C60EA51F827415E9D632DE43F59414966F5F68BF82741F67CCDC9CD3F59414E64E6E279F827416EDA8CDFBD3F59412DB4739A35F82741183E22D6BB3F59411D56B88573F72741C7293A35D03F59411FBDE126B0F627414E7E8B9CD33F5941F2D3B86F20F627415517F076DF3F5941219221B7A0F52741A243E044DA3F5941A148F7334BF52741F6EE8F10D53F5941A35C1ABFD0F42741915F3F1EA13F594138D730AB46F52741A60EF21B923F594140BD196501F52741B5A9BA0C7F3F594159A65F0A8AF4274141446AD0783F59414B01695731F4274127F6D0C2433F5941D9EDB32285F32741A14CA306233F5941D2544FEEB7F227419469343A123F5941F4DE18FA2FF22741770FD06E133F5941', NULL)

UPDATE 2: I have tried to change my field_1 with this query and it remained null. I do not understand why this simple query doesn't impact the field.

UPDATE table_1
SET field_1= 
    CASE WHEN field_1 IS NULL THEN FALSE END;
0

I think there is no issue with your secord query. It returns true or false for features which are in second table. But as your second table is empty the result from your inner select query is empty. So when you user where condition WHERE table_1.id = liste.name; no feature is updated. Instead you should have condition like WHERE table_1.id = liste.name or table_1.field_1 is null; So your Final Query would be something like

UPDATE table_1
SET field_1 = CASE WHEN liste.my_bool IS NOT NULL THEN liste.my_bool ELSE False END
FROM(
    SELECT T1.id AS name,
    bool_or((ST_Contains(T2.geom, T1.geom) OR ST_Overlaps(T1.geom,T2.geom))) AS my_bool
    FROM table_1 AS T1
    CROSS JOIN table_2 AS T2
    GROUP BY T1.id
     ) AS liste
WHERE table_1.id = liste.name or table_1.field_1 is null;
1
  • my filed_1 from my table_1 is still null after this querry.
    – Basile
    Jul 8 '19 at 7:33
0

UPDATE1: although why you want to do this escapes me (joining with empty table) this query probably does what you want.

    WITH empty as (SELECT T1.id AS name,
                          bool_or((ST_Contains(T2.geom, T1.geom) OR ST_Overlaps(T1.geom,T2.geom))) AS my_bool
                   FROM table_1 AS T1
                   CROSS JOIN table_2 AS T2
                   GROUP BY T1.id
                  )

    UPDATE table_1
    SET field_1 = coalesce(liste.my_bool,False)
    FROM (
         SELECT table_1.id, empty.name, empty.my_bool
         FROM empty
         RIGHT JOIN table_1
         on table_1.id = empty.name
        ) as liste
    WHERE table_1.id = liste.id
6
  • My field is still full of "null" values. The code does not change the values. This is something I had guessed previously. I cannot change the value of field_1 by the querry anymore...
    – Basile
    Jul 5 '19 at 8:02
  • You are right, that's because the NULL value is not returned by the bool_or function. Unfortunately I will be away for the next day's from a PC and without some sample data from the two tables it's difficult to suggest a solution
    – vagvaf
    Jul 5 '19 at 8:42
  • Ok, how can give you some data ?
    – Basile
    Jul 5 '19 at 8:57
  • Check this question: gis.stackexchange.com/questions/326433/… try to include data that cover all the cases from both tables. Also post them on the main question to help others that may want to pick it up.
    – vagvaf
    Jul 5 '19 at 9:01
  • hi, i have updated my answer
    – vagvaf
    Jul 8 '19 at 12:33

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.