def txts_to_db(folder, db, delimiter, __encoding='utf-8', apidb='psql',
rewrite=None):
"""
Executes tbl_to_db for every file in a given folder
The file name will be the table name
"""
from gasp.pyt.oss import lst_ff, fprop
from gasp.sql.i import db_exists
if not db_exists(db):
# Create database
from gasp.sql.db import create_db
create_db(db, api=apidb, overwrite=None)
else:
if rewrite:
from gasp.sql.db import create_db
create_db(db, api=db, overwrite=True)
__files = lst_ff(folder, file_format=['.txt', '.csv', '.tsv'])
"""
Send data to DB using Pandas
"""
for __file in __files:
tbl_to_db(
__file, db, fprop(__file, 'fn'),
delimiter=delimiter, encoding_=__encoding, api_db=apidb
)
def osm_to_psql(osmXml, osmdb):
"""
Use GDAL to import osmfile into PostGIS database
"""
from gasp import exec_cmd
from gasp.cons.psql import con_psql
from gasp.sql.i import db_exists
is_db = db_exists(osmdb)
if not is_db:
from gasp.sql.db import create_db
create_db(osmdb, api='psql')
con = con_psql()
cmd = ("ogr2ogr -f PostgreSQL \"PG:dbname='{}' host='{}' port='{}' "
"user='******' password='******'\" {} -lco COLUM_TYPES=other_tags=hstore"
).format(osmdb, con["HOST"], con["PORT"], con["USER"],
con["PASSWORD"], osmXml)
cmdout = exec_cmd(cmd)
return osmdb
def break_lines_on_points(lineShp, pntShp, outShp, lnhidonpnt,
api='shply', db=None):
"""
Break lines on points location
api's available:
- shply (shapely);
- psql (postgis);
"""
if api == 'shply':
result = shply_break_lines_on_points(
lineShp, pntShp, lnhidonpnt, outShp)
elif api == 'psql':
from gasp.pyt.oss import fprop
from gasp.sql.db import create_db
from gasp.gql.to import shp_to_psql
from gasp.gt.toshp.db import dbtbl_to_shp
from gasp.gql.brk import split_lines_on_pnt
# Create DB
if not db:
db = create_db(fprop(lineShp, 'fn', forceLower=True), api='psql')
else:
from gasp.sql.i import db_exists
isDb = db_exists(db)
if not isDb:
db = create_db(db, api='psql')
# Send Data to BD
lnhTbl = shp_to_psql(db, lineShp, api="shp2pgsql")
pntTbl = shp_to_psql(db, pntShp, api="shp2pgsql")
# Get result
outTbl = split_lines_on_pnt(
db, lnhTbl, pntTbl,
fprop(outShp, 'fn', forceLower=True),
lnhidonpnt, 'gid'
)
# Export result
result = dbtbl_to_shp(
db, outTbl, "geom", outShp, inDB='psql', tableIsQuery=None,
api="pgsql2shp"
)
else:
raise ValueError(
"API {} is not available".format(api)
)
return result
def line_intersect_to_pnt(inShp, outShp, db=None):
"""
Get Points where two line features of the same feature class
intersects.
"""
from gasp.pyt.oss import fprop
from gasp.gt.toshp.db import dbtbl_to_shp
from gasp.sql.db import create_db
from gasp.gql.to import shp_to_psql
from gasp.gql.ovly import line_intersection_pnt
# Create DB if necessary
if not db:
db = create_db(fprop(inShp, 'fn', forceLower=True), api='psql')
else:
from gasp.sql.i import db_exists
isDb = db_exists(db)
if not isDb:
create_db(db, api='psql')
# Send data to DB
inTbl = shp_to_psql(db, inShp, api="shp2pgsql")
# Get result
outTbl = line_intersection_pnt(db, inTbl,
fprop(outShp, 'fn', forceLower=True))
# Export data from DB
outShp = dbtbl_to_shp(db,
outTbl,
"geom",
outShp,
inDB='psql',
tableIsQuery=None,
api="pgsql2shp")
return outShp
def tbl_to_areamtx(inShp, col_a, col_b, outXls, db=None, with_metrics=None):
"""
Table to Matrix
Table as:
FID | col_a | col_b | geom
0 | 1 | A | A | ....
0 | 2 | A | B | ....
0 | 3 | A | A | ....
0 | 4 | A | C | ....
0 | 5 | A | B | ....
0 | 6 | B | A | ....
0 | 7 | B | A | ....
0 | 8 | B | B | ....
0 | 9 | B | B | ....
0 | 10 | C | A | ....
0 | 11 | C | B | ....
0 | 11 | C | D | ....
To:
classe | A | B | C | D
A | | | |
B | | | |
C | | | |
D | | | |
col_a = rows
col_b = cols
api options:
* pandas;
* psql;
"""
if not db:
import pandas as pd
import numpy as np
from gasp.gt.fmshp import shp_to_obj
from gasp.to import obj_to_tbl
# Open data
df = shp_to_obj(inShp)
# Remove nan values by -9999
df = df[pd.notnull(df[col_a])]
df = df[pd.notnull(df[col_b])]
# Get Area
df['realarea'] = df.geometry.area / 1000000
# Get rows and Cols
rows = df[col_a].unique()
cols = df[col_b].unique()
refval = list(np.sort(np.unique(np.append(rows, cols))))
# Produce matrix
outDf = []
for row in refval:
newCols = [row]
for col in refval:
newDf = df[(df[col_a] == row) & (df[col_b] == col)]
if not newDf.shape[0]:
newCols.append(0)
else:
area = newDf.realarea.sum()
newCols.append(area)
outDf.append(newCols)
outcols = ['class'] + refval
outDf = pd.DataFrame(outDf, columns=outcols)
if with_metrics:
from gasp.pyt.dtcls.eval import get_measures_for_mtx
out_df = get_measures_for_mtx(outDf, 'class')
return obj_to_tbl(out_df, outXls)
# Export to Excel
return obj_to_tbl(outDf, outXls)
else:
from gasp.pyt.oss import fprop
from gasp.sql.db import create_db
from gasp.sql.i import db_exists
from gasp.gql.to import shp_to_psql
from gasp.gql.tomtx import tbl_to_area_mtx
from gasp.to import db_to_tbl
# Create database if not exists
is_db = db_exists(db)
if not is_db:
create_db(db, api='psql')
# Add data to database
tbl = shp_to_psql(db, inShp, api='shp2pgsql')
# Create matrix
mtx = tbl_to_area_mtx(db, tbl, col_a, col_b, fprop(outXls, 'fn'))
# Export result
return db_to_tbl(db, mtx, outXls, sheetsNames='matrix')
def merge_dbs(destinationDb, dbs,
tbls_to_merge=None, ignoreCols=None):
"""
Put several database into one
For now works only with PostgreSQL
"""
import os
from gasp.pyt.oss import fprop, del_file
from gasp.sql import psql_cmd
from gasp.sql.i import db_exists, lst_tbl
from gasp.sql.db import create_db, drop_db
from gasp.sql.tbl import rename_tbl, tbls_to_tbl
from gasp.sql.fm import dump_tbls
from gasp.sql.to import restore_tbls
from gasp.sql.tbl import distinct_to_table, del_tables
# Prepare database
fdb = fprop(destinationDb, ['fn', 'ff'])
if os.path.isfile(destinationDb):
if fdb['fileformat'] == '.sql':
newdb = create_db(fdb['filename'],
overwrite=True, api='psql')
psql_cmd(newdb, destinationDb)
destinationDb = newdb
else:
raise ValueError((
'destinationDb is a file but is not correct. The file must be'
' a SQL Script'
))
else:
# Check if destination db exists
if not db_exists(destinationDb):
create_db(destinationDb, overwrite=None, api='psql')
# Check if dbs is a list or a dir
if type(dbs) == list:
dbs = dbs
elif os.path.isdir(dbs):
# list SQL files
from gasp.pyt.oss import lst_ff
dbs = lst_ff(dbs, file_format='.sql')
else:
raise ValueError(
'''
dbs value should be a list with paths
to sql files or a dir with sql files inside
'''
)
TABLES = {}
for i in range(len(dbs)):
# Create DB
DB_NAME = fprop(dbs[i], 'fn')
create_db(DB_NAME, overwrite=True, api='psql')
# Restore DB
psql_cmd(DB_NAME, dbs[i])
# List Tables
if not tbls_to_merge:
tbls__ = lst_tbl(DB_NAME, excludeViews=True, api='psql')
tbls = [t for t in tbls__ if t not in ignoreCols]
else:
tbls = tbls_to_merge
# Rename Tables
newTbls = rename_tbl(DB_NAME, {tbl : "{}_{}".format(
tbl, str(i)) for tbl in tbls})
for t in range(len(tbls)):
if tbls[t] not in TABLES:
TABLES[tbls[t]] = ["{}_{}".format(tbls[t], str(i))]
else:
TABLES[tbls[t]].append("{}_{}".format(tbls[t], str(i)))
# Dump Tables
SQL_DUMP = os.path.join(
os.path.dirname(dbs[i]), 'tbl_{}.sql'.format(DB_NAME)
); dump_tbls(DB_NAME, newTbls, SQL_DUMP)
# Restore Tables in the destination Database
restore_tbls(destinationDb, SQL_DUMP, newTbls)
# Delete Temp Database
drop_db(DB_NAME)
# Delete SQL File
del_file(SQL_DUMP)
# Union of all tables
max_len = max([len(TABLES[t]) for t in TABLES])
for tbl in TABLES:
# Rename original table
NEW_TBL = "{}_{}".format(tbl, max_len)
rename_tbl(destinationDb, {tbl : NEW_TBL})
TABLES[tbl].append(NEW_TBL)
# Union
tbls_to_tbl(destinationDb, TABLES[tbl], tbl + '_tmp')
# Group By
distinct_to_table(destinationDb, tbl + '_tmp', tbl, cols=None)
# Drop unwanted tables
del_tables(destinationDb, TABLES[tbl] + [tbl + '_tmp'])
return destinationDb
def proj(inShp, outShp, outEPSG, inEPSG=None,
gisApi='ogr', sql=None, db_name=None):
"""
Project Geodata using GIS
API's Available:
* ogr;
* ogr2ogr;
* pandas;
* ogr2ogr_SQLITE;
* psql;
"""
import os
if gisApi == 'ogr':
"""
Using ogr Python API
"""
if not inEPSG:
raise ValueError(
'To use ogr API, you should specify the EPSG Code of the'
' input data using inEPSG parameter'
)
from osgeo import ogr
from gasp.g.lyr.fld import copy_flds
from gasp.gt.prop.feat import get_gtype
from gasp.gt.prop.ff import drv_name
from gasp.gt.prop.prj import get_sref_from_epsg, get_trans_param
from gasp.pyt.oss import fprop
def copyShp(out, outDefn, lyr_in, trans):
for f in lyr_in:
g = f.GetGeometryRef()
g.Transform(trans)
new = ogr.Feature(outDefn)
new.SetGeometry(g)
for i in range(0, outDefn.GetFieldCount()):
new.SetField(outDefn.GetFieldDefn(i).GetNameRef(), f.GetField(i))
out.CreateFeature(new)
new.Destroy()
f.Destroy()
# ####### #
# Project #
# ####### #
transP = get_trans_param(inEPSG, outEPSG)
inData = ogr.GetDriverByName(
drv_name(inShp)).Open(inShp, 0)
inLyr = inData.GetLayer()
out = ogr.GetDriverByName(
drv_name(outShp)).CreateDataSource(outShp)
outlyr = out.CreateLayer(
fprop(outShp, 'fn'), get_sref_from_epsg(outEPSG),
geom_type=get_gtype(
inShp, name=None, py_cls=True, gisApi='ogr'
)
)
# Copy fields to the output
copy_flds(inLyr, outlyr)
# Copy/transform features from the input to the output
outlyrDefn = outlyr.GetLayerDefn()
copyShp(outlyr, outlyrDefn, inLyr, transP)
inData.Destroy()
out.Destroy()
elif gisApi == 'ogr2ogr':
"""
Transform SRS of any OGR Compilant Data. Save the transformed data
in a new file
"""
if not inEPSG:
from gasp.gt.prop.prj import get_epsg_shp
inEPSG = get_epsg_shp(inShp)
if not inEPSG:
raise ValueError('To use ogr2ogr, you must specify inEPSG')
from gasp import exec_cmd
from gasp.gt.prop.ff import drv_name
cmd = (
'ogr2ogr -f "{}" {} {}{} -s_srs EPSG:{} -t_srs EPSG:{}'
).format(
drv_name(outShp), outShp, inShp,
'' if not sql else ' -dialect sqlite -sql "{}"'.format(sql),
str(inEPSG), str(outEPSG)
)
outcmd = exec_cmd(cmd)
elif gisApi == 'ogr2ogr_SQLITE':
"""
Transform SRS of a SQLITE DB table. Save the transformed data in a
new table
"""
from gasp import exec_cmd
if not inEPSG:
raise ValueError((
'With ogr2ogr_SQLITE, the definition of inEPSG is '
'demandatory.'
))
# TODO: Verify if database is sqlite
db, tbl = inShp['DB'], inShp['TABLE']
sql = 'SELECT * FROM {}'.format(tbl) if not sql else sql
outcmd = exec_cmd((
'ogr2ogr -update -append -f "SQLite" {db} -nln "{nt}" '
'-dialect sqlite -sql "{_sql}" -s_srs EPSG:{inepsg} '
'-t_srs EPSG:{outepsg} {db}'
).format(
db=db, nt=outShp, _sql=sql, inepsg=str(inEPSG),
outepsg=str(outEPSG)
))
elif gisApi == 'pandas':
# Test if input Shp is GeoDataframe
from gasp.gt.fmshp import shp_to_obj
from gasp.gt.toshp import df_to_shp
df = shp_to_obj(inShp)
# Project df
newDf = df.to_crs({'init' : 'epsg:{}'.format(str(outEPSG))})
# Save as file
return df_to_shp(df, outShp)
elif gisApi == 'psql':
from gasp.sql.db import create_db
from gasp.pyt.oss import fprop
from gasp.gql.to import shp_to_psql
from gasp.gt.toshp.db import dbtbl_to_shp
from gasp.gql.prj import sql_proj
# Create Database
if not db_name:
db_name = create_db(fprop(
outShp, 'fn', forceLower=True), api='psql'
)
else:
from gasp.sql.i import db_exists
isDb = db_exists(db_name)
if not isDb:
create_db(db_name, api='psql')
# Import Data
inTbl = shp_to_psql(db_name, inShp, api='shp2pgsql', encoding="LATIN1")
# Transform
oTbl = sql_proj(
db_name, inTbl, fprop(outShp, 'fn', forceLower=True),
outEPSG, geomCol='geom', newGeom='geom'
)
# Export
outShp = dbtbl_to_shp(
db_name, oTbl, 'geom', outShp, api='psql', epsg=outEPSG
)
else:
raise ValueError('Sorry, API {} is not available'.format(gisApi))
return outShp
def lnh_to_polygons(inShp, outShp, api='saga', db=None):
"""
Line to Polygons
API's Available:
* saga;
* grass;
* pygrass;
* psql;
"""
if api == 'saga':
"""
http://www.saga-gis.org/saga_tool_doc/7.0.0/shapes_polygons_3.html
Converts lines to polygons. Line arcs are closed to polygons simply
by connecting the last point with the first. Optionally parts of
polylines can be merged into one polygon optionally.
"""
from gasp import exec_cmd
rcmd = exec_cmd(("saga_cmd shapes_polygons 3 -POLYGONS {} "
"LINES {} -SINGLE 1 -MERGE 1").format(outShp, inShp))
elif api == 'grass' or api == 'pygrass':
# Do it using GRASS GIS
import os
from gasp.gt.wenv.grs import run_grass
from gasp.pyt.oss import fprop
# Create GRASS GIS Session
wk = os.path.dirname(outShp)
lo = fprop(outShp, 'fn', forceLower=True)
gs = run_grass(wk, lo, srs=inShp)
import grass.script as grass
import grass.script.setup as gsetup
gsetup.init(gs, wk, lo, 'PERMANENT')
# Import Packages
from gasp.gt.toshp.cff import shp_to_grs, grs_to_shp
from gasp.gt.toshp.cgeo import line_to_polyline
from gasp.gt.toshp.cgeo import geomtype_to_geomtype
from gasp.gt.toshp.cgeo import boundary_to_areas
# Send data to GRASS GIS
lnh_shp = shp_to_grs(inShp,
fprop(inShp, 'fn', forceLower=True),
asCMD=True if api == 'grass' else None)
# Build Polylines
pol_lnh = line_to_polyline(lnh_shp,
"polylines",
asCmd=True if api == 'grass' else None)
# Polyline to boundary
bound = geomtype_to_geomtype(pol_lnh,
'bound_shp',
'line',
'boundary',
cmd=True if api == 'grass' else None)
# Boundary to Area
areas_shp = boundary_to_areas(bound,
lo,
useCMD=True if api == 'grass' else None)
# Export data
outShp = grs_to_shp(areas_shp,
outShp,
'area',
asCMD=True if api == 'grass' else None)
elif api == 'psql':
""" Do it using PostGIS """
from gasp.pyt.oss import fprop
from gasp.sql.db import create_db
from gasp.gql.to import shp_to_psql
from gasp.gt.toshp.db import dbtbl_to_shp
from gasp.gql.cnv import lnh_to_polg
from gasp.gt.prop.prj import get_epsg_shp
# Create DB
if not db:
db = create_db(fprop(inShp, 'fn', forceLower=True), api='psql')
else:
from gasp.sql.i import db_exists
isDB = db_exists(db)
if not isDB:
create_db(db, api='psql')
# Send data to DB
in_tbl = shp_to_psql(db, inShp, api="shp2pgsql")
# Get Result
result = lnh_to_polg(db, in_tbl, fprop(outShp, 'fn', forceLower=True))
# Export Result
outshp = dbtbl_to_shp(db,
result,
"geom",
outShp,
api='psql',
epsg=get_epsg_shp(inShp))
else:
raise ValueError("API {} is not available".format(api))
return outShp