def osm2lulc(osmdata, nomenclature, refRaster, lulcRst, overwrite=None, dataStore=None, roadsAPI='POSTGIS'): """ Convert OSM data into Land Use/Land Cover Information A matrix based approach roadsAPI Options: * SQLITE * POSTGIS """ # ************************************************************************ # # Python Modules from Reference Packages # # ************************************************************************ # import os; import numpy; import datetime from threading import Thread from osgeo import gdal # ************************************************************************ # # Dependencies # # ************************************************************************ # from gasp.gt.fmrst import rst_to_array from gasp.gt.prop.ff import check_isRaster from gasp.gt.prop.rst import get_cellsize from gasp.gt.prop.prj import get_rst_epsg from gasp.pyt.oss import mkdir, copy_file from gasp.pyt.oss import fprop if roadsAPI == 'POSTGIS': from gasp.sql.db import create_db from gasp.gql.to.osm import osm_to_psql from gasp.sds.osm2lulc.mod2 import pg_num_roads from gasp.sql.fm import dump_db from gasp.sql.db import drop_db else: from gasp.gt.toshp.osm import osm_to_sqdb from gasp.sds.osm2lulc.mod2 import num_roads from gasp.sds.osm2lulc.utils import osm_project, add_lulc_to_osmfeat from gasp.sds.osm2lulc.utils import osmlulc_rsttbl from gasp.sds.osm2lulc.utils import get_ref_raster from gasp.sds.osm2lulc.mod1 import num_selection from gasp.sds.osm2lulc.m3_4 import num_selbyarea from gasp.sds.osm2lulc.mod5 import num_base_buffer from gasp.sds.osm2lulc.mod6 import num_assign_builds from gasp.gt.torst import obj_to_rst # ************************************************************************ # # Global Settings # # ************************************************************************ # # Check if input parameters exists! if not os.path.exists(os.path.dirname(lulcRst)): raise ValueError('{} does not exist!'.format(os.path.dirname(lulcRst))) if not os.path.exists(osmdata): raise ValueError('File with OSM DATA ({}) does not exist!'.format(osmdata)) if not os.path.exists(refRaster): raise ValueError('File with reference area ({}) does not exist!'.format(refRaster)) # Check if Nomenclature is valid nomenclature = "URBAN_ATLAS" if nomenclature != "URBAN_ATLAS" and \ nomenclature != "CORINE_LAND_COVER" and \ nomenclature == "GLOBE_LAND_30" else nomenclature time_a = datetime.datetime.now().replace(microsecond=0) workspace = os.path.join(os.path.dirname( lulcRst), 'num_osmto') if not dataStore else dataStore # Check if workspace exists: if os.path.exists(workspace): if overwrite: mkdir(workspace, overwrite=True) else: raise ValueError('Path {} already exists'.format(workspace)) else: mkdir(workspace, overwrite=None) # Get Ref Raster and EPSG refRaster, epsg = get_ref_raster(refRaster, workspace, cellsize=2) CELLSIZE = get_cellsize(refRaster, gisApi='gdal') from gasp.sds.osm2lulc import osmTableData, PRIORITIES time_b = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # Convert OSM file to SQLITE DB or to POSTGIS DB # # ************************************************************************ # if roadsAPI == 'POSTGIS': osm_db = create_db(fprop( osmdata, 'fn', forceLower=True), overwrite=True) osm_db = osm_to_psql(osmdata, osm_db) else: osm_db = osm_to_sqdb(osmdata, os.path.join(workspace, 'osm.sqlite')) time_c = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # Add Lulc Classes to OSM_FEATURES by rule # # ************************************************************************ # add_lulc_to_osmfeat(osm_db, osmTableData, nomenclature, api=roadsAPI) time_d = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # Transform SRS of OSM Data # # ************************************************************************ # osmTableData = osm_project( osm_db, epsg, api=roadsAPI, isGlobeLand=None if nomenclature != "GLOBE_LAND_30" else True ) time_e = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # MapResults # # ************************************************************************ # mergeOut = {} timeCheck = {} RULES = [1, 2, 3, 4, 5, 7] def run_rule(ruleID): time_start = datetime.datetime.now().replace(microsecond=0) _osmdb = copy_file( osm_db, os.path.splitext(osm_db)[0] + '_r{}.sqlite'.format(ruleID) ) if roadsAPI == 'SQLITE' else None # ******************************************************************** # # 1 - Selection Rule # # ******************************************************************** # if ruleID == 1: res, tm = num_selection( _osmdb if _osmdb else osm_db, osmTableData['polygons'], workspace, CELLSIZE, epsg, refRaster, api=roadsAPI ) # ******************************************************************** # # 2 - Get Information About Roads Location # # ******************************************************************** # elif ruleID == 2: res, tm = num_roads( _osmdb, nomenclature, osmTableData['lines'], osmTableData['polygons'], workspace, CELLSIZE, epsg, refRaster ) if _osmdb else pg_num_roads( osm_db, nomenclature, osmTableData['lines'], osmTableData['polygons'], workspace, CELLSIZE, epsg, refRaster ) # ******************************************************************** # # 3 - Area Upper than # # ******************************************************************** # elif ruleID == 3: if nomenclature != "GLOBE_LAND_30": res, tm = num_selbyarea( osm_db if not _osmdb else _osmdb, osmTableData['polygons'], workspace, CELLSIZE, epsg, refRaster, UPPER=True, api=roadsAPI ) else: return # ******************************************************************** # # 4 - Area Lower than # # ******************************************************************** # elif ruleID == 4: if nomenclature != "GLOBE_LAND_30": res, tm = num_selbyarea( osm_db if not _osmdb else _osmdb, osmTableData['polygons'], workspace, CELLSIZE, epsg, refRaster, UPPER=False, api=roadsAPI ) else: return # ******************************************************************** # # 5 - Get data from lines table (railway | waterway) # # ******************************************************************** # elif ruleID == 5: res, tm = num_base_buffer( osm_db if not _osmdb else _osmdb, osmTableData['lines'], workspace, CELLSIZE, epsg, refRaster, api=roadsAPI ) # ******************************************************************** # # 7 - Assign untagged Buildings to tags # # ******************************************************************** # elif ruleID == 7: if nomenclature != "GLOBE_LAND_30": res, tm = num_assign_builds( osm_db if not _osmdb else _osmdb, osmTableData['points'], osmTableData['polygons'], workspace, CELLSIZE, epsg, refRaster, apidb=roadsAPI ) else: return time_end = datetime.datetime.now().replace(microsecond=0) mergeOut[ruleID] = res timeCheck[ruleID] = {'total': time_end - time_start, 'detailed': tm} thrds = [] for r in RULES: thrds.append(Thread( name="to_{}".format(str(r)), target=run_rule, args=(r,) )) for t in thrds: t.start() for t in thrds: t.join() # Merge all results into one Raster compileResults = {} for rule in mergeOut: for cls in mergeOut[rule]: if cls not in compileResults: if type(mergeOut[rule][cls]) == list: compileResults[cls] = mergeOut[rule][cls] else: compileResults[cls] = [mergeOut[rule][cls]] else: if type(mergeOut[rule][cls]) == list: compileResults[cls] += mergeOut[rule][cls] else: compileResults[cls].append(mergeOut[rule][cls]) time_m = datetime.datetime.now().replace(microsecond=0) # All Rasters to Array arrayRst = {} for cls in compileResults: for raster in compileResults[cls]: if not raster: continue array = rst_to_array(raster) if cls not in arrayRst: arrayRst[cls] = [array.astype(numpy.uint8)] else: arrayRst[cls].append(array.astype(numpy.uint8)) time_n = datetime.datetime.now().replace(microsecond=0) # Sum Rasters of each class for cls in arrayRst: if len(arrayRst[cls]) == 1: sumArray = arrayRst[cls][0] else: sumArray = arrayRst[cls][0] for i in range(1, len(arrayRst[cls])): sumArray = sumArray + arrayRst[cls][i] arrayRst[cls] = sumArray time_o = datetime.datetime.now().replace(microsecond=0) # Apply priority rule __priorities = PRIORITIES[nomenclature + "_NUMPY"] for lulcCls in __priorities: __lulcCls = rstcls_map(lulcCls) if __lulcCls not in arrayRst: continue else: numpy.place(arrayRst[__lulcCls], arrayRst[__lulcCls] > 0, lulcCls ) for i in range(len(__priorities)): lulc_i = rstcls_map(__priorities[i]) if lulc_i not in arrayRst: continue else: for e in range(i+1, len(__priorities)): lulc_e = rstcls_map(__priorities[e]) if lulc_e not in arrayRst: continue else: numpy.place(arrayRst[lulc_e], arrayRst[lulc_i] == __priorities[i], 0 ) time_p = datetime.datetime.now().replace(microsecond=0) # Merge all rasters startCls = 'None' for i in range(len(__priorities)): lulc_i = rstcls_map(__priorities[i]) if lulc_i in arrayRst: resultSum = arrayRst[lulc_i] startCls = i break if startCls == 'None': return 'NoResults' for i in range(startCls + 1, len(__priorities)): lulc_i = rstcls_map(__priorities[i]) if lulc_i not in arrayRst: continue resultSum = resultSum + arrayRst[lulc_i] # Save Result outIsRst = check_isRaster(lulcRst) if not outIsRst: from gasp.pyt.oss import fprop lulcRst = os.path.join( os.path.dirname(lulcRst), fprop(lulcRst, 'fn') + '.tif' ) numpy.place(resultSum, resultSum==0, 1) obj_to_rst(resultSum, lulcRst, refRaster, noData=1) osmlulc_rsttbl(nomenclature + "_NUMPY", os.path.join( os.path.dirname(lulcRst), os.path.basename(lulcRst) + '.vat.dbf' )) time_q = datetime.datetime.now().replace(microsecond=0) # Dump Database if PostGIS was used # Drop Database if PostGIS was used if roadsAPI == 'POSTGIS': dump_db(osm_db, os.path.join(workspace, osm_db + '.sql'), api='psql') drop_db(osm_db) return lulcRst, { 0 : ('set_settings', time_b - time_a), 1 : ('osm_to_sqdb', time_c - time_b), 2 : ('cls_in_sqdb', time_d - time_c), 3 : ('proj_data', time_e - time_d), 4 : ('rule_1', timeCheck[1]['total'], timeCheck[1]['detailed']), 5 : ('rule_2', timeCheck[2]['total'], timeCheck[2]['detailed']), 6 : None if 3 not in timeCheck else ( 'rule_3', timeCheck[3]['total'], timeCheck[3]['detailed']), 7 : None if 4 not in timeCheck else ( 'rule_4', timeCheck[4]['total'], timeCheck[4]['detailed']), 8 : ('rule_5', timeCheck[5]['total'], timeCheck[5]['detailed']), 9 : None if 7 not in timeCheck else ( 'rule_7', timeCheck[7]['total'], timeCheck[7]['detailed']), 10 : ('rst_to_array', time_n - time_m), 11 : ('sum_cls', time_o - time_n), 12 : ('priority_rule', time_p - time_o), 13 : ('merge_rst', time_q - time_p) }
# Create ZIP Table ("CREATE TABLE zip_vistoburn AS " "SELECT rowi, coli, array_agg(pntid) AS pntid " "FROM vistoburn GROUP BY rowi, coli"), # Delete vistoburn "DROP TABLE IF EXISTS vistoburn" ] import os from gasp.sql import psql_cmd from gasp.pyt.oss import lst_ff, fprop from gasp.sql.q import exec_write_q from gasp.sql.fm import dump_db from gasp.sql.db import create_db, drop_db sqls = lst_ff(sql_fld) for sql in sqls: # Restore database new_db = create_db(fprop(sql, 'fn')) psql_cmd(new_db, sql) # Execute queries exec_write_q(new_db, QS) # Dump Database dump_db(new_db, os.path.join(outfld, os.path.basename(sql)), api='psql') # Drop Database drop_db(new_db)
def merge_dbs(destinationDb, dbs, tbls_to_merge=None, ignoreCols=None): """ Put several database into one For now works only with PostgreSQL """ import os from gasp.pyt.oss import fprop, del_file from gasp.sql import psql_cmd from gasp.sql.i import db_exists, lst_tbl from gasp.sql.db import create_db, drop_db from gasp.sql.tbl import rename_tbl, tbls_to_tbl from gasp.sql.fm import dump_tbls from gasp.sql.to import restore_tbls from gasp.sql.tbl import distinct_to_table, del_tables # Prepare database fdb = fprop(destinationDb, ['fn', 'ff']) if os.path.isfile(destinationDb): if fdb['fileformat'] == '.sql': newdb = create_db(fdb['filename'], overwrite=True, api='psql') psql_cmd(newdb, destinationDb) destinationDb = newdb else: raise ValueError(( 'destinationDb is a file but is not correct. The file must be' ' a SQL Script' )) else: # Check if destination db exists if not db_exists(destinationDb): create_db(destinationDb, overwrite=None, api='psql') # Check if dbs is a list or a dir if type(dbs) == list: dbs = dbs elif os.path.isdir(dbs): # list SQL files from gasp.pyt.oss import lst_ff dbs = lst_ff(dbs, file_format='.sql') else: raise ValueError( ''' dbs value should be a list with paths to sql files or a dir with sql files inside ''' ) TABLES = {} for i in range(len(dbs)): # Create DB DB_NAME = fprop(dbs[i], 'fn') create_db(DB_NAME, overwrite=True, api='psql') # Restore DB psql_cmd(DB_NAME, dbs[i]) # List Tables if not tbls_to_merge: tbls__ = lst_tbl(DB_NAME, excludeViews=True, api='psql') tbls = [t for t in tbls__ if t not in ignoreCols] else: tbls = tbls_to_merge # Rename Tables newTbls = rename_tbl(DB_NAME, {tbl : "{}_{}".format( tbl, str(i)) for tbl in tbls}) for t in range(len(tbls)): if tbls[t] not in TABLES: TABLES[tbls[t]] = ["{}_{}".format(tbls[t], str(i))] else: TABLES[tbls[t]].append("{}_{}".format(tbls[t], str(i))) # Dump Tables SQL_DUMP = os.path.join( os.path.dirname(dbs[i]), 'tbl_{}.sql'.format(DB_NAME) ); dump_tbls(DB_NAME, newTbls, SQL_DUMP) # Restore Tables in the destination Database restore_tbls(destinationDb, SQL_DUMP, newTbls) # Delete Temp Database drop_db(DB_NAME) # Delete SQL File del_file(SQL_DUMP) # Union of all tables max_len = max([len(TABLES[t]) for t in TABLES]) for tbl in TABLES: # Rename original table NEW_TBL = "{}_{}".format(tbl, max_len) rename_tbl(destinationDb, {tbl : NEW_TBL}) TABLES[tbl].append(NEW_TBL) # Union tbls_to_tbl(destinationDb, TABLES[tbl], tbl + '_tmp') # Group By distinct_to_table(destinationDb, tbl + '_tmp', tbl, cols=None) # Drop unwanted tables del_tables(destinationDb, TABLES[tbl] + [tbl + '_tmp']) return destinationDb
def raster_based(osmdata, nomenclature, refRaster, lulcRst, overwrite=None, dataStore=None, roadsAPI='POSTGIS'): """ Convert OSM Data into Land Use/Land Cover Information An raster based approach. TODO: Add detailed description """ # ************************************************************************ # # Python Modules from Reference Packages # # ************************************************************************ # import datetime import os import pandas import copy # ************************************************************************ # # Gasp dependencies # # ************************************************************************ # from gasp.pyt.oss import mkdir, fprop from gasp.gt.prop.ff import check_isRaster from gasp.gt.prop.prj import get_rst_epsg from gasp.gt.wenv.grs import run_grass if roadsAPI == 'POSTGIS': from gasp.sql.db import create_db from gasp.gql.to.osm import osm_to_psql from gasp.sds.osm2lulc.mod2 import roads_sqdb from gasp.sql.fm import dump_db from gasp.sql.db import drop_db else: from gasp.gt.toshp.osm import osm_to_sqdb from gasp.sds.osm2lulc.mod2 import grs_rst_roads from gasp.sds.osm2lulc.utils import osm_project, add_lulc_to_osmfeat, osmlulc_rsttbl from gasp.sds.osm2lulc.utils import get_ref_raster from gasp.sds.osm2lulc.mod1 import grs_rst from gasp.sds.osm2lulc.m3_4 import rst_area from gasp.sds.osm2lulc.mod5 import basic_buffer from gasp.sds.osm2lulc.mod6 import rst_pnt_to_build # ************************************************************************ # # Global Settings # # ************************************************************************ # # Check if input parameters exists! if not os.path.exists(os.path.dirname(lulcRst)): raise ValueError('{} does not exist!'.format(os.path.dirname(lulcRst))) if not os.path.exists(osmdata): raise ValueError( 'File with OSM DATA ({}) does not exist!'.format(osmdata)) if not os.path.exists(refRaster): raise ValueError( 'File with reference area ({}) does not exist!'.format(refRaster)) # Check if Nomenclature is valid nomenclature = "URBAN_ATLAS" if nomenclature != "URBAN_ATLAS" and \ nomenclature != "CORINE_LAND_COVER" and \ nomenclature == "GLOBE_LAND_30" else nomenclature time_a = datetime.datetime.now().replace(microsecond=0) workspace = os.path.join(os.path.dirname(lulcRst), 'osmtolulc') if not dataStore else dataStore # Check if workspace exists if os.path.exists(workspace): if overwrite: mkdir(workspace) else: raise ValueError('Path {} already exists'.format(workspace)) else: mkdir(workspace) # Get Ref Raster refRaster, epsg = get_ref_raster(refRaster, workspace, cellsize=2) from gasp.sds.osm2lulc import PRIORITIES, osmTableData, LEGEND __priorites = PRIORITIES[nomenclature] __legend = LEGEND[nomenclature] time_b = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # Convert OSM file to SQLITE DB or to POSTGIS DB # # ************************************************************************ # if roadsAPI == 'POSTGIS': osm_db = create_db(fprop(osmdata, 'fn', forceLower=True), overwrite=True) osm_db = osm_to_psql(osmdata, osm_db) else: osm_db = osm_to_sqdb(osmdata, os.path.join(workspace, 'osm.sqlite')) time_c = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # Add Lulc Classes to OSM_FEATURES by rule # # ************************************************************************ # add_lulc_to_osmfeat(osm_db, osmTableData, nomenclature, api=roadsAPI) time_d = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # Transform SRS of OSM Data # # ************************************************************************ # osmTableData = osm_project( osm_db, epsg, api=roadsAPI, isGlobeLand=None if nomenclature != 'GLOBE_LAND_30' else True) time_e = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # Start a GRASS GIS Session # # ************************************************************************ # grass_base = run_grass(workspace, grassBIN='grass78', location='grloc', srs=epsg) import grass.script as grass import grass.script.setup as gsetup gsetup.init(grass_base, workspace, 'grloc', 'PERMANENT') # ************************************************************************ # # IMPORT SOME GASP MODULES FOR GRASS GIS # # ************************************************************************ # from gasp.gt.torst import rst_to_grs, grs_to_rst from gasp.gt.nop.mos import rsts_to_mosaic from gasp.gt.wenv.grs import rst_to_region # ************************************************************************ # # SET GRASS GIS LOCATION EXTENT # # ************************************************************************ # extRst = rst_to_grs(refRaster, 'extent_raster') rst_to_region(extRst) time_f = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # MapResults # mergeOut = {} # ************************************************************************ # # ************************************************************************ # # 1 - Selection Rule # # ************************************************************************ # """ selOut = { cls_code : rst_name, ... } """ selOut, timeCheck1 = grs_rst(osm_db, osmTableData['polygons'], api=roadsAPI) for cls in selOut: mergeOut[cls] = [selOut[cls]] time_g = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # 2 - Get Information About Roads Location # # ************************************************************************ # """ roads = { cls_code : rst_name, ... } """ if roadsAPI != 'POSTGIS': roads, timeCheck2 = grs_rst_roads( osm_db, osmTableData['lines'], osmTableData['polygons'], workspace, 1221 if nomenclature != "GLOBE_LAND_30" else 801) else: roadCls = 1221 if nomenclature != "GLOBE_LAND_30" else 801 roads, timeCheck2 = roads_sqdb(osm_db, osmTableData['lines'], osmTableData['polygons'], apidb='POSTGIS', asRst=roadCls) roads = {roadCls: roads} for cls in roads: if cls not in mergeOut: mergeOut[cls] = [roads[cls]] else: mergeOut[cls].append(roads[cls]) time_h = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # 3 - Area Upper than # # ************************************************************************ # """ auOut = { cls_code : rst_name, ... } """ if nomenclature != 'GLOBE_LAND_30': auOut, timeCheck3 = rst_area(osm_db, osmTableData['polygons'], UPPER=True, api=roadsAPI) for cls in auOut: if cls not in mergeOut: mergeOut[cls] = [auOut[cls]] else: mergeOut[cls].append(auOut[cls]) time_l = datetime.datetime.now().replace(microsecond=0) else: timeCheck3 = None time_l = None # ************************************************************************ # # 4 - Area Lower than # # ************************************************************************ # """ alOut = { cls_code : rst_name, ... } """ if nomenclature != 'GLOBE_LAND_30': alOut, timeCheck4 = rst_area(osm_db, osmTableData['polygons'], UPPER=None, api=roadsAPI) for cls in alOut: if cls not in mergeOut: mergeOut[cls] = [alOut[cls]] else: mergeOut[cls].append(alOut[cls]) time_j = datetime.datetime.now().replace(microsecond=0) else: timeCheck4 = None time_j = None # ************************************************************************ # # 5 - Get data from lines table (railway | waterway) # # ************************************************************************ # """ bfOut = { cls_code : rst_name, ... } """ bfOut, timeCheck5 = basic_buffer(osm_db, osmTableData['lines'], workspace, apidb=roadsAPI) for cls in bfOut: if cls not in mergeOut: mergeOut[cls] = [bfOut[cls]] else: mergeOut[cls].append(bfOut[cls]) time_m = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # 7 - Assign untagged Buildings to tags # # ************************************************************************ # if nomenclature != "GLOBE_LAND_30": buildsOut, timeCheck7 = rst_pnt_to_build(osm_db, osmTableData['points'], osmTableData['polygons'], api_db=roadsAPI) for cls in buildsOut: if cls not in mergeOut: mergeOut[cls] = buildsOut[cls] else: mergeOut[cls] += buildsOut[cls] time_n = datetime.datetime.now().replace(microsecond=0) else: timeCheck7 = None time_n = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # Produce LULC Map # # ************************************************************************ # """ Merge all results for one cls into one raster mergeOut = { cls_code : [rst_name, rst_name, ...], ... } into mergeOut = { cls_code : patched_raster, ... } """ for cls in mergeOut: if len(mergeOut[cls]) == 1: mergeOut[cls] = mergeOut[cls][0] else: mergeOut[cls] = rsts_to_mosaic(mergeOut[cls], 'mosaic_{}'.format(str(cls)), api="grass") time_o = datetime.datetime.now().replace(microsecond=0) """ Merge all Class Raster using a priority rule """ __priorities = PRIORITIES[nomenclature] lst_rst = [] for cls in __priorities: if cls not in mergeOut: continue else: lst_rst.append(mergeOut[cls]) outGrs = rsts_to_mosaic(lst_rst, os.path.splitext(os.path.basename(lulcRst))[0], api="grass") time_p = datetime.datetime.now().replace(microsecond=0) # Ceck if lulc Rst has an valid format outIsRst = check_isRaster(lulcRst) if not outIsRst: from gasp.pyt.oss import fprop lulcRst = os.path.join(os.path.dirname(lulcRst), fprop(lulcRst, 'fn') + '.tif') grs_to_rst(outGrs, lulcRst, as_cmd=True) osmlulc_rsttbl( nomenclature, os.path.join(os.path.dirname(lulcRst), os.path.basename(lulcRst) + '.vat.dbf')) time_q = datetime.datetime.now().replace(microsecond=0) # Dump Database if PostGIS was used # Drop Database if PostGIS was used if roadsAPI == 'POSTGIS': dump_db(osm_db, os.path.join(workspace, osm_db + '.sql'), api='psql') drop_db(osm_db) return lulcRst, { 0: ('set_settings', time_b - time_a), 1: ('osm_to_sqdb', time_c - time_b), 2: ('cls_in_sqdb', time_d - time_c), 3: ('proj_data', time_e - time_d), 4: ('set_grass', time_f - time_e), 5: ('rule_1', time_g - time_f, timeCheck1), 6: ('rule_2', time_h - time_g, timeCheck2), 7: None if not timeCheck3 else ('rule_3', time_l - time_h, timeCheck3), 8: None if not timeCheck4 else ('rule_4', time_j - time_l, timeCheck4), 9: ('rule_5', time_m - time_j if timeCheck4 else time_m - time_h, timeCheck5), 10: None if not timeCheck7 else ('rule_7', time_n - time_m, timeCheck7), 11: ('merge_rst', time_o - time_n), 12: ('priority_rule', time_p - time_o), 13: ('export_rst', time_q - time_p) }
def vector_based(osmdata, nomenclature, refRaster, lulcShp, overwrite=None, dataStore=None, RoadsAPI='POSTGIS'): """ Convert OSM Data into Land Use/Land Cover Information An vector based approach. TODO: Add a detailed description. RoadsAPI Options: * GRASS * SQLITE * POSTGIS """ # ************************************************************************ # # Python Modules from Reference Packages # # ************************************************************************ # import datetime import os import copy # ************************************************************************ # # GASP dependencies # # ************************************************************************ # from gasp.pyt.oss import fprop, mkdir from gasp.gt.wenv.grs import run_grass if RoadsAPI == 'POSTGIS': from gasp.sql.db import create_db from gasp.gql.to.osm import osm_to_psql from gasp.sql.db import drop_db from gasp.sql.fm import dump_db else: from gasp.gt.toshp.osm import osm_to_sqdb from gasp.sds.osm2lulc.utils import osm_project, add_lulc_to_osmfeat, get_ref_raster from gasp.gt.toshp.mtos import shps_to_shp from gasp.sds.osm2lulc.mod1 import grs_vector if RoadsAPI == 'SQLITE' or RoadsAPI == 'POSTGIS': from gasp.sds.osm2lulc.mod2 import roads_sqdb else: from gasp.sds.osm2lulc.mod2 import grs_vec_roads from gasp.sds.osm2lulc.m3_4 import grs_vect_selbyarea from gasp.sds.osm2lulc.mod5 import grs_vect_bbuffer from gasp.sds.osm2lulc.mod6 import vector_assign_pntags_to_build from gasp.gt.toshp.mtos import same_attr_to_shp from gasp.gt.prj import def_prj # ************************************************************************ # # Global Settings # # ************************************************************************ # # Check if input parameters exists! if not os.path.exists(os.path.dirname(lulcShp)): raise ValueError('{} does not exist!'.format(os.path.dirname(lulcShp))) if not os.path.exists(osmdata): raise ValueError( 'File with OSM DATA ({}) does not exist!'.format(osmdata)) if not os.path.exists(refRaster): raise ValueError( 'File with reference area ({}) does not exist!'.format(refRaster)) # Check if Nomenclature is valid nomenclature = "URBAN_ATLAS" if nomenclature != "URBAN_ATLAS" and \ nomenclature != "CORINE_LAND_COVER" and \ nomenclature == "GLOBE_LAND_30" else nomenclature time_a = datetime.datetime.now().replace(microsecond=0) # Create workspace for temporary files workspace = os.path.join(os.path.dirname(lulcShp), 'osmtolulc') if not dataStore else dataStore # Check if workspace exists if os.path.exists(workspace): if overwrite: mkdir(workspace) else: raise ValueError('Path {} already exists'.format(workspace)) else: mkdir(workspace) # Get Reference Raster refRaster, epsg = get_ref_raster(refRaster, workspace, cellsize=10) from gasp.sds.osm2lulc import osmTableData, PRIORITIES, LEGEND __priorities = PRIORITIES[nomenclature] __legend = LEGEND[nomenclature] time_b = datetime.datetime.now().replace(microsecond=0) if RoadsAPI != 'POSTGIS': # ******************************************************************** # # Convert OSM file to SQLITE DB # # ******************************************************************** # osm_db = osm_to_sqdb(osmdata, os.path.join(workspace, 'osm.sqlite')) else: # Convert OSM file to POSTGRESQL DB # osm_db = create_db(fprop(osmdata, 'fn', forceLower=True), overwrite=True) osm_db = osm_to_psql(osmdata, osm_db) time_c = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # Add Lulc Classes to OSM_FEATURES by rule # # ************************************************************************ # add_lulc_to_osmfeat(osm_db, osmTableData, nomenclature, api='SQLITE' if RoadsAPI != 'POSTGIS' else RoadsAPI) time_d = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # Transform SRS of OSM Data # # ************************************************************************ # osmTableData = osm_project( osm_db, epsg, api='SQLITE' if RoadsAPI != 'POSTGIS' else RoadsAPI, isGlobeLand=None if nomenclature != 'GLOBE_LAND_30' else True) time_e = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # Start a GRASS GIS Session # # ************************************************************************ # grass_base = run_grass(workspace, grassBIN='grass78', location='grloc', srs=epsg) #import grass.script as grass import grass.script.setup as gsetup gsetup.init(grass_base, workspace, 'grloc', 'PERMANENT') # ************************************************************************ # # IMPORT SOME GASP MODULES FOR GRASS GIS # # ************************************************************************ # from gasp.gt.gop.ovlay import erase from gasp.gt.wenv.grs import rst_to_region from gasp.gt.gop.genze import dissolve from gasp.gt.tbl.grs import add_and_update, reset_table, update_table from gasp.gt.tbl.fld import add_fields from gasp.gt.toshp.cff import shp_to_grs, grs_to_shp from gasp.gt.torst import rst_to_grs # ************************************************************************ # # SET GRASS GIS LOCATION EXTENT # # ************************************************************************ # extRst = rst_to_grs(refRaster, 'extent_raster') rst_to_region(extRst) time_f = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # MapResults # # ************************************************************************ # osmShps = [] # ************************************************************************ # # 1 - Selection Rule # # ************************************************************************ # ruleOneShp, timeCheck1 = grs_vector(osm_db, osmTableData['polygons'], apidb=RoadsAPI) osmShps.append(ruleOneShp) time_g = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # 2 - Get Information About Roads Location # # ************************************************************************ # ruleRowShp, timeCheck2 = roads_sqdb( osm_db, osmTableData['lines'], osmTableData['polygons'], apidb=RoadsAPI ) if RoadsAPI == 'SQLITE' or RoadsAPI == 'POSTGIS' else grs_vec_roads( osm_db, osmTableData['lines'], osmTableData['polygons']) osmShps.append(ruleRowShp) time_h = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # 3 - Area Upper than # # ************************************************************************ # if nomenclature != "GLOBE_LAND_30": ruleThreeShp, timeCheck3 = grs_vect_selbyarea(osm_db, osmTableData['polygons'], UPPER=True, apidb=RoadsAPI) osmShps.append(ruleThreeShp) time_l = datetime.datetime.now().replace(microsecond=0) else: timeCheck3 = None time_l = None # ************************************************************************ # # 4 - Area Lower than # # ************************************************************************ # if nomenclature != "GLOBE_LAND_30": ruleFourShp, timeCheck4 = grs_vect_selbyarea(osm_db, osmTableData['polygons'], UPPER=False, apidb=RoadsAPI) osmShps.append(ruleFourShp) time_j = datetime.datetime.now().replace(microsecond=0) else: timeCheck4 = None time_j = None # ************************************************************************ # # 5 - Get data from lines table (railway | waterway) # # ************************************************************************ # ruleFiveShp, timeCheck5 = grs_vect_bbuffer(osm_db, osmTableData["lines"], api_db=RoadsAPI) osmShps.append(ruleFiveShp) time_m = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # 7 - Assign untagged Buildings to tags # # ************************************************************************ # if nomenclature != "GLOBE_LAND_30": ruleSeven11, ruleSeven12, timeCheck7 = vector_assign_pntags_to_build( osm_db, osmTableData['points'], osmTableData['polygons'], apidb=RoadsAPI) if ruleSeven11: osmShps.append(ruleSeven11) if ruleSeven12: osmShps.append(ruleSeven12) time_n = datetime.datetime.now().replace(microsecond=0) else: timeCheck7 = None time_n = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # Produce LULC Map # # ************************************************************************ # """ Get Shps with all geometries related with one class - One Shape for Classe """ _osmShps = [] for i in range(len(osmShps)): if not osmShps[i]: continue _osmShps.append( grs_to_shp(osmShps[i], os.path.join(workspace, osmShps[i] + '.shp'), 'auto', lyrN=1, asCMD=True, asMultiPart=None)) for shp in _osmShps: def_prj(os.path.splitext(shp)[0] + '.prj', epsg=epsg, api='epsgio') _osmShps = same_attr_to_shp(_osmShps, "cat", workspace, "osm_", resultDict=True) del osmShps time_o = datetime.datetime.now().replace(microsecond=0) """ Merge all Classes into one feature class using a priority rule """ osmShps = {} for cls in _osmShps: if cls == '1': osmShps[1221] = shp_to_grs(_osmShps[cls], "osm_1221", asCMD=True) else: osmShps[int(cls)] = shp_to_grs(_osmShps[cls], "osm_" + cls, asCMD=True) # Erase overlapping areas by priority osmNameRef = copy.deepcopy(osmShps) for e in range(len(__priorities)): if e + 1 == len(__priorities): break if __priorities[e] not in osmShps: continue else: for i in range(e + 1, len(__priorities)): if __priorities[i] not in osmShps: continue else: osmShps[__priorities[i]] = erase( osmShps[__priorities[i]], osmShps[__priorities[e]], "{}_{}".format(osmNameRef[__priorities[i]], e), notTbl=True, api='pygrass') time_p = datetime.datetime.now().replace(microsecond=0) # Export all classes lst_merge = [] a = None for i in range(len(__priorities)): if __priorities[i] not in osmShps: continue if not a: reset_table(osmShps[__priorities[i]], { 'cls': 'varchar(5)', 'leg': 'varchar(75)' }, { 'cls': str(__priorities[i]), 'leg': str(__legend[__priorities[i]]) }) a = 1 else: add_and_update(osmShps[__priorities[i]], {'cls': 'varchar(5)'}, {'cls': str(__priorities[i])}) ds = dissolve(osmShps[__priorities[i]], 'dl_{}'.format(str(__priorities[i])), 'cls', api="grass") add_fields(ds, {'leg': 'varchar(75)'}, api="grass") update_table(ds, 'leg', str(__legend[__priorities[i]]), 'leg is null') lst_merge.append( grs_to_shp(ds, os.path.join(workspace, "lulc_{}.shp".format( str(__priorities[i]))), 'auto', lyrN=1, asCMD=True, asMultiPart=None)) time_q = datetime.datetime.now().replace(microsecond=0) if fprop(lulcShp, 'ff') != '.shp': lulcShp = os.path.join(os.path.dirname(lulcShp), fprop(lulcShp, 'fn') + '.shp') shps_to_shp(lst_merge, lulcShp, api='pandas') # Check if prj of lulcShp exists and create it if necessary prj_ff = os.path.splitext(lulcShp)[0] + '.prj' if not os.path.exists(prj_ff): def_prj(prj_ff, epsg=epsg, api='epsgio') time_r = datetime.datetime.now().replace(microsecond=0) # Dump Database if PostGIS was used # Drop Database if PostGIS was used if RoadsAPI == 'POSTGIS': dump_db(osm_db, os.path.join(workspace, osm_db + '.sql'), api='psql') drop_db(osm_db) return lulcShp, { 0: ('set_settings', time_b - time_a), 1: ('osm_to_sqdb', time_c - time_b), 2: ('cls_in_sqdb', time_d - time_c), 3: ('proj_data', time_e - time_d), 4: ('set_grass', time_f - time_e), 5: ('rule_1', time_g - time_f, timeCheck1), 6: ('rule_2', time_h - time_g, timeCheck2), 7: None if not timeCheck3 else ('rule_3', time_l - time_h, timeCheck3), 8: None if not timeCheck4 else ('rule_4', time_j - time_l, timeCheck4), 9: ('rule_5', time_m - time_j if timeCheck4 else time_m - time_h, timeCheck5), 10: None if not timeCheck7 else ('rule_7', time_n - time_m, timeCheck7), 11: ('disj_cls', time_o - time_n), 12: ('priority_rule', time_p - time_o), 13: ('export_cls', time_q - time_p), 14: ('merge_cls', time_r - time_q) }
def psql_to_djgdb(sql_dumps, db_name, djg_proj=None, mapTbl=None, userDjgAPI=None): """ Import PGSQL database in a SQL Script into the database controlled by one Django Project To work, the name of a model instance of type foreign key should be equal to the name of the 'db_column' clause. """ import os from gasp import __import from gasp.pyt import obj_to_lst from gasp.sql.to import restore_tbls from gasp.sql.db import create_db, drop_db from gasp.sql.i import lst_tbl from gasp.sql.fm import q_to_obj from gasp.web.djg.mdl.rel import order_mdl_by_rel from gasp.web.djg.mdl.i import lst_mdl_proj # Global variables TABLES_TO_EXCLUDE = [ 'geography_columns', 'geometry_columns', 'spatial_ref_sys', 'raster_columns', 'raster_columns', 'raster_overviews', 'pointcloud_formats', 'pointcloud_columns' ] # Several SQL Files are expected sql_scripts = obj_to_lst(sql_dumps) # Create Database tmp_db_name = db_name + '_xxxtmp' create_db(tmp_db_name) # Restore tables in SQL files for sql in sql_scripts: restore_tbls(tmp_db_name, sql) # List tables in the database tables = [x for x in lst_tbl(tmp_db_name, excludeViews=True, api='psql') ] if not mapTbl else mapTbl # Open Django Project if djg_proj: from gasp.web.djg import open_Django_Proj application = open_Django_Proj(djg_proj) # List models in project app_mdls = lst_mdl_proj(djg_proj, thereIsApp=True, returnClassName=True) data_tbl = {} for t in tables: if t == 'auth_user' or t == 'auth_group' or t == 'auth_user_groups': data_tbl[t] = t elif t.startswith('auth') or t.startswith('django'): continue elif t not in app_mdls or t in TABLES_TO_EXCLUDE: continue else: data_tbl["{}.models.{}".format(t.split('_')[0], app_mdls[t])] = t from django.contrib.gis.db import models mdl_cls = [ "{}.models.{}".format(m.split('_')[0], app_mdls[m]) for m in app_mdls ] orderned_table = order_mdl_by_rel(mdl_cls) # Add default tables of Django def_djg_tbl = [] if 'auth_group' in data_tbl: def_djg_tbl.append('auth_group') if 'auth_user' in data_tbl: def_djg_tbl.append('auth_user') if 'auth_user_groups' in data_tbl: def_djg_tbl.append('auth_user_groups') orderned_table = def_djg_tbl + orderned_table if userDjgAPI: for table in orderned_table: # Map pgsql table data tableData = q_to_obj(tmp_db_name, data_tbl[table], of='dict') # Table data to Django Model if table == 'auth_user': mdl_cls = __import('django.contrib.auth.models.User') elif table == 'auth_group': mdl_cls = __import('django.contrib.auth.models.Group') else: mdl_cls = __import(table) __mdl = mdl_cls() for row in tableData: for col in row: # Check if field is a foreign key field_obj = mdl_cls._meta.get_field(col) if not isinstance(field_obj, models.ForeignKey): # If not, use the value # But first check if value is nan (special type of float) if row[col] != row[col]: row[col] = None setattr(__mdl, col, row[col]) else: # If yes, use the model instance of the related table # Get model of the table related com aquela cujos dados # estao a ser restaurados related_name = field_obj.related_model.__name__ related_model = __import('{a}.models.{m}'.format( a=table.split('_')[0], m=related_name)) # If NULL, continue if not row[col]: setattr(__mdl, col, row[col]) continue related_obj = related_model.objects.get( pk=int(row[col])) setattr(__mdl, col, related_obj) __mdl.save() else: import json import pandas as pd from gasp.sql.fm import q_to_obj from gasp.sql.to import df_to_db for tbl in orderned_table: if tbl not in data_tbl: continue data = q_to_obj(tmp_db_name, "SELECT * FROM {}".format(data_tbl[tbl])) if tbl == 'auth_user': data['last_login'] = pd.to_datetime(data.last_login, utc=True) data['date_joined'] = pd.to_datetime(data.date_joined, utc=True) df_to_db(db_name, data, data_tbl[tbl], append=True) drop_db(tmp_db_name)