Share
Embed
Db2でシェアサイクルポートの地理情報分析をやってみよう! / 20220928-Db2-Spatial
2022年9/28(水) オンライン開催
Db2でシェアサイクルポートの地理情報分析をやってみよう!
https://ibm-developer.connpass.com/event/259583/
の資料です。
Kyoko Nishito
September 28, 2022
Transcript
48 slides
-
github.com/kyokonishito 2 twitter.com/KyokoNishito www.linkedin.com/in/kyokonishito qiita.com/nishikyon speakerdeck.com/kyokonishito ށ ژࢠ ຊΞΠɾϏʔɾΤϜגࣜձࣾ ςΫϊϩδʔࣄۀ෦
%BUB"*5FDIOJDBM4BMFT ຊ*#.ʹͯओʹ*#.ͷࣾγεςϜ։ൃɾอकʹैࣄ͠ɺཁ݅ఆ ͔ٛΒอक·Ͱɺ։ൃऀɾ1.ɾΞʔΩςΫτͱͯ͠ຊɺ"1֤ࠃ ͷੜ࢈ཧγεςϜߪങγεςϜɺ*5ࢿ࢈ཧγεςϜ ɺ෯ ͘ܦݧ͖ͯ͠·ͨ͠ɻͦͷޙ*#.%FWFMPQFS"EWPDBUFͱͯ͠։ ൃऀͷܒ׆ಈΛߦ͍ͬͯ·ͨ͠ɻ ݱࡏData and "*ςΫχΧϧεϖγϟϦετ͍ͬͯ·͢ɻ 8PNFOJO%BUB4DJFODF 5PLZP!*#. ։࠵ ୈ ճ ຊ౷ܭֶձ౷ܭڭҭ ड -
ຊͷ"HFOEB ཧۭؒੳʁ %Cͷཧۭؒੳػೳ ϋϯζΦϯ ·ͱΊɾ2" ©
2022 IBM Corporation 5 -
ཧۭؒใ ཧɾۭؒʹ͚ؔͮΒΕͨใ • ॅॴͷೖͬͨࣄۀॴҰཡ • ࣸਅͱࡱӨॴ • ෩ͷݱࡏ Ңɾܦ ͱن
© 2022 IBM Corporation 7 IUUQTXXXEBUBKNBHPKQGDEZPIPUZQIPPOSPVUF@NBQCTUW IUNM ʲʳୈ߸ۙ ΑΓҾ༻ ཧۭؒੳ ཧۭؒใ -
© 2022 IBM Corporation 8 ཧۭؒੳ ཧۭؒใΛͬͯߦ͏ੳ σʔλΛࢹ֮Խ ਤʹϚοϐϯά ͢Δ
͜ͱͰΑΓਂ͍ಎ͕ಘΒΕΔ l ͋ΔҬͷച্͕ߴ͍ళฮΛਤ্ ʹϚοϐϯάͯ͠ߴച্ళฮͷཧ తಛΛಎ l աڈͷ෩ͷܦ࿏ใΛਤʹϚο ϐϯάͯ͠ɺܦ࿏ύλʔϯΛಎ ཧۭؒੳ ཧۭؒੳ -
%Cͷཧۭؒੳػೳ %C 4QBUJBM"OBMZUJDT © 2022 IBM Corporation 10 ରԠ •
%C-68 48൛ɾίϯςφ൛ • %C8BSFIPVTF • %C8BSFIPVTFPO$MPVE 4QBUJBM"OBMZUJDTίϯϙʔωϯτͷ༻ՄೳԽ͕ඞཁͳ ߹͕͋Γ·͢ɻ • ۭؒσʔλɾλΠϓͰཧۭؒσʔλ Λอ • ࠷େ .#·Ͱอ࣋Ͱ͖Δಛघͳσʔ λɾλΠϓ #-0# • ۭؒॲཧؔΛ༻ͯ͠ɺςʔϒϧʹ อ͞Ε͍ͯΔσʔλΛੳ https://www.ibm.com/docs/ja/db2/11.5? topic=data-db2-spatial-analytics -
%Cͷཧۭؒੳػೳ ۭؒσʔλɾλΠϓ © 2022 IBM Corporation 11 -JOF ઢ 1PJOU
1PMZHPO ໘ Y Z Y Z Y Z Y Z Y Z Y Z Y Z Y Z %C %BUB 5ZQF 45@1PJOU [email protected] 45@-JOF4USJOH [email protected] 45@1PMZHPO [email protected] 45@(FPNFUSZ ༻్ ਤ্ͷ Ңɾܦ ళͳͲϐϯϙΠϯτͷҢɾܰ ઢঢ়ͷܗ ಓ࿏ɺਫ࿏ɺͳͲ ڥքઢͳͲଟ֯ܗͰғ·ΕͨྖҬ બڍ۠ɺࢢொଜڥքͳͲ ܗঢ় IUUQTXXXJCNDPNEPDTKBEC UPQJD DPODFQUTTQBUJBMEBUBUZQFT -
%Cͷཧۭؒੳػೳ ۭؒσʔλλΠϓͷม © 2022 IBM Corporation 12 42-Ͱͷมྫ Ңɾܦͷ͕͋Δ߹ɺۭؒॲཧؔ45@1PJOUؔΛͬͯม INSERT
INTO SAMPLE_POINTS1(id, geom) VALUES(100, ST_Point(135, 35)); γΣΠϓɾϑΝΠϧ͔ΒͷۭؒσʔλͷΠϯϙʔτྫ γΣΠϓϑΝΠϧ͕͋Δ߹ɺECTFJNQPSU@TIBQFίϚϯυΛͬͯม db2se import_shape DOJOWOC -userId bluadmin -pw xxxx -sa 1 -fileName /N03-20210101_13_GML/tokyo.shp -createTableFlag 1 -srsName GCS_JGD_2011 -tableSchema DOJOUSER_99 -tableName CITY_SHAPE -spatialColumn geo -idColumn OBJECTID -idColumnIsIdentity 1 -commitScope 200 -client 1 ˢ࣮ࡍʹࠓճͷϋϯζΦϯ༻ʹࠃަ௨লͷ౦ژͷߦ۠ҬσʔλΛΠϯϙʔτͨ͠ίϚϯυͰ͢ ͨͩ͠จࣈίʔυͷ 65'มΛผ్ͨ͠ޙʹ࣮ߦ͍ͯ͠·͢ -
%Cͷཧۭؒੳػೳ ۭؒॲཧؔ © 2022 IBM Corporation 13 ۭؒॲཧؔΛ༻ͯ͠ɺܗঢ়ؒͷڑ͋Δܗঢ়͕ผͷܗঢ়ʹؚ·ΕΔ͔Ͳ͏͔ͷ ผͳͲ͕ՄೳͰ͢ɻ ͜ΕΛ42-ͷ8)&3&۟ͱͯ͠༻͢Δ͜ͱ͕Ͱ͖·͢ɻ
ശ࡚ࣄۀॴ ͔ΒڑLNҎͷ-0$"5*0/ 45@%JTUBODFؔ ˣ-0$"5*0/45@1PJOUଐੑͷྻ IUUQTXXXJCNDPNEPDTKBEC UPQJD BOBMZUJDTTQBUJBMGVODUJPOT WHERE ST_Distance( ST_Point (139.7869482 35.6783300), LOCATION, 'KILOMETER') <= 1 தԝ۠ͷ-0$"5*0/ 45@8JUIJOؔ -0$"5*0/45@1PJOUଐੑͷྻɺதԝ۠ͷܗঢ়4)"1&ʹ͋Δͱ͢Δ WHERE ST_Within (LOCATION, SHAPE) -
%Cͷཧۭؒੳػೳ %CͰཧۭؒੳͷ͍͍͜ͱ © 2022 IBM Corporation 14 • ͍͍ͭͬͯΔςʔϒϧͷσʔλͱҐஔใΛΈ߹Θͤͨੳ͕ %CʹσʔλΛஔ͍ͨ··Ͱ͖Δ
• ۭؒੳͷϫʔΫϩʔυΛ%Cαʔόʔ্Ͱ࣮ߦͰ͖Δ ͏ͻͱ͍͖ͳ෦ɿ • ࢹ֮Խπʔϧ ਤʹϚοϐϯά %Cʹͳ͍ • ผ్ࢹ֮Խπʔϧ͕ඞཁ ˠ1ZUIPOKBWBTDSJQUͷϥΠϒϥϦɺ%#FBWFSͳͲར༻Մೳ -
ϋϯζΦϯ ࣮ߦڥ © 2022 IBM Corporation 16 1$ Db2 python
Ibm_db ライブラリ %C 8BSFIPVTF PO$MPVE DBeaver Community Edition JDBC Web Browser Notebook on Watson Studio オプション (導⼊してある⽅のみ) -
ϋϯζΦϯ ༻ςʔϒϧ © 2022 IBM Corporation 17 ౦ژ*#. ࣄۀॴσʔλ (&0@10*/5ʹ
Ңܦใ શࠃγΣΞαΠ Ϋϧϙʔτใ ެڞަ௨Φʔϓϯσʔ ληϯλʔΑΓऔಘ (&0@10*/5ʹҢ ܦใ γΣΞαΠΫ ϧϙʔτ݄ผ ച্ใ Սۭͷച্ใ Λ࡞ ౦ژ ࢢ۠ொଜڥք ࠃަ௨লͷ౦ ژͷߦ۠Ҭ σʔλ ͔Β࡞ (&0ʹڥքϙϦ ΰϯใ 実際に作ります ↓ 実際に作ります ↓ あらかじめ作成済 ↓ あらかじめ作成済 ↓ -
ϋϯζΦϯσʔλऔಘݩ © 2022 IBM Corporation 18 ެڞަ௨ΦʔϓϯσʔληϯλʔΑΓҎԼΛऔಘ • 0QFO4USFFUʢϋϩʔαΠΫϦϯάʣ όΠΫγΣΞؔ࿈ใ
TUBUJPO@JOGPSNBUJPO • υίϞɾόΠΫγΣΞ όΠΫγΣΞؔ࿈ใ TUBUJPO@JOGPSNBUJPO ࠃަ௨লࠃใμϯϩʔυαʔϏεΑΓҎԼΛऔಘ • ߦ۠Ҭσʔλ ౦ژ +1(*4ʢ(.-ʣ४ڌٴͼ4)"1&ܗࣜσʔλ – ྩ -
*#.$MPVEʹϩάΠϯ • *#.$MPVEϩάΠϯ *#.$MPVE"DDPVOU ొͨ͠ϝʔϧΞυϨε ΛೖΕͯϩάΠϯ͍ͯͩ͘͠͞ɻ IUUQTDMPVEJCNDPNMPHJOʹΞΫηεͯ͠ϩάΠϯ͠·͢ɻ IBM Data &
AI / © 2022 IBM Corporation 20 -
ϦιʔεϦετΛදࣔ *#.$MPVEϩΰͷࠨԣͷϋϯόʔΨɾϝχϡʔΛΫϦοΫ ϦιʔεɾϦετΛબ 8BUTPO4UVEJPͷىಈ IBM Data & AI / ©
2022 IBM Corporation 21 -
αʔϏε͓ΑͼιϑτΣΞͷ8BUTPO4UVEJPYYΛબ YYਓʹΑͬͯҧ ͍·͢ 8BUTPO4UVEJPͷىಈ IBM Data & AI /
© 2022 IBM Corporation 22 -
ࣄલ४උͰ࡞ͨ͠ϓϩδΣΫτ໊ΛΫϦοΫ͠·͢ɻ ͜͜Ͱʮ %C4QBUJBMʯͰ࡞ͨ͠ͱͯ͠ਐΊ·͢ɻ ϓϩδΣΫτΛ։͘ IBM Data & AI / ©
2022 IBM Corporation 26 %C4QBUJBM -
ʮ͜ͷϓϩδΣΫτͷσʔλʯ͕දࣔ͞Εͨ߹ ʮ9ʯΛΫϦοΫͯ͠ด͓͖ͯ͡·͢ɻ ϓϩδΣΫτΛ։͘ IBM Data & AI / © 2022
IBM Corporation 27 -
ηογϣϯ༻ͷ൪ͷ/PUFCPPLΛ։͘ ʮࢿ࢈ʯΛΫϦοΫ͠·͢ɻ <৽نࢿ࢈ >ΛΫϦοΫ͠·͢ IBM Data & AI / ©
2022 IBM Corporation 28 %C4QBUJBM %C4QBUJBM -
ηογϣϯ༻ͷ ൪ͷ/PUFCPPLΛ։͘ πʔϧɾλΠϓ͔ΒʮίʔυɾΤσΟλʔʯΛΫϦοΫ͠ɺදࣔ͞Εͨ ʮ+VQZUFS ϊʔτϒοΫɾΤσΟλʔʯΛΫϦοΫ͠·͢ɻ IBM Data & AI /
© 2022 IBM Corporation 29 -
ηογϣϯ༻ͷ ൪ͷ /PUFCPPLΛ։͘ ʮ63-͔ΒʯΛΫϦοΫ ໊લʹ Db2_Spatial_Analysis ͱೖྗ /PUFCPPL63-ʹҎԼΛίϐʔϖʔετͰೖྗ
https://github.com/kyokonishito/Db2_Spatial_Analysis/blob/main/notebooks/Db2_Spatial_Analysis.ipynb ʮ࡞ʯΛΫϦοΫ ηογϣϯͰ൪ʹ༻͢ ΔOPUFCPPL͕ಡΈࠐ·Ε·͢ɻ IBM Data & AI / © 2022 IBM Corporation 30 Db2_Spatial_Analysis -
%CଓใΛηοτ͢Δ IBM Data & AI / © 2022 IBM Corporation
32 9999ͱΛࣗͷଓઌ%#ͷใʹηοτ͢Δ -
൪ͷ/PUFCPPLΛอଘ͢Δ IBM Data & AI / © 2022 IBM Corporation
33 %Cଓใͷηοτ͕ྃͨ͠Β ࠨ্ͷ'JMFͷԼͷ࢛͍֯ΞΠίϯ ϑϩοϐʔ ΛΫϦοΫͯ͠࡞ۀΛอଘ ্ͷϓϩδΣΫτ໊ͷϦϯΫʮ %C4QBUJBMʯΛΫϦοΫͯ͠ɺ ϓϩδΣΫτϏϡʔʹΓ·͢ɻ -
ηογϣϯ༻ͷ൪ͷ/PUFCPPLΛ։͘ ʮࢿ࢈ʯΛΫϦοΫ͠·͢ɻ <৽نࢿ࢈ >ΛΫϦοΫ͠·͢ IBM Data & AI / ©
2022 IBM Corporation 34 %C4QBUJBM %C4QBUJBM -
ηογϣϯ༻ͷ൪ͷ/PUFCPPLΛ։͘ ʮ63-͔ΒʯΛΫϦοΫ ໊લʹ Data_Setup ͱೖྗ /PUFCPPL63-ʹҎԼΛίϐʔϖʔετͰೖྗ https://github.com/kyokonishito/Db2_Spatial_Analysis/blob/main/notebooks/Data_Setup.ipynb ʮ࡞ʯΛΫϦοΫ
ηογϣϯͰ࠷ॳʹ༻͢Δ ͷOPUFCPPL͕ಡΈࠐ·Ε·͢ɻ IBM Data & AI / © 2022 IBM Corporation 36 Data_Setup -
%CଓใΛηοτ͢Δ IBM Data & AI / © 2022 IBM Corporation
38 9999ͱΛࣗͷଓઌ%#ͷใʹηοτ͢Δ ͱಉ͡ใͰ͢ -
ηογϣϯ༻ͷ൪ͷ/PUFCPPLΛ։͘ ʮࢿ࢈ʯΛΫϦοΫ͠·͢ɻ Db2_Spatial_AnalysisͷӈଆͷϝχϡʔΛΫϦοΫ͠ɺฤूΛΫϦοΫ IBM Data & AI / © 2022
IBM Corporation 42 %C4QBUJBM -
·ͱΊɾ2" ·ͱΊ © 2022 IBM Corporation 45 • %Cͷ42-ͰۭؒॲཧؔͱΈ߹Θ͍ͤͯΖ͍Ζͳੳ͕Մೳ •
42-ͷ݁ՌΛผπʔϧͰਤʹϚοϐϯάՄೳ