예제 #1
0
 def encode_feature(self, feature):
     if hasattr(self.datasource, 'srid_out') and self.datasource.srid_out is not None:
         srs = self.datasource.srid_out
     else:
         if hasattr(feature, "geometry_attr"):
             srs = str(feature.srs);
             if 'EPSG' in srs:
                 srs = srs[5:]
         else:
             srs = 4326
     
     wkt = "GeomFromText('" + WKT.to_wkt(feature.geometry) + "', %i)" % int(srs)
     
     sql = "INSERT INTO featureserver (fid, "
     
     for key, value in feature.properties.items():
         if key != "geometry":
             sql += "%s, " % key
     sql += "geometry" 
     sql += ") VALUES ('%s', " % self.escapeSQL(str(feature.id).encode('utf-8'))
     
     for key, value in feature.properties.items():
         #key = self.getFormatedAttributName(key)
         if value == None:
             sql += "null, "
         else:
             sql += "'" + self.escapeSQL(value.encode('utf-8')) + "', "
     sql += wkt
     sql += ");"
     
     self._cursor.execute(sql)
     
예제 #2
0
    def insert(self, action):
        self.begin()
        if action.feature != None:
            feature = action.feature
            columns = ", ".join(self.column_names(feature) + [self.geom_col])
            values = ", ".join(
                self.value_formats(feature) + [
                    "ST_SetSRID('%s'::geometry, %s) " %
                    (WKT.to_wkt(feature.geometry), self.srid)
                ])

            sql = "INSERT INTO \"%s\" (%s) VALUES (%s)" % (self.table, columns,
                                                           values)

            cursor = self.db.cursor()
            cursor.execute(str(sql), self.feature_values(feature))

            cursor.execute("SELECT currval('%s');" % self.id_sequence())
            action.id = cursor.fetchone()[0]

            return InsertResult(action.id, "")

        elif action.wfsrequest != None:
            sql = action.wfsrequest.getStatement(self)

            cursor = self.db.cursor()
            cursor.execute(str(sql))

            cursor.execute("SELECT currval('%s');" % self.id_sequence())
            action.id = cursor.fetchone()[0]

            return InsertResult(action.id, "")

        return None
예제 #3
0
    def encode_feature(self, feature):
        if hasattr(self.datasource,
                   'srid_out') and self.datasource.srid_out is not None:
            srs = self.datasource.srid_out
        else:
            if hasattr(feature, "geometry_attr"):
                srs = str(feature.srs)
                if 'EPSG' in srs:
                    srs = srs[5:]
            else:
                srs = 4326

        wkt = "GeomFromText('" + WKT.to_wkt(
            feature.geometry) + "', %i)" % int(srs)

        sql = "INSERT INTO featureserver ("

        for key, value in feature.properties.items():
            if key != "geometry":
                sql += "%s, " % key
        sql += "geometry"
        sql += ") VALUES ("

        for key, value in feature.properties.items():
            #key = self.getFormatedAttributName(key)
            if value == None:
                sql += "null, "
            else:
                sql += "'" + self.escapeSQL(value.encode('utf-8')) + "', "
        sql += wkt
        sql += ");"

        self._cursor.execute(sql)
예제 #4
0
def wkt_to_geojson(wktStr):
    """Return GeoJSON from a WKT string."""
    # Create instances of vectorfeatures WKT/GeoJSON Classes
    inWKT = WKT.WKT()
    outGeoJSON = GeoJSON.GeoJSON()
    # Decode WKT to vectorformats features, and re-encode as GeoJSON
    return outGeoJSON.encode(inWKT.decode(wktStr), to_string=True)
예제 #5
0
    def insert(self, action, response=None):
        self.begin()
        if action.feature != None:
            feature = action.feature
            columns = ", ".join(self.column_names(feature) + [self.geom_col])
            values = ", ".join(
                self.value_formats(feature)
                + ["SetSRID('%s'::geometry, %s) " % (WKT.to_wkt(feature.geometry), self.srid)]
            )
            sql = 'INSERT INTO "%s" (%s) VALUES (%s)' % (self.table, columns, values)
            cursor = self.db.cursor()
            cursor.execute(str(sql), self.feature_values(feature))
            cursor.execute("SELECT currval('%s');" % self.id_sequence())
            action.id = cursor.fetchone()[0]
            self.db.commit()
            return self.select(action)

        elif action.wfsrequest != None:
            sql = action.wfsrequest.getStatement(self)

            cursor = self.db.cursor()
            cursor.execute(str(sql))

            cursor.execute("SELECT currval('%s');" % self.id_sequence())
            action.id = cursor.fetchone()[0]
            self.db.commit()

            response.addInsertResult(ActionResult(action.id, ""))
            response.getSummary().increaseInserted()

            return self.select(action)

        return None
예제 #6
0
 def update (self, action):
     model = __import__(self.model, fromlist=['*'])
     cls = getattr(model, self.cls)
     feature = action.feature
     obj = self.session.query(cls).get(int(action.id))
     for prop in feature.properties.keys():
         setattr(obj, prop, feature.properties[prop])
     if self.geom_rel and self.geom_cls:
         geom_obj = getattr(obj, self.geom_rel)
         setattr(geom_obj, self.geom_col, WKT.to_wkt(feature.geometry))
         self.session.add(geom_obj)
     elif feature.geometry:
         setattr(obj, self.geom_col, WKT.to_wkt(feature.geometry))
     else:
         pass
     self.session.add(obj)
     return self.select(action)
예제 #7
0
 def update(self, action):
     model = __import__(self.model, fromlist=['*'])
     cls = getattr(model, self.cls)
     feature = action.feature
     obj = self.session.query(cls).get(int(action.id))
     for prop in feature.properties.keys():
         setattr(obj, prop, feature.properties[prop])
     if self.geom_rel and self.geom_cls:
         geom_obj = getattr(obj, self.geom_rel)
         setattr(geom_obj, self.geom_col, WKT.to_wkt(feature.geometry))
         self.session.add(geom_obj)
     elif feature.geometry:
         setattr(obj, self.geom_col, WKT.to_wkt(feature.geometry))
     else:
         pass
     self.session.add(obj)
     return self.select(action)
예제 #8
0
 def feature_predicates (self, feature):
     columns = self.column_names(feature)
     values  = self.value_formats(feature)
     predicates = []
     for pair in zip(columns, values):
         if pair[0] != self.geom_col:
             predicates.append(" %s = %s" % pair)
         else:
             predicates.append(" %s = %s " % (self.geom_col, WKT.to_wkt(feature.geometry)))
     return predicates
예제 #9
0
 def update (self, action):
     feature = action.feature
     sql = """UPDATE %s SET %s = SetSRID(%%(geom)s::geometry, %s),
                            attrs = %%(attrs)s WHERE %s = %(id)d""" % (
             self.table, self.geom_col, self.srid, self.fid_col )
     values = {'geom' : WKT.to_wkt(feature.geometry),
               'id'   : action.id,
               'attrs': self._serializeattrs(feature.properties)}
     cursor = self.db.cursor()
     cursor.execute(str(sql), values)
     return self.select(action)
