def break_lines_on_points(lineShp,
pntShp,
outShp,
lnhidonpnt,
api='shply',
db=None):
"""
Break lines on points location
api's available:
- shply (shapely);
- psql (postgis);
"""
if api == 'shply':
result = shply_break_lines_on_points(lineShp, pntShp, lnhidonpnt,
outShp)
elif api == 'psql':
from glass.pys.oss import fprop
from glass.ng.sql.db import create_db
from glass.g.it.db import shp_to_psql
from glass.g.it.shp import dbtbl_to_shp
from glass.g.gp.brk.sql import split_lines_on_pnt
# Create DB
if not db:
db = create_db(fprop(lineShp, 'fn', forceLower=True), api='psql')
else:
from glass.ng.prop.sql import db_exists
isDb = db_exists(db)
if not isDb:
db = create_db(db, api='psql')
# Send Data to BD
lnhTbl = shp_to_psql(db, lineShp, api="shp2pgsql")
pntTbl = shp_to_psql(db, pntShp, api="shp2pgsql")
# Get result
outTbl = split_lines_on_pnt(db, lnhTbl, pntTbl,
fprop(outShp, 'fn', forceLower=True),
lnhidonpnt, 'gid')
# Export result
result = dbtbl_to_shp(db,
outTbl,
"geom",
outShp,
inDB='psql',
tableIsQuery=None,
api="pgsql2shp")
else:
raise ValueError("API {} is not available".format(api))
return result
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 remove_deadend(inShp, outShp, db=None):
"""
Remove deadend
"""
from glass.pys.oss import fprop
from glass.ng.sql.db import create_db
from glass.g.it.db import shp_to_psql
from glass.g.gp.cln.sql import rm_deadend
from glass.g.it.shp import dbtbl_to_shp
# Create DB
if not db:
db = create_db(fprop(inShp, 'fn', forceLower=True), api='psql')
else:
from glass.ng.prop.sql import db_exists
isDb = db_exists(db)
if not isDb:
create_db(db, api='psql')
# Send data to Database
inTbl = shp_to_psql(db, inShp, api="shp2pgsql", encoding="LATIN1")
# Produce result
out_tbl = rm_deadend(db, inTbl, fprop(
outShp, 'fn', forceLower=True))
# Export result
return dbtbl_to_shp(
db, out_tbl, "geom", outShp, inDB='psql', tableIsQuery=None,
api="pgsql2shp"
)
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 line_intersect_to_pnt(inShp, outShp, db=None):
"""
Get Points where two line features of the same feature class
intersects.
"""
from glass.pys.oss import fprop
from glass.g.it.shp import dbtbl_to_shp
from glass.ng.sql.db import create_db
from glass.g.it.db import shp_to_psql
from glass.g.gp.ovl.sql import line_intersection_pnt
# Create DB if necessary
if not db:
db = create_db(fprop(inShp, 'fn', forceLower=True), api='psql')
else:
from glass.ng.prop.sql import db_exists
isDb = db_exists(db)
if not isDb:
create_db(db, api='psql')
# Send data to DB
inTbl = shp_to_psql(db, inShp, api="shp2pgsql")
# Get result
outTbl = line_intersection_pnt(db, inTbl,
fprop(outShp, 'fn', forceLower=True))
# Export data from DB
outShp = dbtbl_to_shp(db,
outTbl,
"geom",
outShp,
inDB='psql',
tableIsQuery=None,
api="pgsql2shp")
return outShp
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 proj(inShp,
outShp,
outEPSG,
inEPSG=None,
gisApi='ogr',
sql=None,
db_name=None):
"""
Project Geodata using GIS
API's Available:
* ogr;
* ogr2ogr;
* pandas;
* ogr2ogr_SQLITE;
* psql;
"""
import os
if gisApi == 'ogr':
"""
Using ogr Python API
"""
if not inEPSG:
raise ValueError(
'To use ogr API, you should specify the EPSG Code of the'
' input data using inEPSG parameter')
from osgeo import ogr
from glass.g.lyr.fld import copy_flds
from glass.g.prop.feat import get_gtype
from glass.g.prop import drv_name
from glass.g.prop.prj import get_sref_from_epsg, get_trans_param
from glass.pys.oss import fprop
def copyShp(out, outDefn, lyr_in, trans):
for f in lyr_in:
g = f.GetGeometryRef()
g.Transform(trans)
new = ogr.Feature(outDefn)
new.SetGeometry(g)
for i in range(0, outDefn.GetFieldCount()):
new.SetField(
outDefn.GetFieldDefn(i).GetNameRef(), f.GetField(i))
out.CreateFeature(new)
new.Destroy()
f.Destroy()
# ####### #
# Project #
# ####### #
transP = get_trans_param(inEPSG, outEPSG)
inData = ogr.GetDriverByName(drv_name(inShp)).Open(inShp, 0)
inLyr = inData.GetLayer()
out = ogr.GetDriverByName(drv_name(outShp)).CreateDataSource(outShp)
outlyr = out.CreateLayer(fprop(outShp, 'fn'),
get_sref_from_epsg(outEPSG),
geom_type=get_gtype(inShp,
name=None,
py_cls=True,
gisApi='ogr'))
# Copy fields to the output
copy_flds(inLyr, outlyr)
# Copy/transform features from the input to the output
outlyrDefn = outlyr.GetLayerDefn()
copyShp(outlyr, outlyrDefn, inLyr, transP)
inData.Destroy()
out.Destroy()
elif gisApi == 'ogr2ogr':
"""
Transform SRS of any OGR Compilant Data. Save the transformed data
in a new file
"""
if not inEPSG:
from glass.g.prop.prj import get_shp_epsg
inEPSG = get_shp_epsg(inShp)
if not inEPSG:
raise ValueError('To use ogr2ogr, you must specify inEPSG')
from glass.pys import execmd
from glass.g.prop import drv_name
cmd = ('ogr2ogr -f "{}" {} {}{} -s_srs EPSG:{} -t_srs EPSG:{}').format(
drv_name(outShp), outShp, inShp,
'' if not sql else ' -dialect sqlite -sql "{}"'.format(sql),
str(inEPSG), str(outEPSG))
outcmd = execmd(cmd)
elif gisApi == 'ogr2ogr_SQLITE':
"""
Transform SRS of a SQLITE DB table. Save the transformed data in a
new table
"""
from glass.pys import execmd
if not inEPSG:
raise ValueError(
('With ogr2ogr_SQLITE, the definition of inEPSG is '
'demandatory.'))
# TODO: Verify if database is sqlite
db, tbl = inShp['DB'], inShp['TABLE']
sql = 'SELECT * FROM {}'.format(tbl) if not sql else sql
outcmd = execmd(
('ogr2ogr -update -append -f "SQLite" {db} -nln "{nt}" '
'-dialect sqlite -sql "{_sql}" -s_srs EPSG:{inepsg} '
'-t_srs EPSG:{outepsg} {db}').format(db=db,
nt=outShp,
_sql=sql,
inepsg=str(inEPSG),
outepsg=str(outEPSG)))
elif gisApi == 'pandas':
# Test if input Shp is GeoDataframe
from glass.g.rd.shp import shp_to_obj
from glass.g.wt.shp import df_to_shp
df = shp_to_obj(inShp)
# Project df
newDf = df.to_crs('EPSG:{}'.format(str(outEPSG)))
# Save as file
return df_to_shp(df, outShp)
elif gisApi == 'psql':
from glass.ng.sql.db import create_db
from glass.pys.oss import fprop
from glass.g.it.db import shp_to_psql
from glass.g.it.shp import dbtbl_to_shp
from glass.g.prj.sql import sql_proj
# Create Database
if not db_name:
db_name = create_db(fprop(outShp, 'fn', forceLower=True),
api='psql')
else:
from glass.ng.prop.sql import db_exists
isDb = db_exists(db_name)
if not isDb:
create_db(db_name, api='psql')
# Import Data
inTbl = shp_to_psql(db_name, inShp, api='shp2pgsql', encoding="LATIN1")
# Transform
oTbl = sql_proj(db_name,
inTbl,
fprop(outShp, 'fn', forceLower=True),
outEPSG,
geomCol='geom',
newGeom='geom')
# Export
outShp = dbtbl_to_shp(db_name,
oTbl,
'geom',
outShp,
api='psql',
epsg=outEPSG)
else:
raise ValueError('Sorry, API {} is not available'.format(gisApi))
return outShp
def tbl_to_areamtx(inShp, col_a, col_b, outXls, db=None, with_metrics=None):
"""
Table to Matrix
Table as:
FID | col_a | col_b | geom
0 | 1 | A | A | ....
0 | 2 | A | B | ....
0 | 3 | A | A | ....
0 | 4 | A | C | ....
0 | 5 | A | B | ....
0 | 6 | B | A | ....
0 | 7 | B | A | ....
0 | 8 | B | B | ....
0 | 9 | B | B | ....
0 | 10 | C | A | ....
0 | 11 | C | B | ....
0 | 11 | C | D | ....
To:
classe | A | B | C | D
A | | | |
B | | | |
C | | | |
D | | | |
col_a = rows
col_b = cols
api options:
* pandas;
* psql;
"""
# TODO: check if col_a and col_b exists in table
if not db:
import pandas as pd
import numpy as np
from glass.g.rd.shp import shp_to_obj
from glass.ng.wt import obj_to_tbl
# Open data
df = shp_to_obj(inShp)
# Remove nan values
df = df[pd.notnull(df[col_a])]
df = df[pd.notnull(df[col_b])]
# Get Area
df['realarea'] = df.geometry.area / 1000000
# Get rows and Cols
rows = df[col_a].unique()
cols = df[col_b].unique()
refval = list(np.sort(np.unique(np.append(rows, cols))))
# Produce matrix
outDf = []
for row in refval:
newCols = [row]
for col in refval:
newDf = df[(df[col_a] == row) & (df[col_b] == col)]
if not newDf.shape[0]:
newCols.append(0)
else:
area = newDf.realarea.sum()
newCols.append(area)
outDf.append(newCols)
outcols = ['class'] + refval
outDf = pd.DataFrame(outDf, columns=outcols)
if with_metrics:
from glass.ng.cls.eval import get_measures_for_mtx
out_df = get_measures_for_mtx(outDf, 'class')
return obj_to_tbl(out_df, outXls)
# Export to Excel
return obj_to_tbl(outDf, outXls)
else:
from glass.pys.oss import fprop
from glass.ng.sql.db import create_db
from glass.ng.prop.sql import db_exists
from glass.g.it.db import shp_to_psql
from glass.g.dp.tomtx.sql import tbl_to_area_mtx
from glass.ng.it import db_to_tbl
# Create database if not exists
is_db = db_exists(db)
if not is_db:
create_db(db, api='psql')
# Add data to database
tbl = shp_to_psql(db, inShp, api='shp2pgsql')
# Create matrix
mtx = tbl_to_area_mtx(db, tbl, col_a, col_b, fprop(outXls, 'fn'))
# Export result
return db_to_tbl(db, mtx, outXls, sheetsNames='matrix')
def v_break_at_points(workspace, loc, lineShp, pntShp, db, srs, out_correct,
out_tocorrect):
"""
Break lines at points - Based on GRASS GIS v.edit
Use PostGIS to sanitize the result
TODO: Confirm utility
Problem: GRASS GIS always uses the first line to break.
"""
import os
from glass.g.it.db import shp_to_psql
from glass.g.it.shp import dbtbl_to_shp
from glass.g.wenv.grs import run_grass
from glass.pys.oss import fprop
from glass.ng.sql.db import create_db
from glass.ng.sql.q import q_to_ntbl
tmpFiles = os.path.join(workspace, loc)
gbase = run_grass(workspace, location=loc, srs=srs)
import grass.script as grass
import grass.script.setup as gsetup
gsetup.init(gbase, workspace, loc, 'PERMANENT')
from glass.g.it.shp import shp_to_grs, grs_to_shp
grsLine = shp_to_grs(lineShp, fprop(lineShp, 'fn', forceLower=True))
vedit_break(grsLine, pntShp, geomType='line')
LINES = grs_to_shp(grsLine, os.path.join(tmpFiles, grsLine + '_v1.shp'),
'line')
# Sanitize output of v.edit.break using PostGIS
create_db(db, overwrite=True, api='psql')
LINES_TABLE = shp_to_psql(db,
LINES,
srsEpsgCode=srs,
pgTable=fprop(LINES, 'fn', forceLower=True),
api="shp2pgsql")
# Delete old/original lines and stay only with the breaked one
Q = ("SELECT {t}.*, foo.cat_count FROM {t} INNER JOIN ("
"SELECT cat, COUNT(cat) AS cat_count, "
"MAX(ST_Length(geom)) AS max_len "
"FROM {t} GROUP BY cat"
") AS foo ON {t}.cat = foo.cat "
"WHERE foo.cat_count = 1 OR foo.cat_count = 2 OR ("
"foo.cat_count = 3 AND ST_Length({t}.geom) <= foo.max_len)").format(
t=LINES_TABLE)
CORR_LINES = q_to_ntbl(db,
"{}_corrected".format(LINES_TABLE),
Q,
api='psql')
# TODO: Delete Rows that have exactly the same geometry
# Highlight problems that the user must solve case by case
Q = ("SELECT {t}.*, foo.cat_count FROM {t} INNER JOIN ("
"SELECT cat, COUNT(cat) AS cat_count FROM {t} GROUP BY cat"
") AS foo ON {t}.cat = foo.cat "
"WHERE foo.cat_count > 3").format(t=LINES_TABLE)
ERROR_LINES = q_to_ntbl(db,
"{}_not_corr".format(LINES_TABLE),
Q,
api='psql')
dbtbl_to_shp(db, CORR_LINES, "geom", out_correct, api="pgsql2shp")
dbtbl_to_shp(db, ERROR_LINES, "geom", out_tocorrect, api="pgsql2shp")
def dsn_data_collection_by_multibuffer(inBuffers,
workspace,
db,
datasource,
keywords=None):
"""
Extract Digital Social Network Data for each sub-buffer in buffer.
A sub-buffer is a buffer with a radius equals to the main buffer radius /2
and with a central point at North, South, East, West, Northeast, Northwest,
Southwest and Southeast of the main buffer central point.
inBuffers = {
"lisbon" : {
'x' : -89004.994779, # in meters
'y' : -102815.866054, # in meters
'radius' : 10000,
'epsg' : 3763
},
"london : {
'x' : -14210.551441, # in meters
'y' : 6711542.47559, # in meters
'radius' : 10000,
'epsg' : 3857
}
}
or
inBuffers = {
"lisbon" : {
"path" : /path/to/file.shp,
"epsg" : 3763
}
}
keywords = ['flood', 'accident', 'fire apartment', 'graffiti', 'homeless']
datasource = 'facebook' or datasource = 'flickr'
TODO: Only works for Flickr and Facebook
"""
import os
from osgeo import ogr
from glass.pys import obj_to_lst
from glass.ng.sql.db import create_db
from glass.ng.sql.q import q_to_ntbl
from glass.g.wt.sql import df_to_db
from glass.g.it.db import shp_to_psql
from glass.g.it.shp import dbtbl_to_shp
from glass.g.gp.prox.bfing import get_sub_buffers, dic_buffer_array_to_shp
if datasource == 'flickr':
from glass.g.acq.dsn.flickr import photos_location
elif datasource == 'facebook':
from glass.g.acq.dsn.fb.places import places_by_query
keywords = obj_to_lst(keywords)
keywords = ["None"] if not keywords else keywords
# Create Database to Store Data
create_db(db, overwrite=True, api='psql')
for city in inBuffers:
# Get Smaller Buffers
if "path" in inBuffers[city]:
# Get X, Y and Radius
from glass.g.prop.feat.bf import bf_prop
__bfprop = bf_prop(inBuffers[city]["path"],
inBuffers[city]["epsg"],
isFile=True)
inBuffers[city]["x"] = __bfprop["X"]
inBuffers[city]["y"] = __bfprop["Y"]
inBuffers[city]["radius"] = __bfprop["R"]
inBuffers[city]["list_buffer"] = [{
'X': inBuffers[city]["x"],
'Y': inBuffers[city]["y"],
'RADIUS': inBuffers[city]['radius'],
'cardeal': 'major'
}] + get_sub_buffers(inBuffers[city]["x"], inBuffers[city]["y"],
inBuffers[city]["radius"])
# Smaller Buffers to File
multiBuffer = os.path.join(workspace, 'buffers_{}.shp'.format(city))
dic_buffer_array_to_shp(inBuffers[city]["list_buffer"],
multiBuffer,
inBuffers[city]['epsg'],
fields={'cardeal': ogr.OFTString})
# Retrive data for each keyword and buffer
# Record these elements in one dataframe
c = None
tblData = None
for bf in inBuffers[city]["list_buffer"]:
for k in keywords:
if datasource == 'flickr':
tmpData = photos_location(
bf,
inBuffers[city]["epsg"],
keyword=k if k != 'None' else None,
epsg_out=inBuffers[city]["epsg"],
onlySearchAreaContained=False)
elif datasource == 'facebook':
tmpData = places_by_query(
bf,
inBuffers[city]["epsg"],
keyword=k if k != 'None' else None,
epsgOut=inBuffers[city]["epsg"],
onlySearchAreaContained=False)
if type(tmpData) == int:
print("NoData finded for buffer '{}' and keyword '{}'".
format(bf['cardeal'], k))
continue
tmpData["keyword"] = k
tmpData["buffer_or"] = bf["cardeal"]
if not c:
tblData = tmpData
c = 1
else:
tblData = tblData.append(tmpData, ignore_index=True)
inBuffers[city]["data"] = tblData
# Get data columns names
cols = inBuffers[city]["data"].columns.values
dataColumns = [
c for c in cols if c != 'geom' and c != 'keyword' \
and c != 'buffer_or' and c != 'geometry'
]
# Send data to PostgreSQL
if 'geometry' in cols:
cgeom = 'geometry'
else:
cgeom = 'geom'
inBuffers[city]["table"] = 'tbldata_{}'.format(city)
df_to_db(db,
inBuffers[city]["data"],
inBuffers[city]["table"],
api='psql',
epsg=inBuffers[city]["epsg"],
geomType='POINT',
colGeom=cgeom)
# Send Buffers data to PostgreSQL
inBuffers[city]["pg_buffer"] = shp_to_psql(
db,
multiBuffer,
pgTable='buffers_{}'.format(city),
api="shp2pgsql",
srsEpsgCode=inBuffers[city]["epsg"])
inBuffers[city]["filter_table"] = q_to_ntbl(
db,
"filter_{}".format(inBuffers[city]["table"]),
("SELECT srcdata.*, "
"array_agg(buffersg.cardeal ORDER BY buffersg.cardeal) "
"AS intersect_buffer FROM ("
"SELECT {cols}, keyword, geom, "
"array_agg(buffer_or ORDER BY buffer_or) AS extracted_buffer "
"FROM {pgtable} "
"GROUP BY {cols}, keyword, geom"
") AS srcdata, ("
"SELECT cardeal, geom AS bfg FROM {bftable}"
") AS buffersg "
"WHERE ST_Intersects(srcdata.geom, buffersg.bfg) IS TRUE "
"GROUP BY {cols}, keyword, geom, extracted_buffer").format(
cols=", ".join(dataColumns),
pgtable=inBuffers[city]["table"],
bftable=inBuffers[city]["pg_buffer"]),
api='psql')
inBuffers[city]["outside_table"] = q_to_ntbl(
db,
"outside_{}".format(inBuffers[city]["table"]),
("SELECT * FROM ("
"SELECT srcdata.*, "
"array_agg(buffersg.cardeal ORDER BY buffersg.cardeal) "
"AS not_intersect_buffer FROM ("
"SELECT {cols}, keyword, geom, "
"array_agg(buffer_or ORDER BY buffer_or) AS extracted_buffer "
"FROM {pgtable} "
"GROUP BY {cols}, keyword, geom"
") AS srcdata, ("
"SELECT cardeal, geom AS bfg FROM {bftable}"
") AS buffersg "
"WHERE ST_Intersects(srcdata.geom, buffersg.bfg) IS NOT TRUE "
"GROUP BY {cols}, keyword, geom, extracted_buffer"
") AS foo WHERE array_length(not_intersect_buffer, 1) = 9"
).format(cols=", ".join(dataColumns),
pgtable=inBuffers[city]["table"],
bftable=inBuffers[city]["pg_buffer"]),
api='psql')
# Union these two tables
inBuffers[city]["table"] = q_to_ntbl(
db,
"data_{}".format(city),
("SELECT * FROM {intbl} UNION ALL "
"SELECT {cols}, keyword, geom, extracted_buffer, "
"CASE WHEN array_length(not_intersect_buffer, 1) = 9 "
"THEN '{array_symbol}' ELSE not_intersect_buffer END AS "
"intersect_buffer FROM {outbl}").format(
intbl=inBuffers[city]["filter_table"],
outbl=inBuffers[city]["outside_table"],
cols=", ".join(dataColumns),
array_symbol='{' + '}'),
api='psql')
"""
Get Buffers table with info related:
-> pnt_obtidos = nr pontos obtidos usando esse buffer
-> pnt_obtidos_fora = nt pontos obtidos fora desse buffer, mas
obtidos com ele
-> pnt_intersect = nt pontos que se intersectam com o buffer
-> pnt_intersect_non_obtain = nr pontos que se intersectam mas nao
foram obtidos como buffer
"""
inBuffers[city]["pg_buffer"] = q_to_ntbl(
db,
"dt_{}".format(inBuffers[city]["pg_buffer"]),
("SELECT main.*, get_obtidos.pnt_obtidos, "
"obtidos_fora.pnt_obtidos_fora, intersecting.pnt_intersect, "
"int_not_obtained.pnt_intersect_non_obtain "
"FROM {bf_table} AS main "
"LEFT JOIN ("
"SELECT gid, cardeal, COUNT(gid) AS pnt_obtidos "
"FROM {bf_table} AS bf "
"INNER JOIN {dt_table} AS dt "
"ON bf.cardeal = ANY(dt.extracted_buffer) "
"GROUP BY gid, cardeal"
") AS get_obtidos ON main.gid = get_obtidos.gid "
"LEFT JOIN ("
"SELECT gid, cardeal, COUNT(gid) AS pnt_obtidos_fora "
"FROM {bf_table} AS bf "
"INNER JOIN {dt_table} AS dt "
"ON bf.cardeal = ANY(dt.extracted_buffer) "
"WHERE ST_Intersects(bf.geom, dt.geom) IS NOT TRUE "
"GROUP BY gid, cardeal"
") AS obtidos_fora ON main.gid = obtidos_fora.gid "
"LEFT JOIN ("
"SELECT gid, cardeal, COUNT(gid) AS pnt_intersect "
"FROM {bf_table} AS bf "
"INNER JOIN {dt_table} AS dt "
"ON bf.cardeal = ANY(dt.intersect_buffer) "
"GROUP BY gid, cardeal"
") AS intersecting ON main.gid = intersecting.gid "
"LEFT JOIN ("
"SELECT gid, cardeal, COUNT(gid) AS pnt_intersect_non_obtain "
"FROM {bf_table} AS bf "
"INNER JOIN {dt_table} AS dt "
"ON bf.cardeal = ANY(dt.intersect_buffer) "
"WHERE NOT (bf.cardeal = ANY(dt.extracted_buffer)) "
"GROUP BY gid, cardeal"
") AS int_not_obtained "
"ON main.gid = int_not_obtained.gid "
"ORDER BY main.gid").format(bf_table=inBuffers[city]["pg_buffer"],
dt_table=inBuffers[city]["table"]),
api='psql')
"""
Get Points table with info related:
-> nobtido = n vezes um ponto foi obtido
-> obtido_e_intersect = n vezes um ponto foi obtido usando um buffer
com o qual se intersecta
-> obtido_sem_intersect = n vezes um ponto foi obtido usando um buffer
com o qual nao se intersecta
-> nintersect = n vezes que um ponto se intersecta com um buffer
-> intersect_sem_obtido = n vezes que um ponto nao foi obtido apesar
de se intersectar com o buffer
"""
inBuffers[city]["table"] = q_to_ntbl(
db,
"info_{}".format(city),
("SELECT {cols}, dt.keyword, dt.geom, "
"CAST(dt.extracted_buffer AS text) AS extracted_buffer, "
"CAST(dt.intersect_buffer AS text) AS intersect_buffer, "
"array_length(extracted_buffer, 1) AS nobtido, "
"SUM(CASE WHEN ST_Intersects(bf.geom, dt.geom) IS TRUE "
"THEN 1 ELSE 0 END) AS obtido_e_intersect, "
"(array_length(extracted_buffer, 1) - SUM("
"CASE WHEN ST_Intersects(bf.geom, dt.geom) IS TRUE "
"THEN 1 ELSE 0 END)) AS obtido_sem_intersect, "
"array_length(intersect_buffer, 1) AS nintersect, "
"(array_length(intersect_buffer, 1) - SUM("
"CASE WHEN ST_Intersects(bf.geom, dt.geom) IS TRUE "
"THEN 1 ELSE 0 END)) AS intersect_sem_obtido "
"FROM {dt_table} AS dt "
"INNER JOIN {bf_table} AS bf "
"ON bf.cardeal = ANY(dt.extracted_buffer) "
"GROUP BY {cols}, dt.keyword, dt.geom, "
"dt.extracted_buffer, dt.intersect_buffer").format(
dt_table=inBuffers[city]["table"],
bf_table=inBuffers[city]["pg_buffer"],
cols=", ".join(["dt.{}".format(x) for x in dataColumns])),
api='psql')
# Export Results
dbtbl_to_shp(db,
inBuffers[city]["table"],
'geom',
os.path.join(workspace,
"{}.shp".format(inBuffers[city]["table"])),
api='psql',
epsg=inBuffers[city]["epsg"])
dbtbl_to_shp(db,
inBuffers[city]["pg_buffer"],
'geom',
os.path.join(
workspace,
"{}.shp".format(inBuffers[city]["pg_buffer"])),
api='psql',
epsg=inBuffers[city]["epsg"])
return inBuffers
def check_shape_diff(SHAPES_TO_COMPARE, OUT_FOLDER, REPORT, DB,
GRASS_REGION_TEMPLATE):
"""
Script to check differences between pairs of Feature Classes
Suponha que temos diversas Feature Classes (FC) e que cada uma delas
possui um determinado atributo; imagine tambem que,
considerando todos os pares possiveis entre estas FC,
se pretende comparar as diferencas na distribuicao dos valores
desse atributo para cada par.
* Dependencias:
- GRASS;
- PostgreSQL;
- PostGIS.
"""
import datetime
import os
import pandas
from glass.ng.sql.q import q_to_obj
from glass.ng.it import db_to_tbl
from glass.g.wt.sql import df_to_db
from glass.g.dp.rst.toshp import rst_to_polyg
from glass.g.it.db import shp_to_psql
from glass.g.dp.tomtx import tbl_to_area_mtx
from glass.g.prop import check_isRaster
from glass.pys.oss import fprop
from glass.ng.sql.db import create_db
from glass.ng.sql.tbl import tbls_to_tbl
from glass.ng.sql.q import q_to_ntbl
# Check if folder exists, if not create it
if not os.path.exists(OUT_FOLDER):
from glass.pys.oss import mkdir
mkdir(OUT_FOLDER, overwrite=None)
else:
raise ValueError('{} already exists!'.format(OUT_FOLDER))
from glass.g.wenv.grs import run_grass
gbase = run_grass(OUT_FOLDER,
grassBIN='grass78',
location='shpdif',
srs=GRASS_REGION_TEMPLATE)
import grass.script as grass
import grass.script.setup as gsetup
gsetup.init(gbase, OUT_FOLDER, 'shpdif', 'PERMANENT')
from glass.g.it.shp import shp_to_grs, grs_to_shp
from glass.g.it.rst import rst_to_grs
from glass.g.tbl.col import rn_cols
# Convert to SHAPE if file is Raster
i = 0
_SHP_TO_COMPARE = {}
for s in SHAPES_TO_COMPARE:
isRaster = check_isRaster(s)
if isRaster:
# To GRASS
rstName = fprop(s, 'fn')
inRst = rst_to_grs(s, "rst_" + rstName, as_cmd=True)
# To Vector
d = rst_to_polyg(inRst,
rstName,
rstColumn="lulc_{}".format(i),
gisApi="grass")
# Export Shapefile
shp = grs_to_shp(d, os.path.join(OUT_FOLDER, d + '.shp'), "area")
_SHP_TO_COMPARE[shp] = "lulc_{}".format(i)
else:
# To GRASS
grsV = shp_to_grs(s, fprop(s, 'fn'), asCMD=True)
# Change name of column with comparing value
ncol = "lulc_{}".format(str(i))
rn_cols(grsV, {SHAPES_TO_COMPARE[s]: "lulc_{}".format(str(i))},
api="grass")
# Export
shp = grs_to_shp(grsV, os.path.join(OUT_FOLDER, grsV + '_rn.shp'),
"area")
_SHP_TO_COMPARE[shp] = "lulc_{}".format(str(i))
i += 1
SHAPES_TO_COMPARE = _SHP_TO_COMPARE
__SHAPES_TO_COMPARE = SHAPES_TO_COMPARE
# Create database
create_db(DB, api='psql')
""" Union SHAPEs """
UNION_SHAPE = {}
FIX_GEOM = {}
SHPS = list(__SHAPES_TO_COMPARE.keys())
for i in range(len(SHPS)):
for e in range(i + 1, len(SHPS)):
# Optimized Union
print("Union between {} and {}".format(SHPS[i], SHPS[e]))
time_a = datetime.datetime.now().replace(microsecond=0)
__unShp = optimized_union_anls(
SHPS[i],
SHPS[e],
os.path.join(OUT_FOLDER, "un_{}_{}.shp".format(i, e)),
GRASS_REGION_TEMPLATE,
os.path.join(OUT_FOLDER, "work_{}_{}".format(i, e)),
multiProcess=True)
time_b = datetime.datetime.now().replace(microsecond=0)
print(time_b - time_a)
# Rename cols
unShp = rn_cols(
__unShp, {
"a_" + __SHAPES_TO_COMPARE[SHPS[i]]:
__SHAPES_TO_COMPARE[SHPS[i]],
"b_" + __SHAPES_TO_COMPARE[SHPS[e]]:
__SHAPES_TO_COMPARE[SHPS[e]]
})
UNION_SHAPE[(SHPS[i], SHPS[e])] = unShp
# Send data to postgresql
SYNTH_TBL = {}
for uShp in UNION_SHAPE:
# Send data to PostgreSQL
union_tbl = shp_to_psql(DB, UNION_SHAPE[uShp], api='shp2pgsql')
# Produce table with % of area equal in both maps
areaMapTbl = q_to_ntbl(
DB,
"{}_syn".format(union_tbl),
("SELECT CAST('{lulc_1}' AS text) AS lulc_1, "
"CAST('{lulc_2}' AS text) AS lulc_2, "
"round("
"CAST(SUM(g_area) / 1000000 AS numeric), 4"
") AS agree_area, round("
"CAST((SUM(g_area) / MIN(total_area)) * 100 AS numeric), 4"
") AS agree_percentage, "
"round("
"CAST(MIN(total_area) / 1000000 AS numeric), 4"
") AS total_area FROM ("
"SELECT {map1_cls}, {map2_cls}, ST_Area(geom) AS g_area, "
"CASE "
"WHEN {map1_cls} = {map2_cls} "
"THEN 1 ELSE 0 "
"END AS isthesame, total_area FROM {tbl}, ("
"SELECT SUM(ST_Area(geom)) AS total_area FROM {tbl}"
") AS foo2"
") AS foo WHERE isthesame = 1 "
"GROUP BY isthesame").format(
lulc_1=fprop(uShp[0], 'fn'),
lulc_2=fprop(uShp[1], 'fn'),
map1_cls=__SHAPES_TO_COMPARE[uShp[0]],
map2_cls=__SHAPES_TO_COMPARE[uShp[1]],
tbl=union_tbl),
api='psql')
# Produce confusion matrix for the pair in comparison
matrixTbl = tbl_to_area_mtx(DB, union_tbl,
__SHAPES_TO_COMPARE[uShp[0]],
__SHAPES_TO_COMPARE[uShp[1]],
union_tbl + '_mtx')
SYNTH_TBL[uShp] = {"TOTAL": areaMapTbl, "MATRIX": matrixTbl}
# UNION ALL TOTAL TABLES
total_table = tbls_to_tbl(DB, [SYNTH_TBL[k]["TOTAL"] for k in SYNTH_TBL],
'total_table')
# Create table with % of agreement between each pair of maps
mapsNames = q_to_obj(
DB,
("SELECT lulc FROM ("
"SELECT lulc_1 AS lulc FROM {tbl} GROUP BY lulc_1 "
"UNION ALL "
"SELECT lulc_2 AS lulc FROM {tbl} GROUP BY lulc_2"
") AS lu GROUP BY lulc ORDER BY lulc").format(tbl=total_table),
db_api='psql').lulc.tolist()
FLDS_TO_PIVOT = ["agree_percentage", "total_area"]
Q = ("SELECT * FROM crosstab('"
"SELECT CASE "
"WHEN foo.lulc_1 IS NOT NULL THEN foo.lulc_1 ELSE jtbl.tmp1 "
"END AS lulc_1, CASE "
"WHEN foo.lulc_2 IS NOT NULL THEN foo.lulc_2 ELSE jtbl.tmp2 "
"END AS lulc_2, CASE "
"WHEN foo.{valCol} IS NOT NULL THEN foo.{valCol} ELSE 0 "
"END AS agree_percentage FROM ("
"SELECT lulc_1, lulc_2, {valCol} FROM {tbl} UNION ALL "
"SELECT lulc_1, lulc_2, {valCol} FROM ("
"SELECT lulc_1 AS lulc_2, lulc_2 AS lulc_1, {valCol} "
"FROM {tbl}"
") AS tst"
") AS foo FULL JOIN ("
"SELECT lulc_1 AS tmp1, lulc_2 AS tmp2 FROM ("
"SELECT lulc_1 AS lulc_1 FROM {tbl} GROUP BY lulc_1 "
"UNION ALL "
"SELECT lulc_2 AS lulc_1 FROM {tbl} GROUP BY lulc_2"
") AS tst_1, ("
"SELECT lulc_1 AS lulc_2 FROM {tbl} GROUP BY lulc_1 "
"UNION ALL "
"SELECT lulc_2 AS lulc_2 FROM {tbl} GROUP BY lulc_2"
") AS tst_2 WHERE lulc_1 = lulc_2 GROUP BY lulc_1, lulc_2"
") AS jtbl ON foo.lulc_1 = jtbl.tmp1 AND foo.lulc_2 = jtbl.tmp2 "
"ORDER BY lulc_1, lulc_2"
"') AS ct("
"lulc_map text, {crossCols}"
")")
TOTAL_AGREE_TABLE = None
TOTAL_AREA_TABLE = None
for f in FLDS_TO_PIVOT:
if not TOTAL_AGREE_TABLE:
TOTAL_AGREE_TABLE = q_to_ntbl(
DB,
"agreement_table",
Q.format(tbl=total_table,
valCol=f,
crossCols=", ".join([
"{} numeric".format(map_) for map_ in mapsNames
])),
api='psql')
else:
TOTAL_AREA_TABLE = q_to_ntbl(DB,
"area_table",
Q.format(tbl=total_table,
valCol=f,
crossCols=", ".join([
"{} numeric".format(map_)
for map_ in mapsNames
])),
api='psql')
# Union Mapping
UNION_MAPPING = pandas.DataFrame(
[[k[0], k[1], fprop(UNION_SHAPE[k], 'fn')] for k in UNION_SHAPE],
columns=['shp_a', 'shp_b', 'union_shp'])
UNION_MAPPING = df_to_db(DB, UNION_MAPPING, 'union_map', api='psql')
# Export Results
TABLES = [UNION_MAPPING, TOTAL_AGREE_TABLE, TOTAL_AREA_TABLE
] + [SYNTH_TBL[x]["MATRIX"] for x in SYNTH_TBL]
SHEETS = ["union_map", "agreement_percentage", "area_with_data_km"] + [
"{}_{}".format(fprop(x[0], 'fn')[:15],
fprop(x[1], 'fn')[:15]) for x in SYNTH_TBL
]
db_to_tbl(DB, ["SELECT * FROM {}".format(x) for x in TABLES],
REPORT,
sheetsNames=SHEETS,
dbAPI='psql')
return REPORT
def shps_to_shp(shps, outShp, api="ogr2ogr", fformat='.shp',
dbname=None):
"""
Get all features in several Shapefiles and save them in one file
api options:
* ogr2ogr;
* psql;
* pandas;
* psql;
* grass;
"""
import os
if type(shps) != list:
# Check if is dir
if os.path.isdir(shps):
from glass.pys.oss import lst_ff
# List shps in dir
shps = lst_ff(shps, file_format=fformat)
else:
raise ValueError((
'shps should be a list with paths for Feature Classes or a path to '
'folder with Feature Classes'
))
if api == "ogr2ogr":
from glass.pys import execmd
from glass.g.prop import drv_name
out_drv = drv_name(outShp)
# Create output and copy some features of one layer (first in shps)
cmdout = execmd('ogr2ogr -f "{}" {} {}'.format(
out_drv, outShp, shps[0]
))
# Append remaining layers
lcmd = [execmd(
'ogr2ogr -f "{}" -update -append {} {}'.format(
out_drv, outShp, shps[i]
)
) for i in range(1, len(shps))]
elif api == 'pandas':
"""
Merge SHP using pandas
"""
from glass.g.rd.shp import shp_to_obj
from glass.g.wt.shp import df_to_shp
if type(shps) != list:
raise ValueError('shps should be a list with paths for Feature Classes')
dfs = [shp_to_obj(shp) for shp in shps]
result = dfs[0]
for df in dfs[1:]:
result = result.append(df, ignore_index=True, sort=True)
df_to_shp(result, outShp)
elif api == 'psql':
import os
from glass.ng.sql.tbl import tbls_to_tbl, del_tables
from glass.g.it.db import shp_to_psql
if not dbname:
from glass.ng.sql.db import create_db
create_db(dbname, api='psql')
pg_tbls = shp_to_psql(
dbname, shps, api="shp2pgsql"
)
if os.path.isfile(outShp):
from glass.pys.oss import fprop
outbl = fprop(outShp, 'fn')
else:
outbl = outShp
tbls_to_tbl(dbname, pg_tbls, outbl)
if outbl != outShp:
from glass.g.it.shp import dbtbl_to_shp
dbtbl_to_shp(
dbname, outbl, 'geom', outShp, inDB='psql',
api="pgsql2shp"
)
del_tables(dbname, pg_tbls)
elif api == 'grass':
from glass.g.wenv.grs import run_grass
from glass.pys.oss import fprop, lst_ff
from glass.g.prop.prj import get_shp_epsg
lshps = lst_ff(shps, file_format='.shp')
epsg = get_shp_epsg(lshps[0])
gwork = os.path.dirname(outShp)
outshpname = fprop(outShp, "fn")
loc = f'loc_{outshpname}'
gbase = run_grass(gwork, loc=loc, srs=epsg)
import grass.script.setup as gsetup
gsetup.init(gbase, gwork, loc, 'PERMANENT')
from glass.g.it.shp import shp_to_grs, grs_to_shp
# Import data
gshps = [shp_to_grs(s, fprop(s, 'fn'), asCMD=True) for s in lshps]
patch = vpatch(gshps, outshpname)
grs_to_shp(patch, outShp, "area")
else:
raise ValueError(
"{} API is not available"
)
return outShp
def lnh_to_polygons(inShp, outShp, api='saga', db=None):
"""
Line to Polygons
API's Available:
* saga;
* grass;
* pygrass;
* psql;
"""
if api == 'saga':
"""
http://www.saga-gis.org/saga_tool_doc/7.0.0/shapes_polygons_3.html
Converts lines to polygons. Line arcs are closed to polygons simply
by connecting the last point with the first. Optionally parts of
polylines can be merged into one polygon optionally.
"""
from glass.pys import execmd
rcmd = execmd(("saga_cmd shapes_polygons 3 -POLYGONS {} "
"LINES {} -SINGLE 1 -MERGE 1").format(outShp, inShp))
elif api == 'grass' or api == 'pygrass':
# Do it using GRASS GIS
import os
from glass.g.wenv.grs import run_grass
from glass.pys.oss import fprop
# Create GRASS GIS Session
wk = os.path.dirname(outShp)
lo = fprop(outShp, 'fn', forceLower=True)
gs = run_grass(wk, lo, srs=inShp)
import grass.script as grass
import grass.script.setup as gsetup
gsetup.init(gs, wk, lo, 'PERMANENT')
# Import Packages
from glass.g.it.shp import shp_to_grs, grs_to_shp
# Send data to GRASS GIS
lnh_shp = shp_to_grs(inShp,
fprop(inShp, 'fn', forceLower=True),
asCMD=True if api == 'grass' else None)
# Build Polylines
pol_lnh = line_to_polyline(lnh_shp,
"polylines",
asCmd=True if api == 'grass' else None)
# Polyline to boundary
bound = geomtype_to_geomtype(pol_lnh,
'bound_shp',
'line',
'boundary',
cmd=True if api == 'grass' else None)
# Boundary to Area
areas_shp = boundary_to_areas(bound,
lo,
useCMD=True if api == 'grass' else None)
# Export data
outShp = grs_to_shp(areas_shp,
outShp,
'area',
asCMD=True if api == 'grass' else None)
elif api == 'psql':
""" Do it using PostGIS """
from glass.pys.oss import fprop
from glass.ng.sql.db import create_db
from glass.g.it.db import shp_to_psql
from glass.g.it.shp import dbtbl_to_shp
from glass.g.dp.cg.sql import lnh_to_polg
from glass.g.prop.prj import get_shp_epsg
# Create DB
if not db:
db = create_db(fprop(inShp, 'fn', forceLower=True), api='psql')
else:
from glass.ng.prop.sql import db_exists
isDB = db_exists(db)
if not isDB:
create_db(db, api='psql')
# Send data to DB
in_tbl = shp_to_psql(db, inShp, api="shp2pgsql")
# Get Result
result = lnh_to_polg(db, in_tbl, fprop(outShp, 'fn', forceLower=True))
# Export Result
outshp = dbtbl_to_shp(db,
result,
"geom",
outShp,
api='psql',
epsg=get_shp_epsg(inShp))
else:
raise ValueError("API {} is not available".format(api))
return outShp
