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