예제 #10
0
 def create (self, action):
     feature = action.feature
     values = {'geom' : WKT.to_wkt(feature.geometry),
               'uuid' : uuid.uuid1().hex,
               'attrs': self._serializeattrs(feature.properties)}
     sql = """INSERT INTO %s (%s, uuid, attrs)
                 VALUES (SetSRID(%%(geom)s::geometry, %s),
                             %%(uuid)s, %%(attrs)s)""" % (
                                 self.table, self.geom_col, self.srid)
     cursor = self.db.cursor()
     cursor.execute(str(sql), values)
     return {}
예제 #11
0
    def select (self, action):
        cursor = self.db.cursor()

        if action.id is not None:
            sql = "SELECT AsText(%s) as fs_binary_geom_col, * FROM %s WHERE %s = %%(%s)d" % (
                    self.geom_col, self.table, self.fid_col, self.fid_col )
            cursor.execute(str(sql), {self.fid_col: action.id})
            result = [cursor.fetchone()]
        else:
            filters = []
            attrs   = {}
            if action.bbox:
                filters.append( "%s && SetSRID('BOX3D(%f %f,%f %f)'::box3d, %s) and intersects(%s, SetSRID('BOX3D(%f %f,%f %f)'::box3d, %s))" % (
                                        (self.geom_col,) + tuple(action.bbox) + (self.srid,) + (self.geom_col,) + (tuple(action.bbox) + (self.srid,))))
            
            if action.attributes:
                match = Feature(props = action.attributes)
                filters = self.feature_predicates(match)
                attrs = action.attributes

            sql = "SELECT AsText(%s) as fs_binary_geom_col, uuid, id, attrs FROM %s" % (self.geom_col, self.table)
            #if filters:
            #    sql += " WHERE " + " AND ".join(filters)
            
            if self.order:
                sql += self.order
            if action.maxfeatures:
                sql += " LIMIT %d" % action.maxfeatures
            else:   
                sql += " LIMIT 1000"
            if action.startfeature:
                sql += " OFFSET %d" % action.startfeature
            
            cursor.execute(str(sql), attrs)
            result = cursor.fetchall() # should use fetchmany(action.maxfeatures)

        columns = [desc[0] for desc in cursor.description]
        features = []
        for row in result:
            props = dict(zip(columns, row))
            geom  = WKT.from_wkt(props['fs_binary_geom_col'])
            if props.has_key(self.geom_col): del props[self.geom_col]
            del props['fs_binary_geom_col']
            props.update(self._deserializeattrs(props['attrs']))
            del props['attrs']
            fid = props[self.fid_col]
            del props[self.fid_col]
            for key, value in props.items():
                if isinstance(value, str): 
                    props[key] = unicode(value, "utf-8")
            features.append( Feature( fid, geom, props ) ) 
        return features
예제 #12
0
 def insert(self, action):
     model = __import__(self.model, fromlist=['*'])
     cls = getattr(model, self.cls)
     feature = action.feature
     obj = cls()
     for prop in feature.properties.keys():
         setattr(obj, prop, feature.properties[prop])
     if self.geom_rel and self.geom_cls:
         geom_cls = getattr(model, self.geom_cls)
         geom_obj = geom_cls()
         setattr(geom_obj, self.geom_col, WKT.to_wkt(feature.geometry))
         try:
             getattr(obj, self.geom_rel).append(geom_obj)
         except:
             # Handle specific exception
             setattr(obj, self.geom_rel, geom_obj)
         self.session.add(geom_obj)
     elif feature.geometry:
         setattr(obj, self.geom_col, WKT.to_wkt(feature.geometry))
     else:
         pass
     self.session.add(obj)
     return self.select(action)
예제 #13
0
 def create (self, action):
     model = __import__(self.model, fromlist=['*'])
     cls = getattr(model, self.cls)
     feature = action.feature
     obj =  cls()
     for prop in feature.properties.keys():
         setattr(obj, prop, feature.properties[prop])
     if self.geom_rel and self.geom_cls:
         geom_cls = getattr(model, self.geom_cls)
         geom_obj = geom_cls()
         setattr(geom_obj, self.geom_col, WKT.to_wkt(feature.geometry))
         try:
             getattr(obj, self.geom_rel).append(geom_obj)
         except:
             # Handle specific exception
             setattr(obj, self.geom_rel, geom_obj)
         self.session.add(geom_obj)
     elif feature.geometry:
         setattr(obj, self.geom_col, WKT.to_wkt(feature.geometry))
     else:
         pass
     self.session.add(obj)
     return self.select(action)
예제 #14
0
 def create(self, action):
     feature = action.feature
     columns = ", ".join(self.column_names(feature) + [self.geom_col])
     values = ", ".join(
         self.value_formats(feature) + [
             "SetSRID('%s'::geometry, %s) " %
             (WKT.to_wkt(feature.geometry), self.srid)
         ])
     sql = "INSERT INTO \"%s\" (%s) VALUES (%s)" % (self.table, columns,
                                                    values)
     cursor = self.db.cursor()
     cursor.execute(str(sql), self.feature_values(feature))
     cursor.execute("SELECT currval('%s');" % self.id_sequence())
     action.id = cursor.fetchone()[0]
     self.db.commit()
     return self.select(action)
예제 #15
0
 def feature_predicates (self, feature):
     columns = self.column_names(feature)
     values  = self.value_formats(feature)
     predicates = []
     for pair in zip(columns, values):
         if pair[0] != self.geom_col:
             if isinstance(pair[1], dict):
                 # Special Query: pair[0] is 'a', pair[1] is {'type', 'pred', 'value'}
                 # We build a Predicate here, then we replace pair[1] with pair[1] value below
                 if pair[1].has_key('value'):
                     predicates.append("%s %s %s" % (pair[0], 
                                                     self.query_action_sql[pair[1]['type']],
                                                     pair[1]['pred']))
             else:
                 predicates.append("%s = %s" % pair)
     if feature.geometry.has_key("coordinates"):
         predicates.append(" %s = SetSRID('%s'::geometry, %s) " % (self.geom_col, WKT.to_wkt(feature.geometry), self.srid))     
     return predicates
예제 #16
0
    def insert (self, action):
        feature = action.feature
        bbox = feature.get_bbox()

        columns = ", ".join([self.geom_col,'xmin,ymin,xmax,ymax'])
        values = [WKT.to_wkt(feature.geometry)] + list(bbox) 
        sql = "INSERT INTO \"%s\" (%s) VALUES (?,?,?,?,?)" % ( self.table, columns)
        cursor = self.db.cursor()
        res = cursor.execute(str(sql), values)
        action.id = res.lastrowid
        #self.db.commit()

        insert_tuples = [(res.lastrowid, k, v) for k,v in feature.properties.items()]
        sql = "INSERT INTO \"%s_attrs\" (feature_id, key, value) VALUES (?, ?, ?)" % (self.table,) 
        cursor.executemany(sql,insert_tuples)

        #self.db.commit()
        return self.select(action)
