Stefan, I am not sure if this could work for you, but you could try creating a view or a function (if parameters are needed) and then call that from MSS. I've used that to avoid constructs that MSS didn't like, or to do other behind-the-scenes processing.
Plus diagnosing the postgres sql errors is sometimes easier than figuring out what MSS is doing, depending on how it is configured. Greg Williamson DBA GlobeXplorer LLC -----Original Message----- From: UMN MapServer Users List on behalf of Stefan Schwarzer Sent: Wed 2/22/2006 10:55 PM To: [email protected] Cc: Subject: Re: [UMN_MAPSERVER-USERS] Postgis and EXPRESSION Thanks for the suggestions. Unfortunately neither one of them worked. 1) The CLASSITEM seems not to be the problem. At least when I took it away it still didn't use the the EXPRESSION. 2) For the NATURAL JOIN I get an error message. (Postgresql reports the error as 'ERROR: syntax error at or near "ON" at character 251 '). But the LEFT JOIN seems to work and when I run the query directly in the database the result looks correct. I tried to play a bit around, but without success... Just to be sure that I understand it correctly: the EXPRESSION parameter references to the alias in the SELECT statement, no? So if I have regions_view.name AS name then my EXPRESSION ([name] ne 'Africa') is based on the alias... Gush, I am really stuck there... Stef On Feb 22, 2006, at 10:14 PM, Clint Johnson wrote: > I believe your script is failing at the DATA clause -- or because > there exists no data within your tables that is join-able. > > *[Malformed DATA clause]* > I've run into a similar problem myself before. > > I am pretty sure your problem is with your DATA statement. In fact > it may be throwing an exception that you haven't been able to see > (rather than ignoring the expression). > > See the advanced-usage at: http://postgis.refractions.net/docs/ > ch04.html#id2789268 > Note that the SQL used within the DATA tag is "pseudo-SQL" > > Do not use the WHERE clause to perform your join; instead, use the > NATURAL JOIN operator. Instead, only use the WHERE clause to > filter upon items as you would with the FILTER tag (such as > regions_view.name = 'foo'). I believe the FILTER and WHERE clauses > to be coupled (which further complicates matters when doing a > complex join in your DATA clause). > > Therefore, try this and let me know if it works: > > DATA "the_geom FROM (SELECT countries.the_geom AS the_geom, > regions_view.name AS name, countries.oid AS oid FROM countries > NATURAL JOIN regions_view ON countries.geo_region = > regions_view.id) AS foo using unique oid using srid=-1" > > *[Nothing to join]* > Perhaps there is nothing to join -- in that case try using LEFT > JOIN instead of the NATURAL JOIN > > Clint > > > Stefan Schwarzer wrote: >> Hi, >> >> I migrated from mysql to postgres/postgis and update now all my >> map-queries. But I have a problem getting it working. I wonder if >> this is a major problem or some minor issue. The doc says : >> * >> * >> *"**Logical expressions allow you to build fairly complex tests >> based on one or more attributes and therefore are only available >> with shapefiles"* >> >> So, i would almost presume that using EXPRESSIONS is not possible >> via postgis. But I can't imagine that this is true... In the >> moment it runs the query but with no result - meaning that it >> "ignores" the epression. I couldn't find anything similar in the >> archives either... >> >> >> LAYER >> NAME bg_shape >> CONNECTIONTYPE postgis >> CONNECTION 'user=xxx password=xxx dbname=xxx' >> TYPE POLYGON >> DATA 'the_geom FROM (SELECT countries.the_geom AS the_geom, >> regions_view.name AS name, countries.oid AS oid FROM countries, >> regions_view WHERE countries.geo_region = regions_view.id) AS foo >> USING UNIQUE oid USING srid=-1' >> CLASSITEM 'name' >> CLASS >> NAME '' EXPRESSION ([name] ne 'Africa') >> OUTLINECOLOR 230 230 230 >> COLOR 230 230 230 >> END >> END # LAYER >> >> >> Thanks for any help! >> >> Stef >> > > > -- > Clint Johnson > Refractions Research Inc. > [EMAIL PROTECTED] !DSPAM:43fd5c91219451348188260!
