search

pacificclimate / pdp_util

A package supplying numerous apps for running PCIC's data server
GNU General Public License v3.0
0 stars 0 forks source link

Using 'Clip time series to filter date range' returns a NetCDF file with 0 observations #2

Closed basilveerman closed 9 years ago

basilveerman commented 9 years ago

Summary

When 'Clip time series to filter date range' is selected for downloads on the pcds map, the sql statment sent to the databse has a clause WHERE (obs_time >= '2000-12-31 00:00:00') AND (obs_time <= '2000-12-31 00:00:00') resulting in delivering a file with 0 observations.

Details and logs

This request works as expected and returns a NetCDF file with 1387 observations:

http://dev_pdp_server/auth/agg/?from-date=1970%2F01%2F01&to-date=2000%2F12%2F31&input-polygon=MULTIPOLYGON%28%28%28-119.27142393125258+52.68728947124434%2C-119.28155103111212+52.62293479212887%2C-119.1170647070386+52.64580799959%2C-119.1340318536963+52.694272675011334%2C-119.27142393125258+52.68728947124434%29%29%29&input-var=thickness_of_rainfall_amount&network-name=EC&input-freq=daily&only-with-climatology=only-with-climatology&data-format=nc&download-timeseries=Timeseries

However if you add 'Clip time series to filter date range', you get back a NetCDF file with 0 observations. Here is the request:

http://dev_pdp_server/auth/agg/?from-date=1970%2F01%2F01&to-date=2000%2F12%2F31&input-polygon=MULTIPOLYGON%28%28%28-119.27142393125258+52.68728947124434%2C-119.28155103111212+52.62293479212887%2C-119.1170647070386+52.64580799959%2C-119.1340318536963+52.694272675011334%2C-119.27142393125258+52.68728947124434%29%29%29&input-var=thickness_of_rainfall_amount&network-name=EC&input-freq=daily&only-with-climatology=only-with-climatology&data-format=nc&cliptodate=cliptodate&download-timeseries=Timeseries

Tracing it through we can see it's processed in pdp_util:agg:

pdp_util:agg.py:~L152
log.info(clip_dates) -> INFO:pdp_util.agg:(datetime.datetime(1970, 1, 1, 0, 0), datetime.datetime(2000, 12, 31, 0, 0))
log.info(qs) -> INFO:pdp_util.agg:["station_observations.time>='1970-01-01 00:00:00'", "station_observations.time<='2000-12-31 00:00:00'"]

Results in this ini generated by pydap.handlers.pcic:

INFO:pydap.handlers.pcic:database:
  dsn: "postgresql://db_user@db_server/crmp?application_name=pcds"
  id: "obs_time"
  table: "(SELECT obs_time,MAX(CASE WHEN vars_id=428 THEN datum END) as MAX_TEMP,MAX(CASE WHEN vars_id=427 THEN datum END) as MIN_TEMP,MAX(CASE WHEN vars_id=429 THEN datum END) as ONE
_DAY_PRECIPITATION,MAX(CASE WHEN vars_id=430 THEN datum END) as ONE_DAY_RAIN,MAX(CASE WHEN vars_id=431 THEN datum END) as ONE_DAY_SNOW,MAX(CASE WHEN vars_id=432 THEN datum END) as SNO
W_ON_THE_GROUND from obs_raw WHERE (history_id = 1744) AND vars_id IN (428,427,429,430,431,432) GROUP BY obs_time ORDER BY obs_time) as foo"

dataset:

  NC_GLOBAL:
    name: "CRMP/EC"
    owner: "PCIC"
    contact: "Faron Anslow "
    version: 0.2
    station_id: "1170237"
    station_name: "ALBREDA"
    network: "EC"
    latitude: 52.666667
    longitude: -119.200000
    history: "Created dynamically by the Pydap SQL handler, the Pydap PCIC SQL handler, and the PCIC/CRMP database"

sequence:
  name: "station_observations"

time:
  name: "time"
  axis: "T"
  col: "obs_time"
  long_name: "observation time"
  type: String

ONE_DAY_SNOW:
  name: "ONE_DAY_SNOW"
  display_name: "Snowfall Amount"
  long_name: "Daily snow accumulation"
  standard_name: "thickness_of_snowfall_amount"
  units: "cm"
  cell_method: "time: sum"
  col: "ONE_DAY_SNOW"
  axis: "Y"
  missing_value: -9999
  type: Float64