예제 #17
0
    def feature_predicates (self, feature):
        columns = self.column_names(feature)
        values  = self.value_formats(feature)
        predicates = []
        for pair in zip(columns, values):
            if pair[0] != self.geom_col:
                if isinstance(pair[1], dict):
                    # Special Query: pair[0] is 'a', pair[1] is {'type', 'pred', 'value'}
                    # We build a Predicate here, then we replace pair[1] with pair[1] value below
                    if pair[1].has_key('value'):
                        predicates.append("%s %s %s" % (pair[1]['column'], 
                                                        self.query_action_sql[pair[1]['type']],
                                                        pair[1]['pred']))

                else:
                    predicates.append("%s = %s" % pair)
        if feature.geometry and feature.geometry.has_key("coordinates"):
            predicates.append(" %s = SetSRID('%s'::geometry, %s) " % (self.geom_col, WKT.to_wkt(feature.geometry), self.srid))
        return predicates
예제 #18
0
    def update (self, action):
        feature = action.feature
        bbox = feature.get_bbox()
        predicates =  self.feature_predicates(feature) 

        # this assumes updates can not introduce new attrs.... fix?
        sql = "UPDATE \"%s_attrs\" SET value = :value WHERE key = :key AND %s = %d" % (
                    self.table, self.fid_col, action.id )

        cursor = self.db.cursor()
        predicate_list = []
        for i in range(0, len(predicates) - 1, 2):
            predicate_list.append( dict(key=predicates[i], value=predicates[i+1]) )

        cursor.executemany(str(sql), predicate_list)

        # should check if changed before doing this ...
        geom_sql = "UPDATE %s SET %s = ?, xmin = ?, ymin = ?, xmax = ?, ymax = ? WHERE %s = %d" \
                           % (self.table, self.geom_col, self.fid_col, action.id)
        cursor.execute(geom_sql,  [WKT.to_wkt(feature.geometry)] + list(bbox))

        #self.db.commit()
        return self.select(action)
예제 #19
0
    def select (self, action):
        if not hasattr (action, 'parameters') or not action.parameters.has_key('area_filter'):
            raise ApplicationException("Missing spatial area filter, supply a Well Known Text geometry for 'area_filter'")
        else:
            cursor = self.db.cursor()

            # validate input WKT string
            if not action.parameters['area_filter'].find ('POLYGON ('):
                raise ApplicationException ("Invalid spatial filter, should be Well Known Text respresentation of a Polygon") 

            sql  = "SELECT %s AS id, ST_AsText(%s) as fs_text_geom, %s FROM \"%s\"" % (self.fid_col, self.geom_col, self.attribute_cols, self.table)
            sql += "('%s')" % action.parameters['area_filter']

            try:
                cursor.execute(str(sql))
            except Exception, e:
                raise ApplicationException ("<em>Internal error:</em><br><pre>%s</pre>" % (str (e)))

            result = cursor.fetchall()

            # column names from cursor
            columns = [desc[0] for desc in cursor.description]

            # output features to return
            features = []

            for row in result:
                if not row: continue

                # convert result set row record into dictionary
                props = dict(zip(columns, row))

                # skip records with no geometry
                if not props['fs_text_geom']: continue

                geom = WKT.from_wkt(props['fs_text_geom'])

                # add feature id column and remove it from result set dictionary
                id = props[self.fid_col]
                del props[self.fid_col]

                # all attribute columns, including geometry have been selected, remove the duplicate
                if self.attribute_cols == '*':
                    del props[self.geom_col]

                # no need for it anymore, geom is already been set
                del props['fs_text_geom']

                # attribute data for feature
                data = {}

                for key, value in props.items():
                    data[key] = value     

                props.clear()

                # go through data fields and format according to type
                for key, value in data.items():
                    if isinstance(value, str): 
                        data[key] = unicode(value, self.encoding)
                    elif isinstance(value, datetime.datetime) or isinstance(value, datetime.date):
                        # stringify datetimes 
                        data[key] = str(value)
                        
                    try:
                        if isinstance(value, decimal.Decimal):
                            data[key] = unicode(str(value), self.encoding)
                    except:
                        pass

                if (geom):
                    features.append( Feature( id, geom, self.geom_col, self.srid_out, data ) ) 

            return features
예제 #20
0
    def select(self, action):
        cursor = self.db.cursor()
        features = []
        sql_attrs = "SELECT key, value FROM \"%s_attrs\" WHERE feature_id = :feature_id" % (
            self.table, )
        selection_dict = {}

        if action.id is not None:
            sql = "SELECT * FROM \"%s\" WHERE %s = ?" % (self.table,
                                                         self.fid_col)
            cursor.execute(str(sql), (action.id, ))
            results = [cursor.fetchone()]

        else:
            match = Feature(props=action.attributes)
            filters = match.properties.items()

            sql = "SELECT DISTINCT(t.feature_id) as feature_id, t.%s as %s,\
            t.%s as %s FROM \"%s\" t LEFT JOIN \"%s_attrs\" a ON a.feature_id =\
            t.feature_id " % (self.geom_col, self.geom_col, self.fid_col,
                              self.fid_col, self.table, self.table)
            select_dict = {}
            if filters:
                sql += "WHERE 1 "
                for ii, (key, value) in enumerate(filters):
                    select_dict['key%i' % ii] = key
                    if isinstance(value, dict):

                        select_dict['value%i' % ii] = value['value']
                        sql += (" AND a.key = :key%i AND a.value " +
                                self.query_action_sql[value['type']] +
                                " :value%i") % (ii, ii)

                    else:
                        select_dict['value%i' % ii] = value
                        sql += " AND a.key = :key%i AND a.value = :value%i" % (
                            ii, ii)

            bbox = ''
            if action.bbox:
                # skip sql interpolation as these are from calculation.
                bbox = " AND %f   > t.xmin \
                     AND t.xmax > %f \
                     AND %f   > t.ymin \
                     AND t.ymax >  %f "\
                     % (action.bbox[2], action.bbox[0], action.bbox[3], action.bbox[1])

            sql += bbox
            sql += self.order or ''
            sql += " LIMIT %d" % (action.maxfeatures or 1000, )

            if action.startfeature:
                sql += " OFFSET %d" % action.startfeature
            cursor.execute(str(sql), select_dict)
            results = cursor.fetchall()

        for row in results:
            attrs = cursor.execute(
                sql_attrs, dict(feature_id=row['feature_id'])).fetchall()
            d = {}
            #if attrs == []: continue
            for attr in attrs:
                d[attr[0]] = attr[1]
            geom = WKT.from_wkt(row[self.geom_col])
            id = row[self.fid_col]

            if (geom):
                features.append(Feature(id, geom, d))
        return features
