Hi Andrea,
what do you mean with "deciding which one to use"? AFAIK it has to use
both, (almost) all the time.
This is the query found in GeoServer logs (LOCATION is the point
geometry column with a spatial index):
SELECT ID,TIME,LOCATION
FROM lightning_strikes
WHERE (
TIME BETWEEN ? AND ?
AND SDO_FILTER(LOCATION, ?, 'mask=anyinteract querytype=WINDOW') =
'TRUE')
)
Let's say I want just 1 hour worth of lightning strikes but from almost
all of the central Europe (so the BBOX is not covering the whole dataset
but covers 90% of it).
The 'TIME BETWEEN' clause quickly filters 3M rows to let's say 10K
lightnings. However, some of them lie outside of the BBOX. So it has to
filter them out. Unfortunately, there are millions of other records in
the same BBOX, so the spatial index finds them all and then the database
engine has to make intersection of the two sets. Since one of them is
huge, it takes time. And it will get worse over time.
OK, maybe I see your point now. The DB engine could just check each row
from the smaller set if it is inside the BBOX, without using the spatial
index. I wonder if PostGIS can do it. I'll give it a try.
Thank you
On 21. 6. 2016 17:47, Andrea Aime wrote:
Hi Peter,
from my experience Oracle is probably the cause, given a spatial and a
regular index
it has sometimes issues deciding which one to use and ends up using
the wrong one.
You should try PostGis, with proper indexes and statistics setup, with
"only" 3 million records
it should respond fast.
Worth investigating at the very least, let us know how it goes.
Cheers
Andrea
On Tue, Jun 21, 2016 at 4:22 PM, Peter Kovac
<peter.ko...@microstep-mis.com <mailto:peter.ko...@microstep-mis.com>>
wrote:
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
peter.ko...@microstep-mis.com <mailto:peter.ko...@microstep-mis.com>
------------------------------------------------------------------------------
Attend Shape: An AT&T Tech Expo July 15-16. Meet us at AT&T Park
in San
Francisco, CA to explore cutting-edge tech and listen to tech
luminaries
present their vision of the future. This family event has
something for
everyone, including kids. Get more information and register today.
http://sdm.link/attshape
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
<mailto:Geoserver-users@lists.sourceforge.net>
https://lists.sourceforge.net/lists/listinfo/geoserver-users
--
==
GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.
==
Ing. Andrea Aime
@geowolf
Technical Lead
GeoSolutions S.A.S.
Via di Montramito 3/A
55054 Massarosa (LU)
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549
http://www.geo-solutions.it
http://twitter.com/geosolutions_it
*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*
Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate.
Il loro utilizzo è consentito esclusivamente al destinatario del
messaggio, per le finalità indicate nel messaggio stesso. Qualora
riceviate questo messaggio senza esserne il destinatario, Vi preghiamo
cortesemente di darcene notizia via e-mail e di procedere alla
distruzione del messaggio stesso, cancellandolo dal Vostro sistema.
Conservare il messaggio stesso, divulgarlo anche in parte,
distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità
diverse, costituisce comportamento contrario ai principi dettati dal
D.Lgs. 196/2003.
The information in this message and/or attachments, is intended solely
for the attention and use of the named addressee(s) and may be
confidential or proprietary in nature or covered by the provisions of
privacy act (Legislative Decree June, 30 2003, no.196 - Italy's New
Data Protection Code).Any use not in accord with its purpose, any
disclosure, reproduction, copying, distribution, or either
dissemination, either whole or partial, is strictly forbidden except
previous formal approval of the named addressee(s). If you are not the
intended recipient, please contact immediately the sender by
telephone, fax or e-mail and delete the information in this message
that has been received in error. The sender does not give any warranty
or accept liability as the content, accuracy or completeness of sent
messages and accepts no responsibility for changes made after they
were sent or for other risks which arise as a result of e-mail
transmission, viruses, etc.
-------------------------------------------------------
--
Peter Kovac
IMS Programmer
MicroStep-MIS
peter.ko...@microstep-mis.com
------------------------------------------------------------------------------
Attend Shape: An AT&T Tech Expo July 15-16. Meet us at AT&T Park in San
Francisco, CA to explore cutting-edge tech and listen to tech luminaries
present their vision of the future. This family event has something for
everyone, including kids. Get more information and register today.
http://sdm.link/attshape
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
