Sorry for pestering the list with my problems, but in case anyone else has
a similar enough problem that google sends them to this thread I should
post when things go right.
>From this, it seems that the comma is a problem:
http://stackoverflow.com/questions/29746117/geoserver-sql-view-parameters-messing-up-postgis-function-calls
"Ok, in case anybody happens to have the same question, after looking
through the logs as suggested I found the culprit. Geoserver complained
about having two parameters, meaning the comma inside the where clause had
to be escaped. After looking at others with similar problems, I found that
you have to escape the comma three times, "\ \ \," essentially making sure
that a "\," will remain escaped after you send the request through
javascript."
After replacing "," with "\\\," in my javascript, everything works. Joy!
On to the next bug!
Thanks again,
--Christopher
On Fri, Feb 17, 2017 at 11:20 AM, Christopher DeMars <[email protected]>
wrote:
> 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(GetMapKvpR
>> equestReader.java:486)
>> at org.geoserver.wms.map.GetMapKvpRequestReader.read(GetMapKvpR
>> equestReader.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(Dispatche
>> r.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