예제 #21
0
    def select (self, action):
        cursor = self.db.cursor()
        features = []
        sql_attrs = "SELECT key, value FROM \"%s_attrs\" WHERE feature_id = :feature_id" % (self.table,)
        selection_dict = {}

        if action.id is not None:
            sql = "SELECT * FROM \"%s\" WHERE %s = ?" % ( self.table,  self.fid_col)
            cursor.execute(str(sql), (action.id,))
            results = [ cursor.fetchone() ]

        else:
            match = Feature(props = action.attributes)
            filters = match.properties.items()
            
            sql = "SELECT DISTINCT(t.feature_id) as feature_id, t.%s as %s,\
            t.%s as %s FROM \"%s\" t LEFT JOIN \"%s_attrs\" a ON a.feature_id =\
            t.feature_id " % ( self.geom_col, self.geom_col, self.fid_col, self.fid_col,  self.table, self.table )
            select_dict = {}
            if filters:
                sql += "WHERE 1 "
                for ii, (key, value) in enumerate(filters):
                    if isinstance(value, dict):

                        select_dict['key%i' % ii] = value['column']
                        select_dict['value%i' % ii] = value['value']
                        sql += (" AND a.key = :key%i AND a.value " + self.query_action_sql[value['type']] + " :value%i") % (ii, ii)


                    else:
                        select_dict['key%i' % ii] = key
                        select_dict['value%i' % ii] = value
                        sql += " AND a.key = :key%i AND a.value = :value%i" % (ii, ii)

            bbox = '' 
            if action.bbox:
                # skip sql interpolation as these are from calculation.
                bbox = " AND %f   > t.xmin \
                     AND t.xmax > %f \
                     AND %f   > t.ymin \
                     AND t.ymax >  %f "\
                     % (action.bbox[2], action.bbox[0], action.bbox[3], action.bbox[1])

            sql += bbox
            sql += self.order or ''
            sql += " LIMIT %d" % (action.maxfeatures or 1000, )

            if action.startfeature:
                sql += " OFFSET %d" % action.startfeature
            cursor.execute(str(sql), select_dict)
            results = cursor.fetchall()

        for row in results:
            attrs = cursor.execute(sql_attrs, dict(feature_id=row['feature_id']) ).fetchall()
            d = {}
            #if attrs == []: continue
            for attr in attrs:
                d[attr[0]] = attr[1]
            geom  = WKT.from_wkt(row[self.geom_col])
            id = row[self.fid_col]

            if (geom):
                features.append( Feature( id, geom, self.geom_col, self.srid_out, d ) ) 
        return features
예제 #22
0
    def select(self, action):
        model = __import__(self.model, fromlist=['*'])
        cls = getattr(model, self.cls)
        geom_cls = None
        if self.geom_cls:
            geom_cls = getattr(model, self.geom_cls)
        if action.id is not None:
            result = [self.session.query(cls).get(action.id)]
        else:
            if self.geom_rel and self.geom_cls:
                main_table = cls.__tablename__
                geom_table = geom_cls.__tablename__
                join_condition = self.join_condition or "%s.%s_id=%s.id" % (
                    main_table, geom_table, geom_table)
                query = self.session.query(cls,
                                           geom_cls).filter(join_condition)
            else:
                query = self.session.query(cls)
            if action.attributes:
                query = query.filter(
                    and_(*[
                        self.feature_predicate(v['column'], v['type'],
                                               v['value'])
                        for k, v in action.attributes.iteritems()
                    ]))
            if action.bbox:
                if self.geom_rel and self.geom_cls:
                    geom_element = getattr(geom_cls, self.geom_col)
                else:
                    geom_element = getattr(cls, self.geom_col)
                query = query.filter(
                    geom_element.intersects(
                        self.session.scalar(
                            func.GeomFromText(self.bbox2wkt(action.bbox),
                                              self.srid))))
            if self.order:
                query = query.order_by(getattr(cls, self.order))
            if action.maxfeatures:
                query.limit(action.maxfeatures)
            else:
                query.limit(1000)
            if action.startfeature:
                query.offset(action.startfeature)
            result = query.all()

        features = []
        for row_tuple in result:
            props = {}
            id = None
            geom = None
            if not isinstance(row_tuple, (tuple, list, dict, set)):
                row_tuple = (row_tuple, )
            for row in row_tuple:
                if isinstance(row, cls):
                    cols = cls.__table__.c.keys()
                    for col in cols:
                        if col == self.fid_col:
                            id = getattr(row, col)
                        elif col == self.geom_col:
                            geom = WKT.from_wkt(
                                self.session.scalar(getattr(row, col).wkt))
                        else:
                            if self.attribute_cols == '*' or col in self.attribute_cols:
                                props[col] = getattr(row, col)
                elif isinstance(row, geom_cls) and geom_cls:
                    cols = geom_cls.__table__.c.keys()
                    for col in cols:
                        if col == self.fid_col:
                            pass
                        elif col == self.geom_col:
                            geom = WKT.from_wkt(
                                self.session.scalar(getattr(row, col).wkt))
                        else:
                            if self.attribute_cols == '*' or col in self.attribute_cols:
                                props[col] = getattr(row, col)
                else:
                    continue

            for key, value in props.items():
                if isinstance(value, str):
                    props[key] = unicode(value, self.encoding)
                elif isinstance(value, datetime.datetime) or isinstance(
                        value, datetime.date):
                    # stringify datetimes
                    props[key] = str(value)

                try:
                    if isinstance(value, decimal.Decimal):
                        props[key] = unicode(str(value), self.encoding)
                except:
                    pass

            if (geom):
                features.append(
                    Feature(id=id,
                            geometry=geom,
                            geometry_attr=self.geom_col,
                            srs=self.srid_out,
                            props=props))
        return features
