====== Objektově relační mapování ======

   - objektově relační mapování {{:courses:bd6b33dbs:orm-jpa-jpql.odp|osnova8}}
     - ORM
     - JPA
     - JPQL

======= Implementace databázového klienta ========

Pro přístup do databáze lze v Javě použít
   * **rozhraní JDBC** zprostředkovávající komunikaci mezi klientem a databázovým serverem
   * **Persistent API ** provádějící mapování z relační databáze do objektového návrhu
   * **JPQL** - technologie na pomezí výše zmíněných


==== JDBC ====
 {{courses:a4b33ds:ds-jdbc.zip|Ukázková konzolová implementace JDBC s DTO}}

== Objekt zajišťující připojení k databázi ==

  import java.sql.*;
  public class DatabaseConnection
  {
    private static final String CONNECTION = //"jdbc:postgresql://SERVER:PORT/DATABASE";
    private static final String USERNAME = // USERNAME
    private static final String PASSWORD = // HESLO
    private Connection conn;
    public DatabaseConnection() throws ClassNotFoundException, SQLException
    {    /** Creates a new instance of DatabaseConnection */
        Class.forName("org.postgresql.Driver");
        this.conn = DriverManager.getConnection(CONNECTION, USERNAME, PASSWORD);
    }
    public Connection getConnection()
    {
        return this.conn;
    }
  }

== Objekt zajišťující operace nad databází ==

  public class Storage {
    private static Storage storage = null;
    private Connection con;
    protected Storage() throws ClassNotFoundException, SQLException {
        DatabaseConnection dbconn = new DatabaseConnection();
        con = dbconn.getConnection();
    }
    public static Storage getInstance() throws ClassNotFoundException, SQLException {
        if (storage == null) {
            storage = new Storage();
        }
        return storage;
    }
    public List<Department> getAllDepartments() throws SQLException {
        List<Department> list = new ArrayList<Department>();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("SELECT * FROM department");
        while(rs.next()) {
            list.add(loadDepartment(rs));
        }
        return list;
    }
    protected Department loadDepartment(final ResultSet rs) throws SQLException {
        Department d = new Department();
        d.setDepartmentId(rs.getInt("department_id"));
        d.setDepartmentDescription(rs.getString("department_description"));
        return d;
    }
    public List<Employee> getAllEmployees() throws SQLException {
        List<Employee> list = new ArrayList<Employee>();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("SELECT * FROM employee");
        while(rs.next()) {
            list.add(loadEmployee(rs));
        }
        return list;
    }
    public List<Employee> getEmployeesByDepartment(Department dep) throws SQLException {
        List<Employee> list = new ArrayList<Employee>();
        PreparedStatement ps = con.prepareStatement("SELECT * FROM employee WHERE department_id=?");
        ps.setInt(1, dep.getDepartmentId());
        ResultSet rs = ps.executeQuery();
        while(rs.next()) {
            list.add(loadEmployee(rs));
        }
        return list;
    }
    protected Employee loadEmployee(final ResultSet rs) throws SQLException {
        Employee e = new Employee();
        e.setEmployeeId(rs.getInt("employee_id"));
        e.setBirthDate(rs.getDate("birth_date"));
        e.setFullName(rs.getString("full_name"));
        e.setDepartmentId(rs.getInt("department_id"));
        return e;
    }
    public void insertEmployee(Employee emp) throws SQLException {
        PreparedStatement ps = con.prepareStatement("INSERT INTO employee (employee_id,birth_date,full_name,department_id) VALUES (?,?,?,?)");
        ps.setInt(1, emp.getEmployeeId());
        ps.setDate(2, emp.getBirthDate());
        ps.setString(3, emp.getFullName());
        ps.setInt(4, emp.getDepartmentId());
        ps.executeUpdate();
    }
    public List<Salary> getAllSalaries() throws SQLException {
        List<Salary> list = new ArrayList<Salary>();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("SELECT * FROM salary");
        while(rs.next()) {
            list.add(loadSalary(rs));
        }
        return list;
    }
    public List<Salary> getSalariesByEmployee(Employee emp) throws SQLException {
        List<Salary> list = new ArrayList<Salary>();
        PreparedStatement ps = con.prepareStatement("SELECT * FROM salary WHERE employee_id=?");
        ps.setInt(1, emp.getEmployeeId());
        ResultSet rs = ps.executeQuery();
        while(rs.next()) {
            list.add(loadSalary(rs));
        }
        return list;
    }
    protected Salary loadSalary(final ResultSet rs) throws SQLException {
        Salary s = new Salary();
        s.setEmployeeId(rs.getInt("employee_id"));
        s.setPayDate(rs.getDate("pay_date"));
        s.setSalaryPaid(rs.getDouble("salary_paid"));
        return s;
    }
  }

