def on_get(self, req, resp):
        params = req.params

        try:
            x_min = float(params['x_min'])
            x_max = float(params['x_max'])
            y_min = float(params['y_min'])
            y_max = float(params['y_max'])
        except ValueError:
            resp.status = falcon.HTTP_404
            return

        x_gap_max = 1.5
        y_gap_max = 0.5
        x_min, x_max, y_min, y_max = limit_range(x_min, x_max, y_min, y_max,
                                                 x_gap_max, y_gap_max)

        connection = psycopg2.connect(dbname=database,
                                      user=database_user,
                                      password=database_password)
        cursor = connection.cursor()
        postgis.register(cursor)

        cursor.execute(
            """
            select commune, class_0_sum, class_1_sum, class_2_sum, class_3_sum, total, nom, wikipedia, surf_m2, geom
            from commune_merge
            where geom && st_makeenvelope(%s, %s, %s, %s, 4326);
            """, (x_min, y_min, x_max, y_max))

        data = cursor.fetchall()

        cursor.close()
        connection.close()

        features = []
        for i, data_commune in enumerate(data):
            features.append({
                'type': 'Feature',
                'properties': {
                    'commune': data_commune[0],
                    'class_0_sum': data_commune[1],
                    'class_1_sum': data_commune[2],
                    'class_2_sum': data_commune[3],
                    'class_3_sum': data_commune[4],
                    'total': data_commune[5],
                    'nom': data_commune[6],
                    'wikipedia': data_commune[7],
                    'surf_m2': float(data_commune[8]),
                },
                'geometry': data_commune[9].geojson
            })

        resp.status = falcon.HTTP_200
        resp.set_header('Access-Control-Allow-Origin', '*')
        resp.body = json.dumps({
            'type': 'FeatureCollection',
            'features': features,
        })
    def on_get(self, req, resp):
        if not os.path.isfile(departement_stats_filename):
            connection = psycopg2.connect(dbname=database,
                                          user=database_user,
                                          password=database_password)
            cursor = connection.cursor()
            postgis.register(cursor)

            cursor.execute("""
                select departement, class_0_sum, class_1_sum, class_2_sum, class_3_sum, total, nom, nuts3, wikipedia, geom
                from departement_merge;
                """)

            data = cursor.fetchall()

            cursor.close()
            connection.close()

            features = []
            for i, data_departement in enumerate(data):
                geom = data_departement[9]
                if not geom:
                    continue
                features.append({
                    'type': 'Feature',
                    'properties': {
                        'departement': data_departement[0],
                        'class_0_sum': data_departement[1],
                        'class_1_sum': data_departement[2],
                        'class_2_sum': data_departement[3],
                        'class_3_sum': data_departement[4],
                        'total': data_departement[5],
                        'nom': data_departement[6],
                        'nuts3': data_departement[7],
                        'wikipedia': data_departement[8],
                    },
                    'geometry': geom.geojson
                })

            content = json.dumps({
                'type': 'FeatureCollection',
                'features': features,
            })

            with open(departement_stats_filename, 'w') as f:
                f.write(content)

        with open(departement_stats_filename, 'r') as f:
            content = f.read()

        resp.status = falcon.HTTP_200
        resp.set_header('Access-Control-Allow-Origin', '*')
        resp.body = content
