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