예제 #23
0
class PostGIS(DataSource):
    """PostGIS datasource. Setting up the table is beyond the scope of
       FeatureServer."""

    query_action_types = ['lt', 'gt', 'ilike', 'like', 'gte', 'lte']

    query_action_sql = {
        'lt': '<',
        'gt': '>',
        'ilike': 'ilike',
        'like': 'like',
        'gte': '>=',
        'lte': '<=',
        'eq': '='
    }

    def __init__(self,
                 name,
                 srid=4326,
                 srid_out=4326,
                 fid="gid",
                 geometry="the_geom",
                 fe_attributes='true',
                 order="",
                 attribute_cols='*',
                 writable=True,
                 encoding="utf-8",
                 hstore='false',
                 hstore_attr="",
                 **args):
        DataSource.__init__(self, name, **args)
        self.table = args["layer"]
        self.fid_col = fid
        self.encoding = encoding
        self.geom_col = geometry
        self.order = order
        self.srid = srid
        self.srid_out = srid_out
        self.db = None
        self.dsn = args["dsn"]
        self.writable = writable
        self.attribute_cols = attribute_cols

        self.fe_attributes = True
        if fe_attributes.lower() == 'false':
            self.fe_attributes = False

        if hstore.lower() == 'true':
            self.hstore = True
            self.hstoreAttribute = hstore_attr
        else:
            self.hstore = False
            self.hstoreAttribute = ""

    def begin(self):
        try:
            self.db = psycopg.connect(self.dsn)
        except Exception as e:
            raise ConnectionException(
                **{
                    'dump': str(e),
                    'layer': self.name,
                    'locator': 'PostGIS',
                    'code': e.pgcode
                })

    def commit(self):
        if self.writable:
            self.db.commit()
        self.db.close()

    def rollback(self):
        if self.writable:
            self.db.rollback()
        self.db.close()

    def column_names(self, feature):
        return feature.properties.keys()

    def value_formats(self, feature):
        values = ["%%(%s)s" % self.geom_col]
        values = []
        for key, val in feature.properties.items():
            valtype = type(val).__name__
            if valtype == "dict":
                val['pred'] = "%%(%s)s" % (key, )
                values.append(val)
            else:
                fmt = "%%(%s)s" % (key, )
                values.append(fmt)
        return values

    def feature_predicates(self, feature):
        columns = self.column_names(feature)
        values = self.value_formats(feature)
        predicates = []
        for pair in zip(columns, values):
            if pair[0] != self.geom_col:
                if isinstance(pair[1], dict):
                    # Special Query: pair[0] is 'a', pair[1] is {'type', 'pred', 'value'}
                    # We build a Predicate here, then we replace pair[1] with pair[1] value below
                    if pair[1].has_key('value'):
                        predicates.append(
                            "%s %s %s" %
                            (pair[1]['column'],
                             self.query_action_sql[pair[1]['type']],
                             pair[1]['pred']))

                else:
                    predicates.append("%s = %s" % pair)
        if feature.geometry and feature.geometry.has_key("coordinates"):
            predicates.append(
                " %s = ST_SetSRID('%s'::geometry, %s) " %
                (self.geom_col, WKT.to_wkt(feature.geometry), self.srid))
        return predicates

    def feature_values(self, feature):
        props = copy.deepcopy(feature.properties)
        for key, val in props.iteritems():
            if type(val) is unicode:  ### b/c psycopg1 doesn't quote unicode
                props[key] = val.encode(self.encoding)
            if type(val) is dict:
                props[key] = val['value']
        return props

    def id_sequence(self):
        return self.table + "_" + self.fid_col + "_seq"

    def insert(self, action):
        self.begin()
        if action.feature != None:
            feature = action.feature
            columns = ", ".join(self.column_names(feature) + [self.geom_col])
            values = ", ".join(
                self.value_formats(feature) + [
                    "ST_SetSRID('%s'::geometry, %s) " %
                    (WKT.to_wkt(feature.geometry), self.srid)
                ])

            sql = "INSERT INTO \"%s\" (%s) VALUES (%s)" % (self.table, columns,
                                                           values)

            cursor = self.db.cursor()
            cursor.execute(str(sql), self.feature_values(feature))

            cursor.execute("SELECT currval('%s');" % self.id_sequence())
            action.id = cursor.fetchone()[0]

            return InsertResult(action.id, "")

        elif action.wfsrequest != None:
            sql = action.wfsrequest.getStatement(self)

            cursor = self.db.cursor()
            cursor.execute(str(sql))

            cursor.execute("SELECT currval('%s');" % self.id_sequence())
            action.id = cursor.fetchone()[0]

            return InsertResult(action.id, "")

        return None

    def update(self, action):
        if action.feature != None:
            feature = action.feature
            predicates = ", ".join(self.feature_predicates(feature))

            sql = "UPDATE \"%s\" SET %s WHERE %s = %d" % (
                self.table, predicates, self.fid_col, action.id)

            cursor = self.db.cursor()
            cursor.execute(str(sql), self.feature_values(feature))

            return UpdateResult(action.id, "")

        elif action.wfsrequest != None:
            sql = action.wfsrequest.getStatement(self)

            cursor = self.db.cursor()
            cursor.execute(str(sql))

            return UpdateResult(action.id, "")

        return None

    def delete(self, action):
        if action.feature != None:
            sql = "DELETE FROM \"%s\" WHERE %s = %%(%s)d" % (
                self.table, self.fid_col, self.fid_col)
            cursor = self.db.cursor()

            try:
                cursor.execute(str(sql) % {self.fid_col: action.id})
            except:
                cursor.execute(str(sql), {self.fid_col: action.id})

            return DeleteResult(action.id, "")

        elif action.wfsrequest != None:
            sql = action.wfsrequest.getStatement(self)
            cursor = self.db.cursor()
            try:
                cursor.execute(str(sql) % {self.fid_col: action.id})
            except:
                cursor.execute(str(sql), {self.fid_col: action.id})

            return DeleteResult(action.id, "")

        return None

    def select(self, action):
        cursor = self.db.cursor()

        if action.id is not None:
            sql = "SELECT ST_AsText(ST_Transform(%s, %d)) as fs_text_geom, " % (
                self.geom_col, int(self.srid_out))

            if hasattr(self, 'version'):
                sql += "%s as version, " % self.version
            if hasattr(self, 'ele'):
                sql += "%s as ele, " % self.ele

            sql += "\"%s\"" % self.fid_col

            if len(self.attribute_cols) > 0:
                sql += ", %s" % self.attribute_cols

            if hasattr(self, "additional_cols"):
                cols = self.additional_cols.split(';')
                additional_col = ",".join(cols)
                sql += ", %s" % additional_col

            sql += " FROM \"%s\" WHERE %s = %%(%s)s" % (
                self.table, self.fid_col, self.fid_col)

            #sql = "SELECT ST_AsText(ST_Transform(%s, %d)) as fs_text_geom, %s as ele, %s as version, \"%s, %s FROM \"%s\" WHERE %s = %%(%s)s" % (
            #        self.geom_col, int(self.srid_out), self.ele, self.version, self.fid_col, self.attribute_cols, self.table, self.fid_col, self.fid_col )

            cursor.execute(str(sql), {self.fid_col: str(action.id)})

            result = [cursor.fetchone()]
        else:
            filters = []
            attrs = {}
            if action.attributes:
                match = Feature(props=action.attributes)
                filters = self.feature_predicates(match)
                for key, value in action.attributes.items():
                    if isinstance(value, dict):
                        attrs[key] = value['value']
                    else:
                        attrs[key] = value
            if action.bbox:
                filters.append(
                    "%s && ST_Transform(ST_SetSRID('BOX3D(%f %f,%f %f)'::box3d, %s), %s) AND ST_Intersects(%s, ST_Transform(ST_SetSRID('BOX3D(%f %f,%f %f)'::box3d, %s), %s))"
                    % ((self.geom_col, ) + tuple(action.bbox) +
                       (self.srid_out, ) + (self.srid, ) + (self.geom_col, ) +
                       (tuple(action.bbox) + (self.srid_out, ) +
                        (self.srid, ))))
            sql = "SELECT ST_AsText(ST_Transform(%s, %d)) as fs_text_geom, " % (
                self.geom_col, int(self.srid_out))
            if hasattr(self, 'ele'):
                sql += "%s as ele, " % self.ele
            if hasattr(self, 'version'):
                sql += "%s as version, " % self.version
            sql += "\"%s\"" % self.fid_col

            if len(self.attribute_cols) > 0:
                sql += ", %s" % self.attribute_cols

            # check OGC FE attributes
            if self.fe_attributes and action.wfsrequest:
                fe_cols = action.wfsrequest.getAttributes()
                ad_cols = self.getColumns()

                fe_cols = filter(lambda x: x not in ad_cols, fe_cols)

                if len(fe_cols) > 0:
                    sql += ", %s" % ",".join(fe_cols)

            if hasattr(self, "additional_cols"):
                cols = self.additional_cols.split(';')
                additional_col = ",".join(cols)
                sql += ", %s" % additional_col

            sql += " FROM \"%s\"" % (self.table)

            #sql = "SELECT ST_AsText(Transform(%s, %d)) as fs_text_geom, %s as ele, %s as version, \"%s\", %s FROM \"%s\"" % (self.geom_col, int(self.srid_out), self.ele, self.version, self.fid_col, self.attribute_cols, self.table)
            if filters:
                sql += " WHERE " + " AND ".join(filters)
            if action.wfsrequest:
                if filters:
                    sql += " AND "
                else:
                    sql += " WHERE "

                sql += action.wfsrequest.render(self)

            if self.order:
                sql += " ORDER BY " + self.order
            if action.maxfeatures:
                sql += " LIMIT %d" % action.maxfeatures
            #else:
            #    sql += " LIMIT 1000"
            if action.startfeature:
                sql += " OFFSET %d" % action.startfeature

            try:
                cursor.execute(str(sql), attrs)
            except Exception, e:
                if e.pgcode[:
                            2] == errorcodes.CLASS_SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION:
                    raise InvalidValueException(
                        **{
                            'dump': e.pgerror,
                            'layer': self.name,
                            'locator': 'PostGIS'
                        })

            result = cursor.fetchall(
            )  # should use fetchmany(action.maxfeatures)

        columns = [desc[0] for desc in cursor.description]
        features = []
        for row in result:
            props = dict(zip(columns, row))
            if not props['fs_text_geom']: continue
            geom = WKT.from_wkt(props['fs_text_geom'])
            id = props[self.fid_col]
            del props[self.fid_col]
            if self.attribute_cols == '*':
                del props[self.geom_col]
            del props['fs_text_geom']
            for key, value in props.items():
                if isinstance(value, str):
                    props[key] = unicode(value, self.encoding)
                elif isinstance(value, datetime.datetime) or isinstance(
                        value, datetime.date):
                    # stringify datetimes
                    props[key] = str(value)

                try:
                    if isinstance(value, decimal.Decimal):
                        props[key] = unicode(str(value), self.encoding)
                except:
                    pass

            if (geom):
                features.append(
                    Feature(id, geom, self.geom_col, self.srid_out, props))
        return features
