def upgrade(): op.create_table( 'administrative_regions', sa.Column('id', sa.BIGINT, primary_key=True), sa.Column('name', sa.TEXT, nullable=False), sa.Column('uri', sa.TEXT, nullable=False), sa.Column('post_code', sa.TEXT), sa.Column('insee', sa.TEXT), sa.Column('level', sa.INTEGER), sa.Column('coord', ga.Geography(geometry_type='POINT', srid=4326, spatial_index=False)), sa.Column('boundary', ga.Geography(geometry_type='MULTIPOLYGON', srid=4326, spatial_index=False)), )
def upgrade(): op.add_column('line', sa.Column('shape', ga.Geography(geometry_type='MULTILINESTRING', srid=4326, spatial_index=False), nullable=True), schema='navitia') op.add_column('route', sa.Column('shape', ga.Geography(geometry_type='MULTILINESTRING', srid=4326, spatial_index=False), nullable=True), schema='navitia')
def upgrade(): op.create_table( 'access_point', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('name', sa.TEXT(), nullable=False), sa.Column('coord', ga.Geography(geometry_type='POINT', srid=4326, spatial_index=False), nullable=True), sa.Column('stop_code', sa.TEXT(), nullable=True), sa.Column('parent_station', sa.TEXT(), nullable=True), sa.PrimaryKeyConstraint('id'), schema='navitia', ) op.create_table( 'pathway', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('name', sa.TEXT(), nullable=False), sa.Column('from_stop_id', sa.TEXT(), nullable=False), sa.Column('to_stop_id', sa.TEXT(), nullable=False), sa.Column('pathway_mode', sa.INTEGER(), nullable=False), sa.Column('is_bidirectional', sa.BOOLEAN(), nullable=False), sa.Column('length', sa.INTEGER(), nullable=True), sa.Column('traversal_time', sa.INTEGER(), nullable=True), sa.Column('stair_count', sa.INTEGER(), nullable=True), sa.Column('max_slope', sa.INTEGER(), nullable=True), sa.Column('min_width', sa.INTEGER(), nullable=True), sa.Column('signposted_as', sa.TEXT(), nullable=True), sa.Column('reversed_signposted_as', sa.TEXT(), nullable=True), sa.PrimaryKeyConstraint('id'), schema='navitia', )
def upgrade(): op.add_column('journey_pattern_point', sa.Column('shape_from_prev', ga.Geography(geometry_type='LINESTRING', srid=4326, spatial_index=False), nullable=True), schema='navitia')
class Showing(db.Model): """The Cinema/Channel table. """ __tablename__ = 'showing' id = db.Column(db.BigInteger, autoincrement=True, primary_key=True) name = db.Column(db.String, nullable=False) type = db.Column(db.String, nullable=False) website = db.Column(db.String) description = db.Column(db.String) phone = db.Column(db.String) google_place_id = db.Column(db.String) last_modified = db.Column(db.DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow) geometry = db.Column(ga.Geography('POINT', srid=4326, spatial_index=False)) showings = db.relationship('MovieShowing', backref='cinema', lazy='dynamic', cascade='save-update, merge, delete') movies = db.relationship('Movie', secondary='showing_movie', back_populates='showing_cinemas', lazy='dynamic') def __init__(self, **kwargs): self.type = kwargs.get('type') self.name = kwargs.get('name') self.phone = kwargs.get('phone') self.website = kwargs.get('website') self.description = kwargs.get('description') self.geometry = kwargs.get('geometry') self.google_place_id = kwargs.get('google_place_id') @classmethod def get_kwargs(self, request): geometry = Point(float(request.form.get('lng')), float(request.form.get('lat'))) return dict(name=request.form.get('name'), phone=request.form.get('phone'), website=request.form.get('website'), description=request.form.get('description'), google_place_id=request.form.get('google_place_id'), geometry=geometry.wkt) @property def serialize(self): point = wkb.loads(bytes(self.geometry.data)) lng, lat = point.x, point.y return dict(id=self.id, name=self.name, phone=self.phone, website=self.website, description=self.description, lat=lat, lng=lng)
def downgrade(): op.drop_column('stop_time', 'shape_from_prev_id', schema='navitia') op.add_column('stop_time', sa.Column('shape_from_prev', ga.Geography(geometry_type='LINESTRING', srid=4326, spatial_index=False), nullable=True), schema='navitia') op.drop_table('shape', schema='navitia')
class Region(db.Model): __tablename__ = 'regions' id = db.Column(db.Integer, primary_key=True) geog = db.Column(geo.Geography(geometry_type='POLYGON', srid='4326')) zone_id = db.Column(db.Integer, db.ForeignKey('zones.id')) zone = db.relationship("Zone") def __init__(self, geog, zone_id): self.geog = geog self.zone_id = zone_id
def upgrade(): op.add_column('journey_request', sa.Column('departure_admin_name', sa.Text()), schema='stat') op.add_column('journey_request', sa.Column('arrival_admin_name', sa.Text()), schema='stat') op.add_column('journey_sections', sa.Column('from_admin_insee', sa.Text()), schema='stat') op.add_column('journey_sections', sa.Column('to_admin_insee', sa.Text()), schema='stat') op.add_column('journeys', sa.Column('first_pt_id', sa.Text()), schema='stat') op.add_column('journeys', sa.Column('first_pt_name', sa.Text()), schema='stat') op.add_column('journeys', sa.Column('first_pt_coord', ga.Geography(geometry_type='POINT', srid=4326, spatial_index=False), nullable=True), schema='stat') op.add_column('journeys', sa.Column('first_pt_admin_id', sa.Text()), schema='stat') op.add_column('journeys', sa.Column('first_pt_admin_name', sa.Text()), schema='stat') op.add_column('journeys', sa.Column('first_pt_admin_insee', sa.Text()), schema='stat') op.add_column('journeys', sa.Column('last_pt_id', sa.Text()), schema='stat') op.add_column('journeys', sa.Column('last_pt_name', sa.Text()), schema='stat') op.add_column('journeys', sa.Column('last_pt_coord', ga.Geography(geometry_type='POINT', srid=4326, spatial_index=False), nullable=True), schema='stat') op.add_column('journeys', sa.Column('last_pt_admin_id', sa.Text()), schema='stat') op.add_column('journeys', sa.Column('last_pt_admin_name', sa.Text()), schema='stat') op.add_column('journeys', sa.Column('last_pt_admin_insee', sa.Text()), schema='stat')
def upgrade(): op.add_column('ZUPC', sa.Column('insee', sa.String(), nullable=True)) op.add_column('ZUPC', sa.Column('parent_id', sa.Integer(), nullable=True)) op.create_foreign_key(parent_zupc, 'ZUPC', 'ZUPC', ['parent_id'], ['id']) op.drop_column('ZUPC', 'shape') op.add_column( 'ZUPC', sa.Column( 'shape', ga.Geography(geometry_type='MULTIPOLYGON', srid=4326, spatial_index=False)))
def create_journey_sections(): op.create_table( 'journey_sections', sa.Column('id', sa.Integer(), primary_key=True, nullable=False), sa.Column('departure_date_time', sa.DateTime(), nullable=False), sa.Column('arrival_date_time', sa.DateTime(), nullable=False), sa.Column('duration', sa.Integer()), sa.Column('mode', sa.Text()), sa.Column('type', sa.Text()), sa.Column('from_embedded_type', sa.Text()), sa.Column('from_id', sa.Text()), sa.Column('from_name', sa.Text()), sa.Column('from_coord', ga.Geography(geometry_type='POINT', srid=4326, spatial_index=False), nullable=True), sa.Column('from_admin_id', sa.Text()), sa.Column('from_admin_name', sa.Text()), sa.Column('to_embedded_type', sa.Text()), sa.Column('to_id', sa.Text()), sa.Column('to_name', sa.Text()), sa.Column('to_coord', ga.Geography(geometry_type='POINT', srid=4326, spatial_index=False), nullable=True), sa.Column('to_admin_id', sa.Text()), sa.Column('to_admin_name', sa.Text()), sa.Column('vehicle_journey_id', sa.Text()), sa.Column('line_id', sa.Text()), sa.Column('line_code', sa.Text()), sa.Column('route_id', sa.Text()), sa.Column('network_id', sa.Text()), sa.Column('network_name', sa.Text()), sa.Column('commercial_mode_id', sa.Text()), sa.Column('commercial_mode_name', sa.Text()), sa.Column('physical_mode_id', sa.Text()), sa.Column('physical_mode_name', sa.Text()), sa.Column('request_id', sa.BigInteger(), nullable=False), sa.Column('journey_id', sa.BigInteger(), nullable=False), sa.ForeignKeyConstraint(['request_id'], ['stat.requests.id'],), sa.ForeignKeyConstraint(['journey_id'], ['stat.journeys.id'],), schema='stat' )
def upgrade(): op.add_column( 'stop_point', sa.Column('is_zonal', sa.BOOLEAN(), primary_key=False, nullable=False, server_default='false'), schema='navitia', ) op.add_column( 'stop_point', sa.Column( 'area', ga.Geography(geometry_type='MULTIPOLYGON', srid=4326, spatial_index=False), primary_key=False, nullable=True, ), schema='navitia', )
def upgrade(): op.create_table('shape', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('geom', ga.Geography(geometry_type='LINESTRING', srid=4326, spatial_index=False), nullable=True), sa.PrimaryKeyConstraint('id'), schema='navitia') op.drop_column('stop_time', 'shape_from_prev', schema='navitia') op.add_column('stop_time', sa.Column('shape_from_prev_id', sa.BIGINT(), nullable=True), schema='navitia') op.create_foreign_key('fk_stop_time_shape', 'stop_time', 'shape', ['shape_from_prev_id'], ['id'], referent_schema='navitia', source_schema='navitia')
class City(Base): __tablename__ = 'city' id = sa.Column(sa.Integer, primary_key=True) name = sa.Column(sa.String(100), nullable=False) region = sa.Column(sa.String(100), nullable=False) country = sa.Column(sa.String(100), nullable=False) location = sa.Column(ga.Geography(geometry_type='POINT', srid=4326)) # total population (the smallest if there are multiple like # city/urban/metro, etc.) population = sa.Column(sa.Integer) # elevation in meters elevation = sa.Column(sa.Float) # (wikipedia) source source = sa.Column(sa.String(200)) # order in which we will select the cities priority_index = sa.Column(sa.Integer) # rank of the city within it's region/country. I use it to calculate the # priority index region_rank = sa.Column(sa.Integer, default=1e9) country_rank = sa.Column(sa.Integer, default=1e9)
def upgrade(): op.drop_constraint('journey_pattern_route_id_fkey', 'journey_pattern', schema='navitia') op.drop_constraint('journey_pattern_physical_mode_id_fkey', 'journey_pattern', schema='navitia') op.drop_constraint('vehicle_journey_journey_pattern_id_fkey', 'vehicle_journey', schema='navitia') op.drop_constraint('stop_time_journey_pattern_point_id_fkey', 'stop_time', schema='navitia') op.drop_table('journey_pattern_point', schema='navitia') op.drop_table('journey_pattern', schema='navitia') op.add_column('stop_time', sa.Column('order', sa.INTEGER(), nullable=True), schema='navitia') op.add_column( 'stop_time', sa.Column( 'shape_from_prev', ga.Geography(geometry_type='LINESTRING', srid=4326, spatial_index=False), nullable=True, ), schema='navitia', ) op.add_column('stop_time', sa.Column('stop_point_id', sa.BIGINT(), nullable=True), schema='navitia') op.drop_column('stop_time', 'journey_pattern_point_id', schema='navitia') op.add_column('vehicle_journey', sa.Column('physical_mode_id', sa.BIGINT(), nullable=True), schema='navitia') op.add_column('vehicle_journey', sa.Column('route_id', sa.BIGINT(), nullable=True), schema='navitia') op.drop_column('vehicle_journey', 'journey_pattern_id', schema='navitia')
def upgrade(): op.add_column( 'zones', sa.Column('geog', geo.Geography(geometry_type='MULTIPOLYGON', srid=4326)))
def downgrade(): op.add_column( 'vehicle_journey', sa.Column('journey_pattern_id', sa.BIGINT(), autoincrement=False, nullable=False), schema='navitia', ) op.drop_column('vehicle_journey', 'route_id', schema='navitia') op.drop_column('vehicle_journey', 'physical_mode_id', schema='navitia') op.add_column( 'stop_time', sa.Column('journey_pattern_point_id', sa.BIGINT(), autoincrement=False, nullable=False), schema='navitia', ) op.drop_column('stop_time', 'stop_point_id', schema='navitia') op.drop_column('stop_time', 'shape_from_prev', schema='navitia') op.drop_column('stop_time', 'order', schema='navitia') op.create_table( 'journey_pattern', sa.Column('id', sa.BIGINT(), primary_key=True, nullable=False), sa.Column('route_id', sa.BIGINT(), primary_key=False, nullable=False), sa.Column('physical_mode_id', sa.BIGINT(), primary_key=False, nullable=False), sa.Column('uri', sa.TEXT(), primary_key=False, nullable=False), sa.Column('name', sa.TEXT(), primary_key=False, nullable=False), sa.Column('is_frequence', sa.BOOLEAN(), primary_key=False, nullable=False), sa.ForeignKeyConstraint(['route_id'], [u'navitia.route.id'], name=u'journey_pattern_route_id_fkey'), sa.ForeignKeyConstraint(['physical_mode_id'], [u'navitia.physical_mode.id'], name=u'journey_pattern_physical_mode_id_fkey'), schema='navitia', ) op.create_table( 'journey_pattern_point', sa.Column('id', sa.BIGINT(), primary_key=True, nullable=False), sa.Column('journey_pattern_id', sa.BIGINT(), primary_key=False, nullable=False), sa.Column('name', sa.TEXT(), primary_key=False, nullable=False), sa.Column('uri', sa.TEXT(), primary_key=False, nullable=False), sa.Column('order', sa.INTEGER(), primary_key=False, nullable=False), sa.Column('stop_point_id', sa.BIGINT(), primary_key=False, nullable=False), sa.Column( 'shape_from_prev', ga.Geography(geometry_type='LINESTRING', srid=4326, spatial_index=False), primary_key=False, ), sa.ForeignKeyConstraint( ['stop_point_id'], [u'navitia.stop_point.id'], name=u'journey_pattern_point_stop_point_id_fkey'), sa.ForeignKeyConstraint( ['journey_pattern_id'], [u'navitia.journey_pattern.id'], name=u'journey_pattern_point_journey_pattern_id_fkey', ), schema='navitia', )
def upgrade(): op.execute("CREATE SCHEMA navitia;") op.execute("CREATE SCHEMA georef;") op.execute("CREATE SCHEMA realtime;") op.create_table('connection_type', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('name', sa.TEXT(), nullable=False), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('vehicle_properties', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('wheelchair_accessible', sa.BOOLEAN(), nullable=False), sa.Column('bike_accepted', sa.BOOLEAN(), nullable=False), sa.Column('air_conditioned', sa.BOOLEAN(), nullable=False), sa.Column('visual_announcement', sa.BOOLEAN(), nullable=False), sa.Column('audible_announcement', sa.BOOLEAN(), nullable=False), sa.Column('appropriate_escort', sa.BOOLEAN(), nullable=False), sa.Column('appropriate_signage', sa.BOOLEAN(), nullable=False), sa.Column('school_vehicle', sa.BOOLEAN(), nullable=False), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('parameters', sa.Column('beginning_date', sa.DATE(), nullable=True), sa.Column('end_date', sa.DATE(), nullable=True), sa.Column('timezone', sa.TEXT(), nullable=True), sa.Column('shape', ga.Geography(geometry_type='POLYGON', srid=4326, spatial_index=False), nullable=True), sa.Column('shape_computed', sa.BOOLEAN(), nullable=True, server_default="TRUE"), schema='navitia' ) op.create_table('meta_vj', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('name', sa.TEXT(), nullable=False), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('commercial_mode', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('name', sa.TEXT(), nullable=False), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('properties', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('wheelchair_boarding', sa.BOOLEAN(), nullable=False), sa.Column('sheltered', sa.BOOLEAN(), nullable=False), sa.Column('elevator', sa.BOOLEAN(), nullable=False), sa.Column('escalator', sa.BOOLEAN(), nullable=False), sa.Column('bike_accepted', sa.BOOLEAN(), nullable=False), sa.Column('bike_depot', sa.BOOLEAN(), nullable=False), sa.Column('visual_announcement', sa.BOOLEAN(), nullable=False), sa.Column('audible_announcement', sa.BOOLEAN(), nullable=False), sa.Column('appropriate_escort', sa.BOOLEAN(), nullable=False), sa.Column('appropriate_signage', sa.BOOLEAN(), nullable=False), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('poi_type', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('name', sa.TEXT(), nullable=False), sa.PrimaryKeyConstraint('id'), schema='georef' ) op.create_table('connection_kind', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('name', sa.TEXT(), nullable=False), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('network', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('comment', sa.TEXT(), nullable=True), sa.Column('name', sa.TEXT(), nullable=False), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('external_code', sa.TEXT(), nullable=False), sa.Column('sort', sa.INTEGER(), nullable=False), sa.Column('website', sa.TEXT(), nullable=True), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('admin', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('name', sa.TEXT(), nullable=False), sa.Column('comment', sa.TEXT(), nullable=True), sa.Column('post_code', sa.TEXT(), nullable=True), sa.Column('insee', sa.TEXT(), nullable=True), sa.Column('level', sa.INTEGER(), nullable=False), sa.Column('coord', ga.Geography(geometry_type='POINT', srid=4326, spatial_index=False), nullable=True), sa.Column('boundary', ga.Geography(geometry_type='MULTIPOLYGON', srid=4326, spatial_index=False), nullable=True), sa.Column('uri', sa.TEXT(), nullable=False), sa.PrimaryKeyConstraint('id'), schema='georef' ) op.create_table('ticket', sa.Column('ticket_key', sa.TEXT(), nullable=False), sa.Column('ticket_title', sa.TEXT(), nullable=True), sa.Column('ticket_comment', sa.TEXT(), nullable=True), sa.PrimaryKeyConstraint('ticket_key'), schema='navitia' ) op.create_table('week_pattern', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('monday', sa.BOOLEAN(), nullable=False), sa.Column('tuesday', sa.BOOLEAN(), nullable=False), sa.Column('wednesday', sa.BOOLEAN(), nullable=False), sa.Column('thursday', sa.BOOLEAN(), nullable=False), sa.Column('friday', sa.BOOLEAN(), nullable=False), sa.Column('saturday', sa.BOOLEAN(), nullable=False), sa.Column('sunday', sa.BOOLEAN(), nullable=False), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('way', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('name', sa.TEXT(), nullable=False), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('type', sa.TEXT(), nullable=True), sa.PrimaryKeyConstraint('id'), schema='georef' ) op.create_table('physical_mode', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('name', sa.TEXT(), nullable=False), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('node', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('coord', ga.Geography(geometry_type='POINT', srid=4326, spatial_index=False), nullable=True), sa.PrimaryKeyConstraint('id'), schema='georef' ) op.create_table('company', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('comment', sa.TEXT(), nullable=True), sa.Column('name', sa.TEXT(), nullable=False), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('address_name', sa.TEXT(), nullable=True), sa.Column('address_number', sa.TEXT(), nullable=True), sa.Column('address_type_name', sa.TEXT(), nullable=True), sa.Column('phone_number', sa.TEXT(), nullable=True), sa.Column('mail', sa.TEXT(), nullable=True), sa.Column('website', sa.TEXT(), nullable=True), sa.Column('fax', sa.TEXT(), nullable=True), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('message_status', sa.Column('id', sa.INTEGER(), nullable=False), sa.Column('name', sa.TEXT(), nullable=False), sa.PrimaryKeyConstraint('id'), schema='realtime' ) op.create_table('contributor', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('name', sa.TEXT(), nullable=False), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('odt_type', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('name', sa.TEXT(), nullable=False), sa.PrimaryKeyConstraint('id'), schema='navitia' ) fare_od_mode = postgresql.ENUM(u'Zone', u'StopArea', u'Mode', name='fare_od_mode') op.create_table('origin_destination', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('origin_id', sa.TEXT(), nullable=False), sa.Column('origin_mode', fare_od_mode, nullable=False), sa.Column('destination_id', sa.TEXT(), nullable=False), sa.Column('destination_mode', fare_od_mode, nullable=False), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('synonym', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('key', sa.TEXT(), nullable=False), sa.Column('value', sa.TEXT(), nullable=False), sa.PrimaryKeyConstraint('id'), schema='georef' ) op.create_table('object_type', sa.Column('id', sa.INTEGER(), nullable=False), sa.Column('name', sa.TEXT(), nullable=False), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('validity_pattern', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('days', postgresql.BIT(length=400, varying=True), nullable=False), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('calendar', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('external_code', sa.TEXT(), nullable=False), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('name', sa.TEXT(), nullable=False), sa.Column('week_pattern_id', sa.BIGINT(), nullable=False), sa.ForeignKeyConstraint(['week_pattern_id'], [u'navitia.week_pattern.id'], name=u'calendar_week_pattern_id_fkey'), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('dated_ticket', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('ticket_id', sa.TEXT(), nullable=True), sa.Column('valid_from', sa.DATE(), nullable=False), sa.Column('valid_to', sa.DATE(), nullable=False), sa.Column('ticket_price', sa.INTEGER(), nullable=False), sa.Column('comments', sa.TEXT(), nullable=True), sa.Column('currency', sa.TEXT(), nullable=True), sa.ForeignKeyConstraint(['ticket_id'], [u'navitia.ticket.ticket_key'], name=u'dated_ticket_ticket_id_fkey'), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('message', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('start_publication_date', postgresql.TIMESTAMP(timezone=True), nullable=False), sa.Column('end_publication_date', postgresql.TIMESTAMP(timezone=True), nullable=False), sa.Column('start_application_date', postgresql.TIMESTAMP(timezone=True), nullable=False), sa.Column('end_application_date', postgresql.TIMESTAMP(timezone=True), nullable=False), sa.Column('start_application_daily_hour', postgresql.TIME(timezone=True), nullable=False), sa.Column('end_application_daily_hour', postgresql.TIME(timezone=True), nullable=False), sa.Column('active_days', postgresql.BIT(length=8), nullable=False), sa.Column('object_uri', sa.TEXT(), nullable=False), sa.Column('object_type_id', sa.INTEGER(), nullable=False), sa.Column('message_status_id', sa.INTEGER(), nullable=False), sa.Column('is_active', sa.BOOLEAN(), nullable=False), sa.ForeignKeyConstraint(['message_status_id'], [u'realtime.message_status.id'], name=u'message_message_status_id_fkey'), sa.ForeignKeyConstraint(['object_type_id'], [u'navitia.object_type.id'], name=u'message_object_type_id_fkey'), sa.PrimaryKeyConstraint('id'), schema='realtime' ) op.create_table('edge', sa.Column('source_node_id', sa.BIGINT(), nullable=False), sa.Column('target_node_id', sa.BIGINT(), nullable=False), sa.Column('way_id', sa.BIGINT(), nullable=False), sa.Column('the_geog', ga.Geography(geometry_type='LINESTRING', srid=4326, spatial_index=False), nullable=False), sa.Column('pedestrian_allowed', sa.BOOLEAN(), nullable=False), sa.Column('cycles_allowed', sa.BOOLEAN(), nullable=False), sa.Column('cars_allowed', sa.BOOLEAN(), nullable=False), sa.ForeignKeyConstraint(['source_node_id'], [u'georef.node.id'], name=u'edge_source_node_id_fkey'), sa.ForeignKeyConstraint(['target_node_id'], [u'georef.node.id'], name=u'edge_target_node_id_fkey'), schema='georef' ) op.create_table('od_ticket', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('od_id', sa.BIGINT(), nullable=False), sa.Column('ticket_id', sa.TEXT(), nullable=False), sa.ForeignKeyConstraint(['od_id'], [u'navitia.origin_destination.id'], name=u'od_ticket_od_id_fkey'), sa.ForeignKeyConstraint(['ticket_id'], [u'navitia.ticket.ticket_key'], name=u'od_ticket_ticket_id_fkey'), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('rel_admin_admin', sa.Column('master_admin_id', sa.BIGINT(), nullable=False), sa.Column('admin_id', sa.BIGINT(), nullable=False), sa.ForeignKeyConstraint(['admin_id'], [u'georef.admin.id'], name=u'rel_admin_admin_admin_id_fkey'), sa.ForeignKeyConstraint(['master_admin_id'], [u'georef.admin.id'], name=u'rel_admin_admin_master_admin_id_fkey'), sa.PrimaryKeyConstraint('master_admin_id', 'admin_id'), schema='georef' ) op.create_table('rel_way_admin', sa.Column('admin_id', sa.BIGINT(), nullable=False), sa.Column('way_id', sa.BIGINT(), nullable=False), sa.ForeignKeyConstraint(['admin_id'], [u'georef.admin.id'], name=u'rel_way_admin_admin_id_fkey'), sa.ForeignKeyConstraint(['way_id'], [u'georef.way.id'], name=u'rel_way_admin_way_id_fkey'), sa.PrimaryKeyConstraint('admin_id', 'way_id'), schema='georef' ) op.create_table('stop_area', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('properties_id', sa.BIGINT(), nullable=True), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('external_code', sa.TEXT(), nullable=False), sa.Column('name', sa.TEXT(), nullable=False), sa.Column('coord', ga.Geography(geometry_type='POINT', srid=4326, spatial_index=False), nullable=True), sa.Column('comment', sa.TEXT(), nullable=True), sa.Column('visible', sa.BOOLEAN(), nullable=False), sa.Column('timezone', sa.TEXT(), nullable=True), sa.ForeignKeyConstraint(['properties_id'], [u'navitia.properties.id'], name=u'stop_area_properties_id_fkey'), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('house_number', sa.Column('way_id', sa.BIGINT(), nullable=True), sa.Column('coord', ga.Geography(geometry_type='POINT', srid=4326, spatial_index=False), nullable=False), sa.Column('number', sa.TEXT(), nullable=False), sa.Column('left_side', sa.BOOLEAN(), nullable=False), sa.ForeignKeyConstraint(['way_id'], [u'georef.way.id'], name=u'house_number_way_id_fkey'), schema='georef' ) op.create_table('line', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('network_id', sa.BIGINT(), nullable=False), sa.Column('commercial_mode_id', sa.BIGINT(), nullable=False), sa.Column('comment', sa.TEXT(), nullable=True), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('external_code', sa.TEXT(), nullable=False), sa.Column('name', sa.TEXT(), nullable=False), sa.Column('code', sa.TEXT(), nullable=True), sa.Column('color', sa.TEXT(), nullable=True), sa.Column('sort', sa.INTEGER(), nullable=False), sa.ForeignKeyConstraint(['commercial_mode_id'], [u'navitia.commercial_mode.id'], name=u'line_commercial_mode_id_fkey'), sa.ForeignKeyConstraint(['network_id'], [u'navitia.network.id'], name=u'line_network_id_fkey'), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('transition', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('before_change', sa.TEXT(), nullable=False), sa.Column('after_change', sa.TEXT(), nullable=False), sa.Column('start_trip', sa.TEXT(), nullable=False), sa.Column('end_trip', sa.TEXT(), nullable=False), sa.Column('global_condition', postgresql.ENUM(u'nothing', u'exclusive', u'with_changes', name='fare_transition_condition'), nullable=False), sa.Column('ticket_id', sa.TEXT(), nullable=True), sa.ForeignKeyConstraint(['ticket_id'], [u'navitia.ticket.ticket_key'], name=u'transition_ticket_id_fkey'), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('poi', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('weight', sa.INTEGER(), nullable=False), sa.Column('coord', ga.Geography(geometry_type='POINT', srid=4326, spatial_index=False), nullable=True), sa.Column('name', sa.TEXT(), nullable=False), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('visible', sa.BOOLEAN(), nullable=False), sa.Column('poi_type_id', sa.BIGINT(), nullable=False), sa.Column('address_name', sa.TEXT(), nullable=True), sa.Column('address_number', sa.TEXT(), nullable=True), sa.ForeignKeyConstraint(['poi_type_id'], [u'georef.poi_type.id'], name=u'poi_poi_type_id_fkey'), sa.PrimaryKeyConstraint('id'), schema='georef' ) op.create_table('at_perturbation', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('start_application_date', postgresql.TIMESTAMP(timezone=True), nullable=False), sa.Column('end_application_date', postgresql.TIMESTAMP(timezone=True), nullable=False), sa.Column('start_application_daily_hour', postgresql.TIME(timezone=True), nullable=False), sa.Column('end_application_daily_hour', postgresql.TIME(timezone=True), nullable=False), sa.Column('active_days', postgresql.BIT(length=8), nullable=False), sa.Column('object_uri', sa.TEXT(), nullable=False), sa.Column('object_type_id', sa.INTEGER(), nullable=False), sa.Column('is_active', sa.BOOLEAN(), nullable=False), sa.ForeignKeyConstraint(['object_type_id'], [u'navitia.object_type.id'], name=u'at_perturbation_object_type_id_fkey'), sa.PrimaryKeyConstraint('id'), schema='realtime' ) op.create_table('admin_stop_area', sa.Column('admin_id', sa.TEXT(), nullable=False), sa.Column('stop_area_id', sa.BIGINT(), nullable=False), sa.ForeignKeyConstraint(['stop_area_id'], [u'navitia.stop_area.id'], name=u'admin_stop_area_stop_area_id_fkey'), schema='navitia' ) op.create_table('localized_message', sa.Column('message_id', sa.BIGINT(), nullable=False), sa.Column('language', sa.TEXT(), nullable=False), sa.Column('body', sa.TEXT(), nullable=False), sa.Column('title', sa.TEXT(), nullable=True), sa.ForeignKeyConstraint(['message_id'], [u'realtime.message.id'], name=u'localized_message_message_id_fkey'), sa.PrimaryKeyConstraint('message_id', 'language'), schema='realtime' ) op.create_table('exception_date', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('datetime', sa.DATE(), nullable=False), sa.Column('type_ex', postgresql.ENUM(u'Add', u'Sub', name='exception_type'), nullable=False), sa.Column('calendar_id', sa.BIGINT(), nullable=False), sa.ForeignKeyConstraint(['calendar_id'], [u'navitia.calendar.id'], name=u'exception_date_calendar_id_fkey'), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('route', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('line_id', sa.BIGINT(), nullable=False), sa.Column('comment', sa.TEXT(), nullable=True), sa.Column('name', sa.TEXT(), nullable=False), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('external_code', sa.TEXT(), nullable=False), sa.ForeignKeyConstraint(['line_id'], [u'navitia.line.id'], name=u'route_line_id_fkey'), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('period', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('calendar_id', sa.BIGINT(), nullable=False), sa.Column('begin_date', sa.DATE(), nullable=False), sa.Column('end_date', sa.DATE(), nullable=False), sa.ForeignKeyConstraint(['calendar_id'], [u'navitia.calendar.id'], name=u'period_calendar_id_fkey'), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('poi_properties', sa.Column('poi_id', sa.BIGINT(), nullable=False), sa.Column('key', sa.TEXT(), nullable=True), sa.Column('value', sa.TEXT(), nullable=True), sa.ForeignKeyConstraint(['poi_id'], [u'georef.poi.id'], name=u'poi_properties_poi_id_fkey'), schema='georef' ) op.create_table('stop_point', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('properties_id', sa.BIGINT(), nullable=True), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('external_code', sa.TEXT(), nullable=False), sa.Column('coord', ga.Geography(geometry_type='POINT', srid=4326, spatial_index=False), nullable=True), sa.Column('fare_zone', sa.INTEGER(), nullable=True), sa.Column('name', sa.TEXT(), nullable=False), sa.Column('comment', sa.TEXT(), nullable=True), sa.Column('stop_area_id', sa.BIGINT(), nullable=False), sa.Column('platform_code', sa.TEXT(), nullable=True), sa.ForeignKeyConstraint(['properties_id'], [u'navitia.properties.id'], name=u'stop_point_properties_id_fkey'), sa.ForeignKeyConstraint(['stop_area_id'], [u'navitia.stop_area.id'], name=u'stop_point_stop_area_id_fkey'), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('rel_calendar_line', sa.Column('calendar_id', sa.BIGINT(), nullable=False), sa.Column('line_id', sa.BIGINT(), nullable=False), sa.ForeignKeyConstraint(['calendar_id'], [u'navitia.calendar.id'], name=u'rel_calendar_line_calendar_id_fkey'), sa.ForeignKeyConstraint(['line_id'], [u'navitia.line.id'], name=u'rel_calendar_line_line_id_fkey'), sa.PrimaryKeyConstraint('calendar_id', 'line_id'), schema='navitia' ) op.create_table('rel_line_company', sa.Column('line_id', sa.BIGINT(), nullable=False), sa.Column('company_id', sa.BIGINT(), nullable=False), sa.ForeignKeyConstraint(['company_id'], [u'navitia.company.id'], name=u'rel_line_company_company_id_fkey'), sa.ForeignKeyConstraint(['line_id'], [u'navitia.line.id'], name=u'rel_line_company_line_id_fkey'), sa.PrimaryKeyConstraint('line_id', 'company_id'), schema='navitia' ) op.create_table('connection', sa.Column('departure_stop_point_id', sa.BIGINT(), nullable=False), sa.Column('destination_stop_point_id', sa.BIGINT(), nullable=False), sa.Column('connection_type_id', sa.BIGINT(), nullable=False), sa.Column('properties_id', sa.BIGINT(), nullable=True), sa.Column('duration', sa.INTEGER(), nullable=False), sa.Column('max_duration', sa.INTEGER(), nullable=False), sa.Column('display_duration', sa.INTEGER(), nullable=False), sa.ForeignKeyConstraint(['connection_type_id'], [u'navitia.connection_type.id'], name=u'connection_connection_type_id_fkey'), sa.ForeignKeyConstraint(['departure_stop_point_id'], [u'navitia.stop_point.id'], name=u'connection_departure_stop_point_id_fkey'), sa.ForeignKeyConstraint(['destination_stop_point_id'], [u'navitia.stop_point.id'], name=u'connection_destination_stop_point_id_fkey'), sa.ForeignKeyConstraint(['properties_id'], [u'navitia.properties.id'], name=u'connection_properties_id_fkey'), sa.PrimaryKeyConstraint('departure_stop_point_id', 'destination_stop_point_id'), schema='navitia' ) op.create_table('journey_pattern', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('route_id', sa.BIGINT(), nullable=False), sa.Column('physical_mode_id', sa.BIGINT(), nullable=False), sa.Column('comment', sa.TEXT(), nullable=True), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('name', sa.TEXT(), nullable=False), sa.Column('is_frequence', sa.BOOLEAN(), nullable=False), sa.ForeignKeyConstraint(['physical_mode_id'], [u'navitia.physical_mode.id'], name=u'journey_pattern_physical_mode_id_fkey'), sa.ForeignKeyConstraint(['route_id'], [u'navitia.route.id'], name=u'journey_pattern_route_id_fkey'), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('vehicle_journey', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('adapted_validity_pattern_id', sa.BIGINT(), nullable=False), sa.Column('validity_pattern_id', sa.BIGINT(), nullable=True), sa.Column('company_id', sa.BIGINT(), nullable=False), sa.Column('journey_pattern_id', sa.BIGINT(), nullable=False), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('external_code', sa.TEXT(), nullable=False), sa.Column('comment', sa.TEXT(), nullable=True), sa.Column('odt_message', sa.TEXT(), nullable=True), sa.Column('name', sa.TEXT(), nullable=False), sa.Column('odt_type_id', sa.BIGINT(), nullable=True), sa.Column('vehicle_properties_id', sa.BIGINT(), nullable=True), sa.Column('theoric_vehicle_journey_id', sa.BIGINT(), nullable=True), sa.Column('previous_vehicle_journey_id', sa.BIGINT(), nullable=True), sa.Column('next_vehicle_journey_id', sa.BIGINT(), nullable=True), sa.Column('start_time', sa.INTEGER(), nullable=True), sa.Column('end_time', sa.INTEGER(), nullable=True), sa.Column('headway_sec', sa.INTEGER(), nullable=True), sa.Column('utc_to_local_offset', sa.INTEGER(), nullable=True), sa.ForeignKeyConstraint(['adapted_validity_pattern_id'], [u'navitia.validity_pattern.id'], name=u'vehicle_journey_adapted_validity_pattern_id_fkey'), sa.ForeignKeyConstraint(['company_id'], [u'navitia.company.id'], name=u'vehicle_journey_company_id_fkey'), sa.ForeignKeyConstraint(['journey_pattern_id'], [u'navitia.journey_pattern.id'], name=u'vehicle_journey_journey_pattern_id_fkey'), sa.ForeignKeyConstraint(['next_vehicle_journey_id'], [u'navitia.vehicle_journey.id'], name=u'vehicle_journey_next_vehicle_journey_id_fkey'), sa.ForeignKeyConstraint(['previous_vehicle_journey_id'], [u'navitia.vehicle_journey.id'], name=u'vehicle_journey_previous_vehicle_journey_id_fkey'), sa.ForeignKeyConstraint(['theoric_vehicle_journey_id'], [u'navitia.vehicle_journey.id'], name=u'vehicle_journey_theoric_vehicle_journey_id_fkey'), sa.ForeignKeyConstraint(['validity_pattern_id'], [u'navitia.validity_pattern.id'], name=u'vehicle_journey_validity_pattern_id_fkey'), sa.ForeignKeyConstraint(['vehicle_properties_id'], [u'navitia.vehicle_properties.id'], name=u'vehicle_journey_vehicle_properties_id_fkey'), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('journey_pattern_point', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('journey_pattern_id', sa.BIGINT(), nullable=False), sa.Column('name', sa.TEXT(), nullable=False), sa.Column('uri', sa.TEXT(), nullable=False), sa.Column('order', sa.INTEGER(), nullable=False), sa.Column('comment', sa.TEXT(), nullable=True), sa.Column('stop_point_id', sa.BIGINT(), nullable=False), sa.ForeignKeyConstraint(['journey_pattern_id'], [u'navitia.journey_pattern.id'], name=u'journey_pattern_point_journey_pattern_id_fkey'), sa.ForeignKeyConstraint(['stop_point_id'], [u'navitia.stop_point.id'], name=u'journey_pattern_point_stop_point_id_fkey'), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('stop_time', sa.Column('id', sa.BIGINT(), nullable=False), sa.Column('vehicle_journey_id', sa.BIGINT(), nullable=False), sa.Column('journey_pattern_point_id', sa.BIGINT(), nullable=False), sa.Column('arrival_time', sa.INTEGER(), nullable=True), sa.Column('departure_time', sa.INTEGER(), nullable=True), sa.Column('local_traffic_zone', sa.INTEGER(), nullable=True), sa.Column('odt', sa.BOOLEAN(), nullable=False), sa.Column('pick_up_allowed', sa.BOOLEAN(), nullable=False), sa.Column('drop_off_allowed', sa.BOOLEAN(), nullable=False), sa.Column('is_frequency', sa.BOOLEAN(), nullable=False), sa.Column('comment', sa.TEXT(), nullable=True), sa.Column('date_time_estimated', sa.BOOLEAN(), nullable=False), sa.Column('properties_id', sa.BIGINT(), nullable=True), sa.ForeignKeyConstraint(['journey_pattern_point_id'], [u'navitia.journey_pattern_point.id'], name=u'stop_time_journey_pattern_point_id_fkey'), sa.ForeignKeyConstraint(['properties_id'], [u'navitia.properties.id'], name=u'stop_time_properties_id_fkey'), sa.ForeignKeyConstraint(['vehicle_journey_id'], [u'navitia.vehicle_journey.id'], name=u'stop_time_vehicle_journey_id_fkey'), sa.PrimaryKeyConstraint('id'), schema='navitia' ) op.create_table('rel_metavj_vj', sa.Column('meta_vj', sa.BIGINT(), nullable=True), sa.Column('vehicle_journey', sa.BIGINT(), nullable=True), sa.Column('vj_class', postgresql.ENUM(u'Theoric', u'Adapted', u'RealTime', name='vj_classification'), nullable=False), sa.ForeignKeyConstraint(['meta_vj'], [u'navitia.meta_vj.id'], name=u'rel_metavj_vj_meta_vj_fkey'), sa.ForeignKeyConstraint(['vehicle_journey'], [u'navitia.vehicle_journey.id'], name=u'rel_metavj_vj_vehicle_journey_fkey'), schema='navitia' )
def upgrade(): ### commands auto generated by Alembic - please adjust! ### op.create_table( 'stop_point', sa.Column('id', sa.Integer(), nullable=False), sa.Column('uri', sa.Text(), nullable=False), sa.Column('coord', ga.Geography(geometry_type='POINT', srid=4326, spatial_index=False), nullable=True), sa.Column('name', sa.Text(), nullable=False), sa.Column('external_code', sa.Text(), nullable=True), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('uri')) op.create_index('ix_stop_point_external_code', 'stop_point', ['external_code'], unique=False) op.create_table('line', sa.Column('id', sa.Integer(), nullable=False), sa.Column('uri', sa.Text(), nullable=False), sa.Column('name', sa.Text(), nullable=False), sa.Column('external_code', sa.Text(), nullable=True), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('uri')) op.create_index('ix_line_external_code', 'line', ['external_code'], unique=False) op.create_table( 'poi', sa.Column('id', sa.Integer(), nullable=False), sa.Column('uri', sa.Text(), nullable=False), sa.Column('coord', ga.Geography(geometry_type='POINT', srid=4326, spatial_index=False), nullable=True), sa.Column('name', sa.Text(), nullable=False), sa.Column('external_code', sa.Text(), nullable=True), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('uri')) op.create_index('ix_poi_external_code', 'poi', ['external_code'], unique=False) op.create_table('route', sa.Column('id', sa.Integer(), nullable=False), sa.Column('uri', sa.Text(), nullable=False), sa.Column('name', sa.Text(), nullable=False), sa.Column('external_code', sa.Text(), nullable=True), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('uri')) op.create_index('ix_route_external_code', 'route', ['external_code'], unique=False) op.create_table('admin', sa.Column('id', sa.Integer(), nullable=False), sa.Column('uri', sa.Text(), nullable=False), sa.Column('name', sa.Text(), nullable=False), sa.Column('external_code', sa.Text(), nullable=True), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('uri')) op.create_index('ix_admin_external_code', 'admin', ['external_code'], unique=False) op.create_table('network', sa.Column('id', sa.Integer(), nullable=False), sa.Column('uri', sa.Text(), nullable=False), sa.Column('name', sa.Text(), nullable=False), sa.Column('external_code', sa.Text(), nullable=True), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('uri')) op.create_index('ix_network_external_code', 'network', ['external_code'], unique=False) op.create_table( 'stop_area', sa.Column('id', sa.Integer(), nullable=False), sa.Column('uri', sa.Text(), nullable=False), sa.Column('coord', ga.Geography(geometry_type='POINT', srid=4326, spatial_index=False), nullable=True), sa.Column('name', sa.Text(), nullable=False), sa.Column('external_code', sa.Text(), nullable=True), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('uri')) op.create_index('ix_stop_area_external_code', 'stop_area', ['external_code'], unique=False) op.create_table( 'rel_stop_area_instance', sa.Column('object_id', sa.Integer(), nullable=False), sa.Column('instance_id', sa.Integer(), nullable=False), sa.ForeignKeyConstraint(['instance_id'], ['instance.id'], ondelete='CASCADE'), sa.ForeignKeyConstraint(['object_id'], ['stop_area.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('object_id', 'instance_id')) op.create_table( 'rel_poi_instance', sa.Column('object_id', sa.Integer(), nullable=False), sa.Column('instance_id', sa.Integer(), nullable=False), sa.ForeignKeyConstraint(['instance_id'], ['instance.id'], ondelete='CASCADE'), sa.ForeignKeyConstraint(['object_id'], ['poi.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('object_id', 'instance_id')) op.create_table( 'rel_network_instance', sa.Column('object_id', sa.Integer(), nullable=False), sa.Column('instance_id', sa.Integer(), nullable=False), sa.ForeignKeyConstraint(['instance_id'], ['instance.id'], ondelete='CASCADE'), sa.ForeignKeyConstraint(['object_id'], ['network.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('object_id', 'instance_id')) op.create_table( 'rel_line_instance', sa.Column('object_id', sa.Integer(), nullable=False), sa.Column('instance_id', sa.Integer(), nullable=False), sa.ForeignKeyConstraint(['instance_id'], ['instance.id'], ondelete='CASCADE'), sa.ForeignKeyConstraint(['object_id'], ['line.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('object_id', 'instance_id')) op.create_table( 'rel_admin_instance', sa.Column('object_id', sa.Integer(), nullable=False), sa.Column('instance_id', sa.Integer(), nullable=False), sa.ForeignKeyConstraint(['object_id'], ['admin.id'], ondelete='CASCADE'), sa.ForeignKeyConstraint(['instance_id'], ['instance.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('object_id', 'instance_id')) op.create_table( 'rel_stop_point_instance', sa.Column('object_id', sa.Integer(), nullable=False), sa.Column('instance_id', sa.Integer(), nullable=False), sa.ForeignKeyConstraint(['instance_id'], ['instance.id'], ondelete='CASCADE'), sa.ForeignKeyConstraint(['object_id'], ['stop_point.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('object_id', 'instance_id')) op.create_table( 'rel_route_instance', sa.Column('object_id', sa.Integer(), nullable=False), sa.Column('instance_id', sa.Integer(), nullable=False), sa.ForeignKeyConstraint(['instance_id'], ['instance.id'], ondelete='CASCADE'), sa.ForeignKeyConstraint(['object_id'], ['route.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('object_id', 'instance_id')) op.execute(""" CREATE VIEW ptobject AS SELECT id, uri, external_code, name, 'stop_area' as type FROM stop_area UNION SELECT id, uri, external_code, name, 'stop_point' as type FROM stop_point UNION SELECT id, uri, external_code, name, 'poi' as type FROM poi UNION SELECT id, uri, external_code, name, 'admin' as type FROM admin UNION SELECT id, uri, external_code, name, 'line' as type FROM line UNION SELECT id, uri, external_code, name, 'route' as type FROM route UNION SELECT id, uri, external_code, name, 'network' as type FROM network ;""")
class Place(Base): """ A simple model denoting a "place", which could be any geographical entity with a name, coordinates, and a polygon. """ __tablename__ = "place" id = sa.Column(sa.BigInteger, primary_key=True) name = sa.Column(sa.String, nullable=False) latitude = sa.Column(sa.Float, nullable=False) longitude = sa.Column(sa.Float, nullable=False) center_raw = sa.Column("center", ga.Geography("POINT"), nullable=False) center = sa.orm.column_property(ga.functions.ST_AsGeoJSON(center_raw)) polygon_raw = sa.Column("polygon", ga.Geography("POLYGON")) polygon = sa.orm.column_property(ga.functions.ST_AsGeoJSON(polygon_raw)) popularity = sa.Column(sa.BigInteger, nullable=False, default=0) def __init__(self, *args, **kwargs) -> None: super(Place, self).__init__(*args, **kwargs) self.__distance = None @property def distance(self) -> float: return self.__distance @distance.setter def distance(self, value: float) -> None: self.__distance = value @staticmethod def _format_point_postgis(lat: float, lon: float) -> Point: """ Format the lat/lon as a PostGIS Point Geography, to be used in SQLAlchemy queries. :param lat: latitude value :param lon: longitude value :return: the lat/lon pair in SQL representation, a PostGIS POINT cast to a PostGIS Geography type """ return sa.cast("POINT({} {})".format(lon, lat), ga.types.Geography) @staticmethod def _set_distances(results: List[(Place, float)]) -> List[Place]: """ For a list of tuples containing Place + distance, set each provided distance value onthe distance property of the corresponding Place. :param results: Place + distance pairs from our queries :return: Places, with the `distance` property set """ all_entities = [] for entity, distance in results: entity.distance = distance all_entities.append(entity) return all_entities @classmethod def _postgis_distance(cls, point: Point) -> STDistance: """ For a given Point, return the geoalchemy ST_Distance instance denoting the distance that a point resides from a Place's center. Compiling the ST_Intersects function to raw SQL is handled by SQLAlchemy. :param point: :return: the resulting geoalchemy STDistance class instance """ return ga.functions.ST_Distance(cls.center_raw, point) @classmethod def _postgis_buffered_intersect( cls, point: Point, buffer: int = DEFAULT_INTERSECT_BUFFER ) -> STIntersects: """ For a given Point, return the geoalchemy ST_Intersects instance denoting the filter for checking whether a buffered point intersects a Place's polygon. Compiling the ST_Intersects function to raw SQL is handled by SQLAlchemy. :param point: a PostGIS-formatted point :param buffer: the buffer around the point :return: the resulting geoalchemy STIntersects class instance """ return ga.functions.ST_Intersects( Place.polygon_raw, ga.functions.ST_Buffer(point, DEFAULT_INTERSECT_BUFFER) ) @classmethod def nearby(cls, lat: float, lon: float, radius: float) -> List[Place]: """ Find places nearest to the given lat/lon, limited to the provided radius in meters. :param lat: latitude :param lon: longitude :param radius: :return: """ formatted_point = cls._format_point_postgis(lat, lon) distance = cls._postgis_distance(formatted_point) query = ( cls.query.with_entities(cls, distance) .filter(distance < radius) .order_by(distance) .limit(DEFAULT_LIMIT) .all() ) return cls._set_distances(query) @classmethod def reverse_geolocate( cls, lat: float, lon: float, weighted: bool = False ) -> List[Place]: """ Given the provided latitude and longitude, find a set of candidate places that the lat/lon may be associated with. :param lat: latitude :param lon: longitude :param weighted: denotes whether the query should be weighted by popularity :return: candidate Places """ formatted_point = cls._format_point_postgis(lat, lon) distance = cls._postgis_distance(formatted_point) ordering = (distance + 1) / (Place.popularity + 1) if weighted else distance query = ( cls.query.with_entities(cls, distance) .filter(cls._postgis_buffered_intersect(formatted_point)) .order_by(ordering) .limit(DEFAULT_LIMIT) .all() ) return cls._set_distances(query)
server_default=func.current_timestamp()), Column('last_activity', TIMESTAMP, nullable=False, default=func.current_timestamp(), server_default=func.current_timestamp()), UniqueConstraint('device_id', 'installation_id', name='uix_device_id_installation_id'), Index('idx_devices_device_id_inst_id', 'device_id', 'installation_id')) device_data_table = Table( 'device_data', metadata, Column('id', Integer, primary_key=True), Column('device_id', Integer, ForeignKey('devices.id'), nullable=False), Column('coordinate', ga2.Geography('point', 4326, spatial_index=False), nullable=False), Column('accuracy', DOUBLE_PRECISION, nullable=False), Column('time', TIMESTAMP, nullable=False), Column('activity_1', activity_type_enum), Column('activity_1_conf', Integer), Column('activity_2', activity_type_enum), Column('activity_2_conf', Integer), Column('activity_3', activity_type_enum), Column('activity_3_conf', Integer), Column('waypoint_id', BigInteger), Column('snapping_time', TIMESTAMP), Index('idx_device_data_time', 'time'), Index('idx_device_data_device_id_time', 'device_id', 'time')) Index('idx_device_data_snapping_time_null',