begin;

truncate obs_by_station_id;
drop index obs_by_station_id_rts;
drop index obs_by_station_id_stid;

-- build a table that has the past 2 day data by station_id

insert into obs_by_station_id (
  station_id,
  report_time_stamp,
  the_geom,
  air_pressure_time_stamp,
  air_pressure_mb,
  air_pressure_mb_graph_and_data,
  air_pressure_inches_mercury,
  air_pressure_inches_mercury_graph_and_data,
  air_temperature_time_stamp,
  air_temperature_celcius,
  air_temperature_celcius_graph_and_data,
  air_temperature_fahrenheit,
  air_temperature_fahrenheit_graph_and_data,
  sea_surface_current_speed_and_dir_time_stamp,
  sea_surface_current_speed_and_dir_mps,
  sea_surface_current_speed_and_dir_mps_graph_and_data,
  sea_surface_current_speed_and_dir_mph,
  sea_surface_current_speed_and_dir_mph_graph_and_data,
  sea_surface_current_speed_and_dir_knots,
  sea_surface_current_speed_and_dir_knots_graph_and_data,
  salinity_time_stamp,
  salinity_ppt,
  salinity_ppt_graph_and_data,
  water_level_time_stamp,
  water_level_mllw_m,
  water_level_mllw_m_graph_and_data,
  water_level_mllw_ft,
  water_level_mllw_ft_graph_and_data,
  water_level_msl_m,
  water_level_msl_m_graph_and_data,
  water_level_msl_ft,
  water_level_msl_ft_graph_and_data,
  water_level_navd88_m,
  water_level_navd88_m_graph_and_data,
  water_level_navd88_ft,
  water_level_navd88_ft_graph_and_data,
  wind_speed_and_dir_time_stamp,
  wind_speed_and_dir_mps,
  wind_speed_and_dir_mps_graph_and_data,
  wind_speed_and_dir_mph,
  wind_speed_and_dir_mph_graph_and_data,
  wind_speed_and_dir_knots,
  wind_speed_and_dir_knots_graph_and_data,
  wind_gust_time_stamp,
  wind_gust_mps,
  wind_gust_mps_graph_and_data,
  wind_gust_mph,
  wind_gust_mph_graph_and_data,
  wind_gust_knots,
  wind_gust_knots_graph_and_data,
  normalized_wind_speed_and_dir_time_stamp,
  normalized_wind_speed_and_dir_mps,
  normalized_wind_speed_and_dir_mps_graph_and_data,
  normalized_wind_speed_and_dir_mph,
  normalized_wind_speed_and_dir_mph_graph_and_data,
  normalized_wind_speed_and_dir_knots,
  normalized_wind_speed_and_dir_knots_graph_and_data,
  normalized_wind_gust_time_stamp,
  normalized_wind_gust_mps,
  normalized_wind_gust_mps_graph_and_data,
  normalized_wind_gust_mph,
  normalized_wind_gust_mph_graph_and_data,
  normalized_wind_gust_knots,
  normalized_wind_gust_knots_graph_and_data,
  sea_surface_temperature_time_stamp,
  sea_surface_temperature_celcius,
  sea_surface_temperature_celcius_graph_and_data,
  sea_surface_temperature_fahrenheit,
  sea_surface_temperature_fahrenheit_graph_and_data,
  significant_wave_height_time_stamp,
  significant_wave_height_m,
  significant_wave_height_m_graph_and_data,
  significant_wave_height_ft,
  significant_wave_height_ft_graph_and_data,
  dominant_wave_period_time_stamp,
  dominant_wave_period_seconds,
  dominant_wave_period_seconds_graph_and_data,
  bottom_water_temp_time_stamp,
  bottom_water_temp_celcius,
  bottom_water_temp_celcius_graph_and_data,
  bottom_water_temp_fahrenheit,
  bottom_water_temp_fahrenheit_graph_and_data
)
select
  station_id,
  report_time_stamp,
  GeometryFromText('POINT(' || lon || ' ' || lat || ')',-1) as the_geom,
  air_pressure_map.time_stamp as air_pressure_time_stamp,
  value_pressure as air_pressure_mb,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_air_pressure_in_situ.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&air_pressure_table=air_pressure_prod&archive_flag=&time_stamp=' || to_char(air_pressure_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&pressure_units=MB' as air_pressure_mb_graph_and_data,
  value_pressure_inches_mercury as air_pressure_inches_mercury,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_air_pressure_in_situ.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&air_pressure_table=air_pressure_prod&archive_flag=&time_stamp=' || to_char(air_pressure_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&pressure_units=INCHES_MERCURY' as air_pressure_inches_mercury_graph_and_data,
  air_temperature_map.time_stamp as air_temperature_time_stamp,
  air_temperature_map.value_temperature_celcius as air_temperature_celcius,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_air_temperature.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&air_temperature_table=air_temperature_prod&archive_flag=&time_stamp=' || to_char(air_temperature_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&degree_units=C' as air_temperature_celcius_graph_and_data,
  air_temperature_map.value_temperature_fahrenheit as air_tempearture_fahrenheit,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_air_temperature.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&air_temperature_table=air_temperature_prod&archive_flag=&time_stamp=' || to_char(air_temperature_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&degree_units=F' as air_tempearture_fahrenheit_graph_and_data,
  current_in_situ_map.time_stamp as sea_surface_current_speed_and_dir_time_stamp,
  current_in_situ_map.value as sea_surface_current_speed_and_dir_mps,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_surface_currents_in_situ.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&surface_currents_table=current_in_situ_prod&archive_flag=&time_stamp=' || to_char(current_in_situ_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&velocity_units=MPS' as sea_surface_current_speed_and_dir_mps_graph_and_data,
  current_in_situ_map.value_mph as sea_surface_current_speed_and_dir_mph,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_surface_currents_in_situ.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&surface_currents_table=current_in_situ_prod&archive_flag=&time_stamp=' || to_char(current_in_situ_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&velocity_units=MPH' as sea_surface_current_speed_and_dir_mph_graph_and_data,
  current_in_situ_map.value_knots as sea_surface_current_speed_and_dir_knots,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_surface_currents_in_situ.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&surface_currents_table=current_in_situ_prod&archive_flag=&time_stamp=' || to_char(current_in_situ_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&velocity_units=KNOTS' as sea_surface_current_speed_and_dir_knots_graph_and_data,
  salinity_map.time_stamp as salinity_time_stamp,
  value_salinity as salinity_ppt,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_salinity.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&salinity_table=salinity_prod&archive_flag=&time_stamp=' || to_char(salinity_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') as salinity_ppt_graph_and_data,
  water_level_map.time_stamp as water_level_time_stamp,
  value_water_level_mllw as water_level_mllw_m,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_water_level_obs.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&water_level_table=water_level_prod&archive_flag=&normalized_prefix=normalized_&time_stamp=' || to_char(water_level_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&water_level_reference=MLLW&elevation_units=METERS' as water_level_mllw_m_graph_and_data,
  value_ft_water_level_mllw as water_level_mllw_ft,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_water_level_obs.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&water_level_table=water_level_prod&archive_flag=&normalized_prefix=normalized_&time_stamp=' || to_char(water_level_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&water_level_reference=MLLW&elevation_units=FEET' as water_level_mllw_ft_graph_and_data,
  value_water_level_msl as water_level_msl_m,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_water_level_obs.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&water_level_table=water_level_prod&archive_flag=&normalized_prefix=normalized_&time_stamp=' || to_char(water_level_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&water_level_reference=MSL&elevation_units=METERS' as water_level_msl_m_graph_and_data,
  value_ft_water_level_msl as water_level_msl_ft,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_water_level_obs.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&water_level_table=water_level_prod&archive_flag=&normalized_prefix=normalized_&time_stamp=' || to_char(water_level_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&water_level_reference=MSL&elevation_units=FEET' as water_level_msl_ft_graph_and_data,
  value_water_level_navd88 as water_level_navd88_m,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_water_level_obs.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&water_level_table=water_level_prod&archive_flag=&normalized_prefix=normalized_&time_stamp=' || to_char(water_level_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&water_level_reference=NAVD88&elevation_units=METERS' as water_level_navd88_m_graph_and_data,
  value_ft_water_level_navd88 as water_level_navd88_ft,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_water_level_obs.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&water_level_table=water_level_prod&archive_flag=&normalized_prefix=normalized_&time_stamp=' || to_char(water_level_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&water_level_reference=NAVD88&elevation_units=FEET' as water_level_navd88_ft_graph_and_data,
  wind_map.time_stamp as wind_speed_and_dir_time_stamp,
  wind_map.value as wind_speed_and_dir_mps_mps,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_wind.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&wind_table=wind_prod&archive_flag=&time_stamp=' || to_char(wind_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&velocity_units=MPS' as wind_speed_and_dir_mps_graph_and_data,
  wind_map.value_mph as wind_speed_and_dir_mps_mph,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_wind.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&wind_table=wind_prod&archive_flag=&time_stamp=' || to_char(wind_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&velocity_units=MPH' as wind_speed_and_dir_mph_graph_and_data,
  wind_map.value_knots as wind_speed_and_dir_mps_knots,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_wind.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&wind_table=wind_prod&archive_flag=&time_stamp=' || to_char(wind_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&velocity_units=KNOTS' as wind_speed_and_dir_knots_graph_and_data,
  wind_map.time_stamp as wind_gust_time_stamp,
  wind_map.wind_gust_value as wind_gust_mps,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_wind_gust.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&wind_table=wind_prod&archive_flag=&time_stamp=' || to_char(wind_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&velocity_units=MPS' as wind_gust_mps_graph_and_data,
  wind_map.wind_gust_value_mph as wind_gust_mph,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_wind_gust.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&wind_table=wind_prod&archive_flag=&time_stamp=' || to_char(wind_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&velocity_units=MPH' as wind_gust_mph_graph_and_data,
  wind_map.wind_gust_value_knots as wind_gust_knots,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_wind_gust.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&wind_table=wind_prod&archive_flag=&time_stamp=' || to_char(wind_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&velocity_units=KNOTS' as wind_gust_knots_graph_and_data,
  wind_map.time_stamp as normalized_wind_speed_and_dir_time_stamp,
  wind_map.normalized_value as normalized_wind_speed_and_dir_mps,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_wind.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&wind_table=wind_prod&archive_flag=&normalized_prefix=normalized_&time_stamp=' || to_char(wind_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&velocity_units=MPS' as normalized_wind_speed_and_dir_mps_graph_and_data,
  wind_map.normalized_value_mph as normalized_wind_speed_and_dir_mph,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_wind.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&wind_table=wind_prod&archive_flag=&normalized_prefix=normalized_&time_stamp=' || to_char(wind_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&velocity_units=MPH' as normalized_wind_speed_and_dir_mph_graph_and_data,
  wind_map.normalized_value_knots as normalized_wind_speed_and_dir_knots,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_wind.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&wind_table=wind_prod&archive_flag=&normalized_prefix=normalized_&time_stamp=' || to_char(wind_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&velocity_units=KNOTS' as normalized_wind_speed_and_dir_knots_graph_and_data,
  wind_map.time_stamp as normalized_wind_gust_time_stamp,
  wind_map.normalized_wind_gust_value as wind_gust_mps,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_wind_gust.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&wind_table=wind_prod&archive_flag=&normalized_prefix=normalized_&time_stamp=' || to_char(wind_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&velocity_units=MPS' as normalized_wind_gust_mps_graph_and_data,
  wind_map.normalized_wind_gust_value_mph as wind_gust_mph,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_wind_gust.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&wind_table=wind_prod&archive_flag=&normalized_prefix=normalized_&time_stamp=' || to_char(wind_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&velocity_units=MPH' as normalized_wind_gust_mph_graph_and_data,
  wind_map.normalized_wind_gust_value_knots as wind_gust_knots,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_wind_gust.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&wind_table=wind_prod&archive_flag=&normalized_prefix=normalized_&time_stamp=' || to_char(wind_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&velocity_units=KNOTS' as normalized_wind_gust_knots_graph_and_data,
  sst_map.time_stamp as sea_surface_temperature_time_stamp,
  sst_map.value_temperature_celcius as sea_surface_temeprature_celcius,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_sst.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&sst_table=sst_prod&archive_flag=&time_stamp=' || to_char(sst_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&degree_units=C' as sea_surface_temeprature_celcius_graph_and_data,
  sst_map.value_temperature_fahrenheit as sea_surface_temperature_fahrenheit,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_sst.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&sst_table=sst_prod&archive_flag=&time_stamp=' || to_char(sst_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&degree_units=F' as sea_surface_temperature_fahrenheit_graph_and_data,
  wave_in_situ_map.time_stamp as significant_wave_height_time_stamp,
  wave_in_situ_map.value_significant_wave_height as significant_wave_height_m,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_significant_wave_height.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&significant_wave_height_table=wave_in_situ_prod&archive_flag=&time_stamp=' || to_char(wave_in_situ_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&elevation_units=METERS' as significant_wave_height_m_graph_and_data,
  wave_in_situ_map.value_ft_significant_wave_height as significant_wave_height_ft,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_significant_wave_height.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&significant_wave_height_table=wave_in_situ_prod&archive_flag=&time_stamp=' || to_char(wave_in_situ_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&elevation_units=FEET' as significant_wave_height_ft_graph_and_data,
  wave_in_situ_map.time_stamp as dominant_wave_period_time_stamp,
  wave_in_situ_map.value_dominant_wave_period as dominant_wave_period_seconds,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_dominant_wave_period.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&dominant_wave_period_table=wave_in_situ_prod&archive_flag=&time_stamp=' || to_char(wave_in_situ_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') as dominant_wave_period_seconds_graph_and_data,
  bottom_water_temp_map.time_stamp as bottom_water_temp_time_stamp,
  bottom_water_temp_map.value_temperature_celcius as bottom_water_temp_celcius,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_bottom_water_temperature.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&sst_table=bottom_water_temp_prod&archive_flag=&time_stamp=' || to_char(bottom_water_temp_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&degree_units=C' as bottom_water_temp_celcius_graph_and_data,
  bottom_water_temp_map.value_temperature_fahrenheit as bottom_water_temp_fahrenheit,
  'http://nautilus.baruch.sc.edu/portal_rs/query_details_bottom_water_temperature.phtml?hour_range=24&station_id=' || station_id || '&lon=' || lon || '&lat=' || lat || '&sst_table=bottom_water_temp_prod&archive_flag=&time_stamp=' || to_char(sst_map.time_stamp,'YYYY_MM_DD_HH24_MI_SS') || '&degree_units=F' as bottom_water_temp_fahrenheit_graph_and_data
