path: root/scripts/build_db.py

#!/usr/bin/env python
###############################################################################
# $Id$
#
#  Project:  PROJ
#  Purpose:  Build SRS and coordinate transform database
#  Author:   Even Rouault <even.rouault at spatialys.com>
#
###############################################################################
#  Copyright (c) 2018, Even Rouault <even.rouault at spatialys.com>
#
#  Permission is hereby granted, free of charge, to any person obtaining a
#  copy of this software and associated documentation files (the "Software"),
#  to deal in the Software without restriction, including without limitation
#  the rights to use, copy, modify, merge, publish, distribute, sublicense,
#  and/or sell copies of the Software, and to permit persons to whom the
#  Software is furnished to do so, subject to the following conditions:
#
#  The above copyright notice and this permission notice shall be included
#  in all copies or substantial portions of the Software.
#
#  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
#  OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
#  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
#  THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
#  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
#  FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
#  DEALINGS IN THE SOFTWARE.
###############################################################################

import os
import sqlite3

EPSG_AUTHORITY = 'EPSG'

def ingest_sqlite_dump(cursor, filename):
    sql = ''
    for line in open(filename, 'rt').readlines():
        sql += line
        if sqlite3.complete_statement(sql):
            sql = sql.strip()
            if sql != 'COMMIT;':
                cursor.execute(sql)
            sql = ''


def ingest_epsg():

    for f in ['PostgreSQL_Data_Script.sql',  'PostgreSQL_Table_Script.sql']:
        if not os.path.exists(f):
            raise Exception('Missing file: ' + f)

    epsg_tmp_db_filename = 'tmp_epsg.db'

    if os.path.exists(epsg_tmp_db_filename):
        os.unlink(epsg_tmp_db_filename)

    conn = sqlite3.connect(epsg_tmp_db_filename)
    cursor = conn.cursor()
    cursor.execute('PRAGMA journal_mode = OFF;')
    ingest_sqlite_dump(cursor, 'PostgreSQL_Table_Script.sql')
    ingest_sqlite_dump(cursor, 'PostgreSQL_Data_Script.sql')
    cursor.close()
    conn.commit()

    return (conn, epsg_tmp_db_filename)


def fill_unit_of_measure(proj_db_cursor):
    proj_db_cursor.execute(
        "INSERT INTO unit_of_measure SELECT ?, uom_code, unit_of_meas_name, unit_of_meas_type, factor_b / factor_c, deprecated FROM epsg.epsg_unitofmeasure", (EPSG_AUTHORITY,))


def fill_ellipsoid(proj_db_cursor):
    proj_db_cursor.execute(
        "INSERT INTO ellipsoid SELECT ?, ellipsoid_code, ellipsoid_name, NULL, 'PROJ', 'EARTH', semi_major_axis, ?, uom_code, inv_flattening, semi_minor_axis, deprecated FROM epsg.epsg_ellipsoid", (EPSG_AUTHORITY, EPSG_AUTHORITY))


def fill_area(proj_db_cursor):
    proj_db_cursor.execute(
        "INSERT INTO area SELECT ?, area_code, area_name, area_of_use, area_south_bound_lat, area_north_bound_lat, area_west_bound_lon, area_east_bound_lon, deprecated FROM epsg.epsg_area", (EPSG_AUTHORITY,))


def fill_prime_meridian(proj_db_cursor):
    proj_db_cursor.execute(
        "INSERT INTO prime_meridian SELECT ?, prime_meridian_code, prime_meridian_name, greenwich_longitude, ?, uom_code, deprecated FROM epsg.epsg_primemeridian", (EPSG_AUTHORITY, EPSG_AUTHORITY))


def fill_geodetic_datum(proj_db_cursor):
    proj_db_cursor.execute(
        "SELECT DISTINCT * FROM epsg.epsg_datum WHERE datum_type NOT IN ('geodetic', 'vertical', 'engineering')")
    res = proj_db_cursor.fetchall()
    if res:
        raise Exception('Found unexpected datum_type in epsg_datum: %s' % str(res))

    proj_db_cursor.execute(
        "INSERT INTO geodetic_datum SELECT ?, datum_code, datum_name, NULL, NULL, ?, ellipsoid_code, ?, prime_meridian_code, ?, area_of_use_code, deprecated FROM epsg.epsg_datum WHERE datum_type = 'geodetic'", (EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY))


def fill_vertical_datum(proj_db_cursor):
    proj_db_cursor.execute(
        "INSERT INTO vertical_datum SELECT ?, datum_code, datum_name, NULL, NULL, ?, area_of_use_code, deprecated FROM epsg.epsg_datum WHERE datum_type = 'vertical'", (EPSG_AUTHORITY,EPSG_AUTHORITY))


def fill_coordinate_system(proj_db_cursor):
    proj_db_cursor.execute(
        "INSERT INTO coordinate_system SELECT ?, coord_sys_code, coord_sys_type, dimension FROM epsg.epsg_coordinatesystem", (EPSG_AUTHORITY,))


def fill_axis(proj_db_cursor):
    proj_db_cursor.execute("INSERT INTO axis SELECT ?, coord_axis_code, coord_axis_name, coord_axis_abbreviation, coord_axis_orientation, ?, coord_sys_code, coord_axis_order, ?, uom_code FROM epsg.epsg_coordinateaxis ca LEFT JOIN epsg.epsg_coordinateaxisname can ON ca.coord_axis_name_code = can.coord_axis_name_code", (EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY))


