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__)