-- First of all, set search path
/* Following data are considered to be present in database:

cyklotrasy table url: http://www.geoportalpraha.cz/cs/opendata/48409BAA-9EE2-4099-86B1-1C177C3EFD50#.VrILw14oDtQ
mestskecast table url: http://www.geoportalpraha.cz/cs/opendata/17B544F5-A295-4AF4-BA8F-3C707537E5F7#.VrIMaV4oDtQ
obj_MPP table url: http://www.geoportalpraha.cz/cs/opendata/87C3E2D0-5F4C-4336-81CA-18C5FD02ACA4#.VrIMsF4oDtQ
pesitrasy table url: http://www.geoportalpraha.cz/cs/opendata/22161CF2-48EB-4FEF-B545-2B70D048D62D#.VrIMTV4oDtQ
verejnawc table url: http://www.geoportalpraha.cz/cs/opendata/B5E575FB-AE56-4928-8C4F-AD0B97308093#.VrIM014oDtQ
verejnstavby table url: http://www.geoportalpraha.cz/cs/opendata/69735BAD-4062-437E-8092-1CE7765552BD#.VrINJl4oDtQ

the data should be downloaded in *.shp (Esri Shapefile) format and then converted using following commands:

$ shp2pgsql -s 5514 -W latin2 DOP_Cyklotrasy_l.shp cyklotrasy > cyklotrasy.sql
$ shp2pgsql -s 5514 -W latin2 MAP_MestskeCasti_p.shp mestskecast > mestskecast.sql
$ shp2pgsql -s 5514 -W latin2 BEZ_ObjektMPP_b.shp obj_MPP > obj_MPP.sql
$ shp2pgsql -s 5514 -W latin2 DOP_PesiTrasy_l.shp pesitrasy > pesitrasy.sql
$ shp2pgsql -s 5514 -W latin2 FSV_VerejnaWC_b.shp verejnawc > verejnawc.sql
$ shp2pgsql -s 5514 -W latin2 VPS_b.shp verejnstavby > verejnstavby.sql

NOTE: you can also use -S switch to get single geometry, but this will end with an error:

Shapefile type: Arc
Postgis type: LINESTRING[2]
We have a Multilinestring with 2 parts, can't use -S switch!

and then imported into database using psql import command as follows (in psql):

\i cyklotrasy.sql;
\i mestskecast.sql;
\i obj_MPP.sql;
\i pesitrasy.sql;
\i verejnawc.sql
\i verejnstavby.sql;
*/

/* $$$$$$$$$$$ START OF Prepartion $$$$$$$$$$$$$$$$$$$$$$$$$$$$ */
/* check if we have some empty geometry */
SELECT * FROM cyklotrasy WHERE st_isempty(cyklotrasy.geom);
SELECT * FROM mestskecast WHERE st_isempty(mestskecast.geom);
SELECT * FROM obj_MPP WHERE st_isempty(obj_MPP.geom);
SELECT * FROM pesitrasy WHERE st_isempty(pesitrasy.geom);
SELECT * FROM verejnawc WHERE st_isempty(verejnawc.geom);
SELECT * FROM verejnstavby WHERE st_isempty(verejnstavby.geom);

/* geometry validity check - for validity definition please see doc */
SELECT * FROM cyklotrasy WHERE st_isvalid(cyklotrasy.geom) IN (NULL, false);
SELECT * FROM mestskecast WHERE st_isvalid(mestskecast.geom) IN (NULL, false);
SELECT * FROM obj_MPP WHERE st_isvalid(obj_MPP.geom) IN (NULL, false);
SELECT * FROM pesitrasy WHERE st_isvalid(pesitrasy.geom) IN (NULL, false);
SELECT * FROM verejnawc WHERE st_isvalid(verejnawc.geom) IN (NULL, false);
SELECT * FROM verejnstavby WHERE st_isvalid(verejnstavby.geom) IN (NULL, false);

