map_app/test-init.sql at main · leolabdev/map_app · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
CREATE DATABASE IF NOT EXISTS mapApp;

USE mapApp;


CREATE TABLE IF NOT EXISTS Profile (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS Client (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255),
    type VARCHAR(255) NOT NULL,
    city VARCHAR(255) NOT NULL,
    street VARCHAR(255) NOT NULL,
    building VARCHAR(255) NOT NULL,
    flat INT,
    lon DOUBLE NOT NULL,
    lat DOUBLE NOT NULL,
    profileId INT NOT NULL,
    FOREIGN KEY (profileId) REFERENCES Profile(id)
);

CREATE TABLE IF NOT EXISTS OrderData (
    id INT PRIMARY KEY AUTO_INCREMENT,
    senderId INT NOT NULL,
    recipientId INT NOT NULL,
    profileId INT NOT NULL,
    FOREIGN KEY (senderId) REFERENCES Client(id),
    FOREIGN KEY (recipientId) REFERENCES Client(id),
    FOREIGN KEY (profileId) REFERENCES Profile(id)
);

CREATE TABLE IF NOT EXISTS Data (
    name VARCHAR(255) PRIMARY KEY,
    value VARCHAR(255),
    lastUpdated DATE
);

CREATE TABLE IF NOT EXISTS Area (
    areaName VARCHAR(255) PRIMARY KEY,
    polygon TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS TMS (
    stationId INT PRIMARY KEY,
    lon DOUBLE NOT NULL,
    lat DOUBLE NOT NULL,
    polygonCoordinates TEXT NOT NULL
);


INSERT INTO Area (areaName, polygon) VALUES
    ('HelsinkiCenter', '{"type":"Polygon","coordinates":[[[24.941890002981467,60.17603643297042],[24.93338681110933,60.170191688772974],[24.928945917949648,60.16494914657193],[24.933016219240955,60.16136351859848],[24.940410299269672,60.15975503608763],[24.9491841845944,60.16026642534982],[24.95270820198894,60.16324641885882],[24.95308406332134,60.16655752452894],[24.95177946063086,60.17161726542065],[24.941890002981467,60.17603643297042]]]}'),
    ('LahtiCenter', '{"type":"Polygon","coordinates":[[[25.66599090537329,60.983724302388595],[25.65358122815951,60.98458420701496],[25.652876960763706,60.98253453296476],[25.664995216986057,60.98155676831499],[25.66599090537329,60.983724302388595]]]}'),
    ('SlowTraffic', '{"type":"MultiPolygon","coordinates":[[[[[24.637861345837642,60.22083062635369],[24.637861345837642,60.22096537364631],[24.638132654162355,60.22096537364631],[24.638132654162355,60.22083062635369],[24.637861345837642,60.22083062635369]]],[[[24.729125428307462,60.20088462635369],[24.729125428307462,60.20101937364631],[24.72939657169254,60.20101937364631],[24.72939657169254,60.20088462635369],[24.729125428307462,60.20088462635369]]]]]}'),
    ('TampereCenter', '{"type":"Polygon","coordinates":[[[23.747208469556597,61.501388996700626],[23.74879417521842,61.49487533079994],[23.76273111169465,61.495388791129415],[23.76057649032225,61.50195734504658],[23.747208469556597,61.501388996700626]]]}'),
    ('TurkuCenter', '{"type":"Polygon","coordinates":[[[22.25588786227135,60.45179704850969],[22.262687292015528,60.44754941330234],[22.27248544462273,60.45088565941978],[22.269242639667368,60.45432472970708],[22.25588786227135,60.45179704850969]]]}');

INSERT INTO TMS (stationId, lon, lat, polygonCoordinates) VALUES
    (20002, 24.637997, 60.220898, '[[24.637861345837642,60.22083062635369],[24.637861345837642,60.22096537364631],[24.638132654162355,60.22096537364631],[24.638132654162355,60.22083062635369],[24.637861345837642,60.22083062635369]]'),
    (20004, 24.680272, 60.210344, '[[24.68013638948933,60.21027662635369],[24.68013638948933,60.21041137364631],[24.680407610510667,60.21041137364631],[24.680407610510667,60.21027662635369],[24.68013638948933,60.21027662635369]]');