<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Vorformatiert Zchn";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.HTMLVorformatiertZchn
{mso-style-name:"HTML Vorformatiert Zchn";
mso-style-priority:99;
mso-style-link:"HTML Vorformatiert";
font-family:Consolas;
mso-fareast-language:DE;}
span.gmail-c1
{mso-style-name:gmail-c1;}
span.gmail-k
{mso-style-name:gmail-k;}
span.gmail-n
{mso-style-name:gmail-n;}
span.gmail-p
{mso-style-name:gmail-p;}
span.gmail-mf
{mso-style-name:gmail-mf;}
span.gmail-nb
{mso-style-name:gmail-nb;}
span.gmail-s
{mso-style-name:gmail-s;}
span.gmail-dl
{mso-style-name:gmail-dl;}
span.gmail-s1
{mso-style-name:gmail-s1;}
span.gmail-nv
{mso-style-name:gmail-nv;}
span.gmail-pre
{mso-style-name:gmail-pre;}
span.E-MailFormatvorlage33
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=DE link=blue vlink=purple style='word-wrap:break-word'><div class=WordSection1><p class=MsoNormal><span style='mso-fareast-language:EN-US'>Hi Sindile,<o:p></o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'>Thank you very much for the detailed explanations.<o:p></o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'>I will test it in the next few days.<o:p></o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'>Regards,<o:p></o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'>Günter<o:p></o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b>Von:</b> Sindile Bidla <sindile.bidla@geoilocate.co.za> <br><b>Gesendet:</b> Samstag, 11. Juni 2022 17:25<br><b>An:</b> G. Wagner, Wagner-IT <info@wagner-it.de>; lizmap@lists.osgeo.org<br><b>Betreff:</b> Re: [Lizmap] Behaviour of search in Lizmap<o:p></o:p></p></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>Hello,<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Yes it is.<o:p></o:p></p></div><div><p class=MsoNormal>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><o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>My setup is using docker-compose<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>In my spatial database:<o:p></o:p></p></div><div><pre><span class=gmail-c1>-- Add the extension pg_trgm</span><o:p></o:p></pre><pre><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><o:p></o:p></pre><pre><o:p> </o:p></pre><pre><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><o:p></o:p></pre><pre><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><o:p></o:p></pre><pre><o:p> </o:p></pre><pre><span class=gmail-c1>-- Add the f_unaccent function to be used in the index</span><o:p></o:p></pre><pre><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><o:p></o:p></pre><pre><span class=gmail-k>RETURNS</span> <span class=gmail-nb>text</span> <span class=gmail-k>AS</span><o:p></o:p></pre><pre><span class=gmail-s>$</span><span class=gmail-dl>func</span><span class=gmail-s>$</span><o:p></o:p></pre><pre><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><o:p></o:p></pre><pre style='margin-bottom:12.0pt'><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>;</span><o:p></o:p></pre><pre><span class=gmail-p>the I ran this:</span><br><span class=gmail-p> replace Commune with the name of you want to use for item_layer</span><o:p></o:p></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</span><o:p></o:p></pre><pre><span class=gmail-p> replace cadastre.geo_commume with own table (qualify with schema name if not in public schema)</span><o:p></o:p></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><o:p></o:p></pre><pre><span class=gmail-k>DROP</span><span class=gmail-p> </span><span class=gmail-n>MATERIALIZED</span><span class=gmail-p> </span><span class=gmail-k>VIEW</span><span class=gmail-p> </span><span class=gmail-k>IF</span><span class=gmail-p> </span><span class=gmail-k>EXISTS</span><span class=gmail-p> </span><span class=gmail-n>lizmap_search</span><span class=gmail-p>;<o:p></o:p></span></pre><pre><span class=gmail-k>CREATE</span><span class=gmail-p> </span><span class=gmail-n>MATERIALIZED</span><span class=gmail-p> </span><span class=gmail-k>VIEW</span><span class=gmail-p> </span><span class=gmail-n>lizmap_search</span><span class=gmail-p> </span><span class=gmail-k>AS</span><span class=gmail-p><o:p></o:p></span></pre><pre><span class=gmail-k>SELECT</span><span class=gmail-p><o:p></o:p></span></pre><pre><span class=gmail-p> </span><span class=gmail-s1>'Commune'</span><span class=gmail-p> </span><span class=gmail-k>AS</span><span class=gmail-p> </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-p><o:p></o:p></span></pre><pre><span class=gmail-p> </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-p> </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-p><o:p></o:p></span></pre><pre><span class=gmail-p> </span><span class=gmail-k>NULL</span><span class=gmail-p> </span><span class=gmail-k>AS</span><span class=gmail-p> </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-p><o:p></o:p></span></pre><pre><span class=gmail-p> </span><span class=gmail-k>NULL</span><span class=gmail-p> </span><span class=gmail-k>AS</span><span class=gmail-p> </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-p><o:p></o:p></span></pre><pre><span class=gmail-p> </span><span class=gmail-n>geom</span><span class=gmail-p> </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-p><o:p></o:p></span></pre><pre><span class=gmail-k>FROM</span><span class=gmail-p> </span><span class=gmail-n>cadastre</span><span class=gmail-p>.</span><span class=gmail-n>geo_commune</span><span class=gmail-p><o:p></o:p></span></pre><pre><span class=gmail-k>UNION</span><span class=gmail-p> </span><span class=gmail-k>ALL</span><span class=gmail-p> </span><span class=gmail-c1>-- combine the data between the 'Commune' (above) and the 'Parcelles' (below) tables</span><span class=gmail-p><o:p></o:p></span></pre><pre><span class=gmail-k>SELECT</span><span class=gmail-p><o:p></o:p></span></pre><pre><span class=gmail-p> </span><span class=gmail-s1>'Parcelles'</span><span class=gmail-p> </span><span class=gmail-k>AS</span><span class=gmail-p> </span><span class=gmail-n>item_layer</span><span class=gmail-p>,<o:p></o:p></span></pre><pre><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-p> </span><span class=gmail-n>item_label</span><span class=gmail-p>,<o:p></o:p></span></pre><pre><span class=gmail-p> </span><span class=gmail-s1>'admins'</span><span class=gmail-p> </span><span class=gmail-k>AS</span><span class=gmail-p> </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-p><o:p></o:p></span></pre><pre><span class=gmail-p> </span><span class=gmail-s1>'cadastre,urban'</span><span class=gmail-p> </span><span class=gmail-k>AS</span><span class=gmail-p> </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-p><o:p></o:p></span></pre><pre><span class=gmail-p> </span><span class=gmail-n>geom</span><span class=gmail-p><o:p></o:p></span></pre><pre><span class=gmail-k>FROM</span><span class=gmail-p> </span><span class=gmail-n>cadastre</span><span class=gmail-p>.</span><span class=gmail-n>parcelle_info</span><span class=gmail-p><o:p></o:p></span></pre><pre style='margin-bottom:12.0pt'><span class=gmail-p>;</span><o:p></o:p></pre><pre><span class=gmail-p>Then create an index</span><o:p></o:p></pre></div><div><pre><span class=gmail-c1>-- Create the index on the unaccentuated item_label column:</span><o:p></o:p></pre><pre><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><o:p></o:p></pre><pre style='margin-bottom:12.0pt'><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>);</span><br><br><br><span class=gmail-p>Add a new </span><strong><span style='font-family:"Courier New"'>database connection profile</span></strong><span class=gmail-p> in Lizmap configuration file </span><span class=gmail-pre>lizmap/var/config/profiles.ini.php</span><o:p></o:p></pre><pre style='margin-bottom:12.0pt'><span class=gmail-pre>Hope I have been helpful.</span><o:p></o:p></pre><pre><span class=gmail-pre>Regards,</span><o:p></o:p></pre><pre><span class=gmail-pre>Sindile</span><o:p></o:p></pre><pre><o:p> </o:p></pre></div><div><p class=MsoNormal><o:p> </o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>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:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm'><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Hi Sindile,<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I have no answer to this question, but I have a question about the search:<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Is this the SQL search (Postgres)?<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I'm trying to implement this for a long time, unfortunately without success.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><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><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Do you have any tips / hints for me?<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Or do you possibly even have a small sample database?<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Regards,<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Günter<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div style='border:none;border-top:solid windowtext 1.0pt;padding:3.0pt 0cm 0cm 0cm;border-color:currentcolor currentcolor'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><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<o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Hello,<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I have implemented lizmap_search and it is working.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I am not sure though if auto-complete (like type ahead) is not available.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>The current workflow I use is:<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>1. type the search string<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>2. hit enter (no results show until one hits enter)<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>3. choose the correct value<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><img border=0 width=367 height=202 style='width:3.825in;height:2.1in' id="gmail-m_-6458174021995393170Bild_x0020_1" src="cid:image001.png@01D87E98.8C37DE30"><o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Is the above the default behaviour<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I was expecting a behaviour similar to Locating.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Regards,<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Sindile<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div></div></div></div></blockquote></div></div></body></html>