class ReceiverPosition(db.Model):
__tablename__ = "receiver_positions"
reference_timestamp = db.Column(db.DateTime, primary_key=True)
# APRS data
name = db.Column(db.String)
dstcall = db.Column(db.String)
#relay = db.Column(db.String)
receiver_name = db.Column(db.String(9))
timestamp = db.Column(db.DateTime)
location = db.Column("location", Geometry("POINT", srid=4326))
symboltable = None
symbolcode = None
#track = db.Column(db.SmallInteger)
#ground_speed = db.Column(db.Float(precision=2))
altitude = db.Column(db.Float(precision=2))
comment = None
# Type information
beacon_type = None
aprs_type = None
# Debug information
raw_message = None
# Receiver specific data
user_comment = None
# Calculated values (from this software)
location_mgrs = db.Column(db.String(15)) # full mgrs (15 chars)
location_mgrs_short = db.Column(db.String(
9)) # reduced mgrs (9 chars), e.g. used for melissas range tool
agl = db.Column(db.Float(precision=2))
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table(
"flight_path_chunks",
sa.Column("id", Integer(), nullable=False),
sa.Column("time_created", DateTime(), nullable=False),
sa.Column("time_modified", DateTime(), nullable=False),
sa.Column("timestamps", postgresql.ARRAY(DateTime()), nullable=False),
sa.Column("locations",
Geometry(geometry_type="LINESTRING", srid=4326),
nullable=False),
sa.Column("start_time", DateTime(), nullable=False),
sa.Column("end_time", DateTime(), nullable=False),
sa.Column("flight_id", Integer(), nullable=False),
sa.ForeignKeyConstraint(["flight_id"], ["flights.id"],
ondelete="CASCADE"),
sa.PrimaryKeyConstraint("id"),
)
op.create_index(
"ix_flight_path_chunks_end_time",
"flight_path_chunks",
["end_time"],
unique=False,
)
op.create_index(
"ix_flight_path_chunks_start_time",
"flight_path_chunks",
["start_time"],
unique=False,
)
op.create_index(
"ix_flight_path_chunks_flight_id",
"flight_path_chunks",
["flight_id"],
unique=False,
)
class TraTrechoDutoL(Base):
__tablename__ = 'tra_trecho_duto_l'
__table_args__ = {'schema': 'bcim'}
id_objeto = Column(Integer, primary_key=True)
nome = Column(String(100))
nomeabrev = Column(String(50))
geometriaaproximada = Column(String(3))
nrdutos = Column(Integer)
tipotrechoduto = Column(String(22))
mattransp = Column(String(12))
setor = Column(String(21))
posicaorelativa = Column(String(15))
matconstr = Column(String(18))
situacaoespacial = Column(String(11))
operacional = Column(String(12))
situacaofisica = Column(Text)
geom = Column(Geometry('LINESTRING', 4674, from_text='ST_GeomFromEWKT', name='geometry'), index=True)
id_produtor = Column(Integer)
id_elementoprodutor = Column(Integer)
cd_insumo_orgao = Column(Integer)
nr_insumo_mes = Column(SmallInteger)
nr_insumo_ano = Column(SmallInteger)
tx_insumo_documento = Column(String(60))
class EcoExtMineralA(Base):
__tablename__ = 'eco_ext_mineral_a'
__table_args__ = {'schema': 'bcim'}
id_objeto = Column(Integer, primary_key=True)
nome = Column(String(100))
nomeabrev = Column(String(50))
geometriaaproximada = Column(String(3))
tiposecaocnae = Column(String(50))
operacional = Column(String(12))
situacaofisica = Column(Text)
tipoextmin = Column(String(20))
tipoprodutoresiduo = Column(String(40))
tipopocomina = Column(String(15))
procextracao = Column(String(12))
formaextracao = Column(String(12))
atividade = Column(String(12))
geom = Column(Geometry('POLYGON', 4674, from_text='ST_GeomFromEWKT', name='geometry'), index=True)
id_produtor = Column(Integer)
id_elementoprodutor = Column(Integer)
cd_insumo_orgao = Column(Integer)
nr_insumo_mes = Column(SmallInteger)
nr_insumo_ano = Column(SmallInteger)
tx_insumo_documento = Column(String(60))
class Dlm250Sie03P(Base):
__tablename__ = 'dlm250_sie03_p'
__table_args__ = {'schema': 'environment'}
gid = Column(
Integer,
primary_key=True,
server_default=text(
"nextval('environment.dlm250_sie03_p_gid_seq'::regclass)"))
land = Column(String(3))
modellart = Column(String(20))
objart = Column(String(5))
objart_txt = Column(String(50))
objid = Column(String(16))
hdu_x = Column(SmallInteger)
beginn = Column(String(20))
ende = Column(String(20))
bwf = Column(String(4))
hho = Column(Float(53))
nam = Column(String(100))
spo = Column(String(4))
zus = Column(String(4))
bemerkung = Column(String(200))
geom = Column(Geometry('POINT', 31467), index=True)
class Incident(db.Model):
_tablename_ = 'incident'
ID = db.Column(db.Integer, primary_key=True)
caseNumber = db.Column(db.String(15))
date = db.Column(db.DateTime)
block = db.Column(db.String(132))
IUCR = db.Column(db.String(10))
primaryType = db.Column(db.String(132))
description = db.Column(db.String(64))
locationDescription = db.Column(db.String(132))
arrest = db.Column(db.Boolean)
domestic = db.Column(db.Boolean)
beat = db.Column(db.Float, server_default='0')
district = db.Column(db.Float, server_default='0')
ward = db.Column(db.Float, server_default='0')
communityArea = db.Column(db.Float)
FBIcode = db.Column(db.String(20))
xCoord = db.Column(db.Float, default='')
yCoord = db.Column(db.Float, default='')
year = db.Column(db.Integer)
updatedOn = db.Column(db.String(32))
latitude = db.Column(db.Float, default='')
longitude = db.Column(db.Float, default='')
location = db.Column(Geometry('POINT'))
class Dlm250Sie02F(Base):
__tablename__ = 'dlm250_sie02_f'
__table_args__ = {'schema': 'environment'}
gid = Column(
Integer,
primary_key=True,
server_default=text(
"nextval('environment.dlm250_sie02_f_gid_seq'::regclass)"))
land = Column(String(3))
modellart = Column(String(20))
objart = Column(String(5))
objart_txt = Column(String(50))
objid = Column(String(16))
hdu_x = Column(SmallInteger)
beginn = Column(String(20))
ende = Column(String(20))
agt = Column(String(4))
fkt = Column(String(4))
nam = Column(String(100))
rgs = Column(String(100))
zus = Column(String(4))
bemerkung = Column(String(200))
geom = Column(Geometry('MULTIPOLYGON', 31467), index=True)
class Country(db.Model):
__tablename__ = "countries"
gid = db.Column(db.Integer, primary_key=True)
fips = db.Column(db.String(2))
iso2 = db.Column(db.String(2))
iso3 = db.Column(db.String(3))
un = db.Column(db.SmallInteger)
name = db.Column(db.String(50))
area = db.Column(db.Integer)
pop2005 = db.Column(db.BigInteger)
region = db.Column(db.SmallInteger)
subregion = db.Column(db.SmallInteger)
lon = db.Column(db.Float)
lat = db.Column(db.Float)
geom = db.Column("geom", Geometry("MULTIPOLYGON", srid=4326))
def __repr__(self):
return "<Country %s: %s,%s,%s,%s,%s,%s,%s,%s,%s,%s>" % (
self.fips, self.iso2, self.iso3, self.un, self.name, self.area,
self.pop2005, self.region, self.subregion, self.lon, self.lat)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column(
"zipcodes",
sa.Column(
"coordinates",
Geometry(geometry_type="POINT",
from_text="ST_GeomFromEWKT",
name="geometry"),
nullable=True,
),
)
# ### end Alembic commands ###
# Now populate coordinates from existing data
from airq.lib.util import chunk_list
from airq.models.zipcodes import Zipcode
bind = op.get_bind()
session = orm.Session(bind=bind)
updates = []
for zipcode in session.query(Zipcode).all():
data = dict(
id=zipcode.id,
coordinates=f"POINT({zipcode.longitude} {zipcode.latitude})",
)
updates.append(data)
print(f"Setting coordinates for {len(updates)} zipcodes")
num_processed = 0
for mappings in chunk_list(updates):
session.bulk_update_mappings(Zipcode, mappings)
session.commit()
num_processed += len(mappings)
print(f"Processed {num_processed} zipcodes")
class Position(db.Model):
__table_args__ = (PrimaryKeyConstraint('location', 'operator_code'), {})
# The SRID = 4326 is the ID of WGS84 projection
SRS_WGS_84 = 4326
location = db.Column(Geometry(geometry_type='POINT', srid=SRS_WGS_84), nullable=False)
operator_code = db.Column(db.Integer, db.ForeignKey('operator.mcc_mnc'), nullable=False)
has_2g = db.Column(db.Boolean, default=False)
has_3g = db.Column(db.Boolean, default=False)
has_4g = db.Column(db.Boolean, default=False)
@classmethod
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 __repr__(self):
return '<Position {} - Op {} - 2G {} - 3G {} - 4G {} >'.format(self.location, self.operator_code,
self.has_2g, self.has_3g, self.has_4g)
class Antenna(db.Model):
# Id de las antenas
mcc = db.Column(db.Integer, primary_key=True, default=214)
mnc = db.Column(db.Integer, primary_key=True)
lac = db.Column(db.Integer, primary_key=True)
cid = db.Column(db.Integer, primary_key=True)
# Coordenadas en latitud y longitud
lon = db.Column(db.Float)
lat = db.Column(db.Float)
# Radio de alcance de las antenas
range = db.Column(db.Integer)
# Objeto donde se almacena la latitud y longitud para posteriores consultas con PostGIS
point = db.Column(Geometry(geometry_type="POINT"))
# una antena puede tener varios numeros que han llamado usando dicha antena
telephones = db.relationship('Telephone', lazy=True, backref='antenna')
def __repr__(self):
return "<antenna {mcc} {mnc} {lac} {cid} ({lat}, {lon})>".format(
mcc=self.mcc, mnc=self.mnc, lac=self.lac, cid=self.cid, lat=self.lat, lon=self.lon)
@classmethod
def update_geometries(cls):
"""Esta función rellena la columna point con los datos proporcionados
por la latitud y longitud una vez han sido rellenados"""
ants = Antenna.query.all()
for ant in ants:
point = 'POINT({} {})'.format(ant.lon, ant.lat)
ant.point = point
db.session.commit()
class WnAbwGridMvGriddistrict(Base):
__tablename__ = 'wn_abw_grid_mv_griddistrict'
__table_args__ = {'schema': 'windnode'}
version = Column(Text, nullable=False)
subst_id = Column(Integer, primary_key=True)
subst_sum = Column(Integer)
type1 = Column(Integer)
type1_cnt = Column(Integer)
type2 = Column(Integer)
type2_cnt = Column(Integer)
type3 = Column(Integer)
type3_cnt = Column(Integer)
group = Column(String(1))
gem = Column(Integer)
gem_clean = Column(Integer)
zensus_sum = Column(Integer)
zensus_count = Column(Integer)
zensus_density = Column(Numeric)
population_density = Column(Numeric)
la_count = Column(Integer)
area_ha = Column(Numeric)
la_area = Column(Numeric(10, 1))
free_area = Column(Numeric(10, 1))
area_share = Column(Numeric(4, 1))
consumption = Column(Numeric)
consumption_per_area = Column(Numeric)
dea_cnt = Column(Integer)
dea_capacity = Column(Numeric)
lv_dea_cnt = Column(Integer)
lv_dea_capacity = Column(Numeric)
mv_dea_cnt = Column(Integer)
mv_dea_capacity = Column(Numeric)
geom_type = Column(Text)
geom = Column(Geometry('MULTIPOLYGON', 3035), index=True)
consumption_total = Column(Integer)
class EgoConventionalPowerplant(Base):
__tablename__ = 'ego_conventional_powerplant'
__table_args__ = {'schema': 'supply'}
gid = Column(Integer, primary_key=True)
bnetza_id = Column(Text)
company = Column(Text)
name = Column(Text)
postcode = Column(Text)
city = Column(Text)
street = Column(Text)
state = Column(Text)
block = Column(Text)
commissioned_original = Column(Text)
commissioned = Column(Float(53))
retrofit = Column(Float(53))
shutdown = Column(Float(53))
status = Column(Text)
fuel = Column(Text)
technology = Column(Text)
type = Column(Text)
eeg = Column(Text)
chp = Column(Text)
capacity = Column(Float(53))
capacity_uba = Column(Float(53))
chp_capacity_uba = Column(Float(53))
efficiency_data = Column(Float(53))
efficiency_estimate = Column(Float(53))
network_node = Column(Text)
voltage = Column(Text)
network_operator = Column(Text)
name_uba = Column(Text)
lat = Column(Float(53))
lon = Column(Float(53))
comment = Column(Text)
geom = Column(Geometry('POINT', 4326))
class FlightPathChunks(db.Model):
"""
This table stores flight path chunks of about 100 fixes per column which
enable PostGIS/Postgres to do fast queries due to tight bounding boxes
around those short flight pahts.
"""
__tablename__ = 'flight_path_chunks'
id = db.Column(Integer, autoincrement=True, primary_key=True)
time_created = db.Column(DateTime, nullable=False, default=datetime.utcnow)
time_modified = db.Column(DateTime,
nullable=False,
default=datetime.utcnow)
timestamps = deferred(db.Column(postgresql.ARRAY(DateTime),
nullable=False),
group='path')
locations = deferred(db.Column(Geometry('LINESTRING', srid=4326),
nullable=False),
group='path')
start_time = db.Column(DateTime, nullable=False, index=True)
end_time = db.Column(DateTime, nullable=False, index=True)
flight_id = db.Column(Integer,
db.ForeignKey('flights.id', ondelete='CASCADE'),
nullable=False,
index=True)
flight = db.relationship('Flight')
@staticmethod
def get_near_flights(flight, filter=None):
"""
WITH src AS
(SELECT ST_Buffer(ST_Simplify(locations, 0.005), 0.015) AS src_loc_buf,
start_time AS src_start,
end_time AS src_end
FROM flight_paths
WHERE flight_id = 8503)
SELECT (dst_points).geom AS dst_point,
dst_times[(dst_points).path[1]] AS dst_time,
dst_points_fid AS dst_flight_id
FROM
(SELECT ST_dumppoints(locations) as dst_points,
timestamps AS dst_times,
src_loc_buf,
flight_id AS dst_points_fid,
src_start,
src_end
FROM flight_paths, src
WHERE flight_id != 8503 AND
end_time >= src_start AND
start_time <= src_end AND
locations && src_loc_buf AND
_ST_Intersects(ST_Simplify(locations, 0.005), src_loc_buf)) AS foo
WHERE _ST_Contains(src_loc_buf, (dst_points).geom);
"""
cte = db.session.query(FlightPathChunks.locations.ST_Simplify(0.005).ST_Buffer(0.015).label('src_loc_buf'),
FlightPathChunks.start_time.label('src_start'),
FlightPathChunks.end_time.label('src_end')) \
.filter(FlightPathChunks.flight == flight) \
.cte('src')
subq = db.session.query(func.ST_DumpPoints(FlightPathChunks.locations).label('dst_points'),
FlightPathChunks.timestamps.label('dst_times'),
cte.c.src_loc_buf,
FlightPathChunks.flight_id.label('dst_points_fid'),
cte.c.src_start,
cte.c.src_end) \
.filter(and_(FlightPathChunks.flight != flight,
FlightPathChunks.end_time >= cte.c.src_start,
FlightPathChunks.start_time <= cte.c.src_end,
FlightPathChunks.locations.intersects(cte.c.src_loc_buf),
_ST_Intersects(FlightPathChunks.locations.ST_Simplify(0.005), cte.c.src_loc_buf))) \
.subquery()
dst_times = literal_column('dst_times[(dst_points).path[1]]')
q = db.session.query(subq.c.dst_points.geom.label('dst_location'),
dst_times.label('dst_time'),
subq.c.dst_points_fid.label('dst_point_fid')) \
.filter(_ST_Contains(subq.c.src_loc_buf, subq.c.dst_points.geom)) \
.order_by(subq.c.dst_points_fid, dst_times) \
.all()
src_trace = to_shape(flight.locations).coords
max_distance = 1000
other_flights = dict()
for point in q:
dst_time = point.dst_time
dst_loc = to_shape(point.dst_location).coords
# we might have got a destination point earier than source takeoff
# or later than source landing. Check this case and disregard early.
if dst_time < flight.takeoff_time or dst_time > flight.landing_time:
continue
# find point closest to given time
closest = bisect_left(flight.timestamps,
dst_time,
hi=len(flight.timestamps) - 1)
if closest == 0:
src_point = src_trace[0]
else:
# interpolate flight trace between two fixes
dx = (dst_time - flight.timestamps[closest - 1]).total_seconds() / \
(flight.timestamps[closest] - flight.timestamps[closest - 1]).total_seconds()
src_point_prev = src_trace[closest - 1]
src_point_next = src_trace[closest]
src_point = [
src_point_prev[0] +
(src_point_next[0] - src_point_prev[0]) * dx,
src_point_prev[1] +
(src_point_next[1] - src_point_prev[1]) * dx
]
point_distance = Location(
latitude=dst_loc[0][1],
longitude=dst_loc[0][0]).geographic_distance(
Location(latitude=src_point[1], longitude=src_point[0]))
if point_distance > max_distance:
continue
if point.dst_point_fid not in other_flights:
other_flights[point.dst_point_fid] = []
other_flights[point.dst_point_fid].append(
dict(times=list(), points=list()))
elif len(other_flights[point.dst_point_fid][-1]['times']) and \
(dst_time - other_flights[point.dst_point_fid][-1]['times'][-1]).total_seconds() > 600:
other_flights[point.dst_point_fid].append(
dict(times=list(), points=list()))
other_flights[point.dst_point_fid][-1]['times'].append(dst_time)
other_flights[point.dst_point_fid][-1]['points'].append(
Location(latitude=dst_loc[0][1], longitude=dst_loc[0][0]))
return other_flights
@staticmethod
def update_flight_path(flight):
from skylines.lib.xcsoar_ import flight_path
from skylines.lib.datetime import from_seconds_of_day
# Now populate the FlightPathChunks table with the (full) flight path
path_detailed = flight_path(flight.igc_file,
max_points=3000,
qnh=flight.qnh)
if len(path_detailed) < 2:
return False
# Number of points in each chunck.
num_points = 100
# Interval of the current chunck: [i, j] (-> path_detailed[i:j + 1])
i = 0
j = min(num_points - 1, len(path_detailed) - 1)
# Ensure that the last chunk contains at least two fixes
if j == len(path_detailed) - 2:
j = len(path_detailed) - 1
FlightPathChunks.query().filter(
FlightPathChunks.flight == flight).delete()
date_utc = flight.igc_file.date_utc
while True:
flight_path = FlightPathChunks(flight=flight)
# Save the timestamps of the coordinates
flight_path.timestamps = \
[from_seconds_of_day(date_utc, c.seconds_of_day) for c in path_detailed[i:j + 1]]
flight_path.start_time = path_detailed[i].datetime
flight_path.end_time = path_detailed[j].datetime
# Convert the coordinate into a list of tuples
coordinates = [(c.location['longitude'], c.location['latitude'])
for c in path_detailed[i:j + 1]]
# Create a shapely LineString object from the coordinates
linestring = LineString(coordinates)
# Save the new path as WKB
flight_path.locations = from_shape(linestring, srid=4326)
db.session.add(flight_path)
if j == len(path_detailed) - 1:
break
else:
i = j + 1
j = min(j + num_points, len(path_detailed) - 1)
if j == len(path_detailed) - 2:
j = len(path_detailed) - 1
db.session.commit()
return True
class Flight(db.Model):
__tablename__ = 'flights'
id = db.Column(Integer, autoincrement=True, primary_key=True)
time_created = db.Column(DateTime, nullable=False, default=datetime.utcnow)
time_modified = db.Column(DateTime,
nullable=False,
default=datetime.utcnow)
pilot_id = db.Column(Integer,
db.ForeignKey('users.id', ondelete='SET NULL'),
index=True)
pilot = db.relationship('User', foreign_keys=[pilot_id])
# Fallback if the pilot is not registered
pilot_name = db.Column(Unicode(255))
co_pilot_id = db.Column(Integer,
db.ForeignKey('users.id', ondelete='SET NULL'),
index=True)
co_pilot = db.relationship('User', foreign_keys=[co_pilot_id])
# Fallback if the co-pilot is not registered
co_pilot_name = db.Column(Unicode(255))
club_id = db.Column(Integer,
db.ForeignKey('clubs.id', ondelete='SET NULL'),
index=True)
club = db.relationship('Club', backref='flights')
model_id = db.Column(Integer,
db.ForeignKey('models.id', ondelete='SET NULL'))
model = db.relationship('AircraftModel')
registration = db.Column(Unicode(32))
competition_id = db.Column(Unicode(5))
# The date of the flight in local time instead of UTC. Used for scoring.
date_local = db.Column(Date, nullable=False, index=True)
takeoff_time = db.Column(DateTime, nullable=False, index=True)
scoring_start_time = db.Column(DateTime, nullable=True)
scoring_end_time = db.Column(DateTime, nullable=True)
landing_time = db.Column(DateTime, nullable=False)
takeoff_location_wkt = db.Column('takeoff_location',
Geometry('POINT', srid=4326))
landing_location_wkt = db.Column('landing_location',
Geometry('POINT', srid=4326))
takeoff_airport_id = db.Column(
Integer, db.ForeignKey('airports.id', ondelete='SET NULL'))
takeoff_airport = db.relationship('Airport',
foreign_keys=[takeoff_airport_id])
landing_airport_id = db.Column(
Integer, db.ForeignKey('airports.id', ondelete='SET NULL'))
landing_airport = db.relationship('Airport',
foreign_keys=[landing_airport_id])
timestamps = deferred(db.Column(postgresql.ARRAY(DateTime),
nullable=False),
group='path')
locations = deferred(db.Column(Geometry('LINESTRING', srid=4326),
nullable=False),
group='path')
olc_classic_distance = db.Column(Integer)
olc_triangle_distance = db.Column(Integer)
olc_plus_score = db.Column(Float)
igc_file_id = db.Column(Integer,
db.ForeignKey('igc_files.id', ondelete='CASCADE'),
nullable=False)
igc_file = db.relationship('IGCFile', backref='flights', innerjoin=True)
qnh = db.Column(Float)
needs_analysis = db.Column(Boolean, nullable=False, default=True)
# Privacy level of the flight
class PrivacyLevel:
PUBLIC = 0
LINK_ONLY = 1
PRIVATE = 2
privacy_level = db.Column(SmallInteger,
nullable=False,
default=PrivacyLevel.PUBLIC)
##############################
def __repr__(self):
return ('<Flight: id=%s, modified=%s>' %
(self.id, self.time_modified)).encode('unicode_escape')
##############################
@hybrid_property
def duration(self):
return self.landing_time - self.takeoff_time
@hybrid_property
def year(self):
return self.date_local.year
@hybrid_property
def index_score(self):
if self.model and self.model.dmst_index > 0:
return self.olc_plus_score * 100 / self.model.dmst_index
else:
return self.olc_plus_score
@index_score.expression
def index_score(cls):
return case([(AircraftModel.dmst_index > 0,
cls.olc_plus_score * 100 / AircraftModel.dmst_index)],
else_=cls.olc_plus_score)
@year.expression
def year(cls):
return db.func.date_part('year', cls.date_local)
@property
def takeoff_location(self):
if self.takeoff_location_wkt is None:
return None
coords = to_shape(self.takeoff_location_wkt)
return Location(latitude=coords.y, longitude=coords.x)
@takeoff_location.setter
def takeoff_location(self, location):
if location is None:
self.takeoff_location_wkt = None
else:
self.takeoff_location_wkt = location.to_wkt_element()
@property
def landing_location(self):
if self.landing_location_wkt is None:
return None
coords = to_shape(self.landing_location_wkt)
return Location(latitude=coords.y, longitude=coords.x)
@landing_location.setter
def landing_location(self, location):
if location is None:
self.landing_location_wkt = None
else:
self.landing_location_wkt = location.to_wkt_element()
@classmethod
def by_md5(cls, _md5):
file = IGCFile.by_md5(_md5)
if file is None:
return None
return cls.query().filter_by(igc_file=file).first()
# Permissions ################
@hybrid_method
def is_viewable(self, user):
return (self.privacy_level == Flight.PrivacyLevel.PUBLIC
or self.privacy_level == Flight.PrivacyLevel.LINK_ONLY
or self.is_writable(user))
@is_viewable.expression
def is_viewable_expression(cls, user):
return or_(cls.privacy_level == Flight.PrivacyLevel.PUBLIC,
cls.privacy_level == Flight.PrivacyLevel.LINK_ONLY,
cls.is_writable(user))
@hybrid_method
def is_listable(self, user):
return (self.privacy_level == Flight.PrivacyLevel.PUBLIC
or self.is_writable(user))
@is_listable.expression
def is_listable_expression(cls, user):
return or_(cls.privacy_level == Flight.PrivacyLevel.PUBLIC,
cls.is_writable(user))
@hybrid_method
def is_rankable(self):
return self.privacy_level == Flight.PrivacyLevel.PUBLIC
@hybrid_method
def is_writable(self, user):
return user and \
(self.igc_file.owner_id == user.id or
self.pilot_id == user.id or
user.is_manager())
@is_writable.expression
def is_writable_expression(self, user):
return user and (user.is_manager() or or_(IGCFile.owner_id == user.id,
self.pilot_id == user.id))
@hybrid_method
def may_delete(self, user):
return user and (self.igc_file.owner_id == user.id
or user.is_manager())
##############################
@classmethod
def get_largest(cls):
"""Returns a query object ordered by distance"""
return cls.query().order_by(cls.olc_classic_distance.desc())
def get_optimised_contest_trace(self, contest_type, trace_type):
from skylines.model.trace import Trace
return Trace.query(contest_type=contest_type,
trace_type=trace_type,
flight=self).first()
def get_contest_speed(self, contest_type, trace_type):
contest = self.get_optimised_contest_trace(contest_type, trace_type)
return contest and contest.speed
def get_contest_legs(self, contest_type, trace_type):
return ContestLeg.query(contest_type=contest_type, trace_type=trace_type,
flight=self) \
.filter(ContestLeg.end_time - ContestLeg.start_time > timedelta(0)) \
.order_by(ContestLeg.start_time)
@property
def speed(self):
return self.get_contest_speed('olc_plus', 'classic')
@property
def has_phases(self):
return bool(self._phases)
@property
def phases(self):
return [p for p in self._phases if not p.aggregate]
def delete_phases(self):
self._phases = []
@property
def circling_performance(self):
from skylines.model.flight_phase import FlightPhase
stats = [
p for p in self._phases
if (p.aggregate and p.phase_type == FlightPhase.PT_CIRCLING
and p.duration.total_seconds() > 0)
]
order = [
FlightPhase.CD_TOTAL, FlightPhase.CD_LEFT, FlightPhase.CD_RIGHT,
FlightPhase.CD_MIXED
]
stats.sort(lambda a, b: cmp(order.index(a.circling_direction),
order.index(b.circling_direction)))
return stats
@property
def cruise_performance(self):
from skylines.model.flight_phase import FlightPhase
return [
p for p in self._phases
if p.aggregate and p.phase_type == FlightPhase.PT_CRUISE
]
def update_flight_path(self):
from skylines.lib.xcsoar_ import flight_path
from skylines.lib.datetime import from_seconds_of_day
# Run the IGC file through the FlightPath utility
path = flight_path(self.igc_file, qnh=self.qnh)
if len(path) < 2:
return False
# Save the timestamps of the coordinates
date_utc = self.igc_file.date_utc
self.timestamps = \
[from_seconds_of_day(date_utc, c.seconds_of_day) for c in path]
# Convert the coordinate into a list of tuples
coordinates = [(c.location['longitude'], c.location['latitude'])
for c in path]
# Create a shapely LineString object from the coordinates
linestring = LineString(coordinates)
# Save the new path as WKB
self.locations = from_shape(linestring, srid=4326)
return True
class MappedClass(Base):
__tablename__ = "table"
id = Column(types.Integer, primary_key=True)
text = Column(types.Unicode)
geom = Column(
Geometry(geometry_type='GEOMETRY', dimension=2, srid=4326))
def test_get_col_spec(self):
g = Geometry(geometry_type='CURVE', srid=900913)
assert g.get_col_spec() == 'geometry(CURVE,900913)'
class VernetzenWindPotentialArea(Base):
__tablename__ = 'vernetzen_wind_potential_area'
__table_args__ = {'schema': 'supply'}
region_key = Column(String, primary_key=True)
geom = Column(Geometry('MULTIPOLYGON', 25832), index=True)
class Incident(db.Model):
"""
This model schema is taken directly from CSV available
at SF Open Data SODA API for SFPD crime incident records
and reports from 2018 to present. The point model attribute
is set to a database column of type Geometry, available
through the Postgis extension for PostgreSQL.
"""
id = db.Column(db.Integer, primary_key=True)
incident_datetime = db.Column(db.DateTime)
incident_date = db.Column(db.Date)
incident_time = db.Column(db.Time)
incident_year = db.Column(db.Integer)
incident_day_of_the_week = db.Column(db.String(12), unique=False)
report_datetime = db.Column(db.DateTime)
row_id = db.Column(db.BigInteger)
incident_id = db.Column(db.Integer)
incident_number = db.Column(db.Integer)
cad_number = db.Column(db.String(12))
report_type_code = db.Column(db.String(12), unique=False)
report_type_description = db.Column(db.Text)
filed_online = db.Column(db.String(12))
incident_code = db.Column(db.String(12), unique=False)
incident_category = db.Column(db.Text)
incident_subcategory = db.Column(db.Text)
incident_description = db.Column(db.Text)
resolution = db.Column(db.Text)
intersection = db.Column(db.Text)
cnn = db.Column(db.String(12))
police_district = db.Column(db.String(30), unique=False)
analysis_neighborhood = db.Column(db.String(30), unique=False)
supervisor_district = db.Column(db.String(12))
latitude = db.Column(db.Float, None)
longitude = db.Column(db.Float, None)
point = db.Column(Geometry(geometry_type='Point'))
# Take model instance and serialize as a JSON object.
# This allows for data to be easily exchanged while
# also easily convertable to its original type.
# For unserializable objects use str()
@property
def serialize(self):
return {
'id': self.id,
'incident_datetime': self.incident_datetime,
'incident_date': self.incident_date,
'incident_time': str(self.incident_time),
'incident_year': self.incident_year,
'incident_day_of_the_week': self.incident_day_of_the_week,
'report_datetime': self.report_type_code,
'row_id': self.row_id,
'incident_id': self.incident_id,
'incident_number': self.incident_number,
'cad_number': self.cad_number,
'report_type_code': self.report_type_code,
'report_type_description': self.report_type_description,
'filed_online': self.filed_online,
'incident_code': self.incident_code,
'incident_category': self.incident_subcategory,
'incident_subcategory': self.incident_subcategory,
'incident_description': self.incident_description,
'resolution': self.resolution,
'intersection': self.intersection,
'cnn': self.cnn,
'police_district': self.police_district,
'analysis_neighborhood': self.analysis_neighborhood,
'supervisor_district': self.supervisor_district,
'latitude': self.latitude,
'longitude': self.longitude,
'point': str(self.point),
}
def __repr__(self):
return '<Incident ID %r>' % self.incident_id
def test_get_col_spec(self):
from geoalchemy2.types import Geometry
g = Geometry(geometry_type='GEOMETRYCOLLECTION', srid=900913)
eq_(g.get_col_spec(), 'geometry(GEOMETRYCOLLECTION,900913)')
def creer_graph(gdf, bdd,id_name='id', schema='public', table='graph_temp', table_vertex='graph_temp_vertices_pgr',localisation='boulot'):
"""
creer un graph a partir d'une geodataframe en utilisant les ofnctions de postgis.
attention, pas de return, la table reste dans postgis.
attention aussi si les lignes en entree sont des multilignes : seule la 1ere composante estconservee
en entree :
gdf : geodataframe de geopandas
bdd : string, bdd postgis utilisee pour faire le graph
id_name : string : nom de la colonne contenant l'id_uniq
schema : string, nom du schema ou stocke le graph teporaire dans postgis
table : string, nom dde la table ou stocke le graph teporaire dans postgis
table_vertex : string, nom de l table des vertex ou stocke le graph teporaire dans postgis
"""
gdf_w=gdf.copy()
#verifier que l'identifiant est un entier
if gdf[id_name].dtype!=np.int64 :
raise TypeError('l''id doit etre converti en entier')
#verifier qu'on a bien une geoDataFrame
if not isinstance(gdf_w,gp.GeoDataFrame) :
raise TypeError('if faut convertir les donnees en GeoDataFrame')
#trouver le nom de la geom
geom_name=gdf_w.geometry.name
#passer les donnees en 2D et en linestring si Multi
gdf_w[geom_name]=gdf_w.geometry.apply(lambda x : LineString([xy[0:2] for xy in list(x[0].coords)]) if x.geom_type=='MultiLineString' else
LineString([xy[0:2] for xy in list(x.coords)]))
#type de geom ets rid
geo_type=gdf_w.geometry.geom_type.unique()[0].upper()
geo_srid=int(gdf_w.crs.to_string().split(':')[1])
#passer la geom en texte pour export dans postgis
gdf_w[geom_name] = gdf_w[geom_name].apply(lambda x: WKTElement(x.wkt, srid=geo_srid))
with ct.ConnexionBdd(bdd, localisation=localisation) as c:
#supprimer table si elle existe
rqt=f"drop table if exists {schema}.{table} ; drop table if exists {schema}.{table_vertex} "
c.sqlAlchemyConn.execute(rqt)
print(f'creer_graph : donnees mise en forme, connexion ouverte ; {datetime.now()}')
#passer les donnees
gdf_w.to_sql(table,c.sqlAlchemyConn,schema=schema, index=False,
dtype={geom_name:Geometry(geo_type, srid=geo_srid)})
print(f'creer_graph : donnees transferees dans la base postgis ; {datetime.now()}')
rqt_modif_geom=f"""alter table {schema}.{table} rename column {geom_name} to geom ;
alter table {schema}.{table} alter column geom type geometry(MULTILINESTRING,{geo_srid})
using st_Multi(geom)""" if geom_name!='geom' else f"""
alter table {schema}.{table} alter column geom type geometry(MULTILINESTRING,{geo_srid})
using st_Multi(geom) ; """
rqt_modif_attr=f"""alter table {schema}.{table} alter column "source" TYPE int8 ;
alter table {schema}.{table} alter column "target" TYPE int8"""
c.sqlAlchemyConn.execute(rqt_modif_geom)
c.sqlAlchemyConn.execute(rqt_modif_attr)
print(f'creer_graph : geometrie modifiee ; {datetime.now()}')
#creer le graph : soit source et target existent deja et on les remets a null, soit on les crees
if all([a in gdf_w.columns for a in ['source', 'target']]) :
rqt_creation_graph=f"""update {schema}.{table} set source=null::integer, target=null::integer ;
select pgr_createTopology('{schema}.{table}', 0.001,'geom','{id_name}')"""
elif all([a not in gdf_w.columns for a in ['source', 'target']]):
rqt_creation_graph=f"""alter table {schema}.{table} add column source int, add column target int ;
select pgr_createTopology('{schema}.{table}', 0.001,'geom','{id_name}')"""
c.sqlAlchemyConn.execute(rqt_creation_graph)
print(f'creer_graph : topologie cree ; {datetime.now()}')
rqt_anlyse_graph=f"SELECT pgr_analyzeGraph('{schema}.{table}', 0.001,'geom','{id_name}')"
c.curs.execute(rqt_anlyse_graph)#je le fait avec psycopg2 car avec sql acchemy ça ne passe pas
print(f'creer_graph : graph cree ; {datetime.now()}')
c.connexionPsy.commit()
def test_get_col_spec(self):
g = Geometry(srid=900913)
assert g.get_col_spec() == 'geometry(GEOMETRY,900913)'
def test_get_col_spec(self):
from geoalchemy2.types import Geometry
g = Geometry(geometry_type='LINESTRING', srid=900913)
eq_(g.get_col_spec(), 'geometry(LINESTRING,900913)')
def test_get_col_spec(self):
g = Geometry(geometry_type='GEOMETRYCOLLECTION', srid=900913)
assert g.get_col_spec() == 'geometry(GEOMETRYCOLLECTION,900913)'
def test_get_col_spec(self):
g = Geometry(geometry_type='MULTIPOLYGON', srid=900913)
assert g.get_col_spec() == 'geometry(MULTIPOLYGON,900913)'
def test_get_col_spec(self):
g = Geometry(geometry_type='MULTILINESTRING', srid=900913)
assert g.get_col_spec() == 'geometry(MULTILINESTRING,900913)'
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'bloodbank', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=150), nullable=False),
sa.Column('address', sa.String(length=1000), nullable=False),
sa.Column('contact_no', sa.String(length=20), nullable=False),
sa.Column('email', sa.String(length=120), nullable=False),
sa.Column('latitude', sa.Float(precision=20), nullable=False),
sa.Column('longitude', sa.Float(precision=20), nullable=False),
sa.Column('geom',
Geometry(geometry_type='POINT',
from_text='ST_GeomFromEWKT',
name='geometry'),
nullable=False), sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('contact_no'))
op.create_table(
'donor', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=30), nullable=False),
sa.Column('email', sa.String(length=120), nullable=False),
sa.Column('contact_no', sa.String(length=15), nullable=False),
sa.Column('blood_group', sa.String(length=5), nullable=False),
sa.Column('last_donation', sa.Date(), nullable=False),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('contact_no'),
sa.UniqueConstraint('email'))
op.create_table(
'app_user', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=30), nullable=False),
sa.Column('email', sa.String(length=120), nullable=False),
sa.Column('image_file', sa.String(length=20), nullable=False),
sa.Column('password', sa.String(length=60), nullable=False),
sa.Column('contact_no', sa.String(length=15), nullable=False),
sa.Column('role', sa.String(length=15), nullable=False),
sa.Column('bloodbank_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['bloodbank_id'],
['bloodbank.id'],
), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('contact_no'),
sa.UniqueConstraint('email'))
op.create_table(
'bloodbank_stats',
sa.Column('bloodbank_id', sa.Integer(), nullable=False),
sa.Column('a_positive', sa.Integer(), nullable=False),
sa.Column('a_negative', sa.Integer(), nullable=False),
sa.Column('b_positive', sa.Integer(), nullable=False),
sa.Column('b_negative', sa.Integer(), nullable=False),
sa.Column('ab_positive', sa.Integer(), nullable=False),
sa.Column('ab_negative', sa.Integer(), nullable=False),
sa.Column('o_positive', sa.Integer(), nullable=False),
sa.Column('o_negative', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['bloodbank_id'],
['bloodbank.id'],
), sa.PrimaryKeyConstraint('bloodbank_id'))
op.create_table(
'donation', sa.Column('donor_id', sa.Integer(), nullable=False),
sa.Column('bloodbank_id', sa.Integer(), nullable=False),
sa.Column('date', sa.Date(), nullable=False),
sa.Column('units', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['bloodbank_id'],
['bloodbank.id'],
), sa.ForeignKeyConstraint(
['donor_id'],
['donor.id'],
), sa.PrimaryKeyConstraint('donor_id', 'bloodbank_id', 'date'))
op.create_table(
'utilisation', sa.Column('bloodbank_id', sa.Integer(), nullable=False),
sa.Column('date_time', sa.DateTime(), nullable=False),
sa.Column('blood_group', sa.String(length=5), nullable=False),
sa.Column('units', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['bloodbank_id'],
['bloodbank.id'],
), sa.PrimaryKeyConstraint('bloodbank_id', 'date_time'))
op.create_table(
'request', sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('date', sa.DateTime(), nullable=False),
sa.Column('blood_group', sa.String(length=5), nullable=False),
sa.Column('units', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['user_id'],
['app_user.id'],
), sa.PrimaryKeyConstraint('user_id', 'date'))
def test_get_col_spec_no_typmod(self):
g = Geometry(geometry_type=None)
assert g.get_col_spec() == 'geometry'
fk_s3 = Column(String(2))
nuts = Column(String(5))
rs_0 = Column(String(12))
ags_0 = Column(String(12))
wsk = Column(Date)
debkg_id = Column(String(16))
t_bkg_vg250_1_sta_bbox_mview = Table('bkg_vg250_1_sta_bbox_mview',
metadata,
Column('reference_date', Text),
Column('id', Integer, unique=True),
Column('bez', Text),
Column('area_ha', Float(53)),
Column('geom',
Geometry('POLYGON', 3035),
index=True),
schema='boundaries')
t_bkg_vg250_1_sta_error_geom_mview = Table('bkg_vg250_1_sta_error_geom_mview',
metadata,
Column('id', Integer, unique=True),
Column('error', Boolean),
Column('error_reason', String),
Column('geom',
Geometry('POINT', 3035),
index=True),
schema='boundaries')
t_bkg_vg250_1_sta_mview = Table('bkg_vg250_1_sta_mview',
metadata,
class ContestLeg(db.Model):
"""
This table saves the legs of a optimized Flight.
"""
__tablename__ = 'contest_legs'
id = db.Column(Integer, autoincrement=True, primary_key=True)
flight_id = db.Column(Integer,
db.ForeignKey('flights.id', ondelete='CASCADE'),
nullable=False,
index=True)
flight = db.relationship('Flight',
innerjoin=True,
backref=db.backref(
'_legs',
passive_deletes=True,
cascade='all, delete, delete-orphan'))
contest_type = db.Column(String, nullable=False)
trace_type = db.Column(String, nullable=False)
# direct distance from start to end
distance = db.Column(Integer)
# total height and duration of cruise phases
cruise_height = db.Column(Integer)
cruise_distance = db.Column(Integer)
cruise_duration = db.Column(Interval)
# total height and duration of climb phases
climb_height = db.Column(Integer)
climb_duration = db.Column(Interval)
# start and end height
start_height = db.Column(Integer)
end_height = db.Column(Integer)
# start and end time
start_time = db.Column(DateTime, nullable=False)
end_time = db.Column(DateTime, nullable=False)
# start and end locations
start_location_wkt = db.Column('start_location',
Geometry('POINT', srid=4326))
end_location_wkt = db.Column('end_location', Geometry('POINT', srid=4326))
@property
def duration(self):
return self.end_time - self.start_time
@property
def speed(self):
if self.distance is None:
return None
return float(self.distance) / self.duration.total_seconds()
@property
def start_location(self):
if self.start_location_wkt is None:
return None
coords = to_shape(self.start_location_wkt)
return Location(latitude=coords.y, longitude=coords.x)
@start_location.setter
def start_location(self, location):
if location is None:
self.start_location_wkt = None
else:
self.start_location_wkt = location.to_wkt_element()
@property
def end_location(self):
if self.end_location_wkt is None:
return None
coords = to_shape(self.end_location_wkt)
return Location(latitude=coords.y, longitude=coords.x)
@end_location.setter
def end_location(self, location):
if location is None:
self.end_location_wkt = None
else:
self.end_location_wkt = location.to_wkt_element()
Column('type', Text),
Column('eeg', Text),
Column('chp', Text),
Column('capacity', Float(53)),
Column('capacity_uba', Float(53)),
Column('chp_capacity_uba', Float(53)),
Column('efficiency_data', Float(53)),
Column('efficiency_estimate', Float(53)),
Column('network_node', Text),
Column('voltage', Text),
Column('network_operator', Text),
Column('name_uba', Text),
Column('lat', Float(53)),
Column('lon', Float(53)),
Column('comment', Text),
Column('geom', Geometry('POINT', 4326)),
Column('voltage_level', SmallInteger),
Column('subst_id', BigInteger),
Column('otg_id', BigInteger),
Column('un_id', BigInteger),
Column('la_id', Integer),
Column('scenario', Text),
Column('flag', Text),
Column('nuts', String),
schema='supply')
t_ego_dp_conv_powerplant_nep2035_mview = Table(
'ego_dp_conv_powerplant_nep2035_mview',
metadata,
Column('version', Text),
Column('id', Integer),
class Receiver(db.Model):
__tablename__ = "receivers"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(9))
location_wkt = db.Column("location", Geometry("POINT", srid=4326))
altitude = db.Column(db.Float(precision=2))
firstseen = db.Column(db.DateTime, index=True)
lastseen = db.Column(db.DateTime, index=True)
timestamp = db.Column(db.DateTime, index=True)
version = db.Column(db.String)
platform = db.Column(db.String)
cpu_temp = db.Column(db.Float(precision=2))
rec_input_noise = db.Column(db.Float(precision=2))
agl = db.Column(db.Float(precision=2))
# Relations
country_id = db.Column(db.Integer,
db.ForeignKey("countries.gid", ondelete="SET NULL"),
index=True)
country = db.relationship("Country",
foreign_keys=[country_id],
backref=db.backref(
"receivers", order_by="Receiver.name.asc()"))
airport_id = db.Column(db.Integer,
db.ForeignKey("airports.id", ondelete="CASCADE"),
index=True)
airport = db.relationship("Airport",
foreign_keys=[airport_id],
backref=db.backref(
"receivers", order_by="Receiver.name.asc()"))
__table_args__ = (db.Index('idx_receivers_name_uc', 'name', unique=True), )
@property
def location(self):
if self.location_wkt is None:
return None
coords = to_shape(self.location_wkt)
return Location(lat=coords.y, lon=coords.x)
@property
def state(self):
import datetime
if datetime.datetime.utcnow() - self.lastseen < datetime.timedelta(
minutes=10):
return ReceiverState.OK if len(
self.statistics) > 0 else ReceiverState.ZOMBIE
elif datetime.datetime.utcnow() - self.lastseen < datetime.timedelta(
hours=1):
return ReceiverState.UNKNOWN
else:
return ReceiverState.OFFLINE
def airports_nearby(self):
query = (db.session.query(
Airport,
db.func.st_distance_sphere(self.location_wkt,
Airport.location_wkt),
db.func.st_azimuth(
self.location_wkt, Airport.location_wkt)).filter(
db.func.st_contains(
db.func.st_buffer(Airport.location_wkt, 1),
self.location_wkt)).filter(Airport.style.in_(
(2, 4, 5))).order_by(
db.func.st_distance_sphere(
self.location_wkt,
Airport.location_wkt).asc()).limit(5))
airports = [(airport, distance, azimuth)
for airport, distance, azimuth in query]
return airports
class BnetzaEegAnlagenstammdaten(Base):
__tablename__ = 'bnetza_eeg_anlagenstammdaten'
__table_args__ = {'schema': 'supply'}
id = Column(
Integer,
primary_key=True,
server_default=text(
"nextval('supply.bnetza_eeg_anlagenstammdaten_id_seq'::regclass)"))
version = Column(Text)
meldedatum = Column(Date)
meldegrund = Column(Text)
anlagennummer = Column(String(14))
_1_8_eeg_anlagenschlüssel = Column('1.8_eeg-anlagenschl\xfcssel', Text)
_3_1_genehmigungs_datum = Column('3.1_genehmigungs-datum', Date)
_3_2_genehmigungs_behörde = Column('3.2_genehmigungs-beh\xf6rde', Text)
_3_3_genehmigungs_aktenzeichen = Column('3.3_genehmigungs-aktenzeichen',
Text)
_3_4_geplantes_inbetriebnahme_datum = Column(
'3.4_geplantes_inbetriebnahme-datum', Date)
_3_5_errichtungs_frist = Column('3.5_errichtungs-frist', Date)
_4_1_energieträger = Column('4.1_energietr\xe4ger', Text)
_4_2_installierte_leistung = Column('4.2_installierte_leistung', Float(53))
_4_2_1_inst__leistung_vor_änderung = Column(
'4.2.1_inst._leistung_vor_\xe4nderung', Float(53))
_4_2_2_inst__leistung_nach_änderung = Column(
'4.2.2_inst._leistung_nach_\xe4nderung', Float(53))
_4_3_tatsächliche_inbetriebnahme = Column(
'4.3_tats\xe4chliche_inbetriebnahme', Date)
_4_4_datum_leistungsänderung = Column('4.4_datum_leistungs\xe4nderung',
Date)
_4_5_stilllegungsdatum = Column('4.5_stilllegungsdatum', Date)
_4_6_name_der_anlage = Column('4.6_name_der_anlage', Text)
_4_7_strasse_bzw__flurstück = Column('4.7_strasse_bzw._flurst\xfcck', Text)
_4_8_hausnummer = Column('4.8_hausnummer', Text)
_4_9_postleitzahl = Column('4.9_postleitzahl', Text)
_4_10_ort_bzw__gemarkung = Column('4.10_ort_bzw._gemarkung', Text)
_4_10_1_gemeindeschlüssel = Column('4.10_1_gemeindeschl\xfcssel', Text)
_4_11_bundesland = Column('4.11_bundesland', Text)
_4_12_utm_zonenwert = Column('4.12_utm-zonenwert', Integer)
_4_12_utm_east = Column('4.12_utm-east', Float(53))
_4_12_utm_north = Column('4.12_utm-north', Float(53))
_4_13_zugehörigkeit_anlagenpark = Column(
'4.13_zugeh\xf6rigkeit_anlagenpark', Text)
_4_13_1_name_des_anlagenparks = Column('4.13.1_name_des_anlagenparks',
Text)
_4_14_spannungsebene = Column('4.14_spannungsebene', Text)
_4_15_netzanschlusspunkt = Column('4.15_netzanschlusspunkt', Text)
_4_15_1_zählpunktbezeichnung = Column('4.15.1_z\xe4hlpunktbezeichnung',
Text)
_4_16_name_des_netzbetreibers = Column('4.16_name_des_netzbetreibers',
Text)
_4_17_fernsteuerbarkeit_durch = Column('4.17_fernsteuerbarkeit_durch',
Text)
_4_18_gemeinsame_techn__einrichtung = Column(
'4.18_gemeinsame_techn._einrichtung', Text)
_4_19_inanspruchnahme_finanzielle_Förderung = Column(
'4.19_inanspruchnahme_finanzielle_F\xf6rderung', Text)
_4_20_Eigenverbrauch_geplant = Column('4.20_Eigenverbrauch_geplant', Text)
_5_1_eingesetzte_biomasse = Column('5.1_eingesetzte_biomasse', Text)
_5_2_ausschließlich_biomasse = Column('5.2_ausschlie\xdflich_biomasse',
Text)
_5_3_flexprämie_eeg = Column('5.3_flexpr\xe4mie_eeg', Text)
_5_4_erstmalige_inanspruchnahme_flexprämie = Column(
'5.4_erstmalige_inanspruchnahme_flexpr\xe4mie', Date)
_5_4_1_leistungserhöhung_flexprämie = Column(
'5.4.1_leistungserh\xf6hung_flexpr\xe4mie', Text)
_5_4_2_datum_leistungserhöhung_flexprämie = Column(
'5.4.2_datum_leistungserh\xf6hung_flexpr\xe4mie', Date)
_5_4_3_umfang_der_leistungserhöhung = Column(
'5.4.3_umfang_der_leistungserh\xf6hung', Text)
_5_5_erstmalig_ausschließlich_biomethan = Column(
'5.5_erstmalig_ausschlie\xdflich_biomethan', Text)
_5_6_zustimmung_gesonderte_veröffentlich = Column(
'5.6_zustimmung_gesonderte_ver\xf6ffentlich', Text)
_6_1_kwk_anlage = Column('6.1_kwk-anlage', Text)
_6_2_thermische_leistung = Column('6.2_thermische_leistung', Float(53))
_6_3_andere_energieträger = Column('6.3_andere_energietr\xe4ger', Text)
_6_4_eingesetzte_andere_energieträger = Column(
'6.4_eingesetzte_andere_energietr\xe4ger', Text)
_6_5_erstmalige_stromerzeugung = Column('6.5_erstmalige_stromerzeugung',
Date)
_7_1_windanlagenhersteller = Column('7.1_windanlagenhersteller', Text)
_7_2_anlagentyp = Column('7.2_anlagentyp', Text)
_7_3_nabenhöhe = Column('7.3_nabenh\xf6he', Float(53))
_7_4_rotordurch_messer = Column('7.4_rotordurch-messer', Float(53))
_7_5_repowering = Column('7.5_repowering', Text)
_7_6_stilllegung_gemeldet = Column('7.6_stilllegung_gemeldet', Text)
_7_7_1_mittlere_windgeschwindigkeit = Column(
'7.7.1_mittlere_windgeschwindigkeit', Float(53))
_7_7_2_formparameter_weibull_verteilung = Column(
'7.7.2_formparameter_weibull-verteilung', Float(53))
_7_7_3_skalenparameter_weibull_verteilung = Column(
'7.7.3_skalenparameter_weibull-verteilung', Float(53))
_7_7_4_ertrags_einschätzung = Column('7.7.4_ertrags-einsch\xe4tzung',
Float(53))
_7_7_5_ertragseinschätzung_referenzertrag = Column(
'7.7.5_ertragseinsch\xe4tzung/referenzertrag', Float(53))
_7_8_1_seelage = Column('7.8.1_seelage', Text)
_7_8_2_wassertiefe = Column('7.8.2_wassertiefe', Text)
_7_8_3_küstenentfernung = Column('7.8.3_k\xfcstenentfernung', Text)
_7_9_pilotwindanlage = Column('7.9_pilotwindanlage', Text)
_8_1_ertüchtigung_wasserkraftanlage = Column(
'8.1_ert\xfcchtigung_wasserkraftanlage', Text)
_8_2_art_der_ertüchtigung = Column('8.2_art_der_ert\xfcchtigung', Text)
_8_3_zulassungspflichtige_maßnahme = Column(
'8.3_zulassungspflichtige_ma\xdfnahme', Text)
_8_4__höhe_leistungssteigerung = Column('8.4._h\xf6he_leistungssteigerung',
Float(53))
_8_5_datum_der_ertüchtigung = Column('8.5_datum_der_ert\xfcchtigung', Date)
_9_1_zuschlagnummer_pv_freifläche = Column(
'9.1_zuschlagnummer_pv-freifl\xe4che', Text)
_9_2_fläche_pv_freiflächenanlage = Column(
'9.2_fl\xe4che_pv-freifl\xe4chenanlage', Float(53))
_9_3_pv_freifläche_auf_ackerland = Column(
'9.3_pv-freifl\xe4che_auf_ackerland', Float(53))
geom = Column(Geometry('POINT', 5652))
Column('sector_share_sum', Numeric),
Column('sector_count_residential', Integer),
Column('sector_count_retail', Integer),
Column('sector_count_industrial', Integer),
Column('sector_count_agricultural', Integer),
Column('sector_count_sum', Integer),
Column('sector_consumption_residential', Float(53)),
Column('sector_consumption_retail', Float(53)),
Column('sector_consumption_industrial', Float(53)),
Column('sector_consumption_agricultural', Float(53)),
Column('sector_consumption_sum', Float(53)),
Column('sector_peakload_retail', Float(53)),
Column('sector_peakload_residential', Float(53)),
Column('sector_peakload_industrial', Float(53)),
Column('sector_peakload_agricultural', Float(53)),
Column('geom_centroid', Geometry('POINT', 3035)),
Column('geom_surfacepoint', Geometry('POINT', 3035)),
Column('geom_centre', Geometry('POINT', 3035)),
Column('geom', Geometry('POLYGON', 3035), index=True),
schema='demand')
t_ego_dp_loadarea_v0_4_5_mview = Table(
'ego_dp_loadarea_v0_4_5_mview',
metadata,
Column('version', Text),
Column('id', Integer, unique=True),
Column('subst_id', Integer),
Column('area_ha', Numeric),
Column('nuts', String(5)),
Column('rs_0', String(12)),
Column('ags_0', String(12)),
def test_get_col_spec(self):
from geoalchemy2.types import Geometry
g = Geometry(geometry_type='CURVE', srid=900913)
eq_(g.get_col_spec(), 'geometry(CURVE,900913)')
def test_get_col_spec(self):
g = Geometry(geometry_type='POINT', srid=900913)
assert g.get_col_spec() == 'geometry(POINT,900913)'
def test_get_col_spec(self):
from geoalchemy2.types import Geometry
g = Geometry(geometry_type='MULTIPOLYGON', srid=900913)
eq_(g.get_col_spec(), 'geometry(MULTIPOLYGON,900913)')
def test_get_col_spec(self):
g = Geometry(geometry_type='GEOMETRYCOLLECTION', srid=900913)
assert g.get_col_spec() == 'geometry(GEOMETRYCOLLECTION,900913)'
def test_get_col_spec(self):
from geoalchemy2 import Geometry
g = Geometry(srid=900913)
eq_(g.get_col_spec(), 'geometry(GEOMETRY,900913)')
def test_get_col_spec(self):
g = Geometry(srid=900913)
assert g.get_col_spec() == 'geometry(GEOMETRY,900913)'
class TrackingFix(db.Model):
__tablename__ = 'tracking_fixes'
id = db.Column(Integer, autoincrement=True, primary_key=True)
time = db.Column(DateTime, nullable=False, default=datetime.utcnow)
location_wkt = db.Column('location', Geometry('POINT', srid=4326))
track = db.Column(SmallInteger)
ground_speed = db.Column(REAL)
airspeed = db.Column(REAL)
altitude = db.Column(SmallInteger)
elevation = db.Column(SmallInteger)
vario = db.Column(REAL)
engine_noise_level = db.Column(SmallInteger)
pilot_id = db.Column(Integer,
db.ForeignKey('users.id', ondelete='CASCADE'),
nullable=False)
pilot = db.relationship('User', innerjoin=True)
ip = db.Column(INET)
def __repr__(self):
return '<TrackingFix: id={} time=\'{}\'>' \
.format(self.id, self.time).encode('unicode_escape')
@property
def location(self):
if self.location_wkt is None:
return None
coords = to_shape(self.location_wkt)
return Location(latitude=coords.y, longitude=coords.x)
def set_location(self, longitude, latitude):
self.location_wkt = from_shape(Point(longitude, latitude), srid=4326)
@property
def altitude_agl(self):
if not self.elevation:
raise ValueError('This TrackingFix has no elevation.')
return max(0, self.altitude - self.elevation)
@classmethod
def max_age_filter(cls, max_age):
"""
Returns a filter that makes sure that the fix is not older than a
certain time.
The delay parameter can be either a datetime.timedelta or a numeric
value that will be interpreted as hours.
"""
if isinstance(max_age, (int, long, float)):
max_age = timedelta(hours=max_age)
return cls.time >= datetime.utcnow() - max_age
@classmethod
def delay_filter(cls, delay):
"""
Returns a filter that makes sure that the fix was created at least
a certain time ago.
The delay parameter can be either a datetime.timedelta or a numeric
value that will be interpreted as minutes.
"""
if isinstance(delay, (int, long, float)):
delay = timedelta(minutes=delay)
return cls.time <= datetime.utcnow() - delay
@classmethod
def get_latest(cls, max_age=timedelta(hours=6)):
# Add a db.Column to the inner query with
# numbers ordered by time for each pilot
row_number = db.over(db.func.row_number(),
partition_by=cls.pilot_id,
order_by=cls.time.desc())
# Create inner query
subq = db.session \
.query(cls.id, row_number.label('row_number')) \
.join(cls.pilot) \
.filter(cls.max_age_filter(max_age)) \
.filter(cls.delay_filter(User.tracking_delay_interval())) \
.filter(cls.location_wkt != None) \
.subquery()
# Create outer query that orders by time and
# only selects the latest fix
query = cls.query() \
.options(db.joinedload(cls.pilot)) \
.filter(cls.id == subq.c.id) \
.filter(subq.c.row_number == 1) \
.order_by(cls.time.desc())
return query
class User(Base):
"""
Basic user and profile details
"""
__tablename__ = "users"
id = Column(BigInteger, primary_key=True)
username = Column(String, nullable=False, unique=True)
email = Column(String, nullable=False, unique=True)
# stored in libsodium hash format, can be null for email login
hashed_password = Column(Binary, nullable=True)
# phone number
# TODO: should it be unique?
phone = Column(String, nullable=True, unique=True)
phone_status = Column(Enum(PhoneStatus), nullable=True)
# timezones should always be UTC
## location
# point describing their location. EPSG4326 is the SRS (spatial ref system, = way to describe a point on earth) used
# by GPS, it has the WGS84 geoid with lat/lon
geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
# their display location (displayed to other users), in meters
geom_radius = Column(Float, nullable=True)
# the display address (text) shown on their profile
city = Column(String, nullable=False)
hometown = Column(String, nullable=True)
joined = Column(DateTime(timezone=True),
nullable=False,
server_default=func.now())
last_active = Column(DateTime(timezone=True),
nullable=False,
server_default=func.now())
# id of the last message that they received a notification about
last_notified_message_id = Column(BigInteger, nullable=False, default=0)
# display name
name = Column(String, nullable=False)
gender = Column(String, nullable=False)
pronouns = Column(String, nullable=True)
birthdate = Column(Date, nullable=False) # in the timezone of birthplace
# name as on official docs for verification, etc. not needed until verification
full_name = Column(String, nullable=True)
avatar_key = Column(ForeignKey("uploads.key"), nullable=True)
hosting_status = Column(Enum(HostingStatus), nullable=True)
meetup_status = Column(Enum(MeetupStatus), nullable=True)
# verification score
verification = Column(Float, nullable=True)
# community standing score
community_standing = Column(Float, nullable=True)
occupation = Column(String, nullable=True) # CommonMark without images
education = Column(String, nullable=True) # CommonMark without images
about_me = Column(String, nullable=True) # CommonMark without images
my_travels = Column(String, nullable=True) # CommonMark without images
things_i_like = Column(String, nullable=True) # CommonMark without images
about_place = Column(String, nullable=True) # CommonMark without images
# TODO: array types once we go postgres
languages = Column(String, nullable=True)
countries_visited = Column(String, nullable=True)
countries_lived = Column(String, nullable=True)
additional_information = Column(String,
nullable=True) # CommonMark without images
is_banned = Column(Boolean, nullable=False, default=False)
# hosting preferences
max_guests = Column(Integer, nullable=True)
last_minute = Column(Boolean, nullable=True)
has_pets = Column(Boolean, nullable=True)
accepts_pets = Column(Boolean, nullable=True)
pet_details = Column(String, nullable=True) # CommonMark without images
has_kids = Column(Boolean, nullable=True)
accepts_kids = Column(Boolean, nullable=True)
kid_details = Column(String, nullable=True) # CommonMark without images
has_housemates = Column(Boolean, nullable=True)
housemate_details = Column(String,
nullable=True) # CommonMark without images
wheelchair_accessible = Column(Boolean, nullable=True)
smoking_allowed = Column(Enum(SmokingLocation), nullable=True)
smokes_at_home = Column(Boolean, nullable=True)
drinking_allowed = Column(Boolean, nullable=True)
drinks_at_home = Column(Boolean, nullable=True)
other_host_info = Column(String,
nullable=True) # CommonMark without images
sleeping_arrangement = Column(Enum(SleepingArrangement), nullable=True)
sleeping_details = Column(String,
nullable=True) # CommonMark without images
area = Column(String, nullable=True) # CommonMark without images
house_rules = Column(String, nullable=True) # CommonMark without images
parking = Column(Boolean, nullable=True)
parking_details = Column(Enum(ParkingDetails),
nullable=True) # CommonMark without images
camping_ok = Column(Boolean, nullable=True)
accepted_tos = Column(Integer, nullable=False, default=0)
# for changing their email
new_email = Column(String, nullable=True)
new_email_token = Column(String, nullable=True)
new_email_token_created = Column(DateTime(timezone=True), nullable=True)
new_email_token_expiry = Column(DateTime(timezone=True), nullable=True)
avatar = relationship("Upload", foreign_keys="User.avatar_key")
@hybrid_property
def is_jailed(self):
return self.accepted_tos < 1 or self.is_missing_location
@property
def is_missing_location(self):
return not self.geom or not self.geom_radius
@property
def coordinates(self):
if self.geom:
return get_coordinates(self.geom)
else:
return None
@property
def age(self):
max_day = monthrange(date.today().year, self.birthdate.month)[1]
age = date.today().year - self.birthdate.year
# in case of leap-day babies, make sure the date is valid for this year
safe_birthdate = self.birthdate
if self.birthdate.day > max_day:
safe_birthdate = safe_birthdate.replace(day=max_day)
if date.today() < safe_birthdate.replace(year=date.today().year):
age -= 1
return age
@property
def display_joined(self):
"""
Returns the last active time rounded down to the nearest hour.
"""
return self.joined.replace(minute=0, second=0, microsecond=0)
@property
def display_last_active(self):
"""
Returns the last active time rounded down to the nearest 15 minutes.
"""
return self.last_active.replace(
minute=(self.last_active.minute // 15) * 15,
second=0,
microsecond=0)
def mutual_friends(self, target_id):
if target_id == self.id:
return []
session = Session.object_session(self)
q1 = (session.query(
FriendRelationship.from_user_id.label("user_id")).filter(
FriendRelationship.to_user == self).filter(
FriendRelationship.from_user_id != target_id).filter(
FriendRelationship.status == FriendStatus.accepted))
q2 = (session.query(
FriendRelationship.to_user_id.label("user_id")).filter(
FriendRelationship.from_user == self).filter(
FriendRelationship.to_user_id != target_id).filter(
FriendRelationship.status == FriendStatus.accepted))
q3 = (session.query(
FriendRelationship.from_user_id.label("user_id")).filter(
FriendRelationship.to_user_id == target_id).filter(
FriendRelationship.from_user != self).filter(
FriendRelationship.status == FriendStatus.accepted))
q4 = (session.query(
FriendRelationship.to_user_id.label("user_id")).filter(
FriendRelationship.from_user_id == target_id).filter(
FriendRelationship.to_user != self).filter(
FriendRelationship.status == FriendStatus.accepted))
return session.query(User).filter(
User.id.in_(q1.union(q2).intersect(
q3.union(q4)).subquery())).all()
def __repr__(self):
return f"User(id={self.id}, email={self.email}, username={self.username})"
