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 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 _validate_the_geom(geom):
"""Takes a GeoJSON Point and converts it to a WKT"""
long, lat = geom["coordinates"]
errors = ""
if lat < -90 or lat > 90:
errors += f"Latitude {long} is out of range"
if long < -180 or long > 180:
errors += f"Longitude {long} is out of range"
if errors:
raise ValidationError(errors)
shape = shapely.geometry.shape(geom)
wkt = geoalchemy2.WKTElement(str(shape), srid=_WGS84_COORDINATE_SYSTEM)
return wkt
def updateUserLocation():
statuscode = 500
userID = get_jwt_identity()
user = User.query.get(userID)
if user is None:
res = {"error": "Incorrect Email"}
statuscode = 400
else:
postBody = json.loads(request.data)
lat = postBody["lat"]
longt = postBody["longt"]
user.point = geoalchemy2.WKTElement("POINT({} {})".format(longt, lat),
srid=4326)
db.session.commit()
statuscode = 200
res = {"data": user.serialize()}
return json.dumps(res), statuscode
def _to_wkt_elem(cls, value, srid=None):
"""Private method to create a geoalchemy WKTElement from the GOB_type, based on value (and optional srid)"""
if srid is None:
srid = cls._srid
return geoalchemy2.WKTElement(value, srid=srid)
def to_wkt(geom):
""" Use with apply to make Well-Known-Text objects of geom """
return ga.WKTElement(geom.wkt, srid=4326)