def fill_geodetic_crs(proj_db_cursor):
    proj_db_cursor.execute(
        "SELECT DISTINCT * FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind NOT IN ('projected', 'geographic 2D', 'geographic 3D', 'geocentric', 'vertical', 'compound', 'engineering')")
    res = proj_db_cursor.fetchall()
    if res:
        raise Exception('Found unexpected coord_ref_sys_kind in epsg_coordinatereferencesystem: %s' % str(res))

    #proj_db_cursor.execute(
    #    "INSERT INTO crs SELECT ?, coord_ref_sys_code, coord_ref_sys_kind FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('geographic 2D', 'geographic 3D', 'geocentric') AND datum_code IS NOT NULL", (EPSG_AUTHORITY,))
    proj_db_cursor.execute("INSERT INTO geodetic_crs SELECT ?, coord_ref_sys_code, coord_ref_sys_name, NULL, NULL, coord_ref_sys_kind, ?, coord_sys_code, ?, datum_code, ?, area_of_use_code, NULL, deprecated FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('geographic 2D', 'geographic 3D', 'geocentric') AND datum_code IS NOT NULL", (EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY))


def fill_vertical_crs(proj_db_cursor):
    #proj_db_cursor.execute(
    #    "INSERT INTO crs SELECT ?, coord_ref_sys_code, coord_ref_sys_kind FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('vertical') AND datum_code IS NOT NULL", (EPSG_AUTHORITY,))
    proj_db_cursor.execute("INSERT INTO vertical_crs SELECT ?, coord_ref_sys_code, coord_ref_sys_name, NULL, NULL, ?, coord_sys_code, ?, datum_code, ?, area_of_use_code, deprecated FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('vertical') AND datum_code IS NOT NULL", (EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY))

def fill_conversion(proj_db_cursor):

    already_mapped_methods = set()
    trigger_sql = """
CREATE TRIGGER conversion_method_check_insert_trigger
BEFORE INSERT ON conversion
FOR EACH ROW BEGIN
"""

    proj_db_cursor.execute("SELECT coord_op_code, coord_op_name, area_of_use_code, coord_op_method_code, coord_op_method_name, epsg_coordoperation.deprecated FROM epsg.epsg_coordoperation LEFT JOIN epsg.epsg_coordoperationmethod USING (coord_op_method_code) WHERE coord_op_type = 'conversion' AND coord_op_name NOT LIKE '%to DMSH'")
    for (code, name, area_of_use_code, method_code, method_name, deprecated) in proj_db_cursor.fetchall():
        expected_order = 1
        max_n_params = 7
        param_auth_name = [None for i in range(max_n_params)]
        param_code = [None for i in range(max_n_params)]
        param_name = [None for i in range(max_n_params)]
        param_value = [None for i in range(max_n_params)]
        param_uom_auth_name = [None for i in range(max_n_params)]
        param_uom_code = [None for i in range(max_n_params)]
        param_uom_type = [None for i in range(max_n_params)]

        iterator = proj_db_cursor.execute("SELECT sort_order, cop.parameter_code, parameter_name, parameter_value, uom_code, uom.unit_of_meas_type FROM epsg_coordoperationparam cop LEFT JOIN epsg_coordoperationparamvalue copv LEFT JOIN epsg_unitofmeasure uom USING (uom_code) LEFT JOIN epsg_coordoperationparamusage copu ON cop.parameter_code = copv.parameter_code AND copu.parameter_code = copv.parameter_code WHERE copu.coord_op_method_code = copv.coord_op_method_code AND coord_op_code = ? AND copv.coord_op_method_code = ? ORDER BY sort_order", (code, method_code))
        for (order, parameter_code, parameter_name, parameter_value, uom_code, uom_type) in iterator:
            # Modified Krovak and Krovak North Oriented: keep only the 7 first parameters
            if order == max_n_params + 1 and method_code in (1042, 1043):
                break
            assert order <= max_n_params
            assert order == expected_order
            param_auth_name[order - 1] = EPSG_AUTHORITY
            param_code[order - 1] = parameter_code
            param_name[order - 1] = parameter_name
            param_value[order - 1] = parameter_value
            param_uom_auth_name[order - 1] = EPSG_AUTHORITY if uom_code else None
            param_uom_code[order - 1] = uom_code
            param_uom_type[order - 1] = uom_type
            expected_order += 1

        if method_code not in already_mapped_methods:
            already_mapped_methods.add(method_code)
            trigger_sql += """
    SELECT RAISE(ABORT, 'insert on conversion violates constraint: bad parameters for %(method_name)s')
        WHERE NEW.deprecated != 1 AND NEW.method_auth_name = 'EPSG' AND NEW.method_code = '%(method_code)s' AND (NEW.method_name != '%(method_name)s'""" % {'method_name': method_name, 'method_code' : method_code}
            for i in range(expected_order-1):
                trigger_sql += " OR NEW.param%(n)d_auth_name != 'EPSG' OR NEW.param%(n)d_code != '%(code)d' OR NEW.param%(n)d_name != '%(param_name)s'" % {'n': i+1, 'code': param_code[i], 'param_name': param_name[i]}

                if method_name in ('Change of Vertical Unit'):
                    trigger_sql += " OR (NOT((NEW.param%(n)d_value IS NULL AND NEW.param%(n)d_uom_auth_name IS NULL AND NEW.param%(n)d_uom_code IS NULL) OR (NEW.param%(n)d_value IS NOT NULL AND (SELECT type FROM unit_of_measure WHERE auth_name = NEW.param%(n)s_uom_auth_name AND code = NEW.param%(n)s_uom_code) = 'scale')))" % {'n': i+1, 'param_name': param_name[i]}
                else:
                    trigger_sql += " OR NEW.param%(n)d_value IS NULL OR NEW.param%(n)d_uom_auth_name IS NULL OR NEW.param%(n)d_uom_code IS NULL" % {'n': i+1, 'param_name': param_name[i]}

                if param_uom_type[i]:
                    trigger_sql += " OR (SELECT type FROM unit_of_measure WHERE auth_name = NEW.param%(n)s_uom_auth_name AND code = NEW.param%(n)s_uom_code) != '%(uom_type)s'" % {'n': i+1, 'uom_type': param_uom_type[i]}
            for i in range(expected_order-1, max_n_params):
                trigger_sql += " OR NEW.param%(n)d_auth_name IS NOT NULL OR NEW.param%(n)d_code IS NOT NULL OR NEW.param%(n)d_name IS NOT NULL OR NEW.param%(n)d_value IS NOT NULL OR NEW.param%(n)d_uom_auth_name IS NOT NULL OR NEW.param%(n)d_uom_code IS NOT NULL" % {'n': i+1}
            trigger_sql += ");\n"

        arg = (EPSG_AUTHORITY, code, name,
               None, None, # description + scope
               EPSG_AUTHORITY, area_of_use_code,
               EPSG_AUTHORITY, method_code, method_name,
               param_auth_name[0], param_code[0], param_name[0],
               param_value[0], param_uom_auth_name[0], param_uom_code[0],
               param_auth_name[1], param_code[1], param_name[1], param_value[1],
               param_uom_auth_name[1], param_uom_code[1], param_auth_name[2],
               param_code[2], param_name[2], param_value[2],
               param_uom_auth_name[2], param_uom_code[2],
               param_auth_name[3], param_code[3], param_name[3], param_value[3],
               param_uom_auth_name[3], param_uom_code[3], param_auth_name[4],
               param_code[4], param_name[4], param_value[4],
               param_uom_auth_name[4], param_uom_code[4], param_auth_name[5],
               param_code[5], param_name[5], param_value[5],
               param_uom_auth_name[5], param_uom_code[5], param_auth_name[6],
               param_code[6], param_name[6], param_value[6],
               param_uom_auth_name[6], param_uom_code[6],
               deprecated)

        #proj_db_cursor.execute("INSERT INTO coordinate_operation VALUES (?,?,'conversion')", (EPSG_AUTHORITY, code))
        proj_db_cursor.execute('INSERT INTO conversion VALUES (' +
            '?,?,?, ?,?, ?,?, ?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ' +
            '?,?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ?)', arg)

    trigger_sql += "END;";
    #print(trigger_sql)
    proj_db_cursor.execute(trigger_sql)


