def create_spatial_table(conn,tbl_name,srid,geom_type,other_cols=None,geom_col='geometry'):
"""
Create a spatial table.
Args:
conn: database connection object
tbl_name: name of new table
srid: SRID of projection for new table
geom_type: type of geometries in new table
other_cols: sequence of tuples with (name,type) of additional columns
geom_col: name of geometry column
"""
geom_type = geom_type.upper()
if geom_type not in GEOM_COL_TYPES:
raise ValueError("Unknown geometry column type '{}'".format(geom_type))
srid = int(srid)
if other_cols:
other_col_list = []
other_col_sep = ', '
for colname,coltype in other_cols:
esc_name = u.quote_identifier(colname)
coltype = coltype.upper()
if coltype not in OTHER_COL_TYPES:
raise ValueError("Unknown column type '{}'.".format(coltype))
other_col_list.append(esc_name+' '+coltype)
other_col_str = other_col_sep + other_col_sep.join(other_col_list)
else:
other_col_str = ''
esc_tbl_name = u.quote_identifier(tbl_name)
esc_geom_col = u.quote_identifier(geom_col)
# Create table for geometries
sql = 'CREATE TABLE IF NOT EXISTS {} ("id" INTEGER PRIMARY KEY ASC{})'\
.format(esc_tbl_name,other_col_str)
logger.debug("Table creation SQL:\n '{}'".format(sql))
logger.info("Creating table {}".format(esc_tbl_name))
conn.execute(sql)
sql = 'SELECT AddGeometryColumn({}, {}, {}, "{}", "XY")'\
.format(esc_tbl_name,esc_geom_col,srid,geom_type)
logger.debug("Geometry column creation SQL:\n '{}'".format(sql))
logger.info("Creating geometry column {}.".format(esc_geom_col))
conn.execute(sql)
def drop_table(conn,table):
"""
Drop table from database.
Args:
conn: database connection object
table: name of table to drop
"""
sql = 'DROP TABLE IF EXISTS {}'.format(u.quote_identifier(table))
logger.debug("Drop table SQL:\n '{}'".format(sql))
logger.info('Dropping table {}'.format(table))
conn.execute(sql)
def get_intersections(sdb_conn, regions_table='nuts2006', grid_table='merra_grid', reg_proj=3035, grid_proj=4326):
"""
Calculate area of intersections between regions and grid cells.
Args:
sdb_conn: connection object to spatial database
regions_table: name of table containing region geometries (default 'nuts2006')
grid_table: name of table containing grid geometries (default 'merra_grid')
reg_proj: projection SRID for regions' projection (default 3035 (European LAEA))
grid_proj: projection SRID for grid's projection (default 4326 (lat/long))
Returns:
dict: areas of intersecting grid cells for each region
e.g. {'reg1': {'grid_cell1': area1, 'grid_cell2': area1, ...}, ...}
"""
sql = """SELECT gid, rid,SUM(AREA(ST_Intersection(rgeom,ggeom))) AS overlap
FROM
(SELECT TRANSFORM(r.geometry,:1) AS rgeom,TRANSFORM(g.geometry,:1) as ggeom,g.id AS gid,r.NUTS_ID AS rid
FROM {grid_table} AS g, {regions_table} as r
WHERE r.STAT_LEVL_=2 AND g.ROWID IN (
SELECT ROWID
FROM SpatialIndex
WHERE f_table_name = {grid_table}
AND search_frame = TRANSFORM(rgeom,:2))
AND ST_Intersects(ggeom,rgeom))
GROUP BY rid,gid
ORDER BY gid""".format(grid_table=u.quote_identifier(grid_table),regions_table=u.quote_identifier(regions_table))
c = sdb_conn.cursor()
logger.info("Calculating grid/regions intersections from spatial data.")
logger.debug("SQL:\n"+sql)
result = c.execute(sql, (reg_proj,grid_proj))
logger.debug("Creating dictionary.")
intersections = u.multilevel_dict()
for gid,rid,overlap in result:
intersections[rid][gid] = overlap
return intersections
def copy_geoms(to_conn,to_table,from_db,from_tables,geom_col='geometry',
calc_area_col='shape_area',**kwargs):
"""
Copy geometries in 'from_tables' in 'from_db' into 'to_table' in 'to_conn'.
Args:
to_conn: target database connection object
to_table: table to copy into
from_db: path to original db
from_tables: list of tables to copy
geom_col: name of geometry column
calc_area_col: name of column for calculated area (area not calculated
if set to None)
"""
# Escape input values
esc_to_table = u.quote_identifier(to_table)
esc_from_tables = map(u.quote_identifier,from_tables)
esc_geom_col = u.quote_identifier(geom_col)
create_spatial_table(to_conn,to_table,3035,'MULTIPOLYGON')
print conn.execute("SELECT COUNT({}) FROM {} LIMIT 1".format(esc_geom_col,esc_to_table)).fetchall()