|
-- データベースの作成 |
|
CREATE DATABASE IF NOT EXISTS urayasu; |
|
USE urayasu; |
|
|
|
-- locations テーブルの作成 |
|
CREATE TABLE IF NOT EXISTS locations ( |
|
id INT AUTO_INCREMENT PRIMARY KEY, |
|
name VARCHAR(255) NOT NULL, |
|
latitude VARCHAR(255) NOT NULL, |
|
longitude VARCHAR(255) NOT NULL |
|
); |
|
|
|
-- distances テーブルの作成 |
|
CREATE TABLE IF NOT EXISTS distances ( |
|
id INT AUTO_INCREMENT PRIMARY KEY, |
|
from_location INT NOT NULL, |
|
to_location INT NOT NULL, |
|
distance FLOAT, |
|
FOREIGN KEY (from_location) REFERENCES locations(id), |
|
FOREIGN KEY (to_location) REFERENCES locations(id) |
|
); |
|
|
|
-- urayasu_bunkazai.csvのデータをlocationsテーブルに挿入 |
|
INSERT INTO locations (name, latitude, longitude) |
|
VALUES |
|
('宝城院', '35.66167', '139.8911'), |
|
('浦安市郷土博物館', '35.65536', '139.90122'), |
|
('旧大塚家住宅', '35.66187', '139.8927'), |
|
('花蔵院', '35.66078', '139.89685'), |
|
('豊受神社', '35.66067', '139.89758'), |
|
('善福寺', '35.66989', '139.89277'), |
|
('大蓮寺', '35.66203', '139.89049'), |
|
('庚申塔', '35.66408', '139.8938'), |
|
('旧宇田川家住宅', '35.66196', '139.89228'), |
|
('清瀧神社', '35.66238', '139.89122'), |
|
('稲荷神社', '35.67241', '139.89154'), |
|
('浦安駅','35.66604888473298','139.89326652577006'); |
|
|
|
|
|
-- distancesテーブルにデータをインポート |
|
INSERT INTO distances (from_location, to_location, distance) |
|
VALUES |
|
(1, 1, 0.0), (1, 2, 1647.0), (1, 3, 174.0), (1, 4, 997.0), (1, 5, 1092.0), (1, 6, 1010.0), (1, 7, 69.0), (1, 8, 713.0), (1, 9, 92.0), (1, 10, 133.0), (1, 11, 1462.0), (1, 12, 919.0), |
|
(2, 1, 1819.0), (2, 2, 0.0), (2, 3, 1928.0), (2, 4, 1100.0), (2, 5, 1164.0), (2, 6, 2534.0), (2, 7, 1888.0), (2, 8, 1809.0), (2, 9, 1846.0), (2, 10, 1887.0), (2, 11, 2932.0), (2, 12, 2443.0), |
|
(3, 1, 872.0), (3, 2, 1643.0), (3, 3, 0.0), (3, 4, 639.0), (3, 5, 710.0), (3, 6, 1683.0), (3, 7, 1118.0), (3, 8, 823.0), (3, 9, 900.0), (3, 10, 941.0), (3, 11, 2248.0), (3, 12, 1592.0), |
|
(4, 1, 997.0), (4, 2, 1100.0), (4, 3, 1107.0), (4, 4, 0.0), (4, 5, 65.0), (4, 6, 1466.0), (4, 7, 1243.0), (4, 8, 606.0), (4, 9, 1025.0), (4, 10, 632.0), (4, 11, 1864.0), (4, 12, 1375.0), |
|
(5, 1, 1068.0), (5, 2, 960.0), (5, 3, 1178.0), (5, 4, 65.0), (5, 5, 0.0), (5, 6, 1537.0), (5, 7, 1314.0), (5, 8, 677.0), (5, 9, 1096.0), (5, 10, 703.0), (5, 11, 1935.0), (5, 12, 1446.0), |
|
(6, 1, 1370.0), (6, 2, 2707.0), (6, 3, 1461.0), (6, 4, 1826.0), (6, 5, 2153.0), (6, 6, 0.0), (6, 7, 1251.0), (6, 8, 1323.0), (6, 9, 1379.0), (6, 10, 1314.0), (6, 11, 462.0), (6, 12, 669.0), |
|
(7, 1, 69.0), (7, 2, 1893.0), (7, 3, 243.0), (7, 4, 1243.0), (7, 5, 1338.0), (7, 6, 1251.0), (7, 7, 0.0), (7, 8, 1076.0), (7, 9, 162.0), (7, 10, 202.0), (7, 11, 1374.0), (7, 12, 1140.0), |
|
(8, 1, 713.0), (8, 2, 1622.0), (8, 3, 804.0), (8, 4, 606.0), (8, 5, 677.0), (8, 6, 963.0), (8, 7, 782.0), (8, 8, 0.0), (8, 9, 722.0), (8, 10, 656.0), (8, 11, 1362.0), (8, 12, 872.0), |
|
(9, 1, 864.0), (9, 2, 1635.0), (9, 3, 82.0), (9, 4, 630.0), (9, 5, 701.0), (9, 6, 1674.0), (9, 7, 1110.0), (9, 8, 815.0), (9, 9, 0.0), (9, 10, 933.0), (9, 11, 2240.0), (9, 12, 1583.0), |
|
(10, 1, 133.0), (10, 2, 1716.0), (10, 3, 224.0), (10, 4, 632.0), (10, 5, 1160.0), (10, 6, 954.0), (10, 7, 379.0), (10, 8, 656.0), (10, 9, 142.0), (10, 10, 0.0), (10, 11, 1405.0), (10, 12, 863.0), |
|
(11, 1, 1493.0), (11, 2, 2830.0), (11, 3, 1584.0), (11, 4, 1949.0), (11, 5, 2276.0), (11, 6, 462.0), (11, 7, 1374.0), (11, 8, 1446.0), (11, 9, 1502.0), (11, 10, 1437.0), (11, 11, 0.0), (11, 12, 1024.0), |
|
(12, 1, 547.0), (12, 2, 1884.0), (12, 3, 638.0), (12, 4, 1003.0), (12, 5, 1330.0), (12, 6, 798.0), (12, 7, 616.0), (12, 8, 500.0), (12, 9, 556.0), (12, 10, 491.0), (12, 11, 1196.0), (12, 12, 0.0); |