def insert_site_in_current(cursor, target_srid, timestamp, site, values_hash):
position_part = transform_srid(site.position.as_sql(), target_srid)
query = (
"INSERT INTO {0}.{1}_curr "
"(entity_id, timestamp, hash, position) "
"VALUES (%s, %s, %s, {2})").format(SCHEMA, SITE_TABLENAME,
position_part)
args = site.entity_id, timestamp, values_hash
try:
cursor.execute(query, args)
except psycopg2.DatabaseError as exc:
if exc.pgcode == psycopg2.errorcodes.UNIQUE_VIOLATION:
fix = partial(remove_from_current, cursor, SITE_TABLENAME,
site.entity_id)
raise RecoverableError(str(exc), fix)
else:
raise NonRecoverableError(
"{0}, {1!s} in query '{2}'".format(exc.pgcode, exc, query))
except (psycopg2.DataError, psycopg2.ProgrammingError,
psycopg2.IntegrityError) as exc:
raise NonRecoverableError(
"{0}, {1!s} in query '{2}'".format(exc.pgcode, exc, query))
def get_entities_in_region(conn, database_srid, region, region_srid,
entitytype):
bbox2d = transform_srid(set_srid(make_box_2d(region), region_srid),
database_srid)
relation_name = get_relation_name(conn, "Cell", entitytype.name)
relation_site_cell_name = get_relation_name(conn, "Cell", "Site")
query = (
"SELECT r.target_id "
"FROM relation.\"{0}\" r "
"JOIN relation.\"{1}\" site_rel on site_rel.source_id = r.source_id "
"JOIN gis.site site ON site.entity_id = site_rel.target_id "
"AND site.position && {2}").format(
relation_name, relation_site_cell_name, bbox2d)
with closing(conn.cursor()) as cursor:
try:
cursor.execute(query, region)
except psycopg2.ProgrammingError:
conn.rollback()
rows = []
else:
rows = cursor.fetchall()
return [entity_id for entity_id, in rows]
def retrieve_related_trend(conn, database_srid, region, region_srid,
datasource, entitytype, attribute_name,
granularity_str, timestamp, limit=None):
granularity = create_granularity(granularity_str)
with closing(conn.cursor()) as cursor:
trendstore = TrendStore.get(cursor, datasource, entitytype,
granularity)
partition = trendstore.partition(timestamp)
table = partition.table()
full_base_tbl_name = table.render()
relation_name = get_relation_name(conn, "Cell", entitytype.name)
relation_cell_site_name = get_relation_name(conn, "Cell", "Site")
bbox2d = transform_srid(set_srid(make_box_2d(region), region_srid),
database_srid)
query = (
"SELECT r.source_id, r.target_id, base_table.\"{0}\" "
"FROM {1} base_table "
"JOIN relation.\"{2}\" r ON r.target_id = base_table.entity_id "
"JOIN relation.\"{3}\" site_rel on site_rel.source_id = r.source_id "
"JOIN gis.site site ON site.entity_id = site_rel.target_id "
"AND site.position && {4} "
"WHERE base_table.\"timestamp\" = %(timestamp)s").format(
attribute_name, full_base_tbl_name, relation_name,
relation_cell_site_name, bbox2d)
args = {
"left": region["left"],
"bottom": region["bottom"],
"right": region["right"],
"top": region["top"],
"timestamp": timestamp}
with closing(conn.cursor()) as cursor:
try:
cursor.execute(query, args)
except psycopg2.ProgrammingError:
conn.rollback()
rows = []
else:
rows = cursor.fetchall()
result = {}
for entity_id, related_entity_id, value in rows:
if entity_id not in result:
result[entity_id] = {}
result[entity_id][related_entity_id] = value
return result
def retrieve_related_attribute(conn, database_srid, region, region_srid,
datasource, entitytype, attribute_name, limit):
with closing(conn.cursor()) as cursor:
attributestore = AttributeStore.get_by_attributes(cursor, datasource, entitytype)
full_base_tbl_name = attributestore.history_table.render()
relation_name = get_relation_name(conn, "Cell", entitytype.name)
relation_cell_site_name = get_relation_name(conn, "Cell", "Site")
bbox2d = transform_srid(set_srid(make_box_2d(region), region_srid),
database_srid)
query ="""
SELECT public.first(entity_id) AS entity_id, public.first(related_id) AS related_id,
min(timestamp) AS "timestamp", \"{0}\" FROM (
SELECT entity_id, related_id, timestamp, \"{0}\", sum(change) OVER w2 AS run
FROM ( SELECT entity_id, related_id, timestamp, \"{0}\",
CASE WHEN \"{0}\" <> lag(\"{0}\" ) OVER w THEN 1 ELSE 0 END AS change
FROM ( SELECT r.source_id as entity_id, r.target_id as related_id,
base_table.timestamp, base_table.\"{0}\" FROM {1} base_table
JOIN relation.\"{2}\" r ON r.target_id = base_table.entity_id
JOIN relation.\"{3}\" site_rel on site_rel.source_id = r.source_id
JOIN gis.site site ON site.entity_id = site_rel.target_id AND
site.position && {4} ) a WINDOW w AS (PARTITION BY entity_id ORDER BY
timestamp asc)) t WINDOW w2 AS (PARTITION BY entity_id ORDER BY
timestamp ASC)) runs GROUP BY entity_id, \"{0}\" , run""".format(
attribute_name, full_base_tbl_name, relation_name,
relation_cell_site_name, bbox2d)
with closing(conn.cursor()) as cursor:
try:
cursor.execute(query, region)
except psycopg2.ProgrammingError:
conn.rollback()
rows = []
else:
rows = cursor.fetchall()
result = {}
for entity_id, related_id, timestamp, value in rows:
if not value is None:
if entity_id not in result:
result[entity_id] = {}
if related_id not in result[entity_id]:
result[entity_id][related_id] = []
result[entity_id][related_id].append(
(timestamp, value))
return result
def get_sites_in_region(conn, database_srid, region, region_srid):
bbox2d = transform_srid(set_srid(make_box_2d(region), region_srid),
database_srid)
query = (
"SELECT site.entity_id "
"FROM gis.site site "
"WHERE site.position && {}").format(bbox2d)
with closing(conn.cursor()) as cursor:
try:
cursor.execute(query, region)
except psycopg2.ProgrammingError:
conn.rollback()
rows = []
else:
rows = cursor.fetchall()
return [entity_id for entity_id, in rows]
def create_sql_for_bbox(conn, entitytype, site_srid, region, srid):
box2d = transform_srid(set_srid(make_box_2d(region), srid), site_srid)
if entitytype.name.lower() == "cell":
relation_name = get_relation_name(conn, entitytype.name, "site")
return (
"SELECT cell.entity_id AS id "
"FROM gis.cell_curr cell "
"JOIN relation.\"{}\" rel on rel.source_id = cell.entity_id "
"JOIN gis.site_curr site on rel.target_id = site.entity_id "
"WHERE site.position && {}").format(relation_name, box2d)
else:
relation_site_cell_name = get_relation_name(conn, "Cell", "Site")
relation_name = get_relation_name(conn, "Cell", entitytype.name)
return (
"SELECT r.target_id AS id "
"FROM relation.\"{}\" r "
"JOIN relation.\"{}\" rel on rel.source_id = r.source_id "
"JOIN gis.site_curr site on rel.target_id = site.entity_id "
"WHERE site.position && {}").format(
relation_name, relation_site_cell_name, box2d)