def fill_projected_crs(proj_db_cursor):
    #proj_db_cursor.execute(
    #    "INSERT INTO crs SELECT 'EPSG', coord_ref_sys_code, coord_ref_sys_kind FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('projected')")
    #proj_db_cursor.execute("INSERT INTO projected_crs SELECT 'EPSG', coord_ref_sys_code, coord_ref_sys_name, 'EPSG', coord_sys_code, 'EPSG', source_geogcrs_code, 'EPSG', projection_conv_code, 'EPSG', area_of_use_code, deprecated FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('projected')")
    proj_db_cursor.execute("SELECT ?, coord_ref_sys_code, coord_ref_sys_name, NULL, NULL, ?, coord_sys_code, ?, source_geogcrs_code, ?, projection_conv_code, ?, area_of_use_code, deprecated FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('projected')", (EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY))
    for row in proj_db_cursor.fetchall():
        (auth_name, code, name, description, scope, coordinate_system_auth_name, coordinate_system_code, geodetic_crs_auth_name, geodetic_crs_code, conversion_auth_name, conversion_code, area_of_use_auth_name, area_of_use_code, deprecated) = row
        proj_db_cursor.execute("SELECT 1 FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_code = ? AND coord_ref_sys_kind IN ('geographic 2D', 'geographic 3D', 'geocentric')", (geodetic_crs_code,))
        if proj_db_cursor.fetchone():
            #proj_db_cursor.execute("INSERT INTO crs VALUES (?, ?, 'projected')", (EPSG_AUTHORITY, code))
            proj_db_cursor.execute("INSERT INTO projected_crs VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,NULL,?)", row)

