def rm_deadend(db, in_tbl, out_tbl):
"""
Remove deadend
"""
from glass.ng.prop.sql import cols_name, row_num
from glass.ng.sql.q import q_to_ntbl
from glass.ng.sql.tbl import rename_tbl
# Sanitize In table
cols = ", ".join([
c for c in cols_name(db, in_tbl, sanitizeSpecialWords=True, api='psql')
if c != 'geom' and c != 'gid'
])
_t = q_to_ntbl(db, "san_geom",
("SELECT gid, {cln}, geom, "
"ST_AsText(ST_StartPoint(geom)) AS pnt_start, "
"ST_AsText(ST_EndPoint(geom)) AS pnt_end FROM ("
"SELECT gid, {cln}, (ST_Dump(geom)).geom AS geom "
"FROM {t}"
") AS foo").format(cln=cols, t=in_tbl))
run_ = 1
i = 1
while run_:
# Get Table with Points of lines to delete
delpnt = q_to_ntbl(db, "del_pnt_{}".format(
str(i)), ("SELECT ROW_NUMBER() OVER (ORDER BY txtgeom) AS idx, "
"txtgeom FROM ("
"SELECT txtgeom, COUNT(txtgeom) AS npnt FROM ("
"SELECT pnt_start AS txtgeom "
"FROM {t} UNION ALL "
"SELECT pnt_end AS txtgeom "
"FROM {t}"
") AS tbl GROUP BY txtgeom"
") AS delg WHERE npnt=1").format(t=_t))
npnt = row_num(db, delpnt, api='psql')
if not npnt:
run_ = None
break
# Get Lines without dead-end
Q = ("SELECT mtbl.* "
"FROM {mtbl} AS mtbl LEFT JOIN {ptbl} AS st_tbl "
"ON mtbl.pnt_start = st_tbl.txtgeom "
"LEFT JOIN {ptbl} AS end_tbl "
"ON mtbl.pnt_end = end_tbl.txtgeom "
"WHERE st_tbl.txtgeom IS NULL AND "
"end_tbl.txtgeom IS NULL").format(cls=cols, mtbl=_t, ptbl=delpnt)
_t = q_to_ntbl(db, "rows_{}".format(str(i)), Q)
i += 1
rename_tbl(db, {_t: out_tbl})
return out_tbl
def feat_count(shp, gisApi='pandas', work=None, loc=None):
"""
Count the number of features in a feature class
API'S Available:
* gdal;
* arcpy;
* pygrass;
* pandas;
"""
if gisApi == 'ogr':
from osgeo import ogr
from glass.g.prop import drv_name
data = ogr.GetDriverByName(drv_name(shp)).Open(shp, 0)
lyr = data.GetLayer()
fcnt = int(lyr.GetFeatureCount())
data.Destroy()
elif gisApi == 'grass':
if not work or not loc:
raise ValueError((
"If gisApi=='grass', work and loc must be defined!"
))
import os
from glass.ng.prop.sql import row_num
db = os.path.join(
work, loc, 'PERMANENT', 'sqlite', 'sqlite.db'
)
fcnt = row_num(db, shp, api='sqlite')
elif gisApi == 'pandas':
from glass.g.rd.shp import shp_to_obj
gdf = shp_to_obj(shp)
fcnt = int(gdf.shape[0])
del gdf
else:
raise ValueError('The api {} is not available'.format(gisApi))
return fcnt
def split_table_by_range(db, table, row_number):
"""
Split tables in several
"""
from glass.ng.prop.sql import cols_name, row_num
from glass.ng.sql.q import q_to_ntbl
rowsN = row_num(db, table, api='psql')
nrTables = int(rowsN / float(row_number)) + 1
COLS = cols_name(db, table)
offset = 0
for i in range(nrTables):
q_to_ntbl(db,
'{}_{}'.format(table, str(i)),
"SELECT * FROM {} ORDER BY {} OFFSET {} LIMIT {} ;".format(
table, ', '.join(COLS), str(offset), str(row_number)),
api='psql')
offset += row_number
def exportBuild():
time_ee = dt.datetime.now().replace(microsecond=0)
NB = row_num(osmdata,
polyTbl,
where="building IS NOT NULL",
api='sqlite')
time_e = dt.datetime.now().replace(microsecond=0)
timeGasto[3] = ('check_builds', time_e - time_ee)
if not NB:
return
bShp = sel_by_attr(
osmdata,
"SELECT geometry FROM {} WHERE building IS NOT NULL".format(
polyTbl),
os.path.join(folder, 'road_builds.shp'),
api_gis='ogr')
time_f = dt.datetime.now().replace(microsecond=0)
bRst = shp_to_rst(bShp,
None,
cellsize,
-1,
os.path.join(folder, 'road_builds.tif'),
epsg=srs,
rst_template=rstTemplate,
api='gdal')
time_g = dt.datetime.now().replace(microsecond=0)
BUILDINGS.append(bRst)
timeGasto[4] = ('export_builds', time_f - time_e)
timeGasto[5] = ('builds_to_rst', time_g - time_f)
def grs_rst_roads(osmdb, lineTbl, polyTbl, dataFolder, LULC_CLS):
"""
Raster Roads for GRASS
"""
import datetime
from glass.g.it.shp import dbtbl_to_shp
from glass.g.dp.torst import grsshp_to_grsrst as shp_to_rst
from glass.g.gp.prox.bfing.sql import splite_buffer
from glass.ng.prop.sql import row_num
time_a = datetime.datetime.now().replace(microsecond=0)
NR = row_num(osmdb, lineTbl, where="roads IS NOT NULL", api='sqlite')
time_b = datetime.datetime.now().replace(microsecond=0)
if not NR: return None, {0: ('count_rows_roads', time_b - time_a)}
roadFile = splite_buffer(
osmdb,
lineTbl,
"bf_roads",
"geometry",
'bfu_roads',
#os.path.join(dataFolder, 'bf_roads.gml'),
whrClause="roads IS NOT NULL",
outTblIsFile=None,
dissolve="ALL")
time_c = datetime.datetime.now().replace(microsecond=0)
#roadGrs = shp_to_grs(roadFile, "bf_roads", filterByReg=True, asCMD=True)
roadGrs = dbtbl_to_shp(osmdb,
roadFile,
"geom",
'bf_roads',
notTable=True,
outShpIsGRASS=True,
inDB='sqlite')
time_d = datetime.datetime.now().replace(microsecond=0)
roadRst = shp_to_rst(roadGrs, int(LULC_CLS), "rst_roads", cmd=True)
time_e = datetime.datetime.now().replace(microsecond=0)
# Builds to GRASS and to RASTER
NB = row_num(osmdb, polyTbl, where="building IS NOT NULL", api='sqlite')
time_f = datetime.datetime.now().replace(microsecond=0)
if NB:
from glass.g.rst.alg import rstcalc
from glass.g.rst.rcls import set_null, null_to_value
buildsShp = dbtbl_to_shp(osmdb,
polyTbl,
"geom",
"all_builds",
where="building IS NOT NULL",
notTable=True,
outShpIsGRASS=True,
inDB='sqlite')
time_g = datetime.datetime.now().replace(microsecond=0)
buildsRst = shp_to_rst(buildsShp, 1, "rst_builds", cmd=True)
time_h = datetime.datetime.now().replace(microsecond=0)
# Buildings to nodata | Nodata to 0
null_to_value(buildsRst, 0, as_cmd=True)
time_i = datetime.datetime.now().replace(microsecond=0)
set_null(buildsRst, 1, ascmd=True)
time_j = datetime.datetime.now().replace(microsecond=0)
# Do the math: roads + builds | if builds and roads at the same cell
# cell will be null in the road layer
roadsRes = rstcalc("{} + {}".format(roadRst, buildsRst),
"cls_roads",
api="grass")
time_l = datetime.datetime.now().replace(microsecond=0)
return {
LULC_CLS: roadsRes
}, {
0: ('count_rows_roads', time_b - time_a),
1: ('buffer_roads', time_c - time_b),
2: ('import_roads', time_d - time_c),
3: ('roads_to_rst', time_e - time_d),
4: ('count_build', time_f - time_e),
5: ('builds_to_grs', time_g - time_f),
6: ('builds_to_rst', time_h - time_g),
7: ('bnull_to_val', time_i - time_h),
8: ('builds_to_nd', time_j - time_i),
9: ('roads_build_mc', time_l - time_j)
}
else:
return {
LULC_CLS: roadRst
}, {
0: ('count_rows_roads', time_b - time_a),
1: ('buffer_roads', time_c - time_b),
2: ('import_roads', time_d - time_c),
3: ('roads_to_rst', time_e - time_d),
4: ('count_build', time_f - time_e)
}
def pg_num_roads(osmdb, 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 glass.ng.prop.sql import row_num
from glass.g.gp.prox.bfing.sql import st_buffer
from glass.g.dp.torst import shp_to_rst
# There are roads?
time_a = datetime.datetime.now().replace(microsecond=0)
NR = row_num(osmdb, 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(osmdb, polyTbl, where="building IS NOT NULL", api='psql')
time_c = datetime.datetime.now().replace(microsecond=0)
if NB:
from glass.g.gp.prox.sql import st_near
from glass.ng.sql.q import exec_write_q
nroads = st_near(
osmdb,
("(SELECT gid, roads, bf_roads, geometry FROM {} "
"WHERE roads IS NOT NULL)").format(lnhTbl),
"geometry",
("(SELECT * FROM {} WHERE building IS NOT NULL)").format(polyTbl),
"geometry",
"near_roads",
until_dist="12",
near_col="dist_near",
intbl_pk="gid")
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"
), "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(osmdb,
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_rst(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 num_roads(osmdata, nom, lineTbl, polyTbl, folder, cellsize, srs,
rstTemplate):
"""
Select Roads and convert To Raster
"""
import datetime as dt
import os
import numpy as np
from threading import Thread
from glass.g.rd.rst import rst_to_array
from glass.g.tbl.filter import sel_by_attr
from glass.g.gp.prox.bfing.sql import splite_buffer
from glass.g.dp.torst import shp_to_rst
from glass.g.wt.rst import obj_to_rst
from glass.ng.prop.sql import row_num
time_a = dt.datetime.now().replace(microsecond=0)
NR = row_num(osmdata, lineTbl, where="roads IS NOT NULL", api='sqlite')
time_b = dt.datetime.now().replace(microsecond=0)
if not NR: return None, {0: ('count_rows_roads', time_b - time_a)}
timeGasto = {0: ('count_rows_roads', time_b - time_a)}
# Get Roads Buffer
LULC_CLS = '1221' if nom != "GLOBE_LAND_30" else '801'
bfShps = []
def exportAndBuffer():
time_cc = dt.datetime.now().replace(microsecond=0)
roadFile = splite_buffer(osmdata,
lineTbl,
"bf_roads",
"geometry",
os.path.join(folder, 'bf_roads.gml'),
whrClause="roads IS NOT NULL",
outTblIsFile=True,
dissolve=None)
time_c = dt.datetime.now().replace(microsecond=0)
distRst = shp_to_rst(roadFile,
None,
cellsize,
-1,
os.path.join(folder, 'rst_roads.tif'),
epsg=srs,
rst_template=rstTemplate,
api="gdal")
time_d = dt.datetime.now().replace(microsecond=0)
bfShps.append(distRst)
timeGasto[1] = ('buffer_roads', time_c - time_cc)
timeGasto[2] = ('to_rst_roads', time_d - time_c)
BUILDINGS = []
def exportBuild():
time_ee = dt.datetime.now().replace(microsecond=0)
NB = row_num(osmdata,
polyTbl,
where="building IS NOT NULL",
api='sqlite')
time_e = dt.datetime.now().replace(microsecond=0)
timeGasto[3] = ('check_builds', time_e - time_ee)
if not NB:
return
bShp = sel_by_attr(
osmdata,
"SELECT geometry FROM {} WHERE building IS NOT NULL".format(
polyTbl),
os.path.join(folder, 'road_builds.shp'),
api_gis='ogr')
time_f = dt.datetime.now().replace(microsecond=0)
bRst = shp_to_rst(bShp,
None,
cellsize,
-1,
os.path.join(folder, 'road_builds.tif'),
epsg=srs,
rst_template=rstTemplate,
api='gdal')
time_g = dt.datetime.now().replace(microsecond=0)
BUILDINGS.append(bRst)
timeGasto[4] = ('export_builds', time_f - time_e)
timeGasto[5] = ('builds_to_rst', time_g - time_f)
thrds = [
Thread(name="build-th", target=exportBuild),
Thread(name='roads-th', target=exportAndBuffer)
]
for t in thrds:
t.start()
for t in thrds:
t.join()
if not len(BUILDINGS):
return {LULC_CLS: bfShps[0]}
time_x = dt.datetime.now().replace(microsecond=0)
BUILD_ARRAY = rst_to_array(BUILDINGS[0], with_nodata=True)
rst_array = rst_to_array(bfShps[0], with_nodata=True)
np.place(rst_array, BUILD_ARRAY == 1, 0)
newRaster = obj_to_rst(rst_array,
os.path.join(folder, 'fin_roads.tif'),
rstTemplate,
noData=-1)
time_z = dt.datetime.now().replace(microsecond=0)
timeGasto[6] = ('sanitize_roads', time_z - time_x)
return {int(LULC_CLS): newRaster}, timeGasto
def grs_vec_roads(osmdb, lineTbl, polyTbl):
"""
Select Roads for GRASS GIS
"""
import datetime
from glass.ng.prop.sql import row_num
from glass.g.it.shp import dbtbl_to_shp
from glass.g.gp.prox.bfing import _buffer
from glass.g.gp.gen import dissolve
from glass.g.tbl.grs import add_table
# Roads to GRASS GIS
time_a = datetime.datetime.now().replace(microsecond=0)
NR = row_num(osmdb, lineTbl, where="roads IS NOT NULL", api='sqlite')
time_b = datetime.datetime.now().replace(microsecond=0)
if not NR: return None, {0: ('count_rows_roads', time_b - time_a)}
roadsVect = dbtbl_to_shp(osmdb,
lineTbl,
"geometry",
"all_roads",
where="roads IS NOT NULL",
inDB='sqlite',
outShpIsGRASS=True)
time_c = datetime.datetime.now().replace(microsecond=0)
# Buildings to GRASS GIS
NB = row_num(osmdb, polyTbl, where="building IS NOT NULL", api='sqlite')
time_d = datetime.datetime.now().replace(microsecond=0)
if NB:
from glass.g.gp.prox import grs_near as near
from glass.g.tbl.grs import update_table
builds = dbtbl_to_shp(osmdb,
polyTbl,
"geometry",
"all_builds",
where="building IS NOT NULL",
filterByReg=True,
inDB='sqlite',
outShpIsGRASS=True)
time_e = datetime.datetime.now().replace(microsecond=0)
near(roadsVect, builds, nearDistCol="todist", maxDist=12, as_cmd=True)
time_f = datetime.datetime.now().replace(microsecond=0)
update_table(roadsVect,
"bf_roads",
"round(todist,0)",
"\"todist > 0\"",
lyrN=1,
ascmd=True)
time_g = datetime.datetime.now().replace(microsecond=0)
else:
time_e = None
time_f = None
time_g = None
# Run Buffer tool
roadsBf = _buffer(roadsVect,
"bf_roads",
"bf_roads",
api='grass',
geom_type="line")
time_h = datetime.datetime.now().replace(microsecond=0)
# Dissolve Roads
roadsDiss = dissolve(roadsBf, "diss_roads", "roads", api="grass")
add_table(roadsDiss, None, lyrN=1, asCMD=True)
time_i = datetime.datetime.now().replace(microsecond=0)
return roadsDiss, {
0: ('count_rows_roads', time_b - time_a),
1: ('import_roads', time_c - time_b),
2: ('count_rows_build', time_d - time_c),
3: None if not time_e else ('import_builds', time_e - time_d),
4: None if not time_f else ('near_analysis', time_f - time_e),
5: None if not time_g else ('update_buffer_tbl', time_g - time_f),
6: ('buffer_roads', time_h - time_g if time_g else time_h - time_d),
7: ('diss_roads', time_i - time_h)
}