Re: [UMN_MAPSERVER-USERS] Postgis and EXPRESSION

[Stefan Schwarzer]

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]