Example #1
0
def getGeographIDs(bbox,key):
    """
    Same as getUUIDs but for geograph files
    """
    if bbox and not key:
        res = spatialite.execute("""
            SELECT group_concat(id) as geograph_ids from geot_geograph g
            WHERE g.ROWID IN (SELECT pkid FROM idx_geot_geograph_geom 
                WHERE xmin >= ? AND ymin >= ? AND xmax <= ? AND ymax <= ?)
            AND id IN ( select id from sample_ids )
        """, bbox.split(',') )
    if not bbox and key:
        #filter out small keywords for keyword-only queries
        if ( len(key) < 3):
            res = [(None,)]
        else:
            res = spatialite.execute("""
                SELECT group_concat(b.gridimage_id) FROM gridimage_base b, gridimage_tag gt,tag  
                WHERE gt.gridimage_id=b.gridimage_id AND tag.tag_id=gt.tag_id 
                AND b.gridimage_id IN ( select id from sample_ids )
                AND tag like ?
                """, ( key + '%' ,))
    if bbox and key:
        # TODO too slow unless one uses a sorted text index and substitutes LIKE with a 
        # x< foobas AND x >= foobar kind of optimization 
        res = spatialite.execute("""
                    SELECT group_concat(id) as geograph_ids from geot_geograph g
                    WHERE g.ROWID IN (SELECT pkid FROM idx_geot_geograph_geom 
                        WHERE xmin >= ? AND ymin >= ? AND xmax <= ? AND ymax <= ?)
                    AND id IN ( select id from sample_ids )
                    -- AND tags like ?
            """, tuple(bbox.split(',')) ) # + (key,)
    res = ','.join(res[0]) if res[0]!=(None,) else ""
    return res    
Example #2
0
def getUUIDs(bbox,key):
    """
    Return all UUIDs in database for mediafile within a BBOX and/or with keyword `key'    
    
    Args:
        bbox (optional): string of comma separated xmin,ymin,xmax,ymax
        key (optional): keyword string to filter by 
    """
    if bbox and not key:
        res = spatialite.execute("""
            SELECT group_concat(uuid) from sessions WHERE sessions.ROWID IN 
            (SELECT pkid FROM idx_sessions_geom WHERE xmin >= ? AND ymin >= ? AND xmax <= ? AND ymax <= ?)
        """, bbox.split(',') )
    if not bbox and key:
        res = spatialite.execute("""
            SELECT group_concat(uuid) from sessions s, gtr_tags tags, gtr_tag tag 
            WHERE s.id = tags.sessions_id and tag.id = tags.tag_id and tag=?
        """, (key,) )
    if bbox and key:
        res = spatialite.execute("""
            SELECT group_concat(uuid) from sessions s, gtr_tags tags, gtr_tag tag 
            WHERE s.id = tags.sessions_id and tag.id = tags.tag_id and tag=?
            AND s.ROWID IN (SELECT pkid FROM idx_sessions_geom 
                WHERE xmin >= ? AND ymin >= ? AND xmax <= ? AND ymax <= ?)
        """, (key,) + tuple(bbox.split(',')) )
    res = ','.join(res[0]) if res[0]!=(None,) else ""
    return res
Example #3
0
def get_userid_list(bbox, key):
    """
    Return all userids in database for mediafile within a BBOX and/or with keyword `key'    
    
    Args:
        bbox (optional): string of comma separated xmin,ymin,xmax,ymax
        key (optional): keyword string to filter by
        
    Returns:
        String of comma-separated userids
    """
    if bbox and not key:
        res = spatialite.execute(
            """
            SELECT group_concat(userid) from sessions WHERE sessions.ROWID IN 
            (SELECT pkid FROM idx_sessions_geom WHERE xmin >= ? AND ymin >= ? AND xmax <= ? AND ymax <= ?)
        """, bbox.split(','))
    if not bbox and key:
        res = spatialite.execute(
            """
            SELECT group_concat(userid) from sessions s, tags, tag 
            WHERE s.id = tags.sessions_id and tag.id = tags.tag_id and tag=?
        """, (key, ))
    if bbox and key:
        res = spatialite.execute(
            """
            SELECT group_concat(userid) from sessions s, tags, tag 
            WHERE s.id = tags.sessions_id and tag.id = tags.tag_id and tag=?
            AND s.ROWID IN (SELECT pkid FROM idx_sessions_geom 
                WHERE xmin >= ? AND ymin >= ? AND xmax <= ? AND ymax <= ?)
        """, (key, ) + tuple(bbox.split(',')))
    res = ','.join(res[0]) if res[0] != (None, ) else ""
    return res
