Postgis and EXPRESSION

[Gregory S. Williamson]

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!