def FilterAndExport(CLS, cnt):
time_x = datetime.datetime.now().replace(microsecond=0)
if api == 'SQLITE':
shp = sel_by_attr(
osmdb, SQL_Q.format(lc=str(CLS), tbl=polyTbl),
os.path.join(folder, 'sel_{}.shp'.format(str(CLS))),
api_gis='ogr'
)
else:
shp = sel_by_attr(
osmdb, SQL_Q.format(lc=str(CLS), tbl=polyTbl), "geometry",
os.path.join(folder, 'sel_{}.shp'.format(str(CLS))),
api='pgsql2shp', tableIsQuery=True
)
time_y = datetime.datetime.now().replace(microsecond=0)
rstCls = shp_to_rst(
shp, None, cellsize, 0,
os.path.join(folder, 'sel_{}.tif'.format(str(CLS))),
epsg=srscode, rst_template=rstTemplate, api='gdal'
)
time_z = datetime.datetime.now().replace(microsecond=0)
clsRst[int(CLS)] = rstCls
timeGasto[cnt + 1] = ('toshp_{}'.format(str(CLS)), time_y - time_x)
timeGasto[cnt + 2] = ('torst_{}'.format(str(CLS)), time_z - time_y)
def splite_buffer(db,
table,
dist,
geomField,
outTbl,
cols_select=None,
bufferField="geometry",
whrClause=None,
outTblIsFile=None,
dissolve=None):
"""
Run ST_Buffer
if not dissolve, no generalization will be applied;
if dissolve == to str or list, a generalization will be accomplish
using the fields referenced by this object;
if dissolve == 'ALL', all features will be dissolved.
"""
from gasp.pyt import obj_to_lst
dissolve = obj_to_lst(dissolve) if dissolve != "ALL" else "ALL"
sql = (
"SELECT{sel}{spFunc}{geom}, {_dist}{endFunc} AS {bf} "
"FROM {tbl}{whr}{grpBy}"
).format(
sel = " " if not cols_select else " {}, ".format(
", ".join(obj_to_lst(cols_select))
),
tbl=table,
geom=geomField, _dist=str(dist), bf=bufferField,
whr="" if not whrClause else " WHERE {}".format(whrClause),
spFunc="ST_Buffer(" if not dissolve else \
"ST_UnaryUnion(ST_Collect(ST_Buffer(",
endFunc = ")" if not dissolve else ")))",
grpBy="" if not dissolve or dissolve == "ALL" else " GROUP BY {}".format(
", ".join(dissolve)
)
)
if outTblIsFile:
from gasp.gt.attr import sel_by_attr
sel_by_attr(db, sql, outTbl, api_gis='ogr')
else:
from gasp.sql.to import q_to_ntbl
q_to_ntbl(db, outTbl, sql, api='ogr2ogr')
return outTbl
def split_whr_attrIsTrue(osm_fc,
outputfolder,
fields=None,
sel_fields=None,
basename=None):
"""
For each field in osm table or in fields, creates a new feature class
where the field attribute is not empty
"""
import os
from gasp.gt.prop.feat import lst_fld
from gasp.gt.attr import sel_by_attr
# List table fields
tbl_fields = fields if fields else lst_fld(osm_fc)
if type(tbl_fields) == str:
tbl_fields = [tbl_fields]
if sel_fields:
sel_fields.append('geometry')
aux = 1
else:
aux = 0
# Export each field in data
outFilename = '{}.shp' if not basename else basename + '_{}.shp'
for fld in tbl_fields:
a = 0
if not aux:
sel_fields = ['geometry', fld]
else:
if fld not in sel_fields:
sel_fields.append(fld)
a += 1
sel_by_attr(
osm_fc,
"SELECT {flds} FROM {t} WHERE {f}<>''".format(
f=fld,
t=os.path.splitext(os.path.basename(osm_fc))[0],
flds=', '.join(sel_fields)),
os.path.join(
outputfolder,
outFilename.format(fld if fld.islower() else fld.lower())),
api_gis='ogr')
if a:
sel_fields.remove(fld)
def build12_torst(buildTbl):
LulcCls = q_to_obj(
osmdb,
"SELECT cls FROM {} GROUP BY cls".format(buildTbl),
db_api='psql' if apidb == 'POSTGIS' else 'sqlite').cls.tolist()
for lulc_cls in LulcCls:
time_m = dt.datetime.now().replace(microsecond=0)
# To SHP
if apidb == 'SQLITE':
shpB = sel_by_attr(osmdb,
"SELECT * FROM {} WHERE cls={}".format(
buildTbl, str(lulc_cls)),
os.path.join(
folder,
'nshp_build_{}.shp'.format(lulc_cls)),
api_gis='ogr')
else:
shpB = sel_by_attr(osmdb,
"SELECT * FROM {} WHERE cls={}".format(
buildTbl, str(lulc_cls)),
"geometry",
os.path.join(
folder,
'nshp_build_{}.shp'.format(lulc_cls)),
api='pgsql2shp',
tableIsQuery=True)
time_n = dt.datetime.now().replace(microsecond=0)
# To RST
brst = shp_to_rst(shpB,
None,
cells,
0,
os.path.join(
folder,
'nrst_build_{}.tif'.format(lulc_cls)),
srscode,
rstT,
api='gdal')
time_o = dt.datetime.now().replace(microsecond=0)
resLyr[int(lulc_cls)] = [brst]
timeGasto[int(lulc_cls)] = ('to_shp_{}'.format(str(lulc_cls)),
time_n - time_m)
timeGasto[int(lulc_cls) + 1] = ('to_rst_n_{}'.format(
str(lulc_cls)), time_o - time_n)
def exportBuild():
time_ee = datetime.datetime.now().replace(microsecond=0)
NB = row_num(
osmdata, polyTbl, where="building IS NOT NULL", api='sqlite'
)
time_e = datetime.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 = datetime.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 = datetime.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 st_dissolve(db, table, geomColumn, outTable, whrClause=None,
diss_cols=None, outTblIsFile=None, api='sqlite'):
"""
Dissolve a Polygon table
"""
from gasp.pyt import obj_to_lst
diss_cols = obj_to_lst(diss_cols) if diss_cols else None
geomcol = "geometry" if api == 'sqlite' else 'geom'
sql = (
"SELECT{selCols} ST_UnaryUnion(ST_Collect({geom})) AS {gout} "
"FROM {tbl}{whr}{grpBy}"
).format(
selCols="" if not diss_cols else " {},".format(", ".join(diss_cols)),
geom=geomColumn, tbl=table,
whr="" if not whrClause else " WHERE {}".format(whrClause),
grpBy="" if not diss_cols else " GROUP BY {}".format(
", ".join(diss_cols)
), gout=geomcol
)
if outTblIsFile:
if api == 'sqlite':
from gasp.gt.attr import sel_by_attr
sel_by_attr(db, sql, outTable, api_gis='ogr')
elif api == 'psql':
from gasp.gt.toshp.db import dbtbl_to_shp
dbtbl_to_shp(
db, table, geomColumn, outTable, api='pgsql2shp',
tableIsQuery=True
)
else:
from gasp.sql.to import q_to_ntbl
q_to_ntbl(
db, outTable, sql, api='ogr2ogr' if api == 'sqlite' else 'psql'
)
return outTable
def selAndExport(CLS, cnt):
time_x = datetime.datetime.now().replace(microsecond=0)
if api == "SQLITE":
shpCls = sel_by_attr(db,
SQL_Q.format(c=str(CLS),
tbl=polyTbl,
w=WHR.format(op=OPERATOR,
r=RULE_COL,
ga=GEOM_AREA,
cls_=CLS)),
os.path.join(
folder, "{}_{}.shp".format(RULE_COL,
CLS)),
api_gis='ogr')
else:
shpCls = sel_by_attr(db,
SQL_Q.format(c=str(CLS),
tbl=polyTbl,
w=WHR.format(op=OPERATOR,
r=RULE_COL,
ga=GEOM_AREA,
cls_=CLS)),
"geometry",
os.path.join(
folder,
"{}_{}.shp".format(RULE_COL, str(CLS))),
api='pgsql2shp',
tableIsQuery=True)
time_y = datetime.datetime.now().replace(microsecond=0)
rst = shp_to_rst(shpCls,
None,
cellsize,
0,
os.path.join(folder,
"{}_{}.tif".format(RULE_COL, CLS)),
epsg=srscode,
rst_template=rstTemplate,
api='gdal')
time_z = datetime.datetime.now().replace(microsecond=0)
clsRst[int(CLS)] = rst
timeGasto[cnt + 1] = ('sq_to_shp_{}'.format(str(CLS)), time_y - time_x)
timeGasto[cnt + 2] = ('shp_to_rst_{}'.format(str(CLS)),
time_z - time_y)
def osm_to_featcls(xmlOsm,
output,
fileFormat='.shp',
useXmlName=None,
outepsg=4326):
"""
OSM to ESRI Shapefile
"""
import os
from gasp.gt.attr import sel_by_attr
from gasp.pyt.oss import fprop, del_file
# Convert xml to sqliteDB
gpkg = osm_to_gpkg(xmlOsm,
os.path.join(output,
fprop(xmlOsm, 'fn') + '.gpkg'))
# sqliteDB to Feature Class
TABLES = {
'points': 'pnt',
'lines': 'lnh',
'multilinestrings': 'mlnh',
'multipolygons': 'poly'
}
for T in TABLES:
sel_by_attr(
gpkg,
"SELECT * FROM {}".format(T),
os.path.join(
output, "{}{}{}".format(
"" if not useXmlName else fprop(xmlOsm, 'fn') + "_",
TABLES[T],
fileFormat if fileFormat[0] == '.' else "." + fileFormat)),
api_gis='ogr',
oEPSG=None if outepsg == 4326 else outepsg,
iEPSG=4326)
# Del temp DB
del_file(gpkg)
return output
def splitShp_by_range(shp, nrFeat, outFolder):
"""
Split one feature class by range
"""
import os
from gasp.pyt.oss import fprop
from gasp.gt.prop.feat import feat_count, lst_fld
from gasp.gt.attr import sel_by_attr
rowsN = feat_count(shp, gisApi='ogr')
nrShp = int(rowsN / float(nrFeat)) + 1 if nrFeat != rowsN else 1
fields = lst_fld(shp)
offset = 0
exportedShp = []
for i in range(nrShp):
f = fprop(shp, ['fn', 'ff'], forceLower=True)
outShp = sel_by_attr(
shp,
"SELECT {cols}, geometry FROM {t} ORDER BY {cols} LIMIT {l} OFFSET {o}".format(
t=os.path.splitext(os.path.basename(shp))[0],
l=str(nrFeat), o=str(offset),
cols=", ".join(fields)
),
os.path.join(outFolder, "{}_{}{}".format(
f['filename'], str(i), f['fileformat']
)), api_gis='ogr'
)
exportedShp.append(outShp)
offset += nrFeat
return exportedShp
def get_not_used_tags(OSM_FILE, OUT_TBL):
"""
Use a file OSM to detect tags not considered in the
OSM2LULC procedure
"""
import os
from gasp.to import obj_to_tbl
from gasp.gt.attr import sel_by_attr
from gasp.sql.fm import q_to_obj
from gasp.pyt.df.split import df_split
from gasp.pyt.oss import fprop
from gasp.gt.toshp.osm import osm_to_gpkg
OSM_TAG_MAP = {
"DB":
os.path.join(
os.path.dirname(os.path.dirname(os.path.abspath(__file__))),
'osmtolulc.sqlite'),
"OSM_FEAT":
"osm_features",
"KEY_COL":
"key",
"VALUE_COL":
"value",
"GEOM_COL":
"geom"
}
WORKSPACE = os.path.dirname(OUT_TBL)
sqdb = osm_to_gpkg(
OSM_FILE, os.path.join(WORKSPACE,
fprop(OSM_FILE, 'fn') + '.gpkg'))
# Get Features we are considering
ourOSMFeatures = q_to_obj(
OSM_TAG_MAP["DB"],
("SELECT {key} AS key_y, {value} AS value_y, {geom} AS geom_y "
"FROM {tbl}").format(key=OSM_TAG_MAP["KEY_COL"],
value=OSM_TAG_MAP["VALUE_COL"],
geom=OSM_TAG_MAP["GEOM_COL"],
tbl=OSM_TAG_MAP["OSM_FEAT"]),
db_api='sqlite')
# Get Features in File
TABLES_TAGS = {
'points': ['highway', 'man_made', 'building'],
'lines':
['highway', 'waterway', 'aerialway', 'barrier', 'man_made', 'railway'],
'multipolygons': [
'aeroway', 'amenity', 'barrier', 'building', 'craft', 'historic',
'land_area', ''
'landuse', 'leisure', 'man_made', 'military', 'natural', 'office',
'place', 'shop', 'sport', 'tourism', 'waterway', 'power',
'railway', 'healthcare', 'highway'
]
}
Qs = [
" UNION ALL ".join([(
"SELECT '{keycol}' AS key, {keycol} AS value, "
"'{geomtype}' AS geom FROM {tbl} WHERE "
"{keycol} IS NOT NULL"
).format(
keycol=c, geomtype='Point' if table == 'points' else 'Line' \
if table == 'lines' else 'Polygon',
tbl=table
) for c in TABLES_TAGS[table]]) for table in TABLES_TAGS
]
fileOSMFeatures = q_to_obj(sqdb,
("SELECT key, value, geom FROM ({}) AS foo "
"GROUP BY key, value, geom").format(
" UNION ALL ".join(Qs)),
db_api='sqlite')
_fileOSMFeatures = fileOSMFeatures.merge(
ourOSMFeatures,
how='outer',
left_on=["key", "value", "geom"],
right_on=["key_y", "value_y", "geom_y"])
# Select OSM Features of file without correspondence
_fileOSMFeatures["isnew"] = _fileOSMFeatures.key_y.fillna(value='nenhum')
newTags = _fileOSMFeatures[_fileOSMFeatures.isnew == 'nenhum']
newTags["value"] = newTags.value.str.replace("'", "''")
newTags["whr"] = newTags.key + "='" + newTags.value + "'"
# Export tags not being used to new shapefile
def to_regular_str(row):
san_str = row.whr
row["whr_san"] = san_str
return row
for t in TABLES_TAGS:
if t == 'points':
filterDf = newTags[newTags.geom == 'Point']
elif t == 'lines':
filterDf = newTags[newTags.geom == 'Line']
elif t == 'multipolygons':
filterDf = newTags[newTags.geom == 'Polygon']
if filterDf.shape[0] > 500:
dfs = df_split(filterDf, 500, nrows=True)
else:
dfs = [filterDf]
Q = "SELECT * FROM {} WHERE {}".format(
t, filterDf.whr.str.cat(sep=" OR "))
i = 1
for df in dfs:
fn = t + '.shp' if len(dfs) == 1 else '{}_{}.shp'.format(t, str(i))
try:
shp = sel_by_attr(sqdb,
Q.format(t, df.whr.str.cat(sep=" OR ")),
os.path.join(WORKSPACE, fn),
api_gis='ogr')
except:
__df = df.apply(lambda x: to_regular_str(x), axis=1)
shp = sel_by_attr(sqdb,
Q.format(t, __df.whr.str.cat(sep=" OR ")),
os.path.join(WORKSPACE, fn))
i += 1
# Export OUT_TBL with tags not being used
newTags.drop(['key_y', 'value_y', 'geom_y', 'isnew', 'whr'],
axis=1,
inplace=True)
obj_to_tbl(newTags, OUT_TBL, sheetsName="new_tags", sanitizeUtf8=True)
return OUT_TBL
def st_near(db,
inTbl,
inGeom,
nearTbl,
nearGeom,
output,
near_col='near',
api='psql',
whrNear=None,
outIsFile=None,
until_dist=None,
cols_in_tbl=None,
intbl_pk=None,
cols_near_tbl=None):
"""
Near tool for PostGIS and Spatialite
api options:
* psql
* splite or spatialite
"""
if api == 'psql' and not intbl_pk:
from gasp.pyt import obj_to_lst
from gasp.sql.to import q_to_ntbl
_out = q_to_ntbl(
db,
output,
("SELECT m.*, ST_Distance(m.{ingeom}, j.geom) AS {distCol} "
"FROM {t} AS m, ("
"SELECT ST_UnaryUnion(ST_Collect({neargeom})) AS geom "
"FROM {tblNear}{nearwhr}"
") AS j").format(ingeom=inGeom,
distCol=near_col,
t=inTbl,
neargeom=nearGeom,
tblNear=nearTbl),
api='psql')
return output
elif api == 'psql' and intbl_pk:
from gasp.pyt import obj_to_lst
from gasp.sql.to import q_to_ntbl
_out = q_to_ntbl(
db,
output,
("SELECT DISTINCT ON (s.{col_pk}) "
"{inTblCols}, {nearTblCols}"
"ST_Distance("
"s.{ingeomCol}, h.{negeomCol}"
") AS {nearCol} FROM {in_tbl} AS s "
"LEFT JOIN {near_tbl} AS h "
"ON ST_DWithin(s.{ingeomCol}, h.{negeomCol}, {dist_v}) "
"ORDER BY s.{col_pk}, ST_Distance(s.{ingeomCol}, h.{negeomCol})"
).format(
col_pk=intbl_pk,
inTblCols="s.*" if not cols_in_tbl else ", ".join(
["s.{}".format(x) for x in obj_to_lst(cols_in_tbl)]),
nearTblCols="" if not cols_near_tbl else ", ".join(
["h.{}".format(x)
for x in obj_to_lst(cols_near_tbl)]) + ", ",
ingeomCol=inGeom,
negeomCol=nearGeom,
nearCol=near_col,
in_tbl=inTbl,
near_tbl=nearTbl,
dist_v="100000" if not until_dist else until_dist),
api='psql')
return output
elif api == 'splite' or api == 'spatialite':
Q = ("SELECT m.*, ST_Distance(m.{ingeom}, j.geom) AS {distCol} "
"FROM {t} AS m, ("
"SELECT ST_UnaryUnion(ST_Collect({neargeom})) AS geom "
"FROM {tblNear}{nearwhr}"
") AS j").format(
ingeom=inGeom,
distCol=near_col,
t=inTbl,
neargeom=nearGeom,
tblNear=nearTbl,
nearwhr="" if not whrNear else " WHERE {}".format(whrNear))
if outIsFile:
from gasp.gt.attr import sel_by_attr
sel_by_attr(db, Q, output, api_gis='ogr')
else:
from gasp.sql.to import q_to_ntbl
q_to_ntbl(db, output, Q, api='ogr2ogr')
return output
else:
raise ValueError("api {} does not exist!".format(api))
def rn_cols(inShp, columns, api="ogr2ogr"):
"""
Rename Columns in Shp
api options:
* ogr2ogr;
* grass;
* pygrass;
"""
if api == "ogr2ogr":
import os
from gasp.pyt import obj_to_lst
from gasp.pyt.oss import fprop
from gasp.pyt.oss import del_file, lst_ff
from gasp.gt.attr import sel_by_attr
from gasp.gt.prop.fld import lst_cols
# List Columns
cols = lst_cols(inShp)
for c in cols:
if c in columns:
continue
else:
columns[c] = c
columns["geometry"] = "geometry"
# Get inShp Folder
inshpfld = os.path.dirname(inShp)
# Get inShp Filename and format
inshpname = fprop(inShp, 'fn')
# Temporary output
output = os.path.join(inshpfld, inshpname + '_xtmp.shp')
# Rename columns by selecting data from input
outShp = sel_by_attr(
inShp,
"SELECT {} FROM {}".format(
", ".join(["{} AS {}".format(c, columns[c]) for c in columns]),
inshpname),
output,
api_gis='ogr')
# Delete Original file
infiles = lst_ff(inshpfld, filename=inshpname)
del_file(infiles)
# Rename Output file
oufiles = lst_ff(inshpfld, filename=inshpname + '_xtmp')
for f in oufiles:
os.rename(f, os.path.join(inshpfld, inshpname + fprop(f, 'ff')))
elif api == 'grass':
from gasp import exec_cmd
for col in columns:
rcmd = exec_cmd(
("v.db.renamecolumn map={} layer=1 column={},{}").format(
inShp, col, columns[col]))
elif api == 'pygrass':
from grass.pygrass.modules import Module
for col in columns:
func = Module("v.db.renamecolumn",
map=inShp,
column="{},{}".format(col, columns[col]),
quiet=True,
run_=False)
func()
else:
raise ValueError("{} is not available".format(api))
return inShp