Changeset 540

Show
Ignore:
Timestamp:
11/04/06 08:12:56 (2 years ago)
Author:
zool
Message:

some changes to support date range constraints to within_shape, also needs testing

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • bbox/bbox/spatialStore.py

    Revision 464 Revision 540
    1"""provides the unified data store between redland RDF and spatial (for now PostGIS) index""" 1"""provides the unified data store between redland RDF and spatial (for now PostGIS) index""" 
    2import pgdb 2import pgdb 
    3import re 3import re 
    4from warnings import warn 4from warnings import warn 
    5 5 
    6# Spatial Reference ID for WGS84 lat/long is 4326 6# Spatial Reference ID for WGS84 lat/long is 4326 
    7 7 
    8_srid = str(4326) 8_srid = str(4326) 
    9 9 
    10class SpatialStore: 10class SpatialStore: 
    11 11 
    12    def __init__(self,database,vars=None,model=None): 12    def __init__(self,database,vars=None,model=None): 
    13        """Create a new store interface to a database name.""" 13        """Create a new store interface to a database name.""" 
    14        self.vars = vars 14        self.vars = vars 
    15        self.model = model 15        self.model = model 
    16 16 
    17        if database is None: 17        if database is None: 
    18            return AttributeError 18            return AttributeError 
    19             19             
    20        db = pgdb.connect(database=database) 20        db = pgdb.connect(database=database) 
    21        self.db = db 21        self.db = db 
    22 22 
    23    def parse_points(self,line): 23    def parse_points(self,line): 
    24        """Given a string of space and comma separated points, 24        """Given a string of space and comma separated points, 
    25        got usually from GeoRDF, return a list of lists. 25        got usually from GeoRDF, return a list of lists. 
    26 26 
    27        This could optionally support different line formats. 27        This could optionally support different line formats. 
    28        (e.g. WKT, or http://brainoff.com/worldkit/doc/polygon.php ) 28        (e.g. WKT, or http://brainoff.com/worldkit/doc/polygon.php ) 
    29        """ 29        """ 
    30        sets = line.split(',') 30        sets = line.split(',') 
    31        points = [] 31        points = [] 
    32        for s in sets: 32        for s in sets: 
    33            s = re.sub('^ ','',s) 33            s = re.sub('^ ','',s) 
    34            p = s.split(' ') 34            p = s.split(' ') 
    35            points.append(p) 35            points.append(p) 
    36             36             
    37        return points 37        return points 
    38 38 
    39    def wkt_from_points(self,points): 39    def wkt_from_points(self,points): 
    40        sets = [] 40        sets = [] 
    41        for p in points: 41        for p in points: 
    42            set = ' '.join(p) 42            set = ' '.join(p) 
    43            sets.append(set) 43            sets.append(set) 
    44        return ','.join(sets) 44        return ','.join(sets) 
    45 45 
    46    def geom(self,uri): 46    def geom(self,uri): 
    47        """Passed the uri of an object, or an rdfobj Object, returns geometry for it as a dict; if the geom is a point, {'x':x,'y':y}; if the geom is a line, x1,y1,x2,y2; polygon geom isn't really supported yet.""" 47        """Passed the uri of an object, or an rdfobj Object, returns geometry for it as a dict; if the geom is a point, {'x':x,'y':y}; if the geom is a line, x1,y1,x2,y2; polygon geom isn't really supported yet.""" 
    48        # again we have an issue of what kind of geom this is 48        # again we have an issue of what kind of geom this is 
    49        select = "select GeometryType(geom) from nodes where node='"+uri+"'" 49        select = "select GeometryType(geom) from nodes where node='"+uri+"'" 
    50        db = self.db.cursor() 50        db = self.db.cursor() 
    51        db.execute(select) 51        db.execute(select) 
    52        g = db.fetchone() 52        g = db.fetchone() 
    53        geom = {} 53        geom = {} 
    54        if g[0] == 'LINESTRING': 54        if g[0] == 'LINESTRING': 
    55            select = "SELECT name, type, X(StartPoint(geom)) as x, Y(StartPoint(geom)), GeomFromText(geom) as y, X(EndPoint(geom)), Y(EndPoint(geom)) from nodes where node='"+str(uri)+"'" 55            select = "SELECT name, type, X(StartPoint(geom)) as x, Y(StartPoint(geom)), GeomFromText(geom) as y, X(EndPoint(geom)), Y(EndPoint(geom)) from nodes where node='"+str(uri)+"'" 
    56            db.execute(select) 56            db.execute(select) 
    57            c = db.fetchone() 57            c = db.fetchone() 
    58            geom['x1'] = c[2] 58            geom['x1'] = c[2] 
    59            geom['x2'] = c[4] 59            geom['x2'] = c[4] 
    60            geom['y1'] = c[3] 60            geom['y1'] = c[3] 
    61            geom['y2'] = c[5] 61            geom['y2'] = c[5] 
    62 62 
    63        elif g[0] == 'POINT': 63        elif g[0] == 'POINT': 
    64            select = "SELECT name,type,X(geom),Y(geom) from nodes where node='"+uri+"'" 64            select = "SELECT name,type,X(geom),Y(geom) from nodes where node='"+uri+"'" 
    65            db.execute(select) 65            db.execute(select) 
    66            c = db.fetchone() 66            c = db.fetchone() 
    67            geom['x'] = c[2] 67            geom['x'] = c[2] 
    68            geom['y'] = c[3] 68            geom['y'] = c[3] 
    69 69 
    70        elif g[0] == 'POLYGON': 70        elif g[0] == 'POLYGON': 
    71            #@@TODO support this properly, as vectors, whatever 71            #@@TODO support this properly, as vectors, whatever 
    72            select = 'SELECT GeomAsText(geom) where node = \''+uri+"'"   72            select = 'SELECT GeomAsText(geom) where node = \''+uri+"'"   
    73         73         
    74        return geom 74        return geom 
    75 75 
    76     76     
    77 77 
    78    def find_near(self,node=None,x=None,y=None,r=None,type=None,terse=None): 78    def find_near(self,node=None,x=None,y=None,r=None,type=None,terse=None): 
    79        if r is None: 79        if r is None: 
    80            r = 0.1  80            r = 0.1  
    81        found = self.within_box(minx=x-r,miny=y-r,maxx=x+r,maxy=y+r,type=type) 81        found = self.within_box(minx=x-r,miny=y-r,maxx=x+r,maxy=y+r,type=type) 
    82        return found 82        return found 
    83     83     
    84    def within_box(self,minx=None,miny=None,maxx=None,maxy=None,type=None): 84    def within_box(self,minx=None,miny=None,maxx=None,maxy=None,type=None,mindate=None,maxdate=None): 
    85        """Accepts a minx,miny,maxx,maxy bounding box. 85        """Accepts a minx,miny,maxx,maxy bounding box. 
    86        Returns a list of rdfobj Objects.""" 86        Returns a list of rdfobj Objects.""" 
    87        points=[(minx,miny),(minx,maxy),(maxx,maxy),(maxx,miny),(minx,miny)] 87        points=[(minx,miny),(minx,maxy),(maxx,maxy),(maxx,miny),(minx,miny)] 
    88        return self.within_shape(points=points,type=type) 88        return self.within_shape(points=points,type=type,mindate=mindate,maxdate=maxdate) 
    89 89 
    90    def within_shape(self,points=[],type=None): 90    def within_shape(self,points=[],type=None,mindate=None,maxdate=None): 
    91        """Finds all the nodes in the shape made up by the points. 91        """Finds all the nodes in the shape made up by the points. 
    92        In future this may be filterable differently or we might post-filter it.  92         Can be limited by type (URL of an RDF type) or by min and max date range. 
       93         (In future this may be filterable differently or we might post-filter it.) 
    93        Returns a list of rdfobj objects which can be explored for connections. 94        Returns a list of rdfobj objects which can be explored for connections. 
    94        """ 95        """ 
    95        poly = [] 96        poly = [] 
    96        points = self.join_into_shape(points) 97        points = self.join_into_shape(points) 
    97           
    98        for p in points: 98        for p in points: 
    99            poly.append(str(p[0])+' '+str(p[1])) 99            poly.append(str(p[0])+' '+str(p[1])) 
    100        polystring = ','.join(poly) 100        polystring = ','.join(poly) 
    101 101 
    102        select = "SELECT node FROM nodes where Within( geom , GeomFromText('POLYGON(("+polystring+"))',4326) )" 102        select = "SELECT node FROM nodes where Within( geom , GeomFromText('POLYGON(("+polystring+"))',4326) )" 
    103 103 
    104        """Also optionally filter by type"""  104         # Also optionally filter by RDF type 
    105        if type is not None:  105         if type is not None: select = select+' AND type=\''+str(type)+'\'' 
    106            select = select+' AND type=\''+str(type)+'\''  106  
       107         # And by date range (this is hacked in for WFS Simple support) 
       108         if mindate is not None: select = select+" AND dated >= '"+str(mindate)+"'" 
       109         if maxdate is not None: select = select+" AND dated <= '"+str(maxdate)+"'" 
    107             110             
    108        db = self.db 111        db = self.db 
    109        c = db.cursor() 112        c = db.cursor() 
    110        c.execute(select) 113        c.execute(select) 
    111        geoms = c.fetchall() 114        geoms = c.fetchall() 
    112        things = [] 115        things = [] 
    113        for g in geoms: 116        for g in geoms: 
    114            thing = self.model.fetch(g[0]) 117            thing = self.model.fetch(g[0]) 
    115            if thing is not None: 118            if thing is not None: 
    116                things.append(thing) 119                things.append(thing) 
    117         120         
    118        """Return a rdfobj node/object for each geometry.""" 121        """Return a rdfobj node/object for each geometry.""" 
    119        return things 122        return things 
    120 123 
    121    def join_into_shape(self,points): 124    def join_into_shape(self,points): 
    122        first = points[0] 125        first = points[0] 
    123        last = points.pop() 126        last = points.pop() 
    124        if first[0] == last[0]: 127        if first[0] == last[0]: 
    125            if first[1] == last[1]: 128            if first[1] == last[1]: 
    126                points.append(last) 129                points.append(last) 
    127                return points 130                return points 
    128        points.append(last) 131        points.append(last) 
    129        points.append(first) 132        points.append(first) 
    130        return points 133        return points 
    131 134 
    132    def recent(self,days=None,type=None,object=None): 135    def recent(self,days=None,type=None,object=None): 
    133        if days is None: days = 30 136        if days is None: days = 30 
    134         137         
    135        if type is None:  138        if type is None:  
    136            if object is not None: 139            if object is not None: 
    137                type = object.rdf_type 140                type = object.rdf_type 
    138 141 
    139        query = "SELECT node from nodes where (now() - interval '"+str(days)+" days') <= created" 142        query = "SELECT node from nodes where (now() - interval '"+str(days)+" days') <= created" 
    140        if type is not None: 143        if type is not None: 
    141            query = query+" and rdf_type = '"+type+"'" 144            query = query+" and rdf_type = '"+type+"'" 
    142 145 
    143        db = self.db 146        db = self.db 
    144        c = db.cursor() 147        c = db.cursor() 
    145        c.execute(query) 148        c.execute(query) 
    146        nodes = c.fetchall() 149        nodes = c.fetchall() 
    147        things = [] 150        things = [] 
    148        for n in nodes: 151        for n in nodes: 
    149            thing = self.model.fetch(n[0]) 152            thing = self.model.fetch(n[0]) 
    150            if thing is not None: things.append(thing) 153            if thing is not None: things.append(thing) 
    151        return things 154        return things 
    152 155 
    153      156     def within_date(self,mindate=None,maxdate=None): 
       157         clauses = [] 
       158         things = [] 
       159         if mindate is None and maxdate is None: return  
       160         if mindate is not None: 
       161              clauses.append("dated >= '"+str(mindate)+"'") 
       162         if maxdate is not None: 
       163              clauses.append("dated <= '"+str(maxdate)+"'") 
       164  
       165         query = "SELECT node from nodes WHERE"+clauses.join(' AND ') 
       166         db = self.db 
       167         c = db.cursor() 
       168         c.execute(query) 
       169         nodes = c.fetchall() 
       170         things = [] 
       171         for n in nodes: 
       172             thing = self.model.fetch(n[0]) 
       173             if thing is not None: things.append(thing) 
       174         return things 
       175  
       176           
    154    def add_geom(self,geom,uri,type=None,owner=None,points=[],object=None,date=None,name=None): 177    def add_geom(self,geom,uri,type=None,owner=None,points=[],object=None,date=None,name=None): 
    155        """ Add geometry for a Uri or rdfobj Object into a spatial index. name=[name] REALLY SHOULD be supplied.""" 178        """ Add geometry for a Uri or rdfobj Object into a spatial index. name=[name] REALLY SHOULD be supplied.""" 
    156        if type is None:  179        if type is None:  
    157            if object is not None: 180            if object is not None: 
    158                if object.rdf_type is not None: 181                if object.rdf_type is not None: 
    159                    type = str(object.rdf_type)  182                    type = str(object.rdf_type)  
    160 183 
    161        if type is None: 184        if type is None: 
    162            type = 'http://www.w3.org/2003/01/geo/wgs84_pos#SpatialThing' 185            type = 'http://www.w3.org/2003/01/geo/wgs84_pos#SpatialThing' 
    163 186 
    164        if owner is None: owner = 'wl@frot.org' 187        if owner is None: owner = 'wl@frot.org' 
    165 188 
    166        if date is None: 189        if date is None: 
    167            date = 'now()' 190            date = 'now()' 
    168        else: 191        else: 
    169            date = "'"+str(date)+"'" 192            date = "'"+str(date)+"'" 
    170     193     
    171        geomtext = None 194        geomtext = None 
    172        if geom == 'POINT': 195        if geom == 'POINT': 
    173            geomtext = "GeomFromText('POINT("+str(points[0][0])+' '+str(points[0][1])+")',4326)" 196            geomtext = "GeomFromText('POINT("+str(points[0][0])+' '+str(points[0][1])+")',4326)" 
    174        elif geom == 'LINESTRING': 197        elif geom == 'LINESTRING': 
    175            geomtext = "GeomFromText('LINESTRING("+str(points[0][0])+' '+str(points[0][1])+','+str(points[1][0])+' '+str(points[1][1])+")',4326)" 198            geomtext = "GeomFromText('LINESTRING("+str(points[0][0])+' '+str(points[0][1])+','+str(points[1][0])+' '+str(points[1][1])+")',4326)" 
    176        elif geom == 'POLYGON': 199        elif geom == 'POLYGON': 
    177            poly = [] 200            poly = [] 
    178            for p in points: 201            for p in points: 
    179                poly.append(str(p[0])+' '+str(p[1])) 202                poly.append(str(p[0])+' '+str(p[1])) 
    180            polystring = ','.join(poly) 203            polystring = ','.join(poly) 
    181            geomtext = "GeomFromText('POLYGON(("+polystring+"))',4326)"  204            geomtext = "GeomFromText('POLYGON(("+polystring+"))',4326)"  
    182 205 
    183        db = self.db 206        db = self.db 
    184        db.cursor().execute("INSERT INTO nodes (node,name,geom,type,created) values (%s,%s,"+geomtext+",%s,"+date+")", (str(uri),str(name),str(type))) 207        db.cursor().execute("INSERT INTO nodes (node,name,geom,type,created) values (%s,%s,"+geomtext+",%s,"+date+")", (str(uri),str(name),str(type))) 
    185        db.commit() 208        db.commit() 
    186 209 
    187     210     
    188    def update_geom(self,geom,uri,points=None,object=None,type=None,owner=None,name=None): 211    def update_geom(self,geom,uri,points=None,object=None,type=None,owner=None,name=None): 
    189         212         
    190        """Given a geometry type ('POINT','LINESTRING' or 'POLYGON'), and a uri, replace  213        """Given a geometry type ('POINT','LINESTRING' or 'POLYGON'), and a uri, replace  
    191        the geometry for that URI with the new one contained in the points list.""" 214        the geometry for that URI with the new one contained in the points list.""" 
    192        if geom is None: return 215        if geom is None: return 
    193        if uri is None: 216        if uri is None: 
    194            if object is not None: 217            if object is not None: 
    195                uri = str(object) 218                uri = str(object) 
    196            else: 219            else: 
    197                return 220                return 
    198        uri = uri.replace("'","\\'") 221        uri = uri.replace("'","\\'") 
    199        db = self.db 222        db = self.db 
    200        c = db.cursor() 223        c = db.cursor() 
    201        if geom == 'POINT': 224        if geom == 'POINT': 
    202            c.execute("UPDATE nodes SET geom = GeomFromText('POINT("+str(points[0][0])+' '+str(points[0][1])+")',4326) where node = '"+str(uri)+"'") 225            c.execute("UPDATE nodes SET geom = GeomFromText('POINT("+str(points[0][0])+' '+str(points[0][1])+")',4326) where node = '"+str(uri)+"'") 
    203        elif geom == 'LINESTRING': 226        elif geom == 'LINESTRING': 
    204            pass 227            pass 
    205        elif geom == 'POLYGON': 228        elif geom == 'POLYGON': 
    206            pass 229            pass 
    207        if name is not None: 230        if name is not None: 
    208            c.execute("UPDATE nodes SET name = %s WHERE node = %s",(str(name),str(uri),))        231            c.execute("UPDATE nodes SET name = %s WHERE node = %s",(str(name),str(uri),))        
    209        db.commit() 232        db.commit() 
    210 233 
    211    def geom_exists(self,uri): 234    def geom_exists(self,uri): 
    212        if uri is None: return 235        if uri is None: return 
    213        uri = str(uri) 236        uri = str(uri) 
    214        uri = re.sub("'","\\'",uri) 237        uri = re.sub("'","\\'",uri) 
    215        db = self.db 238        db = self.db 
    216        c = db.cursor() 239        c = db.cursor() 
    217        c.execute("SELECT X(geom) as x, Y(geom) as y from nodes WHERE node = '"+uri+"'") 240        c.execute("SELECT X(geom) as x, Y(geom) as y from nodes WHERE node = '"+uri+"'") 
    218        node = c.fetchone() 241        node = c.fetchone() 
    219        if node is not None: 242        if node is not None: 
    220            return True 243            return True 
    221        else: 244        else: 
    222            return False 245            return False 
    223         246         
    224 247 
    225    def add_or_update_geom(self,geom,uri,points=None,type=None,owner=None,object=None,name=None): 248    def add_or_update_geom(self,geom,uri,points=None,type=None,owner=None,object=None,name=None): 
    226        exists = self.geom_exists(uri) 249        exists = self.geom_exists(uri) 
    227        if exists is True: 250        if exists is True: 
    228            self.update_geom(geom,uri,points=points,type=type,owner=owner,object=object,name=name) 251            self.update_geom(geom,uri,points=points,type=type,owner=owner,object=object,name=name) 
    229        else: 252        else: 
    230            self.add_geom(geom,uri,points=points,type=type,owner=owner,object=object,name=name) 253            self.add_geom(geom,uri,points=points,type=type,owner=owner,object=object,name=name) 
    231 254 
    232    def add_or_update_point(self,uri,points=[],x=None,y=None,type=None,owner=None,object=None,name=None): 255    def add_or_update_point(self,uri,points=[],x=None,y=None,type=None,owner=None,object=None,name=None): 
    233        if x is not None: 256        if x is not None: 
    234            points = [(x,y)] 257            points = [(x,y)] 
    235        return self.add_or_update_geom('POINT',uri,points=points,type=type,owner=owner,object=object,name=name) 258        return self.add_or_update_geom('POINT',uri,points=points,type=type,owner=owner,object=object,name=name) 
    236 259 
    237    def add_or_update_line(self,uri,points=[],type=None,owner=None,object=None,name=None): 260    def add_or_update_line(self,uri,points=[],type=None,owner=None,object=None,name=None): 
    238        return self.add_or_update_geom('LINESTRING',uri,points=points,type=type,owner=owner,object=object,name=name)     261        return self.add_or_update_geom('LINESTRING',uri,points=points,type=type,owner=owner,object=object,name=name)     
    239 262 
    240    def add_or_update_polygon(self,uri,points=[],type=None,owner=None,object=None,name=None): 263    def add_or_update_polygon(self,uri,points=[],type=None,owner=None,object=None,name=None): 
    241        points = self.join_into_shape(points) 264        points = self.join_into_shape(points) 
    242        return self.add_or_update_geom('POLYGON',uri,points=points,type=type,owner=owner,object=object,name=name)  265        return self.add_or_update_geom('POLYGON',uri,points=points,type=type,owner=owner,object=object,name=name)