/* since pesitrasy and cyklotrasy contains MultiLineStrings we should make them LineString */
CREATE TABLE cyklo_single AS 
SELECT (st_dump(cyklotrasy.geom)).geom, dopr_stav 
FROM cyklotrasy;
ALTER TABLE cyklo_single ADD COLUMN gid serial; /* serial is a notational convenience for creating unique identifier columns
this procedure is necessary, because if we select dumped geometry with gid then gid is no longer unique */
ALTER TABLE cyklo_single ADD PRIMARY KEY (gid);
CREATE INDEX ON cyklo_single USING gist (geom); /* geospatial index */
CREATE TABLE pesi_single AS SELECT (st_dump(pesitrasy.geom)).geom FROM pesitrasy;
ALTER TABLE pesi_single ADD COLUMN gid serial;
ALTER TABLE pesi_single ADD PRIMARY KEY (gid);
CREATE INDEX ON pesi_single USING gist (geom);

/* verify that we have now only single geometry type (except polygon) */
SELECT st_geometrytype(geom) FROM cyklo_single GROUP BY st_geometrytype(geom); -- st_linestring
SELECT st_geometrytype(geom) FROM mestskacast GROUP BY st_geometrytype(geom); -- st_multipolygon
SELECT st_geometrytype(geom) FROM obj_MPP GROUP BY st_geometrytype(geom); -- st_point
SELECT st_geometrytype(geom) FROM pesi_single GROUP BY st_geometrytype(geom); -- st_linestring
SELECT st_geometrytype(geom) FROM verejnawc GROUP BY st_geometrytype(geom); -- st_point
SELECT st_geometrytype(geom) FROM verejnstavby GROUP BY st_geometrytype(geom); -- st_point

/* to build network topology, we need to have sources and targets */
ALTER TABLE cyklo_single ADD COLUMN source INTEGER;
ALTER TABLE pesi_single ADD COLUMN source INTEGER;
ALTER TABLE cyklo_single ADD COLUMN target INTEGER;
ALTER TABLE pesi_single ADD COLUMN target INTEGER;

/*  build a network topology based on the geometry information - input table will be affected/changed */
SELECT pgr_createTopology('cyklo_single','0.01','geom','gid');
SELECT pgr_createTopology('pesi_single','0.01','geom','gid');

/* now sources and targets are filled with values so we create index on them */
CREATE INDEX ON cyklo_single (source);
CREATE INDEX ON cyklo_single (target);
CREATE INDEX ON pesi_single (source);
CREATE INDEX ON pesi_single (target);

/* add cost */
ALTER TABLE cyklo_single ADD COLUMN cost double precision;
UPDATE cyklo_single SET cost = ST_length(geom);
ALTER TABLE pesi_single ADD COLUMN cost double precision;
UPDATE pesi_single SET cost = ST_length(geom);

/* analyze the network topology */
SELECT pgr_analyzeGraph('cyklo_single','0.01','geom','gid');
SELECT pgr_analyzeGraph('pesi_single','0.01','geom','gid');

/* geom of public toalet in Petrovice and in Prague 17 */
CREATE TABLE wc_petr AS 
SELECT A.* FROM verejnawc AS A 
JOIN mestskecast AS B 
ON st_within(A.geom,B.geom) 
WHERE B.nazev LIKE 'Praha-Petrovice';

CREATE TABLE wc_17 AS 
SELECT A.* FROM verejnawc AS A 
JOIN mestskecast AS B ON 
st_within(A.geom,B.geom) 
WHERE B.nazev LIKE 'Praha 17' LIMIT 1;
/* $$$$$$$$$$$ END OF Prepartion $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ */

/* $$$$$$$$$$$ START OF Evaluation $$$$$$$$$$$$$$$$$$$$$$$$$$$$ */
/* cyklotrasy */
/* číslo nejbližší linie */
CREATE TABLE cyclo_wc_petr_lin AS 
SELECT netw.gid FROM 
cyklo_single AS netw, wc_petr AS pnt 
WHERE ST_distance(netw.geom, pnt.geom) = (
SELECT min(st_distance(netw.geom, pnt.geom)) 
FROM cyklo_single AS netw, wc_petr AS pnt);

