Filter layer with MS/SQL based attribute ? b=SMftptmlbsr0DT00M4m7daF/51hDTSm35WHy/94yZIRDphDM0FPGm2tkV1LmtdgbMd5Vk1cBJixCTDeM2f2H/VzIAA9atB3PtyMnroddAltBA7JFses2z3tneVcXv7BSmrrfwOvY116jp4A09d2p0c6uc3Z20p/gGI39T

[Lars V. Nielsen (HVM)]

Hi Frank,

Thanks for your response. The reason I didn't provide more detail was that I didn't know which way I was heading, and even how to
proceed :-)

I got a little further by myself before calling it a day yesterday. I did get some sort of OGR JOIN to work, but have a couple of
remaning problems :

- it only works on tables, not on views, so then it's a little difficult to use MS/SQL to filter the content

- besides, OGR always perform a LEFT JOIN (apparently adding random values if none are found), so it doesn't filter anything anyway

- OGR definitely doesn't like non-A-Z characters in the table and column name, I get an SQL parse error if such are used

I've tried to filter my layer content with both a LAYER/FILTER or a CLASS/EXPRESSION, but how to I test for the value NULL ?

Best regards / Med venlig hilsen
Lars V. Nielsen
--------------------------------------------------------
Hvenegaard A/S
http://www.hvenegaard.dk

----- Original Message ----- 
From: "Frank Warmerdam" <fwarmerdam at GMAIL.COM>
To: <MAPSERVER-USERS at LISTS.UMN.EDU>
Sent: Tuesday, August 23, 2005 2:50 PM
Subject: Re: [UMN_MAPSERVER-USERS] Filter layer with MS/SQL based attribute ?
b=SMftptmlbsr0DT00M4m7daF/51hDTSm35WHy/94yZIRDphDM0FPGm2tkV1LmtdgbMd5Vk1cBJixCTDeM2f2H/VzIAA9atB3PtyMnroddAltBA7JFses2z3tneVcXv7BSmr
rfwOvY116jp4A09d2p0c6uc3Z20p/gGI39TJA/u6A=


On 8/23/05, Lars V. Nielsen (Hvenegaard) <LVN at hvenegaard.dk> wrote:
> I have an OGR based polygon table with just a numeric ID attached. I'm
> displaying this in the map, it's working fine.
>
> A non-geographical table in MS/SQL has a table with this ID as key, and an
> attribute to tell whether the entry is active or not (null if active).
>
> I've tried to use a JOIN and a FILTER in my layer to implement the
> filtering, but I'm somehow not getting it right. And the docs are rather
> scarce on examples.
>
> Can anyone give a fast rundown on how to do this ?
>
> Is JOIN+FILTER even the way to accomplish this ?

Lars,

Are you accessing your MS/SQL non-geographical table through
OGR as well?  Are you using an OGR SQL JOIN or a MapServer
JOIN?

OGR SQL joins do not permit WHERE queries on joined fields.
The WHERE clause is currently evaluated on the primary table
before the join is done.  I *suspect* that is the problem you are
running into though you haven't really provided enough details
to make it clear.

If your polygon data is in MS/SQL too then I suspect the best
way to organize things is executing a custom SQL query doing
the join and WHERE all within MS/SQL.

Best regards,
-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent