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',
