$30 off During Our Annual Pro Sale. View Details »

Db2でシェアサイクルポートの地理情報分析をやってみよう! / 20220928-Db2-Spatial

Db2でシェアサイクルポートの地理情報分析をやってみよう! / 20220928-Db2-Spatial

2022年9/28(水) オンライン開催
Db2でシェアサイクルポートの地理情報分析をやってみよう!
https://ibm-developer.connpass.com/event/259583/
の資料です。

Kyoko Nishito

September 28, 2022
Tweet

More Decks by Kyoko Nishito

Other Decks in Programming

Transcript

  1. *#.%BUBCBTF%PKPγʔζϯ %CͰγΣΞαΠΫϧϙʔτͷ ஍ཧ৘ใ෼ੳΛ΍ͬͯΈΑ͏ ੢ށ ژࢠ ,ZPLP/JTIJUP ೔ຊΞΠɾϏʔɾΤϜגࣜձࣾ ςΫϊϩδʔࣄۀ෦ %BUB"*5FDIOJDBM4BMFT

  2. 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!*#. ։࠵ ୈ ճ ೔ຊ౷ܭֶձ౷ܭڭҭ৆ ड৆
  3. ຊ೔ͷϋϯζΦϯͷࣄલ४උ IUUQTJCNCPYDPNWEPKPQSFQ © 2022 IBM Corporation 3

  4. ຊ೔ͷ໨తͱΰʔϧ ໨త %Cͷ஍ཧۭؒ෼ੳػೳΛ࣮ࡍʹମݧ ΰʔϧ %Cͷ஍ཧۭؒ෼ੳػೳ͸Կ͕Ͱ͖Δ͔Θ͔Δʂ © 2022 IBM Corporation 4

  5. ຊ೔ͷ"HFOEB ෼  ஍ཧۭؒ෼ੳʁ  %Cͷ஍ཧۭؒ෼ੳػೳ  ϋϯζΦϯ ·ͱΊɾ2" ©

    2022 IBM Corporation 5
  6. ຊ೔ͷ"HFOEB ஍ཧۭؒ෼ੳʁ %Cͷ஍ཧۭؒ෼ੳػೳ ϋϯζΦϯ ·ͱΊɾ2" © 2022 IBM Corporation 6

  7. ஍ཧۭؒ৘ใ ஍ཧɾۭؒʹؔ܎͚ͮΒΕͨ৘ใ • ॅॴͷೖͬͨࣄۀॴҰཡ • ࣸਅͱࡱӨ৔ॴ • ୆෩ͷݱࡏ஍ Ң౓ɾܦ౓ ͱن໛

    © 2022 IBM Corporation 7 IUUQTXXXEBUBKNBHPKQGDEZPIPUZQIPPOSPVUF@NBQCTUW IUNM ʲʳୈ߸઀ۙ ΑΓҾ༻ ஍ཧۭؒ෼ੳ  ஍ཧۭؒ৘ใ
  8. © 2022 IBM Corporation 8 ஍ཧۭؒ෼ੳ ஍ཧۭؒ৘ใΛ࢖ͬͯߦ͏෼ੳ σʔλΛࢹ֮Խ ஍ਤʹϚοϐϯά ͢Δ

    ͜ͱͰΑΓਂ͍ಎ࡯͕ಘΒΕΔ l ͋Δ஍Ҭͷച্͕ߴ͍ళฮΛ஍ਤ্ ʹϚοϐϯάͯ͠ߴച্ళฮͷ஍ཧ తಛ௃Λಎ࡯ l աڈͷ୆෩ͷܦ࿏৘ใΛ஍ਤʹϚο ϐϯάͯ͠ɺܦ࿏ύλʔϯΛಎ࡯ ஍ཧۭؒ෼ੳ  ஍ཧۭؒ෼ੳ
  9. ຊ೔ͷ"HFOEB ஍ཧۭؒ෼ੳʁ %Cͷ஍ཧۭؒ෼ੳػೳ ϋϯζΦϯ ·ͱΊɾ2" © 2022 IBM Corporation 9

  10. %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
  11. %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 45@.VMUJ1PJOU 45@-JOF4USJOH 45@.VMUJ-JOF4USJOH 45@1PMZHPO 45@.VMUJ1PMZHPO 45@(FPNFUSZ ༻్ ஍ਤ্ͷ஍఺ Ң౓ɾܦ౓ ళͳͲϐϯϙΠϯτͷҢ౓ɾܰ౓ ઢঢ়ͷ஍ܗ ಓ࿏ɺਫ࿏ɺ഑؅ͳͲ ڥքઢͳͲଟ֯ܗͰғ·ΕͨྖҬ બڍ۠ɺࢢொଜڥքͳͲ ܗঢ় IUUQTXXXJCNDPNEPDTKBEC UPQJD DPODFQUTTQBUJBMEBUBUZQFT
  12. %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'ม׵Λผ్ͨ͠ޙʹ࣮ߦ͍ͯ͠·͢
  13. %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)
  14. %Cͷ஍ཧۭؒ෼ੳػೳ %CͰ஍ཧۭؒ෼ੳͷ͍͍͜ͱ © 2022 IBM Corporation 14 • ͍ͭ΋࢖͍ͬͯΔςʔϒϧͷσʔλͱҐஔ৘ใΛ૊Έ߹Θͤͨ෼ੳ͕ %CʹσʔλΛஔ͍ͨ··Ͱ͖Δ

    • ۭؒ෼ੳͷϫʔΫϩʔυΛ%Cαʔόʔ্Ͱ࣮ߦͰ͖Δ ΋͏ͻͱ͍͖ͳ෦෼ɿ • ࢹ֮Խπʔϧ ஍ਤʹϚοϐϯά ͸%Cʹ͸ͳ͍ • ผ్ࢹ֮Խπʔϧ͕ඞཁ ˠ1ZUIPO΍KBWBTDSJQUͷϥΠϒϥϦɺ%#FBWFSͳͲར༻Մೳ
  15. ຊ೔ͷ"HFOEB ஍ཧۭؒ෼ੳʁ %Cͷ஍ཧۭؒ෼ੳػೳ ϋϯζΦϯ ·ͱΊɾ2" © 2022 IBM Corporation 15

  16. ϋϯζΦϯ ࣮ߦ؀ڥ © 2022 IBM Corporation 16 1$ Db2 python

    Ibm_db ライブラリ %C 8BSFIPVTF PO$MPVE DBeaver Community Edition JDBC Web Browser Notebook on Watson Studio オプション (導⼊してある⽅のみ)
  17. ϋϯζΦϯ ࢖༻ςʔϒϧ © 2022 IBM Corporation 17 ౦ژ౎಺*#. ࣄۀॴσʔλ (&0@10*/5ʹ

    Ң౓ܦ౓৘ใ શࠃγΣΞαΠ Ϋϧϙʔτ৘ใ ެڞަ௨Φʔϓϯσʔ ληϯλʔΑΓऔಘ  (&0@10*/5ʹҢ౓ ܦ౓৘ใ γΣΞαΠΫ ϧϙʔτ݄ผ ച্৘ใ Սۭͷച্৘ใ Λ࡞੒ ౦ژ౎ ࢢ۠ொଜڥք ࠃ౔ަ௨লͷ౦ ژ౎ͷߦ੓۠Ҭ σʔλ ͔Β࡞ ੒ (&0ʹڥքϙϦ ΰϯ৘ใ 実際に作ります ↓ 実際に作ります ↓ あらかじめ作成済 ↓ あらかじめ作成済 ↓
  18. ϋϯζΦϯσʔλऔಘݩ © 2022 IBM Corporation 18 ެڞަ௨ΦʔϓϯσʔληϯλʔΑΓҎԼΛऔಘ • 0QFO4USFFUʢϋϩʔαΠΫϦϯάʣ όΠΫγΣΞؔ࿈৘ใ

    TUBUJPO@JOGPSNBUJPO • υίϞɾόΠΫγΣΞ όΠΫγΣΞؔ࿈৘ใ TUBUJPO@JOGPSNBUJPO ࠃ౔ަ௨লࠃ౔਺஋৘ใμ΢ϯϩʔυαʔϏεΑΓҎԼΛऔಘ • ߦ੓۠Ҭσʔλ ౦ژ౎ +1(*4ʢ(.-ʣ४ڌٴͼ4)"1&ܗࣜσʔλ – ྩ࿨೥
  19. ϋϯζΦϯ 45"35 © 2022 IBM Corporation 19 ຊ೔ͷϋϯζΦϯͷࣄલ४උ IUUQTJCNCPYDPNWEPKPQSFQ

  20. *#.$MPVEʹϩάΠϯ • *#.$MPVEϩάΠϯ *#.$MPVE"DDPVOU ొ࿥ͨ͠ϝʔϧΞυϨε ΛೖΕͯϩάΠϯ͍ͯͩ͘͠͞ɻ IUUQTDMPVEJCNDPNMPHJOʹΞΫηεͯ͠ϩάΠϯ͠·͢ɻ IBM Data &

    AI / © 2022 IBM Corporation 20
  21. ϦιʔεϦετΛදࣔ *#.$MPVEϩΰͷࠨԣͷϋϯόʔΨɾϝχϡʔΛΫϦοΫ ϦιʔεɾϦετΛબ୒ 8BUTPO4UVEJPͷىಈ IBM Data & AI / ©

    2022 IBM Corporation 21
  22.  αʔϏε͓Αͼιϑτ΢ΣΞͷ8BUTPO4UVEJPYYΛબ୒ YY͸ਓʹΑͬͯҧ ͍·͢ 8BUTPO4UVEJPͷىಈ IBM Data & AI /

    © 2022 IBM Corporation 22
  23. -BVODIJO*#.$MPVE1BLGPS%BUBΛબ୒ͯ͠ىಈ ৽͍͠ϒϥ΢β͕։͖·͢ 8BUTPO4UVEJPͷىಈ IBM Data & AI / © 2022

    IBM Corporation 23
  24. 24 ʮ৘ใΛೖྗͯ͠ଓߦ͍ͯͩ͘͠͞ʯ͕දࣔ͞Εͨ৔߹͸ɺద౰ͳి࿩ ൪߸ΛೖΕͯɺʮଓߦʯΛΫϦοΫ͍ͯͩ͘͠͞ɻ ʮ$MPVE1BLGPS%BUB΁Α͏ͦ͜ɻʯ͋Δ͍͸ʮ#VJMEBOENBOBHF.- NPEFMTʯͷ΢Πϯυ΢ͳͲ͕දࣔ͞Εͨ৔߹͸9ΛΫϦοΫͯ͠ফ͠·͢ɻ 8BUTPO4UVEJPͷىಈ

  25. 1. 左上の「ナビゲーションメニュー」を開き、 「プロジェクト」の下の「すべてのプロジェクトの表⽰」 をクリックしてください。 25 ϓϩδΣΫτͷΦʔϓϯ

  26. ࣄલ४උͰ࡞੒ͨ͠ϓϩδΣΫτ໊ΛΫϦοΫ͠·͢ɻ ͜͜Ͱ͸ʮ %C4QBUJBMʯͰ࡞੒ͨ͠ͱͯ͠ਐΊ·͢ɻ ϓϩδΣΫτΛ։͘ IBM Data & AI / ©

    2022 IBM Corporation 26 %C4QBUJBM
  27. ʮ͜ͷϓϩδΣΫτͷσʔλʯ͕දࣔ͞Εͨ৔߹͸ ʮ9ʯΛΫϦοΫͯ͠ด͓͖ͯ͡·͢ɻ ϓϩδΣΫτΛ։͘ IBM Data & AI / © 2022

    IBM Corporation 27
  28. ηογϣϯ༻ͷ൪໨ͷ/PUFCPPLΛ։͘ ʮࢿ࢈ʯΛΫϦοΫ͠·͢ɻ <৽نࢿ࢈ >ΛΫϦοΫ͠·͢ IBM Data & AI / ©

    2022 IBM Corporation 28 %C4QBUJBM %C4QBUJBM
  29. ηογϣϯ༻ͷ ൪໨ͷ/PUFCPPLΛ։͘ πʔϧɾλΠϓ͔ΒʮίʔυɾΤσΟλʔʯΛΫϦοΫ͠ɺදࣔ͞Εͨ ʮ+VQZUFS ϊʔτϒοΫɾΤσΟλʔʯΛΫϦοΫ͠·͢ɻ IBM Data & AI /

    © 2022 IBM Corporation 29
  30. ηογϣϯ༻ͷ ൪໨ͷ /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
  31. ηογϣϯ༻ͷ/PUFCPPLΛ։͘ ΋͜͠ͷΑ͏ͳը໘͕දࣔ͞Εͨํ͸<9>ΛΫϦοΫͯ͠ফ͍ͯͩ͘͠͞ IBM Data & AI / © 2022 IBM

    Corporation 31
  32. %C઀ଓ৘ใΛηοτ͢Δ IBM Data & AI / © 2022 IBM Corporation

    32 9999ͱΛࣗ෼ͷ઀ଓઌ%#ͷ৘ใʹηοτ͢Δ
  33. ൪໨ͷ/PUFCPPLΛอଘ͢Δ IBM Data & AI / © 2022 IBM Corporation

    33 %C઀ଓ৘ใͷηοτ͕׬ྃͨ͠Β ࠨ্ͷ'JMFͷԼͷ࢛͍֯ΞΠίϯ ϑϩοϐʔ ΛΫϦοΫͯ͠࡞ۀΛอଘ ্ͷϓϩδΣΫτ໊ͷϦϯΫʮ %C4QBUJBMʯΛΫϦοΫͯ͠ɺ ϓϩδΣΫτϏϡʔʹ໭Γ·͢ɻ  
  34. ηογϣϯ༻ͷ൪໨ͷ/PUFCPPLΛ։͘ ʮࢿ࢈ʯΛΫϦοΫ͠·͢ɻ <৽نࢿ࢈ >ΛΫϦοΫ͠·͢ IBM Data & AI / ©

    2022 IBM Corporation 34 %C4QBUJBM %C4QBUJBM
  35. ηογϣϯ༻ͷ൪໨ͷ/PUFCPPLΛ։͘ πʔϧɾλΠϓ͔ΒʮίʔυɾΤσΟλʔʯΛΫϦοΫ͠ɺදࣔ͞Εͨ ʮ+VQZUFS ϊʔτϒοΫɾΤσΟλʔʯΛΫϦοΫ͠·͢ɻ IBM Data & AI / ©

    2022 IBM Corporation 35
  36. ηογϣϯ༻ͷ൪໨ͷ/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
  37. ্෦ϝχϡʔͷộΛΫϦοΫ͠ɺʮϓϩδΣΫτɾτʔΫϯͷૠೖʯΛ ΫϦοΫ Ұ൪্ʹϓϩδΣΫττʔΫϯΛॲཧ͢Δ ৽͍͠ηϧ͕࡞੒͞Ε·͢ɻ ௥Ճ͞ΕͨηϧΛΫϦοΫͯ͠ <$USM> <&OUFS>Ͱ࣮ߦ͓͖ͯ͠·͢ɻ ηογϣϯ༻ͷ/PUFCPPLΛ։͘ IBM Data

    & AI / © 2022 IBM Corporation 37
  38. %C઀ଓ৘ใΛηοτ͢Δ IBM Data & AI / © 2022 IBM Corporation

    38 9999ͱΛࣗ෼ͷ઀ଓઌ%#ͷ৘ใʹηοτ͢Δ ͱಉ͡৘ใͰ͢
  39. Ҏޙ͸/PUFCPPLͰઆ໌͠·͢ %C@4FUVQͷ/PUFCPPLͷॲཧ͕׬ྃޙ ࣍ϖʔδʹਐΜͰ͍ͩ͘͞ IBM Data & AI / © 2022

    IBM Corporation 39
  40. ൪໨ͷ/PUFCPPLΛอଘ͢Δ 40 %C@4FUVQͷ࣮ߦ͕׬ྃͨ͠Β ӈ্ͷ'JMFͷԼͷ࢛͍֯ΞΠίϯ ϑϩοϐʔ ΛΫϦοΫͯ͠࡞ۀΛอଘ ্ͷϓϩδΣΫτ໊ͷϦϯΫʮ %C4QBUJBMʯΛΫϦοΫͯ͠ɺ ϓϩδΣΫτϏϡʔʹ໭Γ·͢ɻ 

    
  41. ิ଍൪໨ͷ/PUFCPPLΛ࣮ߦதʹ൪໨Λผλ ϒͰ։͘ 41 ্ͷϓϩδΣΫτ໊ͷϦϯΫʮ %C4QBUJBMʯΛӈΫϦοΫ ʮ ϦϯΫΛ৽͍͠λϒͰ։͘ ʯΛΫϦοΫͯ͠ɺϓϩδΣΫτϏϡʔΛ දࣔ͠·͢ɻ ӈΫϦοΫ

    
  42. ηογϣϯ༻ͷ൪໨ͷ/PUFCPPLΛ։͘ ʮࢿ࢈ʯΛΫϦοΫ͠·͢ɻ Db2_Spatial_AnalysisͷӈଆͷϝχϡʔΛΫϦοΫ͠ɺฤूΛΫϦοΫ IBM Data & AI / © 2022

    IBM Corporation 42 %C4QBUJBM
  43. Ҏޙ͸/PUFCPPLͰઆ໌͠·͢ IBM Data & AI / © 2022 IBM Corporation

    43
  44. ຊ೔ͷ"HFOEB ஍ཧۭؒ෼ੳʁ %Cͷ஍ཧۭؒ෼ੳػೳ %C 8BSFIPVTF PO$MPVEͰ஍ཧۭؒ෼ੳσϞ ·ͱΊɾ2" © 2022 IBM

    Corporation 44
  45. ·ͱΊɾ2" ·ͱΊ © 2022 IBM Corporation 45 • %Cͷ42-Ͱۭؒॲཧؔ਺ͱ૊Έ߹Θ͍ͤͯΖ͍Ζͳ෼ੳ͕Մೳ •

    42-ͷ݁ՌΛผπʔϧͰ஍ਤʹϚοϐϯάՄೳ
  46. 2" © 2022 IBM Corporation 46

  47. 5IBOLZPV ͋Γ͕ͱ͏͍͟͝·ͨ͠ © 2022 IBM Corporation 47

  48. None