def fill_compound_crs(proj_db_cursor):
    #proj_db_cursor.execute(
    #    "INSERT INTO crs SELECT ?, coord_ref_sys_code, coord_ref_sys_kind FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('compound')", (EPSG_AUTHORITY,))

    proj_db_cursor.execute("SELECT ?, coord_ref_sys_code, coord_ref_sys_name, NULL, NULL, ?, cmpd_horizcrs_code, ?, cmpd_vertcrs_code, ?, area_of_use_code, deprecated FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind IN ('compound') AND cmpd_horizcrs_code NOT IN (SELECT coord_ref_sys_code FROM epsg.epsg_coordinatereferencesystem WHERE coord_ref_sys_kind = 'engineering')", (EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY, EPSG_AUTHORITY))
    for auth_name, code, name, description, scope, horiz_auth_name, horiz_code, vert_auth_name, vert_code, area_of_use_auth_name, area_of_use_code, deprecated in proj_db_cursor.fetchall():
        try:
            proj_db_cursor.execute("INSERT INTO compound_crs VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", (auth_name, code, name, description, scope, horiz_auth_name, horiz_code, vert_auth_name, vert_code, area_of_use_auth_name, area_of_use_code, deprecated))
        except sqlite3.IntegrityError as e:
            print(e)
            print(auth_name, code, name, description, scope, horiz_auth_name, horiz_code, vert_auth_name, vert_code, area_of_use_auth_name, area_of_use_code, deprecated)
            raise

def fill_helmert_transformation(proj_db_cursor):
    proj_db_cursor.execute("SELECT coord_op_code, coord_op_name, coord_op_method_code, coord_op_method_name, source_crs_code, target_crs_code, area_of_use_code, coord_op_accuracy, epsg_coordoperation.deprecated FROM epsg.epsg_coordoperation LEFT JOIN epsg.epsg_coordoperationmethod USING (coord_op_method_code) WHERE coord_op_type = 'transformation' AND coord_op_method_code IN (1031, 1032, 1033, 1034, 1035, 1037, 1038, 1039, 1053, 1054, 1055, 1056, 1057, 1058, 1061, 1062, 1063, 1065, 1066, 9603, 9606, 9607, 9636) ")
    for (code, name, method_code, method_name, source_crs_code, target_crs_code, area_of_use_code, coord_op_accuracy, deprecated) in proj_db_cursor.fetchall():
        expected_order = 1
        max_n_params = 15
        param_auth_name = [None for i in range(max_n_params)]
        param_code = [None for i in range(max_n_params)]
        param_name = [None for i in range(max_n_params)]
        param_value = [None for i in range(max_n_params)]
        param_uom_code = [None for i in range(max_n_params)]

        iterator = proj_db_cursor.execute("SELECT sort_order, cop.parameter_code, parameter_name, parameter_value, uom_code from epsg_coordoperationparam cop LEFT JOIN epsg_coordoperationparamvalue copv LEFT JOIN epsg_coordoperationparamusage copu ON cop.parameter_code = copv.parameter_code AND copu.parameter_code = copv.parameter_code WHERE copu.coord_op_method_code = copv.coord_op_method_code AND coord_op_code = ? AND copv.coord_op_method_code = ? ORDER BY sort_order", (code, method_code))
        for (order, parameter_code, parameter_name, parameter_value, uom_code) in iterator:
            assert order <= max_n_params
            assert order == expected_order
            param_auth_name[order - 1] = EPSG_AUTHORITY
            param_code[order - 1] = parameter_code
            param_name[order - 1] = parameter_name
            param_value[order - 1] = parameter_value
            param_uom_code[order - 1] = uom_code
            expected_order += 1
        n_params = expected_order - 1

        if param_value[0] is None and deprecated:
            continue # silently discard non sense deprecated transforms (like EPSG:1076)

        assert param_code[0] == 8605
        assert param_code[1] == 8606
        assert param_code[2] == 8607
        assert param_uom_code[0] == param_uom_code[1]
        assert param_uom_code[0] == param_uom_code[2]
        px = None
        py = None
        pz = None
        pivot_uom_code = None
        if n_params > 3:
            assert param_code[3] == 8608
            assert param_code[4] == 8609
            assert param_code[5] == 8610
            assert param_code[6] == 8611
            assert param_uom_code[3] == param_uom_code[4]
            assert param_uom_code[3] == param_uom_code[5]
        if n_params == 8: # Time-specific transformation
            assert param_code[7] == 1049, (code, name, param_code[7])
            param_value[14] = param_value[7]
            param_uom_code[14] = param_uom_code[7]
            param_value[7] = None
            param_uom_code[7] = None

        elif n_params == 10: # Molodensky-Badekas
            assert param_code[7] == 8617, (code, name, param_code[7])
            assert param_code[8] == 8618, (code, name, param_code[8])
            assert param_code[9] == 8667, (code, name, param_code[9])
            assert param_uom_code[7] == param_uom_code[8]
            assert param_uom_code[7] == param_uom_code[9]
            px = param_value[7]
            py = param_value[8]
            pz = param_value[9]
            pivot_uom_code = param_uom_code[7]
            param_value[7] = None
            param_uom_code[7] = None
            param_value[8] = None
            param_uom_code[8] = None
            param_value[9] = None
            param_uom_code[9] = None

        elif n_params > 7: # Time-dependant transformation
            assert param_code[7] == 1040, (code, name, param_code[7])
            assert param_code[8] == 1041
            assert param_code[9] == 1042
            assert param_code[10] == 1043
            assert param_code[11] == 1044
            assert param_code[12] == 1045
            assert param_code[13] == 1046
            assert param_code[14] == 1047
            assert param_uom_code[7] == param_uom_code[8]
            assert param_uom_code[7] == param_uom_code[9]
            assert param_uom_code[10] == param_uom_code[11]
            assert param_uom_code[10] == param_uom_code[12]

        arg = (EPSG_AUTHORITY, code, name,
               None, None, # description + scope
               EPSG_AUTHORITY, method_code, method_name,
               EPSG_AUTHORITY, source_crs_code,
               EPSG_AUTHORITY, target_crs_code,
               EPSG_AUTHORITY, area_of_use_code,
               coord_op_accuracy,
               param_value[0], param_value[1], param_value[2], EPSG_AUTHORITY, param_uom_code[0],
               param_value[3], param_value[4], param_value[5], EPSG_AUTHORITY if param_uom_code[3] else None, param_uom_code[3],
               param_value[6], EPSG_AUTHORITY if param_uom_code[6] else None, param_uom_code[6],
               param_value[7], param_value[8], param_value[9], EPSG_AUTHORITY if param_uom_code[7] else None, param_uom_code[7],
               param_value[10], param_value[11], param_value[12], EPSG_AUTHORITY if param_uom_code[10] else None, param_uom_code[10],
               param_value[13], EPSG_AUTHORITY if param_uom_code[13] else None, param_uom_code[13],
               param_value[14], EPSG_AUTHORITY if param_uom_code[14] else None, param_uom_code[14],
               px, py, pz, EPSG_AUTHORITY if px else None, pivot_uom_code,
               deprecated
               )

        #proj_db_cursor.execute("INSERT INTO coordinate_operation VALUES (?,?,'helmert_transformation')", (EPSG_AUTHORITY, code))
        proj_db_cursor.execute('INSERT INTO helmert_transformation VALUES (' +
            '?,?,?, ?,?, ?,?,?, ?,?, ?,?, ?,?, ?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?, ?,?,?, ?,?,?,?,?, ?)', arg)

