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 splite_near(sqdb,
tbl,
nearTbl,
tblGeom,
nearGeom,
output,
whrNear=None,
outIsFile=None):
"""
Near Analysis using Spatialite
"""
Q = ("SELECT m.*, ST_Distance(m.{inGeom}, j.geom) AS dist_near "
"FROM {t} AS m, ("
"SELECT ST_UnaryUnion(ST_Collect({neargeom})) AS geom "
"FROM {tblNear}{nearwhr}"
") AS j").format(
inGeom=tblGeom,
t=tbl,
neargeom=nearGeom,
tblNear=nearTbl,
nearwhr="" if not whrNear else " WHERE {}".format(whrNear))
if outIsFile:
from gasp.anls.exct import sel_by_attr
sel_by_attr(sqdb, Q, output, api_gis='ogr')
else:
from gasp.sql.mng.qw import ntbl_by_query
ntbl_by_query(sqdb, output, Q, api='ogr2ogr')
return output
def split_table_by_range(conP, table, row_number):
"""
Split tables in several
"""
from gasp.sql.mng.fld import cols_name
from gasp.sql.mng.tbl import row_num
from gasp.sql.mng.qw import ntbl_by_query
rowsN = row_num(conP, table, api='psql')
nrTables = int(rowsN / float(row_number)) + 1
COLS = cols_name(conP, table)
offset = 0
for i in range(nrTables):
ntbl_by_query(
conP,
'{}_{}'.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 split_table_by_col_distinct(conParam, pgtable, column):
"""
Create a new table for each value in one column
"""
from gasp.fm.sql import query_to_df
from gasp.sql.mng.fld import get_columns_type
from gasp.sql.mng.qw import ntbl_by_query
fields_types = get_columns_type(conParam, pgtable)
# Get unique values
VALUES = query_to_df(conParam,
"SELECT {col} FROM {t} GROUP BY {col}".format(
col=interest_column, t=pgtable),
db_api='psql')[interest_column].tolist()
whr = '{}=\'{}\'' if fields_types[interest_column] == str else '{}={}'
for row in VALUES:
ntbl_by_query(conParam,
'{}_{}'.format(pgtable, str(row[0])),
"SELECT * FROM {} WHERE {}".format(
pgtable, whr.format(interest_column, str(row[0]))),
api='psql')
def split_column_value_into_columns(lnkPgsql, table, column, splitChar,
new_cols, new_table):
"""
Split column value into several columns
"""
from gasp.sql.mng.qw import ntbl_by_query
if type(new_cols) != list:
raise ValueError('new_cols should be a list')
nr_cols = len(new_cols)
if nr_cols < 2:
raise ValueError('new_cols should have 2 or more elements')
# Get columns types from table
tblCols = cols_name(lnkPgsql, table)
# SQL construction
SQL = "SELECT {}, {} FROM {}".format(
", ".join(tblCols), ", ".join([
"split_part({}, '{}', {}) AS {}".format(column, splitChar, i + 1,
new_cols[i])
for i in range(len(new_cols))
]), table)
ntbl_by_query(lnkPgsql, new_table, SQL, api='psql')
return new_table
def text_columns_to_column(conParam,
inTable,
columns,
strSep,
newCol,
outTable=None):
"""
Several text columns to a single column
"""
from gasp import goToList
from gasp.sql.mng.qw import ntbl_by_query
mergeCols = goToList(columns)
tblCols = get_columns_type(conParam,
inTable,
sanitizeColName=None,
pyType=False)
for col in mergeCols:
if tblCols[col] != 'text' and tblCols[col] != 'varchar':
raise ValueError('{} should be of type text'.format(col))
coalesce = ""
for i in range(len(mergeCols)):
if not i:
coalesce += "COALESCE({}, '')".format(mergeCols[i])
else:
coalesce += " || '{}' || COALESCE({}, '')".format(
strSep, mergeCols[i])
if outTable:
# Write new table
colsToSelect = [_c for _c in tblCols if _c not in mergeCols]
if not colsToSelect:
sel = coalesce + " AS {}".format(newCol)
else:
sel = "{}, {}".format(", ".join(colsToSelect),
coalesce + " AS {}".format(newCol))
ntbl_by_query(conParam,
outTable,
"SELECT {} FROM {}".format(sel, inTable),
api='psql')
return outTable
else:
# Add column to inTable
from gasp.sql.mng.qw import update_table
add_field(conParam, inTable, {newCol: 'text'})
update_table(conParam, inTable, {newCol: coalesce})
return inTable
def select_using_excel_refs(conParam, excel_file, sheet_name,
pgtable, ref_fields,
tableInRef, tableOutRef=None):
"""
Split PGTABLE using references in excel table
Create two tables:
* One with similar rows - columns combination are in excel table;
* One with rows not in excel table.
TODO: Check if it's works.
"""
from gasp.fm import tbl_to_obj
from gasp.sql.mng.fld import get_columns_type
from gasp.sql.mng.qw import ntbl_by_query
def to_and(row, cols, cols_type):
def get_equal(_type):
return '{}=\'{}\'' if _type == str else '{}={}'
row['AND_E'] = ' AND '.join(
get_equal(cols_type[col]).format(col, row[col]) for col in cols
)
row['AND_E'] = '(' + row['AND_E'] + ')'
return row
# Get excel data
table = tbl_to_obj(excel_file, sheet=sheet_name)
# Get reference fields type
TYPE_COLS = get_columns_type(conParam, pgtable)
table = table.apply(lambda x: to_and(x, ref_fields, TYPE_COLS))
whr_equal = ' OR '.join(table['AND_E'])
ntbl_by_query(conParam, tableInRef, "SELECT * FROM {} WHERE {}".format(
pgtable, whr_equal
), api='psql')
if tableOutRef:
COLS_RELATION = " AND ".join(["{ft}.{f} = {st}.{f}".format(
ft=pgtable, f=col, st=tableInRef
) for col in TYPE_COLS])
ntbl_by_query(
conParam, tableOutRef,
(
"SELECT {ft}.* FROM {ft} LEFT JOIN {st} ON "
"{rel} WHERE {st}.{c} IS NULL"
).format(
ft=pgtable, st=tableInRef, rel=COLS_RELATION,
c=TYPE_COLS.keys()[0]
), api='psql'
)
def split_table_entity_number(conP, table, entity_field, entity_number):
"""
Split tables in several using as reference a number of entities per table
If a table has 1 000 000 entities and the entity_number is 250 000,
this method will create four tables, each one with 250 000 entities.
250 000 entities, not rows. Don't forget that the main table may have
more than one reference to the same entity.
"""
import pandas
from gasp.fm.sql import query_to_df
from gasp.sql.mng.fld import get_columns_type
from gasp.sql.mng.qw import ntbl_by_query
# Select entities in table
entities = query_to_df(conP,
"SELECT {c} FROM {t} GROUP BY {c}".format(
c=entity_field, t=table),
db_api='psql')
# Split entities into groups acoording entity_number
entityGroup = []
lower = 0
high = entity_number
while lower <= len(entities.index):
if high > len(entities.index):
high = len(entities.index)
entityGroup.append(entities.iloc[lower:high])
lower += entity_number
high += entity_number
# For each dataframe, create a new table
COLS_TYPE = get_columns_type(conP, table)
c = 0
for df in entityGroup:
if COLS_TYPE[entity_field] != str:
df[entity_field] = '{}='.format(
entity_field) + df[entity_field].astype(str)
else:
df[entity_field] = '{}=\''.format(
entity_field) + df[entity_field].astype(str) + '\''
whr = ' OR '.join(df[entity_field])
ntbl_by_query(conP,
'{}_{}'.format(table, str(c)),
("SELECT * FROM {} WHERE {}").format(table, whr),
api='psql')
c += 1
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 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 import goToList
dissolve = goToList(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(goToList(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.exct import sel_by_attr
sel_by_attr(db, sql, outTbl, api_gis='ogr')
else:
from gasp.sql.mng.qw import ntbl_by_query
ntbl_by_query(db, outTbl, sql, api='ogr2ogr')
return outTbl
def trim_char_in_col(conParam,
pgtable,
cols,
trim_str,
outTable,
onlyTrailing=None,
onlyLeading=None):
"""
Python implementation of the TRIM PSQL Function
The PostgreSQL trim function is used to remove spaces or set of
characters from the leading or trailing or both side from a string.
"""
from gasp import goToList
from gasp.sql.mng.qw import ntbl_by_query
cols = goToList(cols)
colsTypes = get_columns_type(conParam,
pgtable,
sanitizeColName=None,
pyType=False)
for col in cols:
if colsTypes[col] != 'text' and colsTypes[col] != 'varchar':
raise ValueError('{} should be of type text'.format(col))
colsToSelect = [_c for _c in colsTypes if _c not in cols]
tail_lead_str = "" if not onlyTrailing and not onlyLeading else \
"TRAILING " if onlyTrailing and not onlyLeading else \
"LEADING " if not onlyTrailing and onlyLeading else ""
trimCols = [
"TRIM({tol}{char} FROM {c}) AS {c}".format(c=col,
tol=tail_lead_str,
char=trim_str)
for col in cols
]
if not colsToSelect:
cols_to_select = "{}".format(", ".join(trimCols))
else:
cols_to_select = "{}, {}".format(", ".join(colsToSelect),
", ".join(colsReplace))
ntbl_by_query(conParam,
outTable,
"SELECT {} FROM {}".format(colsToSelect, pgtable),
api='psql')
def pnts_to_lines(conParam,
inTable,
outTable,
entityCol,
orderCol,
geomCol=None,
xCol=None,
yCol=None,
epsg=4326):
"""
Given a table with points by entity, create a new table with a polyline
for each entity. The points are added to the polyline based on a
sequence in one column.
"""
if not geomCol:
if not xCol or not yCol:
raise ValueError(
'If geomCol is not specified, xCol and ycol must replace it!')
from gasp.sql.mng.qw import ntbl_by_query
geomRef = geomCol if geomCol else "ST_MakePoint({}, {})".format(xCol, yXol)
Q = ("SELECT {entCol}, ST_SetSRID(ST_MakeLine("
"array_agg({pntCol} ORDER BY {orderF})), {srs}) "
"FROM {tbl} GROUP BY {entCol}").format(entCol=entityCol,
pntCol=geomRef,
orderF=orderCol,
srs=epsg,
tbl=inTable)
return ntbl_by_query(conParam, outTable, Q, api='psql')
def pg_erase(conParam, inTbl, eraseTbl, inGeom, eraseGeom, outTbl):
"""
Erase
"""
from gasp.sql.mng.fld import cols_name
from gasp.sql.mng.qw import ntbl_by_query
cols = ["mtbl.{}".format(
x) for x in cols_name(conParam, inTbl, api='psql') if x != inGeom]
q = (
"SELECT {}, ST_Difference(mtbl.{}, foo.erase_geom) AS {} "
"FROM {} AS mtbl, "
"("
"SELECT ST_UnaryUnion(ST_Collect(eetbl.{})) AS erase_geom "
"FROM {} AS eetbl "
"INNER JOIN {} AS jtbl ON ST_Intersects(eetbl.{}, jtbl.{})"
") AS foo"
).format(
", ".join(cols), inGeom, inGeom, inTbl, eraseGeom, eraseTbl,
inTbl, eraseGeom, inGeom
)
return ntbl_by_query(conParam, outTbl, q, api='psql')
def intersect_in_same_table(conParam, table, geomA, geomB, outtable,
intersectField='intersects',
intersectGeom=None, colsSel=None):
"""
Intersect two Geometries in the same table
"""
from gasp import goToList
from gasp.sql.c import psqlcon
from gasp.sql.mng.qw import ntbl_by_query
COLS = goToList(colsSel)
return ntbl_by_query(
conParam, outtable,
("SELECT {cls}, CASE WHEN interse IS TRUE THEN 1 ELSE 0 END AS {intF} "
"{intgeomF}FROM ("
"SELECT {cls}, ST_Intersects({gA}, {gB}) AS interse "
"{intgeom}FROM {t}"
") AS tst").format(
gA=geomA, gB=geomB, t=table, intF=intersectField,
cls="*" if not COLS else ", ".join(COLS),
intgeom= "" if not intersectGeom else \
", ST_Intersection({}, {}) AS intersect_geom".format(
geomA, geomB
),
intgeomF = "" if not intersectGeom else ", intersect_geom"
), api='psql'
)
def geom_to_points(conParam,
table,
geomCol,
outTable,
selCols=None,
newGeomCol=None):
"""
Convert a Polygon/Polyline Geometry to Points
Equivalent to feature to point tool
"""
from gasp import goToList
from gasp.sql.mng.qw import ntbl_by_query
selCols = goToList(selCols)
Q = ("SELECT {cols}(ST_DumpPoints({geom})).geom AS {newCol} "
"FROM {tbl}").format(
cols="" if not selCols else "{}, ".format(", ".join(selCols)),
geom=geomCol,
newCol="geom" if not newGeomCol else newGeomCol,
tbl=table)
return ntbl_by_query(conParam, outTable, Q, api='psql')
def fix_geom(conParam, table, geom, out_tbl, colsSelect=None, whr=None):
"""
Remove some topological incorrections on the PostGIS data
"""
from gasp.sql.mng.qw import ntbl_by_query
if not colsSelect:
from gasp.sql.mng.fld import cols_name
cols_tbl = [
'{}.{}'.format(table, x)
for x in cols_name(conParam, table, sanitizeSpecialWords=None)
if x != geom
]
else:
from gasp import goToList
cols_tbl = [
'{}.{}'.format(table, x) for x in goToList(colsSelect) if x != geom
]
Q = "SELECT {c}, ST_MakeValid({g}) AS {g} FROM {t}{w}".format(
c=", ".join(cols_tbl),
g=geom,
t=table,
w="" if not whr else " WHERE {}".format(whr))
ntbl = ntbl_by_query(conParam, out_tbl, Q, api='psql')
return ntbl
def select_main_geom_type(conparam, table, outbl, geomCol='geom'):
"""
Assuming a table with several geometry types, this method
counts the rows for each geometry type and select the rows with a geometry
type with more rows
"""
from gasp.sql.mng.qw import ntbl_by_query
from gasp.sql.mng.fld import cols_name
COLS = [
x for x in cols_name(conparam, table, sanitizeSpecialWords=None)
if x != geomCol
]
Q = (
"SELECT {cols}, {geomcol} FROM ("
"SELECT *, MAX(jtbl.geom_cont) OVER (PARTITION BY "
"jtbl.tst) AS max_cnt FROM ("
"SELECT {cols}, (ST_Dump({geomcol})).geom AS {geomcol}, "
"ST_GeometryType((ST_Dump({geomcol})).geom) AS geom_type "
"FROM {tbl}"
") AS foo INNER JOIN ("
"SELECT ST_GeometryType((ST_Dump({geomcol})).geom) AS gt, "
"COUNT(ST_GeometryType((ST_Dump({geomcol})).geom)) AS geom_cont, "
"1 AS tst FROM {tbl} GROUP BY ST_GeometryType((ST_Dump({geomcol})).geom)"
") AS jtbl ON foo.geom_type = jtbl.gt"
") AS foo WHERE geom_cont = max_cnt").format(cols=", ".join(COLS),
geomcol=geomCol,
tbl=table)
return ntbl_by_query(conparam, outbl, Q, api='psql')
def get_rows_notin_query(conParam,
tblA,
tblB,
joinCols,
newTable,
cols_to_mantain=None,
tblAisQuery=None,
tblBisQuery=None):
"""
Get rows from tblA that are not present in tblB
joinCols = {colTblA : colTblB}
"""
from gasp import goToList
from gasp.sql.mng.qw import ntbl_by_query
cols_to_mantain = goToList(cols_to_mantain)
q = ("SELECT {cls} FROM {ta} LEFT JOIN {tb} ON "
"{rel} WHERE {tblB}.{fldB} IS NULL").format(
cls=cols_to_mantain if cols_to_mantain else "{}.*".format(tblA),
ta=tblA if not tblAisQuery else tblAisQuery,
tb=tblB if not tblBisQuery else tblBisQuery,
rel=" AND ".join([
"{ta}.{ca} = {tb}.{cb}".format(ta=tblA,
tb=tblB,
ca=k,
cb=joinCols[k])
for k in joinCols
]))
newTable = ntbl_by_query(conParam, newTable, q, api='psql')
return newTable
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 replace_char_in_col(conParam, pgtable, cols, match_str, replace_str,
outTable):
"""
Replace char in all columns in cols for the value of replace_str
Python implementation of the REPLACE PSQL Function
"""
from gasp import goToList
from gasp.sql.mng.qw import ntbl_by_query
cols = goToList(cols)
colsTypes = get_columns_type(conParam,
pgtable,
sanitizeColName=None,
pyType=False)
for col in cols:
if colsTypes[col] != 'text' and colsTypes[col] != 'varchar':
raise ValueError('{} should be of type text'.format(col))
colsToSelect = [_c for _c in colsTypes if _c not in cols]
colsReplace = [
"REPLACE({c}, '{char}', '{nchar}') AS {c}".format(c=col,
char=match_str,
nchar=replace_str)
for col in cols
]
if not colsToSelect:
cols_to_select = "{}".format(", ".join(colsReplace))
else:
cols_to_select = "{}, {}".format(", ".join(colsToSelect),
", ".join(colsReplace))
ntbl_by_query(conParam,
outTable,
"SELECT {cols} FROM {tbl}".format(cols=cols_to_select,
tbl=pgtable),
api='psql')
return outTable
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 disjoint_polygons_rel_points(sqBD, pntTbl, pntGeom,
polyTbl, polyGeom, outTbl,
polySelect=None,
pntQuery=None, polyQuery=None,
outTblIsFile=None):
"""
Get Disjoint relation
What TODO with this?
"""
import os
if not polySelect:
raise ValueError("Man, select something!")
sql = (
"SELECT {selCols} FROM {polTable} WHERE ("
"{polName}.{polGeom} not in ("
"SELECT {polName}.{polGeom} FROM {pntTable} "
"INNER JOIN {polTable} ON "
"ST_Within({pntName}.{pntGeom_}, {polName}.{polGeom})"
"))"
).format(
selCols = "*" if not polySelect else polySelect,
polTable = polyTbl if not polyQuery else polyQuery,
polGeom = polyGeom,
pntTable = pntTbl if not pntQuery else pntQuery,
pntGeom_ = pntGeom,
pntName = pntTbl,
polName = polyTbl
)
if outTblIsFile:
from gasp.anls.exct import sel_by_attr
sel_by_attr(sqBD, sql, outTbl, api_gis='ogr')
else:
from gasp.sql.mng.qw import ntbl_by_query
ntbl_by_query(sqBD, outTbl, sql, api='ogr2ogr')
def intersect_point_with_polygon(sqDB, pntTbl, pntGeom,
polyTbl, polyGeom, outTbl,
pntSelect=None, polySelect=None,
pntQuery=None, polyQuery=None,
outTblIsFile=None):
"""
Intersect Points with Polygons
What TODO with this?
"""
import os
if not pntSelect and not polySelect:
raise ValueError("You have to select something")
sql = (
"SELECT {colPnt}{colPoly} FROM {pnt_tq} "
"INNER JOIN {poly_tq} ON "
"ST_Within({pnt}.{pnGeom}, {poly}.{pgeom})"
).format(
colPnt = pntSelect if pntSelect else "",
colPoly = polySelect if polySelect and not pntSelect else \
", " + polySelect if polySelect and pntSelect else "",
pnt_tq = pntTbl if not pntQuery else pntQuery,
poly_tq = polyTbl if not polyQuery else polyQuery,
pnt = pntTbl,
poly = polyTbl,
pnGeom = pntGeom,
pgeom = polyGeom
)
if outTblIsFile:
from gasp.anls.exct import sel_by_attr
sel_by_attr(sqDB, sql, outTbl, api_gis='ogr')
else:
from gasp.sql.mng.qw import ntbl_by_query
ntbl_by_query(sqDB, outTbl, sql, api='ogr2ogr')
def re_project(lnk,
in_tbl,
fld_geom,
outEpsg,
out_tbl,
newgeom_fld=None,
new_pk=None,
colsSelect=None,
whr=None):
"""
Reproject geometric layer to another spatial reference system (srs)
lnk is a dict with parameters for connecting to PostgreSQL
tbl_geom is the table to project
fld_geom is the column of the previous table with geometric data
outEpsg is the srs of destiny
out_tbl is the repreoject table (output)
"""
from gasp import goToList
from gasp.sql.k import create_pk
from gasp.sql.mng.qw import ntbl_by_query
colsSelect = goToList(colsSelect)
newGeom = newgeom_fld if newgeom_fld else fld_geom if \
colsSelect else "proj_{}".format(fld_geom)
ntbl_by_query(lnk,
out_tbl,
"SELECT {}, ST_Transform({}, {}) AS {} FROM {}{}".format(
"*" if not colsSelect else ", ".join(colsSelect),
fld_geom, str(outEpsg), newGeom, in_tbl,
"" if not whr else " WHERE {}".format(whr)),
api='psql')
if new_pk:
create_pk(lnk, out_tbl, new_pk)
return out_tbl
def tbls_to_tbl(conParam, lst_tables, outTable):
"""
Append all tables in lst_tables into the outTable
"""
from gasp.sql.mng.qw import ntbl_by_query
sql = " UNION ALL ".join(
["SELECT * FROM {}".format(t) for t in lst_tables])
outTable = ntbl_by_query(conParam, outTable, sql, api='psql')
return outTable
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 columns_to_timestamp(conParam,
inTbl,
dayCol,
hourCol,
minCol,
secCol,
newTimeCol,
outTbl,
selColumns=None,
whr=None):
"""
Columns to timestamp column
"""
from gasp import goToList
from gasp.sql.mng.qw import ntbl_by_query
selCols = goToList(selColumns)
sql = ("SELECT {C}, TO_TIMESTAMP("
"COALESCE(CAST({day} AS text), '') || ' ' || "
"COALESCE(CAST({hor} AS text), '') || ':' || "
"COALESCE(CAST({min} AS text), '') || ':' || "
"COALESCE(CAST({sec} AS text), ''), 'YYYY-MM-DD HH24:MI:SS'"
") AS {TC} FROM {T}{W}").format(
C="*" if not selCols else ", ".join(selCols),
day=dayCol,
hor=hourCol,
min=minCol,
sec=secCol,
TC=newTimeCol,
T=inTbl,
W="" if not whr else " WHERE {}".format(whr))
ntbl_by_query(conParam, outTbl, sql, api='psql')
return outTbl
def st_near(link,
inTbl,
inTblPK,
inGeom,
nearTbl,
nearGeom,
output,
near_col='near',
untilDist=None,
colsInTbl=None,
colsNearTbl=None):
"""
Near tool for PostGIS
"""
from gasp import goToList
from gasp.sql.mng.qw import ntbl_by_query
_out = ntbl_by_query(
link,
output,
("SELECT DISTINCT ON (s.{colPk}) "
"{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.{colPk}, ST_Distance(s.{ingeomCol}, h.{negeomCol})"
).format(colPk=inTblPK,
inTblCols="s.*" if not colsInTbl else ", ".join(
["s.{}".format(x) for x in goToList(colsInTbl)]),
nearTblCols="" if not colsNearTbl else
", ".join(["h.{}".format(x)
for x in goToList(colsNearTbl)]) + ", ",
ingeomCol=inGeom,
negeomCol=nearGeom,
nearCol=near_col,
in_tbl=inTbl,
near_tbl=nearTbl,
dist_v="100000" if not untilDist else untilDist),
api='psql')
return output
def xycols_to_geom(conP,
intable,
x_col,
y_col,
outtable,
geom_field='geom',
epsg=4326):
"""
X and Y Colums to PostGIS Geom Column
"""
from gasp.sql.mng.qw import ntbl_by_query
return ntbl_by_query(
conP,
outtable, ("SELECT *, ST_SetSRID(ST_MakePoint({}, {}), {}) AS {} "
"FROM {}").format(x_col, y_col, str(epsg), geom_field,
intable),
api='psql')
def add_endpoints_to_table(conP,
inTable,
outTable,
idCol='gid',
geomCol='geom',
startCol="start_vertex",
endCol="end_vertex"):
"""
Add start/end points columns to table
"""
from gasp.sql.mng.qw import ntbl_by_query
from gasp.sql.mng.fld import cols_name
return ntbl_by_query(conP,
outTable,
("SELECT {cols}, {stPnt}, {endPnt} FROM ("
"SELECT *, lead({stPnt}) OVER ("
"PARTITION BY {colId} ORDER BY pnt_idx) AS {endPnt} "
"FROM ("
"SELECT {cols}, pnt_idx, {stPnt}, "
"CASE "
"WHEN pnt_idx = 1 OR pnt_idx = MAX(pnt_idx) "
"OVER (PARTITION BY {colId}) "
"THEN 1 ELSE 0 END AS pnt_cat "
"FROM ("
"SELECT {cols}, "
"(ST_DumpPoints({geomF})).path[1] AS pnt_idx, "
"(ST_DumpPoints({geomF})).geom AS {stPnt} "
"FROM {table}"
") AS foo"
") AS foo2 "
"WHERE pnt_cat = 1"
") AS foo3 "
"WHERE {endPnt} IS NOT NULL "
"ORDER BY {colId}, pnt_idx").format(cols=", ".join(
cols_name(conP, inTable)),
stPnt=startCol,
endPnt=endCol,
colId=idCol,
geomF=geomCol,
table=inTable),
api='psql')
