Obsidian/Doktoritöö/001_artiklid/OSM/shell/create_osm_tables.sh

#!/usr/bin/env bash

# Lae keskkonnamuutujad
source env.sh
# Liigume ajutisse kausta
#cd /tmp

# Laadime alla Eesti OSM andmed ajutisse kausta
#wget https://download.geofabrik.de/europe/estonia-latest.osm.pbf -P /tmp
#wget https://raw.githubusercontent.com/openstreetmap/osm2pgsql/master/default.style -O default.style

# Impordime andmed PostGIS andmebaasi
# pbf failist luuakse lähtetabelid 'osm_point', 'osm_line', 'osm_polygon', 'osm_roads'
# Ülejäänud tabelid luuakse lua failide abil.


# https://github.com/osm2pgsql-dev/osm2pgsql/blob/master/flex-config/bbox.lua
# First use the import-countries.lua to import country boundaries, 
# then use these with the other config files.

PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/import-countries.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

psql ${CONNECTION_NAME} -c "DROP TABLE IF EXISTS ${destination_schema}.osm_pois;" && \
PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/osm_pois.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

psql ${CONNECTION_NAME} -c "DROP TABLE IF EXISTS ${destination_schema}.highways;
DROP TABLE IF EXISTS ${destination_schema}.routes;" && \
PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/highways.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

psql ${CONNECTION_NAME} -c "DROP TABLE IF EXISTS ${destination_schema}.buildings;" && \
PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/buildings.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

psql ${CONNECTION_NAME} -c "DROP TABLE IF EXISTS ${destination_schema}.administrative;" && \
PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/administrative.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

# Kaalude lisamine administratiiv polügoonidele  
psql ${CONNECTION_NAME} -c "
ALTER TABLE ${destination_schema}.administrative ADD COLUMN weight_poi integer;"
psql ${CONNECTION_NAME} -c "
UPDATE ${destination_schema}.administrative p
SET weight_poi = sub.poi_count
FROM (
  SELECT
    a.relation_id,
    COUNT(poi.*) AS poi_count
  FROM ${destination_schema}.administrative a
  JOIN ${destination_schema}.osm_pois poi
    ON ST_Intersects(a.geom, poi.geom)
  GROUP BY a.relation_id
) AS sub
WHERE p.relation_id = sub.relation_id;"

## ----------------------  GRAAFI LOOMINE  ---------------------------
# a) links loomine
echo "$COUNTIES" | parallel -j4 "county={}; bash batch_create_links.sh"
#./batch_create_links.sh
# maakonna links tabelite ühendamine
./merge_links.sh
# testimiseks Valga valla links
#./test_links.sh
# Kontrollime ridade arvusid tabelites link_*
./generate_links_table_stats.sh
# b) nodes loomine
#psql ${CONNECTION_NAME} -v schema=${destination_schema} -d ${destination_dbname} -f sql/create_nodes.sql

# c) links: source, target, cost
./update_links_sources_targets_costs.sh
# Täiendav isoleeritud lõikude eemaldamine + reverse_cost lisamine
# d) PGROUTING ettevalmistus
psql ${CONNECTION_NAME} -c "
SELECT pgr_createVerticesTable('${destination_schema}.links', 'geom', 'source', 'target');
SELECT pgr_analyzeGraph('${destination_schema}.links', 0.001, the_geom := 'geom', id := 'id', source := 'source', target := 'target');
"
./update_links_reverse_cost.sh
# Kustuta isoleeritud lõigud
./pgr_prepare_batchwise.sh
# Puhasta "intersection detected" lõigud
./detect_and_split_intersections_batchwise_fixed.sh
psql ${CONNECTION_NAME} -v schema=${destination_schema} -d ${destination_dbname} -f sql/delete_links_covered_by_split.sql
psql ${CONNECTION_NAME} -c "
SELECT pgr_createVerticesTable('${destination_schema}.links', 'geom', 'source', 'target');
SELECT pgr_analyzeGraph('${destination_schema}.links', 0.001, the_geom := 'geom', id := 'id', source := 'source', target := 'target');
"
# -------------- Siiani oleks nagu h½sti ---------------
# NOTICE:                    Isolated segments: 0
# NOTICE:                            Dead ends: 111179
# NOTICE:  Potential gaps found near dead ends: 0
# NOTICE:               Intersections detected: 6
# NOTICE:                      Ring geometries: 0
# Teistkordne probleemsete kohtade leidmine läbi tippude
psql ${CONNECTION_NAME} -v schema=${destination_schema} -d ${destination_dbname} -f sql/detect_unconnected_intersections_batchwise_fixed.sql


