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