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
------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