ONE_DAY_RAIN:
  name: "ONE_DAY_RAIN"
  display_name: "Rainfall Amount"
  long_name: "Daily rainfall"
  standard_name: "thickness_of_rainfall_amount"
  units: "mm"
  cell_method: "time: sum"
  col: "ONE_DAY_RAIN"
  axis: "Y"
  missing_value: -9999
  type: Float64

MAX_TEMP:
  name: "MAX_TEMP"
  display_name: "Temperature (Max.)"
  long_name: "Maximum daily temperature"
  standard_name: "air_temperature"
  units: "celsius"
  cell_method: "time: maximum"
  col: "MAX_TEMP"
  axis: "Y"
  missing_value: -9999
  type: Float64

ONE_DAY_PRECIPITATION:
  name: "ONE_DAY_PRECIPITATION"
  display_name: "Precipitation Amount"
  long_name: "Daily precipitation"
  standard_name: "lwe_thickness_of_precipitation_amount"
  units: "mm"
  cell_method: "time: sum"
  col: "ONE_DAY_PRECIPITATION"
  axis: "Y"
  missing_value: -9999
  type: Float64

MIN_TEMP:
  name: "MIN_TEMP"
  display_name: "Temperature (Min.)"
  long_name: "Minimum daily temperature"
  standard_name: "air_temperature"
  units: "celsius"
  cell_method: "time: minimum"
  col: "MIN_TEMP"
  axis: "Y"
  missing_value: -9999
  type: Float64

Resulting in these sql queries being executed on the database:

2014-11-21 09:15:59 PST db_user@db_server(55280) LOG:  statement: BEGIN
2014-11-21 09:15:59 PST db_user@db_server(55280) LOG:  statement: SELECT 1
2014-11-21 09:15:59 PST db_user@db_server(55280) LOG:  statement: SELECT crmp_network_geoserver.network_name AS crmp_network_geoserver_network_name, crmp_network_geoserver.native_id AS crmp_network_geoserver_native_id
    FROM crmp_network_geoserver
    WHERE crmp_network_geoserver.max_obs_time > '1970-01-01T00:00:00'::timestamp AND crmp_network_geoserver.min_obs_time < '2000-12-31T00:00:00'::timestamp AND ST_intersects(ST_GeomFromText('MULTIPOLYGON(((-119.27142393125258 52.68728947124434,-119.28155103111212 52.62293479212887,-119.1170647070386 52.64580799959,-119.1340318536963 52.694272675011334,-119.27142393125258 52.68728947124434)))', 4326), the_geom) AND crmp_network_geoserver.vars LIKE '%thickness_of_rainfall_amount%' AND crmp_network_geoserver.network_name = 'EC' AND crmp_network_geoserver.freq = 'daily' AND (crmp_network_geoserver.vars LIKE '%within%' OR crmp_network_geoserver.vars LIKE '%over%')
2014-11-21 09:15:59 PST [unknown]@ LOG:  connection received: host=db_server port=55346
2014-11-21 09:15:59 PST db_user@db_server(55346) LOG:  connection authorized: user=db_user database=crmp
2014-11-21 09:15:59 PST db_user@db_server(55346) LOG:  statement: BEGIN
2014-11-21 09:15:59 PST db_user@db_server(55346) LOG:  statement: SELECT 1
2014-11-21 09:15:59 PST db_user@db_server(55346) LOG:  statement: SELECT meta_vars.vars_id AS meta_vars_vars_id, meta_vars.net_var_name AS meta_vars_net_var_name, meta_vars.long_description AS meta_vars_long_description, meta_vars.unit AS meta_vars_unit, meta_vars.standard_name AS meta_vars_standard_name, meta_vars.cell_method AS meta_vars_cell_method, meta_vars.precision AS meta_vars_precision, meta_vars.display_name AS meta_vars_display_name, meta_vars.short_name AS meta_vars_short_name, meta_vars.network_id AS meta_vars_network_id
    FROM meta_vars JOIN meta_network ON meta_network.network_id = meta_vars.network_id
    WHERE meta_network.network_name = 'EC' AND NOT (meta_vars.cell_method LIKE '%within%' OR meta_vars.cell_method LIKE '%over%')
