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 test_projnode_constructor(projnode1):
# Trivial test for classic constructor
act_node = ProjNode(1, 'Node 1', 480075.52727804193, 155325.20673843563)
assert_projnode(projnode1, act_node)
return
def test_projnode_from_db_row(projnode1):
# Tests ProjNode constructor from_db_row
db_row = (1, 'POINT(480075.52727804193 155325.20673843563)', 'Node 1')
act_node = ProjNode.from_db_row(db_row)
assert_projnode(projnode1, 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 nearest_node(self):
# Finds the node nearest to location
# Arguments:
# Returns - the ProjNode closest to location
sql = 'SELECT id, ST_AsText(proj_coord), name, ST_Distance(' + self.location.point_string() + ', proj_coord) ' \
'AS dist ' \
'FROM node ' \
'ORDER BY dist ' \
'LIMIT 1;'
row = self._execute_query(sql)
return ProjNode.from_db_row(row)
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()
def bed_nearest_node(self, bed_id):
# Finds the id of the closest node to bed_id
# Arguments:
# bed_id - the id of the flower bed
# Returns:
# The ProjNode nearest to the flower bed
sql = 'SELECT f.nearest_node, ST_AsText(n.proj_coord), n.name ' \
'FROM flower_bed AS f ' \
'JOIN node AS n ' \
'ON f.nearest_node = n.id ' \
'WHERE f.id = ' + str(bed_id) + ';'
row = self._execute_query(sql)
if not row:
#If place doesn't exist, raise custom exception
raise BedNotFound('Flower bed ' + str(bed_id) + ' not found')
return ProjNode.from_db_row(row)
def place_nearest_node(self, place_id):
# Finds the id of the closest node to place_id
# Arguments:
# place_id - the id of the place
# Returns:
# ProjNode closest to the place
sql = 'SELECT p.nearest_node, ST_AsText(n.proj_coord), n.name ' \
'FROM place AS p ' \
'JOIN node AS n ' \
'ON p.nearest_node = n.id ' \
'WHERE p.id = ' + str(place_id) + ';'
row = self._execute_query(sql)
if not row:
#If place doesn't exist, raise custom exception
raise PlaceNotFound('Place ' + str(place_id) + ' not found')
return ProjNode.from_db_row(row)
def projnode3():
# Node 12
return ProjNode(12, 'Node 12', 480153.12022010185, 155351.79401970515)
def projnode2():
# Node 1
return ProjNode(1, 'Node 1', 480075.52727804193, 155325.20673843563)
def projnode1():
# Place 2
return ProjNode(0, '', 480060.6195600935, 155300.5319708603)
def projnode3():
return ProjNode(0, '', 480075.52727804193, 155325.20673843563)
def projnode2():
return ProjNode(2, 'Node 2', 480075.52727804193, 155325.20673843563)
