def add_geomtype_to_col(conParam, table, newCol, geomCol):
"""
Add Geom Type to Column
"""
from gasp.sql.mng.qw import exec_write_q
# Add new field to table
add_field(conParam, table, {newCol: "text"})
exec_write_q(conParam,
"UPDATE {} SET {} = ST_GeometryType({})".format(
table, newCol, geomCol),
api='psql')
return table
def substring_to_newfield(conParam, table, field, newCol, idxFrom, idxTo):
"""
Get substring of string by range
"""
from gasp.sql.mng.qw import exec_write_q
# Add new field to table
add_field(conParam, table, {newCol: "text"})
# Update table
exec_write_q(conParam,
("UPDATE {tbl} SET {nf} = substring({f} from {frm} for {to}) "
"WHERE {nf} IS NULL").format(tbl=table,
nf=newCol,
f=field,
frm=idxFrom,
to=idxTo),
api='psql')
return table
def roads_sqdb(osmcon, lnhTbl, plTbl, apidb='SQLITE', asRst=None):
"""
Raods procedings using SQLITE
"""
import datetime
from gasp.sql.mng.tbl import row_num as cnt_rows
if apidb == 'SQLITE':
from gasp.sql.anls.prox import splite_buffer as st_buffer
from gasp.to.shp.grs import sqlite_to_shp as db_to_shp
else:
from gasp.sql.anls.prox import st_buffer
from gasp.to.shp.grs import psql_to_grs as db_to_shp
time_a = datetime.datetime.now().replace(microsecond=0)
NR = cnt_rows(osmcon,
lnhTbl,
where="roads IS NOT NULL",
api='psql' if apidb == 'POSTGIS' else 'sqlite')
time_b = datetime.datetime.now().replace(microsecond=0)
if not NR: return None, {0: ('count_rows_roads', time_b - time_a)}
NB = cnt_rows(osmcon,
plTbl,
where="building IS NOT NULL",
api='psql' if apidb == 'POSTGIS' else 'sqlite')
time_c = datetime.datetime.now().replace(microsecond=0)
if NB:
from gasp.sql.mng.qw import exec_write_q
ROADS_Q = "(SELECT{} roads, bf_roads, geometry FROM {} WHERE roads IS NOT NULL)".format(
"" if apidb == 'SQLITE' else " gid,", lnhTbl)
if apidb == 'SQLITE':
from gasp.sql.anls.prox import splite_near
nroads = splite_near(osmcon,
ROADS_Q,
plTbl,
"geometry",
"geometry",
"near_roads",
whrNear="building IS NOT NULL")
time_d = datetime.datetime.now().replace(microsecond=0)
# Update buffer distance field
exec_write_q(osmcon, [
("UPDATE near_roads SET bf_roads = CAST(round(dist_near, 0) AS integer) "
"WHERE dist_near >= 1 AND dist_near <= 12"),
("UPDATE near_roads SET bf_roads = 1 WHERE dist_near >= 0 AND "
"dist_near < 1")
],
api='sqlite')
time_e = datetime.datetime.now().replace(microsecond=0)
else:
from gasp.sql.anls.prox import st_near
nroads = st_near(
osmcon,
ROADS_Q,
'gid',
'geometry',
"(SELECT * FROM {} WHERE building IS NOT NULL)".format(plTbl),
"geometry",
"near_roads",
untilDist="12",
near_col="dist_near")
time_d = datetime.datetime.now().replace(microsecond=0)
exec_write_q(osmcon, [
("UPDATE near_roads SET "
"bf_roads = CAST(round(CAST(dist_near AS numeric), 0) AS integer) "
"WHERE dist_near >= 1 AND dist_near <= 12"),
("UPDATE near_roads SET bf_roads = 1 WHERE dist_near >= 0 AND "
"dist_near < 1"),
("CREATE INDEX near_dist_idx ON near_roads USING gist (geometry)"
)
],
api='psql')
time_e = datetime.datetime.now().replace(microsecond=0)
else:
nroads = ("(SELECT roads, bf_roads, geometry "
"FROM {} WHERE roads IS NOT NULL) AS foo").format(lnhTbl)
time_d = None
time_e = None
# Execute Buffer
bfTbl = st_buffer(osmcon,
nroads,
"bf_roads",
"geometry",
"bf_roads",
cols_select="roads",
outTblIsFile=None,
dissolve="ALL")
time_f = datetime.datetime.now().replace(microsecond=0)
# Send data to GRASS GIS
roadsGrs = db_to_shp(osmcon,
bfTbl,
"froads",
notTable=None,
filterByReg=True)
time_g = datetime.datetime.now().replace(microsecond=0)
if asRst:
from gasp.to.rst import shp_to_raster
roadsGrs = shp_to_raster(roadsGrs,
int(asRst),
None,
None,
"rst_roads",
api="grass")
time_h = datetime.datetime.now().replace(microsecond=0)
else:
time_h = None
return roadsGrs, {
0: ('count_rows_roads', time_b - time_a),
1: ('count_rows_build', time_c - time_b),
2: None if not time_d else ('near_analysis', time_d - time_c),
3: None if not time_e else ('update_buffer_tbl', time_e - time_d),
4: ('buffer_roads', time_f - time_e if time_e else time_f - time_c),
5: ('import_roads', time_g - time_f),
6: None if not time_h else ('roads_to_raster', time_h - time_g)
}
def pg_num_roads(osmLink, nom, lnhTbl, polyTbl, folder, cellsize, srs, rstT):
"""
Select, Calculate Buffer distance using POSTGIS, make buffer of roads
and convert roads to raster
"""
import datetime
import os
from osgeo import gdal
from gasp.sql.mng.tbl import row_num
from gasp.sql.anls.prox import st_buffer
from gasp.to.rst import shp_to_raster
# There are roads?
time_a = datetime.datetime.now().replace(microsecond=0)
NR = row_num(osmLink, lnhTbl, where="roads IS NOT NULL", api='psql')
time_b = datetime.datetime.now().replace(microsecond=0)
if not NR: return None, {0: ('count_rows_roads', time_b - time_a)}
# There are buildings?
NB = row_num(osmLink, polyTbl, where="building IS NOT NULL", api='psql')
time_c = datetime.datetime.now().replace(microsecond=0)
if NB:
from gasp.sql.anls.prox import st_near
from gasp.sql.mng.qw import exec_write_q
nroads = st_near(
osmLink,
("(SELECT gid, roads, bf_roads, geometry FROM {} "
"WHERE roads IS NOT NULL)").format(lnhTbl),
"gid",
"geometry",
("(SELECT * FROM {} WHERE building IS NOT NULL)").format(polyTbl),
"geometry",
"near_roads",
untilDist="12",
near_col="dist_near")
time_d = datetime.datetime.now().replace(microsecond=0)
exec_write_q(osmLink, [(
"UPDATE near_roads SET "
"bf_roads = CAST(round(CAST(dist_near AS numeric), 0) AS integer) "
"WHERE dist_near >= 1 AND dist_near <= 12"
), "CREATE INDEX near_dist_idx ON near_roads USING gist (geometry)"])
time_e = datetime.datetime.now().replace(microsecond=0)
else:
nroads = ("(SELECT roads, bf_roads, geometry FROM {} "
"WHERE roads IS NOT NULL) AS foo").format(lnhTbl)
time_d = None
time_e = None
# Execute Buffer
bufferShp = st_buffer(osmLink,
nroads,
"bf_roads",
"geometry",
os.path.join(folder, "bf_roads.shp"),
cols_select="roads",
outTblIsFile=True,
dissolve=None)
time_f = datetime.datetime.now().replace(microsecond=0)
# Convert to Raster
roadsRst = shp_to_raster(bufferShp,
None,
cellsize,
0,
os.path.join(folder, "rst_roads.tif"),
epsg=srs,
rst_template=rstT,
api='gdal')
time_g = datetime.datetime.now().replace(microsecond=0)
LULC_CLS = '1221' if nom != "GLOBE_LAND_30" else '801'
return {
int(LULC_CLS): roadsRst
}, {
0: ('count_rows_roads', time_b - time_a),
1: ('count_rows_build', time_c - time_b),
2: None if not time_d else ('near_analysis', time_d - time_c),
3: None if not time_e else ('update_buffer_tbl', time_e - time_d),
4: ('buffer_roads', time_f - time_e if time_e else time_f - time_c),
5: ('roads_to_raster', time_g - time_f)
}
def add_lulc_to_osmfeat(osmdb, osmTbl, nomenclature, api='SQLITE'):
"""
Add LULC Classes in OSM Data Tables
"""
from gasp.sql.mng.qw import exec_write_q
from gasp.osm2lulc.var import DB_SCHEMA
KEY_COL = DB_SCHEMA["OSM_FEATURES"]["OSM_KEY"]
VALUE_COL = DB_SCHEMA["OSM_FEATURES"]["OSM_VALUE"]
LULC_COL = DB_SCHEMA[nomenclature]["CLS_FK"]
RULE_NAME = DB_SCHEMA["RULES"]["RULE_NAME"]
osmFeaturesDf = get_osm_feat_by_rule(nomenclature)
osmFeaturesDf.loc[:, VALUE_COL] = osmFeaturesDf[KEY_COL] + "='" + \
osmFeaturesDf[VALUE_COL] + "'"
Q = []
for rule in osmFeaturesDf[RULE_NAME].unique():
filterDf = osmFeaturesDf[osmFeaturesDf[RULE_NAME] == rule]
if rule == 'selection' or rule == 'buildings':
OSM_TABLE = 'polygons'
BUFFER_COL = None
AREA_COL = None
elif rule == 'roads':
OSM_TABLE = 'lines'
BUFFER_COL = DB_SCHEMA[nomenclature]["RULES_FIELDS"]["BUFFER"]
AREA_COL = None
elif rule == 'area_upper' or rule == 'area_lower':
OSM_TABLE = 'polygons'
BUFFER_COL = None
AREA_COL = DB_SCHEMA[nomenclature]["RULES_FIELDS"]["AREA"]
elif rule == 'basic_buffer':
OSM_TABLE = 'lines'
BUFFER_COL = DB_SCHEMA[nomenclature]["RULES_FIELDS"]["BUFFER"]
AREA_COL = None
filterDf.loc[:,
VALUE_COL] = osmTbl[OSM_TABLE] + "." + filterDf[VALUE_COL]
Q.append("ALTER TABLE {} ADD COLUMN {} integer".format(
osmTbl[OSM_TABLE], rule))
if BUFFER_COL:
Q.append("ALTER TABLE {} ADD COLUMN {} integer".format(
osmTbl[OSM_TABLE], "bf_" + rule))
if AREA_COL:
Q.append("ALTER TABLE {} ADD COLUMN {} integer".format(
osmTbl[OSM_TABLE], "t_" + rule))
for cls in filterDf[LULC_COL].unique():
__filterDf = filterDf[filterDf[LULC_COL] == cls]
Q.append("UPDATE {} SET {}={} WHERE {}".format(
osmTbl[OSM_TABLE], rule, cls,
str(__filterDf[VALUE_COL].str.cat(sep=" OR "))))
if BUFFER_COL:
for bfdist in filterDf[BUFFER_COL].unique():
__filterDf = filterDf[filterDf[BUFFER_COL] == bfdist]
Q.append("UPDATE {} SET {}={} WHERE {}".format(
osmTbl[OSM_TABLE], "bf_" + rule, bfdist,
str(__filterDf[VALUE_COL].str.cat(sep=" OR "))))
if AREA_COL:
for areaval in filterDf[AREA_COL].unique():
__filterDf = filterDf[filterDf[AREA_COL] == areaval]
Q.append("UPDATE {} SET {}={} WHERE {}".format(
osmTbl[OSM_TABLE], "t_" + rule, areaval,
str(__filterDf[VALUE_COL].str.cat(sep=" OR "))))
if rule == 'buildings':
fd = osmFeaturesDf[
(osmFeaturesDf[RULE_NAME] == 'selection') & \
(osmFeaturesDf[KEY_COL] == 'building') & \
(osmFeaturesDf[LULC_COL] == 12)
]
Q += [
"ALTER TABLE {} ADD COLUMN {} integer".format(
osmTbl["points"], rule),
"UPDATE {} SET {}={} WHERE {}".format(
osmTbl["points"], rule, 12,
str(fd[VALUE_COL].str.cat(sep=" OR ")))
]
exec_write_q(osmdb, Q, api='psql' if api == 'POSTGIS' else 'sqlite')