CREATE TABLE cyclo_wc_17_lin AS 
SELECT netw.gid FROM 
cyklo_single AS netw, wc_17 AS pnt 
WHERE ST_distance(netw.geom, pnt.geom) = (
SELECT min(st_distance(netw.geom, pnt.geom)) 
FROM cyklo_single AS netw, wc_17 AS pnt);

/* Tabulky bodů liní */
CREATE TABLE cyclo_wc_petr_xy AS 
SELECT num AS id, ST_X(ST_PointN(geom,num)) AS x, ST_Y(ST_PointN(geom, num)) AS y 
FROM cyklo_single, (
SELECT generate_series(1, (
SELECT ST_NumPoints(geom) 
FROM cyklo_single, cyclo_wc_petr_lin 
WHERE cyklo_single.gid = cyclo_wc_petr_lin.gid)) AS num) AS series, cyclo_wc_petr_lin 
WHERE cyklo_single.gid = cyclo_wc_petr_lin.gid;

CREATE TABLE cyclo_wc_17_xy AS 
SELECT num AS id, ST_X(ST_PointN(geom,num)) AS x, ST_Y(ST_PointN(geom, num)) AS y 
FROM cyklo_single, (
SELECT generate_series(1, (
SELECT ST_NumPoints(geom) 
FROM cyklo_single, cyclo_wc_17_lin 
WHERE cyklo_single.gid = cyclo_wc_17_lin.gid)) AS num) AS series, cyclo_wc_17_lin 
WHERE cyklo_single.gid = cyclo_wc_17_lin.gid;

/* Nejbližší z tabulky bodů liní k našemu bodu */
CREATE TABLE cyclo_start_pnt AS 
SELECT ST_SetSRID(ST_Point(nodes.x,nodes.y),ST_SRID(pnt.geom)) AS geom 
FROM cyclo_wc_petr_xy AS nodes, wc_petr AS pnt 
WHERE nodes.id = (
SELECT nodes.id 
FROM cyclo_wc_petr_xy AS nodes, wc_petr AS pnt 
WHERE ST_distance(ST_SetSRID(ST_Point(nodes.x, nodes.y),ST_SRID(pnt.geom)),pnt.geom) = (
SELECT min(ST_distance(ST_SetSRID(ST_Point(nodes.x, nodes.y),ST_SRID(pnt.geom)),pnt.geom)) 
FROM cyclo_wc_petr_xy AS nodes, wc_petr AS pnt));

CREATE TABLE cyclo_end_pnt AS 
SELECT ST_SetSRID(ST_Point(nodes.x,nodes.y),ST_SRID(pnt.geom)) AS geom 
FROM cyclo_wc_17_xy AS nodes, wc_17 AS pnt 
WHERE nodes.id = (
SELECT nodes.id 
FROM cyclo_wc_17_xy AS nodes, wc_17 AS pnt 
WHERE ST_distance(ST_SetSRID(ST_Point(nodes.x, nodes.y),ST_SRID(pnt.geom)),pnt.geom) = (
SELECT min(ST_distance(ST_SetSRID(ST_Point(nodes.x, nodes.y),ST_SRID(pnt.geom)),pnt.geom)) 
FROM cyclo_wc_17_xy AS nodes, wc_17 AS pnt));

/* Nejbližší z tabulky vrcholů k našemu bodu na linii */
CREATE TABLE cyclo_start_vert AS 
SELECT vertex.id 
FROM cyklo_single_vertices_pgr AS vertex, cyclo_start_pnt AS pnt 
WHERE ST_distance(vertex.the_geom,pnt.geom) = (
SELECT min(ST_distance(vertex.the_geom,pnt.geom)) 
FROM cyklo_single_vertices_pgr AS vertex, cyclo_start_pnt AS pnt);