def fill_grid_transformation(proj_db_cursor):
    proj_db_cursor.execute("SELECT coord_op_code, coord_op_name, coord_op_method_code, coord_op_method_name, source_crs_code, target_crs_code, area_of_use_code, coord_op_accuracy, epsg_coordoperation.deprecated FROM epsg.epsg_coordoperation LEFT JOIN epsg.epsg_coordoperationmethod USING (coord_op_method_code) WHERE coord_op_type = 'transformation' AND (coord_op_method_name LIKE 'Geographic3D to%' OR coord_op_method_name LIKE 'Geog3D to%' OR coord_op_method_name LIKE 'Point motion by grid%' OR coord_op_method_name LIKE 'Vertical Offset by Grid Interpolation%' OR coord_op_method_name IN ('NADCON', 'NTv1', 'NTv2', 'VERTCON'))")
    for (code, name, method_code, method_name, source_crs_code, target_crs_code, area_of_use_code, coord_op_accuracy, deprecated) in proj_db_cursor.fetchall():
        expected_order = 1
        max_n_params = 2
        param_auth_name = [None for i in range(max_n_params)]
        param_code = [None for i in range(max_n_params)]
        param_name = [None for i in range(max_n_params)]
        param_value = [None for i in range(max_n_params)]
        param_uom_code = [None for i in range(max_n_params)]

        iterator = proj_db_cursor.execute("SELECT sort_order, cop.parameter_code, parameter_name, parameter_value, param_value_file_ref, uom_code from epsg_coordoperationparam cop LEFT JOIN epsg_coordoperationparamvalue copv LEFT JOIN epsg_coordoperationparamusage copu ON cop.parameter_code = copv.parameter_code AND copu.parameter_code = copv.parameter_code WHERE copu.coord_op_method_code = copv.coord_op_method_code AND coord_op_code = ? AND copv.coord_op_method_code = ? ORDER BY sort_order", (code, method_code))
        for (order, parameter_code, parameter_name, parameter_value, param_value_file_ref, uom_code) in iterator:
            assert order <= max_n_params
            assert order == expected_order
            if parameter_value is not None:
                assert param_value_file_ref is None or len(param_value_file_ref) == 0, (order, parameter_code, parameter_name, parameter_value, param_value_file_ref, uom_code)
            if param_value_file_ref is not None and len(param_value_file_ref) != 0:
                assert parameter_value is None, (order, parameter_code, parameter_name, parameter_value, param_value_file_ref, uom_code)
            param_auth_name[order - 1] = EPSG_AUTHORITY
            param_code[order - 1] = parameter_code
            param_name[order - 1] = parameter_name
            param_value[order - 1] = parameter_value if parameter_value else param_value_file_ref
            param_uom_code[order - 1] = uom_code
            expected_order += 1
        n_params = expected_order - 1

        assert param_code[0] in (1050, 8656, 8657, 8666, 8732), (code, param_code[0])

        grid2_param_auth_name = None
        grid2_param_code = None
        grid2_param_name = None
        grid2_value = None
        interpolation_crs_auth_name = None
        interpolation_crs_code = None

        if method_code == 9613: # NADCON
            assert param_code[1] == 8658, param_code[1]
            grid2_param_auth_name = EPSG_AUTHORITY
            grid2_param_code = param_code[1]
            grid2_param_name = param_name[1]
            grid2_value = param_value[1]
        elif method_code == 1071: # Vertical Offset by Grid Interpolation (NZLVD) 
            assert param_code[1] == 1048, param_code[1]
            interpolation_crs_auth_name = EPSG_AUTHORITY
            interpolation_crs_code = str(int(param_value[1])) # needed to avoid codes like XXXX.0
        else:
            assert n_params == 1, (code, method_code)


        arg = (EPSG_AUTHORITY, code, name,
               None, None, # description + scope
               EPSG_AUTHORITY, method_code, method_name,
               EPSG_AUTHORITY, source_crs_code,
               EPSG_AUTHORITY, target_crs_code,
               EPSG_AUTHORITY, area_of_use_code,
               coord_op_accuracy,
               EPSG_AUTHORITY, param_code[0], param_name[0], param_value[0],
               grid2_param_auth_name, grid2_param_code, grid2_param_name, grid2_value,
               interpolation_crs_auth_name, interpolation_crs_code,
               deprecated
               )

        #proj_db_cursor.execute("INSERT INTO coordinate_operation VALUES (?,?,'grid_transformation')", (EPSG_AUTHORITY, code))
        proj_db_cursor.execute('INSERT INTO grid_transformation VALUES (' +
            '?,?,?, ?,?, ?,?,?, ?,?, ?,?, ?,?, ?, ?,?,?,?, ?,?,?,?, ?,?, ?)', arg)

