"Fossies" - the Fresh Open Source Software Archive

Member "user/tutorials/cql/cql_tutorial.html" (22 Nov 2019, 19932 Bytes) of package /linux/www/geoserver-2.16.1-htmldoc.zip:


The requested HTML page contains a <FORM> tag that is unusable on "Fossies" in "automatic" (rendered) mode so that page is shown as HTML source code syntax highlighting (style: standard) with prefixed line numbers. Alternatively you can here view or download the uninterpreted source code file.

    1 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    2   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    3 <html xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-US">
    4 <head>
    5   <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    6   
    7   <title>CQL and ECQL &mdash; GeoServer 2.16.1 User Manual</title>
    8   <link rel="stylesheet" href="../../_static/blueprint/screen.css" type="text/css" media="screen, projection" />
    9   <link rel="stylesheet" href="../../_static/blueprint/print.css" type="text/css" media="print" /> 
   10   <!--[if IE]>
   11   <link rel="stylesheet" href="../../_static/blueprint/ie.css" type="text/css" media="screen, projection" />
   12   <![endif]-->
   13   <link rel="stylesheet" href="../../_static/default.css" type="text/css" />
   14   <link rel="stylesheet" href="../../_static/pygments.css" type="text/css" />
   15   <script type="text/javascript">
   16     var DOCUMENTATION_OPTIONS = {
   17         URL_ROOT:    '../../',
   18         VERSION:     '2.16.1',
   19         COLLAPSE_MODINDEX: false,
   20         FILE_SUFFIX: '.html'
   21     };
   22   </script>
   23   <script type="text/javascript" src="../../_static/jquery.js"></script>
   24   <script type="text/javascript" src="../../_static/doctools.js"></script>
   25   <script type="text/javascript" src="../../_static/searchtools.js"></script>
   26   <script type="text/javascript" src="../../searchindex.js"></script>
   27   <link rel="shortcut icon" href="../../_static/geoserver.ico"/>
   28       <link rel="search" title="Search" href="../../search.html" />
   29       <link rel="top" title="GeoServer 2.16.1 User Manual" href="../../index.html" />
   30       <link rel="up" title="Tutorials" href="../index.html" />
   31       <link rel="next" title="Using the ImageMosaic plugin for raster time-series data" href="../imagemosaic_timeseries/imagemosaic_timeseries.html" />
   32       <link rel="prev" title="WMS Animator" href="../animreflector.html" />
   33 </head>
   34 <body class="tutorials/cql/cql_tutorial">
   35   <div id="header" class="selfclear">
   36     <div class="wrap selfclear">
   37       <div id="logo"><a href="../../index.html">GeoServer 2.16.1 User Manual</a></div>
   38       <ul id="top-nav">
   39         <li class="first"><a href="http://geoserver.org/about">About</a></li>
   40         <li><a href="http://blog.geoserver.org/">Blog</a></li>
   41         <li><a href="http://geoserver.org/download">Download</a></li>
   42         <!--<li><a href="../../index.html">Documentation</a></li>-->
   43       </ul>
   44         <form id="quick-search" action="../../search.html" method="get">
   45           <fieldset>
   46             <input type="hidden" name="check_keywords" value="yes" />
   47             <input type="hidden" name="area" value="default" />
   48             <input id="quick-search-query" type="text" name="q" accessKey="q" name="searchQuery.queryString" size="25" value="Search Documentation&hellip;" size="20" tabindex="3" onblur="if(this.value=='') this.value='Search Documentation&hellip;';" onfocus="if(this.value=='Search Documentation&hellip;') this.value='';" />
   49             <input id="quick-search-submit" type="image" value="Search" src="../../_static/chrome/search_icon_green.png" />
   50           </fieldset>
   51         </form>
   52     </div><!-- /.wrap -->
   53   </div><!-- /#header -->
   54   <div id="main">
   55     <div class="wrap selfclear">
   56       <div id="content-left" class="content-border"></div>
   57       <div id="content">
   58 <ul id="breadcrumbs">
   59   
   60   <li><a href="../../index.html">GeoServer 2.16.1 User Manual</a> &raquo;</li>
   61   <li><a href="../index.html" accesskey="U">Tutorials</a> &raquo;</li>
   62   <li>CQL and ECQL</li>
   63 </ul>
   64 <ul id="relatedlinks" class="selfclear">
   65   <li class="first">
   66     <a href="../../py-modindex.html" title="Python Module Index"
   67        accesskey="">modules</a></li>
   68   <li>
   69     <a href="../imagemosaic_timeseries/imagemosaic_timeseries.html" title="Using the ImageMosaic plugin for raster time-series data"
   70        accesskey="N">next</a>|</li>
   71   <li>
   72     <a href="../animreflector.html" title="WMS Animator"
   73        accesskey="P">previous</a>|</li>
   74 </ul>
   75         
   76   <div class="section" id="cql-and-ecql">
   77 <span id="cql-tutorial"></span><h1>CQL and ECQL<a class="headerlink" href="#cql-and-ecql" title="Permalink to this headline"></a></h1>
   78 <p>CQL (Common Query Language) is a query language created by the OGC for the <a class="reference external" href="http://www.opengeospatial.org/standards/cat">Catalogue Web Services specification</a>.
   79 Unlike the XML-based Filter Encoding language, CQL is written using a familiar text-based syntax.
   80 It is thus more readable and better-suited for manual authoring.</p>
   81 <p>However, CQL has some limitations.  For example it cannot encode id filters, and it requires an attribute to be on the left side of any comparison operator.
   82 For this reason, GeoServer provides an extended version of CQL called ECQL.
   83 ECQL removes the limitations of CQL, providing a more flexible language with stronger similarities with SQL.</p>
   84 <p>GeoServer supports the use of both CQL and ECQL in WMS and WFS requests, as well as in GeoServer’s SLD <a class="reference internal" href="../../styling/sld/extensions/pointsymbols.html#pointsymbols"><span class="std std-ref">dynamic symbolizers</span></a>.
   85 Whenever the documentation refers to CQL, ECQL syntax can be used as well (and if not, please report that as a bug!).</p>
   86 <p>This tutorial introduces the CQL/ECQL language by example.
   87 For a full reference, refer to the <a class="reference internal" href="../../filter/ecql_reference.html#filter-ecql-reference"><span class="std std-ref">ECQL Reference</span></a>.</p>
   88 <div class="section" id="getting-started">
   89 <h2>Getting started<a class="headerlink" href="#getting-started" title="Permalink to this headline"></a></h2>
   90 <p>The following examples use the <code class="docutils literal notranslate"><span class="pre">topp:states</span></code> sample layer shipped with GeoServer.
   91 They demonstrate how CQL filters work by using the WMS <a class="reference internal" href="../../services/wms/vendor.html#wms-vendor-parameters"><span class="std std-ref">CQL_FILTER vendor parameter</span></a> to alter the data displayed by WMS requests.
   92 The easiest way to follow the tutorial is to open the GeoServer Map Preview for the <code class="docutils literal notranslate"><span class="pre">topp:states</span></code> layer.
   93 Click on the <em>Options</em> button at the top of the map preview to open the advanced options toolbar.
   94 The example filters can be entered in the <em>Filter: CQL</em> box.</p>
   95 <div class="figure align-center" id="id1">
   96 <img alt="../../_images/gettingStarted1.png" src="../../_images/gettingStarted1.png" />
   97 <p class="caption"><span class="caption-text"><em>topp:states preview with advanced toolbar open.</em></span></p>
   98 </div>
   99 <p>The attributes used in the filter examples are those included in the layer.
  100 For example, the following are the attribute names and values for the Colorado feature:</p>
  101 <table border="1" class="docutils">
  102 <colgroup>
  103 <col width="50%" />
  104 <col width="50%" />
  105 </colgroup>
  106 <tbody valign="top">
  107 <tr class="row-odd"><td><strong>Attribute</strong></td>
  108 <td><strong>states.6</strong></td>
  109 </tr>
  110 <tr class="row-even"><td>STATE_NAME</td>
  111 <td>Colorado</td>
  112 </tr>
  113 <tr class="row-odd"><td>STATE_FIPS</td>
  114 <td>08</td>
  115 </tr>
  116 <tr class="row-even"><td>SUB_REGION</td>
  117 <td>Mtn</td>
  118 </tr>
  119 <tr class="row-odd"><td>STATE_ABBR</td>
  120 <td>CO</td>
  121 </tr>
  122 <tr class="row-even"><td>LAND_KM</td>
  123 <td>268659.501</td>
  124 </tr>
  125 <tr class="row-odd"><td>WATER_KM</td>
  126 <td>960.364</td>
  127 </tr>
  128 <tr class="row-even"><td>PERSONS</td>
  129 <td>3294394.0</td>
  130 </tr>
  131 <tr class="row-odd"><td>FAMILIES</td>
  132 <td>854214.0</td>
  133 </tr>
  134 <tr class="row-even"><td>HOUSHOLD</td>
  135 <td>1282489.0</td>
  136 </tr>
  137 <tr class="row-odd"><td>MALE</td>
  138 <td>1631295.0</td>
  139 </tr>
  140 <tr class="row-even"><td>FEMALE</td>
  141 <td>1663099.0</td>
  142 </tr>
  143 <tr class="row-odd"><td>WORKERS</td>
  144 <td>1233023.0</td>
  145 </tr>
  146 <tr class="row-even"><td>DRVALONE</td>
  147 <td>1216639.0</td>
  148 </tr>
  149 <tr class="row-odd"><td>CARPOOL</td>
  150 <td>210274.0</td>
  151 </tr>
  152 <tr class="row-even"><td>PUBTRANS</td>
  153 <td>46983.0</td>
  154 </tr>
  155 <tr class="row-odd"><td>EMPLOYED</td>
  156 <td>1633281.0</td>
  157 </tr>
  158 <tr class="row-even"><td>UNEMPLOY</td>
  159 <td>99438.0</td>
  160 </tr>
  161 <tr class="row-odd"><td>SERVICE</td>
  162 <td>421079.0</td>
  163 </tr>
  164 <tr class="row-even"><td>MANUAL</td>
  165 <td>181760.0</td>
  166 </tr>
  167 <tr class="row-odd"><td>P_MALE</td>
  168 <td>0.495</td>
  169 </tr>
  170 <tr class="row-even"><td>P_FEMALE</td>
  171 <td>0.505</td>
  172 </tr>
  173 <tr class="row-odd"><td>SAMP_POP</td>
  174 <td>512677.0</td>
  175 </tr>
  176 </tbody>
  177 </table>
  178 </div>
  179 <div class="section" id="simple-comparisons">
  180 <h2>Simple comparisons<a class="headerlink" href="#simple-comparisons" title="Permalink to this headline"></a></h2>
  181 <p>Let’s get started with a simple example. In CQL arithmetic and comparisons
  182 are expressed using plain text. The filter <code class="docutils literal notranslate"><span class="pre">PERSONS</span> <span class="pre">&gt;</span> <span class="pre">15000000</span></code> will select states that
  183 have more than 15 million inhabitants:</p>
  184 <div class="figure align-center" id="id2">
  185 <img alt="../../_images/more15M.png" src="../../_images/more15M.png" />
  186 <p class="caption"><span class="caption-text"><em>PERSONS &gt; 15000000</em></span></p>
  187 </div>
  188 <p>The full list of comparison operators is: <code class="docutils literal notranslate"><span class="pre">=</span></code>, <code class="docutils literal notranslate"><span class="pre">&lt;&gt;</span></code>, <code class="docutils literal notranslate"><span class="pre">&gt;</span></code>, <code class="docutils literal notranslate"><span class="pre">&gt;=</span></code>,  <code class="docutils literal notranslate"><span class="pre">&lt;</span></code>, <code class="docutils literal notranslate"><span class="pre">&lt;=</span></code>.</p>
  189 <p>To select a range of values the BETWEEN operator can be used: <code class="docutils literal notranslate"><span class="pre">PERSONS</span> <span class="pre">BETWEEN</span> <span class="pre">1000000</span> <span class="pre">AND</span> <span class="pre">3000000</span></code>:</p>
  190 <div class="figure align-center" id="id3">
  191 <img alt="../../_images/between.png" src="../../_images/between.png" />
  192 <p class="caption"><span class="caption-text"><em>PERSONS BETWEEN 1000000 AND 3000000</em></span></p>
  193 </div>
  194 <p>Comparison operators also support text values. For instance, to select only the state of California, the filter is
  195 <code class="docutils literal notranslate"><span class="pre">STATE_NAME</span> <span class="pre">=</span> <span class="pre">'California'</span></code>.
  196 More general text comparisons can be made using the <code class="docutils literal notranslate"><span class="pre">LIKE</span></code> operator. <code class="docutils literal notranslate"><span class="pre">STATE_NAME</span> <span class="pre">LIKE</span> <span class="pre">'N%'</span></code> will extract all states starting with an “N”:</p>
  197 <div class="figure align-center" id="id4">
  198 <img alt="../../_images/startn.png" src="../../_images/startn.png" />
  199 <p class="caption"><span class="caption-text"><em>STATE_NAME LIKE ‘N%’</em></span></p>
  200 </div>
  201 <p>It is also possible to compare two attributes with each other. <code class="docutils literal notranslate"><span class="pre">MALE</span> <span class="pre">&gt;</span> <span class="pre">FEMALE</span></code> selects the
  202 states in which the male population surpasses the female one (a rare occurrence):</p>
  203 <div class="figure align-center" id="id5">
  204 <img alt="../../_images/malefemale.png" src="../../_images/malefemale.png" />
  205 <p class="caption"><span class="caption-text"><em>MALE &gt; FEMALE</em></span></p>
  206 </div>
  207 <p>Arithmetic expressions can be computed using the <code class="docutils literal notranslate"><span class="pre">+,</span> <span class="pre">-,</span> <span class="pre">*,</span> <span class="pre">/</span></code> operators.
  208 The filter <code class="docutils literal notranslate"><span class="pre">UNEMPLOY</span> <span class="pre">/</span> <span class="pre">(EMPLOYED</span> <span class="pre">+</span> <span class="pre">UNEMPLOY)</span> <span class="pre">&gt;</span> <span class="pre">0.07</span></code> selects all states whose unemployment ratio is above 7% (remember the sample data is very old, so don’t draw any conclusion from the results!)</p>
  209 <div class="figure align-center" id="id6">
  210 <img alt="../../_images/employ.png" src="../../_images/employ.png" />
  211 <p class="caption"><span class="caption-text"><em>UNEMPLOY / (EMPLOYED + UNEMPLOY) &gt; 0.07</em></span></p>
  212 </div>
  213 </div>
  214 <div class="section" id="id-and-list-comparisons">
  215 <h2>Id and list comparisons<a class="headerlink" href="#id-and-list-comparisons" title="Permalink to this headline"></a></h2>
  216 <p>If we want to extract only the states with specific feature ids we can use the <code class="docutils literal notranslate"><span class="pre">IN</span></code> operator without specifying any attribute, as in <code class="docutils literal notranslate"><span class="pre">IN</span> <span class="pre">('states.1',</span> <span class="pre">'states.12')</span></code>:</p>
  217 <div class="figure align-center" id="id7">
  218 <img alt="../../_images/idfilter.png" src="../../_images/idfilter.png" />
  219 <p class="caption"><span class="caption-text"><em>IN (‘states.1’, ‘states.12’)</em></span></p>
  220 </div>
  221 <p>If instead we want to extract the states whose name is in a given list we can use the <code class="docutils literal notranslate"><span class="pre">IN</span></code> operator specifying an attribute name, as in <code class="docutils literal notranslate"><span class="pre">STATE_NAME</span> <span class="pre">IN</span> <span class="pre">('New</span> <span class="pre">York',</span> <span class="pre">'California',</span> <span class="pre">'Montana',</span> <span class="pre">'Texas')</span></code>:</p>
  222 <div class="figure align-center" id="id8">
  223 <img alt="../../_images/statenames.png" src="../../_images/statenames.png" />
  224 <p class="caption"><span class="caption-text"><em>STATE_NAME IN (‘New York’, ‘California’, ‘Montana’, ‘Texas’)</em></span></p>
  225 </div>
  226 </div>
  227 <div class="section" id="filter-functions">
  228 <h2>Filter functions<a class="headerlink" href="#filter-functions" title="Permalink to this headline"></a></h2>
  229 <p>CQL/ECQL can use any of the <a class="reference internal" href="../../filter/function_reference.html#filter-function-reference"><span class="std std-ref">filter functions</span></a> available in GeoServer.
  230 This greatly increases the power of CQL expressions.</p>
  231 <p>For example, suppose we want to find all states whose name contains an “m”, regardless of letter case. We can use the <code class="docutils literal notranslate"><span class="pre">strToLowerCase</span></code> to turn all the state names to lowercase and then use a like comparison: <code class="docutils literal notranslate"><span class="pre">strToLowerCase(STATE_NAME)</span> <span class="pre">like</span> <span class="pre">'%m%'</span></code>:</p>
  232 <div class="figure align-center" id="id9">
  233 <img alt="../../_images/mstates.png" src="../../_images/mstates.png" />
  234 <p class="caption"><span class="caption-text"><em>strToLowerCase(STATE_NAME) like ‘%m%’</em></span></p>
  235 </div>
  236 </div>
  237 <div class="section" id="geometric-filters">
  238 <h2>Geometric filters<a class="headerlink" href="#geometric-filters" title="Permalink to this headline"></a></h2>
  239 <p>CQL provides a full set of geometric filter capabilities. Say, for example, you want to display only the states that intersect the (-90,40,-60,45) bounding box.
  240 The filter will be <code class="docutils literal notranslate"><span class="pre">BBOX(the_geom,</span> <span class="pre">-90,</span> <span class="pre">40,</span> <span class="pre">-60,</span> <span class="pre">45)</span></code></p>
  241 <div class="figure align-center" id="id10">
  242 <img alt="../../_images/bbox.png" src="../../_images/bbox.png" />
  243 <p class="caption"><span class="caption-text"><em>BBOX(the_geom, -90, 40, -60, 45)</em></span></p>
  244 </div>
  245 <p>Conversely, you can select the states that do <em>not</em> intersect the bounding box with the filter: <code class="docutils literal notranslate"><span class="pre">DISJOINT(the_geom,</span> <span class="pre">POLYGON((-90</span> <span class="pre">40,</span> <span class="pre">-90</span> <span class="pre">45,</span> <span class="pre">-60</span> <span class="pre">45,</span> <span class="pre">-60</span> <span class="pre">40,</span> <span class="pre">-90</span> <span class="pre">40)))</span></code>:</p>
  246 <div class="figure align-center" id="id11">
  247 <img alt="../../_images/disjoint.png" src="../../_images/disjoint.png" />
  248 <p class="caption"><span class="caption-text"><em>DISJOINT(the_geom, POLYGON((-90 40, -90 45, -60 45, -60 40, -90 40)))</em></span></p>
  249 </div>
  250 <p>The full list of geometric predicates is: <code class="docutils literal notranslate"><span class="pre">EQUALS</span></code>, <code class="docutils literal notranslate"><span class="pre">DISJOINT</span></code>, <code class="docutils literal notranslate"><span class="pre">INTERSECTS</span></code>, <code class="docutils literal notranslate"><span class="pre">TOUCHES</span></code>, <code class="docutils literal notranslate"><span class="pre">CROSSES</span></code>, <code class="docutils literal notranslate"><span class="pre">WITHIN</span></code>, <code class="docutils literal notranslate"><span class="pre">CONTAINS</span></code>, <code class="docutils literal notranslate"><span class="pre">OVERLAPS</span></code>, <code class="docutils literal notranslate"><span class="pre">RELATE</span></code>, <code class="docutils literal notranslate"><span class="pre">DWITHIN</span></code>, <code class="docutils literal notranslate"><span class="pre">BEYOND</span></code>.</p>
  251 </div>
  252 </div>
  253 
  254 
  255       <div class="selfclear pagination-nav">
  256           <div class="leftwise"><strong>Previous</strong>: <a href="../animreflector.html" title="previous chapter">WMS Animator</a></div>
  257           <div class="rightwise"><strong>Next</strong>: <a href="../imagemosaic_timeseries/imagemosaic_timeseries.html" title="next chapter">Using the ImageMosaic plugin for raster time-series data</a></div>
  258       </div>
  259       </div><!-- /#content> -->
  260       <div id="content-right" class="content-border"></div>
  261   <div id="sidebar" class="contrast">
  262       <div id="toc" class="section">
  263         <h3 class="pngfix">Table Of Contents</h3>
  264         <ul>
  265 <li><a class="reference internal" href="#">CQL and ECQL</a><ul>
  266 <li><a class="reference internal" href="#getting-started">Getting started</a></li>
  267 <li><a class="reference internal" href="#simple-comparisons">Simple comparisons</a></li>
  268 <li><a class="reference internal" href="#id-and-list-comparisons">Id and list comparisons</a></li>
  269 <li><a class="reference internal" href="#filter-functions">Filter functions</a></li>
  270 <li><a class="reference internal" href="#geometric-filters">Geometric filters</a></li>
  271 </ul>
  272 </li>
  273 </ul>
  274 
  275         <div class="section-footer"></div>
  276       </div>
  277         <div class="section">
  278           <h3>Continue Reading</h3>
  279           <ul>
  280             <li>Previous: <a href="../animreflector.html" title="previous chapter">WMS Animator</a></li>
  281             <li>Next: <a href="../imagemosaic_timeseries/imagemosaic_timeseries.html" title="next chapter">Using the ImageMosaic plugin for raster time-series data</a></li>
  282           </ul>
  283         </div>
  284         <div class="section">
  285         <h3>This Page</h3>
  286         <ul class="this-page-menu">
  287                 
  288         <li><a href="https://github.com/geoserver/geoserver/tree/master/doc/en/user/source/tutorials/cql/cql_tutorial.rst">Edit</a></li>
  289         </ul>
  290         </div>
  291   </div><!-- /#sidebar -->
  292   </div><!-- /.wrap> -->
  293 </div><!-- /#main -->
  294 <div id="footer">
  295   <div class="wrap">
  296     &copy; Copyright 2019, Open Source Geospatial Foundation. License <a href="http://creativecommons.org/licenses/by/3.0/">Creative Commons Attribution</a>.
  297     Last updated on Nov 22, 2019.
  298     Created using <a href="http://sphinx.pocoo.org/">Sphinx</a>.
  299   </div><!-- /.wrap> -->
  300 </div><!-- /#footer -->
  301   </body>
  302 </html>