def grs_vect_selbyarea(osmdb, polyTbl, UPPER=True, apidb='SQLITE'):
"""
Select features with area upper than.
A field with threshold is needed in the database.
"""
import datetime
from gasp.gt.gop.genze import dissolve
from gasp.gt.tbl.grs import add_table
from gasp.sds.osm2lulc import GEOM_AREA
from gasp.sql.i import row_num as cnt_row
from gasp.gt.toshp.db import dbtbl_to_shp as db_to_shp
OPERATOR = ">" if UPPER else "<"
DIRECTION = "upper" if UPPER else "lower"
WHR = "{ga} {op} t_area_{r} and area_{r} IS NOT NULL".format(op=OPERATOR,
r=DIRECTION,
ga=GEOM_AREA)
# Check if we have interest data
time_a = datetime.datetime.now().replace(microsecond=0)
N = cnt_row(osmdb,
polyTbl,
where=WHR,
api='psql' if apidb == 'POSTGIS' else 'sqlite')
time_b = datetime.datetime.now().replace(microsecond=0)
if not N: return None, {0: ('count_rows', time_b - time_a)}
# Data to GRASS GIS
grsVect = db_to_shp(osmdb,
polyTbl,
"geometry",
"area_{}".format(DIRECTION),
where=WHR,
inDB='psql' if apidb == 'POSTGIS' else 'sqlite',
filterByReg=True,
outShpIsGRASS=True)
time_c = datetime.datetime.now().replace(microsecond=0)
dissVect = dissolve(grsVect,
"diss_area_{}".format(DIRECTION),
"area_{}".format(DIRECTION),
api="grass")
add_table(dissVect, None, lyrN=1, asCMD=True)
time_d = datetime.datetime.now().replace(microsecond=0)
return dissVect, {
0: ('count_rows', time_b - time_a),
1: ('import', time_c - time_b),
2: ('dissolve', time_d - time_c)
}
def grs_vect_bbuffer(osmdata, lineTbl, api_db='SQLITE'):
"""
Basic Buffer strategie
"""
import datetime
from gasp.gt.prox.bf import _buffer
from gasp.gt.gop.genze import dissolve
from gasp.gt.tbl.grs import add_table
from gasp.sql.i import row_num as cnt_row
from gasp.gt.toshp.db import dbtbl_to_shp as db_to_shp
WHR = "basic_buffer IS NOT NULL"
# Check if we have data
time_a = datetime.datetime.now().replace(microsecond=0)
N = cnt_row(osmdata,
lineTbl,
where=WHR,
api='psql' if api_db == 'POSTGIS' else 'sqlite')
time_b = datetime.datetime.now().replace(microsecond=0)
if not N: return None, {0: ('count_rows_roads', time_b - time_a)}
grsVect = db_to_shp(osmdata,
lineTbl,
"geometry",
"bb_lnh",
where=WHR,
filterByReg=True,
inDB='psql' if api_db == 'POSTGIS' else 'sqlite',
outShpIsGRASS=True)
time_c = datetime.datetime.now().replace(microsecond=0)
grsBuf = _buffer(grsVect,
"bf_basic_buffer",
"bb_poly",
api="grass",
geom_type="line")
time_d = datetime.datetime.now().replace(microsecond=0)
grsDiss = dissolve(grsBuf, "bb_diss", "basic_buffer", api="grass")
add_table(grsDiss, None, lyrN=1, asCMD=True)
time_e = datetime.datetime.now().replace(microsecond=0)
return grsDiss, {
0: ('count_rows', time_b - time_a),
1: ('import', time_c - time_b),
2: ('buffer', time_d - time_c),
3: ('dissolve', time_e - time_d)
}
def rst_pnt_to_build(osmdb, pntTable, polyTable, api_db='SQLITE'):
"""
Replace buildings with tag yes using the info in the Points Layer
Only used for URBAN ATLAS and CORINE LAND COVER
"""
import datetime as dt
from gasp.sql.i import row_num as cnt_row
from gasp.sql.fm import q_to_obj
from gasp.gt.toshp.db import dbtbl_to_shp as db_to_shp
from gasp.gql.ovly import feat_within, feat_not_within
from gasp.gt.torst import shp_to_rst
time_a = dt.datetime.now().replace(microsecond=0)
new_build = feat_within(
osmdb,
("(SELECT buildings AS pnt_build, geometry AS pnt_geom "
"FROM {} WHERE buildings IS NOT NULL)").format(pntTable),
"pnt_geom",
("(SELECT buildings AS poly_build, geometry AS poly_geom "
"FROM {} WHERE buildings IS NOT NULL)").format(polyTable),
"poly_geom",
"new_buildings",
inTblCols="pnt_build AS cls",
withinCols="poly_geom AS geometry",
outTblIsFile=None,
apiToUse="OGR_SPATIALITE" if api_db != "POSTGIS" else api_db,
geom_col="geometry")
time_b = dt.datetime.now().replace(microsecond=0)
yes_build = feat_not_within(
osmdb,
("(SELECT buildings AS poly_build, geometry AS poly_geom "
"FROM {} WHERE buildings IS NOT NULL)").format(polyTable),
"poly_geom",
("(SELECT buildings AS pnt_build, geometry AS pnt_geom "
"FROM {} WHERE buildings IS NOT NULL)").format(pntTable),
"pnt_geom",
"yes_builds",
inTblCols="poly_geom AS geometry, 11 AS cls",
outTblIsFile=None,
apiToUse="OGR_SPATIALITE" if api_db != "POSTGIS" else api_db,
geom_col="geometry")
time_c = dt.datetime.now().replace(microsecond=0)
resLayers = {}
N11 = cnt_row(osmdb,
yes_build,
api='psql' if api_db == 'POSTGIS' else 'sqlite')
time_d = dt.datetime.now().replace(microsecond=0)
if N11:
# Data to GRASS GIS
grsBuild11 = db_to_shp(
osmdb,
yes_build,
"geometry",
"yes_builds",
notTable=True,
filterByReg=True,
inDB='psql' if api_db == 'POSTGIS' else 'sqlite',
outShpIsGRASS=True)
time_f = dt.datetime.now().replace(microsecond=0)
# To raster
rstBuild11 = shp_to_rst(grsBuild11,
11,
None,
None,
"rst_builds11",
api="grass")
time_g = dt.datetime.now().replace(microsecond=0)
resLayers[11] = [rstBuild11]
else:
time_f = None
time_g = None
# Add data into GRASS GIS
lulcCls = q_to_obj(
osmdb,
"SELECT cls FROM {} GROUP BY cls".format(new_build),
db_api='psql' if api_db == 'POSTGIS' else 'sqlite').cls.tolist()
timeGasto = {
0: ('intersect', time_b - time_a),
1: ('disjoint', time_c - time_b),
2: ('count_b11', time_d - time_c),
3: None if not time_f else ('import_b11', time_f - time_d),
4: None if not time_g else ('torst_b11', time_g - time_f),
}
tk = 5
for cls in lulcCls:
time_x = dt.datetime.now().replace(microsecond=0)
shp = db_to_shp(osmdb,
new_build,
"geometry",
"nbuild_{}".format(str(cls)),
"cls = {}".format(cls),
notTable=True,
filterByReg=True,
outShpIsGRASS=True)
time_y = dt.datetime.now().replace(microsecond=0)
rstb = shp_to_rst(shp,
int(cls),
None,
None,
"rst_nbuild_{}".format(str(cls)),
api="grass")
time_z = dt.datetime.now().replace(microsecond=0)
if int(cls) == 11 and int(cls) in resLayers:
resLayers[int(cls)].append(rstb)
else:
resLayers[int(cls)] = [rstb]
timeGasto[tk] = ('import_bn{}'.format(cls), time_y - time_x)
timeGasto[tk + 1] = ('torst_bn{}'.format(cls), time_z - time_y)
tk += 2
return resLayers, timeGasto
def vector_assign_pntags_to_build(osmdb, pntTable, polyTable, apidb='SQLITE'):
"""
Replace buildings with tag yes using the info in the Points Layer
Only used for URBAN ATLAS and CORINE LAND COVER
"""
import datetime as dt
from gasp.sql.i import row_num as cnt_row
from gasp.gt.toshp.db import dbtbl_to_shp as db_to_shp
from gasp.gql.ovly import feat_within, feat_not_within
from gasp.gt.gop.genze import dissolve
from gasp.gt.tbl.grs import add_table
time_a = dt.datetime.now().replace(microsecond=0)
new_build = feat_within(
osmdb,
("(SELECT buildings AS pnt_build, geometry AS pnt_geom "
"FROM {} WHERE buildings IS NOT NULL)").format(pntTable),
"pnt_geom",
("(SELECT buildings AS poly_build, geometry AS poly_geom "
"FROM {} WHERE buildings IS NOT NULL)").format(polyTable),
"poly_geom",
"new_buildings",
inTblCols="pnt_build AS cls",
withinCols="poly_geom AS geometry",
outTblIsFile=None,
apiToUse="OGR_SPATIALITE" if apidb != "POSTGIS" else apidb,
geom_col="geometry")
time_b = dt.datetime.now().replace(microsecond=0)
yes_build = feat_not_within(
osmdb,
("(SELECT buildings AS poly_build, geometry AS poly_geom "
"FROM {} WHERE buildings IS NOT NULL)").format(polyTable),
"poly_geom",
("(SELECT buildings AS pnt_build, geometry AS pnt_geom "
"FROM {} WHERE buildings IS NOT NULL)").format(pntTable),
"pnt_geom",
"yes_builds",
inTblCols="poly_geom AS geometry, 11 AS cls",
outTblIsFile=None,
apiToUse="OGR_SPATIALITE" if apidb != "POSTGIS" else apidb,
geom_col="geometry")
time_c = dt.datetime.now().replace(microsecond=0)
N12 = cnt_row(osmdb,
new_build,
api='psql' if apidb == 'POSTGIS' else 'sqlite')
time_d = dt.datetime.now().replace(microsecond=0)
N11 = cnt_row(osmdb,
yes_build,
api='psql' if apidb == 'POSTGIS' else 'sqlite')
time_e = dt.datetime.now().replace(microsecond=0)
if N11:
# Add data into grasss
grsBuild11 = db_to_shp(osmdb,
yes_build,
"geometry",
"yes_builds",
filterByReg=True,
inDB='psql' if apidb == 'POSTGIS' else 'sqlite',
outShpIsGRASS=True)
time_f = dt.datetime.now().replace(microsecond=0)
# Dissolve
dissVect = dissolve(grsBuild11,
"dss_{}".format(grsBuild11),
'cls',
api="grass")
add_table(dissVect, None, lyrN=1, asCMD=True)
time_g = dt.datetime.now().replace(microsecond=0)
else:
dissVect = None
time_f = None
time_g = None
if N12:
# Add data into GRASS GIS
grsBuild12 = db_to_shp(osmdb,
new_build,
"geometry",
"pnt_build",
filterByReg=True,
inDB='psql' if apidb == 'POSTGIS' else 'sqlite',
outShpIsGRASS=True)
time_h = dt.datetime.now().replace(microsecond=0)
# Dissolve
dissVect12 = dissolve(grsBuild12,
"dss_{}".format(grsBuild12),
'cls',
api="grass")
add_table(dissVect12, None, lyrN=1, asCMD=True)
time_i = dt.datetime.now().replace(microsecond=0)
else:
dissVect12 = None
time_h = None
time_i = None
return dissVect, dissVect12, {
0: ('intersect', time_b - time_a),
1: ('disjoint', time_c - time_b),
2: ('count_b12', time_d - time_c),
3: ('count_b11', time_e - time_d),
4:
None if not time_f else ('import_b11', time_f - time_e),
5:
None if not time_g else ('dissolve_b11', time_g - time_f),
6:
None if not time_h else
('import_b12', time_h - time_g if time_g else time_h - time_e),
7:
None if not time_i else ('dissolve_b12', time_i - time_h)
}
def roads_sqdb(osmdb, lnhTbl, plTbl, apidb='SQLITE', asRst=None):
"""
Raods procedings using SQLITE
"""
import datetime
from gasp.sql.i import row_num as cnt_rows
from gasp.gt.toshp.db import dbtbl_to_shp as db_to_shp
if apidb=='SQLITE':
from gasp.gql.prox import splite_buffer as st_buffer
else:
from gasp.gql.prox import st_buffer
time_a = datetime.datetime.now().replace(microsecond=0)
NR = cnt_rows(osmdb, 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(osmdb, 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.q import exec_write_q
from gasp.gql.prox import st_near
ROADS_Q = "(SELECT{} roads, bf_roads, geometry FROM {} WHERE roads IS NOT NULL)".format(
"" if apidb == 'SQLITE' else " gid,", lnhTbl)
if apidb == 'SQLITE':
nroads = st_near(
osmdb, ROADS_Q, "geometry",
plTbl, "geometry", "near_roads",
whrNear="building IS NOT NULL", api='splite',
near_col='dist_near'
)
time_d = datetime.datetime.now().replace(microsecond=0)
# Update buffer distance field
exec_write_q(osmdb, [(
"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:
nroads = st_near(
osmdb, ROADS_Q, 'geometry',
"(SELECT * FROM {} WHERE building IS NOT NULL)".format(plTbl),
"geometry", "near_roads", intbl_pk="gid",
until_dist="12", near_col="dist_near"
)
time_d = datetime.datetime.now().replace(microsecond=0)
exec_write_q(osmdb, [(
"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(
osmdb, 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(
osmdb, bfTbl, "geometry", "froads", notTable=None, filterByReg=True,
inDB="psql" if apidb == 'POSTGIS' else 'sqlite',
outShpIsGRASS=True
)
time_g = datetime.datetime.now().replace(microsecond=0)
if asRst:
from gasp.gt.torst import shp_to_rst
roadsGrs = shp_to_rst(
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)
}