111 lines
3.3 KiB
Bash
Executable File
111 lines
3.3 KiB
Bash
Executable File
#!/bin/bash
|
|
# Local Database Preparation & Merge Script
|
|
# To be run on the Mac terminal after download_server_data.sh
|
|
|
|
set -e
|
|
|
|
echo "🚀 Starting local database merge and preparation..."
|
|
|
|
# 1. Start the DB container
|
|
echo "📦 Starting PostGIS container..."
|
|
docker compose up -d db
|
|
|
|
# 2. Wait for DB to be ready
|
|
echo "⏳ Waiting for database to be ready..."
|
|
until docker compose exec -T db pg_isready -U mapuser -d mapdb; do
|
|
sleep 2
|
|
done
|
|
|
|
# 3. Initialize Schema & Import Existing Server Data
|
|
echo "🛠️ Initializing schema and loading server data..."
|
|
docker compose exec -T db psql -U mapuser -d mapdb -c "
|
|
DROP TABLE IF EXISTS places_syria;
|
|
CREATE TABLE places_syria (
|
|
id SERIAL PRIMARY KEY,
|
|
latitude DECIMAL(10, 8),
|
|
longitude DECIMAL(11, 8),
|
|
name TEXT,
|
|
name_ar TEXT,
|
|
name_en TEXT,
|
|
address TEXT,
|
|
category TEXT,
|
|
neighbourhood TEXT,
|
|
city TEXT,
|
|
description TEXT,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
source TEXT,
|
|
location GEOMETRY(Point, 4326)
|
|
);
|
|
|
|
CREATE OR REPLACE FUNCTION sync_place_location() RETURNS trigger AS \$\$
|
|
BEGIN
|
|
IF NEW.latitude IS NOT NULL AND NEW.longitude IS NOT NULL THEN
|
|
NEW.location := ST_SetSRID(ST_MakePoint(CAST(NEW.longitude AS FLOAT), CAST(NEW.latitude AS FLOAT)), 4326);
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
\$\$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS trg_sync_place_location ON places_syria;
|
|
CREATE TRIGGER trg_sync_place_location
|
|
BEFORE INSERT OR UPDATE ON places_syria
|
|
FOR EACH ROW EXECUTE FUNCTION sync_place_location();
|
|
"
|
|
|
|
# Load existing data if available
|
|
if [ -f "infrastructure/docker/postgis/existing_syria_data.sql" ]; then
|
|
echo "📥 Loading existing server data..."
|
|
docker compose exec -T db psql -U mapuser -d mapdb < infrastructure/docker/postgis/existing_syria_data.sql
|
|
fi
|
|
|
|
# 4. Import New CSV Data
|
|
echo "📥 Importing new CSV landmark data..."
|
|
docker cp infrastructure/docker/postgis/syria_final_complete.csv map-db:/tmp/syria_data.csv
|
|
|
|
docker compose exec -T db psql -U mapuser -d mapdb -c "
|
|
DROP TABLE IF EXISTS staging_syria;
|
|
CREATE TABLE staging_syria (
|
|
name TEXT,
|
|
latitude DECIMAL(10, 8),
|
|
longitude DECIMAL(11, 8),
|
|
category TEXT,
|
|
address TEXT,
|
|
description TEXT,
|
|
created_at TIMESTAMP
|
|
);
|
|
|
|
-- HEADER true fixes the 'invalid input syntax' error
|
|
COPY staging_syria(name, latitude, longitude, category, address, description, created_at)
|
|
FROM '/tmp/syria_data.csv'
|
|
WITH (FORMAT csv, HEADER true, QUOTE '\"', ENCODING 'UTF8');
|
|
|
|
-- Merge into places_syria while avoiding duplicates
|
|
-- We check for name + spatial proximity (approx 50m)
|
|
INSERT INTO places_syria (name, name_ar, latitude, longitude, category, address, description, source, created_at)
|
|
SELECT
|
|
name,
|
|
name,
|
|
latitude,
|
|
longitude,
|
|
category,
|
|
address,
|
|
description,
|
|
'csv_import_2026_04',
|
|
COALESCE(created_at, NOW())
|
|
FROM staging_syria s
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM places_syria p
|
|
WHERE (p.name = s.name OR p.name_ar = s.name)
|
|
AND ST_DWithin(
|
|
ST_SetSRID(ST_MakePoint(CAST(p.longitude AS FLOAT), CAST(p.latitude AS FLOAT)), 4326)::geography,
|
|
ST_SetSRID(ST_MakePoint(CAST(s.longitude AS FLOAT), CAST(s.latitude AS FLOAT)), 4326)::geography,
|
|
50
|
|
)
|
|
);
|
|
|
|
DROP TABLE staging_syria;
|
|
"
|
|
|
|
echo "✅ Local database merge complete."
|
|
echo "Run ./infrastructure/scripts/local_verify.sh to review the results."
|