<div dir="ltr"><div>Hello,</div><div><br></div><div>Yes it is.<br></div><div>I followed the documentation here - <a href="https://docs.lizmap.com/current/en/publish/configuration/spatial_search.html#postgresql-search">https://docs.lizmap.com/current/en/publish/configuration/spatial_search.html#postgresql-search</a></div><div><br></div><div>My setup is using docker-compose</div><div><br></div><div>In my spatial database:</div><div><pre><span class="gmail-c1">-- Add the extension pg_trgm</span>
<span class="gmail-k">CREATE</span> <span class="gmail-k">EXTENSION</span> <span class="gmail-k">IF</span> <span class="gmail-k">NOT</span> <span class="gmail-k">EXISTS</span> <span class="gmail-n">pg_trgm</span><span class="gmail-p">;</span>
<span class="gmail-c1">-- Add the extension unaccent, available with PostgreSQL contrib tools. This is needed to provide searches which are not sensitive to accentuated characters.</span>
<span class="gmail-k">CREATE</span> <span class="gmail-k">EXTENSION</span> <span class="gmail-k">IF</span> <span class="gmail-k">NOT</span> <span class="gmail-k">EXISTS</span> <span class="gmail-n">unaccent</span><span class="gmail-p">;</span>
<span class="gmail-c1">-- Add the f_unaccent function to be used in the index</span>
<span class="gmail-k">CREATE</span> <span class="gmail-k">OR</span> <span class="gmail-k">REPLACE</span> <span class="gmail-k">FUNCTION</span> <span class="gmail-n">public</span><span class="gmail-mf">.</span><span class="gmail-n">f_unaccent</span><span class="gmail-p">(</span><span class="gmail-nb">text</span><span class="gmail-p">)</span>
<span class="gmail-k">RETURNS</span> <span class="gmail-nb">text</span> <span class="gmail-k">AS</span>
<span class="gmail-s">$</span><span class="gmail-dl">func</span><span class="gmail-s">$</span>
<span class="gmail-k">SELECT</span> <span class="gmail-n">public</span><span class="gmail-mf">.</span><span class="gmail-n">unaccent</span><span class="gmail-p">(</span><span class="gmail-s1">'public.unaccent'</span><span class="gmail-p">,</span> <span class="gmail-nv">$1</span><span class="gmail-p">)</span> <span class="gmail-c1">-- schema-qualify function and dictionary</span>
<span class="gmail-s">$</span><span class="gmail-dl">func</span><span class="gmail-s">$</span> <span class="gmail-k">LANGUAGE</span> <span class="gmail-n">sql</span> <span class="gmail-k">IMMUTABLE</span><span class="gmail-p">;<br><br></span></pre><pre><span class="gmail-p">the I ran this:<br> replace Commune with the name of you want to use for item_layer<br></span></pre><pre><span class="gmail-p"> replace the parameters for concat(fielda, ' - ', fieldb) - I am sure you can concatenate more than two fields. If it is a single field then fieldname AS item_label<br></span></pre><pre><span class="gmail-p"> replace cadastre.geo_commume with own table (qualify with schema name if not in public schema)<br></span></pre><pre><span class="gmail-p">You will use UNION ALL if you have another table you want to search on - can read this about UNION ALL <a href="https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-union/">https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-union/</a></span></pre><pre><span class="gmail-p"><span class="gmail-k">DROP</span> <span class="gmail-n">MATERIALIZED</span> <span class="gmail-k">VIEW</span> <span class="gmail-k">IF</span> <span class="gmail-k">EXISTS</span> <span class="gmail-n">lizmap_search</span><span class="gmail-p">;</span>
<span class="gmail-k">CREATE</span> <span class="gmail-n">MATERIALIZED</span> <span class="gmail-k">VIEW</span> <span class="gmail-n">lizmap_search</span> <span class="gmail-k">AS</span>
<span class="gmail-k">SELECT</span>
<span class="gmail-s1">'Commune'</span> <span class="gmail-k">AS</span> <span class="gmail-n">item_layer</span><span class="gmail-p">,</span> <span class="gmail-c1">-- name of the layer presented to the user</span>
<span class="gmail-n">concat</span><span class="gmail-p">(</span><span class="gmail-n">idu</span><span class="gmail-p">,</span> <span class="gmail-s1">' - '</span><span class="gmail-p">,</span> <span class="gmail-n">tex2</span><span class="gmail-p">)</span> <span class="gmail-k">AS</span> <span class="gmail-n">item_label</span><span class="gmail-p">,</span> <span class="gmail-c1">-- the search label is a concatenation between the 'Commune' code (idu) and its name (tex2)</span>
<span class="gmail-k">NULL</span> <span class="gmail-k">AS</span> <span class="gmail-n">item_filter</span><span class="gmail-p">,</span> <span class="gmail-c1">-- the data will be searchable for every Lizmap user</span>
<span class="gmail-k">NULL</span> <span class="gmail-k">AS</span> <span class="gmail-n">item_project</span><span class="gmail-p">,</span> <span class="gmail-c1">-- the data will be searchable for every Lizmap maps (published QGIS projects)</span>
<span class="gmail-n">geom</span> <span class="gmail-c1">-- geometry of the 'Commune'. You could also use a simplified version, for example: ST_Envelope(geom) AS geom</span>
<span class="gmail-k">FROM</span> <span class="gmail-n">cadastre</span><span class="gmail-p">.</span><span class="gmail-n">geo_commune</span>
<span class="gmail-k">UNION</span> <span class="gmail-k">ALL</span> <span class="gmail-c1">-- combine the data between the 'Commune' (above) and the 'Parcelles' (below) tables</span>
<span class="gmail-k">SELECT</span>
<span class="gmail-s1">'Parcelles'</span> <span class="gmail-k">AS</span> <span class="gmail-n">item_layer</span><span class="gmail-p">,</span>
<span class="gmail-n">concat</span><span class="gmail-p">(</span><span class="gmail-n">code</span><span class="gmail-p">,</span> <span class="gmail-s1">' - '</span><span class="gmail-p">,</span> <span class="gmail-n">proprietaire</span><span class="gmail-p">)</span> <span class="gmail-k">AS</span> <span class="gmail-n">item_label</span><span class="gmail-p">,</span>
<span class="gmail-s1">'admins'</span> <span class="gmail-k">AS</span> <span class="gmail-n">item_filter</span><span class="gmail-p">,</span> <span class="gmail-c1">-- only users in the admins Lizmap group will be able to search among the 'Parcelles'</span>
<span class="gmail-s1">'cadastre,urban'</span> <span class="gmail-k">AS</span> <span class="gmail-n">item_project</span><span class="gmail-p">,</span> <span class="gmail-c1">-- the Parcelles will be available in search only for the cadastre.qgs and urban.qgs QGIS projects</span>
<span class="gmail-n">geom</span>
<span class="gmail-k">FROM</span> <span class="gmail-n">cadastre</span><span class="gmail-p">.</span><span class="gmail-n">parcelle_info</span>
<span class="gmail-p">;<br><br></span></span></pre><pre><span class="gmail-p"><span class="gmail-p">Then create an index<br></span></span></pre></div><div><pre><span class="gmail-c1">-- Create the index on the unaccentuated item_label column:</span>
<span class="gmail-k">DROP</span> <span class="gmail-k">INDEX</span> <span class="gmail-k">IF</span> <span class="gmail-k">EXISTS</span> <span class="gmail-n">lizmap_search_idx</span><span class="gmail-p">;</span>
<span class="gmail-k">CREATE</span> <span class="gmail-k">INDEX</span> <span class="gmail-n">lizmap_search_idx</span> <span class="gmail-k">ON</span> <span class="gmail-n">lizmap_search</span> <span class="gmail-k">USING</span> <span class="gmail-n">GIN</span> <span class="gmail-p">(</span><span class="gmail-n">f_unaccent</span><span class="gmail-p">(</span><span class="gmail-n">item_label</span><span class="gmail-p">)</span> <span class="gmail-n">gin_trgm_ops</span><span class="gmail-p">);<br><br><br>Add a new <strong>database connection profile</strong> in Lizmap configuration file <code class="gmail-file gmail-docutils gmail-literal gmail-notranslate"><span class="gmail-pre">lizmap/var/config/profiles.ini.php<br><br></span></code></span></pre><pre><span class="gmail-p"><code class="gmail-file gmail-docutils gmail-literal gmail-notranslate"><span class="gmail-pre">Hope I have been helpful.<br><br></span></code></span></pre><pre><span class="gmail-p"><code class="gmail-file gmail-docutils gmail-literal gmail-notranslate"><span class="gmail-pre">Regards,<br></span></code></span></pre><pre><span class="gmail-p"><code class="gmail-file gmail-docutils gmail-literal gmail-notranslate"><span class="gmail-pre">Sindile<br></span></code></span></pre><pre><span class="gmail-p"><code class="gmail-file gmail-docutils gmail-literal gmail-notranslate"><span class="gmail-pre"><br></span></code></span></pre><pre><span class="gmail-p"><code class="gmail-file gmail-docutils gmail-literal gmail-notranslate"><span class="gmail-pre"></span></code></span></pre></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sat, 11 Jun 2022 at 16:41, G. Wagner, Wagner-IT <<a href="mailto:info@wagner-it.de">info@wagner-it.de</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div style="overflow-wrap: break-word;" lang="DE"><div class="gmail-m_-6458174021995393170WordSection1"><p class="MsoNormal">Hi Sindile,<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">I have no answer to this question, but I have a question about the search:<u></u><u></u></p><p class="MsoNormal">Is this the SQL search (Postgres)?<u></u><u></u></p><p class="MsoNormal">I'm trying to implement this for a long time, unfortunately without success.<u></u><u></u></p><p class="MsoNormal"><a href="https://lists.osgeo.org/pipermail/lizmap/2022-May/000455.html" target="_blank">https://lists.osgeo.org/pipermail/lizmap/2022-May/000455.html</a><u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">Do you have any tips / hints for me?<u></u><u></u></p><p class="MsoNormal">Or do you possibly even have a small sample database?<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">Regards,<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">Günter<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal"><span><u></u> <u></u></span></p><div style="border-color:rgb(225,225,225) currentcolor currentcolor;border-style:solid none none;border-width:1pt medium medium;padding:3pt 0cm 0cm"><p class="MsoNormal"><b>Von:</b> Lizmap <<a href="mailto:lizmap-bounces@lists.osgeo.org" target="_blank">lizmap-bounces@lists.osgeo.org</a>> <b>Im Auftrag von </b>Sindile Bidla<br><b>Gesendet:</b> Samstag, 11. Juni 2022 13:19<br><b>An:</b> <a href="mailto:lizmap@lists.osgeo.org" target="_blank">lizmap@lists.osgeo.org</a><br><b>Betreff:</b> [Lizmap] Behaviour of search in Lizmap<u></u><u></u></p></div><p class="MsoNormal"><u></u> <u></u></p><div><div><p class="MsoNormal">Hello,<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">I have implemented lizmap_search and it is working.<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">I am not sure though if auto-complete (like type ahead) is not available.<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">The current workflow I use is:<u></u><u></u></p></div><div><p class="MsoNormal">1. type the search string<u></u><u></u></p></div><div><p class="MsoNormal">2. hit enter (no results show until one hits enter)<u></u><u></u></p></div><div><p class="MsoNormal">3. choose the correct value<u></u><u></u></p></div><div><p class="MsoNormal"><img style="width: 3.825in; height: 2.1in;" id="gmail-m_-6458174021995393170Bild_x0020_1" src="cid:181534a68814cff311" width="367" height="202" border="0"><u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Is the above the default behaviour<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">I was expecting a behaviour similar to Locating.<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Regards,<u></u><u></u></p></div><div><p class="MsoNormal">Sindile<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal"> <u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div></div></div></div></blockquote></div>