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