def fill_other_transformation(proj_db_cursor):
    # 9601: Longitude rotation
    # 9616: Vertical offset
    # 9618: Geographic2D with Height offsets
    # 9619: Geographic2D offsets
    # 9624: Affine Parametric Transformation
    # 9660: Geographic3D offsets
    proj_db_cursor.execute("SELECT coord_op_code, coord_op_name, coord_op_method_code, coord_op_method_name, source_crs_code, target_crs_code, area_of_use_code, coord_op_accuracy, epsg_coordoperation.deprecated FROM epsg.epsg_coordoperation LEFT JOIN epsg.epsg_coordoperationmethod USING (coord_op_method_code) WHERE coord_op_type = 'transformation' AND coord_op_method_code IN (9601, 9616, 9618, 9619, 9624, 9660)")
    for (code, name, method_code, method_name, source_crs_code, target_crs_code, area_of_use_code, coord_op_accuracy, deprecated) in proj_db_cursor.fetchall():
        expected_order = 1
        max_n_params = 7
        param_auth_name = [None for i in range(max_n_params)]
        param_code = [None for i in range(max_n_params)]
        param_name = [None for i in range(max_n_params)]
        param_value = [None for i in range(max_n_params)]
        param_uom_auth_name = [None for i in range(max_n_params)]
        param_uom_code = [None for i in range(max_n_params)]

        iterator = proj_db_cursor.execute("SELECT sort_order, cop.parameter_code, parameter_name, parameter_value, uom_code from epsg_coordoperationparam cop LEFT JOIN epsg_coordoperationparamvalue copv LEFT JOIN epsg_coordoperationparamusage copu ON cop.parameter_code = copv.parameter_code AND copu.parameter_code = copv.parameter_code WHERE copu.coord_op_method_code = copv.coord_op_method_code AND coord_op_code = ? AND copv.coord_op_method_code = ? ORDER BY sort_order", (code, method_code))
        for (order, parameter_code, parameter_name, parameter_value, uom_code) in iterator:
            assert order <= max_n_params
            assert order == expected_order
            param_auth_name[order - 1] = EPSG_AUTHORITY
            param_code[order - 1] = parameter_code
            param_name[order - 1] = parameter_name
            param_value[order - 1] = parameter_value
            param_uom_auth_name[order - 1] = EPSG_AUTHORITY
            param_uom_code[order - 1] = uom_code
            expected_order += 1

        arg = (EPSG_AUTHORITY, code, name,
               None, None, # description + scope
               EPSG_AUTHORITY, method_code, method_name,
               EPSG_AUTHORITY, source_crs_code,
               EPSG_AUTHORITY, target_crs_code,
               EPSG_AUTHORITY, area_of_use_code,
               coord_op_accuracy,
               param_auth_name[0], param_code[0], param_name[0],
               param_value[0], param_uom_auth_name[0], param_uom_code[0],
               param_auth_name[1], param_code[1], param_name[1], param_value[1],
               param_uom_auth_name[1], param_uom_code[1], param_auth_name[2],
               param_code[2], param_name[2], param_value[2],
               param_uom_auth_name[2], param_uom_code[2],
               param_auth_name[3], param_code[3], param_name[3], param_value[3],
               param_uom_auth_name[3], param_uom_code[3], param_auth_name[4],
               param_code[4], param_name[4], param_value[4],
               param_uom_auth_name[4], param_uom_code[4], param_auth_name[5],
               param_code[5], param_name[5], param_value[5],
               param_uom_auth_name[5], param_uom_code[5], param_auth_name[6],
               param_code[6], param_name[6], param_value[6],
               param_uom_auth_name[6], param_uom_code[6],
               deprecated)

        #proj_db_cursor.execute("INSERT INTO coordinate_operation VALUES (?,?,'other_transformation')", (EPSG_AUTHORITY, code))
        proj_db_cursor.execute('INSERT INTO other_transformation VALUES (' +
            '?,?,?, ?,?, ?,?,?, ?,?, ?,?, ?,?, ?, ?,?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ' +
            '?,?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ?)', arg)

