class Bike_Trip(db.Model):
"""Stores information about start station to end station of bikes with their trip
duration."""
__tablename__ = 'trips'
id = db.Column(db.Integer,
nullable=False,
autoincrement=False,
primary_key=True)
trip_duration = db.Column(db.Integer, nullable=False)
start_point = db.Column(Geography(geometry_type='POINT', srid=4326), nullable=False)
end_point = db.Column(Geography(geometry_type='POINT', srid=4326), nullable=False)
def start_lat(self):
coordinates = load(bytes(self.start_point.data))
return coordinates.y
def start_lng(self):
coordinates = load(bytes(self.start_point.data))
return coordinates.x
def stop_lat(self):
coordinates = load(bytes(self.stop_point.data))
return coordinates.y
def stop_lng(self):
coordinates = load(bytes(self.stop_point.data))
return coordinates.x
def __repr__(self):
return '<Trip duration:{trip_duration}>'.format(trip_duration=self.trip_duration)
class Cab_Trip(db.Model):
"""Stores information about pick up location to drop off location with their trip
duration."""
__tablename__ = 'cab_trips'
id = db.Column(db.Integer,
nullable=False,
autoincrement=False,
primary_key=True)
trip_duration = db.Column(db.Integer, nullable=False)
fare = db.Column(db.Integer, nullable=False)
start_point = db.Column(Geography(geometry_type='POINT', srid=4326), nullable=False)
end_point = db.Column(Geography(geometry_type='POINT', srid=4326), nullable=False)
def start_lat(self):
coordinates = load(bytes(self.start_point.data))
return coordinates.y
def start_lng(self):
coordinates = load(bytes(self.start_point.data))
return coordinates.x
def stop_lat(self):
coordinates = load(bytes(self.stop_point.data))
return coordinates.y
def stop_lng(self):
coordinates = load(bytes(self.stop_point.data))
return coordinates.x
def __repr__(self):
return '<Trip duration:Fare {trip_duration}:{fare}>'.format(trip_duration=self.trip_duration, fare=self.fare)
class PDV(ModelBase):
__tablename__ = 'pdv'
id = Column(UUID(as_uuid=True),
unique=True,
nullable=False,
primary_key=True,
server_default=text('gen_random_uuid()'))
public_id = Column(Integer, unique=True, nullable=False)
owner_id = Column(ForeignKey('owner.id'), nullable=False)
owner = relationship('Owner', back_populates='pdvs')
name = Column(String(255), nullable=False)
address = Column(Geography(geometry_type='POINT', srid=4326),
nullable=False)
coverage_area = Column(Geography(geometry_type='MULTIPOLYGON', srid=4326),
nullable=False)
@property
def as_dict(self):
base = {'id': self.public_id}
owner = self.owner.as_dict
owner.pop('id', None)
base.update(owner)
base.update({
'id': str(self.public_id),
'tradingName': self.name,
'coverageArea': parsers.to_geojson(self.coverage_area),
'address': parsers.to_geojson(self.address)
})
return base
class Place(Base):
__tablename__ = 'places'
ptolemy_id = Column(String, primary_key=True)
ptolemy_name = Column(String)
modern_name = Column(String)
ptolemy_point = Column(Geography(geometry_type='POINT'))
modern_point = Column(Geography(geometry_type='POINT'))
disposition = Column(String)
def __repr__(self):
return '<Place(%s)>' % (self.ptolemy_id, )
class Route(Base):
__tablename__ = 'route'
id = Column(postgresql.UUID(), nullable=False, primary_key=True)
origin = Column(Geography(geometry_type='POINT'))
origin_name = Column(String, nullable=False)
destination = Column(Geography(geometry_type='POINT'))
destination_name = Column(String, nullable=False)
waypoints = Column(postgresql.ARRAY(Geography(geometry_type='POINT')),
nullable=True)
waypoints_names = Column(postgresql.ARRAY(String), nullable=True)
polyline = Column(Geography(geometry_type='LINESTRING'))
bounds = Column(postgresql.JSON, nullable=True)
created = Column(DateTime(timezone=True))
def save_geo_series_to_tmp_table(geo_series: gpd.GeoSeries,
eng: sa.engine.Engine) -> str:
"""
Save a geo series as a table in the db, for better performance
Args:
geo_series: The GeoSeries to be inserted into a db table
eng: SQL Alchemy engine
Returns:
The name of the new table
"""
geo_series = geo_series.rename('geom')
gdf = gpd.GeoDataFrame(geo_series, columns=['geom'], geometry='geom')
gdf['geom'] = gdf.geometry.apply(lambda x: WKTElement(x.wkt, srid=4326))
gdf['geom_id'] = range(len(gdf))
tbl_name = get_temp_table_name()
insert_into_table(eng,
gdf,
tbl_name,
dtypes={
'geom': Geography(srid=4326),
'geom_id': sa.INT
})
add_postgis_index(eng, tbl_name, 'geom')
return tbl_name
class Location(Base):
"""
A specific location that we have a lat/lon for.
"""
__tablename__ = "location"
id = Column(Integer, primary_key=True)
location = Column(Geography('Point,4326'))
name = Column(String(512))
population = Column(Integer)
def get_coords(self):
"""
:return: lon, lat
"""
point = wkb.loads(bytes(self.location.data))
return point.x, point.y
def serialize(self):
coords = self.get_coords()
return {
"id": self.id,
"name": self.name,
"lon": coords[0],
"lat": coords[1],
}
def __repr__(self):
return f"<Location id={self.id} name='{self.name}'>"
class RepresentativePoint(Base):
"""Definition of the representative_points table."""
__tablename__ = config.get('database.prefix') + 'representative_points'
id = Column(Integer, primary_key=True, autoincrement=True)
created_at = Column(DateTime, default=func.now())
updated_at = Column(DateTime,
server_default=func.now(),
onupdate=func.now())
service_area_id = Column(String,
ForeignKey(
config.get('database.prefix') +
'service_areas.service_area_id'),
index=True)
latitude = Column(Float, nullable=False)
longitude = Column(Float, nullable=False)
location = Column(Geography(geometry_type='POINT',
srid=4326,
spatial_index=True),
nullable=False,
unique=True)
population = Column(Float, nullable=False)
county = Column(String, nullable=False, index=True)
isodistancePolygon = Column(JSON)
zip_code = Column(String, nullable=False, index=True)
census_block_group = Column(String)
census_block = Column(String)
census_tract = Column(String)
def columnsFromOgrFeat(feat, spatindex=True, forceGType=None, targetsrid=4326):
"""Returns a list of columns from a osgeo feature"""
gisMap = {
'String': String,
'Integer': Integer,
'Real': Float,
'Float': Float,
'Integer64': BigInteger,
"Date": Date
}
df = feat.GetDefnRef()
cols = [Column('id', Integer, primary_key=True)]
for i in range(feat.GetFieldCount()):
fld = df.GetFieldDefn(i)
name = fld.GetName()
if name.lower() == 'id':
# skip columns with id (will be renewed)
continue
cols.append(Column(name.lower(), gisMap[fld.GetTypeName()]))
# append geometry column
if forceGType:
gType = forceGType
else:
gType = feat.geometry().GetGeometryName()
if targetsrid == 4326:
geomtype = Geography(gType, srid=targetsrid, spatial_index=spatindex)
else:
geomtype = Geometry(gType, srid=targetsrid, spatial_index=spatindex)
cols.append(Column('geom', geomtype))
return cols
class Place(db.Model):
__tablename__ = "places"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String, nullable=False)
lat = db.Column(db.Float, nullable=False)
lon = db.Column(db.Float, nullable=False)
coords = db.Column(Geography(geometry_type="POINT", srid=4326),
nullable=False)
types = db.Column(db.VARCHAR(255), nullable=False)
def __init__(self, lat, lon, name, types):
# self.coords = f"POINT({lon} {lat})"
self.coords = f"POINT({lat} {lon})"
self.name = name
self.types = types
self.lat = lat
self.lon = lon
def serialize(self):
return {
"name": self.name,
"latitude": self.lat,
"longitude": self.lon,
"types": self.types,
}
class Building(Base, Entity, HistoryBase):
__tablename__ = "Building"
__table_args__ = {"schema": "Business"}
description = Column("Description", String(64))
fullAddress = Column("FullAddress", String(256))
square = Column("Square", Float, default=0.0)
countFlats = Column("CountFlats", Integer, default=0)
location = Column("Location", Geography(geometry_type='POINT', srid=4326))
buildingPurposeId = Column("BuildingPurposeId", Integer,
ForeignKey("Dictionaries.BuildingPurpose.Id"))
districtId = Column("DistrictId", Integer,
ForeignKey("Dictionaries.District.Id"))
organizationId = Column("OrganizationId", Integer,
ForeignKey("Business.Organization.Id"))
userAdditionalInfoId = Column("UserAdditionalInfoId", Integer,
ForeignKey("Business.UserAdditionalInfo.Id"))
buildingPurpose = relationship("BuildingPurpose",
back_populates="buildings")
district = relationship("District", back_populates="buildings")
organization = relationship("Organization", back_populates="buildings")
userAdditionalInfo = relationship("UserAdditionalInfo",
back_populates="buildings")
class Venue(Base):
__tablename__ = 'venues'
id = Column(Integer, primary_key=True)
foursquare_id = Column(String, unique=True)
name = Column(String)
address = Column(String)
city = Column(String)
category = Column(String)
coordinates = Column(Geography(geometry_type='POINT', srid=4326))
reviews = relationship('Reviews', back_populates='venue', uselist=False)
def to_dataclass(self) -> VenueSchema:
coordinates = wkb.loads(bytes(self.coordinates.data))
point = Point(lat=coordinates.x, lon=coordinates.y)
return VenueSchema(
foursquare_id=self.foursquare_id,
name=self.name,
address=self.address,
city=self.city,
category=self.category,
coordinates=point,
reviews=self.reviews.to_dataclass() if self.reviews else None
)
class Star(Base):
"""SQLAlchemy table for representing a `star`."""
__tablename__ = 'star'
id = Column(Integer, primary_key=True)
ra = Column(Float)
ra_err = Column(Float)
dec = Column(Float)
dec_err = Column(Float)
coord = Column(Geography(geometry_type='POINT', srid=4326))
# Relationship to magnitude with delete cascade
magnitudes = relationship("Magnitude",
backref="star",
passive_deletes=True)
def __init__(self, ra, dec, ra_err, dec_err):
self.ra = ra
self.dec = dec
self.ra_err = ra_err
self.dec_err = dec_err
self.coord = point_str(ra_err, dec_err)
def __repr__(self):
return "<Star(%i)>" % (self.id)
class CrimePoints(db.Model):
""" Latitude and longitudes of crime reports stored as Geography Points """
__tablename__ = 'crime'
crime_id = db.Column(db.Integer, autoincrement=True, primary_key=True)
crime_pt = db.Column(Geography(geometry_type='POINT', srid=4326))
def __repr__(self):
return "<Crime crime_id=%s crime_pt=%s" % (self.crime_id,
self.crime_pt)
@classmethod
def count_crimes_on_leg(cls, origin_lat, origin_lon, dest_lat, dest_lon):
print "Counting crimes on (" + origin_lat + ' ' + origin_lon + ', ' + dest_lat + ' ' + dest_lon + ')'
linestring = 'LINESTRING(' + origin_lon + ' ' + origin_lat + ',' + dest_lon + ' ' + dest_lat + ')'
query = db.session.query(cls).filter(
func.ST_DWithin(cls.crime_pt, linestring, 5)).all()
# print "Query returned", query
return len(query)
@classmethod
def get_heat_pts(cls, maxLat, minLat, maxLon, minLon):
polygon = 'POLYGON((' + str(maxLon) + ' ' + str(maxLat) + ',' + str(
minLon) + ' ' + str(maxLat) + ',' + str(minLon) + ' ' + str(
minLat) + ',' + str(maxLon) + ' ' + str(minLat) + ',' + str(
maxLon) + ' ' + str(maxLat) + '))'
query = db.session.query(cls).filter(
func.ST_Intersects(cls.crime_pt, polygon)).all()
# print "Query returned", query
lonlat_list = []
for crime in query:
ll = wkb.loads(bytes(crime.crime_pt.data))
lonlat_list.append([ll.y, ll.x])
print ll.x, ll.y
# for crime in query:
# lonlat_list.append(func.ST_AsText(crime.crime_pt))
# print "************* lonLat_list is" ,lonlat_list
return lonlat_list
@classmethod
def add_new_pt(cls, lat, lon):
pt = 'POINT(' + lon + ' ' + lat + ')'
new_pt = cls(crime_pt=pt)
print pt
db.session.add(new_pt)
@classmethod
def seed_pts(cls):
# Initialize list that will hold all the longitudes and latitudes
lonlat_list = []
# Open and parse lat and lon tuples from seed file
with open('crime_ll', 'r') as seed_fh:
for line in seed_fh:
lat, lon = line.split()
cls.add_new_pt(lat, lon)
# print lonlat_list
db.session.commit()
seed_fh.close()
class Riders(Base):
__tablename__ = 'riders'
id = Column(Integer, primary_key=True)
name = Column(String)
email = Column(String)
address = Column(String)
address2 = Column(String)
city = Column(String)
province = Column(String)
postal = Column(String)
country = Column(String)
phone = Column(String)
pronouns = Column(String)
availability = Column(JSONB)
capacity = Column(Integer)
max_distance = Column(Integer)
signed_up_on = Column(Date)
inserted_at = Column(DateTime, default=datetime.now)
updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)
onfleet_id = Column(String)
onfleet_account_status = Column(String)
deliveries_completed = Column(Integer)
location = Column(Geography(from_text='ST_GeogFromText'))
mailchimp_id = Column(String)
mailchimp_status = Column(String)
class Source(Base):
name = Column(String, index=True)
location = Column(Geography('POINT', srid=settings.srid),
nullable=False,
index=True)
data = Column(JSON)
comments = relationship('Comment', back_populates='source')
def __init__(self, *args, **kwargs):
"""
Convert the ra/dec fields from the schema into the PostGIS text representation
"""
ra = kwargs.pop('ra')
dec = kwargs.pop('dec')
kwargs['location'] = wkt_point(ra, dec, settings.srid)
return super().__init__(*args, **kwargs)
@property
def ra(self):
ra = shape.to_shape(self.location).x
if ra < 0:
ra = ra + 360
return ra
@property
def dec(self):
return shape.to_shape(self.location).y
class Station(db.Model):
"""Stores information about all available bike stations. Stores bike
availability information"""
__tablename__ = 'stations'
id = db.Column(db.Integer,
nullable=False,
autoincrement=False,
primary_key=True)
name = db.Column(db.String(50), nullable=False)
point = db.Column(Geography(geometry_type='POINT', srid=4326),
nullable=False)
num_bikes_available = db.Column(db.Integer, nullable=False)
num_docks_available = db.Column(db.Integer, nullable=False)
def lat(self):
"""Return the latitude of the station"""
coordinates = loads(bytes(self.point.data))
return coordinates.y
def lng(self):
"""Return the longitude of the station"""
coordinates = loads(bytes(self.point.data))
return coordinates.x
def __repr__(self):
return '<Station id:{id}, Bike:Dock {bike}:{dock}>'.format(
id=self.id,
bike=self.num_bikes_available,
dock=self.num_docks_available)
class LastLocationPostGis(db.Model):
"""Simple database model to track the last location of an active user."""
__tablename__ = 'last_location_post_gis'
user_id = db.Column(db.Integer, primary_key=True)
latest_point = db.Column(Geography(geometry_type='POINT', srid=4326),
nullable=True)
last_modified = db.Column(db.TIMESTAMP(120),
nullable=True,
default=dt.datetime.utcnow())
active = db.Column(db.BOOLEAN(120), nullable=False)
# define relationships with other tables
person_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
def __init__(self, point=None, person_id=None):
self.latest_point = point
self.last_modified = dt.datetime.utcnow()
self.active = True
self.person_id = person_id
self.user_id = person_id
def serialize(self):
return {
'user_id': self.user_id,
'latest_point_lat': str(to_shape(self.latest_point).y),
'latest_point_lng': str(to_shape(self.latest_point).x),
'last_modified': self.last_modified,
'active': self.active,
'person_id': self.person_id,
}
class Activity(Base):
__tablename__ = 'activities'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship('User', back_populates='activities')
name = Column(String)
path = Column(Geography('LINESTRING'))
class Pois(db.Model):
__tablename__ = ops_settings['provider_parameters']['table_name']
logger.info('table name for pois: {}'.format(__tablename__))
uuid = db.Column(db.LargeBinary, primary_key=True)
osm_id = db.Column(db.BigInteger, nullable=False, index=True)
osm_type = db.Column(db.Integer, nullable=False)
# address = db.Column(db.Text, nullable=True)
geom = db.Column(Geography(geometry_type="POINT",
srid=4326,
spatial_index=True),
nullable=False)
tags = db.relationship(
"Tags",
backref='{}'.format(ops_settings['provider_parameters']['table_name']),
lazy='dynamic')
categories = db.relationship(
"Categories",
backref='{}'.format(ops_settings['provider_parameters']['table_name']),
lazy='dynamic')
def __repr__(self):
return '<osm id %r>' % self.osm_id
class Changeset(Base):
__tablename__ = 'changeset'
id = Column(BigInteger, primary_key=True)
author = Column(String, ForeignKey('user.name'))
timestamp = Column(DateTime)
bbox = Column(Geography(geometry_type='POLYGON', srid=4326))
class Zillow_Property(Base):
__tablename__ = 'zillow_property'
zpid = Column(Integer, primary_key=True)
longitude_latitude = Column(Geography(geometry_type='POINT',
srid=4326))
zipcode = Column(Integer)
city = Column(String)
state = Column(String)
valueChange = Column(Integer)
yearBuilt = Column(Integer)
lotSizeSqFt = Column(Integer)
finishedSqFt = Column(Integer)
lastSoldPrice = Column(Integer)
amount = Column(Integer)
taxAssessmentYear = Column(Integer)
FIPScounty = Column(Integer)
low = Column(Integer)
high = Column(Integer)
percentile = Column(Integer)
zindexValue = Column(Integer)
street = Column(String)
lastSoldDate = Column(DATE)
useCode = Column(String)
bathrooms = Column(Float)
bedrooms = Column(Integer)
taxAssessment = Column(Float)
def __repr__(self):
return "<Zillow_Property(zpid='%s', Monthly Rental='%s')>" % (
self.zpid, self.amount)
class OsmCandidate(Base):
__tablename__ = 'osm_candidate'
osm_type = Column(osm_type_enum, primary_key=True)
osm_id = Column(BigInteger, primary_key=True)
name = Column(String)
tags = Column(postgresql.JSON)
geom = Column(Geography(srid=4326, spatial_index=True))
class Report(Base):
__tablename__ = 'report'
id = Column(Integer, primary_key=True)
shape = Column(String)
duration = Column(String)
description = Column(Text)
date = Column(Date)
point = Column(Geography('POINT'))
@classmethod
def query(cls, session):
return session.query(cls.id, cls.shape, cls.duration, cls.description,
cls.date,
func.st_asgeojson(cls.point).label("geojson"))
@staticmethod
def row_to_dict(row):
import json
row_dict = row._asdict()
row_dict["id"] = row.id
row_dict["shape"] = row.shape
row_dict["duration"] = row.duration
row_dict["description"] = row.description
row_dict["date"] = row.date
geo_dict = json.loads(row_dict["geojson"])
del row_dict["geojson"]
geo_dict["properties"] = row_dict
return geo_dict
class glade_2p3(db.Model):
id = db.Column(db.Integer, primary_key=True)
pgc_number = db.Column(db.Integer)
position = db.Column(Geography('POINT', srid=4326))
gwgc_name = db.Column(db.String)
hyperleda_name = db.Column(db.String)
_2mass_name = db.Column(db.String)
sdssdr12_name = db.Column(db.String)
distance = db.Column(db.Float)
distance_error = db.Column(db.Float)
redshift = db.Column(db.Float)
bmag = db.Column(db.Float)
bmag_err = db.Column(db.Float)
bmag_abs = db.Column(db.Float)
jmag = db.Column(db.Float)
jmag_err = db.Column(db.Float)
hmag = db.Column(db.Float)
hmag_err = db.Column(db.Float)
kmag = db.Column(db.Float)
kmag_err = db.Column(db.Float)
flag1 = db.Column(db.String(1))
flag2 = db.Column(db.Integer)
flag3 = db.Column(db.Integer)
@property
def json(self):
return to_json(self, self.__class__)
class Club(Base):
__tablename__ = 'club'
id = Column(Integer, primary_key=True, index=True)
clubName = Column(String)
clubIcon = Column(String)
clubDescription = Column(String)
clubLocation = Column(Geography(geometry_type='POINT', srid=4326))
clubColor = Column(String)
class Node(Element):
__tablename__ = 'node'
osm_id = Column(BigInteger, ForeignKey('element.osm_id'), primary_key=True)
location = Column(Geography(geometry_type='POINT', srid=4326))
__mapper_args__ = {
'polymorphic_identity': 'node',
}
class Poi(Base):
__tablename__ = 'poi'
__table_args__ = {'schema': 'gis'}
id = Column(Integer, primary_key=True)
geog = Column(Geography(geometry_type='POINT', srid=4326))
def __init__(self, geog):
self.geog = geog
class WayPoint(db.Model):
id = db.Column(db.Integer, primary_key=True)
created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)
route_id = db.Column(db.Integer, db.ForeignKey('route.id'), nullable=False)
coordinate = db.Column(Geography(geometry_type='POINT', srid=4326))
def __repr__(self):
return '<WayPoint: %s>' % self.coordinate
class footprint_ccd(db.Model):
id = db.Column(db.Integer, primary_key=True)
instrumentid = db.Column(db.Integer)
footprint = db.Column(Geography('POLYGON', srid=4326))
@property
def json(self):
return to_json(self, self.__class__)
