Obsidian/Doktoritöö/001_artiklid/KT liikumised/SQL päringud.md

Elukohtade aaderssid

-- vallad admin_level = 7, külad admin_level = 9
--  ap.admin_level = 7 AND ap.name like 'Kehtna%'
DROP TABLE IF EXISTS region CASCADE;
CREATE TABLE region AS
select ap.osm_id, ap."name", ap.geom FROM osm_estonia.administrative_polygon ap WHERE ap.admin_level = 9 AND ap.name like 'Kaigutsi%';

Kohalike teede võrkude genereerimine

Nt. kopeerime lähtefaili skeemist osm_kehtna_vald ja tabelist links.

-- 1. Loome ajutise tabeli ainult kohalike teedega (v.a. 'tertiary' ja 'secondary')
DROP TABLE IF EXISTS local_links;
CREATE TEMP TABLE local_links AS
SELECT id, osm_type, geom
FROM osm_kehtna_vald.links
WHERE osm_type NOT IN ('primary', 'primary_link', 'secondary', 'secondary_link', 'tertiary', 'tertiary_link', 'trunk', 'trunk_link');

-- 2. Loome graafi tippude põhjal (algus- ja lõpp-punktid)
DROP TABLE IF EXISTS local_link_nodes;
CREATE TEMP TABLE local_link_nodes AS
SELECT id, ST_StartPoint(geom) AS pt
FROM local_links
UNION ALL
SELECT id, ST_EndPoint(geom) AS pt
FROM local_links;

-- 3. Loome tippudele unikaalsed ID-d
DROP TABLE IF EXISTS local_nodes;
CREATE TEMP TABLE local_nodes AS
SELECT row_number() OVER () AS node_id, pt
FROM (SELECT DISTINCT pt FROM local_link_nodes) AS unique_pts;

-- 4. Loome servade tabeli koos lähte- ja siht-tippude ID-dega
DROP TABLE IF EXISTS local_edges;
CREATE TEMP TABLE local_edges AS
SELECT l.id, l.osm_type, l.geom, n1.node_id AS source, n2.node_id AS target
FROM local_links l
JOIN local_nodes n1 ON ST_DWithin(ST_StartPoint(l.geom), n1.pt, 0.001)
JOIN local_nodes n2 ON ST_DWithin(ST_EndPoint(l.geom), n2.pt, 0.001);

-- 5. Loome graafi ja leiame ühendatud komponendid (GID)
DROP TABLE IF EXISTS node_components;
CREATE TEMP TABLE node_components AS
SELECT * FROM pgr_connectedComponents('SELECT id, source, target, 1 AS cost, 1 AS reverse_cost FROM local_edges');

-- 6. Määrame igale servale GID, kui selle mõlemad tipud kuuluvad samasse komponendisse
DROP TABLE IF EXISTS links_with_gid;
CREATE TABLE links_with_gid AS
SELECT e.id, e.osm_type, e.geom, c1.component AS gid
FROM local_edges e
JOIN node_components c1 ON e.source = c1.node
JOIN node_components c2 ON e.target = c2.node
WHERE c1.component = c2.component;

-- 7. Loome väljapääsupunktide leidmiseks kõik teed (sh 'tertiary' ja 'secondary')
DROP TABLE IF EXISTS all_link_nodes;
CREATE TEMP TABLE all_link_nodes AS
SELECT id,osm_type,ST_StartPoint(geom) AS pt
FROM osm_kehtna_vald.links
UNION ALL
SELECT id,osm_type,ST_EndPoint(geom) AS pt
FROM osm_kehtna_vald.links;

-- 8. Loome väljapääsupunktide tabeli: punktid, kus kohalikud teed puutuvad kokku 'tertiary' või 'secondary' teedega
DROP TABLE IF EXISTS exit_points_with_gid;
CREATE TABLE exit_points_with_gid AS
SELECT DISTINCT ON (ST_AsText(n.pt), l.gid) ST_X(n.pt) AS lon, ST_Y(n.pt) AS lat, l.gid, n.pt AS geom
FROM local_nodes n
JOIN links_with_gid l
  ON ST_DWithin(n.pt, ST_StartPoint(l.geom), 0.001)
  OR ST_DWithin(n.pt, ST_EndPoint(l.geom), 0.001)
JOIN all_link_nodes a
  ON ST_DWithin(n.pt, a.pt, 0.001)
WHERE a.osm_type IN ('tertiary', 'secondary');

Tippude ja servade tabelid

-- 1. Loome tippude tabeli 'nodes' unikaalsete punktidega
DROP TABLE IF EXISTS nodes CASCADE;
CREATE TABLE nodes AS
SELECT row_number() OVER () AS node_id, pt, gid
FROM (SELECT DISTINCT ST_StartPoint(geom) AS pt, gid FROM links_with_gid
    UNION
    SELECT DISTINCT ST_EndPoint(geom), gid FROM links_with_gid
) AS points;

-- 2. Loome servade tabeli 'edges' koos viidetega tippudele ja GID-ga
DROP TABLE IF EXISTS edges CASCADE;
CREATE TABLE edges AS
SELECT l.id, n1.node_id AS source, n2.node_id AS target, ST_Length(l.geom) AS cost, l.gid, l.geom
FROM links_with_gid l
JOIN nodes n1 ON ST_DWithin(ST_StartPoint(l.geom), n1.pt, 0.001)
JOIN nodes n2 ON ST_DWithin(ST_EndPoint(l.geom), n2.pt, 0.001);

-- 3. Lisame ruumiindeksid
CREATE INDEX idx_nodes_geom ON nodes USING GIST (pt);
CREATE INDEX idx_edges_geom ON edges USING GIST (geom);

Elukohtade aadressid