def create_network(self, nodes=[]): cmds = 0 session = Session() for res in session.query(Overlay): msg = json.dumps({'oid': res.id, 'time': time.time()}) # If nodes are passed, process only those overlaps containing # the provided node(s) if nodes: for r in res.overlaps: if r in nodes: self.redis_queue.rpush( config['redis']['processing_queue'], msg) cmds += 1 break else: self.redis_queue.rpush(config['redis']['processing_queue'], msg) cmds += 1 script = 'acn_create_network' spawn_jobarr(script, cmds, mem=config['cluster']['processing_memory'], queue=config['cluster']['queue'], env=config['python']['env_name']) session.close()
def from_database(cls, query_string='SELECT * FROM public.images'): """ This is a constructor that takes the results from an arbitrary query string, uses those as a subquery into a standard polygon overlap query and returns a NetworkCandidateGraph object. By default, an images in the Image table will be used in the outer query. Parameters ---------- query_string : str A valid SQL select statement that targets the Images table Usage ----- Here, we provide usage examples for a few, potentially common use cases. ## Spatial Query This example selects those images that intersect a given bounding polygon. The polygon is specified as a Well Known Text LINESTRING with the first and last points being the same. The query says, select the footprint_latlon (the bounding polygons in the database) that intersect the user provided polygon (the LINESTRING) in the given spatial reference system (SRID), 949900. "SELECT * FROM Images WHERE ST_INTERSECTS(footprint_latlon, ST_Polygon(ST_GeomFromText('LINESTRING(159 10, 159 11, 160 11, 160 10, 159 10)'),949900)) = TRUE" from_database ## Select from a specific orbit This example selects those images that are from a particular orbit. In this case, the regex string pulls all P##_* orbits and creates a graph from them. This method does not guarantee that the graph is fully connected. "SELECT * FROM Images WHERE (split_part(path, '/', 6) ~ 'P[0-9]+_.+') = True" """ composite_query = """WITH i as ({}) SELECT i1.id as i1_id,i1.path as i1_path, i2.id as i2_id, i2.path as i2_path FROM i as i1, i as i2 WHERE ST_INTERSECTS(i1.footprint_latlon, i2.footprint_latlon) = TRUE AND i1.id < i2.id""".format(query_string) session = Session() res = session.execute(composite_query) adjacency = defaultdict(list) adjacency_lookup = {} for r in res: sid, spath, did, dpath = r adjacency_lookup[spath] = sid adjacency_lookup[dpath] = did if spath != dpath: adjacency[spath].append(dpath) session.close() # Add nodes that do not overlap any images obj = cls.from_adjacency(adjacency, node_id_map=adjacency_lookup, config=config) return obj
def matches(self): session = Session() q = session.query(Matches) qf = q.filter(Matches.source == self.source['node_id'], Matches.destination == self.destination['node_id']) odf = pd.read_sql(qf.statement, q.session.bind).set_index('id') df = pd.DataFrame(odf.values, index=odf.index.values, columns=odf.columns.values) df.index.name = 'id' # Explicit close to get the session cleaned up session.close() return DbDataFrame(df, parent=self, name='matches')
def _from_db(self, table_obj): """ Generic database query to pull the row associated with this node from an arbitrary table. We assume that the row id matches the node_id. Parameters ---------- table_obj : object The declared table class (from db.model) """ session = Session() res = session.query(table_obj).\ filter(table_obj.source == self.source['node_id']).\ filter(table_obj.destination == self.destination['node_id']) session.close() return res
def __init__(self, *args, parent=None, **kwargs): # If this is the first time that the image is seen, add it to the DB if parent is None: self.parent = Parent(config) else: self.parent = parent # Create a session to work in session = Session() # For now, just use the PATH to determine if the node/image is in the DB res = session.query(Images).filter( Images.path == kwargs['image_path']).first() exists = False if res: exists = True kwargs['node_id'] = res.id session.close() super(NetworkNode, self).__init__(*args, **kwargs) if exists is False: # Create the camera entry try: self._camera = create_camera(self.geodata) serialized_camera = self._camera.getModelState() cam = Cameras(camera=serialized_camera) except: cam = None kpspath = io_keypoints.create_output_path(self.geodata.file_name) # Create the keypoints entry kps = Keypoints(path=kpspath, nkeypoints=0) # Create the image i = Images(name=kwargs['image_name'], path=kwargs['image_path'], footprint_latlon=self.footprint, cameras=cam, keypoints=kps) session = Session() session.add(i) session.commit() session.close() self.job_status = defaultdict(dict)
def compute_overlaps(self): query = """ SELECT ST_AsEWKB(geom) AS geom FROM ST_Dump(( SELECT ST_Polygonize(the_geom) AS the_geom FROM ( SELECT ST_Union(the_geom) AS the_geom FROM ( SELECT ST_ExteriorRing(footprint_latlon) AS the_geom FROM images) AS lines ) AS noded_lines ) )""" session = Session() oquery = session.query(Overlay) iquery = session.query(Images) rows = [] for q in self._engine.execute(query).fetchall(): overlaps = [] b = bytes(q['geom']) qgeom = shapely.wkb.loads(b) res = iquery.filter( Images.footprint_latlon.ST_Intersects( from_shape(qgeom, srid=949900))) for i in res: fgeom = to_shape(i.footprint_latlon) area = qgeom.intersection(fgeom).area if area < 1e-6: continue overlaps.append(i.id) o = Overlay(geom='SRID=949900;{}'.format(qgeom.wkt), overlaps=overlaps) res = oquery.filter(Overlay.overlaps == o.overlaps).first() if res is None: rows.append(o) session.bulk_save_objects(rows) session.commit() res = oquery.filter(sqlalchemy.func.cardinality(Overlay.overlaps) <= 1) res.delete(synchronize_session=False) session.commit() session.close()
def _from_db(self, table_obj, key='image_id'): """ Generic database query to pull the row associated with this node from an arbitrary table. We assume that the row id matches the node_id. Parameters ---------- table_obj : object The declared table class (from db.model) key : str The name of the column to compare this object's node_id with. For most tables this will be the default, 'image_id' because 'image_id' is the foreign key in the DB. For the Images table (the parent table), the key is simply 'id'. """ if 'node_id' not in self.keys(): return session = Session() res = session.query(table_obj).filter(getattr(table_obj,key) == self['node_id']).first() session.close() return res
def matches(self): session = Session() session.query(Matches).filter(Matches.source == self.source['node_id'], Matches.destination == self.destination['node_id']).delete() session.commit() session.close() return