sqlerror.sql

Running test au.org.emii.aatams.monthlyreport.MonthlyReportsTests...2015-09-18 15:15:57,262 [main] [] WARN  Sql - Failed to execute: WITH vd AS (
  SELECT DISTINCT p.id AS project_id,
	p.name AS project_name,
	i.name AS installation_name,
	ist.name AS station_name,
	vd.transmitter_id, 
	CASE WHEN vd.receiver_deployment_id IS NULL THEN rd.id ELSE vd.receiver_deployment_id END AS receiver_deployment_id, 
	registered, 
	COUNT(timestamp) AS no_detections,
	min(timestamp) AS start_date,
	max(timestamp) AS end_date,
	round((date_part('days', max(timestamp) - min(timestamp)) + date_part('hours', max(timestamp) - min(timestamp))/24)::numeric/365.25, 1) AS coverage_duration
  FROM valid_detection vd
  LEFT JOIN aatams_acoustic_species_all_deployments_view a ON a.transmitter_id = vd.transmitter_id
  FULL JOIN receiver_deployment rd ON rd.id = vd.receiver_deployment_id
  FULL JOIN installation_station ist ON ist.id = rd.station_id
  FULL JOIN installation i ON i.id = ist.installation_id
  FULL JOIN project p ON p.id = i.project_id
	GROUP BY vd.transmitter_id, vd.receiver_deployment_id, registered,rd.id,p.id, p.name,i.name,ist.name)
  SELECT CASE WHEN substring(p.name,'AATAMS')='AATAMS' THEN 'IMOS funded and co-invested' 
	      WHEN p.name = 'Coral Sea  Nautilus tracking project' 
	      OR p.name = 'Seven Gill tracking in Coastal Tasmania' 
	      OR substring(p.name,'Yongala')='Yongala'
	      OR p.name = 'Rowley Shoals reef shark tracking 2007' 
	      OR p.name = 'Wenlock River Array, Gulf of Carpentaria' THEN 'IMOS Receiver Pool' 
	      ELSE 'Fully Co-Invested' END AS funding_type,
	p.id AS project_id,
	p.name AS project_name,
	i.name AS installation_name,
	ist.name AS station_name,
	COUNT(DISTINCT receiver_deployment_id) AS no_deployments,
	CASE WHEN SUM(no_detections) IS NULL THEN 0 ELSE SUM(no_detections) END AS no_detections,
	min(rd.deploymentdatetime_timestamp) AS first_deployment_date,
	max(rd.deploymentdatetime_timestamp) AS last_deployment_date,
	min(vd.start_date) AS start_date,
	max(vd.end_date) AS end_date,
	round((date_part('days', max(vd.end_date) - min(vd.start_date)) + date_part('hours', max(vd.end_date) - min(vd.start_date))/24)::numeric/365.25, 1) AS coverage_duration,
	ROUND(st_y(ist.location)::numeric,1) AS station_lat,
	ROUND(st_x(ist.location)::numeric,1) AS station_lon,
	ROUND(min(rd.depth_below_surfacem::integer),1) AS min_depth,
	ROUND(max(rd.depth_below_surfacem::integer),1) AS max_depth,
	p.is_protected,
	COUNT(DISTINCT CASE WHEN registered = FALSE THEN transmitter_id ELSE NULL END) AS no_unreg_transmitters,
	SUM(CASE WHEN registered = FALSE THEN no_detections ELSE 0 END) AS no_unreg_detections,
	COUNT(DISTINCT transmitter_id) AS no_transmitters
  FROM project p
  FULL JOIN installation i ON i.project_id = p.id
  FULL JOIN installation_station ist ON ist.installation_id = i.id
  FULL JOIN receiver_deployment rd ON rd.station_id = ist.id
  FULL JOIN vd ON vd.receiver_deployment_id = rd.id
	WHERE p.id IS NOT NULL
	GROUP BY p.id, p.name,i.name,ist.name,p.is_protected,ist.location
	ORDER BY project_name,installation_name,station_name; because: ERROR: relation "aatams_acoustic_species_all_deployments_view" does not exist
  Position: 580
2015-09-18 15:15:57,296 [main] [] WARN  Sql - Failed to execute: WITH total_detections_registered_public AS (
	  SELECT SUM (no_detections_public) AS t,
		'no detections for registered tags that are public' AS statistics_type
	  FROM aatams_acoustic_species_data_summary_view
		WHERE registered = 'Registered tags'),
		
total_detections_species AS (
	  SELECT SUM (total_no_detections) AS t,
		'no detections at species level' AS statistics_type
	  FROM aatams_acoustic_species_data_summary_view
		WHERE common_name NOT LIKE 'All %'),

total_detections_species_public AS (
	  SELECT SUM (no_detections_public) AS t,
		'no public detections at species level' AS statistics_type
	  FROM aatams_acoustic_species_data_summary_view
		WHERE common_name NOT LIKE 'All %'),
-- OTHER TOTALS
tag_ids AS (
	  SELECT COUNT(DISTINCT transmitter_id) AS t,
		'no unique tag ids detected' AS statistics_type
	  FROM valid_detection),

tag_aatams_knows AS (
	  SELECT COUNT(*) AS t,
		'no unique registered tag ids' AS statistics_type
	  FROM device 
		WHERE class = 'au.org.emii.aatams.Tag'),
detected_tags_aatams_knows AS (
	  SELECT COUNT(DISTINCT sensor.transmitter_id) AS t,
		'no unique tag ids detected that aatams knows about' AS statistics_type
	  FROM valid_detection 
	  JOIN sensor ON valid_detection.transmitter_id = sensor.transmitter_id),
	  