# ---------------- Siit on pooleli --------------
# Teistkordne puhastamine
# Järgmine päring andis tühja tulemuse. Uuri. Struktuuri oleks hea kasutada.
#psql ${CONNECTION_NAME} -v schema=${destination_schema} -d ${destination_dbname} -f sql/detect_unconnected_intersections_batchwise.sql
psql ${CONNECTION_NAME} -v schema=${destination_schema} -d ${destination_dbname} -f sql/delete_links_from_intersections_unconnected.sql
# Loome tabeli tippudest kus on "intersection detected" (0 rida)
psql ${CONNECTION_NAME} -v schema=${destination_schema} -d ${destination_dbname} -f sql/export_remaining_intersections_batchwise.sql
# Skripti, mis logib kõik lõigud, mis lõikuvad geomeetriliselt, kuid pole ühendatud — sõltumata geomeetria tüübist.
psql ${CONNECTION_NAME} -v schema=${destination_schema} -d ${destination_dbname} -f sql/log_all_unconnected_intersections.sql
# Kustutamine
psql ${CONNECTION_NAME} -v schema=${destination_schema} -d ${destination_dbname} -f sql/delete_links_by_intersections_all_geom.sql
# Peale eelmist käsku tekkis 2 tk "Potential gaps found near dead ends"
psql ${CONNECTION_NAME} -c "
SELECT pgr_createVerticesTable('${destination_schema}.links', 'geom', 'source', 'target');
SELECT pgr_analyzeGraph('${destination_schema}.links', 0.001, the_geom := 'geom', id := 'id', source := 'source', target := 'target');
"
# Parandame vea "Potential gaps found near dead ends"
psql ${CONNECTION_NAME} -v schema=${destination_schema} -d ${destination_dbname} -f sql/repair_links_nearby_vertices.sql
psql ${CONNECTION_NAME} -c "
SELECT pgr_createVerticesTable('${destination_schema}.links', 'geom', 'source', 'target');
SELECT pgr_analyzeGraph('${destination_schema}.links', 0.001, the_geom := 'geom', id := 'id', source := 'source', target := 'target');
"
psql ${CONNECTION_NAME} -v schema=${destination_schema} -d ${destination_dbname} -f sql/fix_small_gaps.sql
psql ${CONNECTION_NAME} -c "
SELECT pgr_createVerticesTable('${destination_schema}.links', 'geom', 'source', 'target');
SELECT pgr_analyzeGraph('${destination_schema}.links', 0.001, the_geom := 'geom', id := 'id', source := 'source', target := 'target');
"



# Abitabelite kustutamised
psql ${CONNECTION_NAME} -v schema=${destination_schema} -d ${destination_dbname} -f sql/drop_links_tables.sql

# Tag'ide lisamine: isoleeritud ja lõikuvad
#./tag_links_batchwise.sh

# e) paranda graafi linke
psql ${CONNECTION_NAME} -v schema=${destination_schema} -d ${destination_dbname} -f sql/fix_gaps_and_isolated.sql
psql ${CONNECTION_NAME} -v schema=${destination_schema} -d ${destination_dbname} -f sql/repair_graph_components.sql


## -----------------------------------------------------------------------------------