예제 #24
0
    def select(self, action):
        cursor = self.db.cursor()

        if action.id is not None:
            sql = "SELECT AsText(%s) as fs_text_geom, \"%s\", %s FROM \"%s\" WHERE %s = %%(%s)s" % (
                self.geom_col, self.fid_col, self.attribute_cols, self.table,
                self.fid_col, self.fid_col)
            cursor.execute(str(sql), {self.fid_col: str(action.id)})

            result = [cursor.fetchone()]
        else:
            filters = []
            attrs = {}
            if action.attributes:
                match = Feature(props=action.attributes)
                filters = self.feature_predicates(match)
                for key, value in action.attributes.items():
                    if isinstance(value, dict):
                        attrs[key] = value['value']
                    else:
                        attrs[key] = value
            if action.bbox:
                filters.append(
                    "%s && SetSRID('BOX3D(%f %f,%f %f)'::box3d, %s) and intersects(%s, SetSRID('BOX3D(%f %f,%f %f)'::box3d, %s))"
                    % ((self.geom_col, ) + tuple(action.bbox) + (self.srid, ) +
                       (self.geom_col, ) + (tuple(action.bbox) +
                                            (self.srid, ))))
            sql = "SELECT AsText(%s) as fs_text_geom, \"%s\", %s FROM \"%s\"" % (
                self.geom_col, self.fid_col, self.attribute_cols, self.table)
            if filters:
                sql += " WHERE " + " AND ".join(filters)
            if self.order:
                sql += " ORDER BY " + self.order
            if action.maxfeatures:
                sql += " LIMIT %d" % action.maxfeatures
            else:
                sql += " LIMIT 1000"
            if action.startfeature:
                sql += " OFFSET %d" % action.startfeature
            cursor.execute(str(sql), attrs)

            result = cursor.fetchall(
            )  # should use fetchmany(action.maxfeatures)
        columns = [desc[0] for desc in cursor.description]
        features = []
        for row in result:
            props = dict(zip(columns, row))
            if not props['fs_text_geom']: continue
            geom = WKT.from_wkt(props['fs_text_geom'])
            id = props[self.fid_col]
            del props[self.fid_col]
            if self.attribute_cols == '*':
                del props[self.geom_col]
            del props['fs_text_geom']
            for key, value in props.items():
                if isinstance(value, str):
                    props[key] = unicode(value, self.encoding)
                elif isinstance(value, datetime.datetime) or isinstance(
                        value, datetime.date):
                    # stringify datetimes
                    props[key] = str(value)

                try:
                    if isinstance(value, decimal.Decimal):
                        props[key] = unicode(str(value), self.encoding)
                except:
                    pass

            if (geom):
                features.append(Feature(id, geom, props))
        return features
