データベーススキーマ
TABLE: mapitem
カラム |
意味 |
ename |
Entity Name ユニークな名前(主キー) |
name |
一般名称 |
addr |
住所 |
lat |
緯度 |
lon |
経度 |
TABLE: maptype
カラム |
意味 |
ename |
Entity Name FOREIGN KEY(ename) REFERENCES mapitem(ename) on delete cascade on update cascade |
maptype |
地図種別 |
TABLE: attribute
カラム |
意味 |
ename |
Entity Name FOREIGN KEY(ename) REFERENCES mapitem(ename) on delete cascade on update cascade |
attr |
属性名 |
value |
属性値 |
TABLE: object
カラム |
意味 |
ename |
Entity Name FOREIGN KEY(ename) REFERENCES mapitem(ename) on delete cascade on update cascade |
objtype |
地点の種別(point, multiline, polygon) |
jsonval |
地物(ちぶつ)のマーカ/ライン/ポリゴン のJSON表記値 |
SQL文
CREATE TABLE IF NOT EXISTS mapitem(
ename PRIMARY KEY, name TEXT NOT NULL, addr TEXT, lat REAL, lon REAL
);
CREATE TABLE IF NOT EXISTS maptype(
ename, maptype TEXT,
FOREIGN KEY(ename) REFERENCES mapitem(ename)
ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE(ename, maptype)
);
CREATE TABLE IF NOT EXISTS attribute(
ename, maptype TEXT,
FOREIGN KEY(ename) REFERENCES mapitem(ename)
ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE(ename, maptype)
);
CREATE TABLE IF NOT EXISTS object(
ename, objtype TEXT, jsonval TEXT,
FOREIGN KEY(ename) REFERENCES mapitem(ename)
ON DELETE CASCADE ON UPDATE CASCADE,
CHECK (objtype IN (
'point', 'multipoint', 'linestring',
'multilinestring', 'polygon', 'multipolygon')),
UNIQUE(ename, objtype, jsonval)
);