==== Java Persistence  ====

Cílem tohoto tutoriálu je na jednoduchém příkladě ukázat základy práce s Java Persistence API v prostředí NetBeans verze 6.9.
V první části tohoto tutoriálu se zaměříme na vytváření entit, manipulaci s entitami a vytvoření relací mezi entitami. 

=== Založení projektu a vytvoření Persistence Unit pro připojení k databázi  ===

  - Založte si nový projekt typu Java Application v Netbeans
    * V menu File vyberte položku New project...
    * V dialogovém boxu vyberte v levém okně kategorii Java a v pravém okně položku Java Application
    * Klikněte na tlačítko Next
    * Název projektu změňte na JPADemo
    * Klikněte na tlačítko Finish
    * V properties projektu JPADemo zvolte JDK 6
  - Nyní vytvořte připojení ke školní databázi 
    * Ze záložky Projects se přepnšte do záložky Services a rozbalte uzel Databases
    * V uzlu Drivers zkontrolujte, že máte driver k PostgreSQL, jinak klepněte pravým tlačítkem myši na Drivers a následně pomocí New driver doplňte driver k PostgreSQL ([[http://jdbc.postgresql.org/download.html|driver link]])
    * Nyní můžete přidat připojení k Vaší databázi, klepněte pravým tlačítkem myši na Databases, New Connection
    * Vyplňte údaje k Vaší školní databázi a potvrďte pomocí OK
    * Pozor, nyní musíte nastavit schéma PUBLIC a potvrďte pomocí OK
    * Klepněte pravým tlačítkem myši na nově vytvořené připojení a připojte se pro kontrolu ke své databázi.
  - Dalším krokem je vlastní připojení k databázi, ta se připojuje pomocí **Persistence Unit**
    * V panelu se seznamem otevřených projektů vyberte projekt JPADemo.
    * V menu File vyberte položku New File...
    * V v dialogovém boxu vyberte v levém okně kategorii Persistencea v pravém okně položku Persistence Unit
    * Klikněte na tlačítko Next. 
    * Jako název ponechte JPADemoPU, jako Persistence Library použijte EclipseLink(JPA 2.0), která bude pro naše účely dostačující.
    * Jako Database Connection vyberte již připravené připojení ke školní databázi 
    * Jako Table Generation Strategy zvolte Create
    * Klikněte na tlačítko Finish.
    * V hlavním okně se otevře konfigurační soubor persistence.xml s nově vytvořenou JPADemoPU.
    * Kliknutím na tlačítko XML v levém horním rohu okna si můžete problédnout vygenerovaný XML soubor
    * Zkontrolujte a případně doplňte heslo
  - Zkontrolujte, že mezi knihovnami projektu máte eclipselink.jar a eclipselink-javax.persistence.jar a přidejte knihovnu pro práci s databází Postgres

=== Vytváření entit  ===
Základem objektově-relačního mapování je vytvoření dvojic entita - tabulka a atribut - sloupec v tabulce.
V JPA jsou entity reprezentovány objekty typu POJO (plain old Java object), tj. objekty mají pouze atributy a funkce pro jejich získaní (getters) a pro jejich nastavení (setters).
Předvedeme si to na příkladu s knihami.

<code java>
@Entity
//@Table(name="book_table")
public class Book implements Serializable {
  @Id //@GeneratedValue(strategy = GenerationType.AUTO)
  private Long id;
  @Column(nullable=false)
  private String title;
  @Column(length=2000)
  private String description;
  private Integer nbOfPages;
  
  public static Book createBook(Long id, String title, String description, Integer nbOfPages) {
        Book book = new Book();
        book.setId(id);
        book.setTitle(title);
        book.setDescription(description);
        book.setNbOfPages(nbOfPages);
        return book;
  }
    
  public String toString() {
        return String.valueOf(this.getId())+" ; "+this.getTitle()+" ; "+this.getDescription();
  }
  
  public Long getId() {
        return id;
  }
  
  public void setId(Long id) {
        this.id = id;
  }
  
  //dalsi getters a setters
  //V Netbeans je mozne getters a setters vygenerovat automaticky. 
  //Kliknete v okne editace pravym tlacitkem mysi a vyberte Refactor
  //a nasledne Encapsulate Fields. Nyni si muzete vybrat, 
  //ktere getters a setters si nechate vygenerovat.
}
</code>

Vysvětlivky:
  * **@Entity** je anotace, která říká, že objekt je entita. Nepovinně se uvádí i jméno tabulky pokud se liší od jména třídy **@Table**(name="book_tab").
  * **@Id** anotace pro označení primárního klíče s možností automatického generování hodnoty **@GeneratedValue**(strategy = GenerationType.AUTO) 
  * **@Column** anotace pro upřesnění vlastností daného atributu
     * **name** - jméno v tabulce, pokud se liší od jména atributu
     * **unique** - vyžadována unikátní hodnota default false
     * **nullable** - nutno vyplnit default true
     * **length** - délka default 255

**POZOR: Každou entitu je nutné přidat do seznamu entit v Persistence Unit. (sekce Include Entity Classes, nebo do tagu <class> v XML.**

=== Manipulace s entitami ===
Nyní si ukážeme práci s entitami. V projektu JPADemo si vytvořte třídu Main typu Java Main Class, jejíž metodu main budeme editovat. Komunikace mezi aplikací a databází probíhá pomocí rozhraní EntityManager. V kódu je postupně ukázáno, jak vytvářet nové instance entit, jak je aktualizovat a mazat.

<code java>
//Entity manager and transaction
EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPADemoPU");
EntityManager em = emf.createEntityManager();
EntityTransaction tx = em.getTransaction();

//create new entity and persist it to the database
Book book = Book.createBook(123L, "JPA 2.0 - Mastering the Java Persistence API",
    "Kompletni pruvodce JPA 2.0",532);
tx.begin();
em.persist(book);
tx.commit();

//finding by ID
Book booka = em.find(Book.class, 123L);
System.out.println("Finding book> "+booka);

//removing an entity
Book bookrem = Book.createBook(124L, "Java", null, null);
tx.begin();
em.persist(bookrem);
tx.commit();
    Book bookf = em.find(Book.class, 124L);
    System.out.println("Remove book before> "+bookf);
tx.begin();
em.remove(bookrem);
tx.commit();
    Book bookg = em.find(Book.class, 124L);
    System.out.println("Remove book after> "+bookg);

//merging an entity outside transaction
Book bookmer = Book.createBook(125L, "Java", null, null);
tx.begin();
em.persist(bookmer);
tx.commit();
em.clear(); //demonstrate another work with database
    bookmer.setDescription("Zase nejaka Java");
    Book bookh = em.find(Book.class, 125L);
    System.out.println("Merging book before> "+bookh);
tx.begin();
em.merge(bookmer);
tx.commit();
    Book booki = em.find(Book.class, 125L);
    System.out.println("Merging book after> "+booki);

//update an entity inside transanction
Book booku = Book.createBook(126L, "Java 6", null, null);
tx.begin();
em.persist(booku);
booku.setDescription("No a jak jinak, zase Java.");
tx.commit();
    Book bookj = em.find(Book.class, 126L);
    System.out.println("Update book> "+bookj);
</code>

<fc #0000FF>Úkol 1: Vytvořte novou entitu Author, která bude mít atributy id (typu @Id, automaticky generované), name (typ String délky 50) a surname (typ String délky 100, nutno vyplnit). Entity se budou mapovat do tabulky author_table. Nezapomeňte přidat třídu Author do Persistence Unit. Otestujte správnost vytvořením instance entity a kontrolou databázové tabulky.</fc>

=== Svázání entit relacemi ===

Nyní když máme dvě entity Book a Author je můžeme svázat relací typu 1:N. 

V JPA můžeme použít jednosměrné (unidirectional) nebo obousměrné (bidirectional) relace. Vždy je nutné rozhodnout, která entita bude nositelem relace (odkaz na svázanou entitu je přímo atributem nosné entity). U obousměrné relace bude jedna entita nositelem relace a druhá entita bude inverzní (svázání je obousměrné, odkazy jsou přímo atributem obou entit). 

**Book a Author - 1:N jednosměrná - nositelem je Book:**

<code java>
public class Book
  @Id
  private Long id;  
  private String title;
  private String description;
  private Integer nbOfPages;
  @ManyToOne
  private Author author;
  
public class Author{
    @Id
    private Long id;
    private String name;
    private String surname;  
  
</code>

**Book a Author - 1:N obousměrná - nositelem je Book:**
<code java>
public class Book
  @Id
  private Long id;  
  private String title;
  private String description;
  private Integer nbOfPages;
  @ManyToOne
  private Author authorRelation;
  
public class Author{
    @Id
    private Long id;
    private String name;
    private String surname;  
    @OneToMany(mappedBy = "authorRelation")
    private List<Book> books;
</code>

<fc #0000FF>Úkol 2: Vyzkoušejte relaci mezi entitami. Vytvořte tři instance entity Book a dvě instance entity Author, správně je provázejte tak, aby první autor měl dvě knihy a druhý knihu jednu a vše uložte do databáze. Následně zjistěte autora jedné z knih. Zkontrolujte, jak se relace projevila v databázi. (Vyzkoušejte postupně jednosměrnou i obousměrnou relaci).</fc>

=== Odkazy  ===

První část uzpůsobena pro potřeby předmětu Databázové systémy z tutoriálu [[http://kore.fi.muni.cz:5080/wiki/index.php/JPA_v_NetBeans_6.0|JPA_v_NetBeans_6.0]]

Daleko podrobnější informace lze nalézt například v [[http://download.oracle.com/javaee/6/tutorial/doc|The Java EE 6 Tutorial]]

Další informace, viz např [[http://www.objectdb.com/java/jpa/entity/generated| Entity @generated]].

==== Java Persistence - JPQL ====

Cílem pokračování tutoriálu o JPA je ukázat možnosti dotazování (JPQL). Dotazování budeme opět zkoušet v databázi FoodMart, na které jsme již zkoušeli SQL příkazy. Syntax jazyka JPQL spolu s příklady dotazů v JPQL lze najít například v [[http://download.oracle.com/javaee/6/tutorial/doc/bnbtl.html|JPQL tutorial]].

Stáhněte si Netbeans projekt {{courses:a4b33ds:cviceni10.zip|cviceni10.zip}}, ve kterém je připraveno připojení k databázi a dvě entity Store a Employee. Do projektu doplňte knihovnu pro PostgreSQL, která je součástí zip souboru. Ve třídě Main je pak ukázka několika dotazů:

<code java>
EntityManagerFactory emf = Persistence.createEntityManagerFactory("DScviceniPU");
EntityManager em = emf.createEntityManager();

System.out.println("********************");
System.out.println("*  Dynamic Queries *");
System.out.println("********************");

TypedQuery queryS = em.createQuery("Select s from Store s", Store.class);
List<Store> listS = queryS.getResultList();

for (Iterator<Store> itS = listS.iterator(); itS.hasNext();) {
  Store store = itS.next();
  System.out.println(store);
}

System.out.println("*******************");
System.out.println("*  Named Queries  *");
System.out.println("*******************");

Query queryC = em.createNamedQuery(Employee.findByLastName);
queryC.setParameter("lastName", "Smith");
Long count = (Long)queryC.getSingleResult();
System.out.println(count);

System.out.println("*******************");
System.out.println("*  Native Queries *");
System.out.println("*******************");

Query queryN = em.createNativeQuery("SELECT first_name, last_name FROM Employee where last_name=?");
queryN.setParameter(1, "Smith");
List<Object[]> listN = queryN.getResultList();
for (Iterator<Object[]> itN = listN.iterator(); itN.hasNext();) {
  Object[] obj = itN.next();
  System.out.println(obj[0] + " " + obj[1]);
}
</code>

**Úkoly (vždy použijte dynamické dotazy):**
  * Vypište pouze zaměstnance s platem větším než zadaná konstanta.
  * Vypište pouze zaměstnance jejichž přijmení začíná na S a srovnejte je podle abecedy.
  * Zjistěte průměrné platy zaměstnanců - mužů podle profese. Profese vypište na obrazovku spolu s průměrným platem.
  * Vypište všechny zaměstnance, kteří pracují v obchodu Store 1.


/*

====== GIS ukázka ======

Ukážeme si použití hotového GIS (geografického informačního systému) uloženého v databázi PostgreSQL. K těmto účelům slouží rozšíření [[http://postgis.refractions.net/|PostGIS]], které databázi //spatially enables//, tedy přidá prostorovou podporu.



==== Založení databáze pro GIS ====

**Upozornění:** vše v této sekci je už v naší databázi zařízeno, uvedené kroky proto neprovádějte. Kromě toho některé operace vyžadují administrátorská práva na databázi. Tato sekce je pro ilustraci, jak byste si takovou databázi sami zakládali.

Zbytek této sekce předpokládá, že je rozšíření PostGIS již nainstalováno v systému (ukázky jsou na linuxu), ať už ze zdrojových kódu, nebo z repository.

Pro založení databáze s GIS daty je potřeba mít v této databázi nainstalovaný SQL procedurální jazyk PL/pgSQL, který PostGIS používá pro funkce a triggery.
<code sql>
CREATE LANGUAGE plpgsql;
</code>

Dále je potřeba načíst definice potřebných funkci a objektů, které jsou v souboru
<code>
lwpostgis.sql
</code>
případně (v novejších verzích PostgreSQL)
<code>
postgis.sql
</code>
který se nachází např. v 
<code>
/usr/share/postgresql-8.3-postgis/
</code>

Dále je šikovné si nahrát definici mnoha souřadnicových systémů do tabulky ''spatial_ref_sys'', které jsou v souboru
<code>
spatial_ref_sys.sql
</code>

A ještě se hodí podpora pro //integer arrays// v souboru:
<code>
/usr/share/postgresql/8.3/contrib/_int.sql
</code>

Při přidávání GIS tabulek je potřeba jejich geometrické sloupce zaregistrovat v tabulce ''geometry_columns'', buď pomocí funkce ''AddGeometryColumn'', nebo ručně. Zde si PostGIS udržuje metadata o těchto sloupcích (typ objektů, SRID, ...).

==== OpenStreetMap ====

[[http://www.openstreetmap.org/|OpenStreetMap]] je veřejně přístupná otevřená editovatelná mapa světa. Do této mapy může každý přispívat, ale velkým zdrojem jsou dostupná data od různých organizací (vládních, apod.), která ale musí splňovat licenci OpenStreetMap. Detaily viz [[http://wiki.openstreetmap.org/wiki/Main_Page|OpenStreetMap wiki]]. Zkuste si prohlédnout vygenerované rastrové [[http://www.openstreetmap.org/|mapy]]. Uvidíte, že jsou na velmi dobré úrovni i v ČR.

Rastrová podoba mapy je generovaná z vektorových dat uložených právě v PostGISu na PostgreSQL. Tím, že je OpenStreetMap otevřená, můžeme si vektorová data map stáhnout. To jiné mapy (např. GoogleMaps, mapy.cz, apod.) často nenabízejí. Data jsou dostupná ve vlastním formátu -- XML nebo binárním, ke kterým OpenStreetMap nabízí utilitu ''osm2pgsql'', kterou se provede import do PostGIS databáze. Data jsou k dispozici např. na [[http://planet.openstreetmap.org/|této adrese]]. Pro představu, vektorová data zabalená do BZIP2 mají cca 27GB. Po importu do databáze mají stovky GB. Pro ukázku jsme si do společné databáze naimportovali jen výňatek -- Českou republiku, dostupnou na [[http://download.geofabrik.de/osm/europe/|Geofabrik.de]]. Ta má zabalená cca 300MB, po importu do databáze řádově 4GB.

==== Projekce ====

V GIS pracujeme s body na povrchu planety, která má tvar přibližně jako elipsoid. Abychom mohli pracovat s dvourozměrnými souřadnicemi bodů, je třeba body z povrchu planety popsat sférickými souřadnicemi (pak použijeme jen úhlové souřadnice -- předpokládáme, že body leží na povrchu), nebo promítnout na nějakou plochu popsatelnou 2D souřadnicemi.

PostGIS tak umožňuje dva režimy při práci s body:
  * geografický -- popisuje body sférickými souřadnicemi (v úhlových jednotkách) -- šířka a délka (latitude a longitude)
    * nejkratší spojnice dvou bodů je oblouk na kouli
    * složité výpočty
    * pro přesnější výpočty (planeta není koule) je třeba uvažovat sféroid, což vše ještě komplikuje
  * geometrický -- pracuje s rovinou (kartézskými souřadnicemi)
    * nejkratší spojnice dvou bodů je rovná čára
    * hodně zjednodušuje výpočty (vzdáleností, ploch, průsečíků...)

OpenStreetMap používá geometrický režim, a také ve zbytku tutoriálu se budeme zabývat výhradně jím.

Obvyklým souřadnicovým systémem je WGS 84, se kterým se můžete často setkávat např. v systémech GPS, nebo na mapách a mapových serverer. Souřadnice WGS 84 budovy E FELu na Karlově námestí je např. (zobrazte si ji na oblíbeném mapovém serveru)
<code>
50°4'36.289"N, 14°25'5.136"E
</code>
vyjádřeno v obvyklém textové formátu, kde šířka a délka je udána ve stupních, minutách a desetinném čísle sekund. Znaménko jednotlivých souřadnic označující polokoule je udáno anglickou zkratkou světové strany (N = severní / S = jižní, E = východní / W = západní polokoule).

Jinak lze tytéž souřadnice vyjádřit prostou dvojicí desetinných čísel x a y (budeme dál používat v ukázkách):
<code>
14.4180933, 50.0767469
</code>

Pozor: souřadnice "šířka a délka" ("latitude a longitude") mají opačné pořadí než "x a y" -- šířka = y, délka = x.

Pro geometrický režim musíme vždy říci, v jakém promítání pracujeme -- jak body z povrchu planety promítáme do roviny. Způsobů je mnoho, každý je vhodný na něco jiného. Většina používaných je standardizována pod označením EPSG, např. zmíněné WGS 84 má označení EPSG:4326. Toto a mnoho dalších najdete v tabulce ''spatial_ref_sys''. WGS 84 tedy ale vlastně není projekce do roviny, pouze se vezmou úhlové sférické souřadnice jako souřadnice 2D prostoru. Takto s nimi tedy nelze např. počítat vzdálenosti.

PostGIS pro promítání používá projekční knihovnu PROJ.4. Zobrazte si parametry zobrazení pro WGS 84 následujícím dotazem. Uvidíte zde inicializační řetězec pro nastavení knihovny PROJ.4.
<code sql>
SELECT * FROM spatial_ref_sys WHERE srid=4326;
</code>
Zde vidíte, že projekce je ''longlat'', tedy prosté úhlové souřadnice, a elipsoid je daný standardem WGS 84.

OpenStreetMap ale používá projekci Google Mercator projection, která defaultně v PostGISu není -- je třeba ji tam doplnit (skript na toto je součástí utility ''osm2pgsql''). Ta nese označení 900913, lze ji tedy vypsat následujícím dotazem:
<code sql>
SELECT * FROM spatial_ref_sys WHERE srid=900913;
</code>
Zkuste odhadnout, co jednotlivé parametry projekce znamenají. Které popisují elipsoid ? Čím je ten elipsoid specifický ? Zkratka projekce //merc// odpovídá Mercator projection, což je cylindrické zobrazení. Posuďte, kde zobrazení vykazuje největší zkreslení, a ověřte na rastrové mapě na webu OpenStreetMap.

Na okraj pro zájemce: pro ČR se často používá tzv. Křovákovo zobrazení, neboli S-JTSK, nesoucí označení EPSG:2065. Jedná se o kuželové zobrazení, které vzniklo pro českou a slovenskou kartografii, protože na území bývalého Československa vykazuje malé zkreslení -- kužel je nastaven tak, že na tomto území "dobře doléhá".

==== GIS objekty ====

Základní objekty v GIS systémech jsou obvykle
  * bod
  * linie, která se skládá z rovných úseček
  * polygon -- plocha ohraničená rovnými úsečkami

Abychom je mohli standardizovaně zapisovat, existují definice
  * WKT -- well-known text, textová reprezentace, vhodná pro zápis dotazů
  * WKB -- well-known binary, binární zápis, použitý pro ukládání do databáze

Příklady textové reprezentace objektů ve WKT jsou:
  * POINT(0 0)
  * LINESTRING(0 0,1 1,1 2)
  * POLYGON( (0 0,4 0,4 4,0 4,0 0), (1 1, 2 1, 2 2, 1 2,1 1) )

Ještě může být před slovem POINT/LINESTRING/POLYGON předpona MULTI, čímž se objekt daného typu může vícekrát v "zapouzdřujícím" objektu opakovat.

Pro převod pak slouží funkce:
<code>
bytea WKB = ST_AsBinary(geometry);
text WKT = ST_AsText(geometry);
geometry = ST_GeomFromWKB(bytea WKB, SRID);
geometry = ST_GeometryFromText(text WKT, SRID);
</code>

==== Dotazování ====

=== Výpočet vzdálenosti ===

Nejprve si zkusíme vypočítat vzdálenost mezi dvěma body. Jako jeden použijeme výše uvedený, reprezentující polohu bodovy E na Karlově náměstí, jako druhý souřadnice vchodu do bodovy FELu v Dejvicích, které jsou v textové podobě
<code>
50°6'9.105"N, 14°23'34.029"E
</code>
a v čistě číselné podobě:
<code>
14.3927858, 50.10252917
</code>

Nejprve učiníme naivní pokus: zavoláme funkci na výpočet vzdálenosti na tyto souřadnice:
<code sql>
SELECT ST_Distance(
  ST_GeometryFromText('POINT(14.4180933 50.0767469)', 4326),
  ST_GeometryFromText('POINT(14.3927858 50.10252917)', 4326)
);
</code>
Co vám dotaz vrátí za hodnotu ? V jakých je jednotkách ? Má hodnota nějaký smysl ? Jak ji PostGIS spočítal ? Zkuste dosáhnout stejného výsledku sami.

Zkusíme tedy body nejprve promítnout zvoleným zobrazením, např. tím v OpenStreetMap -- 900913, a pak teprve určit vzdálenost.
<code sql>
SELECT ST_Distance(
  ST_Transform(ST_GeometryFromText('POINT(14.4180933 50.0767469)', 4326), 900913),
  ST_Transform(ST_GeometryFromText('POINT(14.3927858 50.10252917)', 4326), 900913)
);
</code>
Vypadá výsledek reálně ? Je přesný ? Proč (ne) ? Porovnejte s měřením vzdálenosti např. na [[http://www.mapy.cz/|mapy.cz]].

Zkuste totéž, ale v promítání vhodném pro ČR (S-JTSK):
<code sql>
SELECT ST_Distance(
  ST_Transform(ST_GeometryFromText('POINT(14.4180933 50.0767469)', 4326), 2065),
  ST_Transform(ST_GeometryFromText('POINT(14.3927858 50.10252917)', 4326), 2065)
);
</code>

Porovnejte s hodnotou, kterou vypočítá použití geografického režimu -- výsledek je 3391.7738m. Geografický režim bohužel není ve starší verzi PostGISu, která je nainstalována na našem serveru, ještě implementován.

=== Hledání bodů ===

Nejprve si zkusíme prohlédnout záznam o nějakém bodu (v OpenStreetMap jsou samozřejmě i liniové a plošné objekty), které jsou mimo jiné v tabulce ''planet_osm_point'', např. zvolený obchod na Karlově náměstí, který najdeme podle identifikátoru:
<code sql>
SELECT *
FROM planet_osm_point
WHERE osm_id=290036012
</code>

Podívejte se, co všechno lze v tabulce o objektu nalézt. Podívejte se na binární reprezentaci geometrie ve sloupci ''way''. Tu si můžeme zobrazit ve WKT formátu:

<code sql>
SELECT ST_AsText(way)
FROM planet_osm_point
WHERE osm_id=290036012
</code>

Vidíme, že se skutečne jedná o bod. Můžeme si ho převést do WGS 84:
<code sql>
SELECT ST_AsText(ST_Transform(way, 4326))
FROM planet_osm_point
WHERE osm_id=290036012
</code>
Zobrazte si souřadnice na mapě, např. [[http://maps.google.com|GoogleMaps]] akceptují souřadnice WGS 84 v desetinných číslech. Pozor na pořadí souřadnic (my máme x,y, ale GoogleMaps očekávají šířka,délka).

Nyní zkusíme hledání bodů poblíž referenčního bodu podle vzdálenosti. Např. chceme najít body nacházející se do 200m (na to se hodí predikát ''ST_DWithin'') od budovy E FELu na Karlově náměstí. Následující příkaz najde tyto body, seřadí je podle nalezené vzdálenosti, a tyto vzdálenosti s dalšími detaily o bodech zobrazí. Rozeberte si následující příkaz, najděte parametr maximální vzdálenosti, a příkaz si vyzkoušejte. Co všechno se v tomto okolí nalézá ?

<code sql>
SELECT osm_id,"addr:housenumber",amenity,building,name,railway,shop,tourism,way,
  ST_Distance(way, ST_Transform(ST_GeometryFromText('POINT(14.4180933 50.0767469)', 4326), 900913))
FROM planet_osm_point
WHERE ST_DWithin(way, ST_Transform(ST_GeometryFromText('POINT(14.4180933 50.0767469)', 4326), 900913), 200)
ORDER BY ST_Distance(way, ST_Transform(ST_GeometryFromText('POINT(14.4180933 50.0767469)', 4326), 900913));
</code>

V tabulce je více jak milion bodů. Jaktože je hledání tak rychlé ? Odpovědí jsou opět indexy, tentokrát prostorové -- v tomto případě je prostorový index na sloupci ''way''. Která část příkazu index využije ?

Další příklad ukazuje, jak najdeme např. 10 nejbližších bodů. Rozdíl oproti předchozímu je, že nepoužijeme omezení na vzdálenost predikátem ''ST_DWithin'', ale jen si je opět seřadíme podle vzdálenosti, a omezíme počet řádků ve výsledku:

<code sql>
SELECT osm_id,"addr:housenumber",amenity,building,name,railway,shop,tourism,way,
  ST_Distance(way, ST_Transform(ST_GeometryFromText('POINT(14.4180933 50.0767469)', 4326), 900913))
FROM planet_osm_point
ORDER BY ST_Distance(way, ST_Transform(ST_GeometryFromText('POINT(14.4180933 50.0767469)', 4326), 900913))
LIMIT 10;
</code>

Jak dlouho trvá tento dotaz ? Porovnejte s předchozím časem, a najděte příčinu. Lze vzdálenosti indexovat ?

==== Prostorové indexy ====

PostGIS používá R-stromy (ještě s pomocí GiST -- Generalized Search Trees).
R-stromy jsou rozšířením B+ stromů do 2D. Dělí prostor na MBR -- minimal bounding rectangles, které nemusí být disjunktní. Nové body se umísťují do listu, který vyžaduje nejmenší zvětšení MBR.
Ukázka na cvičení na tabuli.

==== Vizualizace ====

Pro ty, kterým zbyde čas: můžete si zkusit vektorová data graficky prohlédnout např. programem [[http://udig.refractions.net/download/|uDig]]. Zde zvolíte novou vrstvu, PostGIS, a vyplníte údaje pro připojení k databázi.

====== Datový sklad ======

Cílem cvičení je seznámení se s prací v [[http://krizik.felk.cvut.cz:9980/mondrian/testpage.jsp|datovém skladu pomocí aplikace JPivot]].

Dokumentace k [[http://mondrian.pentaho.com/documentation/schema.php|Mondrian OLAP]]

Opět se pracuje s daty FoodMart, jejichž schéma je [[https://cw.felk.cvut.cz/lib/exe/fetch.php/courses/a4b33ds/foodmarter.png|zde]].

*/

===== Další důležité odkazy  =====
Tutoriály na JDBS:

[[http://www.javatpoint.com/java-jdbc]]
[[http://www.wideskills.com/jdbc-tutorial/introduction-to-jdbc]]

Tutoriály na JPA:

youtube tutorial part 1: [[https://www.youtube.com/watch?v=j8m_DyfkI0g]] 

youtube tutorial part 2: [[https://www.youtube.com/watch?v=bhW37_jozB8]]

http://www.informit.com/articles/article.aspx?p=1671224&seqNum=2

https://dzone.com/articles/jpa-tutorial-setting