Exemplo n.º 1
0
 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()
Exemplo n.º 2
0
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()
Exemplo n.º 3
0
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()
Exemplo n.º 4
0
 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() 
Exemplo n.º 5
0
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()
Exemplo n.º 6
0
 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()
Exemplo n.º 7
0
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()
Exemplo n.º 8
0
 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()
Exemplo n.º 9
0
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()
Exemplo n.º 10
0
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()
Exemplo n.º 11
0
    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()