[Geomoose-users] Query.php and data types

[Johan Forsman]

All:

On GM 2.2, I am having some difficulty obtaining results from query.php using PostGIS as the datastore.

There are 2 fields from 1 table being searched. The user selects the analyte_code values from a dropdown and sample_collection_date bracketed values are typed in.

The query executes but returns no results.

I found the debug lines in the query.php source and uncommented those lines which revealed the FILTER string as:

analyte_code = 1005 and sample_collection_date >= 2009-12-31 and sample_collection_date <= 2011-07-01

If I isolate the values individually it appears that the values are passed to the database as integers, which results in data-type errors from the database.

For analyte_code:
Operator does not exist: character = integer.

And for sample_collection_date:
Operator does not exist: timestamp without time zone >= integer

Now that last error looks a little off; is the postqresql trying to interpret the string and do math?

The data are in fact stored in the database as "character" and "timestamp without time zone", respectively.

>From the SQL console I can issue

select * 
from geodata.webmaps_mcl_violations 
where 
	analyte_code = '1005' and 
	sample_collection_date >= '2009-12-31' and 
	sample_collection_date <= '2011-07-01';

which returns the expected results.

The hint from the sql console when the error is generated from the filter string is that I might need to add explicit type casts. If this is indeed the case, where do I add these? I experimented by adding single quotes in the query definition in the mapbook but those were escaped to \' which the database didn't care much for.

Please advise.

Thanks!

----------------------
Johan Forsman
Geologist
Safe Drinking Water Program
Louisiana Department of Health and Hospitals