Full-text search¶

The full-text search table¶

The text search feature uses a dedicated PostgreSQL table. The full-text search table is named tsearch (for text search) and is in the application-specific schema.

You do not need to create the table yourself, as it was already created during application installation (see the section Install an existing application).

Populate the full-text search table¶

Here is an example of an insertion in the tsearch table:

INSERT INTO app_schema.tsearch
  (the_geom, layer_name, label, public, role_id, lang, ts)
VALUES (
   ST_GeomFromText('POINT(2660000 1140000)', 2056),
   'Group',
   'text to display',
   't',
   NULL,
   'fr',
   to_tsvector('french', 'text to search', ' ', 'g'))
);

Where Layer group is the name of the layer group that should be activated, text to display is the text that is displayed in the results, test to search is the text that we search for, french is the language used.

Here is another example where rows from a SELECT are inserted:

INSERT INTO app_schema.tsearch
  (the_geom, layer_name, label, public, role_id, lang, ts)
SELECT
  geom,
  'layer group name',
  text,
  't',
  NULL,
  'de',
  to_tsvector('german', text, ' ', 'g'))
FROM table;

Note

The language string used as the first argument to the to_tsvector function should match that defined in the default_locale_name variable of the application configuration (vars.yaml). For example, if you have “french” text in the database, the application’s default_locale_name should be fr. In other words, c2cgeoportal assumes that the database language and the application’s default language match.

Populate with the themes¶

A script is available to fill the full-text search table, for more information type:

docker-compose exec geoportal theme2fts --help

Security¶

The tsearch table includes two security-related columns, namely public and role_id. If public is true, then the row is available to any user, including anonymous users. In that case, the role_id column is ignored by c2cgeoportal. If public is false, then the row is not available to anonymous users. If role_id is NULL, the row is available to any authenticated user. If role_id is not NULL, the row is only available to users with the corresponding role.

Note

If you want to restrict some data to several specific roles, then you will need to insert that data multiple times. For example, if you want to make the data of a table text-searchable, and restrict that data to the roles whose ids are 1 and 2, you will use two SQL INSERT statements:

INSERT INTO app_schema.tsearch
   (the_geom, layer_name, label, public, role_id, lang, ts)
SELECT
   geom,
   'layer group name',
   text,
   'f',
   1,
   'de',
   to_tsvector('german', text, ' ', 'g'))
FROM table;

INSERT INTO app_schema.tsearch
   (the_geom, layer_name, label, public, role_id, lang, ts)
SELECT
   geom,
   'layer group name',
   text,
   'f',
   2,
   'de',
   to_tsvector('german', text, ' ', 'g'))
FROM table;

Params¶

The params column can contain a JSON with a dictionary of parameters. For instance to specify a floor:

{
    "floor": "1"
}

Query string floor=1 is then automatically appended to all WMS requests.

Actions¶

The actions column contains a JSON with an array of actions like:

{
    "action": "add_layer",
    "data": "<the_layer_name>"
}

{
    "action": "add_group",
    "data": "<the_group_name>"
}

{
    "action": "add_theme",
    "data": "<the_theme_name>"
}

Example of SQL INSERT of actions data to add the layer “cadastre” on the map:

INSERT INTO app_schema.tsearch (..., actions)
VALUES (..., '[{"action": "add_layer", "data": "cadastre"}]')

Interface¶

If the interface_id column contains a value, it means that the result is only for this interface.

Lang¶

If the lang column contains a value, it means that the result is only for this language.

Configuration¶

In the configuration file vars.yaml you can add the following variables:

fulltextsearch_defaultlimit the default limit on the results, default is 30.
fulltextsearch_maxlimit the max possible limit, default is 200.

Ranking system¶

By default, the full-text search uses the similarity system of the pg_trgm module. This is based only on the similarities of words, without language analysis, and it cares only about how near your search is to the result. 12 is nearer to 12 than 120.

Ensure that the extension is created in you database:

sudo -u postgres psql -c "CREATE EXTENSION pg_trgm" <db_name>

Alternatively, you can use the tsvector and ts_rank_cd to rank your search results (see: textsearch-controls). These methods are useful to handle language-based strings. That means for instance that plural nouns are the same as singular nouns. This system only checks if your search word exists in the result. That means that if you search B 12 Zug, B 120 Zug has the same weight because the system only see that the 12 exists in each case. To use this system, your request must contain the parameter rank_system=ts_rank_cd.

Using the unaccent extension¶

The full-text search is accent-sensitive by default. To make it accent-insensitive Postgres’s unaccent extension can be used.

To activate the unaccent extension, first connect to the database:

sudo -u postgres psql -d <database>

Create the Postgres unaccent extension and dictionary:

CREATE EXTENSION unaccent;

Insert the unaccent dictionary into a text search configuration (Documentation):

CREATE TEXT SEARCH CONFIGURATION fr (COPY = french);
ALTER TEXT SEARCH CONFIGURATION fr
    ALTER MAPPING FOR hword, hword_part, word
    WITH unaccent, french_stem;

When populating the tsearch table use the text configuration ‘fr’ instead of ‘french’. For example:

INSERT INTO <schema>.tsearch
  (the_geom, layer_name, label, public, role_id, ts)
VALUES
  (
    ST_GeomFromText('POINT(2660000 1140000)', 2056), 'Layer group',
    'Accent text to display (éàè)', 't', NULL,
    to_tsvector('fr', 'Accent text to search (éàè)')
  );

And define the configuration in the vars.yaml file:

fulltextsearch:
    languages:
        fr: fr

fr: fr is a link between the pyramid language and the text search configuration, by default it is fr: french because the default french text search configuration is named ‘french’.

Synonym and Thesaurus Dictionary¶

You may wish to avoid using the Synonym and Thesaurus dictionaries, because when these are used, a word like ‘alignement’ is simplified as ‘align’:

SELECT to_tsvector('fr', 'alignement');
'align':1

Thus, ‘alignem’ does not match in the search, which might be considered unexpected behavior by users:

SELECT to_tsquery('fr', 'alignem:*');
'alignem':*

To change this behavior, you can create and use a new dictionary named french_alt:

CREATE TEXT SEARCH DICTIONARY french_alt (TEMPLATE = pg_catalog.simple);
ALTER TEXT SEARCH DICTIONARY french_alt (STOPWORDS = french);
ALTER TEXT SEARCH CONFIGURATION fr ALTER MAPPING FOR asciiword WITH french_alt;

Note

We keep the stop words to remove the French short words.

Full-text search¶

The full-text search table¶

Populate the full-text search table¶

Populate with the themes¶

Security¶

Params¶

Actions¶

Interface¶

Lang¶

Configuration¶

Ranking system¶

Using the unaccent extension¶

Synonym and Thesaurus Dictionary¶

c2cgeoportal

Navigation

Related Topics