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:
Tabele połączeniowe:
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 );
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*
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%;