Postgis and EXPRESSION

[Umberto Nicoletti]

Does the error message also include the query that was sent to postgres?
If yes, could you post it to the list?

Thanks,
Umberto

On 2/23/06, Stefan Schwarzer <stefan.schwarzer at grid.unep.ch> wrote:
> 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
>