CREATE TABLE cyclo_end_vert AS 
SELECT vertex.id 
FROM cyklo_single_vertices_pgr AS vertex, cyclo_end_pnt AS pnt 
WHERE ST_distance(vertex.the_geom,pnt.geom) = (
SELECT min(ST_distance(vertex.the_geom,pnt.geom)) 
FROM cyklo_single_vertices_pgr AS vertex, cyclo_end_pnt AS pnt);

/* konecne nejkratsi cesta :-D */
/* snapshot */
/* to sice funguje (proběhne) ale nic nevrací (0 row), zkusíme to s pěšíma */
CREATE TABLE cyclo_shortest AS SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('SELECT gid AS id, source, target, cost FROM cyklo_single',( SELECT cyclo_start_vert.id FROM cyclo_start_vert),( SELECT cyclo_end_vert.id FROM cyclo_end_vert),false,false);

/* pesitrasy */
/* číslo nejbližší linie */
CREATE TABLE pesi_wc_petr_lin AS 
SELECT netw.gid FROM 
pesi_single AS netw, wc_petr AS pnt 
WHERE ST_distance(netw.geom, pnt.geom) = (
SELECT min(st_distance(netw.geom, pnt.geom)) 
FROM pesi_single AS netw, wc_petr AS pnt);

CREATE TABLE pesi_wc_17_lin AS 
SELECT netw.gid FROM 
pesi_single AS netw, wc_17 AS pnt 
WHERE ST_distance(netw.geom, pnt.geom) = (
SELECT min(st_distance(netw.geom, pnt.geom)) 
FROM pesi_single AS netw, wc_17 AS pnt);

/* Tabulky bodů liní */
CREATE TABLE pesi_wc_petr_xy AS 
SELECT num AS id, ST_X(ST_PointN(geom,num)) AS x, ST_Y(ST_PointN(geom, num)) AS y 
FROM pesi_single, (
SELECT generate_series(1, (
SELECT ST_NumPoints(geom) 
FROM pesi_single, pesi_wc_petr_lin 
WHERE pesi_single.gid = pesi_wc_petr_lin.gid)) AS num) AS series, pesi_wc_petr_lin 
WHERE pesi_single.gid = pesi_wc_petr_lin.gid;

CREATE TABLE pesi_wc_17_xy AS 
SELECT num AS id, ST_X(ST_PointN(geom,num)) AS x, ST_Y(ST_PointN(geom, num)) AS y 
FROM pesi_single, (
SELECT generate_series(1, (
SELECT ST_NumPoints(geom) 
FROM pesi_single, pesi_wc_17_lin 
WHERE pesi_single.gid = pesi_wc_17_lin.gid)) AS num) AS series, pesi_wc_17_lin 
WHERE pesi_single.gid = pesi_wc_17_lin.gid;

/* Nejbližší z tabulky bodů liní k našemu bodu */
CREATE TABLE pesi_start_pnt AS 
SELECT ST_SetSRID(ST_Point(nodes.x,nodes.y),ST_SRID(pnt.geom)) AS geom 
FROM pesi_wc_petr_xy AS nodes, wc_petr AS pnt 
WHERE nodes.id = (
SELECT nodes.id 
FROM pesi_wc_petr_xy AS nodes, wc_petr AS pnt 
WHERE ST_distance(ST_SetSRID(ST_Point(nodes.x, nodes.y),ST_SRID(pnt.geom)),pnt.geom) = (
SELECT min(ST_distance(ST_SetSRID(ST_Point(nodes.x, nodes.y),ST_SRID(pnt.geom)),pnt.geom)) 
FROM pesi_wc_petr_xy AS nodes, wc_petr AS pnt));

CREATE TABLE pesi_end_pnt AS 
SELECT ST_SetSRID(ST_Point(nodes.x,nodes.y),ST_SRID(pnt.geom)) AS geom 
FROM pesi_wc_17_xy AS nodes, wc_17 AS pnt 
WHERE nodes.id = (
SELECT nodes.id 
FROM pesi_wc_17_xy AS nodes, wc_17 AS pnt 
WHERE ST_distance(ST_SetSRID(ST_Point(nodes.x, nodes.y),ST_SRID(pnt.geom)),pnt.geom) = (
SELECT min(ST_distance(ST_SetSRID(ST_Point(nodes.x, nodes.y),ST_SRID(pnt.geom)),pnt.geom)) 
FROM pesi_wc_17_xy AS nodes, wc_17 AS pnt));

