[Geoserver-users] How to serve large spatio-temporal dataset with GeoServer?

[Peter K. <pet...@mi...>]

Dear GeoServer users,

I have trouble figuring out fast and scalable way to serve lightning 
data via GeoServer.

My dataset consists of several million points spread over central Europe 
spanning several years. I have approximately 3M lightning strikes right 
now and it's just a fraction of what I'll have to handle ultimately. I'm 
using Oracle Locator database with both spatial and normal indexes and 
while it has a few quirks it works reasonably well when the amount of 
lightnings is small (i.e. in thousands or tens of thousands).

While my WMS client will never allow to show more than 2 hours worth of 
data (that's small amount of lightning strikes) there are particular WMS 
requests which take a very long time: when I want to see a "bigger 
picture" of all lightning strikes in central Europe during a specified 
short period of time.

The core of the problem is my data have both spatial and temporal 
dimension and there is no spatio-temporal index in Oracle Locator. So 
even if regular index on the time dimension can limit the number of 
features to a few thousands in the blink of an eye, the spatial index 
over the point geometry column won't help much (since the BBOX in the 
request covers the whole area anyway) and is in fact doing harm. The 
query found in GeoServer logs runs really fast If I omit the spatial 
index clause in such case (just a few hundred ms compared to 6-7 seconds 
for the full query with SDO_FILTER function call). Another bad thing is 
my colleagues predict that the performance will get worse with more data 
in the table once it won't fit into RAM and the database engine will 
have to use hard drives for processing.

The performance improves rapidly as I zoom to larger scales (smaller 
areas), where the spatial index selects just a small subset of data. 
However, I'd like to be able to serve the whole central Europe quickly, too.

One possible solution is to add the time dimension to my spatial index 
(so it's 3D instead of 2D), but I'm afraid GeoServer won't be able to 
retrieve data from such index (it won't be EPSG:3857 geometry anymore).

Another solution from an Oracle forum suggests using partitioning over 
time and have separate spatial index for each partition, but that 
requires expensive Oracle Enterprise license (which was not budgeted in 
the project of course) and it's just dividing the problem by a constant 
factor anyway.

So, since I'm out of my own ideas, how would you handle this situation? 
What other tools or formats are useful? Is the Postgres/PostGIS combo 
better at serving large-scale spatio-temporal datasets (with regard to 
GeoServer)?


Many thanks for any help!

-- 
Peter Kovac
IMS Programmer
MicroStep-MIS
pet...@mi...