def check_autofc_overlap(checkShp, epsg, conParam, outOverlaps): """ Check if the features of one Feature Class overlaps each other """ import os from gasp.sql.mng.db import create_db from gasp.sql.mng.qw import ntbl_by_query from gasp.to.sql import shp_to_psql_tbl from gasp.to.shp import psql_to_shp create_db(conParam, conParam["DB"]) conParam["DATABASE"] = conParam["DB"] # Send data to postgresql table = shp_to_psql(conParam, checkShp, epsg, api="pandas") # Produce result q = ( "SELECT foo.* FROM (" "SELECT * FROM {t}" ") AS foo, (" "SELECT cat AS relcat, geom AS tst_geom FROM {t}" ") AS foo2 " "WHERE (" "ST_Overlaps(geom, tst_geom) IS TRUE OR " "ST_Equals(geom, tst_geom) IS TRUE OR " "ST_Contains(geom, tst_geom) IS TRUE" ") AND cat <> relcat" ).format(t=table) resultTable = os.path.splitext(os.path.basename(outOverlaps))[0] ntbl_by_query(conParam, resultTable, q, api='psql') psql_to_shp(conParam, resultTable, outOverlaps, api='pandas', epsg=epsg) return outOverlaps
def db_to_db(conDBA, conDBB, typeDBA, typeDBB): """ All tables in one Database to other database typesDB options: * sqlite * psql """ import os from gasp.fm.sql import query_to_df from gasp.sql.mng.tbl import lst_tbl from gasp.sql.mng.db import create_db # List Tables in DB A tbls = lst_tbl(conDBA, excludeViews=True, api=typeDBA) # Create database B if typeDBB == 'psql': con_param = { "HOST": conDBB["HOST"], "USER": conDBB["USER"], "PORT": conDBB["PORT"], "PASSWORD": conDBB["PASSWORD"] } if "TEMPLATE" in conDBB: con_param["TEMPLATE"] = conDBB["TEMPLATE"] NEW_DB = conDBB["DATABASE"] else: con_param = os.path.dirname(conDBB) NEW_DB = os.path.basename(conDBB) db_b = create_db(con_param, NEW_DB, overwrite=False, api=typeDBB) # Table to Database B for tbl in tbls: df = query_to_df(conDBA, "SELECT * FROM {}".format(tbl), db_api=typeDBA) df_to_db(conDBB, df, tbl, append=None, api=typeDBB)
def v_break_at_points(workspace, loc, lineShp, pntShp, conParam, srs, out_correct, out_tocorrect): """ Break lines at points - Based on GRASS GIS v.edit Use PostGIS to sanitize the result TODO: Confirm utility """ import os from gasp.session import run_grass from gasp.sql.mng.db import create_db from gasp.to.sql import shp_to_psql from gasp.to.shp import psql_to_shp from gasp.sql.mng.qw import ntbl_by_query from gasp.oss import get_filename tmpFiles = os.path.join(workspace, loc) gbase = run_grass(workspace, location=loc, srs=srs) import grass.script as grass import grass.script.setup as gsetup gsetup.init(gbase, workspace, loc, 'PERMANENT') from gasp.to.shp.grs import shp_to_grs, grs_to_shp grsLine = shp_to_grs( lineShp, get_filename(lineShp, forceLower=True) ) vedit_break(grsLine, pntShp, geomType='line') LINES = grass_converter( grsLine, os.path.join(tmpFiles, grsLine + '_v1.shp'), 'line') # Sanitize output of v.edit.break using PostGIS create_db(conParam, conParam["DB"], overwrite=True) conParam["DATABASE"] = conParam["DB"] LINES_TABLE = shp_to_psql( conParam, LINES, srs, pgTable=get_filename(LINES, forceLower=True), api="shp2pgsql" ) # Delete old/original lines and stay only with the breaked one Q = ( "SELECT {t}.*, foo.cat_count FROM {t} INNER JOIN (" "SELECT cat, COUNT(cat) AS cat_count, " "MAX(ST_Length(geom)) AS max_len " "FROM {t} GROUP BY cat" ") AS foo ON {t}.cat = foo.cat " "WHERE foo.cat_count = 1 OR foo.cat_count = 2 OR (" "foo.cat_count = 3 AND ST_Length({t}.geom) <= foo.max_len)" ).format(t=LINES_TABLE) CORR_LINES = ntbl_by_query( conParam, "{}_corrected".format(LINES_TABLE), Q, api='psql' ) # TODO: Delete Rows that have exactly the same geometry # Highlight problems that the user must solve case by case Q = ( "SELECT {t}.*, foo.cat_count FROM {t} INNER JOIN (" "SELECT cat, COUNT(cat) AS cat_count FROM {t} GROUP BY cat" ") AS foo ON {t}.cat = foo.cat " "WHERE foo.cat_count > 3" ).format(t=LINES_TABLE) ERROR_LINES = ntbl_by_query( conParam, "{}_not_corr".format(LINES_TABLE), Q, api='psql' ) psql_to_shp( conParam, CORR_LINES, out_correct, api="pgsql2shp", geom_col="geom" ) psql_to_shp( conParam, ERROR_LINES, out_tocorrect, api="pgsql2shp", geom_col="geom" )
def raster_based(osmdata, nomenclature, refRaster, lulcRst, overwrite=None, dataStore=None, roadsAPI='SQLITE'): """ 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 json # ************************************************************************ # # Gasp dependencies # # ************************************************************************ # from gasp.oss.ops import create_folder from gasp.prop.rst import get_epsg_raster from gasp.session import run_grass if roadsAPI == 'POSTGIS': from gasp.sql.mng.db import create_db from gasp.osm2lulc.utils import osm_to_pgsql from gasp.osm2lulc.mod2 import roads_sqdb else: from gasp.osm2lulc.utils import osm_to_sqdb from gasp.osm2lulc.mod2 import grs_rst_roads from gasp.osm2lulc.utils import osm_project, add_lulc_to_osmfeat from gasp.osm2lulc.mod1 import grs_rst from gasp.osm2lulc.m3_4 import rst_area from gasp.osm2lulc.mod5 import basic_buffer from gasp.osm2lulc.mod6 import rst_pnt_to_build # ************************************************************************ # # Global Settings # # ************************************************************************ # if not os.path.exists(os.path.dirname(lulcRst)): raise ValueError('{} does not exist!'.format(os.path.dirname(lulcRst))) # Get EPSG of Reference Raster epsg = get_epsg_raster(refRaster) if not epsg: raise ValueError('Cannot get epsg code of ref raster') # Get Parameters to connect to PostgreSQL conPGSQL = json.load( open( os.path.join(os.path.dirname(os.path.abspath(__file__)), 'con-postgresql.json'), 'r')) if roadsAPI == 'POSTGIS' else None time_a = datetime.datetime.now().replace(microsecond=0) from gasp.osm2lulc.var import PRIORITIES, osmTableData 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: create_folder(workspace) else: raise ValueError('Path {} already exists'.format(workspace)) else: create_folder(workspace) __priorites = PRIORITIES[nomenclature] time_b = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # Convert OSM file to SQLITE DB or to POSTGIS DB # # ************************************************************************ # if roadsAPI == 'POSTGIS': conPGSQL["DATABASE"] = create_db(conPGSQL, os.path.splitext( os.path.basename(osmdata))[0], overwrite=True) osm_db = osm_to_pgsql(osmdata, conPGSQL) 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(conPGSQL if roadsAPI == 'POSTGIS' else osm_db, osmTableData, nomenclature, api=roadsAPI) time_d = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # Transform SRS of OSM Data # # ************************************************************************ # osmTableData = osm_project(conPGSQL if roadsAPI == 'POSTGIS' else osm_db, epsg, api=roadsAPI) time_e = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # Start a GRASS GIS Session # # ************************************************************************ # grass_base = run_grass(workspace, grassBIN='grass76', 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.to.rst import rst_to_grs, grs_to_rst from gasp.cpu.grs.spanlst import mosaic_raster from gasp.prop.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(conPGSQL if roadsAPI == 'POSTGIS' else 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(conPGSQL, 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, ... } """ auOut, timeCheck3 = rst_area(conPGSQL if roadsAPI == 'POSTGIS' else 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) # ************************************************************************ # # 4 - Area Lower than # # ************************************************************************ # """ alOut = { cls_code : rst_name, ... } """ alOut, timeCheck4 = rst_area(conPGSQL if roadsAPI == 'POSTGIS' else 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) # ************************************************************************ # # 5 - Get data from lines table (railway | waterway) # # ************************************************************************ # """ bfOut = { cls_code : rst_name, ... } """ bfOut, timeCheck5 = basic_buffer( conPGSQL if roadsAPI == 'POSTGIS' else 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( conPGSQL if roadsAPI == 'POSTGIS' else 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] = mosaic_raster(mergeOut[cls], 'mosaic_{}'.format(str(cls)), asCmd=True) 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 = mosaic_raster(lst_rst, os.path.splitext(os.path.basename(lulcRst))[0], asCmd=True) time_p = datetime.datetime.now().replace(microsecond=0) grs_to_rst(outGrs, lulcRst, as_cmd=True) time_q = datetime.datetime.now().replace(microsecond=0) 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: ('rule_3', time_l - time_h, timeCheck3), 8: ('rule_4', time_j - time_l, timeCheck4), 9: ('rule_5', time_m - time_j, 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='SQLITE'): """ Convert OSM Data into Land Use/Land Cover Information An vector based approach. TODO: Add a detailed description. RoadsAPI Options: * SQLITE * POSTGIS """ # ************************************************************************ # # Python Modules from Reference Packages # # ************************************************************************ # import datetime import os import json # ************************************************************************ # # GASP dependencies # # ************************************************************************ # from gasp.oss.ops import create_folder from gasp.prop.rst import get_epsg_raster from gasp.session import run_grass if RoadsAPI == 'POSTGIS': from gasp.sql.mng.db import create_db from gasp.osm2lulc.utils import osm_to_pgsql else: from gasp.osm2lulc.utils import osm_to_sqdb from gasp.osm2lulc.utils import osm_project, add_lulc_to_osmfeat from gasp.mng.gen import merge_feat from gasp.osm2lulc.mod1 import grs_vector if RoadsAPI == 'SQLITE' or RoadsAPI == 'POSTGIS': from gasp.osm2lulc.mod2 import roads_sqdb else: from gasp.osm2lulc.mod2 import grs_vec_roads from gasp.osm2lulc.m3_4 import grs_vect_selbyarea from gasp.osm2lulc.mod5 import grs_vect_bbuffer from gasp.osm2lulc.mod6 import vector_assign_pntags_to_build # ************************************************************************ # # Global Settings # # ************************************************************************ # if not os.path.exists(os.path.dirname(lulcShp)): raise ValueError('{} does not exist!'.format(os.path.dirname(lulcShp))) # Get Parameters to connect to PostgreSQL conPGSQL = json.load( open( os.path.join(os.path.dirname(os.path.abspath(__file__)), 'con-postgresql.json'), 'r')) if RoadsAPI == 'POSTGIS' else None # Get EPSG of Reference Raster epsg = get_epsg_raster(refRaster) if not epsg: raise ValueError('Cannot get epsg code of ref raster') time_a = datetime.datetime.now().replace(microsecond=0) from gasp.osm2lulc.var import osmTableData, PRIORITIES 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: create_folder(workspace) else: raise ValueError('Path {} already exists'.format(workspace)) else: create_folder(workspace) __priorities = PRIORITIES[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 # conPGSQL["DATABASE"] = create_db(conPGSQL, os.path.splitext( os.path.basename(osmdata))[0], overwrite=True) osm_db = osm_to_pgsql(osmdata, conPGSQL) time_c = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # Add Lulc Classes to OSM_FEATURES by rule # # ************************************************************************ # add_lulc_to_osmfeat(osm_db if RoadsAPI != 'POSTGIS' else conPGSQL, 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 if RoadsAPI != 'POSTGIS' else conPGSQL, epsg, api='SQLITE' if RoadsAPI != 'POSTGIS' else RoadsAPI) time_e = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # Start a GRASS GIS Session # # ************************************************************************ # grass_base = run_grass(workspace, grassBIN='grass76', 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.anls.ovlay import erase from gasp.prop.grs import rst_to_region from gasp.mng.genze import dissolve from gasp.mng.grstbl import add_and_update, reset_table from gasp.to.shp.grs import shp_to_grs, grs_to_shp from gasp.to.rst 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 if RoadsAPI != 'POSTGIS' else conPGSQL, 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 if RoadsAPI == 'SQLITE' else conPGSQL, 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 # # ************************************************************************ # ruleThreeShp, timeCheck3 = grs_vect_selbyarea( osm_db if RoadsAPI != 'POSTGIS' else conPGSQL, osmTableData['polygons'], UPPER=True, apidb=RoadsAPI) osmShps.append(ruleThreeShp) time_l = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # 4 - Area Lower than # # ************************************************************************ # ruleFourShp, timeCheck4 = grs_vect_selbyarea( osm_db if RoadsAPI != 'POSTGIS' else conPGSQL, osmTableData['polygons'], UPPER=False, apidb=RoadsAPI) osmShps.append(ruleFourShp) time_j = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # 5 - Get data from lines table (railway | waterway) # # ************************************************************************ # ruleFiveShp, timeCheck5 = grs_vect_bbuffer( osm_db if RoadsAPI != 'POSTGIS' else conPGSQL, 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 if RoadsAPI != 'POSTGIS' else conPGSQL, 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 """ from gasp.mng.gen import same_attr_to_shp _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)) _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 import copy 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 = [] for cls in osmShps: if cls == __priorities[0]: reset_table(osmShps[cls], {'cls': 'varchar(5)'}, {'cls': str(cls)}) else: add_and_update(osmShps[cls], {'cls': 'varchar(5)'}, {'cls': str(cls)}) ds = dissolve(osmShps[cls], 'dl_{}'.format(str(cls)), 'cls', api="grass") lst_merge.append( grs_to_shp(ds, os.path.join(workspace, "lulc_{}.shp".format(str(cls))), 'auto', lyrN=1, asCMD=True, asMultiPart=None)) time_q = datetime.datetime.now().replace(microsecond=0) merge_feat(lst_merge, lulcShp, api='pandas') time_r = datetime.datetime.now().replace(microsecond=0) 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: ('rule_3', time_l - time_h, timeCheck3), 8: ('rule_4', time_j - time_l, timeCheck4), 9: ('rule_5', time_m - time_j, 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 check_shape_diff(SHAPES_TO_COMPARE, OUT_FOLDER, REPORT, conPARAM, DB, SRS_CODE, GIS_SOFTWARE="GRASS", GRASS_REGION_TEMPLATE=None): """ Script to check differences between pairs of Feature Classes Suponha que temos diversas Feature Classes (FC) e que cada uma delas possui um determinado atributo; imagine também que, considerando todos os pares possíveis entre estas FC, se pretende comparar as diferenças na distribuição dos valores desse atributo em cada par. * Dependências: - ArcGIS; - GRASS; - PostgreSQL; - PostGIS. * GIS_SOFTWARE Options: - ARCGIS; - GRASS. """ import datetime import os import pandas from gasp.fm.sql import query_to_df from gasp.sql.mng.tbl import tbls_to_tbl from gasp.sql.mng.geom import fix_geom, check_geomtype_in_table from gasp.sql.mng.geom import select_main_geom_type from gasp.sql.mng.qw import ntbl_by_query from gasp.prop.ff import check_isRaster from gasp.oss import get_filename from gasp.sql.mng.db import create_db from gasp.to.sql import shp_to_psql, df_to_db from gasp.to.shp import rst_to_polyg from gasp.to.shp import shp_to_shp, psql_to_shp from gasp.to import db_to_tbl # Check if folder exists, if not create it if not os.path.exists(OUT_FOLDER): from gasp.oss.ops import create_folder create_folder(OUT_FOLDER, overwrite=None) else: raise ValueError('{} already exists!'.format(OUT_FOLDER)) # Start GRASS GIS Session if GIS_SOFTWARE == GRASS if GIS_SOFTWARE == "GRASS": if not GRASS_REGION_TEMPLATE: raise ValueError( 'To use GRASS GIS you need to specify GRASS_REGION_TEMPLATE') from gasp.session import run_grass gbase = run_grass(OUT_FOLDER, grassBIN='grass76', location='shpdif', srs=GRASS_REGION_TEMPLATE) import grass.script as grass import grass.script.setup as gsetup gsetup.init(gbase, OUT_FOLDER, 'shpdif', 'PERMANENT') from gasp.mng.grstbl import rename_col from gasp.to.shp.grs import shp_to_grs, grs_to_shp from gasp.to.rst import rst_to_grs from gasp.mng.fld import rename_column # Convert to SHAPE if file is Raster # Import to GRASS GIS if GIS SOFTWARE == GRASS i = 0 _SHP_TO_COMPARE = {} for s in SHAPES_TO_COMPARE: isRaster = check_isRaster(s) if isRaster: if GIS_SOFTWARE == "ARCGIS": d = rst_to_polyg(s, os.path.join(os.path.dirname(s), get_filename(s) + '.shp'), gisApi='arcpy') _SHP_TO_COMPARE[d] = "gridcode" elif GIS_SOFTWARE == "GRASS": # To GRASS rstName = get_filename(s) inRst = rst_to_grs(s, "rst_" + rstName, as_cmd=True) # To Raster d = rst_to_polyg(inRst, rstName, rstColumn="lulc_{}".format(i), gisApi="grasscmd") # Export Shapefile shp = grs_to_shp(d, os.path.join(OUT_FOLDER, d + '.shp'), "area") _SHP_TO_COMPARE[shp] = "lulc_{}".format(i) else: if GIS_SOFTWARE == "ARCGIS": _SHP_TO_COMPARE[s] = SHAPES_TO_COMPARE[s] elif GIS_SOFTWARE == "GRASS": # To GRASS grsV = shp_to_grs(s, get_filename(s), asCMD=True) # Change name of column with comparing value rename_col(grsV, SHAPES_TO_COMPARE[s], "lulc_{}".format(i), as_cmd=True) # Export shp = grs_to_shp(grsV, os.path.join(OUT_FOLDER, grsV + '_rn.shp'), "area") _SHP_TO_COMPARE[shp] = "lulc_{}".format(i) i += 1 SHAPES_TO_COMPARE = _SHP_TO_COMPARE if GIS_SOFTWARE == "ARCGIS": from gasp.cpu.arcg.mng.fld import calc_fld from gasp.cpu.arcg.mng.wspace import create_geodb from gasp.mng.gen import copy_feat # Sanitize data and Add new field __SHAPES_TO_COMPARE = {} i = 0 # Create GeoDatabase geodb = create_geodb(OUT_FOLDER, 'geo_sanitize') """ Sanitize Data """ for k in SHAPES_TO_COMPARE: # Send data to GeoDatabase only to sanitize newFc = shp_to_shp(k, os.path.join(geodb, get_filename(k)), gisApi='arcpy') # Create a copy to change newShp = copy_feat(newFc, os.path.join(OUT_FOLDER, os.path.basename(k)), gisApi='arcpy') # Sanitize field name with interest data NEW_FLD = "lulc_{}".format(i) calc_fld(newShp, NEW_FLD, "[{}]".format(SHAPES_TO_COMPARE[k]), isNewField={ "TYPE": "INTEGER", "LENGTH": 5, "PRECISION": "" }) __SHAPES_TO_COMPARE[newShp] = NEW_FLD i += 1 else: __SHAPES_TO_COMPARE = SHAPES_TO_COMPARE # Create database conPARAM["DATABASE"] = create_db(conPARAM, DB) """ Union SHAPEs """ UNION_SHAPE = {} FIX_GEOM = {} def fix_geometry(shp): # Send data to PostgreSQL nt = shp_to_psql(conPARAM, shp, SRS_CODE, api='shp2pgsql') # Fix data corr_tbl = fix_geom(conPARAM, nt, "geom", "corr_{}".format(nt), colsSelect=['gid', __SHAPES_TO_COMPARE[shp]]) # Check if we have multiple geometries geomN = check_geomtype_in_table(conPARAM, corr_tbl) if geomN > 1: corr_tbl = select_main_geom_type(conPARAM, corr_tbl, "corr2_{}".format(nt)) # Export data again newShp = psql_to_shp(conPARAM, corr_tbl, os.path.join(OUT_FOLDER, corr_tbl + '.shp'), api='pgsql2shp', geom_col='geom') return newShp SHPS = __SHAPES_TO_COMPARE.keys() for i in range(len(SHPS)): for e in range(i + 1, len(SHPS)): if GIS_SOFTWARE == 'ARCGIS': # Try the union thing unShp = union(SHPS[i], SHPS[e], os.path.join(OUT_FOLDER, "un_{}_{}.shp".format(i, e)), api_gis="arcpy") # See if the union went all right if not os.path.exists(unShp): # Union went not well # See if geometry was already fixed if SHPS[i] not in FIX_GEOM: # Fix SHPS[i] geometry FIX_GEOM[SHPS[i]] = fix_geometry(SHPS[i]) if SHPS[e] not in FIX_GEOM: FIX_GEOM[SHPS[e]] = fix_geometry(SHPS[e]) # Run Union again unShp = union(FIX_GEOM[SHPS[i]], FIX_GEOM[SHPS[e]], os.path.join(OUT_FOLDER, "un_{}_{}_f.shp".format(i, e)), api_gis="arcpy") elif GIS_SOFTWARE == "GRASS": # Optimized Union print "Union between {} and {}".format(SHPS[i], SHPS[e]) time_a = datetime.datetime.now().replace(microsecond=0) __unShp = optimized_union_anls( SHPS[i], SHPS[e], os.path.join(OUT_FOLDER, "un_{}_{}.shp".format(i, e)), GRASS_REGION_TEMPLATE, SRS_CODE, os.path.join(OUT_FOLDER, "work_{}_{}".format(i, e)), multiProcess=True) time_b = datetime.datetime.now().replace(microsecond=0) print time_b - time_a # Rename cols unShp = rename_column( __unShp, { "a_" + __SHAPES_TO_COMPARE[SHPS[i]]: __SHAPES_TO_COMPARE[SHPS[i]], "b_" + __SHAPES_TO_COMPARE[SHPS[e]]: __SHAPES_TO_COMPARE[SHPS[e]] }, os.path.join(OUT_FOLDER, "un_{}_{}_rn.shp".format(i, e))) UNION_SHAPE[(SHPS[i], SHPS[e])] = unShp # Send data one more time to postgresql SYNTH_TBL = {} for uShp in UNION_SHAPE: # Send data to PostgreSQL union_tbl = shp_to_psql(conPARAM, UNION_SHAPE[uShp], SRS_CODE, api='shp2pgsql') # Produce table with % of area equal in both maps areaMapTbl = ntbl_by_query( conPARAM, "{}_syn".format(union_tbl), ("SELECT CAST('{lulc_1}' AS text) AS lulc_1, " "CAST('{lulc_2}' AS text) AS lulc_2, " "round(" "CAST(SUM(g_area) / 1000000 AS numeric), 4" ") AS agree_area, round(" "CAST((SUM(g_area) / MIN(total_area)) * 100 AS numeric), 4" ") AS agree_percentage, " "round(" "CAST(MIN(total_area) / 1000000 AS numeric), 4" ") AS total_area FROM (" "SELECT {map1_cls}, {map2_cls}, ST_Area(geom) AS g_area, " "CASE " "WHEN {map1_cls} = {map2_cls} " "THEN 1 ELSE 0 " "END AS isthesame, total_area FROM {tbl}, (" "SELECT SUM(ST_Area(geom)) AS total_area FROM {tbl}" ") AS foo2" ") AS foo WHERE isthesame = 1 " "GROUP BY isthesame").format( lulc_1=get_filename(uShp[0]), lulc_2=get_filename(uShp[1]), map1_cls=__SHAPES_TO_COMPARE[uShp[0]], map2_cls=__SHAPES_TO_COMPARE[uShp[1]], tbl=union_tbl), api='psql') # Produce confusion matrix for the pair in comparison lulcCls = query_to_df( conPARAM, ("SELECT fcol FROM (" "SELECT CAST({map1_cls} AS text) AS fcol FROM {tbl} " "GROUP BY {map1_cls} " "UNION ALL SELECT CAST({map2_cls} AS text) FROM {tbl} " "GROUP BY {map2_cls}" ") AS foo GROUP BY fcol ORDER BY fcol").format( tbl=union_tbl, map1_cls=__SHAPES_TO_COMPARE[uShp[0]], map2_cls=__SHAPES_TO_COMPARE[uShp[1]]), db_api='psql').fcol.tolist() matrixTbl = ntbl_by_query( conPARAM, "{}_matrix".format(union_tbl), ("SELECT * FROM crosstab('" "SELECT CASE " "WHEN foo.{map1_cls} IS NOT NULL " "THEN foo.{map1_cls} ELSE jtbl.flyr " "END AS lulc1_cls, CASE " "WHEN foo.{map2_cls} IS NOT NULL " "THEN foo.{map2_cls} ELSE jtbl.slyr " "END AS lulc2_cls, CASE " "WHEN foo.garea IS NOT NULL " "THEN round(CAST(foo.garea / 1000000 AS numeric)" ", 3) ELSE 0 " "END AS garea FROM (" "SELECT CAST({map1_cls} AS text) AS {map1_cls}, " "CAST({map2_cls} AS text) AS {map2_cls}, " "SUM(ST_Area(geom)) AS garea " "FROM {tbl} GROUP BY {map1_cls}, {map2_cls}" ") AS foo FULL JOIN (" "SELECT f.flyr, s.slyr FROM (" "SELECT CAST({map1_cls} AS text) AS flyr " "FROM {tbl} GROUP BY {map1_cls}" ") AS f, (" "SELECT CAST({map2_cls} AS text) AS slyr " "FROM {tbl} GROUP BY {map2_cls}" ") AS s" ") AS jtbl " "ON foo.{map1_cls} = jtbl.flyr AND " "foo.{map2_cls} = jtbl.slyr " "ORDER BY 1,2" "') AS ct(" "lulc_cls text, {crossCols}" ")").format(crossCols=", ".join( ["cls_{} numeric".format(c) for c in lulcCls]), tbl=union_tbl, map1_cls=__SHAPES_TO_COMPARE[uShp[0]], map2_cls=__SHAPES_TO_COMPARE[uShp[1]]), api='psql') SYNTH_TBL[uShp] = {"TOTAL": areaMapTbl, "MATRIX": matrixTbl} # UNION ALL TOTAL TABLES total_table = tbls_to_tbl(conPARAM, [SYNTH_TBL[k]["TOTAL"] for k in SYNTH_TBL], 'total_table') # Create table with % of agreement between each pair of maps mapsNames = query_to_df( conPARAM, ("SELECT lulc FROM (" "SELECT lulc_1 AS lulc FROM {tbl} GROUP BY lulc_1 " "UNION ALL " "SELECT lulc_2 AS lulc FROM {tbl} GROUP BY lulc_2" ") AS lu GROUP BY lulc ORDER BY lulc").format(tbl=total_table), db_api='psql').lulc.tolist() FLDS_TO_PIVOT = ["agree_percentage", "total_area"] Q = ("SELECT * FROM crosstab('" "SELECT CASE " "WHEN foo.lulc_1 IS NOT NULL THEN foo.lulc_1 ELSE jtbl.tmp1 " "END AS lulc_1, CASE " "WHEN foo.lulc_2 IS NOT NULL THEN foo.lulc_2 ELSE jtbl.tmp2 " "END AS lulc_2, CASE " "WHEN foo.{valCol} IS NOT NULL THEN foo.{valCol} ELSE 0 " "END AS agree_percentage FROM (" "SELECT lulc_1, lulc_2, {valCol} FROM {tbl} UNION ALL " "SELECT lulc_1, lulc_2, {valCol} FROM (" "SELECT lulc_1 AS lulc_2, lulc_2 AS lulc_1, {valCol} " "FROM {tbl}" ") AS tst" ") AS foo FULL JOIN (" "SELECT lulc_1 AS tmp1, lulc_2 AS tmp2 FROM (" "SELECT lulc_1 AS lulc_1 FROM {tbl} GROUP BY lulc_1 " "UNION ALL " "SELECT lulc_2 AS lulc_1 FROM {tbl} GROUP BY lulc_2" ") AS tst_1, (" "SELECT lulc_1 AS lulc_2 FROM {tbl} GROUP BY lulc_1 " "UNION ALL " "SELECT lulc_2 AS lulc_2 FROM {tbl} GROUP BY lulc_2" ") AS tst_2 WHERE lulc_1 = lulc_2 GROUP BY lulc_1, lulc_2" ") AS jtbl ON foo.lulc_1 = jtbl.tmp1 AND foo.lulc_2 = jtbl.tmp2 " "ORDER BY lulc_1, lulc_2" "') AS ct(" "lulc_map text, {crossCols}" ")") TOTAL_AGREE_TABLE = None TOTAL_AREA_TABLE = None for f in FLDS_TO_PIVOT: if not TOTAL_AGREE_TABLE: TOTAL_AGREE_TABLE = ntbl_by_query( conPARAM, "agreement_table", Q.format(tbl=total_table, valCol=f, crossCols=", ".join([ "{} numeric".format(map_) for map_ in mapsNames ])), api='psql') else: TOTAL_AREA_TABLE = ntbl_by_query( conPARAM, "area_table", Q.format(tbl=total_table, valCol=f, crossCols=", ".join([ "{} numeric".format(map_) for map_ in mapsNames ])), api='psql') # Union Mapping UNION_MAPPING = pandas.DataFrame( [[ get_filename(k[0]), get_filename(k[1]), get_filename(UNION_SHAPE[k]) ] for k in UNION_SHAPE], columns=['shp_a', 'shp_b', 'union_shp' ]) if GIS_SOFTWARE == "ARCGIS" else pandas.DataFrame( [[k[0], k[1], get_filename(UNION_SHAPE[k])] for k in UNION_SHAPE], columns=['shp_a', 'shp_b', 'union_shp']) UNION_MAPPING = df_to_db(conPARAM, UNION_MAPPING, 'union_map', api='psql') # Export Results TABLES = [UNION_MAPPING, TOTAL_AGREE_TABLE, TOTAL_AREA_TABLE ] + [SYNTH_TBL[x]["MATRIX"] for x in SYNTH_TBL] SHEETS = ["union_map", "agreement_percentage", "area_with_data_km"] + [ "{}_{}".format(get_filename(x[0])[:15], get_filename(x[1])[:15]) for x in SYNTH_TBL ] db_to_xls(conPARAM, ["SELECT * FROM {}".format(x) for x in TABLES], REPORT, sheetsNames=SHEETS, dbAPI='psql') return REPORT
def get_unused_data_on_lulcp( pbf, pgsql_data, out_work, nomenclature, dicPostgre={ 'HOST': 'localhost', 'USER': '******', 'DATABASE': 'osm', 'TEMPLATE': 'template_postgis', 'PASSWORD': '******', 'PORT': '5432' }): """ Return data not used in osm2lulc procedure """ import os from gasp.sql import run_sql_file from gasp.sql.mng.db import create_db from gasp.fm.sql import query_to_df from gasp.sql.mng.fld import cols_name from gasp.sql.k import create_pk from gasp.to.shp import psql_to_shp from gasp.to.psql import osm_to_pgsql # TODO: replace postgis from gasp.postgis.analysis import new_geom_table # ################ # # Global Variables # # ################ # # Name of the tables with osmdata after the import with osm2pgsql OsmData = { 'polygon': 'planet_osm_polygon', 'line': 'planet_osm_line', 'point': 'planet_osm_point' } IrrelevantCols = [ 'addr:housename', 'addr:housenumber', 'addr:interpolation', 'generator:source', 'tower:type' ] if nomenclature == 'URBAN_ATLAS': tbl_relation = 'rel_osm_ua' id_osm = 'osm_id' elif nomenclature == 'CORINE_LAND_COVER': tbl_relation = 'rel_osm_clc' id_osm = 'osm_id' elif nomenclature == 'GLOB_LAND_30': tbl_relation = 'rel_osm_global' id_osm = 'id_osm' # ################ # # Auxiliar Methods # # ################ # def get_where_string(dic, operator, table): l = [] for fid in dic: if dic[fid][0] == '' or dic[fid][1] == '' or dic[fid][0] == 'sidewalk'\ or dic[fid][0] == 'cycleway' or dic[fid][0] == 'busway'\ or dic[fid][0] == 'enity' or dic[fid][0] == 'healthcare': continue l.append("{t}.{col}{o}'{val}'".format(col=dic[fid][0], o=operator, val=dic[fid][1], t=table)) return " OR ".join(l) create_db(dicPostgre, dicPostgre['DATABASE']) run_sql_file(dicPostgre, dicPostgre['DATABASE'], pgsql_data) run_osm2pgsql(pbf, dicPostgre['DATABASE'], dicPostgre['HOST'], dicPostgre['USER']) # 1. Obtain data used on OSM2LULC procedure # 1.1 Get key and value for osm features id_related_with = [ x[0] for x in query_to_df(dicPostgre, "SELECT {fid} FROM {t}".format(t=tbl_relation, fid=id_osm), db_api='psql')[id_osm].tolist() ] key_value = { x[0]: [x[1], x[2]] for x in query_to_df( dicPostgre, "SELECT id, key, value FROM osm_features WHERE {s}".format( s=' OR '.join( ['id={y}'.format(y=str(x)) for x in id_related_with])), db_api='psql').values.tolist() } # 1.2 Extract data with this combinations of keys and values for tbl in OsmData: # Create new primary key create_pk(dicPostgre, OsmData[tbl], 'pk_fid') cols = cols_name(dicPostgre, OsmData[tbl]) cols_clean = [] for i in cols: if i not in IrrelevantCols: if i == 'natural': cols_clean.append("{t}.{col}".format(t=OsmData[tbl], col=i)) else: cols_clean.append(i) whr = get_where_string(key_value, "=", OsmData[tbl]) new_geom_table(dicPostgre, cols_clean, OsmData[tbl], whr, 'used{t}'.format(t=OsmData[tbl]), pk=False) export = psql_to_shp( dicPostgre, 'used{t}'.format(t=OsmData[tbl]), os.path.join(out_work, 'used{t}.shp'.format(t=OsmData[tbl])), api="pgsql2shp", geom_col='way') # 2. Obtain data not used on OSM2LULC procedure for tbl in OsmData: new_geom_table(dicPostgre, ['*'], OsmData[tbl], "{t}.pk_fid NOT IN (SELECT pk_fid FROM used{t})".format( t=OsmData[tbl]), 'unused{t}'.format(t=OsmData[tbl]), pk=False) export = psql_to_shp(dicPostgre, 'unused{t}'.format(t=OsmData[tbl]), os.path.join( out_work, 'unused{t}.shp'.format(t=OsmData[tbl])), api="pgsql2shp", geom_col='way')
def matrix_od_mean_dist_by_group(MATRIX_OD, ORIGIN_COL, GROUP_ORIGIN_ID, GROUP_ORIGIN_NAME, GROUP_DESTINA_ID, GROUP_DESTINA_NAME, TIME_COL, epsg, conParam, RESULT_MATRIX): """ Calculate Mean GROUP distance from OD Matrix OD MATRIX EXAMPLE | origin_entity | origin_group | destina_entity | destina_group | distance | XXXX | XXXX | XXXX | XXX | XXX OUTPUT EXAMPLE | origin_group | destina_group | mean_distance | XXXX | XXXX | XXXX """ import os from gasp.oss import get_filename from gasp.to.sql import shp_to_psql from gasp.sql.mng.db import create_db from gasp.sql.mng.qw import ntbl_by_query from gasp.to import db_to_tbl db_name = get_filename(MATRIX_OD) create_db(conParam, db_name, overwrite=True) conParam["DATABASE"] = db_name TABLE = shp_to_psql(conParam, MATRIX_OD, epsg, pgTable="tbl_{}".format(db_name), api="pandas") OUT_TABLE = ntbl_by_query( conParam, get_filename(RESULT_MATRIX), ("SELECT {groupOriginCod}, {groupOriginName}, {groupDestCod}, " "{groupDestName}, AVG(mean_time) AS mean_time FROM (" "SELECT {origin}, {groupOriginCod}, {groupOriginName}, " "{groupDestCod}, {groupDestName}, " "AVG({timeCol}) AS mean_time FROM {t} " "GROUP BY {origin}, {groupOriginCod}, {groupOriginName}, " "{groupDestCod}, {groupDestName}" ") AS foo " "GROUP BY {groupOriginCod}, {groupOriginName}, " "{groupDestCod}, {groupDestName} " "ORDER BY {groupOriginCod}, {groupDestCod}").format( groupOriginCod=GROUP_ORIGIN_ID, groupOriginName=GROUP_ORIGIN_NAME, groupDestCod=GROUP_DESTINA_ID, groupDestName=GROUP_DESTINA_NAME, origin=ORIGIN_COL, timeCol=TIME_COL, t=TABLE), api='psql') return db_to_tbl(conParam, "SELECT * FROM {}".format(OUT_TABLE), RESULT_MATRIX, sheetsNames="matrix", dbAPI='psql')
def osm2lulc(osmdata, nomenclature, refRaster, lulcRst, epsg=3857, overwrite=None, dataStore=None, roadsAPI='SQLITE'): """ 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 import json from threading import Thread from osgeo import gdal # ************************************************************************ # # Dependencies # # ************************************************************************ # from gasp.fm.rst import rst_to_array from gasp.prop.rst import get_cellsize from gasp.oss.ops import create_folder, copy_file if roadsAPI == 'POSTGIS': from gasp.sql.mng.db import create_db from gasp.osm2lulc.utils import osm_to_pgsql from gasp.osm2lulc.mod2 import pg_num_roads else: from gasp.osm2lulc.utils import osm_to_sqdb from gasp.osm2lulc.mod2 import num_roads from gasp.osm2lulc.utils import osm_project, add_lulc_to_osmfeat from gasp.osm2lulc.mod1 import num_selection from gasp.osm2lulc.m3_4 import num_selbyarea from gasp.osm2lulc.mod5 import num_base_buffer from gasp.osm2lulc.mod6 import num_assign_builds from gasp.to.rst import array_to_raster # ************************************************************************ # # Global Settings # # ************************************************************************ # if not os.path.exists(os.path.dirname(lulcRst)): raise ValueError('{} does not exist!'.format(os.path.dirname(lulcRst))) conPGSQL = json.load( open( os.path.join(os.path.dirname(os.path.abspath(__file__)), 'con-postgresql.json'), 'r')) if roadsAPI == 'POSTGIS' else None time_a = datetime.datetime.now().replace(microsecond=0) from gasp.osm2lulc.var import osmTableData, PRIORITIES 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: create_folder(workspace, overwrite=True) else: raise ValueError('Path {} already exists'.format(workspace)) else: create_folder(workspace, overwrite=None) CELLSIZE = get_cellsize(refRaster, xy=False, gisApi='gdal') time_b = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # Convert OSM file to SQLITE DB or to POSTGIS DB # # ************************************************************************ # if roadsAPI == 'POSTGIS': conPGSQL["DATABASE"] = create_db(conPGSQL, os.path.splitext( os.path.basename(osmdata))[0], overwrite=True) osm_db = osm_to_pgsql(osmdata, conPGSQL) 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(conPGSQL if roadsAPI == 'POSTGIS' else osm_db, osmTableData, nomenclature, api=roadsAPI) time_d = datetime.datetime.now().replace(microsecond=0) # ************************************************************************ # # Transform SRS of OSM Data # # ************************************************************************ # osmTableData = osm_project( conPGSQL if roadsAPI == 'POSTGIS' else 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(conPGSQL if not _osmdb else _osmdb, 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( conPGSQL, 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(conPGSQL 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(conPGSQL 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(conPGSQL 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(conPGSQL 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 = 1222 if lulcCls == 98 else 1221 if lulcCls == 99 else \ 802 if lulcCls == 82 else 801 if lulcCls == 81 else lulcCls if __lulcCls not in arrayRst: continue else: numpy.place(arrayRst[__lulcCls], arrayRst[__lulcCls] > 0, lulcCls) for i in range(len(__priorities)): lulc_i = 1222 if __priorities[i] == 98 else 1221 \ if __priorities[i] == 99 else 802 if __priorities[i] == 82 \ else 801 if __priorities[i] == 81 else __priorities[i] if lulc_i not in arrayRst: continue else: for e in range(i + 1, len(__priorities)): lulc_e = 1222 if __priorities[e] == 98 else 1221 \ if __priorities[e] == 99 else \ 802 if __priorities[e] == 82 else 801 \ if __priorities[e] == 81 else __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 = 1222 if __priorities[i] == 98 else 1221 \ if __priorities[i] == 99 else 802 if __priorities[i] == 82 \ else 801 if __priorities[i] == 81 else __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 = 1222 if __priorities[i] == 98 else 1221 \ if __priorities[i] == 99 else 802 if __priorities[i] == 82 \ else 801 if __priorities[i] == 81 else __priorities[i] if lulc_i not in arrayRst: continue resultSum = resultSum + arrayRst[lulc_i] # Save Result numpy.place(resultSum, resultSum == 0, 1) array_to_raster(resultSum, lulcRst, refRaster, epsg, gdal.GDT_Byte, noData=1, gisApi='gdal') time_q = datetime.datetime.now().replace(microsecond=0) 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) }
def txts_to_db(folder, conDB, delimiter, __encoding='utf-8', apidb='psql', dbIsNew=None, rewrite=None, toDBViaPandas=True): """ Executes tbl_to_db for every file in a given folder The file name will be the table name """ from gasp.oss import list_files, get_filename if dbIsNew: # Create database from gasp.sql.mng.db import create_db if api == 'psql': __con = { 'HOST': conDB["HOST"], 'PORT': conDB["PORT"], 'USER': conDB["USER"], 'PASSWORD': conDB["PASSWORD"] } DB = conDB["DATABASE"] else: import os __con = os.path.dirname(conDB) DB = os.path.basename(conDB) create_db(__con, DB, api=apidb, overwrite=rewrite) __files = list_files(folder, file_format=['.txt', '.csv', '.tsv']) if toDBViaPandas: """ Send data to DB using Pandas """ for __file in __files: tbl_to_db(__file, conDB, get_filename(__file), delimiter=delimiter, encoding_=__encoding, api_db=apidb) else: """ Send data to DB using regular Python API """ from gasp.sql.mng.fld import pgtypes_from_pandasdf from gasp.sql.mng.tbl import create_tbl from gasp.fm import tbl_to_obj # Get Table data table_data = { get_filename(f): tbl_to_obj(f, _delimiter=delimiter, encoding_=__encoding) for f in __files } if apidb == 'psql': # Create Tables dicColsT = {} for table in table_data: cols = list(table_data[table].columns) colsT = pgtypes_from_pandasdf(table_data[table]) dicColsT[table] = colsT create_tbl(conDB, table, colsT, orderFields=cols) # Insert data into tables for table in table_data: cols = list(table_data[table].columns) tableDf = table_data[table] for i in range(len(cols)): if not i: if dicColsT[table][cols[i]] == "text": tableDf["row"] = u"('" + \ tableDf[cols[i]].astype(unicode) + u"'" else: tableDf["row"] = u"(" + \ tableDf[cols[i]].astype(unicode) else: if dicColsT[table][cols[i]] == "text": tableDf["row"] = tableDf["row"] + u", '" + \ tableDf[cols[i]].astype(unicode) + u"'" else: tableDf["row"] = tableDf["row"] + u", " + \ tableDf[cols[i]].astype(unicode) str_a = tableDf["row"].str.cat(sep=u"), ") + u")" sql = u"INSERT INTO {} ({}) VALUES {}".format( unicode(table, 'utf-8'), u", ".join(cols), str_a) psql_insert_query(conDB, sql) else: raise ValueError("API {} is not available".format(apidb))