/* Nejbližší z tabulky vrcholů k našemu bodu na linii */
CREATE TABLE pesi_start_vert AS 
SELECT vertex.id 
FROM pesi_single_vertices_pgr AS vertex, pesi_start_pnt AS pnt 
WHERE ST_distance(vertex.the_geom,pnt.geom) = (
SELECT min(ST_distance(vertex.the_geom,pnt.geom)) 
FROM pesi_single_vertices_pgr AS vertex, pesi_start_pnt AS pnt);

CREATE TABLE pesi_end_vert AS 
SELECT vertex.id 
FROM pesi_single_vertices_pgr AS vertex, pesi_end_pnt AS pnt 
WHERE ST_distance(vertex.the_geom,pnt.geom) = (
SELECT min(ST_distance(vertex.the_geom,pnt.geom)) 
FROM pesi_single_vertices_pgr AS vertex, pesi_end_pnt AS pnt);

/* 1.a Nalezněte nejkratší cestu od záchodku v Praze Petrovicích k záchodku na Praze 17 pomocí Dikstrova algoritmu */
CREATE TABLE pesi_dijkstra AS 
SELECT seq, id1 AS node, id2 AS edge, cost 
FROM pgr_dijkstra('SELECT gid AS id, source, target, cost FROM pesi_single',( 
SELECT pesi_start_vert.id FROM pesi_start_vert),( 
SELECT pesi_end_vert.id FROM pesi_end_vert),false,false);

/* 1.b Jaká je délka této nejkratší cesty? */
SELECT sum(cost) FROM pesi_dijkstra;

/* 1.c Sestavte geometrii této nejkratší cesty (LineString vrstvu obsahující pouze nalezenou cestu) */
CREATE TABLE pesi_dijkstra_geom AS 
SELECT A.* FROM pesi_single AS A 
JOIN pesi_dijkstra AS B 
ON A.gid = B.edge;

/* 1.d Přes které městské části vede nalezená nejkratší cesta? */
CREATE TABLE pesi_dijkstra_casti AS 
SELECT DISTINCT(A.gid),A.nazev,A.geom 
FROM mestskecast AS A JOIN pesi_dijkstra_geom AS B 
ON ( ST_Intersects(A.geom,B.geom) OR ST_Within(B.geom,A.geom));

/* 2.a Nalezněte tu samou nejkratší cestu s použitím algoritmu A* */
/* Přidáme sloupce */
ALTER TABLE pesi_single ADD COLUMN x1 double precision;
ALTER TABLE pesi_single ADD COlUMN y1 double precision;
ALTER TABLE pesi_single ADD COlUMN x2 double precision;
ALTER TABLE pesi_single ADD COLUMN y2 double precision;

UPDATE pesi_single SET x1 = ST_X(ST_PointN(geom,1));
UPDATE pesi_single SET y1 = ST_Y(ST_PointN(geom,1));
UPDATE pesi_single SET x2 = ST_X(ST_PointN(geom, ST_NumPoints(geom)));
UPDATE pesi_single SET y2 = ST_Y(ST_PointN(geom, ST_NumPoints(geom)));

/* Samotné volání funkce, pro správné fungování je potřeba přetypovat výsledek dotazů na source a target */
CREATE TABLE pesi_astar AS 
SELECT seq, id1 AS node, id2 AS edge, cost 
FROM pgr_astar('SELECT gid AS id, source, target, cost, x1, y1, x2, y2 FROM pesi_single',( 
SELECT pesi_start_vert.id FROM pesi_start_vert)::integer,( 
SELECT pesi_end_vert.id FROM pesi_end_vert)::integer,false,false);

/* 2.b Jaká je délka takto nalezené cesty? */
SELECT sum(cost) FROM pesi_astar;