2014-11-21 09:15:59 PST [unknown]@ LOG:  connection received: host=db_server port=55347
2014-11-21 09:15:59 PST db_user@db_server(55347) LOG:  connection authorized: user=db_user database=crmp
2014-11-21 09:15:59 PST db_user@db_server(55347) LOG:  statement: BEGIN
2014-11-21 09:15:59 PST db_user@db_server(55347) LOG:  statement: SELECT 1
2014-11-21 09:15:59 PST db_user@db_server(55347) LOG:  statement: SELECT meta_station.station_id AS meta_station_station_id
    FROM meta_station JOIN meta_network ON meta_network.network_id = meta_station.network_id 
    WHERE meta_station.native_id = '1170237' AND meta_network.network_name = 'EC' 
     LIMIT 1
2014-11-21 09:15:59 PST db_user@db_server(55280) LOG:  statement: ROLLBACK
2014-11-21 09:15:59 PST db_user@db_server(55346) LOG:  statement: ROLLBACK
2014-11-21 09:15:59 PST db_user@db_server(55347) LOG:  statement: SELECT meta_station.station_id AS meta_station_station_id 
    FROM meta_station JOIN meta_network ON meta_network.network_id = meta_station.network_id 
    WHERE meta_station.native_id = '1170237' AND meta_network.network_name = 'EC' 
     LIMIT 1
2014-11-21 09:15:59 PST db_user@db_server(55347) LOG:  statement: SELECT query_one_station(1341)
2014-11-21 09:15:59 PST db_user@db_server(55347) LOG:  statement: SELECT meta_station.native_id AS meta_station_native_id, meta_history.station_name AS meta_history_station_name, meta_network.network_name AS meta_network_network_name, ST_AsBinary(meta_history.the_geom) AS meta_history_the_geom 
    FROM meta_station JOIN meta_history ON meta_station.station_id = meta_history.station_id JOIN meta_network ON meta_network.network_id = meta_station.network_id 
    WHERE meta_station.station_id = 1341 
     LIMIT 1
2014-11-21 09:15:59 PST db_user@db_server(55347) LOG:  statement: SELECT ST_Y(ST_GeomFromWKB('\x0101000000cdcccccccccc5dc04529215855554a40'::bytea, 4326)) AS y_1
2014-11-21 09:15:59 PST db_user@db_server(55347) LOG:  statement: SELECT ST_X(ST_GeomFromWKB('\x0101000000cdcccccccccc5dc04529215855554a40'::bytea, 4326)) AS x_1
2014-11-21 09:15:59 PST db_user@db_server(55347) LOG:  statement: SELECT meta_vars.vars_id AS meta_vars_vars_id, meta_vars.net_var_name AS meta_vars_net_var_name, meta_vars.long_description AS meta_vars_long_description, meta_vars.unit AS meta_vars_unit, meta_vars.standard_name AS meta_vars_standard_name, meta_vars.cell_method AS meta_vars_cell_method, meta_vars.precision AS meta_vars_precision, meta_vars.display_name AS meta_vars_display_name, meta_vars.short_name AS meta_vars_short_name, meta_vars.network_id AS meta_vars_network_id 
    FROM meta_vars JOIN vars_per_history_mv ON meta_vars.vars_id = vars_per_history_mv.vars_id JOIN meta_history ON meta_history.history_id = vars_per_history_mv.history_id JOIN meta_station ON meta_station.station_id = meta_history.station_id JOIN meta_network ON meta_network.network_id = meta_station.network_id 
    WHERE meta_station.station_id = 1341 AND NOT (meta_vars.cell_method LIKE '%within%' OR meta_vars.cell_method LIKE '%over%')
