Database

Schemas and functions

In order for the API to work properly, some functions and schemas detailed below are required.

Functions

In order for the API to work correctly, two functions are needed:

  • remove_accents to remove all accents from strings when indexing the search string.
  • quadindex to calculate the quadindex for a geometry. This is used to filter search results.

You can find the code below and in the functions.sql script:

CREATE OR REPLACE FUNCTION public.remove_accents(string character varying)
  RETURNS character varying AS
$BODY$
    DECLARE
        res varchar;
    BEGIN
        res := replace(string, 'ü', 'ue');
        res := replace(res, 'Ü', 'ue');
        res := replace(res, 'ä', 'ae');
        res := replace(res, 'Ä', 'ae');
        res := replace(res, 'ö', 'oe');
        res := replace(res, 'Ö', 'oe');
        res := replace(res, '(', '_');
        res := replace(res, ')', '_');
        res:= translate(res, 'àáâÀÁÂ', 'aaaaaa');
        res:= translate(res, 'èéêëÈÉÊË', 'eeeeeeee');
        res:= translate(res, 'ìíîïÌÍÎÏ', 'iiiiiiii');
        res:= translate(res, 'òóôÒÓÔ', 'oooooo');
        res:= translate(res, 'ùúûÙÚÛ', 'uuuuuu');
        res:= translate(res, 'ç', 'c');
        RETURN trim(lower(res));
    END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;
CREATE OR REPLACE FUNCTION public.quadindex(geom geometry)
  RETURNS text AS
$BODY$
DECLARE
    x_min       double precision;
    y_max       double precision;
    width       double precision;
    height      double precision;
    quadindex   text;
    quadcurrent text;
    bbox    geometry;
    quadgeom    geometry;
    quadgeom_0  geometry;
    quadgeom_1  geometry;
    quadgeom_2  geometry;
    quadgeom_3  geometry;
    maxlevels   integer;

BEGIN
    x_min       := 420000::double precision;
    y_max       := 510000::double precision;
    width       := 480000::double precision;
    height      := 480000::double precision;
    maxlevels   := 20; -- resolution 0.45m
    quadindex   := '0'; -- origin quadindex node address default to 0

    /*
                   510000
          +-------+-------+
          |       |       |
          |       |       |
          |   0   |   1   |
          |       |       |
   420000 +-------+-------+ 900000
          |       |       |
          |       |       |
          |   2   |   3   |
          |       |       |
          +-------+-------+
                30000
    */

    FOR  i IN 1..maxlevels LOOP
        quadgeom = st_geometryfromtext('LINESTRING('||x_min||' '||y_max||','||(x_min+width)||' '||(y_max-height)||')',21781);
            IF  NOT geom @ quadgeom THEN
        EXIT;
        END IF;


        width := width/2;
        height:= height/2;
        quadgeom_0 = st_geometryfromtext('LINESTRING('||x_min||' '||y_max||','||(x_min+width)||' '||(y_max-height)||')',21781);
        quadgeom_1 = st_geometryfromtext('LINESTRING('||(x_min+width)||' '||y_max||','||(x_min+(2*width))||' '||(y_max-height)||')',21781);
        quadgeom_2 = st_geometryfromtext('LINESTRING('||x_min||' '||y_max-height||','||(x_min+width)||' '||(y_max-(2*height))||')',21781);
        quadgeom_3 = st_geometryfromtext('LINESTRING('||(x_min+width)||' '||(y_max-height)||','||(x_min+2*width)||' '||(y_max-(2*height))||')',21781);


        CASE
        WHEN geom @ quadgeom_0 THEN
            quadindex   := quadindex||'0';
        WHEN geom @ quadgeom_1 THEN
            x_min       := x_min+width;
            quadindex   := quadindex||'1';
        WHEN geom @ quadgeom_2 THEN
            y_max       := y_max-height;
            quadindex   := quadindex||'2';
        WHEN geom @ quadgeom_3 THEN
            x_min       := x_min+width;
            y_max       := y_max-height;
            quadindex   := quadindex||'3';
        ELSE
            EXIT;
         END CASE;
        i := i+1;
    END LOOP;
    return ( quadindex );
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Schemas

