treeadvisor/import_geojson.ts

import { Pool } from "https://deno.land/x/postgres/mod.ts";

const GEBIETSTYPES: any = {
    "Gewässer": 1,
    "versickerungsdominiert": 1,
    "verdunstungs- und versickerungsbestimmt": 0.8,
    "verdunstungsdominiert": 0.5,
    "verdunstungs- und abflussbestimmt": 0.2,
    "abflussdominiert": 0.1,
    "ausgewogen": 0.8,
};

const MODE = Deno.args[0];
if (!MODE) {
    throw "No MODE arg";
}

const dbPool = new Pool({
    user: "treeadvisor",
    password: "123",
    database: "treeadvisor",
    
    hostname: "10.233.1.2",
}, 12);
async function withDb(f: any): Promise<any> {
    const client = await dbPool.connect();
    let result;
    try {
        result = await f(client);
    } finally {
        client.release();
    }
    return result;
}

const insertGeoenv = async (src: string, coords: any, attrs: any) => {
    const coords_s = "(" + coords.map((coords: any) => `(${coords[0]},${coords[1]})`).join(",") + ")";
    const attrs_s = JSON.stringify(attrs);
    await withDb(async (db: any) => db.queryObject`INSERT INTO areas (src, coords, attrs) VALUES (${src}, ${coords_s}::polygon, ${attrs_s})`);
};

let pfafCache: any = {};
const insertTree = async (coords: any, props: any) => { await withDb(async (db: any) => {
    const coords_s = `(${coords[0]},${coords[1]})`;
    let botanic = props.art_botanisch
        .replace(/,\s*/g, ", ")
        .replace(/\.\s+/g, ". ")
        .replace(/\s+/g, " ")
        .replace(/'/g, "\"")
        .replace(/\s+$/, "");
    const german = props.art_deutsch && props.art_deutsch
        .replace(/\s+-\s+/, "-")
        .replace(/\.\s+/g, ". ")
        .replace(/\s+x\s+/g, " × ")
        .replace(/\s+/g, " ")
        .replace(/'/g, "\"")
        .replace(/\s+$/, "");

    const age = parseInt(props.jalter, 10);
    let planted = null;
    if (age > 0) {
        const m = props.aend_dat.match(/^(\d+)\.(\d+)\.(\d+) /);
        if (!m) throw `Invalid date: ${props.aend_dat}`;
        const y = parseInt(m[3], 10) - age;
        planted = `${y}-${m[2]}-${m[1]}`;
    };

    let botanic_pfaf;
    if (botanic == "Baumart noch nicht bestimmt" || botanic == "unbekannt" || !botanic) {
        botanic = null;
    } else if (!pfafCache[botanic]) {
        const res: any = await db.queryObject`SELECT "Latin name" as botanic FROM "PlantsForAFuture" WHERE "Latin name" ILIKE CONCAT(${botanic.slice(0, 3)}::text, '%') ORDER BY levenshtein("Latin name", ${botanic}) ASC LIMIT 1`;
        if (res.rows[0]) {
            botanic_pfaf = pfafCache[botanic] = res.rows[0].botanic;
            if (botanic != botanic_pfaf) console.log(botanic + " = " + botanic_pfaf);
        }
    } else {
        botanic_pfaf = pfafCache[botanic];
    }

    let areaSrcSeen: any = {};
    let areaInfo: any = {};
    let res = await db.queryObject`SELECT src, attrs FROM areas WHERE coords @> $1::point ORDER BY coords <-> ${coords_s}::point ASC`;
    res.rows.forEach(function(area: any) {
        if (areaSrcSeen[area.src]) return;

        Object.keys(area.attrs).forEach(function(k) {
            areaInfo[k] = area.attrs[k];
        });
    });
    let scores = [];
    if (areaInfo.hasOwnProperty("versiegelung")) scores.push(1 - areaInfo.versiegelung);
    if (areaInfo.hasOwnProperty("schutt")) scores.push(1 - areaInfo.schutt);
    if (areaInfo.bodenqualitaet) scores.push((areaInfo.bodenqualitaet - 1) / 5);
    if (areaInfo.wasserspeicher && areaInfo.wasserspeicher > 10) scores.push((50 - areaInfo.wasserspeicher) / 40)
    else if (areaInfo.wasserspeicher) scores.push((5 - areaInfo.wasserspeicher) / 4);
    if (areaInfo.gebietstyp) scores.push(GEBIETSTYPES[areaInfo.gebietstyp]);
    let score: number | null = 0;
    for(const s of scores) {
        score += s;
    }
    if (scores.length > 0) {
        score /= scores.length;
        score = Math.max(0, Math.min(1, score));
    } else {
        score = null;
    }

    await db.queryObject`INSERT INTO trees (id, coord, score, botanic, botanic_pfaf, german, planted) VALUES (${props.id}, ${coords_s}, ${score}, ${botanic}, ${botanic_pfaf}, ${german}, ${planted})`;
}) };

const importFeatures = async (src: string, geojson: any) => {
    if (geojson.type != "FeatureCollection") {
        console.warn("Not a FeatureCollection");
        return;
    }

    // var progress = 0, i = 0;
    let promises = [];
    for(const feature of geojson.features) { promises.push((async () => {
        if (MODE == "GEOENV") {
            if (feature.type == "Feature" &&
                feature.geometry.type == "Polygon" &&
                feature.geometry.crs.properties.name == "urn:ogc:def:crs:EPSG::4326") {

                for(const coords of feature.geometry.coordinates) {
                    await insertGeoenv(src, coords, feature.properties);
                }
                // await insertGeoenv(feature.geometry.coordinates, feature.properties);
            } else if (feature.type == "FeatureCollection") {
                await importFeatures(src, feature);
            } else if (feature.type == "Feature" &&
                feature.geometry.type == "MultiPolygon" &&
                feature.geometry.crs.properties.name == "urn:ogc:def:crs:EPSG::4326") {

                for(const coordinates of feature.geometry.coordinates) {
                    for(const coords of coordinates) {
                        await insertGeoenv(src, coords, feature.properties);
                    }
                }
            } else {
                console.warn("Not recognized:", feature);
                // console.warn(`Not recognized: ${JSON.stringify(geojson)}`);
            }
        } else if (MODE == "TREES") {
            if (feature.type == "Feature" &&
                feature.geometry.type == "Point" &&
                feature.geometry.crs.properties.name == "urn:ogc:def:crs:EPSG::4326") {

                await insertTree(feature.geometry.coordinates, feature.properties);
            } else {
                console.warn("Not recognized:", feature);
                // console.warn(`Not recognized: ${JSON.stringify(geojson)}`);
            }
        }

        // i += 1;
        // var newProgress = Math.floor(100 * i / geojson.features.length);
        // if (newProgress != progress) {
        //     progress = newProgress;
        //     console.log(progress + "%");
        // }
    })()); }

    console.log("wait for " + promises.length + " promises");
    await Promise.all(promises);
};

for(const filename of Deno.args.slice(1)) {
    console.log(`Load ${filename}`)
    const geojson = JSON.parse(await Deno.readTextFile(filename));

    await importFeatures(filename, geojson);
}