Exporting Data
Contents
Exporting Data¶
You may want to export your queried data from the database. In this section we talk about how!
# Import the function to get connect to the db
from snowexsql.db import get_db
from snowexsql.data import SiteData, PointData, LayerData, ImageData
# This is what you will use for all of hackweek to access the db
db_name = 'snow:hackweek@db.snowexdata.org/snowex'
Shapefiles and CSVs¶
The following can be done with ANY SiteData, PointData, or LayerData query.
Note: Shapefiles do not support datetime object so they must be converted to strings before writing.
# import the hand method for converting queries to dataframes
from snowexsql.conversions import query_to_geopandas
# Grab a session
engine, session = get_db(db_name)
qry = session.query(SiteData.geom).limit(10)
df = query_to_geopandas(qry, engine)
# Write to shapefile
df.to_file('site_data.shp')
# Write to a csv
df.to_csv('site_data.csv')
session.close()
Rasters¶
# import the handy function to convert raster db results to rasterio
from snowexsql.conversions import raster_to_rasterio
# Import the SQL function to access PostGIS functions
from sqlalchemy.sql import func
# Import rasterio for Writing
import rasterio
# Grab a session
engine, session = get_db(db_name)
# Query 1 raster tile and convert it to a geotiff
result = session.query(func.ST_AsTiff(ImageData.raster)).limit(1).all()
# Convert the dataset to a rasterio dataset
dataset = raster_to_rasterio(session, result)
# Copy the profile/tiff metadata (not to be confused with the database metadata)
profile = dataset[0].profile
# Write to a file
with rasterio.open('raster.tif', 'w', **profile) as dst:
dst.write(dataset[0].read(1), 1)
session.close()
/tmp/ipykernel_2693/1676431373.py:14: SAWarning: UserDefinedType Raster() will not produce a cache key because the ``cache_ok`` attribute is not set to True. This can have significant performance implications including some performance degradations in comparison to prior SQLAlchemy versions. Set this attribute to True if this type object's state is safe to use in a cache key, or False to disable this warning. (Background on this error at: https://sqlalche.me/e/14/cprf)
result = session.query(func.ST_AsTiff(ImageData.raster)).limit(1).all()