/* 2.c Je nalezená trasa pomocí algoritmu A* stejná jako trasa nalezená pomocí Dijkstrova algorimtu? */
SELECT A.gid FROM pesi_dijkstra_geom AS A 
JOIN pesi_astar_geom AS B 
ON ST_Equals(A.geom,B.geom) 
WHERE ST_Equals(A.geom,B.geom) = false;

/* 3.a Studenti pořádají závody. Každý student si vybere jeden z pražských záchodků jako své startovní místo. Studentů je o jednoho méně než záchodků a tak ten který zůstane se stane cílovým místem. Který ze studentů má největší šanci na výhru v závodě, vzhledem ke vzdálenosti startovního a cílového místa? (určete pomocí Dijkstrova algoritmu ve verzi Many to One - pgr_kDijkstra) */
/* Tenhle dotaz vybere rovnou geometrii nejbližšího vertexu */
CREATE TABLE target_toal_vert AS
SELECT DISTINCT ON(A.gid) A.gid AS wcgid, B.id AS vertgid 
FROM (
SELECT * FROM verejnawc 
ORDER BY random() LIMIT 1
) AS A, pesi_single_vertices_pgr AS B 
ORDER BY A.gid, ST_Distance(A.geom,B.the_geom);

/* Ke všem záchodkům najdeme nejbližší bod sítě (řekněme nástupní bod pěší trasy které se musí závodníci držet) */
CREATE TABLE wc_nearest AS
SELECT DISTINCT ON(A.gid) A.gid AS wcgid ,B.id AS vertgid 
FROM verejnawc AS A, pesi_single_vertices_pgr AS B, target_toal_vert AS C
WHERE A.gid <> C.wcgid
ORDER BY A.gid, ST_Distance(A.geom,B.the_geom);

/* Převedeme na array */
CREATE TABLE array_wc_nearest AS
SELECT ARRAY(
SELECT wc_nearest.vertgid FROM wc_nearest) AS vertArray;

/* Konečně vlastní vyhodnocení nejkratších cest */
CREATE TABLE pesi_dijkstra_many_cost AS
SELECT * 
FROM pgr_kDijkstraCost('SELECT gid AS id, source, target, cost FROM pesi_single',(
SELECT target_toal_vert.vertgid FROM target_toal_vert)::integer,(
SELECT array_wc_nearest.vertArray FROM array_wc_nearest)::integer[],false,false);

/* Dotaz na bod ze kterého je to nejblíže */
CREATE TABLE nearest_start AS
SELECT seq FROM pesi_dijkstra_many_cost 
WHERE cost <> -1 AND cost <> 0 
ORDER BY cost ASC LIMIT 1;


/* Sestavení geometrie nejkratší cesty jako MultiLineString */
CREATE TABLE nearest_start_path AS
SELECT id1 AS path, ST_AsText(ST_LineMerge(ST_Union(b.geom))) AS geom 
FROM pgr_kDIjkstraPath('SELECT gid AS id, source, target, cost FROM pesi_single',(
SELECT target_toal_vert.vertgid FROM target_toal_vert)::integer,(
SELECT array_wc_nearest.vertArray FROM array_wc_nearest)::integer[],false,false) AS a, pesi_single AS b, nearest_start AS c
WHERE a.id3 = b.gid AND a.seq = c.seq 
GROUP BY id1 
ORDER BY id1;

/* Příprava na úlohu 4 */
/* Rozdělíme rychlosti obecně 3 (normální jízda); pro 6-stezka pro chodce a cyklisty společná a 9-přejezd přes křižovatku nebo silnici, 16-cyklotrasa na komunikaci s dopravním stresem 2 (pomalejší jízda); pro 15-cyklisto veď kolo, 19-Přívoz, 20-Chodník 1 (jde pěšky nebo se veze na přívozu) */
/* 4.a Na Praze 15 jsou právě dve policejní stanice. Policisté potřebují mezi stanicemi převážet důležité dokumenty a v zájmu zdraví policistů se pohybují po cyklostezkác */
ALTER TABLE cyklo_single ADD COLUMN rychlost integer;
UPDATE cyklo_single SET rychlost = 3 WHERE dopr_stav NOT IN (6,9,16,15,19,20);
UPDATE cyklo_single SET rychlost = 2 WHERE dopr_stav IN (6,9,16);
UPDATE cyklo_single SET rychlost = 1 WHERE dopr_stav IN (15,19,20);

