Projekt logiczny
1. Projekt tabel
We wszystkich tabelach konsekwentnie użyliśmy ID jako klucza głównego. Klucze obce mają jasną nazwę odpowiadającą tabeli na którą wskazują, np. IDGrupy - tabela GrupyWynikowe. Jedynymi indeksami są klucze tabel.
Tabele:
OpisProblemu - Tabela powstała w celu umożliwienia jednoczesnej pracy z aplikacją. „Problem” reprezentuje jedną instancję programu, która jest jednoznacznie identyfikowana. Kolumna CSV typu text zawiera plik CSV podany na wejście, w celu ewentualnego odtworzenia danych, weryfikacji itp.
DaneBazowe - Podstawowe informacje zaimportowane z pliku CSV: Miasto, Ulica - kolumny typu varchar o długości nie większej niż 200 znaków. X, Y, Z - kolumny typu double precision reprezentujące współrzędne. Klucze obce: IDProblemu → OpisProblemu.ID
GrupyWynikowe - Rezultat działania algorytmu. Jedyna kolumna ID jednoznacznie identyfikuje grupę, do której można przypisać punkty. Grupę tworzą współliniowe punkty znajdujące się w danej odległości od siebie z pewną dokładnością.
PasyTymczasowe - Zawiera informacje o kącie nachylenia (kolumna kat) oraz dlugości pasa (kolumna dlugosc), które to informacje są wykorzystywane w kolejnych iteracjach algorytmu. „Pas” to podłużny prostokąt w skład którego wchodzą punkty. Jest to pomocne przy poszukiwaniu punktów współliniowych.
DaneGeograficzne - Tabela przechowująca informacje PostGISa o punkcie. 2 kolumny: punkt2d i punkt3d reprezentują odpowiednio dwu- i trzywymiarowe punkty, które są wykorzystywane podczas działania algorytmu. Klucze obce: ID → DaneBazowe.ID
Parametry - Zawiera nazwy parametrów dodatkowych przekazanych na wejście. Dzięki tej tabeli każdemu punktowi można przypisać dowolną wartość parametru dodatkowego.
Tabele połączeniowe:
Grupy_Punkty - Punkty z tabeli DaneBazowe przypisane do danej grupy z tabeli GrupyWynikowe
Pasy_Punkty - Punkty z tabeli DaneBazowe przypisane do danego pasu z tabeli PasyTymczasowe
DaneBazowe_Parametry - Parametry wraz z wartością odpowiadające punktom z tabeli DaneBazowe
2. Diagram ERD przedstawiający schematy tabel
3. Tablica krzyżowa
4. Projekt bazy w języku SQL
CREATE SEQUENCE OpisProblemu_ID_seq START WITH 1 INCREMENT BY 1 no maxvalue no minvalue cache 1;
CREATE TABLE OpisProblemu(
ID INTEGER DEFAULT NEXTVAL('OpisProblemu_ID_seq') PRIMARY KEY,
CSV text NOT NULL
);
CREATE SEQUENCE DaneBazowe_ID_seq START WITH 1 INCREMENT BY 1 no maxvalue no minvalue cache 1;
CREATE TABLE DaneBazowe(
ID INTEGER DEFAULT NEXTVAL('DaneBazowe_ID_seq') PRIMARY KEY,
IDProblemu INTEGER NOT NULL REFERENCES OpisProblemu (ID),
Miasto VARCHAR(200) NOT NULL,
Ulica VARCHAR(200) NOT NULL,
X DOUBLE PRECISION NOT NULL,
Y DOUBLE PRECISION NOT NULL,
Z DOUBLE PRECISION NOT NULL
);
CREATE SEQUENCE GrupyWynikowe_ID_seq START WITH 1 INCREMENT BY 1 no maxvalue no minvalue cache 1;
CREATE TABLE GrupyWynikowe(
ID INTEGER DEFAULT NEXTVAL('GrupyWynikowe_ID_seq') PRIMARY KEY
);
CREATE SEQUENCE PasyTymczasowe_ID_seq START WITH 1 INCREMENT BY 1 no maxvalue no minvalue cache 1;
CREATE TABLE PasyTymczasowe(
ID INTEGER DEFAULT NEXTVAL('PasyTymczasowe_ID_seq') PRIMARY KEY,
Kat DOUBLE PRECISION NOT NULL,
Dlugosc DOUBLE PRECISION
);
CREATE SEQUENCE Grupy_Punkty_ID_seq START WITH 1 INCREMENT BY 1 no maxvalue no minvalue cache 1;
CREATE TABLE Grupy_Punkty(
ID INTEGER DEFAULT NEXTVAL('Grupy_Punkty_ID_seq') PRIMARY KEY,
IDGrupy INTEGER NOT NULL REFERENCES GrupyWynikowe (ID),
IDPunktu INTEGER NOT NULL REFERENCES DaneBazowe (ID),
Kolejnosc INTEGER NOT NULL
);
CREATE SEQUENCE Pasy_Punkty_ID_seq START WITH 1 INCREMENT BY 1 no maxvalue no minvalue cache 1;
CREATE TABLE Pasy_Punkty(
ID INTEGER DEFAULT NEXTVAL('Pasy_Punkty_ID_seq') PRIMARY KEY,
IDPasa INTEGER NOT NULL REFERENCES PasyTymczasowe (ID),
IDPunktu INTEGER NOT NULL REFERENCES DaneBazowe (ID)
);
CREATE TABLE DaneGeograficzne(
ID INTEGER REFERENCES DaneBazowe (ID)
);
-- geometria 3-wymiarowa
SELECT AddGeometryColumn('danegeograficzne', 'punkt2d', 4326, 'POINT', 2);
SELECT AddGeometryColumn('danegeograficzne', 'punkt3d', 4326, 'POINT', 3);
CREATE TABLE Parametry(
ID serial PRIMARY KEY,
Nazwa text NOT NULL
);
CREATE TABLE DaneBazowe_Parametry(
IDPunktu INTEGER NOT NULL REFERENCES DaneBazowe (ID),
IDParametru serial REFERENCES Parametry (ID),
Wartosc text
);
5. Słowniki danych
DowolneZnaki = *wszystkie znaki dla przyjętego standardu kodowania*
ID = *zakres: od 1 do końca zakresu typu integer*
OpisProblemu = ID + CSV
CSV = *dane wejściowe pobierane z pliku w formacie CSV* {DowolneZnaki}
DaneBazowe = ID + IDProblemu + Miasto + Ulica + X + Y + Z
IDProblemu = *zakres: pełny zakres typu integer* {OpisProblemu}
ZnakiDlaMiasta = [A-Z|a-z|-| |]
Miasto = *zakres: 1-200 znaków* {ZnakiDlaMiasta}
ZnakiDlaUlicy = [A-Z|a-z|-|.| |]
Ulica = *zakres: 1-200 znaków* {ZnakiDlaUlicy}
X = *współrzędna; zakres: pełny zakres typu double*
Y = *współrzędna; zakres: pełny zakres typu double*
Z = *współrzędna; zakres: pełny zakres typu double*
GrupyWynikowe = ID
PasyTymczasowe = ID + Kat + (Dlugosc)
Kat = *kąt obrotu pasa względem osi OX; jednostka: radiany; zakres: -π/2 do π/2*
Dlugosc = *kąt obrotu pasa względem osi OX; jednostka: stopnie; zakres: -180 do 180*
Grupy_Punkty = ID + IDGrupy + IDPunktu + Kolejnosc
IDGrupy = *odnosi się do GrupyWynikowe; zakres: od 1 do końca zakresu typu integer*
IDPunktu = *odnosi się do DaneBazowe; zakres: od 1 do końca zakresu typu integer*
Kolejnosc = *zakres: pełny zakres typu integer*
Pasy_Punkty = ID + IDPasa + IDPunktu
IDPasa = *odnosi się do PasyTymczasowe; zakres: od 1 do końca zakresu typu integer*
IDPunktu = *odnosi się do DaneBazowe; zakres: od 1 do końca zakresu typu integer*
DaneGeograficzne = ID
ID = *odnosi się do DaneBazowe; zakres: od 1 do końca zakresu typu integer*
Parametry = ID + Nazwa
ZnakiDlaNazwy = [A-Z|a-z|-|_|0-9|]
Nazwa = *zakres: nieograniczony łańuch znaków* {ZnakiDlaNazwy}
DaneBazowe_Parametry = ID + IDPunktu + IDParametru
IDPunktu = *odnosi się do DaneBazowe; zakres: od 1 do końca zakresu typu integer*
IDParametru = *odnosi się do Parametry; zakres: od 1 do końca zakresu typu integer*
6. Analiza zależności funkcyjnych i normalizacja tabel
OpisProblemu
IDOpisProblemu
IDOpisProblemu → CSV
Adres
IDAdres
IDAdres → Miasto
IDAdres → Ulica
Wspolrzedne
IDWspolrzedne
IDWspolrzedne → X
IDWspolrzedne → Y
IDWspolrzedne → Z
IDWspolrzedne → punkt2d
IDWspolrzedne → punkt3d
Parametr
IDParametr
IDParametr → Nazwa
Parametr_Wartosc
IDParametr_Wartosc
IDParametr
IDParametr → Wartosc
OpisProblemu_Latarnia
IDOpisProblemu
IDLatarnia
7. Opcjonalnie: Denormalizacja struktury tabel
8. Projektowanie operacji na danych
Listowanie wszystkich punktów dla danego problemu:
SELECT ID, Miasto, Ulica, X, Y, Z
FROM DaneBazowe
WHERE IDProblemu=%id_problemu%;
Tworzenie wierszy w tabeli z danymi i tabeli geograficznej (SRID = 4326):
INSERT INTO DaneBazowe (IDProblemu, Miasto, Ulica, X, Y, Z)
VALUES(%idproblemu%, %miasto%, %ulica%, %x%, %u%, %z%))
RETURNING ID, X, Y; -- potrzebne dla tabeli geograficznej
INSERT INTO DaneGeograficzne (ID, punk2d, punkt3d)
VALUES (%id%,
ST_SetSRID(ST_MakePoint(%x%, %y%), 4326),
ST_SetSRID(ST_MakePoint(%x%, %y%, %z%), 4326));
Pobieranie współrzędnych z tabeli geograficznej:
SELECT ST_X(punkt2d), ST_Y(punkt2d)
FROM DaneGeograficzne
JOIN DaneBazowe ON DaneBazowe.ID = DaneGeograficzne.ID
WHERE DaneBazowe.IDProblemu = %id_problemu%;
Pobierz punkty leżące w pasie pomiędzy punktami (2, 3) a (11, 3) o szerokości 2:
SELECT ST_X(punkt2d) AS X, ST_Y(punkt2d) AS Y
FROM
(SELECT
punkt2d,
ST_Buffer(ST_GeomFromText('LINESTRING(2 3, 11 3)', 4326), 2, 'endcap=round') AS Pas
FROM DaneGeograficzne
JOIN DaneBazowe ON DaneGeograficzne.ID = DaneBazowe.ID
WHERE IDProblemu = %id_problemu%
) AS Temp
WHERE ST_Within(punkt2d, Pas);
Znajdź punkty znajdujące się w odległości w zakresie (1, 3) od punktu o ID = 987:
SELECT
ST_X(Punkt.p1) AS p1x,
ST_Y(Punkt.p1) AS p1y,
ST_X(Punkty.p2) AS p2x,
ST_Y(Punkty.p2) AS p2y,
ST_Distance(p1, p2) AS dist
FROM
(
SELECT punkt2d AS p1
FROM DaneGeograficzne
WHERE ID = 987
) AS Punkt,
(
SELECT punkt2d AS p2
FROM DaneGeograficzne
JOIN DaneBazowe ON DaneGeograficzne.ID = DaneBazowe.ID
) AS Punkty
WHERE ST_Distance(p1, p2) > 1 AND ST_Distance(p1, p2) < 3;
Usuwanie wszystkich danych powiązanych z danym problemem:
DELETE FROM DaneGeograficzne WHERE ID IN (
SELECT ID FROM DaneBazowe WHERE IDProblemu=%problemid%
);
DELETE FROM Grupy_Punkty WHERE IDPunktu IN (
SELECT ID FROM DaneBazowe WHERE IDProblemu=%problemid%
);
DELETE FROM Pasy_Punkty WHERE IDPunktu IN (
SELECT ID FROM DaneBazowe WHERE IDProblemu=%problemid%
);
DELETE FROM DaneBazowe_parametry WHERE IDPunktu IN (
SELECT ID FROM DaneBazowe WHERE IDProblemu=%problemid%
);
DELETE FROM DaneBazowe WHERE IDProblemu=%problemid%;
DELETE FROM OpisProblemu WHERE ID=%problemid%;