In order for the API to work correctly, you will need the following schemas:

Schema api3

It is used by the API to store the shorten links and the id, type and access time of drawings. It must contains two tables:

  1. url_shortener
CREATE TABLE api3.url_shortener
(
  short_url character varying(10) NOT NULL, -- The original URL with protocal and host
  url character varying(2048) NOT NULL,
  createtime timestamp without time zone NOT NULL, -- The timestamp of creation
  accesstime timestamp without time zone NOT NULL,
  portal character varying(200) DEFAULT NULL::character varying,
  CONSTRAINT url_shortener_pkey PRIMARY KEY (short_url)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE api3.url_shortener
  OWNER TO geo_dba;
GRANT ALL ON TABLE api3.url_shortener TO geo_dba;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE api3.url_shortener TO geo_api3;
COMMENT ON TABLE api3.url_shortener
  IS 'Storage for url';
COMMENT ON COLUMN api3.url_shortener.short_url IS 'The original URL with protocal and host';
COMMENT ON COLUMN api3.url_shortener.createtime IS 'The timestamp of creation';
  1. files
CREATE TABLE api3.files
(
  admin_id character varying(24) NOT NULL,
  file_id character varying(24) NOT NULL,
  mime_type character varying(50) NOT NULL, -- The MIME Type of the stored file.
  createtime timestamp without time zone NOT NULL DEFAULT now(),
  accesstime timestamp without time zone NOT NULL,
  portal character varying(200) DEFAULT NULL::character varying,
  CONSTRAINT pk_file_id PRIMARY KEY (admin_id, file_id),
  CONSTRAINT files_admin_id_key UNIQUE (admin_id),
  CONSTRAINT files_file_id_key UNIQUE (file_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE api3.files
  OWNER TO geo_dba;
GRANT ALL ON TABLE api3.files TO geo_dba;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE api3.files TO geo_api3;
COMMENT ON TABLE api3.files
  IS 'Link between file and admin ids.';
COMMENT ON COLUMN api3.files.mime_type IS 'The MIME Type of the stored file.';

Schema features

It is used by the API to make features request. It must at least contain the map_layers_features table described below. See the features section of this document to learn more.

CREATE TABLE features.map_layers_features
(
  feature character varying(200) NOT NULL,
  portal_names character varying(200)[] NOT NULL,
  layer_names character varying(200)[] NOT NULL,
  CONSTRAINT map_layers_features_pkey PRIMARY KEY (feature)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE features.map_layers_features
  OWNER TO geo_dba;
GRANT ALL ON TABLE features.map_layers_features TO geo_dba;
GRANT SELECT, REFERENCES, TRIGGER ON TABLE features.map_layers_features TO geo_api3;

Optional schemas

We also advise you to create the schemas below:

Users and permissions

In order to give only the permissions on the database needed by the different components, we suggest that you create the users below:

  1. A role for admin tasks: geo_dba:

    CREATE ROLE geo_dba LOGIN
      PASSWORD 'GeoDba'
      SUPERUSER INHERIT CREATEDB NOCREATEROLE NOREPLICATION;
    COMMENT ON ROLE geo_dba IS 'superuser for geoportal';
    
  2. A role for MapServer: geo_mapserver

    CREATE ROLE geo_mapserver LOGIN
      PASSWORD 'GeoMap'
      NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
    COMMENT ON ROLE geo_mapserver IS 'geoportal role for mapserver';
    
  3. A role for sphinx search: geo_searchd

    CREATE ROLE geo_searchd LOGIN
      PASSWORD 'GeoSearchd'
      NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
    COMMENT ON ROLE geo_searchd IS 'geoportal user for sphinx searchd daemon';
    
  4. A role for the API: geo_api

    CREATE ROLE geo_api LOGIN
      PASSWORD 'GeoApi'
      NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
    COMMENT ON ROLE geo_api IS 'geoportal wsgi user';
    

Each username and password can be changed, it just has to be coherent with the documentation. You can override:

  1. geo_searchd in search.sphinx_sql_user and search.sphinx_sql_pass from customer-infra/config/_commmon.dist.toml
  2. geo_mapserver in mapserver.PORTAL_DB_USER and mapserver.PORTAL_DB_PASSWORD from customer-infra/config/_commmon.dist.toml (or directly in your Map files)
  3. geo_api in db.host from geo-api3/config/config.<branchname>.toml

You set the permissions for each users with manuel db-grant-update. Be sure that the values of DEFAULT_DB_OWNER, DEFAULT_DB_MAPSERVER_ROLE, DEFAULT_DB_SEARCH_ROLE, DEFAULT_DB_API_ROLE, DEFAULT_DB_HOST and DEFAULT_DB_NAME in customer-infra/config/config.dist.sh are correct. To override some values only temporarily, use manuel help db-grant-update to the what arguments are available for this task. You can also run the script below on the proper database (replace DEFAULT_DB_OWNER, DEFAULT_DB_MAPSERVER_ROLE, DEFAULT_DB_SEARCH_ROLE, DEFAULT_DB_API_ROLE by their correct values):

--
-- run this script with DEFAULT_DB_OWNER
--


 GRANT ALL ON SCHEMA userdata TO DEFAULT_DB_OWNER;
 GRANT ALL ON SCHEMA api3 TO DEFAULT_DB_OWNER;
 GRANT ALL ON SCHEMA features TO DEFAULT_DB_OWNER;
 GRANT ALL ON SCHEMA search TO DEFAULT_DB_OWNER;
-- Fix the error of ERROR:  grant options cannot be granted back to your own grantor
-- GRANT ALL ON SCHEMA public TO DEFAULT_DB_OWNER WITH GRANT OPTION;
-- GRANT ALL ON SCHEMA topology TO DEFAULT_DB_OWNER WITH GRANT OPTION;
 GRANT ALL ON SCHEMA public TO DEFAULT_DB_OWNER;
 GRANT ALL ON SCHEMA topology TO DEFAULT_DB_OWNER;
 GRANT SELECT, REFERENCES, TRIGGER ON TABLE public.spatial_ref_sys TO DEFAULT_DB_OWNER;
 GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE public.geography_columns TO DEFAULT_DB_OWNER;
 GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE public.geometry_columns TO DEFAULT_DB_OWNER;
 GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE public.raster_columns TO DEFAULT_DB_OWNER;
 GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE public.raster_overviews TO DEFAULT_DB_OWNER;

 GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA topology TO DEFAULT_DB_OWNER;
 GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA topology TO DEFAULT_DB_OWNER;


-- mapserver user
-- Read Only access on public & userdata

 REVOKE ALL ON SCHEMA public FROM DEFAULT_DB_MAPSERVER_ROLE;
 GRANT USAGE ON SCHEMA public TO DEFAULT_DB_MAPSERVER_ROLE;
 REVOKE ALL ON ALL TABLES IN SCHEMA public FROM DEFAULT_DB_MAPSERVER_ROLE;
 GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO DEFAULT_DB_MAPSERVER_ROLE;
 REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public FROM DEFAULT_DB_MAPSERVER_ROLE;
 GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO DEFAULT_DB_MAPSERVER_ROLE;

 REVOKE ALL ON SCHEMA topology FROM DEFAULT_DB_MAPSERVER_ROLE;
 GRANT USAGE ON SCHEMA topology TO DEFAULT_DB_MAPSERVER_ROLE;
 GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA topology TO DEFAULT_DB_MAPSERVER_ROLE;
 GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA topology TO DEFAULT_DB_MAPSERVER_ROLE;

 REVOKE ALL ON SCHEMA userdata FROM DEFAULT_DB_MAPSERVER_ROLE;
 GRANT USAGE ON SCHEMA userdata TO DEFAULT_DB_MAPSERVER_ROLE;
 GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA userdata TO DEFAULT_DB_MAPSERVER_ROLE;
 GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA userdata TO DEFAULT_DB_MAPSERVER_ROLE;


-- Sphinx searchd user
-- Access Read Only everything

 REVOKE ALL ON SCHEMA public FROM DEFAULT_DB_SEARCH_ROLE;
 GRANT USAGE ON SCHEMA public TO DEFAULT_DB_SEARCH_ROLE;
 REVOKE ALL ON ALL TABLES IN SCHEMA public FROM DEFAULT_DB_SEARCH_ROLE;
 GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO DEFAULT_DB_SEARCH_ROLE;
 REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public FROM DEFAULT_DB_SEARCH_ROLE;
 GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO DEFAULT_DB_SEARCH_ROLE;

 REVOKE ALL ON SCHEMA topology FROM DEFAULT_DB_SEARCH_ROLE;
 GRANT USAGE ON SCHEMA topology TO DEFAULT_DB_SEARCH_ROLE;
 GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA topology TO DEFAULT_DB_SEARCH_ROLE;
 GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA topology TO DEFAULT_DB_SEARCH_ROLE;

 REVOKE ALL ON SCHEMA search FROM DEFAULT_DB_SEARCH_ROLE;
 GRANT USAGE ON SCHEMA search TO DEFAULT_DB_SEARCH_ROLE;
 GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA search TO DEFAULT_DB_SEARCH_ROLE;
 GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA search TO DEFAULT_DB_SEARCH_ROLE;

 REVOKE ALL ON SCHEMA userdata FROM DEFAULT_DB_SEARCH_ROLE;
 GRANT USAGE ON SCHEMA userdata TO DEFAULT_DB_SEARCH_ROLE;
 GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA userdata TO DEFAULT_DB_SEARCH_ROLE;
 GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA userdata TO DEFAULT_DB_SEARCH_ROLE;


-- API3 user
-- Access Read Only api3 + features + userdata ro

 REVOKE ALL ON SCHEMA public FROM DEFAULT_DB_API_ROLE;
 GRANT USAGE ON SCHEMA public TO DEFAULT_DB_API_ROLE;
 REVOKE ALL ON ALL TABLES IN SCHEMA public FROM DEFAULT_DB_API_ROLE;
 GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO DEFAULT_DB_API_ROLE;
 REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public FROM DEFAULT_DB_API_ROLE;
 GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO DEFAULT_DB_API_ROLE;

 REVOKE ALL ON SCHEMA topology FROM DEFAULT_DB_API_ROLE;
 GRANT USAGE ON SCHEMA topology TO DEFAULT_DB_API_ROLE;
 GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA topology TO DEFAULT_DB_API_ROLE;
 GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA topology TO DEFAULT_DB_API_ROLE;

 REVOKE ALL ON SCHEMA api3 FROM DEFAULT_DB_API_ROLE;
 GRANT USAGE ON SCHEMA api3 TO DEFAULT_DB_API_ROLE;
 GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA api3 TO DEFAULT_DB_API_ROLE;
 GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA api3 TO DEFAULT_DB_API_ROLE;

 GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE api3.url_shortener TO DEFAULT_DB_API_ROLE;
 GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE api3.files TO DEFAULT_DB_API_ROLE;

 REVOKE ALL ON SCHEMA userdata FROM DEFAULT_DB_API_ROLE;
 GRANT USAGE ON SCHEMA userdata TO DEFAULT_DB_API_ROLE;
 GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA userdata TO DEFAULT_DB_API_ROLE;
 GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA userdata TO DEFAULT_DB_API_ROLE;

Features

The point of the features.map_layers_features table is to map the name of each feature view to the portal for which it is active and the layers for which it should be interrogated. Thanks to this mapping, the API can then auto-load all the feature views and create the appropriate Python classes for interrogation automatically. This allows us not to explicitly declare them in a Python file. This means, you can modify these views as you like (adding and removing columns) without needing to bother about the code of API.

Attention

After you add or remove columns, you must ask the API to reload the views by doing in geo-infra:

manuel reload-features

These views will then be used in identify requests. These requests contain:

  • The name of the portal and the name of a layer. This allows us to select the appropriate views for a request.
  • A geometry that will be intersected with the one of the feature to find only the relevant features for this request.

We then return a JSON representation of the row. It will be presented to the user in a popup. This popup will contain a tab for each feature view and each tab will contain a table with a column per column in the feature view.

In order to work as expected, all the views must contain:

  • a column named the_geom containing the geometry of the feature.
  • a column named gid that can be used as a primary key.
  • columns you want to return in the identify request. For instance: name, type. We rely on some convention to automatically render some columns. See the page about features from the user manual to learn more about this.