Postgis and EXPRESSION

[Stefan Schwarzer]

Hmm, tried this too now. Not that it made a difference, but it gave  
me an idea. It sounds really stupid, but although I used "character  
varying" as the field definition I had the impression that something  
was wrong within the fields. So, I found out that there were empty  
spaces behind the field values. I have no idea how they got there.....

Relieved, but ashamed, it refreshed the views... and.... still, it  
did not work...

So, meanwhile I have this definition:

	LAYER
		NAME bg_shape
		CONNECTIONTYPE postgis
		CONNECTION 'user=xxx password=xxx dbname=xxx' 			
		TYPE POLYGON
		DATA 'the_geom FROM countries_regions_view USING UNIQUE id USING  
srid=-1'
		CLASSITEM 'reg_name'
		CLASS
			NAME ''
		        EXPRESSION ([reg_name] ne 'Africa')
			OUTLINECOLOR 230 230 230
			COLOR 230 230 230
		END
	END

and attached is a screenshot of the view.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: screenshot62.png
Type: image/png
Size: 43821 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20060223/1fb25040/attachment.png>
-------------- next part --------------





> 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:	MAPSERVER-USERS at LISTS.UMN.EDU
> 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.
>> Cjohnson at refractions.net
>
>
>
> !DSPAM:43fd5c91219451348188260!