Beispiel #3
0
 def __getattr__(self, attr):
     if not self.obj:
         db = PostgresqlExtDatabase(
             self.prefix + config.DB_NAME,
             user=config.get('DB_USER'),
             password=config.get('DB_PASSWORD'),
             host=config.get('DB_HOST'),
             port=config.get('DB_PORT'),
             autorollback=True,
         )
         self.initialize(db)
         postgis.register(self.obj.get_cursor())
     return getattr(self.obj, attr)
    def run(self):
        print "my_thread_id:", self.threadID
        #conn = psycopg2.connect(PSQL_URI)
        #cur = conn.cursor()
        register(self.cur)
        for time_start in self.time_start_list:
            file_name = self.file_path + 'minute_' + str(
                time_start) + '.geojson'
            if os.path.exists(file_name):
                with open(file_name) as f:
                    data = json.load(f)
                insert_data = []
                insert_string_block = "INSERT INTO " + table_name + \
                                "(gid, unix_time_start, interval_in_second, speed_median, speed_mean, car_cnt_20mins, class) " + \
                                            "VALUES "
                uinx_time_start = zero_oclock + (time_start - 1) * 60
                for feature in data['features']:
                    if len(feature['geometry']['coordinates']) > 1:
                        gid = feature['properties']['gid']
                        speed_median = feature['properties']['median_speed']
                        speed_mean = feature['properties']['mean_speed']
                        car_cnt = feature['properties']['car_cnt']
                        class_osm = int(float(
                            (feature['properties']['class'])))

                        insert_data.append(
                            (gid, uinx_time_start, 60 * TIME_WINDOW,
                             speed_median, speed_mean, car_cnt, class_osm))
                if len(insert_data) > 0:
                    try:
                        """
                        self.cur.execute(insert_string,insert_data)
                        self.conn.commit()
                        """
                        args_str = ','.join(
                            self.cur.mogrify("(%s,%s,%s,%s,%s,%s,%s)", x)
                            for x in insert_data)
                        self.cur.execute(insert_string_block + args_str)
                        self.conn.commit()
                    except:
                        # Rollback in case there is any error
                        e, t, tb = sys.exc_info()
                        print "caught", e, t, tb
                        self.conn.rollback()

        print "Insert done! Thread id = ", self.threadID
        self.cur.close()
        self.conn.close()
Beispiel #5
0
 def initialize_connection(self, conn):
     if not self.postgis_registered:
         postgis.register(conn.cursor())
         self.postgis_registered = True
    def run(self):
        print "my_thread_id:", self.threadID
        #conn = psycopg2.connect(PSQL_URI)
        #cur = conn.cursor()
        register(self.cur)
        for car_id in self.car_list:
            file_name = self.file_path + 'one_car_traj_' + str(
                car_id) + '.geojson'
            if os.path.exists(file_name):
                if car_id % 100 == 0:
                    print "car_id:", car_id, ","
                with open(file_name) as f:
                    data = json.load(f)
                insert_data = []
                insert_string_block = "INSERT INTO " + table_name + \
                                "(unix_time, car_id, time_string, osm_id, gid, percent, unix_time_pre, gid_pre, pick_or_drop, speed,date, the_geom) " + \
                                            "VALUES "
                for feature in data['features']:
                    if len(feature['geometry']['coordinates']) > 1:
                        try:
                            dist = feature['properties']['Dist']
                        except:
                            continue
                        time_current = feature['properties']['ABSTIME']
                        time_current_string = feature['properties']['TIME']
                        osm_id = feature['properties']['OSM_ID']
                        gid_current = feature['properties']['GID']
                        percent_current = feature['properties']['Percent']
                        #percent_pre = feature['properties']['Percent_pre']
                        gid_pre = feature['properties']['GID_pre']
                        pick_or_drop = feature['properties']['Pick_or_drop']
                        time_pre = feature['properties']["ABS_TIME_PRE"]
                        coors = feature['geometry']['coordinates']
                        line_seg = []
                        for item in coors:
                            line_seg.append(tuple(item))

                        insert_string = "INSERT INTO " + table_name + \
                                "(unix_time, car_id, time_string, osm_id, gid, percent, unix_time_pre, gid_pre, pick_or_drop, speed,date, the_geom) " + \
                                            "VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
                        """
                        insert_data = (time_current,car_id,time_current_string,osm_id,gid_current,
                                       percent_current,time_pre,gid_pre,pick_or_drop,dist/(time_current - time_pre),date,
                                       LineString(line_seg, srid=4326))
                                       """
                        insert_data.append(
                            (time_current, car_id, time_current_string, osm_id,
                             gid_current, percent_current, time_pre, gid_pre,
                             pick_or_drop, dist / (time_current - time_pre),
                             date, LineString(line_seg, srid=4326)))
                if len(insert_data) > 0:
                    try:
                        """
                        self.cur.execute(insert_string,insert_data)
                        self.conn.commit()
                        """
                        args_str = ','.join(
                            self.cur.mogrify(
                                "(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", x)
                            for x in insert_data)
                        self.cur.execute(insert_string_block + args_str)
                        self.conn.commit()
                    except:
                        # Rollback in case there is any error
                        e, t, tb = sys.exc_info()
                        print "caught", e, t, tb
                        self.conn.rollback()

        print "Insert done! Thread id = ", self.threadID
        self.cur.close()
        self.conn.close()
