def setup_graph(self): import networkx as nx # Populates a NetworkX Graph object using the edge database table # Arguments: # Returns - a NetworkX Graph object populated with nodes and edges cursor = self.connection.cursor() query = 'SELECT node1, node2, ST_AsTExt(proj1), ST_AsText(proj2), weight ' \ 'FROM edge' cursor.execute(query) G = nx.Graph() for id1, id2, proj1, proj2, weight in cursor: # Vertices need projected coordinates, use ProjNode to get x and y node1 = ProjNode.from_db_string(proj1) node2 = ProjNode.from_db_string(proj2) G.add_edge((node1.x, node1.y), (node2.x, node2.y), weight=weight) # Tag vertices with node id, for later retrieval G.nodes[node1.x, node1.y]['id'] = id1 G.nodes[node2.x, node2.y]['id'] = id2 cursor.close() return G
def test_projnode_from_db_string(projnode3, point2): # Tests ProjNode constructor from_db_string act_node = ProjNode.from_db_string(point2) assert_projnode(projnode3, act_node) return
def flower_beds(self, plant, n): # Gets the n closest flower beds to location which contain plant (optional), sorted by distance from location # Arguments: # n - the number of points of interest to be returned (0 returns all) # Returns - a list of GeoNode objects representing flower beds cursor = self.connection.cursor() # If plant has been passed, looking for n nearest containing plant. If not, just n nearest. if plant: sql = 'SELECT pb.bed_id, ST_Distance(' + self.location.point_string() + ', fb.polygon) AS dist, ' \ 'ST_AsText(ST_Centroid(fb.polygon)) ' \ 'FROM plant_bed pb ' \ 'JOIN flower_bed fb ' \ 'ON pb.bed_id = fb.id ' \ 'WHERE pb.plant_id =' + str(plant) + ' ' \ 'ORDER BY dist' else: sql = 'SELECT id, ST_Distance(' + self.location.point_string() + ', polygon) AS dist, ' \ 'ST_AsText(ST_Centroid(polygon)) ' \ 'FROM flower_bed ' \ 'ORDER BY dist' # Limit query to n rows if required if n != '0': sql += ' LIMIT ' sql += n sql += ';' cursor.execute(sql) flower_beds = [] # Copy flower bed to ProjNode object and append to list # Projected node because result of geometric calculation for row in cursor: bed = ProjNode.from_db_string(row[2]) bed.id = row[0] bed.name = 'Flower Bed ' + str(bed.id) # Will end up back with user, so convert to lat and long flower_beds.append(bed.convert()) cursor.close() return flower_beds
def bed_details(self, bed_id): # Gets full details of the flower bed with id = bed_id # Arguments: # bed_id - id of the flower bed in question # Returns - a GeoNode object representing the centroid of the flower bed polygon sql = 'SELECT ST_AsText(ST_Centroid(polygon)) ' \ 'FROM flower_bed ' \ 'WHERE id = ' + str(bed_id) row = self._execute_query(sql) bed = ProjNode.from_db_string(row[0]) bed.id = bed_id bed.name = 'Flower Bed ' + str(bed_id) # Convert projected coordinates to lat and long and return return bed.convert()