I am trying the following: I have a large table (~2 mill. rows) of the following design
Table "public"."cells_ids_and_counts" Column | Type ----------------------------------------- src_cellid | varchar tgt_cellid | varchar timestamp | timestamp count | int4 the_geom | geometry
I let the user choose a number of ids in my web application. Based on this selection a new map shall be created holding only the rows that contain these ids. In the progress of this selection the count must be aggregated based on the timestamp and the target cell id like so:
select timestamp,tgt_cell_id, sum(count) as count, max(the_geom) as the_geom FROM public.cells_ids_and_counts WHERE src_cellid = 'id0' OR src_cellid = 'id1' [...] GROUP BY tgt_cell_id, timestamp
My fist approach was the following: Create a view in Geoserver with the following query:
select timestamp,tgt_cell_id, sum(count) as count, max(the_geom) as the_geom FROM public.cells_ids_and_counts WHERE %SRC_CELLS% GROUP BY tgt_cell_id, timestamp
is a URL parameter, its value being:
SRC_CELLS:src_cellid = 'id0' OR src_cellid = 'id1'
This was not working though and I assume that it is only possible to hand over values not variables AND values through the
procedure in Geoserver due to SQL injection concerns. Is that correct?
So my second approach was to write a servlet that would create a new view/table for each user selection in the database on-the-fly and let Geoserver display this table. And here are now my questions:
I would like to make this result table unique to each user's session so that no other user query will be able to overwrite another user's result table with his or her selection. If so, how would I then have to design my Geoserver layer? The database table will ideally have a session id attached to its name, so the name will always be unique. In this way I will have to tell my OpenLayers client and Geoserver the name of this table to create a layer from it. Would I do this with the REST services in Geoserver? Or am I thinking to complicated? Is there a best practice in this situation when it comes to danymically changing tables?
Thanks in advance for any suggestions