[mapserver-users] Mapserver 7.0.5, PostGIS and Query Errors

[Jörg Thomsen (WhereGroup)]

Hi,

had the same problem a few weeks ago. The solution was to fix the FILTER
or EXPRESSION within the classes. I don't remember exactly...

Jörg




Am 07.06.2017 um 08:45 schrieb James Colannino:
> Hi everyone,
> 
> I have a problem that's got me stumped.
> 
> I have the following layer defined in a mapfile:
> 
> LAYER
> 
>     NAME "aggregates"
>     TYPE POLYGON
>     CONNECTIONTYPE POSTGIS
>     CONNECTION "host=localhost port=5432 dbname=*** user=*** password=***"
>     DATA "geom FROM (SELECT b.geom AS geom, a.peril_id, a.portfolio_id,
> a.tiv, a.thematic_tiv, a.aggregates_id FROM schema.aggregates a INNER
> JOIN gisdata.adm_1 b on a.gid = b.gid) AS c using SRID=4326 using unique
> aggregates_id"
> 
>     PROJECTION
>         "proj=longlat"
>         "ellps=WGS84"
>         "datum=WGS84"
>         "no_defs"
>     END
> 
> END
> 
> This works perfectly on Mapserver 6.4, but on 7.0.5 it's failing (for
> what it's worth, I compiled 7.0.5 myself because my OS didn't have
> binary packages for it that supported php5-mapscript.) I turned on
> debugging and watched what kind of errors I was getting whenever I made
> tile requests. Here's what I found:
> 
> [Wed Jun  7 03:42:43 2017].502983 msDrawMap(): rendering using
> outputformat named png (AGG/PNG).
> [Wed Jun  7 03:42:43 2017].503018 msDrawMap(): WMS/WFS set-up and query,
> 0.000s
> [Wed Jun  7 03:42:43 2017].523359 msPostGISLayerWhichShapes(): Error
> (ERROR:  syntax error at or near ")"
> LINE 1: ...2300166285,129.462890606979 36.6292300166285))',4326) and ()
>                                                               ^
> ) executing query: select
> "thematic_tiv",encode(ST_AsBinary(ST_Force2D("geom"),'NDR'),'hex') as
> geom,"aggregates_id" from (SELECT b.geom AS geom, a.peril_id,
> a.portfolio_id, a.tiv, a.thematic_tiv, a.aggregates_id FROM
> portfolios.aggregates a INNER JOIN gisdata.adm_1 b on a.gid = b.gid) AS
> c where geom && ST_GeomFromText('POLYGON((129.462890606979
> 36.6292300166285,129.462890606979 68.6423395678331,179.999999974944
> 68.6423395678331,179.999999974944 36.6292300166285,129.462890606979
> 36.6292300166285))',4326) and ()
> [Wed Jun  7 03:42:43 2017].523377 msPostGISLayerWhichShapes(): Query
> error. Error executing query. Check server logs
> [Wed Jun  7 03:42:43 2017].523434 msDrawMap(): Image handling error.
> Failed to draw layer named 'area_aggregates_tiv'.
> [Wed Jun  7 03:42:43 2017].523927 msFreeMap(): freeing map at
> 0x560aa573b6c0.
> 
> That "and ()" at the end of the query causing the syntax error is very
> strange. It's like it's adding on a blank filter. I tried making the
> query as simple as possible, so I created another table that contained
> all the required information and changed the DATA line to:
> 
> DATA "geom from portfolios.area_aggregates_join using SRID=4326 using
> unique aggregates_id"
> 
> However, I still got the same type of error. I'm completely stumped.
> Does anyone have an idea what could be happening? I've spent the past 4
> hours Googling with no success.
> 
> Thank you!
> 
> 
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/mapserver-users
> 


Viele Grüße,
Jörg Thomsen

-- 

**********************************
AGIT 2017
5.-7. Juli 2017 in Salzburg
Besuchen Sie uns an Stand Nr. 31
www.agit.at
**********************************

Jörg Thomsen
WhereGroup GmbH & Co. KG
Gillweg 3
14193 Berlin
Germany

Fon: +49 (0)30 / 5130 278 74
Fax: +49 (0)30 / 89 09 53 21

joerg.thomsen at wheregroup.com
www.wheregroup.com
Amtsgericht Bonn, HRA 6788
-------------------------------
Komplementärin:
WhereGroup Verwaltungs GmbH
vertreten durch:
Olaf Knopp, Peter Stamm
-------------------------------
Folgen Sie der WhereGroup auf twitter: http://twitter.com/WhereGroup_com