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'

# Using the function get_db, we receive 2 ways to interact with the database
engine, session = get_db(db_name)

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

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

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 

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

# Close out the session to avoid hanging transactions
session.close()