예제 #25
0
    def select (self, action):
        if not hasattr (action, 'parameters') or not action.parameters.has_key('area_filter'):
            raise ApplicationException("Missing spatial area filter, supply a Well Known Text geometry for 'area_filter'")
        else:
            cursor = self.db.cursor()

            # validate input WKT string (TODO: implement as proper regexp)
            if not action.parameters['area_filter'].find ('POLYGON ('):
                raise ApplicationException ("Invalid spatial filter, should be Well Known Text respresentation of a Polygon") 

            sql  = "SELECT ST_AsText(%s) as fs_text_geom, 1 AS \"%s\", %s FROM \"%s\"" % (self.geom_col, self.fid_col, self.attribute_cols, self.table)
            sql += "('%s')" % action.parameters['area_filter']

            try:
                cursor.execute(str(sql))
            except Exception, e:
                raise ApplicationException ("<em>Internal error:</em><br><pre>%s</pre>" % (str (e)))

            result = cursor.fetchall()

            # column names from cursor
            columns = [desc[0] for desc in cursor.description]
            # output features to return
            features = []
            for row in result:
                if not row: continue

                # convert result set row record into dictionary
                props = dict(zip(columns, row))
                # skip records with no geometry
                if not props['fs_text_geom']: continue
                geom  = WKT.from_wkt(props['fs_text_geom'])

                # add feature id column and remove it from result set dictionary
                id = props[self.fid_col]
                del props[self.fid_col]

                # all attribute columns, including geometry have been selected, remove the duplicate
                if self.attribute_cols == '*':
                    del props[self.geom_col]

                # no need for it anymore, geom is already been set
                del props['fs_text_geom']

                # the remainder of the items in the result set dictionary are only the attributes
                # we need to convert to attributes, for Traffic Model these are embedded as arrays
                data = {}
                attributes = []
                values = []

                # expand arrays in result set to proper attributes
                for key, value in props.items():
                    if key == "attributes":
                        # attributes is an array of attribute type (must be first)
                        attributes = value
                    else:
                        if key == "aggregates":
                            # no prefix needed for aggregate values
                            prefix = ""
                        else:
                            prefix = "[%s] " % key

                        values = value
                        # go through attributes and add values for each
                        for index, type in enumerate(attributes):
                            # prepend prefix to differentiate different types of aggregates
                            field = "%s%s" % (prefix, type)
                            data[field] = values[index]

                props.clear()

                # go through data fields and format according to type
                for key, value in data.items():
                    if isinstance(value, str): 
                        data[key] = unicode(value, self.encoding)
                    elif isinstance(value, datetime.datetime) or isinstance(value, datetime.date):
                        # stringify datetimes 
                        data[key] = str(value)
                        
                    try:
                        if isinstance(value, decimal.Decimal):
                            data[key] = unicode(str(value), self.encoding)
                    except:
                        pass

                if (geom):
                    features.append( Feature( id, geom, self.geom_col, self.srid_out, data ) ) 

            return features
예제 #26
0
    def select(self, action):
        self.begin()
        cursor = self._connection.cursor()
        
        if action.id is not None:
            sql = "SELECT AsText(Transform(%s, %d)) as fs_text_geom, " % (self.geom_col, int(self.srid_out))

            if hasattr(self, 'version'):
                sql += "%s as version, " % self.version
            if hasattr(self, 'ele'):
                sql += "%s as ele, " % self.ele
            
            sql += "\"%s\"" % self.fid_col
        
            if len(self.attribute_cols) > 0:
                sql += ", %s" % self.attribute_cols

            if hasattr(self, "additional_cols"):
                cols = self.additional_cols.split(';')
                additional_col = ",".join(cols)
                sql += ", %s" % additional_col
            
            sql += " FROM \"%s\" WHERE %s = :%s" % (self.table, self.fid_col, self.fid_col)
            cursor.execute(str(sql), {self.fid_col: str(action.id)})
            
            result = [cursor.fetchone()]
            
        else:
            filters = []
            attrs = []
            if action.attributes:
                match = Feature(props = action.attributes)
                filters = self.feature_predicates(match)
                for key, value in action.attributes.items():
                    if isinstance(value, dict):
                        attrs[key] = value['value']
                    else:
                        attrs[key] = value
            if action.bbox:
                filters.append("Intersects(Transform(BuildMBR(%f, %f, %f, %f, %s), %s), geometry)" % (tuple(action.bbox) + (self.srid_out,) + (self.srid,)))


            sql = "SELECT AsText(Transform(%s, %d)) as fs_text_geom, " % (self.geom_col, int(self.srid_out))
            if hasattr(self, 'ele'):
                sql += "%s as ele, " % self.ele
            if hasattr(self, 'version'):
                sql += "%s as version, " % self.version
            sql += "\"%s\"" % self.fid_col
                
            if len(self.attribute_cols) > 0:
                sql += ", %s" % self.attribute_cols
                        
            # check OGC FE attributes
            if self.fe_attributes and action.wfsrequest:
                fe_cols = action.wfsrequest.getAttributes()
                ad_cols = self.getColumns()    
                
                fe_cols = filter(lambda x: x not in ad_cols, fe_cols)
                
                if len(fe_cols) > 0:
                    sql += ", %s" % ",".join(fe_cols)
                
            if hasattr(self, "additional_cols"):
                cols = self.additional_cols.split(';')
                additional_col = ",".join(cols)
                sql += ", %s" % additional_col

            
            sql += " FROM \"%s\"" % (self.table)
            
            if filters:
                sql += " WHERE " + " AND ".join(filters)
            if action.wfsrequest:
                if filters:
                    sql += " AND "
                else:
                    sql += " WHERE "
                sql += action.wfsrequest.render(self)
            
            if self.order:
                sql += " ORDER BY " + self.order
            if action.maxfeatures:
                sql += " LIMIT %d" % action.maxfeatures
            #else:
            #    sql += " LIMIT 1000"
            if action.startfeature:
                sql += " OFFSET %d" % action.startfeature
            
            cursor.execute(str(sql), attrs)

            result = cursor.fetchall()
                
        columns = [desc[0] for desc in cursor.description]
        features = []
        
        for row in result:
            props = dict(zip(columns, row))
            if not props['fs_text_geom']: continue
            geom  = WKT.from_wkt(props['fs_text_geom'])
            id = props[self.fid_col]
            del props[self.fid_col]
            if self.attribute_cols == '*':
                del props[self.geom_col]
            del props['fs_text_geom']
            for key, value in props.items():
                if isinstance(value, str):
                    props[key] = unicode(value, self.encoding)
                elif isinstance(value, datetime.datetime) or isinstance(value, datetime.date):
                    # stringify datetimes
                    props[key] = str(value)

                try:
                    if isinstance(value, decimal.Decimal):
                        props[key] = unicode(str(value), self.encoding)
                except:
                    pass

            if (geom):
                features.append( Feature( id, geom, self.geom_col, self.srid_out, props ) )
        return features
