def split_colval_into_cols(db_name, table, column, splitChar, new_cols,
new_table):
"""
Split column value into several columns
"""
from glass.ng.prop.sql import cols_name
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(db_name, 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)
q_to_ntbl(db_name, new_table, SQL, api='psql')
return new_table
def split_table_by_col_distinct(db, tbl, col):
"""
Create a new table for each value in one column
"""
from glass.ng.sql.q import q_to_obj
from glass.ng.prop.sql import cols_type
from glass.ng.sql.q import q_to_ntbl
fields_types = cols_type(db, tbl)
# Get unique values
VALUES = q_to_obj(db,
"SELECT {col} FROM {t} GROUP BY {col}".format(col=col,
t=tbl),
db_api='psql')[col].tolist()
whr = '{}=\'{}\'' if fields_types[col] == str else '{}={}'
for row in VALUES:
q_to_ntbl(db,
'{}_{}'.format(tbl, str(row[0])),
"SELECT * FROM {} WHERE {}".format(
tbl, whr.format(col, str(row[0]))),
api='psql')
def rm_deadend(db, in_tbl, out_tbl):
"""
Remove deadend
"""
from glass.ng.prop.sql import cols_name, row_num
from glass.ng.sql.q import q_to_ntbl
from glass.ng.sql.tbl import rename_tbl
# Sanitize In table
cols = ", ".join([
c for c in cols_name(db, in_tbl, sanitizeSpecialWords=True, api='psql')
if c != 'geom' and c != 'gid'
])
_t = q_to_ntbl(db, "san_geom",
("SELECT gid, {cln}, geom, "
"ST_AsText(ST_StartPoint(geom)) AS pnt_start, "
"ST_AsText(ST_EndPoint(geom)) AS pnt_end FROM ("
"SELECT gid, {cln}, (ST_Dump(geom)).geom AS geom "
"FROM {t}"
") AS foo").format(cln=cols, t=in_tbl))
run_ = 1
i = 1
while run_:
# Get Table with Points of lines to delete
delpnt = q_to_ntbl(db, "del_pnt_{}".format(
str(i)), ("SELECT ROW_NUMBER() OVER (ORDER BY txtgeom) AS idx, "
"txtgeom FROM ("
"SELECT txtgeom, COUNT(txtgeom) AS npnt FROM ("
"SELECT pnt_start AS txtgeom "
"FROM {t} UNION ALL "
"SELECT pnt_end AS txtgeom "
"FROM {t}"
") AS tbl GROUP BY txtgeom"
") AS delg WHERE npnt=1").format(t=_t))
npnt = row_num(db, delpnt, api='psql')
if not npnt:
run_ = None
break
# Get Lines without dead-end
Q = ("SELECT mtbl.* "
"FROM {mtbl} AS mtbl LEFT JOIN {ptbl} AS st_tbl "
"ON mtbl.pnt_start = st_tbl.txtgeom "
"LEFT JOIN {ptbl} AS end_tbl "
"ON mtbl.pnt_end = end_tbl.txtgeom "
"WHERE st_tbl.txtgeom IS NULL AND "
"end_tbl.txtgeom IS NULL").format(cls=cols, mtbl=_t, ptbl=delpnt)
_t = q_to_ntbl(db, "rows_{}".format(str(i)), Q)
i += 1
rename_tbl(db, {_t: out_tbl})
return out_tbl
def line_intersection_pnt(db, inTbl, outTbl):
"""
Get Points where two line features of the same feature class
intersects.
"""
from glass.ng.sql.q import q_to_ntbl
# Get Points representing intersection
Q_a = (
"SELECT foo.gid, "
"ST_Intersection(foo.geom, foo2.tstgeom) AS geom "
"FROM (SELECT gid, geom FROM {t}) AS foo, ("
"SELECT gid AS tstfid, geom AS tstgeom "
"FROM {t}"
") AS foo2 "
"WHERE foo.gid <> foo2.tstfid AND "
"ST_Intersects(foo.geom, foo2.tstgeom)"
).format(t=inTbl)
Q_b = (
"SELECT gid AS ogid, (ST_Dump(geom)).geom AS geom FROM ("
"SELECT gid, "
"CASE "
"WHEN ST_GeometryType(geom) = 'ST_LineString' "
"THEN ST_Collect(ST_StartPoint(geom), ST_EndPoint(geom)) "
"ELSE geom "
"END AS geom FROM ("
"SELECT gid, (ST_Dump(geom)).geom AS geom "
"FROM ({t}) AS ttbl"
") AS tbl"
") AS tbll"
).format(t=Q_a)
allpnt = q_to_ntbl(db, "all_pnt", Q_b)
Q_main = (
"SELECT ogid, (ogid - 1) AS ofid, geom FROM ("
"SELECT mtbl.*, st_tbl.st_pnt, st_tbl.end_pnt, "
"CASE "
"WHEN mtbl.geom = st_tbl.st_pnt "
"THEN 1 ELSE 0 "
"END AS is_start, "
"CASE "
"WHEN mtbl.geom = st_tbl.end_pnt "
"THEN 1 ELSE 0 "
"END AS is_end "
"FROM {bpnt} AS mtbl INNER JOIN ("
"SELECT gid, ST_StartPoint(geom) AS st_pnt, "
"ST_EndPoint(geom) AS end_pnt FROM ("
"SELECT gid, (ST_Dump(geom)).geom AS geom "
"FROM {t}"
") AS foo"
") AS st_tbl "
"ON mtbl.ogid = st_tbl.gid"
") AS foo WHERE is_start = 0 AND is_end = 0"
).format(bpnt=allpnt, t=inTbl)
return q_to_ntbl(db, outTbl, Q_main)
def st_dissolve(db,
table,
geomColumn,
outTable,
whrClause=None,
diss_cols=None,
outTblIsFile=None,
api='sqlite'):
"""
Dissolve a Polygon table
API options:
* sqlite
* psql
"""
from glass.pys 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 glass.g.tbl.filter import sel_by_attr
sel_by_attr(db, sql, outTable, api_gis='ogr')
elif api == 'psql':
from glass.g.it.shp import dbtbl_to_shp
dbtbl_to_shp(db,
table,
geomColumn,
outTable,
api='pgsql2shp',
tableIsQuery=True)
else:
from glass.ng.sql.q import q_to_ntbl
q_to_ntbl(db,
outTable,
sql,
api='ogr2ogr' if api == 'sqlite' else 'psql')
return outTable
def select_using_excel_refs(db_name, 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 glass.ng.rd import tbl_to_obj
from glass.ng.prop.sql import cols_type
from glass.ng.sql.q import q_to_ntbl
def to_and(row, cols, ctype):
def get_equal(_type):
return '{}=\'{}\'' if _type == str else '{}={}'
row['AND_E'] = ' AND '.join(
get_equal(ctype[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 = cols_type(db_name, pgtable)
table = table.apply(lambda x: to_and(x, ref_fields, TYPE_COLS))
whr_equal = ' OR '.join(table['AND_E'])
q_to_ntbl(db_name, 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])
q_to_ntbl(db_name, 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(db, 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.
"""
from glass.ng.sql.q import q_to_obj
from glass.ng.prop.sql import cols_type
from glass.ng.sql.q import q_to_ntbl
# Select entities in table
entities = q_to_obj(db,
"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 = cols_type(db, 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])
q_to_ntbl(db,
'{}_{}'.format(table, str(c)),
("SELECT * FROM {} WHERE {}").format(table, whr),
api='psql')
c += 1
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 glass.pys 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 glass.g.tbl.filter import sel_by_attr
sel_by_attr(db, sql, outTbl, api_gis='ogr')
else:
from glass.ng.sql.q import q_to_ntbl
q_to_ntbl(db, outTbl, sql, api='ogr2ogr')
return outTbl
def txt_cols_to_col(db, inTable, columns, strSep, newCol, outTable=None):
"""
Several text columns to a single column
"""
from glass.pys import obj_to_lst
from glass.ng.prop.sql import cols_type
mergeCols = obj_to_lst(columns)
tblCols = cols_type(db, 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))
q_to_ntbl(db,
outTable,
"SELECT {} FROM {}".format(sel, inTable),
api='psql')
return outTable
else:
# Add column to inTable
from glass.ng.sql.tbl import update_table
add_field(db, inTable, {newCol: 'text'})
update_table(db, inTable, {newCol: coalesce})
return inTable
def trim_char_in_col(db,
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 glass.pys import obj_to_lst
from glass.ng.prop.sql import cols_type
cols = obj_to_lst(cols)
colsTypes = cols_type(db, 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(trimCols))
q_to_ntbl(db,
outTable,
"SELECT {} FROM {}".format(colsToSelect, pgtable),
api='psql')
def pg_erase(db, inTbl, eraseTbl, inGeom, eraseGeom, outTbl):
"""
Erase
"""
from glass.ng.prop.sql import cols_name
from glass.ng.sql.q import q_to_ntbl
cols = ["mtbl.{}".format(
x) for x in cols_name(db, 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 q_to_ntbl(db, outTbl, q, api='psql')
def del_topoerror_shps(db, shps, epsg, outfolder):
"""
Remove topological errors from Feature Class data using PostGIS
"""
import os
from glass.pys import obj_to_lst
from glass.ng.prop.sql import cols_name
from glass.ng.sql.q import q_to_ntbl
from glass.g.it.db import shp_to_psql
from glass.g.it.shp import dbtbl_to_shp
shps = obj_to_lst(shps)
TABLES = shp_to_psql(db, shps, srsEpsgCode=epsg, api="shp2pgsql")
NTABLE = [q_to_ntbl(
db, "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(
db, TABLES[t], sanitizeSpecialWords=None
) if x != 'geom']),
tbl=TABLES[t]
), api='psql'
) for t in range(len(TABLES))]
for t in range(len(NTABLE)):
dbtbl_to_shp(db, NTABLE[t], "geom", os.path.join(
outfolder, TABLES[t]), tableIsQuery=None, api='pgsql2shp')
def select_main_geom_type(db, 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 glass.ng.sql.q import q_to_ntbl
from glass.ng.prop.sql import cols_name
COLS = [
x for x in cols_name(db, 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 q_to_ntbl(db, outbl, Q, api='psql')
def intersect_in_same_table(db_name, table, geomA, geomB, outtable,
intersectField='intersects',
intersectGeom=None, colsSel=None):
"""
Intersect two Geometries in the same table
"""
from glass.pys import obj_to_lst
from glass.ng.sql.q import q_to_ntbl
COLS = obj_to_lst(colsSel)
return q_to_ntbl(db_name, 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 fix_geom(db, table, geom, out_tbl, colsSelect=None, whr=None):
"""
Remove some topological incorrections on the PostGIS data
"""
from glass.ng.sql.q import q_to_ntbl
if not colsSelect:
from glass.ng.prop.sql import cols_name
cols_tbl = [
'{}.{}'.format(table, x)
for x in cols_name(db, table, sanitizeSpecialWords=None)
if x != geom
]
else:
from glass.pys import obj_to_lst
cols_tbl = [
'{}.{}'.format(table, x) for x in obj_to_lst(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 = q_to_ntbl(db, out_tbl, Q, api='psql')
return ntbl
def rows_notin_q(db,
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 glass.pys import obj_to_lst
from glass.ng.sql.q import q_to_ntbl
cols_to_mantain = obj_to_lst(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 = q_to_ntbl(db, newTable, q, api='psql')
return newTable
def pnts_to_lines(db,
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 glass.ng.sql.q import q_to_ntbl
geomRef = geomCol if geomCol else "ST_MakePoint({}, {})".format(xCol, yCol)
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 q_to_ntbl(db, outTable, Q, api='psql')
def geom_to_points(db,
table,
geomCol,
outTable,
selCols=None,
newGeomCol=None):
"""
Convert a Polygon/Polyline Geometry to Points
Equivalent to feature to point tool
"""
from glass.pys import obj_to_lst
from glass.ng.sql.q import q_to_ntbl
selCols = obj_to_lst(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 q_to_ntbl(db, outTable, Q, api='psql')
def matrix_od_mean_dist_by_group(MATRIX_OD, ORIGIN_COL, GROUP_ORIGIN_ID,
GROUP_ORIGIN_NAME, GROUP_DESTINA_ID,
GROUP_DESTINA_NAME, TIME_COL, epsg, db,
RESULT_MATRIX):
"""
Calculate Mean GROUP distance from OD Matrix
OD MATRIX EXAMPLE
| origin_entity | origin_group | destina_entity | destina_group | distance
| XXXX | XXXX | XXXX | XXX | XXX
OUTPUT EXAMPLE
| origin_group | destina_group | mean_distance
| XXXX | XXXX | XXXX
"""
from glass.pys.oss import fprop
from glass.g.it.db import shp_to_psql
from glass.ng.sql.db import create_db
from glass.ng.sql.q import q_to_ntbl
from glass.ng.it import db_to_tbl
db = create_db(fprop(MATRIX_OD, 'fn'), overwrite=True, api='psql')
TABLE = shp_to_psql(db,
MATRIX_OD,
pgTable="tbl_{}".format(db),
api="pandas",
srsEpsgCode=epsg)
OUT_TABLE = q_to_ntbl(
db,
fprop(RESULT_MATRIX, 'fn'),
("SELECT {groupOriginCod}, {groupOriginName}, {groupDestCod}, "
"{groupDestName}, AVG(mean_time) AS mean_time FROM ("
"SELECT {origin}, {groupOriginCod}, {groupOriginName}, "
"{groupDestCod}, {groupDestName}, "
"AVG({timeCol}) AS mean_time FROM {t} "
"GROUP BY {origin}, {groupOriginCod}, {groupOriginName}, "
"{groupDestCod}, {groupDestName}"
") AS foo "
"GROUP BY {groupOriginCod}, {groupOriginName}, "
"{groupDestCod}, {groupDestName} "
"ORDER BY {groupOriginCod}, {groupDestCod}").format(
groupOriginCod=GROUP_ORIGIN_ID,
groupOriginName=GROUP_ORIGIN_NAME,
groupDestCod=GROUP_DESTINA_ID,
groupDestName=GROUP_DESTINA_NAME,
origin=ORIGIN_COL,
timeCol=TIME_COL,
t=TABLE),
api='psql')
return db_to_tbl(db,
"SELECT * FROM {}".format(OUT_TABLE),
RESULT_MATRIX,
sheetsNames="matrix",
dbAPI='psql')
def get_isValidDestination_table(db, stopsTable, stopsId, stopsGeom,
stopsRoute, isochronesTable, isoStop, isoGeom,
outTable):
"""
Create a table that shows if a certain BUS Stop is a valid
destination for the BUS Stops of a certain route.
OUTPUT TABLE SCHEMA:
(stop_a_route | stop_b | isValid)
stop_b is a valid destination for any stop of the stop_a_route
if an isochrone (10 min) from stop_b intersects with any stop of the
stop_a_route.
"""
from glass.ng.sql.q import q_to_ntbl
q = (
"SELECT {routeF}, stop_b, "
"MAX(CASE WHEN bool_intersect IS true THEN 1 ELSE 0 END) AS bintersect "
"FROM ("
"SELECT stops1.{routeF}, stops1.{stopF} AS stop_a, "
"stops1.stop_geom AS stop_geom, "
"stops2.{stopF} AS stop_b, stops2.iso_geom AS iso_geom, "
"ST_Intersects(stops1.stop_geom, stops2.iso_geom) AS bool_intersect "
"FROM ("
"SELECT {routeF}, {stopF}, {stopG} AS stop_geom FROM {t} "
"GROUP BY {routeF}, {stopF}, {stopG}"
") AS stops1, ("
"SELECT stops.{stopF}, {isoT}.{isoG} AS iso_geom FROM ("
"SELECT {stopF}, {stopG} FROM {t} GROUP BY {stopF}, {stopG}"
") AS stops INNER JOIN {isoT} ON stops.{stopF} = {isoT}.{isoID}"
") AS stops2"
") AS foo GROUP BY {routeF}, stop_b").format(routeF=stopsRoute,
stopF=stopsId,
stopG=stopsGeom,
isoT=isochronesTable,
isoG=isoGeom,
t=stopsTable,
isoID=isoStop)
q_to_ntbl(db, outTable, q)
return outTable
def st_buffer(db,
inTbl,
bfDist,
geomCol,
outTbl,
bufferField="geometry",
whrClause=None,
dissolve=None,
cols_select=None,
outTblIsFile=None):
"""
Using Buffer on PostGIS Data
"""
from glass.pys import obj_to_lst
dissolve = obj_to_lst(dissolve) if dissolve != "ALL" else "ALL"
SEL_COLS = "" if not cols_select else ", ".join(obj_to_lst(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 glass.ng.sql.q import q_to_ntbl
outTbl = q_to_ntbl(db, outTbl, Q, api='psql')
else:
from glass.g.it.shp import dbtbl_to_shp
dbtbl_to_shp(db,
Q,
bufferField,
outTbl,
api='pgsql2shp',
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 glass.g.tbl.filter import sel_by_attr
sel_by_attr(sqBD, sql, outTbl, api_gis='ogr')
else:
from glass.ng.sql.q import q_to_ntbl
q_to_ntbl(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 glass.g.tbl.filter import sel_by_attr
sel_by_attr(sqDB, sql, outTbl, api_gis='ogr')
else:
from glass.ng.sql.q import q_to_ntbl
q_to_ntbl(sqDB, outTbl, sql, api='ogr2ogr')
def replace_char_in_col(db, 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 glass.pys import obj_to_lst
from glass.ng.prop.sql import cols_type
cols = obj_to_lst(cols)
colsTypes = cols_type(db, 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))
q_to_ntbl(db,
outTable,
"SELECT {cols} FROM {tbl}".format(cols=cols_to_select,
tbl=pgtable),
api='psql')
return outTable
def tbls_to_tbl(db, lst_tables, outTable):
"""
Append all tables in lst_tables into the outTable
"""
from glass.ng.sql.q import q_to_ntbl
sql = " UNION ALL ".join(
["SELECT * FROM {}".format(t) for t in lst_tables])
outTable = q_to_ntbl(db, outTable, sql, api='psql')
return outTable
def split_table_by_range(db, table, row_number):
"""
Split tables in several
"""
from glass.ng.prop.sql import cols_name, row_num
from glass.ng.sql.q import q_to_ntbl
rowsN = row_num(db, table, api='psql')
nrTables = int(rowsN / float(row_number)) + 1
COLS = cols_name(db, table)
offset = 0
for i in range(nrTables):
q_to_ntbl(db,
'{}_{}'.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 check_autofc_overlap(checkShp, epsg, dbname, outOverlaps):
"""
Check if the features of one Feature Class overlaps each other
"""
import os
from glass.ng.sql.db import create_db
from glass.ng.sql.q import q_to_ntbl
from glass.g.it.db import shp_to_psql
from glass.g.it.shp import dbtbl_to_shp
create_db(dbname, api='psql')
# Send data to postgresql
table = shp_to_psql(dbname, checkShp, srsEpsgCode=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]
q_to_ntbl(dbname, resultTable, q, api='psql')
dbtbl_to_shp(
dbname, resultTable, "geom", outOverlaps, api='psql', epsg=epsg)
return outOverlaps
def lnh_to_polg(db, intbl, outtbl):
"""
Line to Polygons
"""
from glass.ng.sql.q import q_to_ntbl
Q = ("SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS gid, "
"(ST_Dump(ST_Polygonize(geom))).geom AS geom FROM ("
"SELECT ST_Node(ST_Collect(geom)) AS geom FROM ("
"SELECT (ST_Dump(geom)).geom FROM {}"
") AS foo"
") AS foo").format(intbl)
return q_to_ntbl(db, outtbl, Q)
def get_nearStopTable(db, stopsTable, stopsId, stopsGeom, stopsRoute, outT):
"""
Creates a table with the distance between all stops and the near
stop of another route
"""
from glass.ng.sql.q import q_to_ntbl
q = ("SELECT * FROM ("
"SELECT {routeF}, stop_b, "
"CASE WHEN distance = MIN(distance) OVER("
"PARTITION BY {routeF}, stop_b) "
"THEN stop_a ELSE NULL END AS stop_a, "
"CASE WHEN distance = MIN(distance) OVER("
"PARTITION BY {routeF}, stop_b) "
"THEN distance ELSE NULL END AS distance "
"FROM ("
"SELECT {routeF}, stop_a, stop_a_geom, stop_b, stop_b_geom, "
"ST_Distance(stop_a_geom, stop_b_geom) AS distance "
"FROM ("
"SELECT {routeF}, {stopF} AS stop_a, {stopG} AS stop_a_geom "
"FROM {t} GROUP BY {routeF}, {stopF}, {stopG}"
") AS stops1, ("
"SELECT {stopF} AS stop_b, {stopG} AS stop_b_geom "
"FROM {t} GROUP BY {stopF}, {stopG}"
") AS stops2"
") AS foo"
") AS foo2 "
"WHERE stop_a IS NOT NULL").format(routeF=stopsRoute,
t=stopsTable,
stopF=stopsId,
stopG=stopsGeom)
q_to_ntbl(db, outT, q)
return outT
def col_to_timestamp(db,
inTbl,
dayCol,
hourCol,
minCol,
secCol,
newTimeCol,
outTbl,
selColumns=None,
whr=None):
"""
Columns to timestamp column
"""
from glass.pys import obj_to_lst
selCols = obj_to_lst(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))
q_to_ntbl(db, outTbl, sql, api='psql')
return outTbl