from
  air_pressure_map full join air_temperature_map using (station_id,report_time_stamp,lon,lat)
  full join current_in_situ_map using (station_id,report_time_stamp,lon,lat)
  full join salinity_map using (station_id,report_time_stamp,lon,lat)
  full join water_level_map using (station_id,report_time_stamp,lon,lat)
  full join wind_map using (station_id,report_time_stamp,lon,lat)
  full join sst_map using (station_id,report_time_stamp,lon,lat)
  full join bottom_water_temp_map using (station_id,report_time_stamp,lon,lat)
  full join wave_in_situ_map using (station_id,report_time_stamp,lon,lat)
where
  report_time_stamp >= now() - interval '2 days';

create index obs_by_station_id_rts on obs_by_station_id(report_time_stamp);
create index obs_by_station_id_stid on obs_by_station_id(station_id);

truncate latest_obs_by_station_id;

-- build a table w/ only the latest data by station_id

insert into latest_obs_by_station_id (
  station_id,
  report_time_stamp,
  the_geom,
  air_pressure_time_stamp,
  air_pressure_mb,
  air_pressure_mb_graph_and_data,
  air_pressure_inches_mercury,
  air_pressure_inches_mercury_graph_and_data,
  air_temperature_time_stamp,
  air_temperature_celcius,
  air_temperature_celcius_graph_and_data,
  air_temperature_fahrenheit,
  air_temperature_fahrenheit_graph_and_data,
  sea_surface_current_speed_and_dir_time_stamp,
  sea_surface_current_speed_and_dir_mps,
  sea_surface_current_speed_and_dir_mps_graph_and_data,
  sea_surface_current_speed_and_dir_mph,
  sea_surface_current_speed_and_dir_mph_graph_and_data,
  sea_surface_current_speed_and_dir_knots,
  sea_surface_current_speed_and_dir_knots_graph_and_data,
  salinity_time_stamp,
  salinity_ppt,
  salinity_ppt_graph_and_data,
  water_level_time_stamp,
  water_level_mllw_m,
  water_level_mllw_m_graph_and_data,
  water_level_mllw_ft,
  water_level_mllw_ft_graph_and_data,
  water_level_msl_m,
  water_level_msl_m_graph_and_data,
  water_level_msl_ft,
  water_level_msl_ft_graph_and_data,
  water_level_navd88_m,
  water_level_navd88_m_graph_and_data,
  water_level_navd88_ft,
  water_level_navd88_ft_graph_and_data,
  wind_speed_and_dir_time_stamp,
  wind_speed_and_dir_mps,
  wind_speed_and_dir_mps_graph_and_data,
  wind_speed_and_dir_mph,
  wind_speed_and_dir_mph_graph_and_data,
  wind_speed_and_dir_knots,
  wind_speed_and_dir_knots_graph_and_data,
  wind_gust_time_stamp,
  wind_gust_mps,
  wind_gust_mps_graph_and_data,
  wind_gust_mph,
  wind_gust_mph_graph_and_data,
  wind_gust_knots,
  wind_gust_knots_graph_and_data,
  normalized_wind_speed_and_dir_time_stamp,
  normalized_wind_speed_and_dir_mps,
  normalized_wind_speed_and_dir_mps_graph_and_data,
  normalized_wind_speed_and_dir_mph,
  normalized_wind_speed_and_dir_mph_graph_and_data,
  normalized_wind_speed_and_dir_knots,
  normalized_wind_speed_and_dir_knots_graph_and_data,
  normalized_wind_gust_time_stamp,
  normalized_wind_gust_mps,
  normalized_wind_gust_mps_graph_and_data,
  normalized_wind_gust_mph,
  normalized_wind_gust_mph_graph_and_data,
  normalized_wind_gust_knots,
  normalized_wind_gust_knots_graph_and_data,
  sea_surface_temperature_time_stamp,
  sea_surface_temperature_celcius,
  sea_surface_temperature_celcius_graph_and_data,
  sea_surface_temperature_fahrenheit,
  sea_surface_temperature_fahrenheit_graph_and_data,
  significant_wave_height_time_stamp,
  significant_wave_height_m,
  significant_wave_height_m_graph_and_data,
  significant_wave_height_ft,
  significant_wave_height_ft_graph_and_data,
  dominant_wave_period_time_stamp,
  dominant_wave_period_seconds,
  dominant_wave_period_seconds_graph_and_data,
  bottom_water_temp_time_stamp,
  bottom_water_temp_celcius,
  bottom_water_temp_celcius_graph_and_data,
  bottom_water_temp_fahrenheit,
  bottom_water_temp_fahrenheit_graph_and_data
)
select
  station_id,
  report_time_stamp,
  the_geom,
  air_pressure_time_stamp,
  air_pressure_mb,
  air_pressure_mb_graph_and_data,
  air_pressure_inches_mercury,
  air_pressure_inches_mercury_graph_and_data,
  air_temperature_time_stamp,
  air_temperature_celcius,
  air_temperature_celcius_graph_and_data,
  air_temperature_fahrenheit,
  air_temperature_fahrenheit_graph_and_data,
  sea_surface_current_speed_and_dir_time_stamp,
  sea_surface_current_speed_and_dir_mps,
  sea_surface_current_speed_and_dir_mps_graph_and_data,
  sea_surface_current_speed_and_dir_mph,
  sea_surface_current_speed_and_dir_mph_graph_and_data,
  sea_surface_current_speed_and_dir_knots,
  sea_surface_current_speed_and_dir_knots_graph_and_data,
  salinity_time_stamp,
  salinity_ppt,
  salinity_ppt_graph_and_data,
  water_level_time_stamp,
  water_level_mllw_m,
  water_level_mllw_m_graph_and_data,
  water_level_mllw_ft,
  water_level_mllw_ft_graph_and_data,
  water_level_msl_m,
  water_level_msl_m_graph_and_data,
  water_level_msl_ft,
  water_level_msl_ft_graph_and_data,
  water_level_navd88_m,
  water_level_navd88_m_graph_and_data,
  water_level_navd88_ft,
  water_level_navd88_ft_graph_and_data,
  wind_speed_and_dir_time_stamp,
  wind_speed_and_dir_mps,
  wind_speed_and_dir_mps_graph_and_data,
  wind_speed_and_dir_mph,
  wind_speed_and_dir_mph_graph_and_data,
  wind_speed_and_dir_knots,
  wind_speed_and_dir_knots_graph_and_data,
  wind_gust_time_stamp,
  wind_gust_mps,
  wind_gust_mps_graph_and_data,
  wind_gust_mph,
  wind_gust_mph_graph_and_data,
  wind_gust_knots,
  wind_gust_knots_graph_and_data,
  normalized_wind_speed_and_dir_time_stamp,
  normalized_wind_speed_and_dir_mps,
  normalized_wind_speed_and_dir_mps_graph_and_data,
  normalized_wind_speed_and_dir_mph,
  normalized_wind_speed_and_dir_mph_graph_and_data,
  normalized_wind_speed_and_dir_knots,
  normalized_wind_speed_and_dir_knots_graph_and_data,
  normalized_wind_gust_time_stamp,
  normalized_wind_gust_mps,
  normalized_wind_gust_mps_graph_and_data,
  normalized_wind_gust_mph,
  normalized_wind_gust_mph_graph_and_data,
  normalized_wind_gust_knots,
  normalized_wind_gust_knots_graph_and_data,
  sea_surface_temperature_time_stamp,
  sea_surface_temperature_celcius,
  sea_surface_temperature_celcius_graph_and_data,
  sea_surface_temperature_fahrenheit,
  sea_surface_temperature_fahrenheit_graph_and_data,
  significant_wave_height_time_stamp,
  significant_wave_height_m,
  significant_wave_height_m_graph_and_data,
  significant_wave_height_ft,
  significant_wave_height_ft_graph_and_data,
  dominant_wave_period_time_stamp,
  dominant_wave_period_seconds,
  dominant_wave_period_seconds_graph_and_data,
  bottom_water_temp_time_stamp,
  bottom_water_temp_celcius,
  bottom_water_temp_celcius_graph_and_data,
  bottom_water_temp_fahrenheit,
  bottom_water_temp_fahrenheit_graph_and_data
from
  obs_by_station_id a
where
  report_time_stamp = (
    select
      report_time_stamp
    from
      obs_by_station_id b
    where
      a.station_id = b.station_id
    order by
      station_id,
      report_time_stamp desc
    limit 1
  );
end;
begin;
update latest_obs_by_station_id set nws_announcement = NULL,
  nws_announcement_url = NULL;
update latest_obs_by_station_id set nws_announcement = 
  vtec_phenomena || ' ' || lower(vtec_significance),
  nws_announcement_url = announcement_url || '&station_id=' || station_id
  from (select * from nws_announcements_verbose order by priority asc) as nws_announcements_verbose
  where now() <= vtec_end_date_time
  and vtec_product_class <> 'Test product' and vtec_actions <> 'Event cancelled'
  and contains(nws_announcements_verbose.the_geom,latest_obs_by_station_id.the_geom);
end;