Example #4
0
def insert_uuid(uuid, lon=None, lat=None):
    """ register uuid. No-op if uuid already exists. 
    Optional argument lon/lat allow for geocoded uuids.
    """
    if ( not (lon and lat)):
        res = spatialite.execute("""
            INSERT OR IGNORE INTO sessions(uuid) VALUES (?);
        """, (uuid,))
    else:
        res = spatialite.execute("""
            INSERT OR IGNORE INTO sessions(uuid, geom) 
            VALUES (?,PointFromText('POINT('|| ? || ' '|| ? || ')', 4326))
        """, (uuid, lon, lat) )
    return res
Example #5
0
def get_request_pair(userid):
    """ return ( userid, req_secret ) pair for provided userid or None """
    res = spatialite.execute(
        """
       SELECT userid, reqsec from temp_request WHERE userid=?
       """, (userid, ))
    return res[0] if res else None
Example #6
0
def prune_temp_request():
    """ delete temporary requests tables. Should be called at app startup """
    res = spatialite.execute("""
        DELETE FROM temp_request(userid,reqsec) VALUES
        """)
    return res==[]
    
Example #7
0
def save_unverified_request(userid, req_secret):
    """ save unverified request keys """
    res = spatialite.execute(
        """
        INSERT OR IGNORE INTO temp_request(userid,reqsec) VALUES (?,?)
        """, (userid, req_secret))
    return res == []
Example #8
0
def delete_token(userid):
    """ delete token with provided userid """
    res = spatialite.execute(
        """
       DELETE FROM tokens WHERE userid=?
       """, (userid, ))
    return res == []
Example #9
0
def insert_key(key):
    """ register a new keyword. Noop if keyword exists. """
    res = spatialite.execute(
        """
       INSERT OR IGNORE INTO tag(tag) VALUES (?);
       """, (key, ))
    return res
Example #10
0
def insert_userid(userid, lon=None, lat=None):
    """ register userid. No-op if userid already exists. 
    Optional argument lon/lat allow for geocoded userids.
    """
    if (not (lon and lat)):
        res = spatialite.execute(
            """
            INSERT OR IGNORE INTO sessions(userid) VALUES (?);
        """, (userid, ))
    else:
        res = spatialite.execute(
            """
            INSERT OR IGNORE INTO sessions(userid, geom) 
            VALUES (?,PointFromText('POINT('|| ? || ' '|| ? || ')', 4326))
        """, (userid, lon, lat))
    return res
Example #11
0
def geograph_byid(id):
    """ Return lon,lat,tags for geogrph image of given id. Tags are None if they are empty"""
    res = spatialite.execute(
        """
            select long, lat, tags from geot_geograph where id = ?;
            """, (id, ))
    return res[0]
Example #12
0
def get_access_pair(userid):
    """ return ( acc_key, acc_secret ) for provided userid or None """
    res = spatialite.execute(
        """
       SELECT accsec, acckey from tokens WHERE userid=?
       """, (userid, ))
    return res[0] if res else None
Example #13
0
def connect_key(uuid,key):
   """ Associate a uuid with a keyword. Both MUST already exist 
   (so run insert_key&insert_uuid before just in case.) """
   res = spatialite.execute("""
       INSERT OR IGNORE INTO gtr_tags(sessions_id,tag_id) 
           SELECT s.id, t.id from sessions s, gtr_tag t 
           WHERE s.uuid=? and t.tag=?
       """ , (uuid,key))
   return res
Example #14
0
def connect_key(userid, key):
    """ Associate a userid with a keyword. Both MUST already exist 
   (so run insert_key&insert_userid before just in case.) """
    res = spatialite.execute(
        """
       INSERT OR IGNORE INTO tags(sessions_id,tag_id) 
           SELECT s.id, t.id from sessions s, tag t 
           WHERE s.userid=? and t.tag=?
       """, (userid, key))
    return res
Example #15
0
def get_tags(userid):
    """ Get all tags associated with a userid        
    """
    stdout = spatialite.execute("""
        BEGIN;
        select group_concat(tag) as tags from sessions LEFT OUTER JOIN tags 
        USING userid LEFT OUTER JOIN tag USING tag_id where userid=?
        COMMIT;""", (userid,))
    if stdout == None:
        raise DBException("No tags found!")
    return stdout.split(',')
Example #16
0
def get_tags():
    """ 
    """
    stdout = spatialite.execute("""
        BEGIN;
        select group_concat(tag) as tags from sessions LEFT OUTER JOIN gtr_tags 
        USING uuid LEFT OUTER JOIN gtr_tag USING tag_id where uuid=?
        COMMIT;""", (uuid,))
    if stdout == None:
        raise DBException("No tags found!")
    return stdout.split(',')
