예제 #1
0
def get_neighbors(nid, connection, spatial=True):
    """
    Get the geospatial neighbors of a node.
    spatial: True: use MySQL spatial functions (5.6+)
             False: use minimum bounding rectangles
    """

    fmt = coll.defaultdict(str)
    fmt["nid"] = nid
    if spatial:
        st_fun = "ST_DISTANCE(target.segment, source.segment)"
        fmt["order"] = "ORDER BY " + st_fun
        fmt["geo"] = "ST_"
    else:
        fmt["geo"] = "MBR"

    sql = [
        "SELECT target.id AS nid",
        "FROM operational source, operational target",
        "WHERE {1}INTERSECTS(source.segment, target.segment)",
        "AND source.id = {0} AND target.id <> {0} {2}",
    ]
    sql = db.process(sql, *[fmt[x] for x in ("nid", "geo", "order")])

    with db.DatabaseCursor(connection) as cursor:
        cursor.execute(sql)
        return frozenset([row["nid"] for row in cursor])
예제 #2
0
def get_neighbors(nid, connection, spatial=True):
    '''
    Get the geospatial neighbors of a node.
    spatial: True: use MySQL spatial functions (5.6+)
             False: use minimum bounding rectangles
    '''

    fmt = coll.defaultdict(str)
    fmt['nid'] = nid
    if spatial:
        st_fun = 'ST_DISTANCE(target.segment, source.segment)'
        fmt['order'] = 'ORDER BY ' + st_fun
        fmt['geo'] = 'ST_'
    else:
        fmt['geo'] = 'MBR'

    sql = [
        'SELECT target.id AS nid',
        'FROM operational source, operational target',
        'WHERE {1}INTERSECTS(source.segment, target.segment)',
        'AND source.id = {0} AND target.id <> {0} {2}',
    ]
    sql = db.process(sql, [fmt[x] for x in ('nid', 'geo', 'order')])

    with db.DatabaseCursor(connection) as cursor:
        cursor.execute(sql)
        return frozenset([row['nid'] for row in cursor])
예제 #3
0
def get_neighbors(nid, connection, spatial=True):
    '''
    Get the geospatial neighbors of a node.
    spatial: True: use MySQL spatial functions (5.6+)
             False: use minimum bounding rectangles
    '''
    
    fmt = coll.defaultdict(str)
    fmt['nid'] = nid
    if spatial:
        st_fun = 'ST_DISTANCE(target.segment, source.segment)'
        fmt['order'] = 'ORDER BY ' + st_fun
        fmt['geo'] = 'ST_'
    else:
        fmt['geo'] = 'MBR'
        
    sql = [ 'SELECT target.id AS nid',
            'FROM operational source, operational target',
            'WHERE {1}INTERSECTS(source.segment, target.segment)',
            'AND source.id = {0} AND target.id <> {0} {2}',
            ]
    sql = db.process(sql, [ fmt[x] for x in ('nid', 'geo', 'order') ])
    
    with db.DatabaseCursor(connection) as cursor:
        cursor.execute(sql)
        return frozenset([ row['nid'] for row in cursor ])
예제 #4
0
    def get_sql(self, nid):
        sql = [ 'SELECT n.{1} AS nid',
                'FROM network n',
                'LEFT JOIN operational o ON n.{1} = o.id',
                'WHERE n.{2} = {0} AND o.id IS NOT NULL',
                ]

        return db.process(sql, nid, *self.columns)
예제 #5
0
 def __init__(self):
     super().__init__()
     sql = [ 'SELECT n.id AS id, o.id AS valid,'
             'ST_ASTEXT(n.segment) AS segment',
             'FROM node n',
             'LEFT OUTER JOIN operational o ON n.id = o.id',
             'WHERE n.segment IS NOT NULL',
     ]
     self.sql = db.process(sql)
예제 #6
0
 def get_sql(self, nid):
     self.fmt['nid'] = nid
     sql = [ 'SELECT target.id AS nid',
             'FROM operational source, operational target',
             'WHERE {1}INTERSECTS(source.segment, target.segment)',
             'AND source.id = {0} AND target.id <> {0} {2}',
             ]
     params = [ self.fmt[x] for x in ('nid', 'geo', 'order') ]
     
     return db.process(sql, *params)
