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)