def __init__(self, database_url: str):
self._connection = databases.Database(database_url)
metadata = sqlalchemy.MetaData()
real_property_table = sqlalchemy.Table(
"properties",
metadata,
sqlalchemy.Column("id", sqlalchemy.String, primary_key=True),
sqlalchemy.Column("geocode_geo",
Geography(geometry_type='POINT', srid=4326),
nullable=True),
sqlalchemy.Column("parcel_geo",
Geography(geometry_type='POLYGON', srid=4326),
nullable=True),
sqlalchemy.Column("building_geo",
Geography(geometry_type='POLYGON', srid=4326),
nullable=True),
sqlalchemy.Column("image_bounds",
postgresql.ARRAY(postgresql.DOUBLE_PRECISION),
nullable=True),
sqlalchemy.Column("image_url", sqlalchemy.String, nullable=True),
)
self._real_property_queries = RealPropertyQueries(
self._connection, real_property_table)
self._real_property_commands = RealPropertyCommands(
self._connection, real_property_table)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('buses',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('vehicleId', sa.Integer(), nullable=True),
sa.Column('location', Geography(geometry_type='POINT', srid=4326, from_text='ST_GeogFromText', name='geography'), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table('stops',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=True),
sa.Column('number', sa.Integer(), nullable=True),
sa.Column('location', Geography(geometry_type='POINT', srid=4326, from_text='ST_GeogFromText', name='geography'), nullable=True),
sa.PrimaryKeyConstraint('id')
)
class UserFavorite(db.Model):
"""User's favorite airports"""
__tablename__ = 'user_favorites'
favorite_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
favorite_title = db.Column(db.String(25))
favorite_lat = db.Column(db.Float)
favorite_lng = db.Column(db.Float)
favorite_location = db.Column(Geography(geometry_type='POINT', srid=4326),
nullable=True)
#Foreign keys:
user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'))
airport_id = db.Column(db.Integer, db.ForeignKey('airports.airport_id'))
#I couldn't find a reason to use a backref here because why would you want
#to reference who favorited the airport.
#Actually, I guess thats something you could want.
#I'll leave this note in incase I decide to do that in the future.
#Relationships:
user = db.relationship('User', backref=db.backref('favorites'))
airports = db.relationship('Airport')
def __repr__(self):
"""Prints information for the user favorite object"""
return '<UserFavorite id={} user id={} airport={}>'.format(
self.favorite_id, self.user_id, self.airport_id)
class Landmark(db.Model):
__tablename__ = 'landmarks'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255))
coordinates = db.Column(Geography('POINT', srid=4326))
_coordinate_description_string = None
def __repr__(self):
return f"<{self.name} @ {self.latitude},{self.longitude}>"
@classmethod
def init(cls, id, name, coordinates):
landmark = cls(name=name)
setattr(landmark, 'id', id)
setattr(landmark, '_coordinate_description_string', coordinates)
return landmark
@property
def point(self):
if self._coordinate_description_string:
desc = self._coordinate_description_string
else:
desc = self.coordinates.desc
binary_data = unhexlify(desc)
return shapely.wkb.loads(binary_data)
@property
def latitude(self):
return self.point.y
@property
def longitude(self):
return self.point.x
class Tracks(db.Model):
__tablename__ = 'tracks'
gid = db.Column(db.Integer, primary_key=True)
rutenavn = db.Column(db.String(33))
rutenummer = db.Column(db.String(13))
spes_fotru = db.Column(db.String(2))
gradering = db.Column(db.String(1))
rutemerkin = db.Column(db.String(3))
rutefolger = db.Column(db.String(2))
oppdatdato = db.Column(db.String(20))
belysning = db.Column(db.SMALLINT)
lokalid = db.Column(db.String(100))
navnerom = db.Column(db.String(100))
noyaktighe = db.Column(db.INTEGER)
synbarhet = db.Column(db.SMALLINT)
objtype = db.Column(db.String(32))
skilting = db.Column(db.String(3))
rutebetydn = db.Column(db.SMALLINT)
geog = db.Column(Geography(geometry_type='LINESTRING'))
trips = db.relationship('Trips', backref='Triptrack', lazy='dynamic')
def __repr__(self):
return '{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}'.format(
self.gid, self.rutenavn, self.rutenummer, self.spes_fotru,
self.gradering, self.rutemerkin, self.rutefolger, self.oppdatdato,
self.belysning, self.lokalid, self.navnerom, self.noyaktighe,
self.synbarhet, self.objtype, self.skilting, self.rutebetydn,
self.geog)
class Station(db.Model):
number = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(127))
address = db.Column(db.String(255))
position = db.Column(Geography(geometry_type='POINT', srid=4326))
banking = db.Column(db.Boolean)
bonus = db.Column(db.Boolean)
status = db.Column(db.String(10))
bike_stands = db.Column(db.Integer)
available_bike_stands = db.Column(db.Integer)
available_bikes = db.Column(db.Integer)
last_update = db.Column(db.DateTime)
def __init__(self, number, name, address, position, banking, bonus, status,
bike_stands, available_bike_stands, available_bikes,
last_update):
self.number = number
self.name = name
self.address = address
self.position = position
self.banking = banking
self.bonus = bonus
self.status = status
self.bike_stands = bike_stands
self.available_bike_stands = available_bike_stands
self.available_bikes = available_bikes
self.last_update = last_update
class Vendor(db.Model):
"""
Represents a business that sells produce.
"""
def __init__(self, name, address, latitude_longitude):
self.name = name
self.address = address
self.latitude_longitude = latitude_longitude
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), nullable=False)
address = db.Column(db.String(200), nullable=False)
latitude_longitude = db.Column(Geography("POINT"), nullable=False)
avg_rating = db.Column(db.Float(), default=0.00)
# relationship to rating
ratings = db.relationship('Rating', backref="vendor", lazy=True)
def update_average_rating(self):
avg_rating = get_rating_average(self.ratings)
def __repr__(self):
return '<Vendor %r>' % self.name
def to_dict(self):
point = to_shape(self.latitude_longitude)
latlon = [point.x, point.y]
return {
"id": self.id,
'name': self.name,
'address': self.address,
'location': latlon
}
class Poi(db.Model, mixin_get_from_uri, mixin_get_from_external_code):
id = db.Column(db.Integer, primary_key=True)
uri = db.Column(db.Text, nullable=False, unique=True)
coord = db.Column(
Geography(geometry_type="POINT", srid=4326, spatial_index=False))
instances = db.relationship("Instance",
secondary="rel_poi_instance",
backref="poi",
cascade="all",
passive_deletes=True,
lazy='joined')
name = db.Column(db.Text, nullable=False)
external_code = db.Column(db.Text, index=True)
cls_rel_instance = PoiInstance
prefix_ext_code = "external_code_poi"
def __init__(self,
id=None,
uri=None,
coord=None,
external_code=None,
name=None):
self.id = id
self.uri = uri
self.coord = coord
self.external_code = external_code
self.name = name
def __repr__(self):
return '<Poi %r>' % self.id
class Poi():
name = db.Column(db.String(127))
address = db.Column(db.String(255))
position = db.Column(Geography(geometry_type='POINT', srid=4326))
def __init__(self, name, address, position):
self.name = name
self.address = address
self.position = position
def within_distance_func(cls, position, distance):
"""
Creates the geoalchemy function that determines if a point is in range < distance from the position item
:param position: the position to check distance with
:param distance: the maximum distance in meters
:return: function to apply to query
"""
point = func.ST_GeomFromText('POINT({0} {1})'.format(*position), srid=Position.SRS_WGS_84)
return func.ST_DWithin(cast(Position.location, Geography(srid=Position.SRS_WGS_84)), point, distance)
def create_coordinate(lat, lng):
"""
Creates a WKT point from a (lat, lng) tuple in EPSG4326 coordinate system (normal GPS-coordinates)
"""
wkb_point = from_shape(Point(lng, lat), srid=4326)
# Casting to Geography and back here to ensure coordinate wrapping
return cast(
cast(wkb_point, Geography(geometry_type="POINT", srid=4326)), Geometry(geometry_type="POINT", srid=4326)
)
class Shop(Base):
__tablename__ = 'shops'
id = Column(Integer, primary_key=True, index=True)
name = Column(String(length=50), nullable=False)
is_available = Column(Boolean, default=True)
phone_number = Column(String(15))
address = Column(String(50), nullable=False)
owner_id = Column(Integer, ForeignKey('owner.id'), nullable=False)
owner = relation("Owner", backref='shops')
location = Column(Geography(geometry_type='POINT', srid=4326),
nullable=False)
radius_metres = Column(Numeric(asdecimal=True, scale=3), nullable=False)
items = relation("Item", back_populates="shop")
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String(50), unique=True)
online = Column(Boolean)
location = Column(Geography(geometry_type='POINT', srid=420))
marker = Column(Geography(geometry_type='POINT', srid=699))
def __init__(self, json=None):
if json is not None:
self.from_json(json)
def __repr__(self):
return '<User %r>' % (self.name)
def to_json(self):
return {
'name': self.name,
'online': self.online,
'location': {
'lat': self.location.geom.y,
'long': self.location.geom.x
},
'marker': {
'lat': self.marker.geom.y,
'long': self.marker.geom.x
}
}
def from_json(self, json):
self.name = json['name']
self.online = json['online']
self.location.geom.y = json['location']['lat']
self.location.geom.x = json['location']['long']
self.marker.geom.y = json['marker']['lat']
self.marker.geom.x = json['marker']['long']
class Apartment(Base):
__tablename__ = 'listings'
id = Column(UUID, primary_key=True, default=uuid4)
no_bed = Column(Integer)
no_bath = Column(Integer)
no_toilets = Column(Integer)
price = Column(Float)
url = Column(String)
address = Column(String)
description = Column(String)
source = Column(String)
date_added = Column(Date)
latlng = Column(Geography(geometry_type='POINT', srid=4326))
class ExposureDatum(Base):
__tablename__ = 'exposure_data'
id = Column(Integer,
primary_key=True,
server_default=text("nextval('cmaq_id_seq'::regclass)"))
date = Column(Date)
fips = Column(BigInteger)
latitude = Column(Float(53))
longitude = Column(Float(53))
location = Column(Geography(u'POINT', 4326))
pm25_daily_average = Column(Float(53))
pm25_daily_average_stderr = Column(Float(53))
ozone_daily_8hour_maximum = Column(Float(53))
ozone_daily_8hour_maximum_stderr = Column(Float(53))
class Point(db.Model):
__tablename__ = 'points'
pid = db.Column(db.Integer, primary_key=True)
props = db.relationship('PointProp')
point = db.Column(Geography(geometry_type='POINT'))
def __repr__(self):
return '{}-{}'.format(self.pid, loads(bytes(self.point.data)).wkt)
@property
def __geo_interface__(self):
g = loads(bytes(self.point.data))
propdict = {}
for prop in self.props:
propdict[prop.prop_name] = prop.prop
f = Feature(id=self.pid, geometry=g, properties=propdict)
return f
class Publication(db.Model): # type: ignore
"""Publications model."""
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, nullable=False)
title = db.Column(db.String)
description = db.Column(db.String)
rooms = db.Column(
db.Integer, db.CheckConstraint('rooms >= 0', name='rooms_nonnegative'))
beds = db.Column(db.Integer,
db.CheckConstraint('beds >= 0', name='beds_nonnegative'))
bathrooms = db.Column(
db.Integer,
db.CheckConstraint('bathrooms >= 0', name='bathrooms_nonnegative'))
price_per_night = db.Column(
db.Numeric,
db.CheckConstraint('price_per_night > 0',
name='price_per_night_nonnegative'),
)
loc = db.Column(Geography(geometry_type='POINT', srid=4326))
publication_date = db.Column(db.DateTime,
nullable=False,
default=func.now())
blocked = db.Column(db.Boolean, default=False)
blockchain_status = db.Column(
db.Enum(BlockChainStatus),
nullable=False,
default=BlockChainStatus.UNSET.value,
)
blockchain_transaction_hash = db.Column(db.String(512), nullable=True)
blockchain_id = db.Column(db.Integer, nullable=True)
images = db.relationship("PublicationImage",
backref="publication",
lazy=True)
questions = db.relationship("PublicationQuestion",
backref="publication",
lazy=True)
stars = db.relationship("PublicationStar",
backref="publication",
lazy=True)
def update_from_dict(self, **kwargs):
for field, value in kwargs.items():
setattr(self, field, value)
class NewDatum(Base):
__tablename__ = 'new_data'
id = Column(Integer,
primary_key=True,
server_default=text("nextval('new_data_id_seq'::regclass)"))
date = Column(DateTime)
fips = Column(BigInteger)
latitude = Column(Float(53))
longitude = Column(Float(53))
location = Column(Geography(u'POINT', 4326))
co_daily_average = Column(Float(53))
no_daily_average = Column(Float(53))
no2_daily_average = Column(Float(53))
nox_daily_average = Column(Float(53))
so2_daily_average = Column(Float(53))
acetaldehyde_daily_average = Column(Float(53))
formaldehyde_daily_average = Column(Float(53))
benzene_daily_average = Column(Float(53))
class Station(wmata_d3.Base):
"""A metro station"""
__tablename__ = 'stations'
code = Column(String(8), primary_key=True)
name = Column(String(64))
station_together_1 = Column(String(8))
point = Column(Geography(geometry_type='POINT', srid=4326))
@property
def dto(self):
return {
'code': self.code,
'name': self.name,
'stationTogether1': self.station_together_1,
'lat': to_shape(self.point).x,
'long': to_shape(self.point).y
}
class Shop(Base):
__tablename__ = 'shops'
id = Column(String(length=100), primary_key=True, index=True)
name = Column(String(length=50), nullable=False)
is_available = Column(Boolean, default=True, nullable=False)
phone_number = Column(String(15))
address = Column(String(50), nullable=False)
owner_id = Column(Integer, ForeignKey('owner.id'), nullable=False)
owner = relation("Owner", backref='shops')
location = Column(Geography(geometry_type='POINT', srid=4326),
nullable=False)
radius_metres = Column(Numeric(asdecimal=True, scale=3), nullable=False)
items = relation("Item", back_populates="shop")
@staticmethod
def slugify_id(target, value: str, oldvalue: str, _):
if value and (not target.id or value != oldvalue):
value = value + " " + str(randint(100000, 999999))
target.id = slugify(value, max_length=100)
class Container(db.Model):
"""
Defines container position and name.
Uses SRID 4326 (WGS 84)
@link https://napoveda.seznam.cz/forum/viewtopic.php?f=31&t=26591&sid=6a47a61ee21176a150ad73fdfe584179
"""
__tablename__ = 'containers'
id = db.Column(db.Integer, primary_key=True)
terms = db.relationship('Term', lazy='dynamic')
name = db.Column(db.String(255), nullable=False)
slug = db.Column(db.String(255), nullable=False)
coordinates = db.Column(Geography('POINT', srid=4326))
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
updated_at = db.Column(db.DateTime,
nullable=False,
default=datetime.now,
onupdate=datetime.now)
def get_coordinates(self):
if self.coordinates is None:
return self.coordinates
geom_json = json.loads(
db.session.scalar(func.ST_AsGeoJSON(self.coordinates)))
return {
"lng": geom_json['coordinates'][0],
"lat": geom_json['coordinates'][1]
}
def get_terms(self, since):
terms = self.terms.filter(Term.datetime_from >= since).\
order_by(Term.datetime_from)
return [{
'id': term.id,
'since': term.datetime_from,
'till': term.datetime_to
} for term in terms]
def __repr__(self):
return '<Container #{}: {}>'.format(self.id, self.slug)
class Address(Base):
"""To store the addresses of user.
`tag` can be Home, Work, Other etc.
"""
id = Column(Integer, primary_key=True, index=True)
user_id = Column(Integer, ForeignKey('user.id'), nullable=False)
user = relation("User", backref='addresses')
longitude = Column(Numeric(precision=15, scale=10), nullable=False)
latitude = Column(Numeric(precision=15, scale=10), nullable=False)
complete_address = Column(String(length=300), nullable=False)
location = Column(Geography(geometry_type='POINT', srid=4326),
nullable=False)
tag = Column(String(length=30), nullable=False)
floor = Column(String(length=50))
landmark = Column(String(length=100))
# Immutable address
deleted_at = Column(DateTime)
archived = Column(Boolean, default=False)
class AnimalSighting(Base):
__tablename__ = "animal_sightings"
id: UUID
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
location: str
location = Column(String)
coords: Geography
coords = Column(Geography("POINT", srid=4326))
images: typing.List[str]
images = Column(ARRAY(String))
animals: typing.List[str]
animals = Column(ARRAY(String))
poster = str
poster = Column(String)
timestamp = DateTime
timestamp = Column(DateTime(timezone=True), server_default=func.now())
def as_dict(self):
return {
c.name: str(getattr(self, c.name))
for c in self.__table__.columns
}
class Photo(db.Model):
"""User's uploaded photo"""
__tablename__ = 'photos'
photo_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
#Foreign keys:
user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'))
airport_id = db.Column(db.Integer, db.ForeignKey('airports.airport_id'))
photo_title = db.Column(db.String(25))
photo_lat = db.Column(db.Float)
photo_lng = db.Column(db.Float)
photo_location = db.Column(Geography(geometry_type='POINT', srid=4326),
nullable=True)
airport_dist = db.Column(db.Float)
datetime = db.Column(db.DateTime)
filepath = db.Column(db.String(100))
sunset_rating = db.Column(db.Integer)
description = db.Column(db.String(150))
#Relationships:
user = db.relationship('User', backref=db.backref('user_photos'))
airport = db.relationship('Airport', backref=db.backref('airport_photos'))
def __repr__(self):
"""shows this when the photo object is printed"""
return '<Photo id={} user={} date={} airport={}'.format(
self.photo_id, self.user_id, self.datetime, self.airport_id)
class Airport(db.Model):
"""Airports and their information"""
#******************** NOTE **********************#
# In order to use geoalchemy2 you have to go into
# sunsets db and type: 'CREATE EXTENSION postgis'
# I did this with a source script in /scripts.
#************************************************#
__tablename__ = 'airports'
#QUESTION:
#would it make more sense to use icao code as airport ID?
#commented out the city and state for now because they aren't neccesarily
#city and state in countries besides US.
airport_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
icao_code = db.Column(db.String(10), nullable=False, unique=True)
lattitude = db.Column(db.Float, nullable=False)
longitude = db.Column(db.Float, nullable=False)
airport_name = db.Column(db.String(150))
city = db.Column(db.String(50))
state = db.Column(db.String(20))
country = db.Column(db.String(20), nullable=True)
#This is going to be the location
#srid=4326 is default using negative values for lat/lon
location = db.Column(Geography(geometry_type='POINT', srid=4326),
nullable=True)
def __repr__(self):
"""Shows this information when airport object is printed"""
return '<Airport id={} code={} name={}>'.format(
self.airport_id, self.icao_code, self.airport_name)
def test_get_col_spec_no_typmod(self):
g = Geography(geometry_type=None)
assert g.get_col_spec() == 'geography'
def test_get_col_spec(self):
from geoalchemy2 import Geography
g = Geography(srid=900913)
eq_(g.get_col_spec(), 'geography(GEOMETRY,900913)')
def test_get_col_spec_no_typmod(self):
g = Geography(geometry_type=None)
assert g.get_col_spec() == 'geography'
def test_get_col_spec(self):
from geoalchemy2 import Geography
g = Geography(srid=900913)
eq_(g.get_col_spec(), 'geography(GEOMETRY,900913)')
def test_get_col_spec(self):
g = Geography(srid=900913)
assert g.get_col_spec() == 'geography(GEOMETRY,900913)'
from sqlalchemy.dialects.postgresql import TIMESTAMP, JSONB
from geoslurp.datapull.http import Uri as http
from geoalchemy2.types import Geography
# from geoalchemy2.elements import WKBElement
from datetime import datetime,timedelta
from geoslurp.tools.time import dt2yearlyinterval,dt2monthlyinterval,decyear2dt
import os
from zipfile import ZipFile
from osgeo import ogr
from geoslurp.config.slurplogger import slurplogger
from geoslurp.config.catalogue import geoslurpCatalogue
scheme="oceanobs"
geoPointtype = Geography(geometry_type="POINTZ", srid='4326', spatial_index=True,dimension=3)
#define a declarative baseclass for spherical harmonics gravity data
@as_declarative(metadata=MetaData(schema=scheme))
class PSMSLTBase(object):
@declared_attr
def __tablename__(cls):
#strip of the 'Table' from the class name
return cls.__name__[:-5].replace("-","_").lower()
id = Column(Integer, primary_key=True)
statname=Column(String)
lastupdate=Column(TIMESTAMP)
tstart=Column(TIMESTAMP,index=True)
tend=Column(TIMESTAMP,index=True)
ndat=Column(Integer)
countrycode=Column(String)
from geoalchemy2.elements import WKBElement
from geoslurp.datapull.http import Uri as http
from datetime import datetime
from zipfile import ZipFile
from geoslurp.config.slurplogger import slurplogger
from geoslurp.datapull import UriFile
from geoslurp.datapull import findFiles
from geoslurp.config.catalogue import geoslurpCatalogue
import re
import os
scheme = "oceanobs"
FrontsTBase = declarative_base(metadata=MetaData(schema=scheme))
geoLineStrType = Geography(geometry_type="MULTILINESTRING",
srid='4326',
spatial_index=True,
dimension=2)
class OrsifrontsTable(FrontsTBase):
"""Defines the Orsifonts PostgreSQL table"""
__tablename__ = 'orsifronts'
id = Column(Integer, primary_key=True)
name = Column(String)
acronym = Column(String)
geom = Column(geoLineStrType)
def orsiMetaExtractor(uri):
"""extract table data from the files"""
lookup = {
def test_get_col_spec(self):
g = Geography(srid=900913)
assert g.get_col_spec() == 'geography(GEOMETRY,900913)'
