def upgrade(): op.execute("CREATE EXTENSION IF NOT EXISTS postgis;") op.add_column('airport', sa.Column('location', ga.Geometry('POINT', srid=4326))) op.execute(UPGRADE_QUERY.format(table_name='airport')) op.drop_column('airport', 'latitude') op.drop_column('airport', 'longitude') op.add_column('aircraft_beacon', sa.Column('location', ga.Geometry('POINT', srid=4326))) op.execute(UPGRADE_QUERY.format(table_name='aircraft_beacon')) op.drop_column('aircraft_beacon', 'latitude') op.drop_column('aircraft_beacon', 'longitude') op.add_column('receiver_beacon', sa.Column('location', ga.Geometry('POINT', srid=4326))) op.execute(UPGRADE_QUERY.format(table_name='receiver_beacon')) op.drop_column('receiver_beacon', 'latitude') op.drop_column('receiver_beacon', 'longitude') op.add_column('receiver', sa.Column('location', ga.Geometry('POINT', srid=4326))) op.execute(UPGRADE_QUERY.format(table_name='receiver')) op.drop_column('receiver', 'latitude') op.drop_column('receiver', 'longitude') op.add_column('takeoff_landing', sa.Column('location', ga.Geometry('POINT', srid=4326))) op.execute(UPGRADE_QUERY.format(table_name='takeoff_landing')) op.drop_column('takeoff_landing', 'latitude') op.drop_column('takeoff_landing', 'longitude')
class Track(db.Model): __tablename__ = 'tracks' id = db.Column(db.BigInteger(), primary_key=True) anon_user_key = db.Column(db.String(80), nullable=False) geo_points = db.Column(ga.Geometry('MultiPointZ')) date_created = db.Column(db.Date()) uploaded = db.Column(db.DateTime(timezone=True), default=utils.utcnow) geo_boundary = db.Column(ga.Geometry('Polygon'))
class BathroomData(db.Model): """View of v_bathroom_data""" __tablename__ = "v_bathroom_data" bathroom_id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(150)) unisex = db.Column(db.Boolean) accessible = db.Column(db.Boolean) changing_table = db.Column(db.Boolean) street = db.Column(db.String(155)) city = db.Column(db.String(50)) state = db.Column(db.String(50)) country = db.Column(db.String(50)) lnglat = db.Column(geoalchemy2.Geometry(geometry_type='POINT', srid=4326)) latitude = db.Column(db.Float) longitude = db.Column(db.Float) directions = db.Column(db.String(2000)) comment = db.Column(db.String(1024)) user_id = db.Column(db.Integer) score = db.Column(db.Integer) def __repr__(self): """Provide useful representation when printed.""" return ("{} {} {} {} {} {} {} {} {} {} {} {} {} {} {}".format( self.__class__.__name__, self.bathroom_id, self.name, self.unisex, self.accessible, self.changing_table, self.street, self.city, self.state, self.country, self.latitude, self.longitude, self.comment, self.user_id, self.score))
def setup_metadata(self, tablename): metadata = db.MetaData(schema=SCHEMA) metadata.bind = env.core.engine geom_fldtype = _GEOM_TYPE_2_DB[self.geometry_type] class model(object): def __init__(self, **kwargs): for k, v in six.iteritems(kwargs): setattr(self, k, v) table = db.Table( tablename, metadata, db.Column('id', db.Integer, primary_key=True), db.Column( 'geom', ga.Geometry(dimension=2, srid=self.srs_id, geometry_type=geom_fldtype)), *map( lambda fld: db.Column(fld.key, _FIELD_TYPE_2_DB[fld.datatype]), self.fields)) db.mapper(model, table) self.metadata = metadata self.table = table self.model = model
class Point(GEOType): name = "Point" sql_type = geoalchemy2.Geometry('POINT') regex = r"^POINT\s*\([0-9\s\.]*\)$" @classmethod def from_values(cls, **values): """Instantiate a Point, using x and y coordinates, and optional srid: point = Point.from_values(x=1, y=2, srid=28992) """ required_keys = ['x', 'y'] optional_keys = ['srid'] for key in required_keys: if key not in values: raise GOBException(f"Missing required key {key}") kwargs = {k: v for k, v in values.items() if k not in required_keys+optional_keys} if 'precision' not in kwargs: kwargs['precision'] = cls._precision x = str(Decimal.from_value(values['x'], **kwargs)) y = str(Decimal.from_value(values['y'], **kwargs)) srid = values['srid'] if 'srid' in values else cls._srid return cls(f'POINT({x} {y})', srid=srid) @classmethod def get_column_definition(cls, column_name, **kwargs): srid = kwargs['srid'] if 'srid' in kwargs else cls._srid return sqlalchemy.Column(column_name, geoalchemy2.Geometry(geometry_type='POINT', srid=srid))
class Polygon(GEOType): # POLYGON ((115145.619264024 485115.91328199, ...)) name = "Polygon" sql_type = geoalchemy2.Geometry('POLYGON') regex = r"^POLYGON\s*\([0-9\s\.,\(\)]*\)$" @classmethod def from_values(cls, **values): """Instantiates a Polygon from a values dictionary :param values: dictionary containing construction parameters :raises ValueError because the method is not implemented :return None """ raise ValueError("NYI") @classmethod def get_column_definition(cls, column_name, **kwargs): """Get the database column definition for a Polygon :param column_name: name of the column in the database :param kwargs: arguments :return: sqlalchemy.Column """ srid = kwargs['srid'] if 'srid' in kwargs else cls._srid return sqlalchemy.Column(column_name, geoalchemy2.Geometry(geometry_type='POLYGON', srid=srid))
def upload_data(dataset, epsg): engine = sqlalchemy.create_engine( 'postgresql://$$USER$$:$$PASSWORD$$@$$IP$$/$$DB$$') content = dataset['content'] print("Attempting to upload {}".format(dataset['name'])) if 'geometry' in content.columns: geometry_type = str(content.geom_type[0]).upper() content['geom'] = content['geometry'].apply( lambda x: geoalchemy2.WKTElement(x.wkt, srid=epsg)) content.drop('geometry', 1, inplace=True) try: content.to_sql(dataset['name'], engine, if_exists='replace', index=False, dtype={ 'geom': geoalchemy2.Geometry(geometry_type, srid=epsg) }) except Exception as e: print("ERROR! Cannot upload {}. Error: {}".format( dataset['name'], str(e))) print('Uploaded {}'.format(dataset['name'])) else: try: content.to_sql(dataset['name'], engine, if_exists='replace') except Exception as e: print("ERROR! Cannot upload {}. Error: {}".format( dataset['name'], str(e))) print('Uploaded {}'.format(dataset['name']))
def makeTable(fields, engine, table_name): GEO_TYPE_MAP = { 'C': sa.String, 'N': sa.Float, 'L': sa.Boolean, 'D': sa.TIMESTAMP, 'F': sa.Float } columns = [] for field in fields: fname, d_type, f_len, d_len = field col_type = GEO_TYPE_MAP[d_type] kwargs = {} if d_type == 'C': col_type = col_type(f_len) if fname == 'objectid': kwargs['primary_key'] = True columns.append(sa.Column(fname.lower(), col_type, **kwargs)) geo_type = 'MULTIPOLYGON' columns.append(sa.Column('geom', ga2.Geometry(geo_type, srid=srid))) table = sa.Table(table_name, sa.MetaData(), *columns) table.drop(engine, checkfirst=True) table.create(engine) return table
class Carbonmonoxide(Base): """ORM Object for Carbonmonoxide Point """ # Tablename __tablename__ = 'carbonmonoxide' # Primary Key id = Column(Integer, primary_key=True) # Carbonmonoxide Value value = Column(Float) # Longitude longitude = Column(Float) # Latitude latitude = Column(Float) # timestamp timestamp = Column(DateTime) # PostGis type geom = Column(geoalchemy2.Geometry(geometry_type="POINT")) def __init__(self, value, longitude, latitude, timestamp): self.value = value self.longitude = longitude self.latitude = latitude self.timestamp = timestamp self.geom = geoalchemy2.elements.WKTElement( f"POINT({longitude} {latitude})")
def downgrade(): op.drop_index('ix_aircraft_beacon_device_id', 'aircraft_beacon') op.drop_foreign_key("aircraft_beacon_device_id_fkey", "aircraft_beacon") op.drop_column('aircraft_beacon', 'device_id') op.drop_index('ix_aircraft_beacon_receiver_id', 'aircraft_beacon') op.drop_foreign_key("aircraft_beacon_receiver_id_fkey", "aircraft_beacon") op.drop_column('aircraft_beacon', 'receiver_id') op.create_index('ix_aircraft_beacon_address', sa.Column('address', sa.String)) op.drop_index('ix_receiver_beacon_receiver_id', 'receiver_beacon') op.drop_foreign_key("ix_receiver_beacon_receiver_id", "receiver_beacon") op.drop_column('receiver_beacon', 'receiver_id') op.drop_index('ix_takeoff_landing_airport_id', 'takeoff_landing') op.drop_foreign_key("takeoff_landing_airport_id_fkey", "takeoff_landing") op.drop_column('takeoff_landing', 'airport_id') op.drop_index('ix_takeoff_landing_device_id', 'takeoff_landing') op.drop_foreign_key("takeoff_landing_device_id_fkey", "takeoff_landing") op.drop_column('takeoff_landing', 'device_id') op.add_column('takeoff_landing', sa.Column('name', sa.String)) op.add_column('takeoff_landing', sa.Column('receiver_name', sa.String(9))) op.add_column('takeoff_landing', sa.Column('address', sa.String(6))) op.add_column('takeoff_landing', sa.Column('location', ga.Geometry('POINT', srid=4326))) op.create_index('ix_takeoff_landing_address', 'takeoff_landing', ['address']) op.create_index('idx_takeoff_landing_location', 'takeoff_landing', ['location'])
def setup_metadata(self, tablename=None): metadata = db.MetaData(schema='vector_layer' if tablename else None) geom_fldtype = _GEOM_TYPE_2_DB[self.geometry_type] class model(object): def __init__(self, **kwargs): for k, v in kwargs.iteritems(): setattr(self, k, v) table = db.Table( tablename if tablename else ('lvd_' + str(uuid.uuid4().hex)), metadata, db.Column('id', db.Integer, primary_key=True), db.Column( 'geom', ga.Geometry(dimension=2, srid=self.srs_id, geometry_type=geom_fldtype)), *map( lambda fld: db.Column(fld.key, _FIELD_TYPE_2_DB[fld.datatype]), self.fields)) db.mapper(model, table) self.metadata = metadata self.table = table self.model = model
def upgrade(): op.execute("create extension if not exists postgis;") op.create_table( 'users', sa.Column('id', sa.Integer, primary_key=True), sa.Column('name', sa.Text, nullable=False), sa.Column('coords', ge.Geometry(geometry_type='POINT', srid=4326), nullable=False) )
def gdfToPostGIS(connection, gdf, tableName, saveIndex=True): """this function uploads a geodataframe to table in AWS RDS. It handles combined polygon/multipolygon geometry and stores it in valid multipolygon in epsg 4326. Args: connection (sqlalchemy.engine.base.Connection) : postGIS enabled database connection gdf (geoPandas.GeoDataFrame) : input geoDataFrame tableName (string) : postGIS table name (string) saveIndex (boolean, optional) : save geoDataFrame index column in separate column in postgresql, otherwise discarded. Default is True Returns: gdf (geoPandas.GeoDataFrame) : the geodataframe loaded from the database. Should match the input dataframe todo: currently removes table if exists. Include option to break or append """ gdf["type"] = gdf.geometry.geom_type geomTypes = ["Polygon", "MultiPolygon"] for geomType in geomTypes: gdfType = gdf.loc[gdf["type"] == geomType] geomTypeLower = str.lower(geomType) gdfType['geom'] = gdfType['geometry'].apply( lambda x: geoalchemy2.WKTElement(x.wkt, srid=4326)) gdfType.drop(["geometry", "type"], 1, inplace=True) print("Create table temp%s" % (geomTypeLower)) gdfType.to_sql(name="temp%s" % (geomTypeLower), con=engine, if_exists='replace', index=saveIndex, dtype={ 'geom': geoalchemy2.Geometry(str.upper(geomType), srid=4326) }) # Merge both tables and make valid sql = [] sql.append("DROP TABLE IF EXISTS %s" % (tableName)) sql.append( "ALTER TABLE temppolygon ALTER COLUMN geom type geometry(MultiPolygon, 4326) using ST_Multi(geom);" ) sql.append( "CREATE TABLE %s AS (SELECT * FROM temppolygon UNION SELECT * FROM tempmultipolygon);" % (tableName)) sql.append("UPDATE %s SET geom = st_makevalid(geom);" % (tableName)) sql.append("DROP TABLE temppolygon,tempmultipolygon") for statement in sql: print(statement) result = connection.execute(statement) gdfFromSQL = gpd.GeoDataFrame.from_postgis("select * from %s" % (tableName), connection, geom_col='geom') return gdfFromSQL
def gdf_to_db( gdf: pd.DataFrame, fqtable: str, write_index: bool = True, geom_type: str = "POLYGON", db_name: str = "default", ) -> None: """ Push a Polygon geometry geodataframe to PostGIS DB Honestly I don't truly understand how this works but I verified the values that get stored in the DB and they're identical, type and value wise. @TODO: See if we can generalise this and put in df_to_db() instead """ ga2_geom_types = [ "GEOMETRY", "POINT", "LINESTRING", "POLYGON", "MULTIPOINT", "MULTILINESTRING", "MULTIPOLYGON", "GEOMETRYCOLLECTION", "CURVE", ] geom_type = geom_type.upper() if geom_type not in ga2_geom_types: raise TypeError( f"Unknow geom_type {geom_type}, only {ga2_geom_types} should work." ) def to_wkt(geom): """ Use with apply to make Well-Known-Text objects of geom """ return ga.WKTElement(geom.wkt, srid=4326) # Make a copy so we don't mess with the original this_gdf = gdf.copy() # Cast geom to a WKT this_gdf["geom"] = this_gdf["geom"].apply(to_wkt) dtype = {"geom": ga.Geometry(geom_type, srid=4326)} schema, table = _unpack_fqtable(fqtable) this_gdf.to_sql( name=table, con=_make_engine(db_name), if_exists="replace", schema=schema, index=write_index, chunksize=10000, dtype=dtype, )
def upgrade(): ### commands auto generated by Alembic - please adjust! ### op.add_column( 'links', sa.Column('geom', ga.Geometry(geometry_type='LINESTRING', srid=4326), server_default=sa.text( "ST_GeomFromText('LINESTRING EMPTY', 4326)"), nullable=False))
class BusStop(Base): __tablename__ = 'bus_stops' __table_args__ = ( {"schema": schema} ) id = Column(BigInteger, primary_key=True) name = Column(Text) geom = Column(geoalchemy2.Geometry('POINT', 4326, management=True))
def get_column_definition(cls, column_name, **kwargs): """Get the database column definition for a Geometry :param column_name: name of the column in the database :param kwargs: arguments :return: sqlalchemy.Geometry """ srid = kwargs['srid'] if 'srid' in kwargs else cls._srid return sqlalchemy.Column(column_name, geoalchemy2.Geometry(geometry_type='GEOMETRY', srid=srid))
class GeoStub(db.Model, models.GeoMixin): lonlat_field = "lonlat_column" geo_stub_id = db.Column(db.Integer, primary_key=True) lonlat_column = db.Column(geoalchemy2.Geometry("POINT"), nullable=False) def __init__(self, geo_stub_id: int, longitude: float, latitude: float): self.geo_stub_id = geo_stub_id self.lonlat_column = f"POINT({longitude} {latitude})"
class WebMapAnnotation(Base): __tablename__ = 'webmap_annotation' id = db.Column(db.Integer, primary_key=True) webmap_id = db.Column(db.ForeignKey(WebMap.id), nullable=False) description = db.Column(db.Unicode) style = db.Column(JSONTextType) geom = db.Column(ga.Geometry(dimension=2, srid=3857), nullable=False) webmap = db.relationship(WebMap)
class Key(db.Model): __tablename__ = 'keys' id = db.Column(db.BigInteger(), primary_key=True) user_key = db.Column(db.String(80), nullable=False) meta = db.Column(db.String(30), nullable=True) date_created = db.Column(db.Date()) uploaded = db.Column(db.DateTime(timezone=True), default=utils.utcnow) geo_boundary = db.Column(ga.Geometry('Polygon'))
class Spatial(Base): __tablename__ = 'spatial' __table_args__ = {'schema': SCHEMA} gid = sa.Column(sa.BIGINT, primary_key=True) geom = sa.Column(geoalchemy2.Geometry('Point', 4326)) timeseries = orm.relationship('Timeseries', secondary=f'{SCHEMA}.located', backref='spatial')
def upgrade(): ### commands auto generated by Alembic - please adjust! ### op.drop_table('raw_feature_codes') op.drop_table('raw_country_info') op.drop_table('raw_locations') op.add_column( 'location', sa.Column('location', ga.Geometry(geometry_type='POINT'), nullable=True))
def upgrade(): ### commands auto generated by Alembic - please adjust! ### op.create_table( 'titles', sa.Column('id', sa.Integer(), nullable=False), sa.Column('title_number', sa.String(length=9), nullable=False), sa.Column('extent', ga.Geometry( 'MULTIPOLYGON', srid=app.config['SPATIAL_REFERENCE_SYSTEM_IDENTIFIER']), nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('title_number'))
class Feature(base): __tablename__ = "feature" # __table_args__ ={ 'schema':'gazetteer'} # Not using autoload as creates issues with geometry at the moment :-( feat_id = Column(Integer, primary_key=True) feat_type = Column(Unicode(4)) status = Column(Unicode(4)) description = Column(Unicode) ref_point = Column(ga.Geometry(geometry_type="POINT", srid=4167)) updated_by = Column(Unicode(64)) update_date = Column(DateTime) names = relationship("Name", backref="feature", cascade="all, delete-orphan") annotations = relationship("FeatureAnnotation", backref="feature", cascade="all, delete-orphan") def preferredName(self): id = Database.scalar( Database.func.gazetteer.gaz_preferredNameId(self.feat_id)) return Name.get(id) @staticmethod def get(id): return Database.query(Feature).get(id) def location(self, srid=4167): if Database.session().is_modified(self): raise RuntimeError("Cannot query location of modifed feature") result = Database.querysql( """ select st_x(pt),st_y(pt) from (select st_transform(ref_point,:srid) as pt from gazetteer.feature where feat_id=:id) as ptq """, id=self.feat_id, srid=srid, ).fetchone() return [float(result[0]), float(result[1])] def setLocation(self, xy, srid=4167): wkt = Database.scalar( "select astext(st_transform(st_setsrid(st_point(:x,:y),:srid),4167))", x=xy[0], y=xy[1], srid=srid, ) self.ref_point = ga.WKTSpatialElement(wkt) def __str__(self): return "Feature<" + str(self.feat_id) + ">"
def upgrade(): op.create_table( "tree", sa.Column("id", sa.Integer(), nullable=False), sa.Column("geofile_id", sa.Integer(), nullable=True), sa.Column("geom", ga.Geometry("POINT")), sa.Column("properties", sa.dialects.postgresql.JSONB(), nullable=True), sa.ForeignKeyConstraint(["geofile_id"], ["geofile.id"], ondelete="CASCADE"), sa.PrimaryKeyConstraint("id"), ) op.create_index(op.f("ix_tree_id"), "tree", ["id"], unique=True)
class AdminRegion(db.Model): __tablename__ = 'admin_regions' gid = db.Column(db.Integer, nullable=False, primary_key=True) geom = db.Column(gdb.Geometry(geometry_type='MULTIPOLYGON', srid=4326), nullable=False) name = db.Column(db.String(150)) type = db.Column(db.String(100)) id = db.Column(db.String(64)) parent0 = db.Column(db.String(64)) parent1 = db.Column(db.String(64)) parent2 = db.Column(db.String(64)) id_com = db.Column(db.String(3)) date_cr = db.Column(db.DateTime()) area = db.Column(db.Float())
class Area(db.Model): __tablename__ = 'areas' gid = db.Column(db.Integer, nullable=False, primary_key=True) geom = db.Column(gdb.Geometry(geometry_type='MULTIPOLYGON', srid=4326), nullable=False) name = db.Column(db.String(150)) type = db.Column(db.String(100)) sous_type = db.Column(db.String(75)) resp = db.Column(db.String(254)) uicn = db.Column(db.String(5)) date_cr = db.Column(db.DateTime()) area = db.Column(db.Float()) id_mrc = db.Column(db.String(3)) id_reg = db.Column(db.String(2))
class Location(db.Model): """Locations table""" """ >>> Location(1,'683 Sutter Ave.','San Francisco','CA',37.773972, -122.431297) <Location location_id=None bathroom_id=1 street=683 Sutter Ave. city=San Francisco state=CA latitude=37.773972 longitude=-122.431297 """ __tablename__ = "locations" location_id = db.Column(db.Integer, autoincrement=True, primary_key=True) bathroom_id = db.Column(db.Integer, db.ForeignKey('bathrooms.bathroom_id'), nullable=False) street = db.Column(db.String(155), nullable=False) city = db.Column(db.String(50), nullable=False) state = db.Column(db.String(50), nullable=False) country = db.Column(db.String(50), nullable=True) latitude = db.Column(db.Float, nullable=False) longitude = db.Column(db.Float, nullable=False) directions = db.Column(db.String(2000), nullable=True) lnglat = db.Column(geoalchemy2.Geometry(geometry_type='POINT', srid=4326)) # Define relationship to bathrooms bathrooms = db.relationship("Bathroom", foreign_keys=[bathroom_id], \ uselist=False) __table_args__ = (db.CheckConstraint('latitude >= -90 and latitude <= 90', name='checklat'), db.CheckConstraint( 'longitude >= -180 and longitude <= 180', name='checklng'), {}) def __init__(self, bathroom_id, street, city, state, \ latitude, longitude, country=None, \ directions=None, lnglat=None): self.bathroom_id = bathroom_id self.street = street self.city = city self.state = state self.country = country self.latitude = latitude self.longitude = longitude self.directions = directions self.lnglat = lnglat def __repr__(self): """Provide useful representation when printed.""" return "<Location location_id={} bathroom_id={} street={} city={} \ state={} latitude={} longitude={}".format(self.location_id, \ self.bathroom_id, self.street, self.city, self.state, \ self.latitude, self.longitude) """
class Event(Base): __tablename__ = 'groza_events' event_id = db.Column(db.Unicode, primary_key=True) event_type = db.Column(db.Integer, nullable=False) # 0 - lighting, 1 - other lighting_type = db.Column( db.Integer) # 0 - cloud, 1 - positive ground, 2 - negative ground amplitude = db.Column(db.Integer) last_modified_ts = db.Column(db.TIMESTAMP, nullable=False) event_ts = db.Column(db.TIMESTAMP, nullable=False) location = db.Column(ga.Geometry('Point', srid=4326, dimension=3)) ellipse_major_ax = db.Column(db.DECIMAL) ellipse_minor_ax = db.Column(db.DECIMAL) ellipse_azimuth = db.Column(db.DECIMAL)
def upgrade(): op.create_table( 'priority_area', sa.Column('id', sa.Integer(), nullable=False), sa.Column('geometry', ga.Geometry(geometry_type='POLYGON', srid=4326), nullable=True), sa.PrimaryKeyConstraint('id')) op.create_table( 'project_priority_areas', sa.Column('project_id', sa.Integer(), nullable=True), sa.Column('priority_area_id', sa.Integer(), nullable=True), sa.ForeignKeyConstraint( ['priority_area_id'], ['priority_area.id'], ), sa.ForeignKeyConstraint( ['project_id'], ['project.id'], ))