[mapserver-users] PostGIS query errors

[Kralidis, Tom (EC)]

> -----Original Message-----
> From: Andy Colson [mailto:andy at squeakycode.net]
> Sent: 09 November 2016 17:43
> To: Kralidis, Tom (EC); mapserver-users at lists.osgeo.org
> Subject: Re: [mapserver-users] PostGIS query errors
> 
> On 11/9/2016 3:30 PM, Kralidis, Tom (EC) wrote:
> > Hi all: using 7.0.2 against a PostgreSQL 9.3/PostGIS 2.2 instance we're getting
> query errors.
> >
> > I've posted a Gist at [1] to help in reporting.  Note that this functionality used
> to work in 6.4.x.
> >
> > From the migration guides it doesn't look like much has changed w.r.t.
> > PostGIS syntax in the mapfile.  Any idea what could be going on here?
> > It looks like the query that is constructed by mappostgis.c is something like:
> >
> > select
> >
> "stn_nam","avg_wnd_dir_10m_mt58_60","avg_wnd_spd_10m_mt58_60_symb
> ol","
> > air_temp","stn_pres","mslp","dwpt_temp","rel_hum","observation_qa_summ
> >
> ary","observation_qa_problem_summary",encode(ST_AsBinary(ST_Force2D("t
> > he_geom"),'NDR'),'hex') as geom,"data_payload_id" from (select * from
> > data_mart_ca_live order by instance_datetime desc) as subquery where
> > the_geom && ST_GeomFromText('POLYGON((-127.881959628204
> > 63.7547758989546,-127.881959628204 70.5833900726923,-
> 103.751686743445
> > 70.5833900726923,-103.751686743445 63.7547758989546,-
> 127.881959628204
> > 63.7547758989546))',4326) and ()
> >
> > Note the trailing "()".  When I remove the "()" and try direct in psql, the query
> works.
> >
> > Any idea what could be going on here?
> >
> > Thanks
> >
> > ..Tom
> >
> > [1]
> > https://gist.github.com/tomkralidis/26cb7214e282c144ba61598116d458e7
> > _______________________________________________
> > mapserver-users mailing list
> > mapserver-users at lists.osgeo.org
> > http://lists.osgeo.org/mailman/listinfo/mapserver-users
> >
> 
> It looks to me, from the code mappostgis.c ~line 2018, in the
> msPostGISBuildSQLWhere function, that you have either a FILTER or
> NATIVE_FILTER being added.  I don't see it in the gist you posted though.  Is it
> applied later some how?  I'd bet it's also empty.
> 

Indeed, it turns out that Python MapScript was doing a setFilter as part of the
workflow (since changed to setProcessingKey).

Thanks

..Tom


> There is a comment about RFC91 too:
> http://mapserver.org/id/development/rfc/ms-rfc-91.html
> 
> any of that apply to you?
> 
> -Andy
>