"Fossies" - the Fresh Open Source Software Archive

Member "user/tutorials/imagemosaic-jdbc/imagemosaic-jdbc_tutorial.html" (22 Nov 2019, 40701 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>Storing a coverage in a JDBC database &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 GeoTools feature-pregeneralized module" href="../feature-pregeneralized/feature-pregeneralized_tutorial.html" />
   32       <link rel="prev" title="Building and using an image pyramid" href="../imagepyramid/imagepyramid.html" />
   33 </head>
   34 <body class="tutorials/imagemosaic-jdbc/imagemosaic-jdbc_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>Storing a coverage in a JDBC database</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="../feature-pregeneralized/feature-pregeneralized_tutorial.html" title="Using the GeoTools feature-pregeneralized module"
   70        accesskey="N">next</a>|</li>
   71   <li>
   72     <a href="../imagepyramid/imagepyramid.html" title="Building and using an image pyramid"
   73        accesskey="P">previous</a>|</li>
   74 </ul>
   75         
   76   <div class="section" id="storing-a-coverage-in-a-jdbc-database">
   77 <span id="imagemosaic-jdbc-tutorial"></span><h1>Storing a coverage in a JDBC database<a class="headerlink" href="#storing-a-coverage-in-a-jdbc-database" title="Permalink to this headline"></a></h1>
   78 <div class="admonition warning">
   79 <p class="first admonition-title">Warning</p>
   80 <p class="last">The screenshots on this tutorial have not yet been updated for the 2.0.x user interface.  But most all the rest of the information should be valid, and the user interface is roughly the same, but a bit more easy to use.</p>
   81 </div>
   82 <div class="section" id="introduction">
   83 <h2>Introduction<a class="headerlink" href="#introduction" title="Permalink to this headline"></a></h2>
   84 <p>This tutorial describes the process of storing a coverage along with its pyramids in a jdbc database. The ImageMosaic JDBC plugin is authored by Christian Mueller and is part of the geotools library.</p>
   85 <p>The full documentation is available here:<a class="reference external" href="http://docs.geotools.org/latest/userguide/library/coverage/jdbc/index.html">http://docs.geotools.org/latest/userguide/library/coverage/jdbc/index.html</a></p>
   86 <p>This tutorial will show one possible scenario, explaining step by step what to do for using this module in GeoServer (since Version 1.7.2)</p>
   87 </div>
   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>We use postgis/postgres as database engine, a database named “gis” and start with an image from openstreetmap. We also need this utility  <a class="reference external" href="http://www.gdal.org/gdal_retile.html">http://www.gdal.org/gdal_retile.html</a> . The best way to install with all dependencies is downloading from here <a class="reference external" href="http://fwtools.maptools.org/">http://fwtools.maptools.org/</a></p>
   91 <img alt="../../_images/start.png" src="../../_images/start.png" />
   92 <p>Create a working directory, lets call it <code class="file docutils literal notranslate"><span class="pre">working</span></code> ,download this image with a right mouse click (Image save as …) and save it as <code class="file docutils literal notranslate"><span class="pre">start_rgb.png</span></code></p>
   93 <p>Check your image with:</p>
   94 <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">gdalinfo</span> <span class="n">start_rgb</span><span class="o">.</span><span class="n">png</span>
   95 </pre></div>
   96 </div>
   97 <p>This image has 4 Bands (Red,Green,Blue,Alpha) and needs much memory. As a rule, it is better to use images with a color table. We can transform with <strong class="command">rgb2pct</strong> (<strong class="command">rgb2pct.py</strong> on Unix).:</p>
   98 <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">rgb2pct</span> <span class="o">-</span><span class="n">of</span> <span class="n">png</span> <span class="n">start_rgb</span><span class="o">.</span><span class="n">png</span> <span class="n">start</span><span class="o">.</span><span class="n">png</span>
   99 </pre></div>
  100 </div>
  101 <p>Compare the sizes of the 2 files.</p>
  102 <p>Afterwards, create a world file <code class="file docutils literal notranslate"><span class="pre">start.wld</span></code> in the <code class="file docutils literal notranslate"><span class="pre">working</span></code> directory with the following content.:</p>
  103 <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="mf">0.0075471698</span>
  104 <span class="mf">0.0000000000</span>
  105 <span class="mf">0.0000000000</span>
  106 <span class="o">-</span><span class="mf">0.0051020408</span>
  107 <span class="mf">8.9999995849</span>
  108 <span class="mf">48.9999999796</span>
  109 </pre></div>
  110 </div>
  111 </div>
  112 <div class="section" id="preparing-the-pyramids-and-the-tiles">
  113 <h2>Preparing the pyramids and the tiles<a class="headerlink" href="#preparing-the-pyramids-and-the-tiles" title="Permalink to this headline"></a></h2>
  114 <p>If you are new to tiles and pyramids, take a quick look here <a class="reference external" href="http://northstar-www.dartmouth.edu/doc/idl/html_6.2/Image_Tiling.html">http://northstar-www.dartmouth.edu/doc/idl/html_6.2/Image_Tiling.html</a></p>
  115 </div>
  116 <div class="section" id="how-many-pyramids-are-needed">
  117 <h2>How many pyramids are needed ?<a class="headerlink" href="#how-many-pyramids-are-needed" title="Permalink to this headline"></a></h2>
  118 <p>Lets do a simple example. Given an image with 1024x1024 pixels and a tile size with 256x256 pixels.We can calculate in our brain that we need 16 tiles. Each pyramid reduces the number of tiles by a factor of 4. The first pyramid has 16/4 = 4 tiles, the second pyramid has only 4/4 = 1 tile.</p>
  119 <p>Solution: The second pyramid fits on one tile, we are finished and we need 2 pyramids.</p>
  120 <p>The formula for this:</p>
  121 <p><strong>number of pyramids = log(pixelsize of image) / log(2) - log (pixelsize of tile) / log(2)</strong>.</p>
  122 <p>Try it: Go to Google and enter as search term “log(1024)/log(2) - log(256)/log(2)” and look at the result.</p>
  123 <p>If your image is 16384 pixels , and your tile size is 512 pixels, it is</p>
  124 <p>log(16384)/log(2) - log(512)/log(2) = 5</p>
  125 <p>If your image is 18000 pixels, the result = 5.13570929. Thake the floor and use 5 pyramids. Remember, the last pyramid reduces 4 tiles to 1 tile, so this pyramid is not important.</p>
  126 <p>If your image is 18000x12000 pixel, use the bigger dimension (18000) for the formula.</p>
  127 <p>For creating pyramids and tiles, use <a class="reference external" href="http://www.gdal.org/gdal_retile.html">http://www.gdal.org/gdal_retile.html</a> from the gdal project.</p>
  128 <p>The executeable for Windows users is <strong class="command">gdal_retile.bat</strong> or only <strong class="command">gdal_retile</strong>, Unix users call <strong class="command">gdal_retile.py</strong></p>
  129 <p>Create a subdirectory <code class="file docutils literal notranslate"><span class="pre">tiles</span></code> in your <code class="file docutils literal notranslate"><span class="pre">working</span></code> directory and execute within the <code class="file docutils literal notranslate"><span class="pre">working</span></code> directory:</p>
  130 <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">gdal_retile</span> <span class="o">-</span><span class="n">co</span> <span class="s2">&quot;WORLDFILE=YES&quot;</span>  <span class="o">-</span><span class="n">r</span> <span class="n">bilinear</span> <span class="o">-</span><span class="n">ps</span> <span class="mi">128</span> <span class="mi">128</span> <span class="o">-</span><span class="n">of</span> <span class="n">PNG</span> <span class="o">-</span><span class="n">levels</span> <span class="mi">2</span> <span class="o">-</span><span class="n">targetDir</span> <span class="n">tiles</span> <span class="n">start</span><span class="o">.</span><span class="n">png</span>
  131 </pre></div>
  132 </div>
  133 <p>What is happening ? We tell gdal_retile to create world files for our tiles (-co “WORLDFILE=YES”), use bilinear interpolation (-r bilinear), the tiles are 128x128 pixels in size (-ps 128 128) , the image format should be PNG (-of PNG), we need 2 pyramid levels (-levels 2) ,the directory for the result is <code class="file docutils literal notranslate"><span class="pre">tiles</span></code> (-targetDir tiles) and the source image is <code class="file docutils literal notranslate"><span class="pre">start.png</span></code>.</p>
  134 <div class="admonition note">
  135 <p class="first admonition-title">Note</p>
  136 <p class="last">A few words about the tile size. 128x128 pixel is proper for this example. Do not use such small sizes in a production environment. A size of 256x256 will reduce the number of tiles by a factor of 4, 512x512 by a factor of 16 and so on. Producing too much tiles will degrade performance on the database side (large tables) and will also raise cpu usage on the client side ( more image operations).</p>
  137 </div>
  138 <p>Now you should have the following directories</p>
  139 <ul class="simple">
  140 <li><code class="file docutils literal notranslate"><span class="pre">working</span></code> containing <code class="file docutils literal notranslate"><span class="pre">start.png</span></code> , <code class="file docutils literal notranslate"><span class="pre">start.wld</span></code> and a subdirectory <code class="file docutils literal notranslate"><span class="pre">tiles</span></code>.</li>
  141 <li><code class="file docutils literal notranslate"><span class="pre">working/tiles</span></code> containing many <code class="file docutils literal notranslate"><span class="pre">*.png</span></code> files and associated <code class="file docutils literal notranslate"><span class="pre">*.wld</span></code> files representing the tiles of <code class="file docutils literal notranslate"><span class="pre">start.png</span></code></li>
  142 <li><code class="file docutils literal notranslate"><span class="pre">working/tiles/1</span></code> containing many <code class="file docutils literal notranslate"><span class="pre">*.png</span></code> files and associated <code class="file docutils literal notranslate"><span class="pre">*.wld</span></code> files representing the tiles of the first pyramid</li>
  143 <li><code class="file docutils literal notranslate"><span class="pre">working/tiles/2</span></code> containing many <code class="file docutils literal notranslate"><span class="pre">*.png</span></code> files and associated <code class="file docutils literal notranslate"><span class="pre">*.wld</span></code> files representing the tiles of the second pyramid</li>
  144 </ul>
  145 </div>
  146 <div class="section" id="configuring-the-new-map">
  147 <h2>Configuring the new map<a class="headerlink" href="#configuring-the-new-map" title="Permalink to this headline"></a></h2>
  148 <p>The configuration for a map is done in a xml file. This file has 3 main parts.</p>
  149 <ol class="arabic simple">
  150 <li>The connect info for the jdbc driver</li>
  151 <li>The mapping info for the sql tables</li>
  152 <li>Configuration data for the map</li>
  153 </ol>
  154 <p>Since the jdbc connect info and the sql mapping may be reused by more than one map, the best practice is to create xml fragments for both of them and to use xml entity references to include them into the map xml.</p>
  155 <p>First, find the location of the GEOSERVER_DATA_DIR. This info is contained in the log file when starting GeoServer.:</p>
  156 <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="o">----------------------------------</span>
  157 <span class="o">-</span> <span class="n">GEOSERVER_DATA_DIR</span><span class="p">:</span> <span class="o">/</span><span class="n">home</span><span class="o">/</span><span class="n">mcr</span><span class="o">/</span><span class="n">geoserver</span><span class="o">-</span><span class="mf">1.7</span><span class="o">.</span><span class="n">x</span><span class="o">/</span><span class="mf">1.7</span><span class="o">.</span><span class="n">x</span><span class="o">/</span><span class="n">data</span><span class="o">/</span><span class="n">release</span>
  158 <span class="o">----------------------------------</span>
  159 </pre></div>
  160 </div>
  161 <p>Put all configuration files into the <code class="file docutils literal notranslate"><span class="pre">coverages</span></code> subdirectory of your GeoServer data directory. The location in this example is</p>
  162 <p><code class="file docutils literal notranslate"><span class="pre">/home/mcr/geoserver-1.7.x/1.7.x/data/release/coverages</span></code></p>
  163 <ol class="arabic simple">
  164 <li>Create a file <code class="file docutils literal notranslate"><span class="pre">connect.postgis.xml.inc</span></code> with the following content</li>
  165 </ol>
  166 <div class="highlight-xml notranslate"><div class="highlight"><pre><span></span><span class="nt">&lt;connect&gt;</span>
  167   <span class="c">&lt;!-- value DBCP or JNDI --&gt;</span>
  168   <span class="nt">&lt;dstype</span> <span class="na">value=</span><span class="s">&quot;DBCP&quot;</span><span class="nt">/&gt;</span>
  169   <span class="c">&lt;!--   &lt;jndiReferenceName value=&quot;&quot;/&gt;  --&gt;</span>
  170   <span class="nt">&lt;username</span> <span class="na">value=</span><span class="s">&quot;postgres&quot;</span> <span class="nt">/&gt;</span>
  171   <span class="nt">&lt;password</span> <span class="na">value=</span><span class="s">&quot;postgres&quot;</span> <span class="nt">/&gt;</span>
  172   <span class="nt">&lt;jdbcUrl</span> <span class="na">value=</span><span class="s">&quot;jdbc:postgresql://localhost:5432/gis&quot;</span> <span class="nt">/&gt;</span>
  173   <span class="nt">&lt;driverClassName</span> <span class="na">value=</span><span class="s">&quot;org.postgresql.Driver&quot;</span><span class="nt">/&gt;</span>
  174   <span class="nt">&lt;maxActive</span> <span class="na">value=</span><span class="s">&quot;10&quot;</span><span class="nt">/&gt;</span>
  175   <span class="nt">&lt;maxIdle</span> <span class="na">value=</span><span class="s">&quot;0&quot;</span><span class="nt">/&gt;</span>
  176 <span class="nt">&lt;/connect&gt;</span>
  177 </pre></div>
  178 </div>
  179 <p>The jdbc user is “postgres”, the password is “postgres”, maxActive and maxIdle are parameters of the apache connection pooling, jdbcUrl and driverClassName are postgres specific. The name of the database is “gis”.</p>
  180 <p>If you deploy GeoServer into a J2EE container capable of handling jdbc data sources, a better approach is</p>
  181 <div class="highlight-xml notranslate"><div class="highlight"><pre><span></span><span class="nt">&lt;connect&gt;</span>
  182   <span class="c">&lt;!-- value DBCP or JNDI --&gt;</span>
  183   <span class="nt">&lt;dstype</span> <span class="na">value=</span><span class="s">&quot;JNDI&quot;</span><span class="nt">/&gt;</span>
  184   <span class="nt">&lt;jndiReferenceName</span> <span class="na">value=</span><span class="s">&quot;jdbc/mydatasource&quot;</span><span class="nt">/&gt;</span>
  185 <span class="nt">&lt;/connect&gt;</span>
  186 </pre></div>
  187 </div>
  188 <p>For this tutorial, we do not use data sources provided by a J2EE container.</p>
  189 <ol class="arabic simple" start="2">
  190 <li>The next xml fragment to create is <code class="file docutils literal notranslate"><span class="pre">mapping.postgis.xml.inc</span></code></li>
  191 </ol>
  192 <div class="highlight-xml notranslate"><div class="highlight"><pre><span></span><span class="c">&lt;!-- possible values: universal,postgis,db2,mysql,oracle --&gt;</span>
  193 <span class="nt">&lt;spatialExtension</span> <span class="na">name=</span><span class="s">&quot;postgis&quot;</span><span class="nt">/&gt;</span>
  194 <span class="nt">&lt;mapping&gt;</span>
  195     <span class="nt">&lt;masterTable</span> <span class="na">name=</span><span class="s">&quot;mosaic&quot;</span> <span class="nt">&gt;</span>
  196       <span class="nt">&lt;coverageNameAttribute</span> <span class="na">name=</span><span class="s">&quot;name&quot;</span><span class="nt">/&gt;</span>
  197       <span class="nt">&lt;maxXAttribute</span> <span class="na">name=</span><span class="s">&quot;maxX&quot;</span><span class="nt">/&gt;</span>
  198       <span class="nt">&lt;maxYAttribute</span> <span class="na">name=</span><span class="s">&quot;maxY&quot;</span><span class="nt">/&gt;</span>
  199       <span class="nt">&lt;minXAttribute</span> <span class="na">name=</span><span class="s">&quot;minX&quot;</span><span class="nt">/&gt;</span>
  200       <span class="nt">&lt;minYAttribute</span> <span class="na">name=</span><span class="s">&quot;minY&quot;</span><span class="nt">/&gt;</span>
  201       <span class="nt">&lt;resXAttribute</span> <span class="na">name=</span><span class="s">&quot;resX&quot;</span><span class="nt">/&gt;</span>
  202       <span class="nt">&lt;resYAttribute</span> <span class="na">name=</span><span class="s">&quot;resY&quot;</span><span class="nt">/&gt;</span>
  203       <span class="nt">&lt;tileTableNameAtribute</span>  <span class="na">name=</span><span class="s">&quot;TileTable&quot;</span> <span class="nt">/&gt;</span>
  204       <span class="nt">&lt;spatialTableNameAtribute</span> <span class="na">name=</span><span class="s">&quot;SpatialTable&quot;</span> <span class="nt">/&gt;</span>
  205     <span class="nt">&lt;/masterTable&gt;</span>
  206     <span class="nt">&lt;tileTable&gt;</span>
  207       <span class="nt">&lt;blobAttributeName</span> <span class="na">name=</span><span class="s">&quot;data&quot;</span> <span class="nt">/&gt;</span>
  208       <span class="nt">&lt;keyAttributeName</span> <span class="na">name=</span><span class="s">&quot;location&quot;</span> <span class="nt">/&gt;</span>
  209     <span class="nt">&lt;/tileTable&gt;</span>
  210     <span class="nt">&lt;spatialTable&gt;</span>
  211       <span class="nt">&lt;keyAttributeName</span> <span class="na">name=</span><span class="s">&quot;location&quot;</span> <span class="nt">/&gt;</span>
  212       <span class="nt">&lt;geomAttributeName</span> <span class="na">name=</span><span class="s">&quot;geom&quot;</span> <span class="nt">/&gt;</span>
  213       <span class="nt">&lt;tileMaxXAttribute</span> <span class="na">name=</span><span class="s">&quot;maxX&quot;</span><span class="nt">/&gt;</span>
  214       <span class="nt">&lt;tileMaxYAttribute</span> <span class="na">name=</span><span class="s">&quot;maxY&quot;</span><span class="nt">/&gt;</span>
  215       <span class="nt">&lt;tileMinXAttribute</span> <span class="na">name=</span><span class="s">&quot;minX&quot;</span><span class="nt">/&gt;</span>
  216       <span class="nt">&lt;tileMinYAttribute</span> <span class="na">name=</span><span class="s">&quot;minY&quot;</span><span class="nt">/&gt;</span>
  217     <span class="nt">&lt;/spatialTable&gt;</span>
  218 <span class="nt">&lt;/mapping&gt;</span>
  219 </pre></div>
  220 </div>
  221 <p>The first element <code class="docutils literal notranslate"><span class="pre">&lt;spatialExtension&gt;</span></code> specifies which spatial extension the module should use. “universal” means that there is no spatial db extension at all, meaning the tile grid is not stored as a geometry, using simple double values instead.</p>
  222 <p>This xml fragment describes 3 tables, first we need a master table where information for each pyramid level is saved. Second and third, the attribute mappings for storing image data, envelopes and tile names are specified. To keep this tutorial simple, we will not further discuss these xml elements. After creating the sql tables things will become clear.</p>
  223 <ol class="arabic simple" start="3">
  224 <li>Create the configuration xml <code class="file docutils literal notranslate"><span class="pre">osm.postgis.xml</span></code> for the map (osm for “open street map”)</li>
  225 </ol>
  226 <div class="highlight-xml notranslate"><div class="highlight"><pre><span></span><span class="cp">&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot; standalone=&quot;no&quot;?&gt;</span>
  227 <span class="cp">&lt;!DOCTYPE ImageMosaicJDBCConfig [</span>
  228 <span class="cp">  &lt;!ENTITY mapping PUBLIC &quot;mapping&quot;  &quot;mapping.postgis.xml.inc&quot;&gt;</span>
  229   <span class="cp">&lt;!ENTITY connect PUBLIC &quot;connect&quot;  &quot;connect.postgis.xml.inc&quot;&gt;</span>]&gt;
  230 <span class="nt">&lt;config</span> <span class="na">version=</span><span class="s">&quot;1.0&quot;</span><span class="nt">&gt;</span>
  231   <span class="nt">&lt;coverageName</span> <span class="na">name=</span><span class="s">&quot;osm&quot;</span><span class="nt">/&gt;</span>
  232   <span class="nt">&lt;coordsys</span> <span class="na">name=</span><span class="s">&quot;EPSG:4326&quot;</span><span class="nt">/&gt;</span>
  233   <span class="c">&lt;!-- interpolation 1 = nearest neighbour, 2 = bilinear, 3 = bicubic --&gt;</span>
  234   <span class="nt">&lt;scaleop</span>  <span class="na">interpolation=</span><span class="s">&quot;1&quot;</span><span class="nt">/&gt;</span>
  235   <span class="nt">&lt;verify</span> <span class="na">cardinality=</span><span class="s">&quot;false&quot;</span><span class="nt">/&gt;</span>
  236   <span class="ni">&amp;mapping;</span>
  237   <span class="ni">&amp;connect;</span>
  238 <span class="nt">&lt;/config&gt;</span>
  239 </pre></div>
  240 </div>
  241 <p>This is the final xml configuration file, including our mapping and connect xml fragment. The coverage name is “osm”, CRS is EPSG:4326. <code class="docutils literal notranslate"><span class="pre">&lt;verify</span> <span class="pre">cardinality=&quot;false&quot;&gt;</span></code> means no check if the number of tiles equals the number of rectangles stored in the db. (could be time consuming in case of large tile sets).</p>
  242 <p>This configuration is the hard stuff, now, life becomes easier :-)</p>
  243 </div>
  244 <div class="section" id="using-the-java-ddl-generation-utility">
  245 <h2>Using the java ddl generation utility<a class="headerlink" href="#using-the-java-ddl-generation-utility" title="Permalink to this headline"></a></h2>
  246 <p>The full documentation is here: <a class="reference external" href="http://docs.geotools.org/latest/userguide/library/coverage/jdbc/ddl.html">http://docs.geotools.org/latest/userguide/library/coverage/jdbc/ddl.html</a></p>
  247 <p>To create the proper sql tables, we can use the java ddl generation utility. This utility is included in the <code class="file docutils literal notranslate"><span class="pre">gt-imagemosaic-jdbc-</span><em><span class="pre">version</span></em><span class="pre">.jar</span></code>. Assure that this jar file is in your <code class="file docutils literal notranslate"><span class="pre">WEB-INF/lib</span></code> directory of your GeoServer installation.</p>
  248 <p>Change to your <code class="file docutils literal notranslate"><span class="pre">working</span></code> directory and do a first test:</p>
  249 <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">java</span> <span class="o">-</span><span class="n">jar</span> <span class="o">&lt;</span><span class="n">your_geoserver_install_dir</span><span class="o">&gt;/</span><span class="n">webapps</span><span class="o">/</span><span class="n">geoserver</span><span class="o">/</span><span class="n">WEB</span><span class="o">-</span><span class="n">INF</span><span class="o">/</span><span class="n">lib</span><span class="o">/</span><span class="n">gt</span><span class="o">-</span><span class="n">imagemosaic</span><span class="o">-</span><span class="n">jdbc</span><span class="o">-</span><span class="p">{</span><span class="n">version</span><span class="p">}</span><span class="o">.</span><span class="n">jar</span>
  250 </pre></div>
  251 </div>
  252 <p>The reply should be:</p>
  253 <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Missing</span> <span class="n">cmd</span> <span class="kn">import</span> <span class="o">|</span> <span class="n">ddl</span>
  254 </pre></div>
  255 </div>
  256 <p>Create a subdirectory <code class="file docutils literal notranslate"><span class="pre">sqlscripts</span></code> in your <code class="file docutils literal notranslate"><span class="pre">working</span></code> directory. Within the <code class="file docutils literal notranslate"><span class="pre">working</span></code> directory, execute:</p>
  257 <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">java</span> <span class="o">-</span><span class="n">jar</span> <span class="o">&lt;</span><span class="n">your_geoserver_install_dir</span><span class="o">&gt;/</span><span class="n">webapps</span><span class="o">/</span><span class="n">geoserver</span><span class="o">/</span><span class="n">WEB</span><span class="o">-</span><span class="n">INF</span><span class="o">/</span><span class="n">lib</span><span class="o">/</span><span class="n">gt</span><span class="o">-</span><span class="n">imagemosaic</span><span class="o">-</span><span class="n">jdbc</span><span class="o">-</span><span class="p">{</span><span class="n">version</span><span class="p">}</span><span class="o">.</span><span class="n">jar</span> <span class="n">ddl</span> <span class="o">-</span><span class="n">config</span> <span class="o">&lt;</span><span class="n">your</span> <span class="n">geoserver</span> <span class="n">data</span> <span class="nb">dir</span> <span class="o">&gt;/</span><span class="n">coverages</span><span class="o">/</span><span class="n">osm</span><span class="o">.</span><span class="n">postgis</span><span class="o">.</span><span class="n">xml</span> <span class="o">-</span><span class="n">spatialTNPrefix</span> <span class="n">tileosm</span> <span class="o">-</span><span class="n">pyramids</span> <span class="mi">2</span> <span class="o">-</span><span class="n">statementDelim</span> <span class="s2">&quot;;&quot;</span> <span class="o">-</span><span class="n">srs</span> <span class="mi">4326</span> <span class="o">-</span><span class="n">targetDir</span> <span class="n">sqlscripts</span>
  258 </pre></div>
  259 </div>
  260 <p>Explanation of parameters</p>
  261 <table border="1" class="colwidths-given docutils">
  262 <colgroup>
  263 <col width="20%" />
  264 <col width="80%" />
  265 </colgroup>
  266 <tbody valign="top">
  267 <tr class="row-odd"><td><strong>parameter</strong></td>
  268 <td><strong>description</strong></td>
  269 </tr>
  270 <tr class="row-even"><td>ddl</td>
  271 <td>create ddl statements</td>
  272 </tr>
  273 <tr class="row-odd"><td>-config</td>
  274 <td>the file name of our <code class="file docutils literal notranslate"><span class="pre">osm.postgis.xml</span></code> file</td>
  275 </tr>
  276 <tr class="row-even"><td>-pyramids</td>
  277 <td>number of pyramids we want</td>
  278 </tr>
  279 <tr class="row-odd"><td>-statementDelim</td>
  280 <td>The SQL statement delimiter to use</td>
  281 </tr>
  282 <tr class="row-even"><td>-srs</td>
  283 <td>The db spatial reference identifier when using a spatial extension</td>
  284 </tr>
  285 <tr class="row-odd"><td>-targetDir</td>
  286 <td>output directory for the scripts</td>
  287 </tr>
  288 <tr class="row-even"><td>-spatialTNPrefix</td>
  289 <td>A prefix for tablenames to be created.</td>
  290 </tr>
  291 </tbody>
  292 </table>
  293 <p>In the directory <code class="file docutils literal notranslate"><span class="pre">working/sqlscripts</span></code> you will find the following files after execution:</p>
  294 <p><code class="file docutils literal notranslate"><span class="pre">createmeta.sql</span></code>  <code class="file docutils literal notranslate"><span class="pre">dropmeta.sql</span></code> <code class="file docutils literal notranslate"><span class="pre">add_osm.sql</span></code> <code class="file docutils literal notranslate"><span class="pre">remove_osm.sql</span></code></p>
  295 <div class="admonition note">
  296 <p class="first admonition-title">Note</p>
  297 <p><em>IMPORTANT:</em></p>
  298 <p class="last">Look into the files <code class="file docutils literal notranslate"><span class="pre">createmeta.sql</span></code> and <code class="file docutils literal notranslate"><span class="pre">add_osm.sql</span></code> and compare them with the content of <code class="file docutils literal notranslate"><span class="pre">mapping.postgis.xml.inc</span></code>. If you understand this relationship, you understand the mapping.</p>
  299 </div>
  300 <p>The generated scripts are only templates, it is up to you to modify them for better performance or other reasons. But do not break the relationship to the xml mapping fragment.</p>
  301 </div>
  302 <div class="section" id="executing-the-ddl-scripts">
  303 <h2>Executing the DDL scripts<a class="headerlink" href="#executing-the-ddl-scripts" title="Permalink to this headline"></a></h2>
  304 <p>For user “postgres”, databae “gis”, execute in the following order:</p>
  305 <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">psql</span> <span class="o">-</span><span class="n">U</span> <span class="n">postgres</span> <span class="o">-</span><span class="n">d</span> <span class="n">gis</span>  <span class="o">-</span><span class="n">f</span> <span class="n">createmeta</span><span class="o">.</span><span class="n">sql</span>
  306 <span class="n">psql</span> <span class="o">-</span><span class="n">U</span> <span class="n">postgres</span> <span class="o">-</span><span class="n">d</span> <span class="n">gis</span>  <span class="o">-</span><span class="n">f</span> <span class="n">add_osm</span><span class="o">.</span><span class="n">sql</span>
  307 </pre></div>
  308 </div>
  309 <p>To clean your database, you can execute <code class="file docutils literal notranslate"><span class="pre">remove_osm.sql</span></code> and <code class="file docutils literal notranslate"><span class="pre">dropmeta.sql</span></code> after finishing the tutorial.</p>
  310 </div>
  311 <div class="section" id="importing-the-image-data">
  312 <h2>Importing the image data<a class="headerlink" href="#importing-the-image-data" title="Permalink to this headline"></a></h2>
  313 <p>The full documentation is here: <a class="reference external" href="http://docs.geotools.org/latest/userguide/library/coverage/jdbc/ddl.html">http://docs.geotools.org/latest/userguide/library/coverage/jdbc/ddl.html</a></p>
  314 <p>First, the jdbc jar file has to be in the <code class="file docutils literal notranslate"><span class="pre">lib/ext</span></code> directory of your java runtime. In my case I had to copy <code class="file docutils literal notranslate"><span class="pre">postgresql-8.1-407.jdbc3.jar</span></code>.</p>
  315 <p>Change to the <code class="file docutils literal notranslate"><span class="pre">working</span></code> directory and execute:</p>
  316 <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">java</span> <span class="o">-</span><span class="n">jar</span> <span class="o">&lt;</span><span class="n">your_geoserver_install_dir</span><span class="o">&gt;/</span><span class="n">webapps</span><span class="o">/</span><span class="n">geoserver</span><span class="o">/</span><span class="n">WEB</span><span class="o">-</span><span class="n">INF</span><span class="o">/</span><span class="n">lib</span><span class="o">/</span><span class="n">gt</span><span class="o">-</span><span class="n">imagemosaic</span><span class="o">-</span><span class="n">jdbc</span><span class="o">-</span><span class="p">{</span><span class="n">version</span><span class="p">}</span><span class="o">.</span><span class="n">jar</span> <span class="kn">import</span>  <span class="o">-</span><span class="n">config</span> <span class="o">&lt;</span><span class="n">your</span> <span class="n">geoserver</span> <span class="n">data</span> <span class="nb">dir</span><span class="o">&gt;/</span><span class="n">coverages</span><span class="o">/</span><span class="n">osm</span><span class="o">.</span><span class="n">postgis</span><span class="o">.</span><span class="n">xml</span> <span class="o">-</span><span class="n">spatialTNPrefix</span> <span class="n">tileosm</span> <span class="o">-</span><span class="n">tileTNPrefix</span> <span class="n">tileosm</span> <span class="o">-</span><span class="nb">dir</span> <span class="n">tiles</span> <span class="o">-</span><span class="n">ext</span> <span class="n">png</span>
  317 </pre></div>
  318 </div>
  319 <p>This statement imports your tiles including all pyramids into your database.</p>
  320 </div>
  321 <div class="section" id="configuring-geoserver">
  322 <h2>Configuring GeoServer<a class="headerlink" href="#configuring-geoserver" title="Permalink to this headline"></a></h2>
  323 <p>Start GeoServer and log in.Under <span class="menuselection">Config ‣ WCS ‣ CoveragePlugins</span> you should see</p>
  324 <img alt="../../_images/snapshot1.png" src="../../_images/snapshot1.png" />
  325 <p>If there is no line starting with “ImageMosaicJDBC”, the <code class="file docutils literal notranslate"><span class="pre">gt-imagemosiac-jdbc-</span><em><span class="pre">version</span></em><span class="pre">.jar</span></code> file is not in your <code class="file docutils literal notranslate"><span class="pre">WEB-INF/lib</span></code> folder.
  326 Go to <span class="menuselection">Config‣Data‣CoverageStores‣New</span> and fill in the formular</p>
  327 <img alt="../../_images/snapshot2.png" src="../../_images/snapshot2.png" />
  328 <p>Press <span class="guilabel">New</span> and fill in the formular</p>
  329 <img alt="../../_images/snapshot3.png" src="../../_images/snapshot3.png" />
  330 <p>Press <span class="guilabel">Submit</span>.</p>
  331 <p>Press <span class="guilabel">Apply</span>, then <span class="guilabel">Save</span> to save your changes.</p>
  332 <p>Next select <span class="menuselection">Config‣Data‣Coverages‣New</span> and select “osm”.</p>
  333 <img alt="../../_images/snapshot4.png" src="../../_images/snapshot4.png" />
  334 <p>Press <span class="guilabel">New</span> and you will enter the Coverage Editor. Press <span class="guilabel">Submit</span>, <span class="guilabel">Apply</span> and <span class="guilabel">Save</span>.</p>
  335 <p>Under <span class="menuselection">Welcome‣Demo‣Map Preview</span> you will find a new layer “topp:osm”. Select it and see the results</p>
  336 <img alt="../../_images/snapshot5.png" src="../../_images/snapshot5.png" />
  337 <p>If you think the image is stretched, you are right. The reason is that the original image is georeferenced with EPSG:900913, but there is no support for this CRS in postigs (at the time of this writing). So I used EPSG:4326. For the purpose of this tutorial, this is ok.</p>
  338 </div>
  339 <div class="section" id="conclusion">
  340 <h2>Conclusion<a class="headerlink" href="#conclusion" title="Permalink to this headline"></a></h2>
  341 <p>There are a lot of other configuration possibilities for specific databases. This tutorial shows a quick cookbook to demonstrate some of the features of this module. Follow the links to the full documentation to dig deeper, especially if you are concerned about performance and database design.</p>
  342 <p>If there is something which is missing, proposals are welcome.</p>
  343 </div>
  344 </div>
  345 
  346 
  347       <div class="selfclear pagination-nav">
  348           <div class="leftwise"><strong>Previous</strong>: <a href="../imagepyramid/imagepyramid.html" title="previous chapter">Building and using an image pyramid</a></div>
  349           <div class="rightwise"><strong>Next</strong>: <a href="../feature-pregeneralized/feature-pregeneralized_tutorial.html" title="next chapter">Using the GeoTools feature-pregeneralized module</a></div>
  350       </div>
  351       </div><!-- /#content> -->
  352       <div id="content-right" class="content-border"></div>
  353   <div id="sidebar" class="contrast">
  354       <div id="toc" class="section">
  355         <h3 class="pngfix">Table Of Contents</h3>
  356         <ul>
  357 <li><a class="reference internal" href="#">Storing a coverage in a JDBC database</a><ul>
  358 <li><a class="reference internal" href="#introduction">Introduction</a></li>
  359 <li><a class="reference internal" href="#getting-started">Getting Started</a></li>
  360 <li><a class="reference internal" href="#preparing-the-pyramids-and-the-tiles">Preparing the pyramids and the tiles</a></li>
  361 <li><a class="reference internal" href="#how-many-pyramids-are-needed">How many pyramids are needed ?</a></li>
  362 <li><a class="reference internal" href="#configuring-the-new-map">Configuring the new map</a></li>
  363 <li><a class="reference internal" href="#using-the-java-ddl-generation-utility">Using the java ddl generation utility</a></li>
  364 <li><a class="reference internal" href="#executing-the-ddl-scripts">Executing the DDL scripts</a></li>
  365 <li><a class="reference internal" href="#importing-the-image-data">Importing the image data</a></li>
  366 <li><a class="reference internal" href="#configuring-geoserver">Configuring GeoServer</a></li>
  367 <li><a class="reference internal" href="#conclusion">Conclusion</a></li>
  368 </ul>
  369 </li>
  370 </ul>
  371 
  372         <div class="section-footer"></div>
  373       </div>
  374         <div class="section">
  375           <h3>Continue Reading</h3>
  376           <ul>
  377             <li>Previous: <a href="../imagepyramid/imagepyramid.html" title="previous chapter">Building and using an image pyramid</a></li>
  378             <li>Next: <a href="../feature-pregeneralized/feature-pregeneralized_tutorial.html" title="next chapter">Using the GeoTools feature-pregeneralized module</a></li>
  379           </ul>
  380         </div>
  381         <div class="section">
  382         <h3>This Page</h3>
  383         <ul class="this-page-menu">
  384                 
  385         <li><a href="https://github.com/geoserver/geoserver/tree/master/doc/en/user/source/tutorials/imagemosaic-jdbc/imagemosaic-jdbc_tutorial.rst">Edit</a></li>
  386         </ul>
  387         </div>
  388   </div><!-- /#sidebar -->
  389   </div><!-- /.wrap> -->
  390 </div><!-- /#main -->
  391 <div id="footer">
  392   <div class="wrap">
  393     &copy; Copyright 2019, Open Source Geospatial Foundation. License <a href="http://creativecommons.org/licenses/by/3.0/">Creative Commons Attribution</a>.
  394     Last updated on Nov 22, 2019.
  395     Created using <a href="http://sphinx.pocoo.org/">Sphinx</a>.
  396   </div><!-- /.wrap> -->
  397 </div><!-- /#footer -->
  398   </body>
  399 </html>