====== 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 ===== 

{{:pl:dydaktyka:sbd:2012:projekty:oswietlenie:start:oracle.png|}}

===== 3. Tablica krzyżowa =====

{{:pl:dydaktyka:sbd:2012:projekty:oswietlenie:start:tablica_krzyzowa_2.png|}}

  * T1 - OpisProblemu
  * T2 - DaneBazowe
  * T3 - GrupyWynikowe
  * T4 - PasyTymczasowe
  * T5 - Grupy_Punkty
  * T6 - Pasy_Punkty
  * T7 - DaneGeograficzne
  * T8 - Parametry
  * T9 - DaneBazowe_Parametry

===== 4. Projekt bazy w języku SQL ===== 

<code 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
);
</code>

===== 5. Słowniki danych =====

  DowolneZnaki = *wszystkie znaki dla przyjętego standardu kodowania*
  ID = *zakres: od 1 do końca zakresu typu integer*

  * **OpisProblemu**

  OpisProblemu = ID + CSV

  CSV = *dane wejściowe pobierane z pliku w formacie CSV* {DowolneZnaki}


  * **DaneBazowe**

    
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**

    GrupyWynikowe = ID

  * **PasyTymczasowe**

    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**

    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**

    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**

    DaneGeograficzne = ID
  
    ID = *odnosi się do DaneBazowe; zakres: od 1 do końca zakresu typu integer*


  * **Parametry**

    Parametry = ID + Nazwa
  
    ZnakiDlaNazwy = [A-Z|a-z|-|_|0-9|]
  
    Nazwa = *zakres: nieograniczony łańuch znaków* {ZnakiDlaNazwy}


  * **DaneBazowe_Parametry**

    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

      * **Latarnia**
        * IDLatarnia
        * IDLatarnia -> IDAdres
        * IDLatarnia -> IDWspolrzedne
        * IDLatarnia -> IDParametr_Wartosc

      * **OpisProblemu_Latarnia**
        * IDOpisProblemu
        * IDLatarnia

===== 7. Opcjonalnie: Denormalizacja struktury tabel ===== 

    
===== 8. Projektowanie operacji na danych ===== 

Listowanie wszystkich punktów dla danego problemu:
<code sql>
SELECT ID, Miasto, Ulica, X, Y, Z
FROM DaneBazowe
WHERE IDProblemu=%id_problemu%;
</code>

Tworzenie wierszy w tabeli z danymi i tabeli geograficznej (SRID = 4326):

<code sql>
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));
        
</code>

Pobieranie współrzędnych z tabeli geograficznej:

<code sql>
SELECT ST_X(punkt2d), ST_Y(punkt2d)
FROM DaneGeograficzne
JOIN DaneBazowe on DaneBazowe.ID = DaneGeograficzne.ID
WHERE DaneBazowe.IDProblemu = %id_problemu%;
</code>

Pobierz punkty leżące w pasie pomiędzy punktami (2, 3) a (11, 3) o szerokości 2:

<code sql>
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);
</code>

Znajdź punkty znajdujące się w odległości w zakresie (1, 3) od punktu o ID = 987:

<code sql>
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;
</code>

Usuwanie wszystkich danych powiązanych z danym problemem:

<code sql>
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%;

</code>