I rewrote the sql view to not need any special characters:
with pur as (select pur.comtrs,
sum(pur.lbs_prd_used * %sumvar% / 100) as lbs
from pur.npsdrift_sum as pur
%aijoin%
%inertjoin%
where pur.comtrs is not null
and pur.applic_dt between date '%startdate%' and date
'%enddate%'
%aiprodwhere%
%inertprodwhere%
%chemcodes%
%prodnos%
%inertcodes%
group by pur.comtrs),
bp as (select * from pur.colorize(array(select lbs from pur),
%numcolors%, '%classification%'::text, '%colormap%'::text))
select mtrs.gid as gid,
pur.comtrs as comtrs,
pur.lbs as lbs,
bp.color as color,
mtrs.geom as geom
from pur left outer join boundaries.mtrs on pur.comtrs = mtrs.co_mtrs,
bp
where pur.lbs >= bp.minbp and pur.lbs < bp.maxbp
Using these view parameters:
sumvar:pc.prodchem_pct;startdate:20140101;enddate:20141231;numcolors:3
;classification:quantile;colormap:Reds;aijoin:, pur.dpr_prod_chem as pc
;aiprodwhere: and pc.prodno = pur.prodno ;chemcodes: and ( pc.chem_code =
253 )
Doing a simple replace, I get the following SQL which runs fine on the
database:
with pur as (select pur.comtrs,
sum(pur.lbs_prd_used * pc.prodchem_pct / 100) as lbs
from pur.npsdrift_sum as pur
, pur.dpr_prod_chem as pc
where pur.comtrs is not null
and pur.applic_dt between date '20140101' and date '20141231'
and pc.prodno = pur.prodno
and ( pc.chem_code = 253 )
group by pur.comtrs),
bp as (select * from pur.colorize(array(select lbs from pur), 3,
'quantile'::text, 'Reds'::text))
select mtrs.gid as gid,
pur.comtrs as comtrs,
pur.lbs as lbs,
bp.color as color,
mtrs.geom as geom
from pur left outer join boundaries.mtrs on pur.comtrs = mtrs.co_mtrs,
bp
where pur.lbs >= bp.minbp and pur.lbs < bp.maxbp
Yet it throws the same error as I reported above:
"org.geoserver.platform.ServiceException:
1 layers requested, but found 2 view params specified" and never seems to
get to the database."
Using the following parameter string works fine on geoserver.
sumvar:100;startdate:20140101;enddate:20141231;numcolors:6
;classification:quantile;colormap:Reds;prodnos: and ( pur.prodno = 30369 )
I'm at a loss of what could be different between the two on geoserver's end.
Thanks for any insight you can share,
--Christopher
On Fri, Feb 17, 2017 at 10:47 AM, Christopher DeMars <[email protected]>
wrote:
> Dear all,
>
> Another issue: when I replace the summation parameter from a simple column
> name "sum(pur.lbs_prd_used)" to an equation "sum(pur.lbs_prd_used *
> pc.prodchem_pct / 100)", I get the following error:
>
> 2017-02-17 18:24:29,758 DEBUG [org.geotools.util] - CRSConverter:
> Converting object of class java.lang.String to org.opengis.referencing.crs.
> CoordinateReferenceSystem
> 2017-02-17 18:24:29,759 DEBUG [org.geotools.util] -
> InterpolationConverterFactory can be applied from Strings to Interpolation
> only.
> 2017-02-17 18:24:29,759 DEBUG [org.geotools.util] - CRSConverterFactory
> can be applied from Strings to CRS only.
> 2017-02-17 18:24:29,759 DEBUG [org.geotools.util] -
> InterpolationConverterFactory can be applied from Strings to Interpolation
> only.
> 2017-02-17 18:24:29,759 DEBUG [org.geotools.util] - CRSConverterFactory
> can be applied from Strings to CRS only.
> 2017-02-17 18:24:29,760 DEBUG [org.geotools.styling] - number of fts set 1
> 2017-02-17 18:24:29,760 ERROR [org.geoserver.ows] -
> org.geoserver.platform.ServiceException: 1 layers requested, but found 2
> view params specified.
> at org.geoserver.wms.map.GetMapKvpRequestReader.read(
> GetMapKvpRequestReader.java:486)
> at org.geoserver.wms.map.GetMapKvpRequestReader.read(
> GetMapKvpRequestReader.java:84)
> at org.geoserver.ows.Dispatcher.parseRequestKVP(Dispatcher.
> java:1488)
> at org.geoserver.ows.Dispatcher.dispatch(Dispatcher.java:674)
> at org.geoserver.ows.Dispatcher.handleRequestInternal(
> Dispatcher.java:258)
> ...
>
> When running the raw SQL with the parameters replaced by what the client
> is sending, everything works fine on the database. There is no query
> reported in the log file, so I think it is failing before it hits the
> database.
>
> Are there special characters like space, *, or /, that need to be escaped?
> There are minimal changes to the query other than the summation variable,
> which is why I'm looking there.
>
> Thank you for any guidance you can give,
> --Christopher
>
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