예제 #7
0
 def __get_readings(self, connection):
     sql = [ 'SELECT as_of, speed, travel_time / {1} AS travel',
             'FROM reading',
             'WHERE node = {0}',
             'ORDER BY as_of ASC',
             ]
     sql = db.process(sql, [ self.nid, constant.minute ])
     
     data = pd.read_sql_query(sql, con=connection, index_col='as_of')
     data.columns = [ 'speed', 'travel' ]
     
     return data.resample(self.freq).mean() if self.freq else data
예제 #8
0
    def __get_name(self, connection):
        sql = ["SELECT name", "FROM operational", "WHERE id = {0}"]
        sql = db.process(sql, self.nid)

        with db.DatabaseCursor(connection) as cursor:
            cursor.execute(sql)
            if cursor.rowcount != 1:
                err = "{0} does not exist!".format(self.nid)
                raise AttributeError(err)
            row = cursor.fetchone()

        return row["name"]
예제 #9
0
    def __get_readings(self, connection):
        sql = [
            'SELECT as_of, speed, travel_time / {1} AS travel',
            'FROM reading',
            'WHERE node = {0}',
            'ORDER BY as_of ASC',
        ]
        sql = db.process(sql, [self.nid, constant.minute])

        data = pd.read_sql_query(sql, con=connection, index_col='as_of')
        data.columns = ['speed', 'travel']

        return data.resample(self.freq).mean() if self.freq else data
예제 #10
0
    def __get_name(self, connection):
        sql = [ 'SELECT name',
                'FROM operational',
                'WHERE id = {0}',
                ]
        sql = db.process(sql, [ self.nid ])
        
        with db.DatabaseCursor(connection) as cursor:
            cursor.execute(sql)
            if cursor.rowcount != 1:
                err = '{0} does not exist!'.format(self.nid)
                raise AttributeError(err)
            row = cursor.fetchone()

        return row['name']
예제 #11
0
    def __len__(self):
        if self.length is None:
            with db.DatabaseConnection() as connection:
                with db.DatabaseCursor(connection) as cursor:
                    sql = ["SELECT ST_LENGTH(segment) * 69 AS length", "FROM reading", "WHERE id = {0}"]
                    sql = db.process(sql, self.nid)

                    cursor.execute(sql)
                    if cursor.rowcount == 1:
                        row = cursor.fetchone()
                        self.length = float(row["length"])
                    else:
                        self.length = -1

        return self.length
예제 #12
0
    def __get_name(self, connection):
        sql = [
            'SELECT name',
            'FROM operational',
            'WHERE id = {0}',
        ]
        sql = db.process(sql, [self.nid])

        with db.DatabaseCursor(connection) as cursor:
            cursor.execute(sql)
            if cursor.rowcount != 1:
                err = '{0} does not exist!'.format(self.nid)
                raise AttributeError(err)
            row = cursor.fetchone()

        return row['name']
예제 #13
0
    def __get_readings(self, connection, speed_threshold=None):
        sql = ["SELECT as_of, speed, travel_time / {1} AS travel", "FROM reading", "WHERE node = {0}"]

        # speed_threshold is intended to remove outliers. If
        # specified, it will restrict the results to speeds that are
        # that percentage above the New York speed limit (65 mph).
        if speed_threshold is not None:
            s = "AND speed < {0}".format(65 * (1 + speed_threshold))
            sql.append(s)

        sql.append("ORDER BY as_of ASC")

        sql = db.process(sql, self.nid, constant.minute)

        data = pd.read_sql_query(sql, con=connection, index_col="as_of")
        data.columns = ["speed", "travel"]

        return data.resample(self.freq).mean() if self.freq else data
예제 #14
0
    def __init__(self, k=4):
        super().__init__()

        fname = '/Users/jerome/nyc/src/cluster/data/speed/dat.csv'
        self.df = pd.read_csv(fname, index_col='nid')
        self.df = self.df[k]
        
        color = Color()
        assert(all([ x in self.df.unique() for x in range(self.df.max()) ]))
        # self.pallete = [ color.unique() for _ in self.df.unique() ]
        self.pallete = [
            'red',
            'blue',
            'green',
            'orange',
            ]

        sql = [ 'SELECT id, ST_ASTEXT(segment) AS segment',
                'FROM node',
                'WHERE segment IS NOT NULL',
                'AND id IN ({0})'
        ]
        self.sql = db.process(sql, ','.join(map(str, self.df.index)))
        print(self.sql)