def fill_concatenated_operation(proj_db_cursor):
    proj_db_cursor.execute("SELECT coord_op_code, coord_op_name, coord_op_method_code, coord_op_method_name, source_crs_code, target_crs_code, area_of_use_code, coord_op_accuracy, epsg_coordoperation.deprecated FROM epsg.epsg_coordoperation LEFT JOIN epsg.epsg_coordoperationmethod USING (coord_op_method_code) WHERE coord_op_type = 'concatenated operation'")
    for (code, name, method_code, method_name, source_crs_code, target_crs_code, area_of_use_code, coord_op_accuracy, deprecated) in proj_db_cursor.fetchall():
        expected_order = 1
        max_n_params = 3
        step_code = [None for i in range(max_n_params)]

        iterator = proj_db_cursor.execute("SELECT op_path_step, single_operation_code FROM epsg_coordoperationpath WHERE concat_operation_code = ? ORDER BY op_path_step", (code,))
        for (order, single_operation_code) in iterator:
            assert order <= max_n_params
            assert order == expected_order
            step_code[order - 1] = single_operation_code
            expected_order += 1
        n_params = expected_order - 1
        if n_params == 0:  # For example http://www.epsg-registry.org//export.htm?gml=urn:ogc:def:coordinateOperation:EPSG::8658
            continue
        assert n_params in (2, 3), (code, n_params)

        arg = (EPSG_AUTHORITY, code, name,
               None, None, # description + scope
               EPSG_AUTHORITY, source_crs_code,
               EPSG_AUTHORITY, target_crs_code,
               EPSG_AUTHORITY, area_of_use_code,
               coord_op_accuracy,
               EPSG_AUTHORITY, step_code[0],
               EPSG_AUTHORITY, step_code[1],
               EPSG_AUTHORITY if step_code[2] else None, step_code[2],
               deprecated
               )

        proj_db_cursor.execute("SELECT 1 FROM coordinate_operation_with_conversion_view WHERE code = ?", (step_code[0],))
        step1_exists = proj_db_cursor.fetchone() is not None

        proj_db_cursor.execute("SELECT 1 FROM coordinate_operation_with_conversion_view WHERE code = ?", (step_code[1],))
        step2_exists = proj_db_cursor.fetchone() is not None

        step3_exists = True
        if step_code[2]:
            proj_db_cursor.execute("SELECT 1 FROM coordinate_operation_with_conversion_view WHERE code = ?", (step_code[2],))
            step3_exists = proj_db_cursor.fetchone() is not None

        if step1_exists and step2_exists and step3_exists:
            #proj_db_cursor.execute("INSERT INTO coordinate_operation VALUES (?,?,'concatenated_operation')", (EPSG_AUTHORITY, code))
            proj_db_cursor.execute('INSERT INTO concatenated_operation VALUES (' +
            '?,?,?, ?,?, ?,?, ?,?, ?,?, ?, ?,?, ?,?, ?,?, ?)', arg)

def fill_alias(proj_db_cursor):
    proj_db_cursor.execute("SELECT object_code, alias FROM epsg.epsg_alias WHERE object_table_name = 'epsg_datum'")
    for row in proj_db_cursor.fetchall():
        code, alt_name = row
        proj_db_cursor.execute('SELECT 1 FROM geodetic_datum WHERE code = ?', (code,))
        if proj_db_cursor.fetchone() is not None:
            proj_db_cursor.execute("INSERT INTO alias_name VALUES ('geodetic_datum','EPSG',?,?,'EPSG')", (code, alt_name))
        else:
            proj_db_cursor.execute('SELECT 1 FROM vertical_datum WHERE code = ?', (code,))
            if proj_db_cursor.fetchone() is not None:
                proj_db_cursor.execute("INSERT INTO alias_name VALUES ('vertical_datum','EPSG',?,?,'EPSG')", (code, alt_name))
            else:
                print('Cannot find datum %s in geodetic_datum or vertical_datum' % (code))


def find_table(proj_db_cursor, code):
    for table_name in ('helmert_transformation', 'grid_transformation', 'concatenated_operation', 'geodetic_crs', 'projected_crs', 'vertical_crs', 'compound_crs'):
        proj_db_cursor.execute('SELECT name FROM %s WHERE code = ?' % table_name, (code,))
        row = proj_db_cursor.fetchone()
        if row is not None:
            return row[0], table_name
    return None

def fill_supersession(proj_db_cursor):
    proj_db_cursor.execute("SELECT object_code, superseded_by FROM epsg.epsg_supersession WHERE object_table_name = 'epsg_coordoperation' AND object_code != superseded_by")
    for row in proj_db_cursor.fetchall():
        code, superseded_by = row
        proj_db_cursor.execute('SELECT 1 FROM coordinate_operation_view WHERE code = ?', (code,))
        if proj_db_cursor.fetchone() is None:
            print('Skipping supersession of %d since it has not been imported' % code)
            continue

        src_name, superseded_table_name = find_table(proj_db_cursor, code)
        dst_name, replacement_table_name = find_table(proj_db_cursor, superseded_by)
        assert superseded_table_name, row
        assert replacement_table_name, row
        if superseded_table_name == 'grid_transformation' and replacement_table_name == 'grid_transformation' and src_name.startswith('NAD27 to NAD83'):
            print('Skipping supersession of %d (%s) by %d (%s)' % (code, src_name, superseded_by, dst_name))
            continue
        proj_db_cursor.execute("INSERT INTO supersession VALUES (?,'EPSG',?,?,'EPSG',?,'EPSG')", (superseded_table_name, code, replacement_table_name, superseded_by))

