def del_topoerror_shps(conParam, shps, epsg, outfolder):
"""
Remove topological errors from Feature Class data using PostGIS
"""
import os
from gasp import goToList
from gasp.sql.mng.fld import cols_name
from gasp.sql.mng.qw import ntbl_by_query
from gasp.to.sql import shp_to_psql
from gasp.to.shp import psql_to_shp
shps = goToList(shps)
TABLES = shp_to_psql(conParam, shps, epsg, api="shp2pgsql")
NTABLE = [ntbl_by_query(
conParam, "nt_{}".format(t),
"SELECT {cols}, ST_MakeValid({tbl}.geom) AS geom FROM {tbl}".format(
cols = ", ".join(["{}.{}".format(TABLES[t], x) for x in cols_name(
conParam, TABLES[t], sanitizeSpecialWords=None
) if x != 'geom']),
tbl=TABLES[t]
), api='psql'
) for t in range(len(TABLES))]
for t in range(len(NTABLE)):
psql_to_shp(
conParam, NTABLE[t],
os.path.join(outfolder, TABLES[t]), tableIsQuery=None,
api='pgsql2shp', geom_col="geom"
)
def sgbd_get_feat_not_within(dbcon, inTbl, inGeom, withinTbl, withinGeom, outTbl,
inTblCols=None, outTblIsFile=None,
apiToUse='OGR_SPATIALITE'):
"""
Get features not Within with any of the features in withinTbl
apiToUse options:
* OGR_SPATIALITE;
* POSTGIS.
"""
from gasp import goToList
Q = (
"SELECT {selCols} FROM {tbl} AS in_tbl WHERE ("
"in_tbl.{in_geom} NOT IN ("
"SELECT inin_tbl.{in_geom} FROM {wi_tbl} AS wi_tbl "
"INNER JOIN {tbl} AS inin_tbl ON "
"ST_Within(wi_tbl.{wi_geom}, inin_tbl.{in_geom})"
"))"
).format(
selCols = "*" if not inTblCols else ", ".join(goToList(inTblCols)),
tbl = inTbl,
in_geom = inGeom,
wi_tbl = withinTbl,
wi_geom = withinGeom
)
if apiToUse == "OGR_SPATIALITE":
if outTblIsFile:
from gasp.anls.exct import sel_by_attr
sel_by_attr(dbcon, Q, outTbl, api_gis='ogr')
else:
from gasp.sql.mng.qw import ntbl_by_query
ntbl_by_query(dbcon, outTbl, Q, api='ogr2ogr')
elif apiToUse == "POSTGIS":
if outTblIsFile:
from gasp.to.shp import psql_to_shp
psql_to_shp(
dbcon, Q, outTbl, api='pgsql2shp',
geom_col=None, tableIsQuery=True
)
else:
from gasp.sql.mng.qw import ntbl_by_query
ntbl_by_query(dbcon, outTbl, Q, api='psql')
else:
raise ValueError((
"API {} is not available. OGR_SPATIALITE and POSTGIS "
"are the only valid options"
))
return outTbl
def st_buffer(conParam,
inTbl,
bfDist,
geomCol,
outTbl,
bufferField="geometry",
whrClause=None,
dissolve=None,
cols_select=None,
outTblIsFile=None):
"""
Using Buffer on PostGIS Data
"""
from gasp import goToList
dissolve = goToList(dissolve) if dissolve != "ALL" else "ALL"
SEL_COLS = "" if not cols_select else ", ".join(goToList(cols_select))
DISS_COLS = "" if not dissolve or dissolve == "ALL" else ", ".join(
dissolve)
GRP_BY = "" if not dissolve else "{}, {}".format(SEL_COLS, DISS_COLS) if \
SEL_COLS != "" and DISS_COLS != "" else SEL_COLS \
if SEL_COLS != "" else DISS_COLS if DISS_COLS != "" else ""
Q = (
"SELECT{sel}{spFunc}{geom}, {_dist}{endFunc} AS {bf} "
"FROM {t}{whr}{grpBy}"
).format(
sel = " " if not cols_select else " {}, ".format(SEL_COLS),
spFunc="ST_Buffer(" if not dissolve else \
"ST_UnaryUnion(ST_Collect(ST_Buffer(",
geom=geomCol, _dist=bfDist,
endFunc=")" if not dissolve else ")))",
t=inTbl,
grpBy=" GROUP BY {}".format(GRP_BY) if GRP_BY != "" else "",
whr="" if not whrClause else " WHERE {}".format(whrClause),
bf=bufferField
)
if not outTblIsFile:
from gasp.sql.mng.qw import ntbl_by_query
outTbl = ntbl_by_query(conParam, outTbl, Q, api='psql')
else:
from gasp.to.shp import psql_to_shp
psql_to_shp(conParam,
Q,
outTbl,
api='pgsql2shp',
geom_col=bufferField,
tableIsQuery=True)
return outTbl
def st_dissolve(db,
table,
geomColumn,
outTable,
whrClause=None,
diss_cols=None,
outTblIsFile=None,
api='sqlite'):
"""
Dissolve a Polygon table
"""
from gasp import goToList
diss_cols = goToList(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.anls.exct import sel_by_attr
sel_by_attr(db, sql, outTable, api_gis='ogr')
elif api == 'psql':
from gasp.to.shp import psql_to_shp
psql_to_shp(db,
table,
outTable,
api='pgsql2shp',
geom_col=geomColumn,
tableIsQuery=True)
else:
from gasp.sql.mng.qw import ntbl_by_query
ntbl_by_query(db,
outTable,
sql,
api='ogr2ogr' if api == 'sqlite' else 'psql')
return outTable
def fix_geometry(shp):
# Send data to PostgreSQL
nt = shp_to_psql(conPARAM, shp, SRS_CODE, api='shp2pgsql')
# Fix data
corr_tbl = fix_geom(conPARAM,
nt,
"geom",
"corr_{}".format(nt),
colsSelect=['gid', __SHAPES_TO_COMPARE[shp]])
# Check if we have multiple geometries
geomN = check_geomtype_in_table(conPARAM, corr_tbl)
if geomN > 1:
corr_tbl = select_main_geom_type(conPARAM, corr_tbl,
"corr2_{}".format(nt))
# Export data again
newShp = psql_to_shp(conPARAM,
corr_tbl,
os.path.join(OUT_FOLDER, corr_tbl + '.shp'),
api='pgsql2shp',
geom_col='geom')
return newShp
def check_autofc_overlap(checkShp, epsg, conParam, outOverlaps):
"""
Check if the features of one Feature Class overlaps each other
"""
import os
from gasp.sql.mng.db import create_db
from gasp.sql.mng.qw import ntbl_by_query
from gasp.to.sql import shp_to_psql_tbl
from gasp.to.shp import psql_to_shp
create_db(conParam, conParam["DB"])
conParam["DATABASE"] = conParam["DB"]
# Send data to postgresql
table = shp_to_psql(conParam, checkShp, epsg, api="pandas")
# Produce result
q = (
"SELECT foo.* FROM ("
"SELECT * FROM {t}"
") AS foo, ("
"SELECT cat AS relcat, geom AS tst_geom FROM {t}"
") AS foo2 "
"WHERE ("
"ST_Overlaps(geom, tst_geom) IS TRUE OR "
"ST_Equals(geom, tst_geom) IS TRUE OR "
"ST_Contains(geom, tst_geom) IS TRUE"
") AND cat <> relcat"
).format(t=table)
resultTable = os.path.splitext(os.path.basename(outOverlaps))[0]
ntbl_by_query(conParam, resultTable, q, api='psql')
psql_to_shp(conParam, resultTable, outOverlaps, api='pandas', epsg=epsg)
return outOverlaps
def v_break_at_points(workspace, loc, lineShp, pntShp, conParam, srs, out_correct,
out_tocorrect):
"""
Break lines at points - Based on GRASS GIS v.edit
Use PostGIS to sanitize the result
TODO: Confirm utility
"""
import os
from gasp.session import run_grass
from gasp.sql.mng.db import create_db
from gasp.to.sql import shp_to_psql
from gasp.to.shp import psql_to_shp
from gasp.sql.mng.qw import ntbl_by_query
from gasp.oss import get_filename
tmpFiles = os.path.join(workspace, loc)
gbase = run_grass(workspace, location=loc, srs=srs)
import grass.script as grass
import grass.script.setup as gsetup
gsetup.init(gbase, workspace, loc, 'PERMANENT')
from gasp.to.shp.grs import shp_to_grs, grs_to_shp
grsLine = shp_to_grs(
lineShp, get_filename(lineShp, forceLower=True)
)
vedit_break(grsLine, pntShp, geomType='line')
LINES = grass_converter(
grsLine, os.path.join(tmpFiles, grsLine + '_v1.shp'), 'line')
# Sanitize output of v.edit.break using PostGIS
create_db(conParam, conParam["DB"], overwrite=True)
conParam["DATABASE"] = conParam["DB"]
LINES_TABLE = shp_to_psql(
conParam, LINES, srs,
pgTable=get_filename(LINES, forceLower=True), api="shp2pgsql"
)
# Delete old/original lines and stay only with the breaked one
Q = (
"SELECT {t}.*, foo.cat_count FROM {t} INNER JOIN ("
"SELECT cat, COUNT(cat) AS cat_count, "
"MAX(ST_Length(geom)) AS max_len "
"FROM {t} GROUP BY cat"
") AS foo ON {t}.cat = foo.cat "
"WHERE foo.cat_count = 1 OR foo.cat_count = 2 OR ("
"foo.cat_count = 3 AND ST_Length({t}.geom) <= foo.max_len)"
).format(t=LINES_TABLE)
CORR_LINES = ntbl_by_query(
conParam, "{}_corrected".format(LINES_TABLE), Q, api='psql'
)
# TODO: Delete Rows that have exactly the same geometry
# Highlight problems that the user must solve case by case
Q = (
"SELECT {t}.*, foo.cat_count FROM {t} INNER JOIN ("
"SELECT cat, COUNT(cat) AS cat_count FROM {t} GROUP BY cat"
") AS foo ON {t}.cat = foo.cat "
"WHERE foo.cat_count > 3"
).format(t=LINES_TABLE)
ERROR_LINES = ntbl_by_query(
conParam, "{}_not_corr".format(LINES_TABLE), Q, api='psql'
)
psql_to_shp(
conParam, CORR_LINES, out_correct,
api="pgsql2shp", geom_col="geom"
)
psql_to_shp(
conParam, ERROR_LINES, out_tocorrect,
api="pgsql2shp", geom_col="geom"
)
def sgbd_get_feat_within(conParam, inTbl, inGeom, withinTbl, withinGeom, outTbl,
inTblCols=None, withinCols=None, outTblIsFile=None,
apiToUse='OGR_SPATIALITE'):
"""
Get Features within other Geometries in withinTbl
e.g. Intersect points with Polygons
apiToUse options:
* OGR_SPATIALITE;
* POSTGIS.
"""
from gasp import goToList
if not inTblCols and not withinCols:
colSelect = "intbl.*, witbl.*"
else:
if inTblCols and not withinCols:
colSelect = ", ".join([
"intbl.{}".format(c) for c in goToList(inTblCols)
])
elif not inTblCols and withinCols:
colSelect = ", ".join([
"witbl.{}".format(c) for c in goToList(withinCols)
])
else:
colSelect = "{}, {}".format(
", ".join(["intbl.{}".format(c) for c in goToList(inTblCols)]),
", ".join(["witbl.{}".format(c) for c in goToList(withinCols)])
)
Q = (
"SELECT {selcols} FROM {in_tbl} AS intbl "
"INNER JOIN {within_tbl} AS witbl ON "
"ST_Within(intbl.{in_geom}, witbl.{wi_geom})"
).format(
selcols=colSelect, in_tbl=inTbl, within_tbl=withinTbl,
in_geom=inGeom, wi_geom=withinGeom
)
if apiToUse == "OGR_SPATIALITE":
if outTblIsFile:
from gasp.anls.exct import sel_by_attr
sel_by_attr(conParam, Q, outTbl, api_gis='ogr')
else:
from gasp.sql.mng.qw import ntbl_by_query
ntbl_by_query(conParam, outTbl, Q, api='ogr2ogr')
elif apiToUse == 'POSTGIS':
if outTblIsFile:
from gasp.to.shp import psql_to_shp
psql_to_shp(
conParam, Q, outTbl, api="pgsql2shp",
geom_col=None, tableIsQuery=True)
else:
from gasp.sql.mng.qw import ntbl_by_query
ntbl_by_query(conParam, outTbl, Q, api='psql')
else:
raise ValueError((
"API {} is not available. OGR_SPATIALITE and POSTGIS "
"are the only valid options"
))
return outTbl
def get_unused_data_on_lulcp(
pbf,
pgsql_data,
out_work,
nomenclature,
dicPostgre={
'HOST': 'localhost',
'USER': '******',
'DATABASE': 'osm',
'TEMPLATE': 'template_postgis',
'PASSWORD': '******',
'PORT': '5432'
}):
"""
Return data not used in osm2lulc procedure
"""
import os
from gasp.sql import run_sql_file
from gasp.sql.mng.db import create_db
from gasp.fm.sql import query_to_df
from gasp.sql.mng.fld import cols_name
from gasp.sql.k import create_pk
from gasp.to.shp import psql_to_shp
from gasp.to.psql import osm_to_pgsql
# TODO: replace postgis
from gasp.postgis.analysis import new_geom_table
# ################ #
# Global Variables #
# ################ #
# Name of the tables with osmdata after the import with osm2pgsql
OsmData = {
'polygon': 'planet_osm_polygon',
'line': 'planet_osm_line',
'point': 'planet_osm_point'
}
IrrelevantCols = [
'addr:housename', 'addr:housenumber', 'addr:interpolation',
'generator:source', 'tower:type'
]
if nomenclature == 'URBAN_ATLAS':
tbl_relation = 'rel_osm_ua'
id_osm = 'osm_id'
elif nomenclature == 'CORINE_LAND_COVER':
tbl_relation = 'rel_osm_clc'
id_osm = 'osm_id'
elif nomenclature == 'GLOB_LAND_30':
tbl_relation = 'rel_osm_global'
id_osm = 'id_osm'
# ################ #
# Auxiliar Methods #
# ################ #
def get_where_string(dic, operator, table):
l = []
for fid in dic:
if dic[fid][0] == '' or dic[fid][1] == '' or dic[fid][0] == 'sidewalk'\
or dic[fid][0] == 'cycleway' or dic[fid][0] == 'busway'\
or dic[fid][0] == 'enity' or dic[fid][0] == 'healthcare':
continue
l.append("{t}.{col}{o}'{val}'".format(col=dic[fid][0],
o=operator,
val=dic[fid][1],
t=table))
return " OR ".join(l)
create_db(dicPostgre, dicPostgre['DATABASE'])
run_sql_file(dicPostgre, dicPostgre['DATABASE'], pgsql_data)
run_osm2pgsql(pbf, dicPostgre['DATABASE'], dicPostgre['HOST'],
dicPostgre['USER'])
# 1. Obtain data used on OSM2LULC procedure
# 1.1 Get key and value for osm features
id_related_with = [
x[0]
for x in query_to_df(dicPostgre,
"SELECT {fid} FROM {t}".format(t=tbl_relation,
fid=id_osm),
db_api='psql')[id_osm].tolist()
]
key_value = {
x[0]: [x[1], x[2]]
for x in query_to_df(
dicPostgre,
"SELECT id, key, value FROM osm_features WHERE {s}".format(
s=' OR '.join(
['id={y}'.format(y=str(x)) for x in id_related_with])),
db_api='psql').values.tolist()
}
# 1.2 Extract data with this combinations of keys and values
for tbl in OsmData:
# Create new primary key
create_pk(dicPostgre, OsmData[tbl], 'pk_fid')
cols = cols_name(dicPostgre, OsmData[tbl])
cols_clean = []
for i in cols:
if i not in IrrelevantCols:
if i == 'natural':
cols_clean.append("{t}.{col}".format(t=OsmData[tbl],
col=i))
else:
cols_clean.append(i)
whr = get_where_string(key_value, "=", OsmData[tbl])
new_geom_table(dicPostgre,
cols_clean,
OsmData[tbl],
whr,
'used{t}'.format(t=OsmData[tbl]),
pk=False)
export = psql_to_shp(
dicPostgre,
'used{t}'.format(t=OsmData[tbl]),
os.path.join(out_work, 'used{t}.shp'.format(t=OsmData[tbl])),
api="pgsql2shp",
geom_col='way')
# 2. Obtain data not used on OSM2LULC procedure
for tbl in OsmData:
new_geom_table(dicPostgre, ['*'],
OsmData[tbl],
"{t}.pk_fid NOT IN (SELECT pk_fid FROM used{t})".format(
t=OsmData[tbl]),
'unused{t}'.format(t=OsmData[tbl]),
pk=False)
export = psql_to_shp(dicPostgre,
'unused{t}'.format(t=OsmData[tbl]),
os.path.join(
out_work,
'unused{t}.shp'.format(t=OsmData[tbl])),
api="pgsql2shp",
geom_col='way')
