[mapserver-users] some questions regarding getFeatureInfo, SLD, PostGIS
Julien Cigar
jcigar at ulb.ac.be
Fri Sep 21 05:37:28 PDT 2012
On 09/21/2012 14:22, Smith, Michael ERDC-RDE-CRREL-NH wrote:
> What I've done is have the join in the sql but either join it to the
> correct join column or join it to a null match (and make it an outer
> join).
>
> I typically use oracle syntax and not ansi syntax but the trick is to have
> all the table joins in the sql but using case to alter the join condition.
I see.. the only problem I see with this solution is that you cannot do
inner join (as the resultset will always be empty if one of the join
condition is false).
Just curious: is the Oracle planner smart enough to not JOIN the table
if the join condition is false ?
With PostgreSQL it seems not the case..:
congo_river=# explain analyze select occ.id as occ_id, si.id as site_id
FROM occurrences occ LEFT JOIN sites si ON 1=2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..536.20 rows=5840 width=8) (actual
time=0.015..39.875 rows=5840 loops=1)
Join Filter: false
-> Seq Scan on occurrences occ (cost=0.00..536.20 rows=5840
width=4) (actual time=0.007..8.234 rows=5840 loops=1)
-> Result (cost=0.00..0.08 rows=1 width=0) (actual
time=0.001..0.001 rows=0 loops=5840)
One-Time Filter: false
Total runtime: 47.016 ms
(6 rows)
congo_river=#
Thanks
Julien
> On 9/21/12 8:06 AM, "Julien Cigar"<jcigar at ulb.ac.be> wrote:
>
>> On 09/21/2012 13:24, Smith, Michael ERDC-RDE-CRREL-NH wrote:
>>> At the SQL level, you can do some fancy work with CASE and setting some
>>> default values
>>>
>>> The CASE function allows you to set all kind of logical comparisons. You
>>> can do conditional joins that way, setting the join condition to a non
>>> matching value when you don't want the join, etc. SQL is more powerful
>>> than any template language.
>> You'll have to explain me how you add a JOIN clause through a CASE
>> statement ..? :)
>>
>>> Mike
>>>
>>>
>>>
>>> On 9/21/12 7:07 AM, "Julien Cigar"<jcigar at ulb.ac.be> wrote:
>>>
>>>> On 09/21/2012 12:50, Rahkonen Jukka wrote:
>>>>> Hi,
>>>> Hello,
>>>>
>>>>> So you have tried to define variables %SEX% and %BOD_ID%, used them
>>>>> inside your DATA and then fired WMS GetMaps by
>>>>> adding&SEX=your_value&BOD_ID=your_id? Does in not work or why it is
>>>>> not
>>>>> powerful enough?
>>>> It is not powerful enough because it doesn't allow you to make simple
>>>> logical tests (if defined/equals/...), the run-time substitution is a
>>>> simple "replace %FOO%" by the value of FOO= from the query string.
>>>>
>>>> It doesn't allow you to do something like: "if FOO is defined or equal
>>>> to xxx, then add "JOIN foo f ON f.id = bar.foo_id", select additional
>>>> columns, and add a "WHERE foo.id = ..." at a certain place in the DATA
>>>> string".
>>>>
>>>> I guess mapscript should be used in this case, but it would be really
>>>> interesting to have this simple template language (or another solution)
>>>> without having to involve mapscript.
>>>>
>>>> Julien
>>>>
>>>>> -Jukka Rahkonen-
>>>>>
>>>>>
>>>>> Julien Cigar wrote:
>>>>>
>>>>>> Hello,
>>>>>>
>>>>>> I'm using Mapserver 6.0.1 with the following PostGIS layer:
>>>>>> http://pastie.org/private/84042k84vmljbontls5xvq
>>>>>>
>>>>>> The user has the possibility to select quite a lot of filters, which
>>>>>> implies that
>>>>>> the SQL query should, in theory ,be generated dynamically.
>>>>>> As this is not possible with Mapserver (run-time substitution with
>>>>>> HTTP
>>>>>> parameters and VALIDATION ... END isn't powerful enough is my case),
>>>>>> I'm
>>>>>> dynamically generating an SLD
>>>>>> (http://pastie.org/private/p66ml3uvqwcnphuuvera) to filter items and
>>>>>> it
>>>>>> works very well.
>>>>>>
>>>>>> Some questions:
>>>>>>
>>>>>> - Why does Mapserver doesn't have a very lightweight template
>>>>>> language
>>>>>> (or
>>>>>> something similar) to dynamically build a string following given
>>>>>> parameters? It
>>>>>> could be really useful to avoid unnecessary JOIN, etc.
>>>>>> For example something like:
>>>>>> http://pastie.org/private/bojm3pi1dwrv18m2tvq (this is just an
>>>>>> example, but
>>>>>> the idea is there)
>>>>>>
>>>>>> - I noticed in the PostgreSQL logs that sometimes Mapserver adds an
>>>>>> additional condition to the WHERE clause when SLD is used. For
>>>>>> example
>>>>>> with
>>>>>> the following<ogc:Filter> in my SLD:
>>>>>> #########################################
>>>>>> <ogc:Filter>
>>>>>> <ogc:PropertyIsEqualTo>
>>>>>> <ogc:PropertyName>basis_of_record_id</ogc:PropertyName>
>>>>>> <ogc:Literal>1</ogc:Literal>
>>>>>> </ogc:PropertyIsEqualTo>
>>>>>> </ogc:Filter>
>>>>>> #########################################
>>>>>>
>>>>>> Mapserver adds an additional "and (( ("basis_of_record_id"= 1) ))":
>>>>>>
>>>>>> (...) AS q where geom&& GeomFromText('POLYGON((-3.6860351557368
>>>>>> -9.81732918571448,-3.6860351557368 9.81732918571447,47.6860351496117
>>>>>> 9.81732918571447,47.6860351496117 -9.81732918571448,-3.6860351557368
>>>>>> -9.81732918571448))',4326) and (( ("basis_of_record_id"= 1) ))
>>>>>>
>>>>>> which is not bad as less rows have to be post-processed by the SLD
>>>>>> engine.
>>>>>>
>>>>>> However, as soon as I add an additional<ogc:or> (or<ogc:and>) as:
>>>>>> #########################################
>>>>>> <ogc:or>
>>>>>> <ogc:PropertyIsEqualTo>
>>>>>> <ogc:PropertyName>basis_of_record_id</ogc:PropertyName>
>>>>>> <ogc:Literal>1</ogc:Literal>
>>>>>> </ogc:PropertyIsEqualTo>
>>>>>> <ogc:PropertyIsEqualTo>
>>>>>> <ogc:PropertyName>basis_of_record_id</ogc:PropertyName>
>>>>>> <ogc:Literal>2</ogc:Literal>
>>>>>> </ogc:PropertyIsEqualTo>
>>>>>> </ogc:or>
>>>>>> #########################################
>>>>>>
>>>>>> Mapserver doesn't add this additional AND/OR clause (and ((
>>>>>> ("basis_of_record_id"= 1) OR ("basis_of_record_id" = 2) )) in this
>>>>>> case).. Why
>>>>>> ?
>>>>>>
>>>>>> - Is it normal that my SLD is totally ignored when I'm using
>>>>>> REQUEST=GetFeatureInfo? Is the SLD only processed with
>>>>>> REQUEST=GetMap?
>>>>>> If this is the case, what's the typical alternative way of doing it?
>>>>>> Should I go through WFS .. ?
>>>>>>
>>>>>> In advance, thank you for your answers :) Julien
>>>>>>
>>>>>> --
>>>>>> No trees were killed in the creation of this message.
>>>>>> However, many electrons were terribly inconvenienced.
>>>>> _______________________________________________
>>>>> mapserver-users mailing list
>>>>> mapserver-users at lists.osgeo.org
>>>>> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>>>> --
>>>> No trees were killed in the creation of this message.
>>>> However, many electrons were terribly inconvenienced.
>>>>
>>
>> --
>> No trees were killed in the creation of this message.
>> However, many electrons were terribly inconvenienced.
>>
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: jcigar.vcf
Type: text/x-vcard
Size: 292 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20120921/2037968b/attachment-0001.vcf>
More information about the mapserver-users
mailing list