def fill_deprecation(proj_db_cursor):
    proj_db_cursor.execute("SELECT object_code, replaced_by FROM epsg.epsg_deprecation WHERE object_table_name = 'epsg_coordinatereferencesystem' AND object_code != replaced_by")
    for row in proj_db_cursor.fetchall():
        code, replaced_by = row
        proj_db_cursor.execute('SELECT 1 FROM crs_view WHERE code = ?', (code,))
        if proj_db_cursor.fetchone() is None:
            print('Skipping deprecation of %d since it has not been imported' % code)
            continue

        src_name, deprecated_table_name = find_table(proj_db_cursor, code)
        dst_name, replacement_table_name = find_table(proj_db_cursor, replaced_by)
        assert deprecated_table_name, row
        assert replacement_table_name, row
        assert deprecated_table_name == replacement_table_name
        proj_db_cursor.execute("INSERT INTO deprecation VALUES (?,'EPSG',?,'EPSG',?,'EPSG')", (deprecated_table_name, code, replaced_by))

def report_non_imported_operations(proj_db_cursor):
    proj_db_cursor.execute("SELECT coord_op_code, coord_op_type, coord_op_name, coord_op_method_code, coord_op_method_name, source_crs_code, target_crs_code, area_of_use_code, coord_op_accuracy, epsg_coordoperation.deprecated FROM epsg.epsg_coordoperation LEFT JOIN epsg.epsg_coordoperationmethod USING (coord_op_method_code) WHERE coord_op_code NOT IN (SELECT code FROM coordinate_operation_with_conversion_view)")
    rows = []
    first = True
    for row in proj_db_cursor.fetchall():
        if first:
            print('Non imported coordinate_operation:')
            first = False
        print('  ' + str(row))
        rows.append(row)
    return rows

epsg_db_conn, epsg_tmp_db_filename = ingest_epsg()

script_dir_name = os.path.dirname(os.path.realpath(__file__))
sql_dir_name = os.path.join(os.path.dirname(script_dir_name), 'data', 'sql')

proj_db_filename = ':memory:'
if os.path.exists(proj_db_filename):
    os.unlink(proj_db_filename)
proj_db_conn = sqlite3.connect(proj_db_filename)
proj_db_cursor = proj_db_conn.cursor()
proj_db_cursor.execute('PRAGMA foreign_keys = 1;')

ingest_sqlite_dump(proj_db_cursor, os.path.join(sql_dir_name, 'proj_db_table_defs.sql'))
proj_db_cursor.execute("ATTACH DATABASE '%s' AS epsg;" % epsg_tmp_db_filename)

fill_unit_of_measure(proj_db_cursor)
fill_ellipsoid(proj_db_cursor)
fill_area(proj_db_cursor)
fill_prime_meridian(proj_db_cursor)
fill_geodetic_datum(proj_db_cursor)
fill_vertical_datum(proj_db_cursor)
fill_coordinate_system(proj_db_cursor)
fill_axis(proj_db_cursor)
fill_geodetic_crs(proj_db_cursor)
fill_vertical_crs(proj_db_cursor)
fill_conversion(proj_db_cursor)
fill_projected_crs(proj_db_cursor)
fill_compound_crs(proj_db_cursor)
fill_helmert_transformation(proj_db_cursor)
fill_grid_transformation(proj_db_cursor)
fill_other_transformation(proj_db_cursor)
fill_concatenated_operation(proj_db_cursor)
fill_alias(proj_db_cursor)
fill_supersession(proj_db_cursor)
fill_deprecation(proj_db_cursor)
non_imported_operations = report_non_imported_operations(proj_db_cursor)

proj_db_cursor.close()
proj_db_conn.commit()

files = {}

# Dump the generated database and split it one .sql file per table
for line in proj_db_conn.iterdump():
    if line.startswith('INSERT INTO "'):
        table_name = line[len('INSERT INTO "'):]
        table_name = table_name[0:table_name.find('"')]
        if table_name in files:
            f = files[table_name]
        else:
            f = open(os.path.join(sql_dir_name, table_name) + '.sql', 'wb')
            f.write("--- This file has been generated by scripts/build_db.py. DO NOT EDIT !\n\n".encode('UTF-8'))
            files[table_name] = f
        f.write((line + '\n').encode('UTF-8'))
    elif line.startswith('CREATE TRIGGER conversion_method_check_insert_trigger'):
        table_name = 'conversion_triggers'
        if table_name in files:
            f = files[table_name]
        else:
            f = open(os.path.join(sql_dir_name, table_name) + '.sql', 'wb')
            f.write("--- This file has been generated by scripts/build_db.py. DO NOT EDIT !\n\n".encode('UTF-8'))
            files[table_name] = f
        f.write((line + '\n').encode('UTF-8'))
#f = files['coordinate_operation']
#for row in non_imported_operations:
#    f.write(("--- Non imported: " + str(row) + '\n').encode('UTF-8'))
del files

proj_db_conn = None

epsg_db_conn = None
if os.path.exists(epsg_tmp_db_filename):
    os.unlink(epsg_tmp_db_filename)