예제 #27
0
    def select (self, action):
        model = __import__(self.model, fromlist=['*'])
        cls = getattr(model, self.cls)
        geom_cls = None
        if self.geom_cls:
            geom_cls = getattr(model, self.geom_cls)
        if action.id is not None:
            result = [self.session.query(cls).get(action.id)]
        else:
            if self.geom_rel and self.geom_cls:
                main_table = cls.__tablename__
                geom_table = geom_cls.__tablename__
                join_condition = self.join_condition or "%s.%s_id=%s.id" % (
			main_table, geom_table, geom_table)
                query = self.session.query(cls, geom_cls).filter(join_condition)
            else:
                query = self.session.query(cls)
            if action.attributes:
                query = query.filter(
                    and_(
                        *[self.feature_predicate(getattr(cls, k),
				v['type'], v['value'])
				for k, v in action.attributes.iteritems()]
                    )
                )
            if action.bbox:
                if self.geom_rel and self.geom_cls:
                    geom_element = getattr(geom_cls, self.geom_col)
                else:
                    geom_element = getattr(cls, self.geom_col)
                query = query.filter(geom_element.intersects(
                    self.session.scalar(func.GeomFromText(
			self.bbox2wkt(action.bbox), self.srid))))
            if self.order:
                query = query.order_by(getattr(cls, self.order))
            if action.maxfeatures:
                query.limit(action.maxfeatures)
            else:   
                query.limit(1000)
            if action.startfeature:
                query.offset(action.startfeature)
            result = query.all()

        features = []
        for row_tuple in result:
            props = {}
            id = None
            geom = None
            if not isinstance(row_tuple, (tuple, list, dict, set)):
                row_tuple = (row_tuple,)
            for  row in row_tuple:
                if isinstance(row, cls):
                    cols = cls.__table__.c.keys()
                    for col in cols:
                        if col == self.fid_col:
                            id = getattr(row, col)
                        elif col == self.geom_col:
                            geom = WKT.from_wkt(self.session.scalar(getattr(row, col).wkt))
                        else:
                            if self.attribute_cols == '*' or col in self.attribute_cols:
                                props[col] = getattr(row, col)
                elif isinstance(row, geom_cls) and geom_cls:
                    cols = geom_cls.__table__.c.keys()
                    for col in cols:
                        if col == self.fid_col:
                            pass
                        elif col == self.geom_col:
                            geom = WKT.from_wkt(self.session.scalar(getattr(row, col).wkt))
                        else:
                            if self.attribute_cols == '*' or col in self.attribute_cols:
                                props[col] = getattr(row, col)
                else:
                    continue

            for key, value in props.items():
                if isinstance(value, str): 
                    props[key] = unicode(value, self.encoding)
                elif isinstance(value, datetime.datetime) or isinstance(value, datetime.date):
                    # stringify datetimes 
                    props[key] = str(value)
                    
                try:
                    if isinstance(value, decimal.Decimal):
                        props[key] = unicode(str(value), self.encoding)
                except:
                    pass
                    
            if (geom):
                features.append( Feature( id, geom, props ) ) 
        return features
예제 #28
0
 def create (self, action):
     feature = action.feature
     columns = ", ".join(self.column_names(feature)+[self.geom_col])
     values = ", ".join(self.value_formats(feature)+["SetSRID('%s'::geometry, %s) " % (WKT.to_wkt(feature.geometry), self.srid)])
     sql = "INSERT INTO \"%s\" (%s) VALUES (%s)" % (
                                     self.table, columns, values)
     cursor = self.db.cursor()
     cursor.execute(str(sql), self.feature_values(feature))
     cursor.execute("SELECT currval('%s');" % self.id_sequence())
     action.id = cursor.fetchone()[0]
     self.db.commit()
     return self.select(action)
예제 #29
0
    def insert (self, action):
        self.begin()
        if action.feature != None:
            feature = action.feature
            columns = ", ".join(self.column_names(feature)+[self.geom_col])
            values = ", ".join(self.value_formats(feature)+["SetSRID('%s'::geometry, %s) " % (WKT.to_wkt(feature.geometry), self.srid)])

            sql = "INSERT INTO \"%s\" (%s) VALUES (%s)" % (self.table, columns, values)

            cursor = self.db.cursor()
            cursor.execute(str(sql), self.feature_values(feature))

            cursor.execute("SELECT currval('%s');" % self.id_sequence())
            action.id = cursor.fetchone()[0]
            
            return InsertResult(action.id, "")
        
        elif action.wfsrequest != None:
            sql = action.wfsrequest.getStatement(self)
            
            cursor = self.db.cursor()
            cursor.execute(str(sql))
            
            cursor.execute("SELECT currval('%s');" % self.id_sequence())
            action.id = cursor.fetchone()[0]
            
            return InsertResult(action.id, "")
                        
        return None
예제 #30
0
    def select (self, action):
        cursor = self.db.cursor()

        if action.id is not None:
            sql = "SELECT AsText(%s) as fs_text_geom, \"%s\", %s FROM \"%s\" WHERE %s = %%(%s)s" % (
                    self.geom_col, self.fid_col, self.attribute_cols, self.table, self.fid_col, self.fid_col )
            cursor.execute(str(sql), {self.fid_col: str(action.id)})

            result = [cursor.fetchone()]
        else:
            filters = []
            attrs   = {}
            if action.attributes:
                match = Feature(props = action.attributes)
                filters = self.feature_predicates(match)
                for key, value in action.attributes.items():
                    if isinstance(value, dict):
                        attrs[key] = value['value']
                    else:
                        attrs[key] = value
            if action.bbox:
                filters.append( "%s && SetSRID('BOX3D(%f %f,%f %f)'::box3d, %s) and intersects(%s, SetSRID('BOX3D(%f %f,%f %f)'::box3d, %s))" % (
                                        (self.geom_col,) + tuple(action.bbox) + (self.srid,) + (self.geom_col,) + (tuple(action.bbox) + (self.srid,))))
            sql = "SELECT AsText(%s) as fs_text_geom, \"%s\", %s FROM \"%s\"" % (self.geom_col, self.fid_col, self.attribute_cols, self.table)
            if filters:
                sql += " WHERE " + " AND ".join(filters)
            if self.order:
                sql += " ORDER BY " + self.order
            if action.maxfeatures:
                sql += " LIMIT %d" % action.maxfeatures
            else:   
                sql += " LIMIT 1000"
            if action.startfeature:
                sql += " OFFSET %d" % action.startfeature
            cursor.execute(str(sql), attrs)
            
            result = cursor.fetchall() # should use fetchmany(action.maxfeatures)
        columns = [desc[0] for desc in cursor.description]
        features = []
        for row in result:
            props = dict(zip(columns, row))
            if not props['fs_text_geom']: continue
            geom  = WKT.from_wkt(props['fs_text_geom'])
            id = props[self.fid_col]
            del props[self.fid_col]
            if self.attribute_cols == '*':
                del props[self.geom_col]
            del props['fs_text_geom']
            for key, value in props.items():
                if isinstance(value, str): 
                        props[key] = unicode(value, self.encoding)
                elif isinstance(value, datetime.datetime) or isinstance(value, datetime.date):
                    # stringify datetimes 
                    props[key] = str(value)
                    
                try:
                    if isinstance(value, decimal.Decimal):
                            props[key] = unicode(str(value), self.encoding)
                except:
                    pass
                    
            if (geom):
                features.append( Feature( id, geom, props ) ) 
        return features