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"<[email protected]> 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"<[email protected]> 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 [email protected] 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.
<<attachment: jcigar.vcf>>
_______________________________________________ mapserver-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/mapserver-users
