Obsidian/Doktoritöö/001_artiklid/OSM/shell/arhiiv/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/ardok_osm_pois.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf


psql ${CONNECTION_NAME} -c "DROP TABLE IF EXISTS ${destination_schema}.ardok_highways;
DROP TABLE IF EXISTS ${destination_schema}.ardok_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/ardok_highways.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

psql ${CONNECTION_NAME} -c "DROP TABLE IF EXISTS ${destination_schema}.ardok_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/ardok_buildings.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

psql ${CONNECTION_NAME} -c "DROP TABLE IF EXISTS ${destination_schema}.ardok_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/ardok_administrative.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

# Kaalude lisamine administratiiv polügoonidele  
psql ${CONNECTION_NAME} -c "
ALTER TABLE ${destination_schema}.ardok_administrative ADD COLUMN weight_poi integer;"
psql ${CONNECTION_NAME} -c "
UPDATE ${destination_schema}.ardok_administrative p
SET weight_poi = sub.poi_count
FROM (
  SELECT
    a.relation_id,
    COUNT(poi.*) AS poi_count
  FROM ${destination_schema}.ardok_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"
bash merge_links.sh
#psql ${CONNECTION_NAME} -v schema=${destination_schema} -d data -f sql/create_links.sql
# Kontrollime ridade arvusid tabelites link_*
bash generate_links_table_stats.sh

# b) nodes loomine
psql ${CONNECTION_NAME} -v schema=${destination_schema} -d data -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
./update_links_reverse_cost.sh

# 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');
"
# Puhaste isoleeritud lõigud
./pgr_prepare_batchwise.sh
./detect_and_split_intersections_batchwise_fixed.sh
./convert_links_split_to_graph.sh
./finalize_road_graph.sh

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

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


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