def createView(self):
# filter indexes
catalog = self.env.catalog.index_catalog
xmlindex_list = catalog.getIndexes(package_id='exupery',
resourcetype_id='infrared')
filter = ['project_id', 'volcano_id', 'latitude_hotspot',
'longitude_hotspot', 'start_datetime', 'end_datetime',
'radiant_flux_hotspot', 'temperature_hotspot',
'area_hotspot', 'percentage_area_hotspot',
'effusion_hotspot']
xmlindex_list = [x for x in xmlindex_list if x.label in filter]
if not xmlindex_list:
return
# build up query
query, joins = catalog._createIndexView(xmlindex_list, compact=True)
options = [
sql.func.GeomFromText(
sql.text("'POINT(' || longitude_hotspot.keyval || ' ' || " + \
"latitude_hotspot.keyval || ')', 4326")).label('geom')
]
for option in options:
query.append_column(option)
query = query.select_from(joins)
# don't show rows without hotspots
query = query.where(sql.column('latitude_hotspot') != None)
return util.compileStatement(query)
def createView(self):
# filter indexes
catalog = self.env.catalog.index_catalog
xmlindex_list = catalog.getIndexes(package_id='seismology',
resourcetype_id='station')
filter = ['network_id', 'station_id', 'location_id', 'channel_id',
'latitude', 'longitude', 'start_datetime', 'end_datetime',
'quality']
xmlindex_list = [x for x in xmlindex_list if x.label in filter]
if not xmlindex_list:
return
# build up query
query, joins = catalog._createIndexView(xmlindex_list, compact=True)
options = [
sql.func.random().label("random"),
sql.func.GeomFromText(
sql.text("'POINT(' || longitude.keyval || ' ' || " + \
"latitude.keyval || ')', 4326")).label('geom')
]
for option in options:
query.append_column(option)
query = query.select_from(joins)
return util.compileStatement(query)
def createView(self):
# filter indexes
catalog = self.env.catalog.index_catalog
xmlindex_list = catalog.getIndexes(package_id='seismology',
resourcetype_id='event')
filter = ['datetime', 'latitude', 'longitude', 'depth',
'magnitude', 'magnitude_type', 'event_type', 'np1_strike',
'np1_dip', 'np1_rake', 'mt_mrr', 'mt_mtt', 'mt_mpp',
'mt_mrt', 'mt_mrp', 'mt_mtp', 'localisation_method']
xmlindex_list = [x for x in xmlindex_list if x.label in filter]
if not xmlindex_list:
return
# build up query
query, joins = catalog._createIndexView(xmlindex_list, compact=True)
options = [
sql.literal_column("datetime.keyval").label("end_datetime"),
sql.literal_column("datetime.keyval").label("start_datetime"),
sql.case(
value=sql.literal_column("localisation_method.keyval"),
whens={'manual': 'circle'},
else_='square').label('gis_localisation_method'),
sql.func.GeomFromText(
sql.text("'POINT(' || longitude.keyval || ' ' || " + \
"latitude.keyval || ')', 4326")).label('geom')
]
for option in options:
query.append_column(option)
query = query.select_from(joins)
return util.compileStatement(query)
def createView(self):
# make sure the relevant database tables exist!
metadata = Base.metadata
metadata.create_all(self.db.engine, checkfirst=True)
# filter indexes
catalog = self.env.catalog.index_catalog
xmlindex_list = catalog.getIndexes(package_id='seismology',
resourcetype_id='station')
filter = ['network_id', 'location_id', 'station_id', 'channel_id',
'latitude', 'longitude', 'start_datetime', 'end_datetime']
xmlindex_list = [x for x in xmlindex_list if x.label in filter]
if not xmlindex_list:
return
# build up query
query, joins = catalog._createIndexView(xmlindex_list, compact=True)
options = [
sql.functions.max(WaveformChannel.endtime).label("latest_activity"),
# XXX: not UTC!!!!
(sql.func.now() - sql.functions.max(WaveformChannel.endtime)).label("latency"),
(sql.literal_column("end_datetime.keyval") == None).label("active"),
sql.func.random().label("random"),
sql.func.GeomFromText(
sql.text("'POINT(' || longitude.keyval || ' ' || " + \
"latitude.keyval || ')', 4326")).label('geom')
]
for option in options:
query.append_column(option)
oncl = WaveformChannel.network == sql.literal_column("network_id.keyval")
oncl = sql.and_(oncl, WaveformChannel.station == sql.literal_column("station_id.keyval"))
oncl = sql.and_(oncl, WaveformChannel.channel == sql.literal_column("channel_id.keyval"))
oncl = sql.and_(oncl, sql.or_(
WaveformChannel.location == sql.literal_column("location_id.keyval"),
sql.and_(
WaveformChannel.location == None,
sql.literal_column("location_id.keyval") == None
)))
oncl = sql.and_(oncl, WaveformChannel.endtime > sql.literal_column("start_datetime.keyval"))
oncl = sql.and_(oncl, sql.or_(
WaveformChannel.endtime <= sql.literal_column("end_datetime.keyval"),
sql.literal_column("end_datetime.keyval") == None
))
#joins = joins.join("default_waveform_channels", onclause=oncl)
query = query.select_from(joins).group_by(
document_tab.c['id'],
document_meta_tab.c['datetime'],
sql.literal_column("station_id.keyval"),
sql.literal_column("channel_id.keyval"),
sql.literal_column("network_id.keyval"),
sql.literal_column("location_id.keyval"),
sql.literal_column("latitude.keyval"),
sql.literal_column("longitude.keyval"),
sql.literal_column("start_datetime.keyval"),
sql.literal_column("end_datetime.keyval"))
return util.compileStatement(query)
def createView(self, name, query):
"""
Create a SQL view from a query and a view name.
"""
try:
self.dropView(name)
except NotFoundError:
pass
name = self.engine.dialect.identifier_preparer.quote_identifier(name)
if not isinstance(query, basestring):
# keep it backwards compatible for SQLViewRegistry
# (is this used at all ?)
self.env.log.warn('compileStatement should not be used for views!')
query = compileStatement(query)
sql = "CREATE VIEW %s AS %s" % (name, query)
self.engine.execute(sql)
def createView(self):
# filter indexes
catalog = self.env.catalog.index_catalog
xmlindex_list = catalog.getIndexes(package_id='exupery',
resourcetype_id='gps-data')
filter = ['station_id', 'start_datetime', 'end_datetime',
'abs_conf_azimuth_a', 'abs_conf_ellipse_b',
'abs_conf_ellipse_a', 'abs_height_conf', 'abs_height',
'abs_longitude', 'abs_latitude']
xmlindex_list = [x for x in xmlindex_list if x.label in filter]
if not xmlindex_list:
return
# build up query
query, joins = catalog._createIndexView(xmlindex_list, compact=True)
query = query.select_from(joins)
return util.compileStatement(query)
def createView(self):
# filter indexes
catalog = self.env.catalog.index_catalog
xmlindex_list = catalog.getIndexes(package_id='exupery',
resourcetype_id='gps-data')
filter = ['project_id', 'volcano_id', 'station_id',
'start_datetime', 'end_datetime', 'epoch0_longitude',
'epoch0_latitude']
xmlindex_list = [x for x in xmlindex_list if x.label in filter]
if not xmlindex_list:
return
# build up query
query, joins = catalog._createIndexView(xmlindex_list, compact=True)
options = [
sql.literal_column("epoch0_latitude.keyval").label("latitude"),
sql.literal_column("epoch0_longitude.keyval").label("longitude"),
sql.func.GeomFromText(
sql.text("'POINT(' || epoch0_longitude.keyval || ' ' || " + \
"epoch0_latitude.keyval || ')', 4326")).label('geom')
]
for option in options:
query.append_column(option)
query = query.select_from(joins)
return util.compileStatement(query)
