Hi list,
has anyone any experience in using Oracles analytics functions (https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#SQLRF06174) e.g. RATIO_TO_REPORT, like in this SQL:
select round(ratio_to_report(d.n) over ()*100,2) as p, d.n, d.kvadratid, kn.geometri
from dkn100km kn,
(
select count(*) as n, kvadratnet.id
from kvadratnet
where kvadratnet.id like '100km%'
and kvadratnet.slags = 1
group by kvadratnet.id
) d
where kn.kn100kmdk = d.id
and mdsys.sdo_filter(
kn.geometri, mdsys.sdo_geometry(
2003, 25832, NULL, mdsys.SDO_ELEM_INFO_ARRAY(1, 1003, 1),
mdsys.sdo_ordinate_array(510000.0,6200000.0,550000.0,6300000.0)
),
'querytype=window') = 'TRUE';
returns e.g.:
23,19 4428 100km_63_5 MDSYS.SDO_GEOMETRY(2003,25832,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(500000,6300000,600000,6300000,600000,6400000,500000,6400000,500000,6300000))
38,6 7372 100km_61_5 MDSYS.SDO_GEOMETRY(2003,25832,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(500000,6100000,600000,6100000,600000,6200000,500000,6200000,500000,6100000))
38,21 7297 100km_62_5 MDSYS.SDO_GEOMETRY(2003,25832,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(500000,6200000,600000,6200000,600000,6300000,500000,6300000,500000,6200000))
I'd like to style the tiles represented by the geometry column, according to the percentage ("ratio") in the first column.
I have tried building an SQL view in geoserver, but Geoserver seems to use the view as select * from (<the sql view>) [as] vtable, the ratio_to_report is calculated always over the entire dataset, whereas I'd like it to be calculated over the present bounding box - which the SQL above does correctly.
Any experiences? Alternative approaches?
Best Regards,
Julian