2014-11-21 09:15:59 PST db_user@db_server(55347) LOG:  statement: COMMIT
2014-11-21 09:15:59 PST db_user@db_server(55280) LOG:  statement: BEGIN
2014-11-21 09:15:59 PST db_user@db_server(55280) LOG:  statement: SELECT 1
2014-11-21 09:15:59 PST db_user@db_server(55280) LOG:  statement:   
    SELECT ONE_DAY_PRECIPITATION,   
    ONE_DAY_RAIN,   
    ONE_DAY_SNOW,   
    obs_time,   
    MIN_TEMP,   
    MAX_TEMP FROM (  
        SELECT obs_time,  
        MAX(CASE WHEN vars_id=428 THEN datum END) as MAX_TEMP,  
        MAX(CASE WHEN vars_id=427 THEN datum END) as MIN_TEMP,  
        MAX(CASE WHEN vars_id=429 THEN datum END) as ONE_DAY_PRECIPITATION,  
        MAX(CASE WHEN vars_id=430 THEN datum END) as ONE_DAY_RAIN,  
        MAX(CASE WHEN vars_id=431 THEN datum END) as ONE_DAY_SNOW,  
        MAX(CASE WHEN vars_id=432 THEN datum END) as SNOW_ON_THE_GROUND   
        from obs_raw   
        WHERE (history_id = 1744) AND vars_id IN (428,427,429,430,431,432)   
        GROUP BY obs_time ORDER BY obs_time  
    ) as foo LIMIT 1
2014-11-21 09:15:59 PST db_user@db_server(55280) LOG:  statement: COMMIT
2014-11-21 09:15:59 PST db_user@db_server(55346) LOG:  statement: BEGIN
2014-11-21 09:15:59 PST db_user@db_server(55346) LOG:  statement: SELECT 1
2014-11-21 09:15:59 PST db_user@db_server(55346) LOG:  statement:  
    SELECT ONE_DAY_PRECIPITATION,   
    ONE_DAY_RAIN,   
    ONE_DAY_SNOW,   
    obs_time,   
    MIN_TEMP,   
    MAX_TEMP  
    FROM ( 
        SELECT obs_time, 
        MAX(CASE WHEN vars_id=428 THEN datum END) as MAX_TEMP, 
        MAX(CASE WHEN vars_id=427 THEN datum END) as MIN_TEMP, 
        MAX(CASE WHEN vars_id=429 THEN datum END) as ONE_DAY_PRECIPITATION, 
        MAX(CASE WHEN vars_id=430 THEN datum END) as ONE_DAY_RAIN, 
        MAX(CASE WHEN vars_id=431 THEN datum END) as ONE_DAY_SNOW, 
        MAX(CASE WHEN vars_id=432 THEN datum END) as SNOW_ON_THE_GROUND  
        from obs_raw  
        WHERE (history_id = 1744) AND vars_id IN (428,427,429,430,431,432)  
        GROUP BY obs_time ORDER BY obs_time
    ) as foo  
    WHERE (obs_time >= '2000-12-31 00:00:00') AND (obs_time <= '2000-12-31 00:00:00')    
    LIMIT 9223372036854775807 OFFSET 0
2014-11-21 09:15:59 PST db_user@db_server(55346) LOG:  statement: COMMIT
2014-11-21 09:15:59 PST db_user@db_server(55347) LOG:  statement: BEGIN
2014-11-21 09:15:59 PST db_user@db_server(55347) LOG:  statement: SELECT 1
2014-11-21 09:15:59 PST db_user@db_server(55347) LOG:  statement: SELECT obs_time 
    FROM ( 
        SELECT obs_time,MAX(CASE WHEN vars_id=428 THEN datum END) as MAX_TEMP, 
        MAX(CASE WHEN vars_id=427 THEN datum END) as MIN_TEMP, 
        MAX(CASE WHEN vars_id=429 THEN datum END) as ONE_DAY_PRECIPITATION, 
        MAX(CASE WHEN vars_id=430 THEN datum END) as ONE_DAY_RAIN, 
        MAX(CASE WHEN vars_id=431 THEN datum END) as ONE_DAY_SNOW, 
        MAX(CASE WHEN vars_id=432 THEN datum END) as SNOW_ON_THE_GROUND from obs_raw  
        WHERE (history_id = 1744)  
        AND vars_id IN (428,427,429,430,431,432)  
        GROUP BY obs_time ORDER BY obs_time
    ) as foo  
    WHERE (obs_time >= '2000-12-31 00:00:00') AND (obs_time <= '2000-12-31 00:00:00')   
    LIMIT 9223372036854775807 OFFSET 0
2014-11-21 09:15:59 PST db_user@db_server(55347) LOG:  statement: COMMIT

The issues is the clause: WHERE (obs_time >= '2000-12-31 00:00:00') AND (obs_time <= '2000-12-31 00:00:00')

jameshiebert commented 9 years ago

This issue was moved to pacificclimate/pydap.handlers.sql#1