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)