tags_by_species AS (
	  SELECT COUNT(DISTINCT s.tag_id) AS t,
		'tags detected by species' AS statistics_type
	  FROM valid_detection vd
	  LEFT JOIN sensor on vd.transmitter_id = sensor.transmitter_id
	  LEFT JOIN device d on sensor.tag_id = d.id
	  JOIN surgery s ON s.tag_id = d.id)
  SELECT 'Species' AS type, t, statistics_type::text FROM total_detections_registered_public
UNION ALL
  SELECT 'Species' AS type, t, statistics_type::text FROM total_detections_species
UNION ALL
  SELECT 'Species' AS type, t, statistics_type::text FROM total_detections_species_public
UNION ALL
  SELECT 'Species' AS type, t, statistics_type::text FROM tag_ids
UNION ALL
  SELECT 'Species' AS type, t, statistics_type::text FROM tag_aatams_knows
UNION ALL
  SELECT 'Species' AS type, t, statistics_type::text FROM detected_tags_aatams_knows
UNION ALL
  SELECT 'Species' AS type, t, statistics_type::text FROM tags_by_species; because: ERROR: relation "aatams_acoustic_species_data_summary_view" does not exist
  Position: 170
2015-09-18 15:15:57,297 [main] [] WARN  Sql - Failed to execute: SELECT DISTINCT vd.id AS detection_id,
	p.name AS project_name,
	i.name AS installation_name,
	ist.name AS station_name,
	vd.receiver_name,
	rd.bottom_depthm AS bottom_depth,
	sp.common_name AS common_name,
	vd.transmitter_id AS transmitter_id,
	ar.releasedatetime_timestamp AT TIME ZONE 'UTC' AS release_date,
	ar.release_locality AS release_locality,
	vd.timestamp AT TIME ZONE 'UTC' AS detection_date,
	ST_X(ist.location) AS longitude,
	ST_Y(ist.location) AS latitude,
	sex.sex AS sex,
	sp.scientific_name AS scientific_name,
	ist.location AS geom,
	'TRUE' AS detected
  FROM valid_detection vd
  LEFT JOIN receiver_deployment rd ON vd.receiver_deployment_id = rd.id
  LEFT JOIN installation_station ist ON ist.id = rd.station_id
  LEFT JOIN installation i ON i.id = ist.installation_id
  LEFT JOIN project p ON p.id = i.project_id  
  LEFT JOIN sensor on vd.transmitter_id = sensor.transmitter_id
  LEFT JOIN device d on sensor.tag_id = d.id
  LEFT JOIN surgery s ON s.tag_id = d.id
  LEFT JOIN animal_release ar ON ar.id = s.release_id
  LEFT JOIN animal a ON a.id = ar.animal_id
  LEFT JOIN species sp ON sp.id = a.species_id 
  LEFT JOIN sex ON a.sex_id = sex.id
	WHERE date_part('day', ar.embargo_date - now()) < 0 AND p.is_protected = FALSE AND
	ar.releasedatetime_timestamp AT TIME ZONE 'UTC' < vd.timestamp AT TIME ZONE 'UTC' AND
	sp.common_name IS NOT NULL AND sp.scientific_name IS NOT NULL
	ORDER BY transmitter_id, detection_date; because: ERROR: column vd.id does not exist
  Position: 17
2015-09-18 15:15:57,298 [main] [] WARN  Sql - Failed to execute: WITH a AS (
  SELECT DISTINCT common_name
  FROM aatams_acoustic_detections_data),
  b AS (
  SELECT a.common_name,
	 COALESCE('#'||''||lpad(to_hex(trunc(random() * 16777215)::integer),6,'0')) AS colour
  FROM a),
  c AS (
  SELECT station_name, transmitter_id,
	 COUNT(DISTINCT detection_id) AS no_detections
  FROM aatams_acoustic_detections_data
	GROUP BY station_name, transmitter_id)
  SELECT project_name,
	installation_name,
	d.station_name,
	d.common_name,
	d.transmitter_id,
	release_locality,
	date(min(detection_date)) AS first_detection_date,
	date(max(detection_date)) AS last_detection_date,
	sex,
	scientific_name,
	replace(ST_AsEWKT(ST_SIMPLIFY(ST_MAKELINE(geom),0.01)), 'LINESTRING','MULTIPOINT')::geometry AS geom,
	b.colour AS colour,
	c.no_detections,
	bool_or(no_detections > 0) AS detected
  FROM aatams_acoustic_detections_data d
  LEFT JOIN b ON b.common_name = d.common_name
  LEFT JOIN c ON c.station_name = d.station_name AND c.transmitter_id = d.transmitter_id
  	GROUP BY project_name, installation_name, d.station_name, d.common_name, d.transmitter_id, release_locality, sex, scientific_name, colour, no_detections
UNION ALL
  SELECT p.name AS project_name, 
	i.name AS installation_name, 
	ist.name AS station_name,
	NULL AS common_name,
	NULL AS transmitter_id,
	NULL AS release_locality,
	NULL AS first_detection_date,
	NULL AS last_detection_date,
	NULL AS sex,
	NULL AS scientific_name,
	ist.location AS geom,
	'#FF0000' AS colour,
	0 AS no_detections,
	'FALSE' AS detected
  FROM installation_station ist
  LEFT JOIN installation i ON i.id = ist.installation_id
  LEFT JOIN project p ON p.id = i.project_id 
	WHERE ist.name NOT IN (SELECT DISTINCT station_name FROM aatams_acoustic_detections_data)
	ORDER BY detected, common_name, transmitter_id, first_detection_date; because: ERROR: relation "aatams_acoustic_detections_data" does not exist
  Position: 50

                    testQueries...FAILED