Example #17
0
def geotag_uuid(uuid, lon, lat):
    """ geocode existing uuid 
    args: 
        lon/lat WGS84 coords.
    """
    log.debug("lon is %s" % lon)
    log.debug("UPDATE sessions SET geom = PointFromText('POINT('|| %s || ' '|| %s || ')', 4326) WHERE uuid='%s'" % (lon,lat,uuid))
    res = spatialite.execute("""
        UPDATE sessions SET geom = PointFromText('POINT('|| ? || ' '|| ? || ')', 4326) WHERE uuid=?
    """, (lon, lat, uuid) )
    return res
Example #18
0
def get_tags(userid):
    """ Get all tags associated with a userid        
    """
    stdout = spatialite.execute(
        """
        BEGIN;
        select group_concat(tag) as tags from sessions LEFT OUTER JOIN tags 
        USING userid LEFT OUTER JOIN tag USING tag_id where userid=?
        COMMIT;""", (userid, ))
    if stdout == None:
        raise DBException("No tags found!")
    return stdout.split(',')
Example #19
0
def get_tags():
    """ 
    """
    stdout = spatialite.execute(
        """
        BEGIN;
        select group_concat(tag) as tags from sessions LEFT OUTER JOIN gtr_tags 
        USING uuid LEFT OUTER JOIN gtr_tag USING tag_id where uuid=?
        COMMIT;""", (uuid, ))
    if stdout == None:
        raise DBException("No tags found!")
    return stdout.split(',')
Example #20
0
def geotag_userid(userid, lon, lat):
    """ geocode existing userid 
    args: 
        lon/lat WGS84 coords.
    """
    log.debug("lon is %s" % lon)
    log.debug(
        "UPDATE sessions SET geom = PointFromText('POINT('|| %s || ' '|| %s || ')', 4326) WHERE userid='%s'"
        % (lon, lat, userid))
    res = spatialite.execute(
        """
        UPDATE sessions SET geom = PointFromText('POINT('|| ? || ' '|| ? || ')', 4326) WHERE userid=?
    """, (lon, lat, userid))
    return res
Example #21
0
def getGeographIDs(bbox, key):
    """
    Same as getUUIDs but for geograph files
    """
    if bbox and not key:
        res = spatialite.execute(
            """
            SELECT group_concat(id) as geograph_ids from geot_geograph g
            WHERE g.ROWID IN (SELECT pkid FROM idx_geot_geograph_geom 
                WHERE xmin >= ? AND ymin >= ? AND xmax <= ? AND ymax <= ?)
            AND id IN ( select id from sample_ids )
        """, bbox.split(','))
    if not bbox and key:
        #filter out small keywords for keyword-only queries
        if (len(key) < 3):
            res = [(None, )]
        else:
            res = spatialite.execute(
                """
                SELECT group_concat(b.gridimage_id) FROM gridimage_base b, gridimage_tag gt,tag  
                WHERE gt.gridimage_id=b.gridimage_id AND tag.tag_id=gt.tag_id 
                AND b.gridimage_id IN ( select id from sample_ids )
                AND tag like ?
                """, (key + '%', ))
    if bbox and key:
        # TODO too slow unless one uses a sorted text index and substitutes LIKE with a
        # x< foobas AND x >= foobar kind of optimization
        res = spatialite.execute("""
                    SELECT group_concat(id) as geograph_ids from geot_geograph g
                    WHERE g.ROWID IN (SELECT pkid FROM idx_geot_geograph_geom 
                        WHERE xmin >= ? AND ymin >= ? AND xmax <= ? AND ymax <= ?)
                    AND id IN ( select id from sample_ids )
                    -- AND tags like ?
            """, tuple(bbox.split(',')))  # + (key,)
    res = ','.join(res[0]) if res[0] != (None, ) else ""
    return res
Example #22
0
def nearest_dist( lon, lat, dist ):
    """ Find nearest neighbours of lon,lat within dist
        Args:
            lon: Longitude
            lat: Latitude
            dist: distance in metres (assuming data in wgs84)
        Returns:
            nearest sorted by distance
    """
    rad = 0.01 # rough approximation for filter
    #FYI sample_ids are the ones that geograph actually delivers for download as a torrent (about 10% of the whole).
    res = spatialite.execute("""
            SELECT id, realname,title,tags,comment, long,lat, GeodesicLength( MakeLine( MAKEPOINT(?, ?, 4326) , geot_geograph.geom)) AS "Distance (m)"
            FROM geot_geograph 
            WHERE GeodesicLength( MakeLine( MAKEPOINT(?, ?, 4326) , geot_geograph.geom)) < ?
                AND geot_geograph.ROWID IN ( 
                    SELECT pkid FROM idx_geot_geograph_geom WHERE xmin >= ? AND ymin >= ? AND xmax <= ? AND ymax <= ?)
                AND geot_geograph.id IN ( select id from sample_ids )
            ORDER BY 8 
            LIMIT 10;
            """, ( lon, lat, lon, lat ,dist, lon - rad, lat - rad , lon + rad, lat + rad )
        )
    return res
