01_load_mimu_data.sh

#!/bin/bash
DOWNLOADS=~projectbank/mimu
PROJECT_DIR=/var/www/projectbank/source/projectbank

SCHEMA="mimu"

cd ${DOWNLOADS}
for file in *.zip
do
    unzip -o ${file}
    shp2pgsql ${file/.zip/.shp} ${SCHEMA}.${file/.zip} > ${file/.zip/.sql}
    rm ${file/.zip/.prj}
    rm ${file/.zip/.cst}
    rm ${file/.zip/.shp}
    rm wfsrequest.txt
    rm ${file/.zip/.shx}
    rm ${file/.zip/.dbf}
done

# Then in django directory

cd ${PROJECT_DIR}
# . env/bin/activate
# cd ${APP_DIR}
echo "DROP SCHEMA IF EXISTS ${SCHEMA} CASCADE;" | ./manage.py dbshell
echo "CREATE SCHEMA IF NOT EXISTS ${SCHEMA};" | ./manage.py dbshell

for file in ${DOWNLOADS}/*.sql
do
    cat ${file} | ./manage.py dbshell
done


# After running sql and py
# drop schema mimu;

02_python_update

# Upgrade townships on Projectbank to MIMU version 9.0.2

try:
    seikkan_township = Area.objects.get(code='MMR013045')
    seikkan_township.delete()
except Area.DoesNotExist:
    pass

try:
    hwa_ts = Area.objects.get(code='MMR013008')
except Area.DoesNotExist:
    hwa_ts = None

if hwa_ts:
    hwa_one = Area.objects.get_or_create(parent=hwa_ts.parent, kind_id=3, level=hwa_ts.parent.level+1, code='MMR013046')[0]
    hwa_two = Area.objects.get_or_create(parent=hwa_ts.parent, kind_id=3, level=hwa_ts.parent.level+1, code='MMR013047')[0]

    for location in Location.objects.filter(area = hwa_ts):
        for area in [hwa_one, hwa_two]:
            location.pk = None  # Set pk to None saves a new object
            location.area = area
            location.adm_code = area.id  # This may actually be a bug, the map geoJSON uses this
            location.save()
        
    Location.objects.filter(area = hwa_ts).delete()
    hwa_ts.delete()


# Next run the "upgrade townships" SQL

03_sql_update.sql

-- Updates Projectbank to use newer data from MIMU 


-- Our simple_locations should use correct pcodes

-- The correct code for Bago is 'MMR111' not 'MMR007' which is Bago (East)
UPDATE simple_locations_area SET code = 'MMR111' WHERE code = 'MMR007';

-- The correct code for Shan is 'MMR222' not 'MMR014 which is one of 3 sub-areas 
UPDATE simple_locations_area SET code = 'MMR222' WHERE code = 'MMR014';


UPDATE simple_locations_area
    SET geom = ST_SETSRID(mimu_states.geom,4326)
    FROM mimu.mmr_polbnda_adm1_250k_mimu mimu_states WHERE
        simple_locations_area.code = mimu_states.st_pcode
        AND NOT ST_EQUALS(simple_locations_area.geom, ST_SETSRID(mimu_states.geom, 4326));


-- Set township geoms
UPDATE simple_locations_area
    SET geom = ST_SETSRID(ts.geom,4326)
    FROM mimu.mmr_polbnda_adm3_mimu_250k ts WHERE
        simple_locations_area.code = ts.ts_pcode;

-- Set name and translated MM name for ts

UPDATE simple_locations_area
    SET name = CONCAT(ts.ts, ' Township')
    FROM mimu.mmr_polbnda_adm3_mimu_250k ts WHERE
        simple_locations_area.code = ts.ts_pcode;

UPDATE simple_locations_area
    SET name_en = CONCAT(ts.ts, ' Township')
    FROM mimu.mmr_polbnda_adm3_mimu_250k ts WHERE
        simple_locations_area.code = ts.ts_pcode;

UPDATE simple_locations_area
    SET name_my = ts.ts_mmr4
    FROM mimu.mmr_polbnda_adm3_mimu_250k ts WHERE
        simple_locations_area.code = ts.ts_pcode;

Comparing MIMU geoms.sql

 -- Get the geometry changes between simple_locations states and 

CREATE TABLE mimu.states_geometry_changes AS (
	SELECT
		simple_locations_area.code,
		ST_DIFFERENCE(ST_SETSRID(mimu_states.geom,4326), simple_locations_area.geom)
    FROM 
		mimu.mmr_polbnda_adm1_250k_mimu mimu_states,
		simple_locations_area
	WHERE
        simple_locations_area.code = mimu_states.st_pcode
        AND NOT ST_EQUALS(simple_locations_area.geom, ST_SETSRID(mimu_states.geom, 4326))
)

CREATE TABLE mimu.ts_geometry_changes AS (
	SELECT
		simple_locations_area.code,
		ST_DIFFERENCE(ST_SETSRID(mimu_ts.geom,4326), simple_locations_area.geom)
    FROM 
		mimu.mmr_polbnda_adm3_mimu_250k mimu_ts,
		simple_locations_area
	WHERE
        simple_locations_area.code = mimu_ts.ts_pcode
        AND NOT ST_EQUALS(simple_locations_area.geom, ST_SETSRID(mimu_ts.geom, 4326))
)

ALTER TABLE simple_locations_area ADD COLUMN new_name text;
ALTER TABLE simple_locations_area ADD COLUMN new_name_my text;

UPDATE simple_locations_area
    SET new_name = CONCAT(ts.ts, ' Township')
    FROM mimu.mmr_polbnda_adm3_mimu_250k ts WHERE
        simple_locations_area.code = ts.ts_pcode;

UPDATE simple_locations_area
    SET new_name_my = ts.ts_mmr4
    FROM mimu.mmr_polbnda_adm3_mimu_250k ts WHERE
        simple_locations_area.code = ts.ts_pcode;

UPDATE simple_locations_area
    SET new_name = CONCAT(ts.ts, ' Township')
    FROM mimu.mmr_polbnda_adm3_mimu_250k ts WHERE
        simple_locations_area.code = ts.ts_pcode;

UPDATE simple_locations_area
    SET new_name_my = ts.ts_mmr4
    FROM mimu.mmr_polbnda_adm3_mimu_250k ts WHERE
        simple_locations_area.code = ts.ts_pcode;


SELECT * FROM simple_locations_area WHERE name != new_name;

SELECT code, name, new_name FROM simple_locations_area WHERE name_en != new_name;
SELECT code, name_my, new_name_my FROM simple_locations_area WHERE name_my != new_name_my;

CREATE TABLE mimu.ts_geometry_changes AS (
	SELECT
		simple_locations_area.code,
		ST_DIFFERENCE(ST_SETSRID(mimu_ts.geom,4326), simple_locations_area.geom)
    FROM 
		mimu.mmr_polbnda_adm3_mimu_250k mimu_ts,
		simple_locations_area
	WHERE
        simple_locations_area.code = mimu_ts.ts_pcode
        AND NOT ST_EQUALS(simple_locations_area.geom, ST_SETSRID(mimu_ts.geom, 4326))
)