ALTER TABLE cyklo_single ADD COLUMN cost2 double precision;
UPDATE cyklo_single SET cost2 = (ST_length(geom)/rychlost)::float;

/* Výběr policejních stanic na Praze 15 */
CREATE TABLE mpp_p15 AS 
SELECT * FROM obj_mpp 
WHERE ST_Contains((
SELECT geom FROM mestskecast 
WHERE nazev LIKE 'Praha 15'),obj_mpp.geom) = true;

/* Nalezení k nim nejbližších vertexů */
CREATE TABLE mpp_nearest AS
SELECT DISTINCT ON(A.gid) A.gid AS mppgid, B.id AS vertgid 
FROM mpp_15 AS A, cyklo_single_vertices_pgr AS B
ORDER BY A.gid, ST_Distance(A.geom,B.the_geom);

/* Nalezení nejkratší cesty */
CREATE TABLE mpp_dijkstra2 AS 
SELECT seq, id1 AS node, id2 AS edge, cost 
FROM pgr_dijkstra('SELECT gid AS id, source, target, cost2 AS cost FROM cyklo_single',( 
SELECT vertgid FROM mpp_nearest LIMIT 1),( 
SELECT vertgid FROM mpp_nearest LIMIT 1 OFFSET 1),false,false);

/* 5.a Šerif Wiggum z Prahy 21 se rozhodl že pojede navštívit své kolegy v ostatních městských částech. Ví ovšem, že nikdy na kole neujel více než 12 km, a tak ho zajímá ke kterým policejním stanicím má šanci dojet. Úlohu řešte použitím funkce driving_distance. */
/* Policejní stanice na Praze 21 */
CREATE TABLE mpp_p21 AS 
SELECT * FROM obj_mpp 
WHERE ST_Contains((
SELECT geom FROM mestskecast 
WHERE nazev LIKE 'Praha 21'),obj_mpp.geom) = true;

/* Nejbližší vertexy grafu pro každou policejní stanici */
CREATE TABLE mpp_nearest_all AS
SELECT DISTINCT ON(A.gid) A.gid AS mppgid ,B.id AS vertgid 
FROM obj_mpp AS A, cyklo_single_vertices_pgr AS B
ORDER BY A.gid, ST_Distance(A.geom,B.the_geom);

/* Nalezení čísel vertexů které jsou v dané dojezdové vzdálenosti */
CREATE TABLE mpp_driving_res AS
SELECT * FROM pgr_drivingDistance('
SELECT gid AS id, source, target, cost FROM cyklo_single',(
SELECT vertgid FROM mpp_nearest_all AS A, mpp_p21 AS B 
WHERE A.mppgid = B.gid), 12000, false, false);

/* Vytvoření vrstvy geometrie s vertexy v dojezdové vzdálenosti */
CREATE TABLE mpp_driving_geom AS
SELECT A.* FROM cyklo_single_vertices_pgr AS A
JOIN mpp_driving_res AS B 
ON B.id1 = A.id;

/* Vytvoření vrstvy se stanicemi ke kterým se dostane */
CREATE TABLE mpp_driving_stat AS
SELECT A.* FROM obj_mpp AS A 
JOIN mpp_nearest_all AS B
ON A.gid = B.mppgid
JOIN mpp_driving_res AS C 
ON B.vertgid = C.id1;

/* Které městské části při tom může navštívit? */
CREATE TABLE mpp_driving_part AS
SELECT DISTINCT(A.nazev), A.geom FROM mestskecast AS A, mpp_driving_stat AS B
WHERE ST_Contains(A.geom,B.geom);



