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

by Kyoko Nishito

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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!*#. ։࠵ ୈ ճ ೔ຊ౷ܭֶձ౷ܭڭҭ৆ ड৆

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

Slide 6

Slide 6 text

Slide 7

Slide 7 text

Slide 8

Slide 8 text

Slide 9

Slide 9 text

Slide 10

Slide 10 text

%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

Slide 11

Slide 11 text

%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

Slide 12

Slide 12 text

%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'ม׵Λผ్ͨ͠ޙʹ࣮ߦ͍ͯ͠·͢

Slide 13

Slide 13 text

%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)

Slide 14

Slide 14 text

%Cͷ஍ཧۭؒ෼ੳػೳ %CͰ஍ཧۭؒ෼ੳͷ͍͍͜ͱ © 2022 IBM Corporation 14 • ͍ͭ΋࢖͍ͬͯΔςʔϒϧͷσʔλͱҐஔ৘ใΛ૊Έ߹Θͤͨ෼ੳ͕ %CʹσʔλΛஔ͍ͨ··Ͱ͖Δ • ۭؒ෼ੳͷϫʔΫϩʔυΛ%Cαʔόʔ্Ͱ࣮ߦͰ͖Δ ΋͏ͻͱ͍͖ͳ෦෼ɿ • ࢹ֮Խπʔϧ ஍ਤʹϚοϐϯά ͸%Cʹ͸ͳ͍ • ผ్ࢹ֮Խπʔϧ͕ඞཁ ˠ1ZUIPO΍KBWBTDSJQUͷϥΠϒϥϦɺ%#FBWFSͳͲར༻Մೳ

Slide 15

Slide 15 text

Slide 16

Slide 16 text

Slide 17

Slide 17 text

ϋϯζΦϯ ࢖༻ςʔϒϧ © 2022 IBM Corporation 17 ౦ژ౎಺*#. ࣄۀॴσʔλ (&0@10*/5ʹ Ң౓ܦ౓৘ใ શࠃγΣΞαΠ Ϋϧϙʔτ৘ใ ެڞަ௨Φʔϓϯσʔ ληϯλʔΑΓऔಘ (&0@10*/5ʹҢ౓ ܦ౓৘ใ γΣΞαΠΫ ϧϙʔτ݄ผ ച্৘ใ Սۭͷച্৘ใ Λ࡞੒ ౦ژ౎ ࢢ۠ொଜڥք ࠃ౔ަ௨লͷ౦ ژ౎ͷߦ੓۠Ҭ σʔλ͔Β࡞ ੒ (&0ʹڥքϙϦ ΰϯ৘ใ 実際に作ります ↓ 実際に作ります ↓ あらかじめ作成済 ↓ あらかじめ作成済 ↓

Slide 18

Slide 18 text

ϋϯζΦϯσʔλऔಘݩ © 2022 IBM Corporation 18 ެڞަ௨ΦʔϓϯσʔληϯλʔΑΓҎԼΛऔಘ • 0QFO4USFFUʢϋϩʔαΠΫϦϯάʣ όΠΫγΣΞؔ࿈৘ใ TUBUJPO@JOGPSNBUJPO • υίϞɾόΠΫγΣΞ όΠΫγΣΞؔ࿈৘ใ TUBUJPO@JOGPSNBUJPO ࠃ౔ަ௨লࠃ౔਺஋৘ใμ΢ϯϩʔυαʔϏεΑΓҎԼΛऔಘ • ߦ੓۠Ҭσʔλ ౦ژ౎ +1(*4ʢ(.-ʣ४ڌٴͼ4)"1&ܗࣜσʔλ – ྩ࿨೥

Slide 19

Slide 19 text

Slide 20

Slide 20 text

Slide 21

Slide 21 text

Slide 22

Slide 22 text

Slide 23

Slide 23 text

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

Slide 27

Slide 27 text

Slide 28

Slide 28 text

Slide 29

Slide 29 text

Slide 30

Slide 30 text

ηογϣϯ༻ͷ ൪໨ͷ /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

Slide 31

Slide 31 text

Slide 32

Slide 32 text

Slide 33

Slide 33 text

Slide 34

Slide 34 text

Slide 35

Slide 35 text

Slide 36

Slide 36 text

ηογϣϯ༻ͷ൪໨ͷ/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

Slide 37

Slide 37 text

Slide 38

Slide 38 text

Slide 39

Slide 39 text

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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