예제 #15
0
from lib import db

#
# Removes values prior to 7 November 2014. Such values will
# occasionally be reported, which are erroneous.
#
sql = [
    'DELETE FROM reading',
    "WHERE as_of < '{0}'",
]
sql = db.process(sql, ['2014-11-07'])

db.EstablishCredentials(user='******')
with db.DatabaseConnection() as connection:
    with db.DatabaseCursor(connection) as cursor:
        cursor.execute(sql)
예제 #16
0
 def __init__(self):
     sql = [ 'SELECT id, ST_ASTEXT(segment) AS segment',
             'FROM node',
             'WHERE segment IS NOT NULL',
     ]
     self.sql = db.process(sql)
예제 #17
0
from lib import db

#
# Removes values prior to 7 November 2014. Such values will
# occasionally be reported, which are erroneous.
#
sql = [
    'DELETE FROM reading',
    "WHERE as_of < '{0}'",
]
sql = db.process(sql, '2014-11-07')

db.EstablishCredentials(user='******')
with db.DatabaseConnection() as connection:
    with db.DatabaseCursor(connection) as cursor:
        cursor.execute(sql)
예제 #18
0
# import matplotlib
# matplotlib.style.use('ggplot')
import matplotlib.pyplot as plt
plt.style.use('ggplot')

from lib import db
from lib import utils

with db.DatabaseConnection() as con:
    sql = [
        'SELECT frequency / {0} AS freq',
        'FROM quality',
        'ORDER BY frequency ASC',
    ]
    sql = db.process(sql, [ constant.minute ])
    df = pd.read_sql_query(sql, con=con)

df['dist'] = df.apply(lambda x: (x.index + 1) / len(df))

args = {
    'xlim': (1, 5),
    'ylim': (0, 1),
    'yticks': np.linspace(0, 1, 11),
    'legend': False
    }
plot = df.plot(x='freq', y='dist', **args)
plot.set_xlabel('Reporting frequency (min)')
plot.set_ylabel('Fraction of segments')

utils.mkplot_(plot, 'reporting.png')
예제 #19
0
#
# Open and parse the data file
#
with open(args.input, mode='rb') as fp:
    data = pickle.load(fp)
    
keys = []
values = []
for i in data:
    if not keys:
        keys = i.keys()
    values.append([ i[x] for x in keys ])
assert(keys and values)

#
# Create the SQL statement and execute!
#
s = [ '%s' ] * len(keys)
opts = [ ','.join(x) for x in (keys, s) ]
sql = [
    'INSERT IGNORE INTO reading ({0})',
    'VALUES ({1})'
]
sql = db.process(sql, opts)

db.EstablishCredentials(user='******')
with db.DatabaseConnection() as connection:
    with db.DatabaseCursor(connection) as cursor:
        # http://stackoverflow.com/a/18245311
        cursor.executemany(sql, values)
예제 #20
0
cargs = cli.CommandLine(cli.optsfile('storage'))
args = cargs.args

#
# Open and parse the data file
#
with open(args.input, mode='rb') as fp:
    data = pickle.load(fp)

keys = []
values = []
for i in data:
    if not keys:
        keys = i.keys()
    values.append([i[x] for x in keys])
assert (keys and values)

#
# Create the SQL statement and execute!
#
s = ['%s'] * len(keys)
opts = [','.join(x) for x in (keys, s)]
sql = ['INSERT IGNORE INTO reading ({0})', 'VALUES ({1})']
sql = db.process(sql, opts)

db.EstablishCredentials(user='******')
with db.DatabaseConnection() as connection:
    with db.DatabaseCursor(connection) as cursor:
        # http://stackoverflow.com/a/18245311
        cursor.executemany(sql, values)
예제 #21
0
from lib import db

#
# Removes values prior to 7 November 2014. Such values will
# occasionally be reported, which are erroneous.
#
sql = [
    'DELETE FROM reading',
    "WHERE as_of < '{0}'",
]
sql = db.process(sql, [ '2014-11-07' ])

db.EstablishCredentials(user='******')
with db.DatabaseConnection() as connection:
    with db.DatabaseCursor(connection) as cursor:
        cursor.execute(sql)