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))