Example #23
0
def nearest_dist(lon, lat, dist):
    """ Find nearest neighbours of lon,lat within dist
        Args:
            lon: Longitude
            lat: Latitude
            dist: distance in metres (assuming data in wgs84)
        Returns:
            nearest sorted by distance
    """
    rad = 0.01  # rough approximation for filter
    #FYI sample_ids are the ones that geograph actually delivers for download as a torrent (about 10% of the whole).
    res = spatialite.execute(
        """
            SELECT id, realname,title,tags,comment, long,lat, GeodesicLength( MakeLine( MAKEPOINT(?, ?, 4326) , geot_geograph.geom)) AS "Distance (m)"
            FROM geot_geograph 
            WHERE GeodesicLength( MakeLine( MAKEPOINT(?, ?, 4326) , geot_geograph.geom)) < ?
                AND geot_geograph.ROWID IN ( 
                    SELECT pkid FROM idx_geot_geograph_geom WHERE xmin >= ? AND ymin >= ? AND xmax <= ? AND ymax <= ?)
                AND geot_geograph.id IN ( select id from sample_ids )
            ORDER BY 8 
            LIMIT 10;
            """,
        (lon, lat, lon, lat, dist, lon - rad, lat - rad, lon + rad, lat + rad))
    return res
Example #24
0
def save_access_tokens(userid, req_secret, acc_key, acc_secret):
   res = spatialite.execute("""
       INSERT OR IGNORE INTO tokens(userid,reqsec,accsec,acckey) VALUES (?,?,?,?)
       """, (userid,req_secret, acc_key, acc_secret))
   return res==[]
Example #25
0
def save_access_tokens(userid, req_secret, acc_key, acc_secret):
    res = spatialite.execute(
        """
       INSERT OR IGNORE INTO tokens(userid,reqsec,accsec,acckey) VALUES (?,?,?,?)
       """, (userid, req_secret, acc_key, acc_secret))
    return res == []
Example #26
0
def dump_tokens():
    res = spatialite.execute("""
       SELECT * FROM tokens;
       """)
    # 2D list of lists
    return res
Example #27
0
def get_access_pair(userid):
    """ return ( acc_key, acc_secret ) for provided userid or None """
    res = spatialite.execute("""
       SELECT accsec, acckey from tokens WHERE userid=?
       """, (userid,))
    return res[0] if res else None
Example #28
0
def delete_token(userid):
    """ delete token with provided userid """
    res = spatialite.execute("""
       DELETE FROM tokens WHERE userid=?
       """, (userid,) )
    return res==[]
Example #29
0
def insert_key(key):
   """ register a new keyword. Noop if keyword exists. """
   res = spatialite.execute("""
       INSERT OR IGNORE INTO gtr_tag(tag) VALUES (?);
       """ , (key,))
   return res
Example #30
0
def geograph_byid( id ):
    """ Return lon,lat,tags for geogrph image of given id. Tags are None if they are empty"""
    res = spatialite.execute("""
            select long, lat, tags from geot_geograph where id = ?;
            """, (id,))
    return res[0]
Example #31
0
def get_request_pair(userid):
    """ return ( userid, req_secret ) pair for provided userid or None """
    res = spatialite.execute("""
       SELECT userid, reqsec from temp_request WHERE userid=?
       """, (userid,))
    return res[0] if res else None
Example #32
0
def save_unverified_request( userid, req_secret ):
    """ save unverified request keys """
    res = spatialite.execute("""
        INSERT OR IGNORE INTO temp_request(userid,reqsec) VALUES (?,?)
        """, (userid,req_secret))
    return res==[]
Example #33
0
def prune_temp_request():
    """ delete temporary requests tables. Should be called at app startup """
    res = spatialite.execute("""
        DELETE FROM temp_request(userid,reqsec) VALUES
        """)
    return res == []
Example #34
0
def dump_tokens():
   res = spatialite.execute("""
       SELECT * FROM tokens;
       """)
   # 2D list of lists
   return res