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 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)