Hello GeoServer Community,
I want to configure a layer with the dimension time where start_time and end_time are SQL View Parameters. Unfortunately the parameter seems not to be replaced in SQL-Query.
This is the definition of the SQL-View:
SELECT DISTINCT ON (object_tracking_uid) objectid, cc_guid, object_tracking_uid, sensor_id, sensor_vendor_lut, sensor_model_lut, sensor_type_lut, log_time, log_time_end, json_store, object_class, classification, fov_intersection_list, alarm_zones_intersection_list, x_coord, y_coord, z_coord, epsg_srid, shape, username, user_is_editor, editkey, to_char(log_time , ‘YYYY-MM-DD’) as log_date_label, to_char(log_time , ‘HH24:MI:SS’) as log_time_label, round(cast(move_distance as numeric), 2) as move_distance, round(cast(move_speed as numeric), 2) as move_speed, ‘%start_time%’::timestamp as start_time, ‘%end_time%’::timestamp as end_time FROM cc_object_tracking_object_user_timeline_movement WHERE log_time >= ‘%start_time%’ AND log_time_end <= ‘%end_time%’ ORDER BY object_tracking_uid, log_time_end DESC;
See the following 2 screenshots for some details of the configuration:
As you can see from the logfile at runtime the parameters are only replaced in the where part of the vtable but not in the SQL-View. There are still the default values I have defined:
05 041 16:41:37 DEBUG [geotools.jdbc] - SELECT “object_tracking_uid”,“log_time_label”,“log_date_label”,encode(ST_AsBinary(ST_Force2D(“shape”)), ‘base64’) as “shape”,“object_class”,“classification” FROM (SELECT DISTINCT ON (object_tracking_uid) objectid, cc_guid, object_tracking_uid, sensor_id, sensor_vendor_lut, sensor_model_lut, sensor_type_lut, log_time, log_time_end, json_store, object_class, classification, fov_intersection_list, alarm_zones_intersection_list, x_coord, y_coord, z_coord, epsg_srid, shape, username, user_is_editor, editkey, to_char(log_time , ‘YYYY-MM-DD’) as log_date_label, to_char(log_time , ‘HH24:MI:SS’) as log_time_label, round(cast(move_distance as numeric), 2) as move_distance, round(cast(move_speed as numeric), 2) as move_speed, ‘2000-01-01’::timestamp as start_time, ‘2100-01-01’::timestamp as end_time FROM cc_object_tracking_object_user_timeline_movement WHERE log_time >= ‘2000-01-01’ AND log_time_end <= ‘2100-01-01’ ORDER BY object_tracking_uid, log_time_end DESC
) as “vtable” WHERE (“start_time” <= ‘2025-02-01 10:04:01.0’ AND “start_time” IS NOT NULL AND “end_time” >= ‘2025-02-01 10:00:00.0’ AND “end_time” IS NOT NULL AND “shape” && ST_GeomFromText(‘POLYGON ((16.347757458147306 48.19814816874566, 16.347757458147306 48.198180321992965, 16.34784077426483 48.198180321992965, 16.34784077426483 48.19814816874566, 16.347757458147306 48.19814816874566))’, -1) AND ((“object_class” = ‘Human’ AND “object_class” IS NOT NULL AND “classification” = ‘intruder’ AND “classification” IS NOT NULL ) OR (“object_class” = ‘Human’ AND “object_class” IS NOT NULL AND (“classification” IS NULL OR (“classification” = ‘unknown’ AND “classification” IS NOT NULL ) OR (“classification” != ‘friend’ AND “classification” IS NOT NULL AND “classification” != ‘intruder’))) OR (“object_class” = ‘Human’ AND “object_class” IS NOT NULL AND “classification” = ‘friend’ AND “classification” IS NOT NULL ) OR (“object_class” = ‘Vehicle’ AND “object_class” IS NOT NULL AND “classification” = ‘intruder’ AND “classification” IS NOT NULL ) OR (“object_class” = ‘Vehicle’ AND “object_class” IS NOT NULL AND (“classification” IS NULL OR (“classification” = ‘unknown’ AND “classification” IS NOT NULL ) OR (“classification” != ‘friend’ AND “classification” IS NOT NULL AND “classification” != ‘intruder’))) OR (“object_class” = ‘Vehicle’ AND “object_class” IS NOT NULL AND “classification” = ‘friend’ AND “classification” IS NOT NULL ) OR “object_class” IS NULL OR (“object_class” = ‘unknown’ AND “object_class” IS NOT NULL ) OR (“object_class” != ‘Vehicle’ AND “object_class” IS NOT NULL AND “object_class” != ‘Human’)))
Can anyone help me to solve this special topic?
Thanks and all the best,
Michael