Beispiel #7
0
database_host = config['main']['database_host']
database_port = config['main']['database_port']
database_name = config['main']['database_name']
database_username = config['main']['database_username']
database_password = config['main']['database_password']
assert database_host == 'localhost'
assert database_port == '1234'

nb_hours = int(sys.argv[1])
nb_worker = int(sys.argv[2])
id_worker = int(sys.argv[3])

duration_in_seconds = nb_hours * 3600
start_time = time.time()
end_time = start_time + duration_in_seconds

# Open connection
connection = psycopg2.connect(dbname=database_name,
                              user=database_username,
                              password=database_password)
cursor = connection.cursor()
postgis.register(cursor)

cursor.execute('set enable_seqscan = off;')

while (time.time() < end_time):
    compute_angle.process_buildings(connection, cursor, nb_worker, id_worker)

cursor.close()
connection.close()
    def on_get(self, req, resp):
        params = req.params

        try:
            x_min = float(params['x_min'])
            x_max = float(params['x_max'])
            y_min = float(params['y_min'])
            y_max = float(params['y_max'])
        except ValueError:
            resp.status = falcon.HTTP_404
            return

        x_gap_max = 0.5
        y_gap_max = 0.1
        x_min, x_max, y_min, y_max = limit_range(x_min, x_max, y_min, y_max,
                                                 x_gap_max, y_gap_max)

        connection = psycopg2.connect(dbname=database,
                                      user=database_user,
                                      password=database_password)
        cursor = connection.cursor()
        postgis.register(cursor)

        cursor.execute(
            """
            select id_osm, geom, class_0, class_1, class_2, class_3
            from buildings
            where geom && st_makeenvelope(%s, %s, %s, %s, 4326);
            """, (x_min, y_min, x_max, y_max))

        data = cursor.fetchall()

        cursor.close()
        connection.close()

        if len(data) != 0:
            id_osm, geom, class_0, class_1, class_2, class_3 = zip(*data)
            probas = np.transpose(
                np.array([class_0, class_1, class_2, class_3], dtype=np.float))
            unknown_mask = np.isnan(probas).sum(1).astype(np.bool)
            probas_num = np.nan_to_num(probas)
            class_max = np.argmax(probas_num, axis=1)
            class_max_score = np.max(probas_num, axis=1)
            class_max_score
            class_estimation = class_max * (class_max_score > 0.5) - (
                class_max_score <= 0.5)
            class_estimation[unknown_mask] = -2

        features = []
        for i in range(len(data)):
            features.append({
                'type': 'Feature',
                'properties': {
                    'id_osm': id_osm[i],
                    'class_0': probas[i, 0],
                    'class_1': probas[i, 1],
                    'class_2': probas[i, 2],
                    'class_3': probas[i, 3],
                    'class_estimation': int(class_estimation[i]),
                },
                'geometry': geom[i].geojson
            })

        resp.status = falcon.HTTP_200
        resp.set_header('Access-Control-Allow-Origin', '*')
        resp.body = json.dumps({
            'type': 'FeatureCollection',
            'features': features,
        })
Beispiel #9
0
import psycopg2

import pytest
from postgis import (GeometryCollection, LineString, MultiLineString,
                     MultiPoint, MultiPolygon, Point, Polygon, register)

db = psycopg2.connect(dbname="test")
cur = db.cursor()
register(db)

geoms = [
    Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon,
    GeometryCollection
]


def pytest_configure(config):
    tpl = 'CREATE TABLE IF NOT EXISTS {} ("geom" geometry({}) NOT NULL)'
    for geom in geoms:
        name = geom.__name__
        cur.execute(tpl.format(name.lower(), name))


def pytest_unconfigure(config):
    for geom in geoms:
        cur.execute('DROP TABLE {}'.format(geom.__name__.lower()))
    db.commit()
    db.close()


@pytest.fixture