From e63f206d994658995505ce322d644fba0b807d5b Mon Sep 17 00:00:00 2001 From: Even Rouault Date: Sun, 21 Apr 2019 21:51:43 +0200 Subject: Database: make conversion & helmert_transformation updatable views - Transform conversion as a view, and when inserting into it, actually insert into 3 tables: conversion_table, conversion_method and conversion_param, so that method and parameter names are not repeated each time. - Similarly for helmert_tranformation, insert into helmert_transformation_tabl and coordinate_operation_method. This reduces the db size from 6 344 704 bytes to 5 853 184 bytes, without significant slowdown for queries. --- scripts/build_db_create_ignf_from_xml.py | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'scripts/build_db_create_ignf_from_xml.py') diff --git a/scripts/build_db_create_ignf_from_xml.py b/scripts/build_db_create_ignf_from_xml.py index 79863eba..b797477f 100755 --- a/scripts/build_db_create_ignf_from_xml.py +++ b/scripts/build_db_create_ignf_from_xml.py @@ -923,7 +923,7 @@ for node in root.iterfind('.//Conversion'): #sql = """INSERT INTO "coordinate_operation" VALUES('IGNF','%s','conversion');""" % (id) #all_sql.append(sql) - sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','1262',NULL,NULL,'Gauss Schreiber Transverse Mercator','EPSG','8801','Latitude of natural origin',%s,'EPSG','9102','EPSG','8802','Longitude of natural origin',%s,'EPSG','9102','EPSG','8805','Scale factor at natural origin',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['k_0'], d['x_0'], d['y_0']) + sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','1262','PROJ','gstm','Gauss Schreiber Transverse Mercator','EPSG','8801','Latitude of natural origin',%s,'EPSG','9102','EPSG','8802','Longitude of natural origin',%s,'EPSG','9102','EPSG','8805','Scale factor at natural origin',%s,'EPSG','9201','EPSG','8806','False easting',%s,'EPSG','9001','EPSG','8807','False northing',%s,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id, name, d['lat_0'], d['lon_0'], d['k_0'], d['x_0'], d['y_0']) all_sql.append(sql) mapConversionId[id] = ('IGNF', id) @@ -963,7 +963,7 @@ for node in root.iterfind('.//Conversion'): #sql = """INSERT INTO "coordinate_operation" VALUES('IGNF','%s','conversion');""" % (id) #all_sql.append(sql) - sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','1262',NULL,NULL,'PROJ mill',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id,name) + sql = """INSERT INTO "conversion" VALUES('IGNF','%s','%s',NULL,NULL,'EPSG','1262','PROJ','mill','PROJ mill',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);""" % (id,name) all_sql.append(sql) mapConversionId[id] = ('IGNF', id) -- cgit v1.2.3