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 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 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 distinct_to_table(db, pgtable, outable, cols=None): """ Distinct values of one column to a new table """ from glass.pys import obj_to_lst from glass.ng.sql.c import sqlcon cols = obj_to_lst(cols) if not cols: from glass.ng.prop.sql import cols_name cols = cols_name(db, pgtable, api='psql') con = sqlcon(db) cs = con.cursor() cs.execute( ("CREATE TABLE {nt} AS " "SELECT {cls} FROM {t} GROUP BY {cls}").format(nt=outable, cls=', '.join(cols), t=pgtable)) con.commit() cs.close() con.close() return outable
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 change_field_type(db, table, fields, outable, cols=None): """ Imagine a table with numeric data saved as text. This method convert that numeric data to a numeric field. fields = {'field_name' : 'field_type'} """ from glass.ng.prop.sql import cols_name if not cols: cols = cols_name(db, table) else: from glass.pys import obj_to_lst cols = obj_to_lst(cols) select_fields = [f for f in cols if f not in fields] con = sqlcon(db) # Create new table with the new field with converted values cursor = con.cursor() cursor.execute(('CREATE TABLE {} AS SELECT {}, {} FROM {}').format( outable, ', '.join(select_fields), ', '.join([ 'CAST({f_} AS {t}) AS {f_}'.format(f_=f, t=fields[f]) for f in fields ]), table)) con.commit() cursor.close() con.close()
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 get_stop_words(inTbl, fidCol, txtCol, outFile, lang='portuguese', inSheet=None, db=None): """ Pick a text column and save it in a new column only with the stop words. Uses PostgreSQL dictionaries to get stop words """ from glass.pys.oss import fprop from glass.ng.prop.sql import cols_name from glass.ng.sql.db import create_db from glass.ng.it.sql import tbl_to_db from glass.ng.it import db_to_tbl FILENAME = fprop(inTbl, 'fn') # Create Temp database db = create_db("db_" + FILENAME if not db else db) # Send table to PostgreSQL tbl = tbl_to_db(inTbl, db, FILENAME, sheet=inSheet, api_db='psql') cols = cols_name(db, tbl, sanitizeSpecialWords=None, api='psql') # Sanitize data and create a new column only with stop words Q1 = ( "(SELECT *, to_tsvector('{_lang}', regexp_replace(" "regexp_replace(lower(unaccent({txt_c})), 'http://[^:\s]+(\S+)', " "' ', 'g'), '[^\w]+', ' ', 'g')) " "AS txt_data FROM {t}) AS stop_table" ).format(_lang=lang, txt_c=txtCol, t=tbl) Q2 = ( "SELECT {selCols}, ARRAY_TO_STRING(array_agg(" "word ORDER BY word_index), ' ', '*') AS {outCol}, " "REPLACE(CAST(STRIP(" "stop_table.txt_data) AS text), '''', '') AS no_duplicated " "FROM (" "SELECT fid, word, CAST(UNNEST(word_index) AS integer) " "AS word_index FROM (" "SELECT fid, SPLIT_PART(tst, ';', 1) AS word, " "STRING_TO_ARRAY(SPLIT_PART(tst, ';', 2), ',') AS word_index FROM (" "SELECT {fid} AS fid, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(" "CAST(UNNEST(txt_data) AS text), " "',{{', ',\"{{'), ',\"{{', ';'), '}}\"', ''), " "'(', ''), '}}', '') AS tst " "FROM {tbl}" ") AS foo" ") AS foo2" ") AS foo3 INNER JOIN {tbl} ON foo3.fid = stop_table.{fid} " "GROUP BY {selCols}, stop_table.txt_data" ).format( outCol="clean_" + txtCol, tbl=Q1, fid=fidCol, selCols=", ".join(["stop_table.{}".format(i) for i in cols]) ) # Export new table return db_to_tbl(db, Q2, outFile, sheetsNames=inSheet)
def q_to_obj(dbname, query, db_api='psql', geomCol=None, epsg=None, of='df', cols=None, dbset='default'): """ Query database and convert data to Pandas Dataframe/GeoDataFrame API's Available: * psql; * sqlite; * mysql; output format options ("of" parameter): * df (Pandas Dataframe); * dict (Python Dict); """ if not query.startswith('SELECT '): # Assuming query is a table name from glass.pys import obj_to_lst from glass.ng.prop.sql import cols_name cols = cols_name(dbname, query) if not cols else \ obj_to_lst(cols) query = "SELECT {} FROM {}".format( ", ".join(["{t}.{c} AS {c}".format(t=query, c=i) for i in cols]), query) if not geomCol: import pandas from glass.ng.sql.c import alchemy_engine pgengine = alchemy_engine(dbname, api=db_api, dbset=dbset) df = pandas.read_sql(query, pgengine, columns=None) else: from geopandas import GeoDataFrame from glass.ng.sql.c import sqlcon con = sqlcon(dbname, sqlAPI='psql', dbset=dbset) df = GeoDataFrame.from_postgis( query, con, geom_col=geomCol, crs="EPSG:{}".format(str(epsg)) if epsg else None) if of == 'dict': df = df.to_dict(orient="records") return df
def add_endpnt_to_tbl(db, inTable, outTable, idCol='gid', geomCol='geom', startCol="start_vertex", endCol="end_vertex"): """ Add start/end points columns to table """ from glass.ng.sql.q import q_to_ntbl from glass.ng.prop.sql import cols_name return q_to_ntbl(db, 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(db, inTable)), stPnt=startCol, endPnt=endCol, colId=idCol, geomF=geomCol, table=inTable), api='psql')
def sql_proj(dbname, tbl, otbl, oepsg, cols=None, geomCol=None, newGeom=None, whr=None, new_pk=None): """ Reproject geometric layer to another spatial reference system (srs) """ from glass.pys import obj_to_lst from glass.ng.sql.q import q_to_ntbl geomCol = 'geom' if not geomCol else geomCol newGeom = 'geom' if not newGeom else newGeom if not cols: from glass.ng.prop.sql import cols_name cols = cols_name(dbname, tbl) cols.remove(geomCol) else: cols = obj_to_lst(cols) if geomCol in cols and geomCol == newGeom: cols.remove(geomCol) cols.append('{c} AS old_{c}'.format(c=geomCol)) Q = ( "SELECT {}, ST_Transform({}, {}) AS {} " "FROM {}{}" ).format( ", ".join(cols), geomCol, str(oepsg), newGeom, tbl, "" if not whr else " WHERE {}".format(whr) ) otbl = q_to_ntbl(dbname, otbl, Q, api='psql') if new_pk: from glass.ng.sql.k import create_pk create_pk(dbname, otbl, new_pk) return otbl
def split_lines_on_pnt(db, inTbl, pntTbl, outTbl, idlnhPnt, lnhid): """ Split lines on point locations """ from glass.ng.prop.sql import cols_name from glass.ng.sql.q import q_to_ntbl # Get cols of lnhTbl cols = ", ".join([c for c in cols_name( db, inTbl, sanitizeSpecialWords=True, api='psql' ) if c != 'geom' and c != idlnhPnt]) # Force MultiLineString to LineString sanQ = ( "SELECT {lid}, {cln}, (ST_Dump(geom)).geom AS geom " "FROM {t}) AS mtbl" ).format(lid=lnhid, cln=cols, t=inTbl) # Split Query Q = ( "SELECT {lid}, {cln}, (ST_Dump(geom)).geom AS geom FROM (" "SELECT mtbl.{lid}, {cln}, " "CASE " "WHEN jtbl.{pid} IS NULL THEN mtbl.geom " "ELSE ST_Split(mtbl.geom, jtbl.geom) " "END AS geom " "FROM {lnh_tbl} LEFT JOIN (" "SELECT {pid}, ST_Collect(geom) AS geom " "FROM {pnt_tbl} " "GROUP BY {pid}" ") AS jtbl on mtbl.{lid} = jtbl.{pid}" ") AS foo" ).format( lid=lnhid, cln=cols, pid=idlnhPnt, lnh_tbl=sanQ, pnt_tbl=pntTbl ) # Produce new table and return it return q_to_ntbl(db, outTbl, Q)
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_endpoint_ispoint(db, lnhTable, pntTable, outTable, nodeStart, nodeEnd, pointId, pntGeom="geom"): """ Check if a Start/End point in a table with line geometries is a point in other table. """ from glass.ng.sql.q import q_to_ntbl from glass.ng.prop.sql import cols_name tCols = [ x for x in cols_name(db, lnhTable) if x != nodeStart and x != nodeEnd ] return q_to_ntbl( db, outTable, ("SELECT * FROM (" "SELECT {fooCols}, foo.{stPnt}, foo.{endPnt}, " "CASE " "WHEN start_tbl.start_x IS NOT NULL THEN 1 ELSE 0 " "END AS start_isstop, " "CASE " "WHEN end_tbl.end_x IS NOT NULL THEN 1 ELSE 0 " "END AS end_isstop, start_tbl.start_id, end_tbl.end_id " "FROM (" "SELECT *, " "CAST(((round(CAST(ST_X({stPnt}) AS numeric), 4)) * 10000) " "AS integer) AS start_x, " "CAST(((round(CAST(ST_Y({stPnt}) AS numeric), 4)) * 10000) " "AS integer) AS start_y, " "CAST(((round(CAST(ST_X({endPnt}) AS numeric), 4)) * 10000) " "AS integer) AS end_x, " "CAST(((round(CAST(ST_Y({endPnt}) AS numeric), 4)) * 10000) " "AS integer) AS end_y " "FROM {lnhT}" ") AS foo " "LEFT JOIN (" "SELECT CAST(((round(CAST(ST_X({pntG}) AS numeric), 4)) " "* 10000) AS integer) AS start_x, " "CAST(((round(CAST(ST_Y({pntG}) AS numeric), 4)) " "* 10000) AS integer) AS start_y, " "{pntid} AS start_id FROM {pntT}" ") AS start_tbl " "ON foo.start_x = start_tbl.start_x AND " "foo.start_y = start_tbl.start_y " "LEFT JOIN (" "SELECT CAST(((round(CAST(ST_X({pntG}) AS numeric), 4)) " "* 10000) AS integer) AS end_x, " "CAST(((round(CAST(ST_Y({pntG}) AS numeric), 4)) " "* 10000) as integer) AS end_y, " "{pntid} AS end_id FROM {pntT}" ") AS end_tbl " "ON foo.end_x = end_tbl.end_x AND foo.end_y = end_tbl.end_y" ") AS foo2 " "GROUP BY {cols}, {stPnt}, {endPnt}, start_isstop, end_isstop, " "start_id, end_id").format(fooCols=", ".join( ["foo.{}".format(c) for c in tCols]), stPnt=nodeStart, endPnt=nodeEnd, lnhT=lnhTable, pntT=pntTable, pntG=pntGeom, cols=", ".join(tCols), pntid=pointId), api='psql')
def intersection(dbname, aShp, bShp, pk, aGeom, bGeom, output, primitive, priority, new_pk='fid_pk', new_geom='geom'): """ Intersect two layers primitive is the geometric primitive (point, line, polygons) priority is an indication of the fields that the user wants to include in the output - fields of aShp or fields of bShp. The user could giver a list (with fields for selection) as value for the priority argument. """ from glass.ng.sql.c import sqlcon from glass.ng.prop.sql import cols_name if priority == 'a': cols_tbl = cols_name(dbname, aShp) cols_tbl.remove(aGeom) elif priority == 'b': cols_tbl = cols_name(dbname, bShp) cols_tbl.remove(bGeom) elif type(priority) == type([0]): cols_tbl = priority cols_tbl.remove(pk) conn = sqlcon(dbname, sqlAPI='psql') cursor = conn.cursor() if primitive == 'point': cols_tbl = ['{t}.{c}'.format(t=aShp, c=x) for x in cols_tbl] if priority == 'a': sel_geom = "{f}.{g}".format(f=aShp, g=aGeom) elif priority == 'b' or type(priority) == type([]): sel_geom = "{f}.{g}".format(f=bShp, g=bGeom) cursor.execute(( "CREATE TABLE {out} AS SELECT {cols}, {int_geom} AS {ngeom} FROM {pnt} " "INNER JOIN {poly} ON ST_Within({pnt}.{geom_a}, " "{poly}.{geom_b});").format( out=output, cols=','.join(cols_tbl), pnt=aShp, geom_a=aGeom, geom_b=bGeom, poly=bShp, int_geom=sel_geom, ngeom=new_geom )) elif primitive == 'line': cols_tbl = ['{t}.{c}'.format(t=output, c=x) for x in cols_tbl] cols_tbl.append(new_geom) cursor.execute(( "CREATE TABLE {out} AS SELECT {cols} FROM (SELECT {shp_a}.*, " "(ST_DUMP(ST_Intersection({shp_b}.geom, {shp_a}.{geom_fld}))).geom " "FROM {shp_b} INNER JOIN {shp_a} ON ST_Intersects({shp_b}.geom, " "{shp_a}.{geom_fld})) As {out} WHERE ST_Dimension({out}.geom) = " "1;").format( out=output, cols=','.join(cols_tbl), shp_a=aShp, shp_b=bShp, geom_fld=aGeom )) elif primitive == 'polygon': cols_tbl = ['{t}.{c}'.format(t=aShp, c=x) for x in cols_tbl] cursor.execute(( 'CREATE TABLE {out} AS SELECT {cols}, ST_Multi(ST_Buffer' '(ST_Intersection({shp_b}.geom, {shp_a}.{geom_fld}), 0.0)) As ' '{ngeom} FROM {shp_b} INNER JOIN {shp_a} ON ST_Intersects({shp_b}.geom, ' '{shp_a}.{geom_fld}) WHERE Not ST_IsEmpty(ST_Buffer(' 'ST_Intersection({shp_b}.geom, {shp_a}.{geom_fld}), 0.0));').format( out=output, cols=','.join(cols_tbl), shp_a=aShp, shp_b = bShp, geom_fld=aGeom, ngeom=new_geom )) cursor.execute( "ALTER TABLE {out} ADD COLUMN {fid_pk} BIGSERIAL PRIMARY KEY;".format( out=output, fid_pk=new_pk)) conn.commit() cursor.close() conn.close() return output, new_pk, new_geom
def pgtables_to_layer_withStyle_by_col( pgtables, sldData, db, workName=None, storeName=None, sldGeom='Polygon', DATATYPE='QUANTITATIVE', TABLE_DESIGNATION=None, COL_DESIGNATION=None, exclude_cols=None, pathToSLDfiles=None): """ Create a new Geoserver Workspace, create a postgis store and one layer for each table in 'pgtables'. Each layer will have multiple styles - one style by column in it. Parameters: 1) pgtables - List of PSQL tables to be transformed as Geoserver Layers 2) sldData - sldData should be a xls table with the styles specifications. For QUANTITATIVE DATA: The table should have two sheets: one for colors and other for intervals: COLORS SHEET STRUCTURE (Sheet Index = 0): cls_id | R | G | B | STROKE_R | STROKE_G | STROKE_B | STROKE_W 1 | X | X | X | X | X | X | 1 2 | X | X | X | X | X | X | 1 3 | X | X | X | X | X | X | 1 4 | X | X | X | X | X | X | 1 5 | X | X | X | X | X | X | 1 INTERVALS SHEET STRUCTURE (Sheet Index = 1): | 0 | 1 | 2 | 3 | 4 | 5 col_0 | 0 | 5 | 10 | 15 | 20 | 25 col_1 | 0 | 6 | 12 | 18 | 24 | 30 ... col_n | 0 | 5 | 10 | 15 | 20 | 25 For CATEGORICAL DATA: The table should have only one sheet: CATEGORICAL SHEET STRUCTURE | R | G | B | STROKE_R | STROKE_G | STROKE_B | STROKE_W attr_1 | X | X | X | X | X | X | 1 attr_2 | X | X | X | X | X | X | 1 ... attr_n | X | X | X | X | X | X | 1 3) pgsql_con - Dict with parameters that will be used to connect to PostgreSQL d = { 'HOST' : 'localhost', 'PORT' : '5432', 'USER' : 'postgres', 'PASSWORD' : 'admin', 'DATABASE' : 'db_name' } 4) workName - String with the name of the Geoserver workspace that will be created 5) storeName - String with the name of the Geoserver store that will be created 6) geoserver_con - Dict with parameters to connect to Geoserver 7) sldGeom - Data Geometry. At the moment, the options 'Polygon' and 'Line' are valid. 8) DATATYPE='QUANTITATIVE' | 'CATEGORICAL' 9) TABLE_DESIGNATION - Table with the association between pgtables name and the designation to be used to name the Geoserver Layer. 10) COL_DESIGNATION - Table xls with association between each column and one style that will be used to present the information of that column. The style designation could not have blank characters. 11) exclude_cols - One style will be created for each column in one pgtable. The columns in 'exclude_cols' will not have a style. 12) pathToSLDfiles - Absolute path to the folder where the SLD (Style Layer Descriptor) will be stored. """ import os; from glass.pys import obj_to_lst from glass.ng.rd import tbl_to_obj from glass.pys.oss import mkdir from glass.ng.prop.sql import cols_name from glass.g.wg.gsrv.ws import create_ws from glass.g.wg.gsrv.stores import create_pgstore from glass.g.wg.gsrv.lyrs import pub_pglyr from glass.g.wg.gsrv.sty import create_style from glass.g.wg.gsrv.sty import lst_styles from glass.g.wg.gsrv.sty import del_style from glass.g.wg.gsrv.sty import assign_style_to_layer from glass.g.wg.sld import write_sld # Sanitize PGtables pgtables = obj_to_lst(pgtables) if not pgtables: raise ValueError('pgtables value is not valid!') exclude_cols = obj_to_lst(exclude_cols) STY_DESIGNATION = tbl_to_obj( COL_DESIGNATION, useFirstColAsIndex=True, output='dict', colsAsArray=True ) if COL_DESIGNATION else None LYR_DESIGNATION = tbl_to_obj( TABLE_DESIGNATION, useFirstColAsIndex=True, output='dict', colsAsArray=True ) if TABLE_DESIGNATION else None # Get intervals and colors data if DATATYPE == 'QUANTITATIVE': if os.path.exists(sldData): FF = os.path.splitext(sldData)[1] if FF == '.xls' or FF == '.xlsx': colorsDict = tbl_to_obj( sldData, sheet=0, useFirstColAsIndex=True, output='dict' ); intervalsDict = tbl_to_obj( sldData, sheet=1, useFirstColAsIndex=True, output='dict' ) else: raise ValueError(( 'At the moment, for DATATYPE QUANTITATIVE, sldData ' 'has to be a xls table' )) else: raise ValueError(( '{} is not a valid file' ).format(sldData)) elif DATATYPE == 'CATEGORICAL': if os.path.exists(sldData): if os.path.splitext(sldData)[1] == 'xls': colorsDict = tbl_to_obj( sldData, sheet=0, useFirstColAsIndex=True, output='dict' ) else: raise ValueError(( 'At the moment, for DATATYPE CATEGORICAL, sldData ' 'has to be a xls table' )) else: raise ValueError(( '{} is not a valid file' ).format(sldData)) else: raise ValueError('{} is not avaiable at the moment'.format(DATATYPE)) # Create Workspace workName = 'w_{}'.format(db) if not workName else workName create_ws(workName, overwrite=True) # Create Store storeName = db if not storeName else storeName create_pgstore(storeName, workName, db) # Create folder for sld's wTmp = mkdir(os.path.join( os.path.dirname(sldData), 'sldFiles' )) if not pathToSLDfiles else pathToSLDfiles # List styles in geoserver STYLES = lst_styles() # For each table in PGTABLES for PGTABLE in pgtables: # Publish Postgis table TITLE = None if not LYR_DESIGNATION else LYR_DESIGNATION[PGTABLE][0] pub_pglyr(workName, storeName, PGTABLE, title=TITLE) # List PGTABLE columns pgCols = cols_name(db, PGTABLE) # For each column for col in pgCols: if exclude_cols and col in exclude_cols: continue STYLE_NAME = '{}_{}'.format( PGTABLE, STY_DESIGNATION[col][0] ) if STY_DESIGNATION else '{}_{}'.format(PGTABLE, col) if STYLE_NAME in STYLES: del_style(STYLE_NAME) # Create Object with association between colors and intervals d = {} OPACITY = str(colorsDict[1]['OPACITY']) for i in colorsDict: d[i] = { 'R' : colorsDict[i]['R'], 'G' : colorsDict[i]['G'], 'B' : colorsDict[i]['B'] } if DATATYPE == 'QUANTITATIVE': d[i]['min'] = intervalsDict[col][i-1] d[i]['max'] = intervalsDict[col][i] if 'STROKE_R' in colorsDict[i] and \ 'STROKE_G' in colorsDict[i] and \ 'STROKE_B' in colorsDict[i]: d[i]['STROKE'] = { 'R' : colorsDict[i]['STROKE_R'], 'G' : colorsDict[i]['STROKE_G'], 'B' : colorsDict[i]['STROKE_B'] } if 'STROKE_W' in colorsDict[i]: d[i]['STROKE']['WIDTH'] = colorsDict[i]['STROKE_W'] # Create SLD sldFile = write_sld( col, d, os.path.join(wTmp, '{}.sld'.format(col)), geometry=sldGeom, DATA=DATATYPE, opacity=OPACITY ) # Create Style create_style(STYLE_NAME, sldFile) # Apply SLD assign_style_to_layer(STYLE_NAME, PGTABLE)
def osm_to_relationaldb(osmData, inSchema, osmGeoTbl, osmCatTbl, osmRelTbl, outSQL=None, db_name=None): """ PostgreSQL - OSM Data to Relational Model E.g. osmData = '/mnt/d/mystuff/flainar/portugal-latest.osm.pbf' inSchema = { "TBL" : ['points', 'lines', 'multipolygons'], 'FID' : 'ogc_fid', "COLS" : [ 'name', 'osm_id', #"ST_X(wkb_geometry) AS longitude", #"ST_Y(wkb_geometry) AS latitude", "wkb_geometry AS geom", "NULL AS featurecategoryid", "NULL AS flainarcategoryid", "NULL AS createdby", "NOW() AS createdon", "NULL AS updatedon", "NULL AS deletedon" ], "NOT_KEYS" : [ 'ogc_fid', 'osm_id', 'name', "wkb_geometry", 'healthcare2', 'other_tags', 'osm_way_id', 'ref', 'sidewalk', 'z_order', 'is_in', 'cuisine', 'barrier', 'busway' ] } osmGeoTbl = { "points" : {"TBL" : 'osm_position', "FID" : 'positionid'}, "multipolygons" : {"TBL" : "osm_polygons", "FID" : 'polygonid'}, "lines" : {"TBL" : 'osm_lines', "FID" : 'lineid'} } osmCatTbl = { "TBL" : 'osmcategory', "FID" : "osmcategoryid", "KEY_COL" : "key", "VAL_COL" : "value", "COLS" : [ "NULL AS createdby", "NOW() AS createdon", "NULL AS updatedon", "NULL AS deletedon" ] } osmRelTbl = { "points" : {"TBL" : "position_osmcat", "FID" : 'pososmcatid'}, "multipolygons" : {"TBL" : "polygons_osmcat", "FID" : 'polygoncatid'}, "lines" : {"TBL" : "lines_osmcat", "FID" : 'linecatid'}, } outSQL = '/mnt/d/mystuff/flainar/portugal-osmdb.sql' db_name='osm_pt' """ from glass.pys import obj_to_lst from glass.pys.oss import fprop from glass.ng.prop.sql import cols_name from glass.ng.sql.q import q_to_ntbl from glass.ng.sql.db import create_db from glass.g.it.db import osm_to_psql inSchema["TBL"] = obj_to_lst(inSchema["TBL"]) # Create DB osm_fn = fprop(osmData, 'fn') osm_fn = osm_fn.replace('-', '').replace('.', '') db = create_db(osm_fn if not db_name else db_name, api='psql') # Send OSM data to Database osm_to_psql(osmData, db) # Get KEYS COLUMNS transcols = {} for tbl in inSchema["TBL"]: transcols[tbl] = [ c for c in cols_name(db, tbl, sanitizeSpecialWords=None) if c not in inSchema["NOT_KEYS"] ] # Create osmGeoTbl osmgeotbl = [ q_to_ntbl(db, osmGeoTbl[tbl]['TBL'], ("SELECT {} AS {}, {} FROM {}").format( inSchema["FID"], osmGeoTbl[tbl]["FID"], ", ".join(inSchema["COLS"]), tbl), api='psql') for tbl in inSchema["TBL"] ] # Create OSM categories table qs = [] for tbl in inSchema["TBL"]: qs.extend([ ("SELECT '{keyV}' AS {keyC}, CAST({t}.{keyV} AS text) AS {valC} " "FROM {t} WHERE {t}.{keyV} IS NOT NULL " "GROUP BY {t}.{keyV}").format(keyV=c, t=tbl, keyC=osmCatTbl["KEY_COL"], valC=osmCatTbl["VAL_COL"]) for c in transcols[tbl] ]) osmcatbl = q_to_ntbl( db, osmCatTbl["TBL"], ("SELECT row_number() OVER(ORDER BY {keyC}) " "AS {osmcatid}, {keyC}, {valC}{ocols} " "FROM ({q}) AS foo").format( q="SELECT {k}, {v} FROM ({t}) AS kvtbl GROUP BY {k}, {v}".format( k=osmCatTbl["KEY_COL"], v=osmCatTbl["VAL_COL"], t=" UNION ALL ".join(qs), ) if len(inSchema["TBL"]) > 1 else " UNION ALL ".join(qs), keyC=osmCatTbl["KEY_COL"], osmcatid=osmCatTbl["FID"], valC=osmCatTbl["VAL_COL"], ocols="" if "COLS" not in osmCatTbl else ", {}".format(", ".join( osmCatTbl["COLS"]))), api='psql') # Create relation table osmreltbl = [] for tbl in inSchema["TBL"]: qs = [( "SELECT {fid}, '{keyV}' AS key, CAST({t}.{keyV} AS text) AS osmval " "FROM {t} WHERE {t}.{keyV} IS NOT NULL").format( fid=inSchema["FID"], keyV=c, t=tbl) for c in transcols[tbl]] osmreltbl.append( q_to_ntbl( db, osmRelTbl[tbl]["TBL"], ("SELECT foo.{fid} AS {nfid}, catbl.{osmcatfid} " "FROM ({mtbl}) AS foo INNER JOIN {catTbl} AS catbl " "ON foo.key = catbl.{catkey} AND foo.osmval = catbl.{catval}" ).format(mtbl=" UNION ALL ".join(qs), fid=inSchema["FID"], nfid=osmRelTbl[tbl]["FID"], catTbl=osmCatTbl["TBL"], osmcatfid=osmCatTbl["FID"], catkey=osmCatTbl["KEY_COL"], catval=osmCatTbl["VAL_COL"]), api='psql')) if not outSQL: return osmgeotbl, osmcatbl, osmreltbl else: from glass.ng.sql.bkup import dump_tbls return dump_tbls(db, osmgeotbl + [osmcatbl] + osmreltbl, outSQL)