def shp2db(self, srs):
conn = engine.connect()
try:
# Function use the create the topology in the BD
trans = conn.begin()
sql = 'CREATE TABLE public.%s (gid integer, geom geometry(LineString, %s), CONSTRAINT %s_pkey PRIMARY KEY (gid)) ' % (self.projectName, srs, self.projectName)
sql += 'WITH (OIDS=FALSE); ALTER TABLE public.%s OWNER TO postgres;' % self.projectName
conn.execute(sql)
i = 1
last = len(self.list_lines)
sql = 'INSERT INTO public.%s VALUES ' % self.projectName
for line in self.list_lines:
if i == last:
sql += "(%s, ST_SetSRID(ST_GeometryFromText('%s'), %s))" % (i, line, srs)
else:
sql += "(%s, ST_SetSRID(ST_GeometryFromText('%s'), %s)), " % (i, line, srs)
i += 1
conn.execute(sql)
trans.commit()
wx.CallAfter(Publisher().sendMessage, self.callback, None)
except:
print traceback.print_exc()
trans.rollback()
wx.CallAfter(Publisher().sendMessage, "catchError", "An error occured during the grid loading to the DB")
self.stop()
finally:
conn.close()
def cleanPublicSchema(name):
connection = engine.connect()
trans = connection.begin()
try:
connection.execute("DROP TABLE public.%s" %name)
trans.commit()
except:
trans.rollback()
print traceback.print_exc()
finally:
connection.close()
def cleanPublicSchema(name):
connection = engine.connect()
trans = connection.begin()
try:
connection.execute("DROP TABLE public.%s" % name)
trans.commit()
except:
trans.rollback()
print traceback.print_exc()
finally:
connection.close()
def getObstacles(self):
conn = engine.connect()
try:
sql = "SELECT ST_AsText(obstacles.geom) FROM public.obstacles"
obstacles = conn.execute(sql)
for ob in obstacles:
self.obstacles.append(loads(ob[0]))
except:
print traceback.print_exc()
wx.CallAfter(Publisher().sendMessage, "catchError", "An error occured during retrieving the obstacles from the DB")
self.stop()
finally:
conn.close()
def deleteTopology(name):
connection = engine.connect()
trans = connection.begin()
try:
# delete meta
connection.execute("delete from topology.meta where topology_id in (select id from topology.topology where name = '%s')" %name)
# drop topology
connection.execute("select topology.DropTopology('%s')" %name)
trans.commit()
except:
trans.rollback()
print traceback.print_exc()
finally:
connection.close()
def getExtent(self):
conn = engine.connect()
try:
# polygon and obstacles are hard coded because they are always the same
sql = 'SELECT ST_xmin(geom), ST_ymin(geom), ST_xmax(geom), ST_ymax(geom) FROM public.polygon'
bbox = conn.execute(sql)
for b in bbox:
self.xmin, self.ymin, self.xmax, self.ymax = b[0], b[1], b[2], b[3]
except:
print traceback.print_exc()
wx.CallAfter(Publisher().sendMessage, "catchError", "An error occured during the extent extraction")
self.stop()
finally:
conn.close()
def deleteTopology(name):
connection = engine.connect()
trans = connection.begin()
try:
# delete meta
connection.execute(
"delete from topology.meta where topology_id in (select id from topology.topology where name = '%s')"
% name)
# drop topology
connection.execute("select topology.DropTopology('%s')" % name)
trans.commit()
except:
trans.rollback()
print traceback.print_exc()
finally:
connection.close()
def createNetworkGrid(self):
conn = engine.connect()
try:
grid_coords = conn.execute(self.sql_grid_coords)
x = -1
y = -1
list_x = list()
self.list_lines = list()
for point in grid_coords:
p = loads(point[0])
if p.to_wkt() not in self.points_within_obstacles:
if p.x != x:
# initialiaze new column
x = p.x
y = p.y
left_point = Point(x - self.resolution, y).to_wkt()
list_x.append(p.to_wkt())
# add horizontal line at the left
if left_point in list_x and left_point not in self.points_within_obstacles:
line = LineString([(x - self.resolution, y), (x, y)])
# if the line created does not intersects the obstacle
# can happen often for a low resolution or if obstacles are lines
if not self.intersectsObstacles(line):
self.list_lines.append(line)
elif p.x == x:
dist = euclidean_distance(x, y, p.x, p.y)
left_point = Point(x - self.resolution, p.y).to_wkt()
# add vertical line at the bottom
if dist == self.resolution:
line = LineString([(x, y), (x, p.y)])
if not self.intersectsObstacles(line):
self.list_lines.append(line)
# add horizontal line at the left
if left_point in list_x and left_point not in self.points_within_obstacles:
line = LineString([(x - self.resolution, p.y), (x, p.y)])
if not self.intersectsObstacles(line):
self.list_lines.append(line)
list_x.append(p.to_wkt())
y = p.y
wx.CallAfter(Publisher().sendMessage, "consoleAppend", 'The grid network has been created')
except:
print traceback.print_exc()
wx.CallAfter(Publisher().sendMessage, "catchError", "An error occured during the grid creation")
self.stop()
finally:
del self.sql_grid_coords
conn.close()
def getUnit(srs):
conn = engine.connect()
trans = conn.begin()
try:
sql = 'SELECT proj4text FROM public.spatial_ref_sys '
sql += 'WHERE auth_srid = %s' %srs
res = conn.execute(sql)
proj4text = res.fetchone()
unit = re.search(r'.*units=(.*)\s\+.*', proj4text[0])
if unit is not None:
return unit.group(1)
else:
return degreeUnit
except:
print traceback.print_exc()
finally:
conn.close()
def getUnit(srs):
conn = engine.connect()
trans = conn.begin()
try:
sql = 'SELECT proj4text FROM public.spatial_ref_sys '
sql += 'WHERE auth_srid = %s' % srs
res = conn.execute(sql)
proj4text = res.fetchone()
unit = re.search(r'.*units=(.*)\s\+.*', proj4text[0])
if unit is not None:
return unit.group(1)
else:
return degreeUnit
except:
print traceback.print_exc()
finally:
conn.close()
def intersect(self):
wx.CallAfter(Publisher().sendMessage, "consoleAppend", 'Start intersecting the grid with the polygon...')
try:
sql = "SELECT ST_ASText(grid.points), ST_X(grid.points) as x, ST_Y(grid.points) as y FROM (SELECT unnest(array["
last = len(self.grid.coordinates)
i = 1
for coord in self.grid.coordinates:
sql += "ST_GeomFromText('%s', %s), " % (coord, self.srs)
if i == last:
sql = sql + "ST_GeomFromText('%s', %s)]) AS points) AS grid, public.polygon " % (coord, self.srs)
i += 1
sql += "WHERE ST_Intersects(polygon.geom, grid.points) "
sql += "ORDER BY x,y"
self.sql_grid_coords = sql
# If obstacle are defined -> remove points intersecting the obstacles
if self.obstacle_within:
wx.CallAfter(Publisher().sendMessage, "consoleAppend", 'Start intersecting the grid with the obstacles...')
sql = "SELECT ST_ASText(grid.points) FROM (SELECT unnest(array["
i = 1
for coord in self.grid.coordinates:
sql += "ST_GeomFromText('%s', %s), " % (coord, self.srs)
if i == last:
sql = sql + "ST_GeomFromText('%s', %s)]) AS points) AS grid, public.obstacles " % (coord, self.srs)
i += 1
sql += "WHERE ST_Intersects(obstacles.geom, grid.points) "
conn = engine.connect()
pts_obstacles_coords = conn.execute(sql)
for point in pts_obstacles_coords:
self.points_within_obstacles.append(loads(point[0]).to_wkt())
wx.CallAfter(Publisher().sendMessage, "consoleAppend", "Intersection of points with polygon(s) and obstacle(s) done...")
else:
wx.CallAfter(Publisher().sendMessage, "consoleAppend", "Intersection of points with polygon(s) done...")
except:
print traceback.print_exc()
wx.CallAfter(Publisher().sendMessage, "catchError", "An error occured during the intersection of the grid points with the polygon and/or obstacles")
self.stop()
finally:
if self.obstacle_within: conn.close()