pgindex

Appearance

Example with foreign data wrapper ​

This example shows how to fetch remote data and make it searchable on a local index

Install foreign data wrapper ​

Install the GDAL based foreign data wrapper ogr_fdw

sql
create extension ogr_fdw;

Connect to a remote WFS server ​

This example connects to arealeditering WFS server

sql
CREATE SERVER arealeditering
	FOREIGN DATA WRAPPER ogr_fdw
	OPTIONS (
		datasource 'WFS:https://arealeditering-dist-geo.miljoeportal.dk/geoserver/wfs',
		format 'WFS' );
	
ALTER SERVER arealeditering
	OPTIONS (ADD updateable 'false');

CREATE SCHEMA arealeditering;

IMPORT FOREIGN SCHEMA ogr_all
	FROM SERVER arealeditering
	INTO arealeditering;

SET client_min_messages = debug1;

Create materialized view from remote dara ​

We create a materialized view we can refreseh periodically. Note we use a bbox filter to limit the data fetched

sql
drop materialized view if exists septima_pgindex.besk_naturtype ;

create materialized view septima_pgindex.besk_naturtype AS
select                    
	  objekt_id as id,
      case 
        when gl_sys_ref  <> '' or gl_sys_ref is not null then gl_sys_ref
      	when gl_sys_ref = '' or gl_sys_ref is null and journalnr is not null  then journalnr
      	else objekt_id
      end as title,
      natyp_navn as description,
      format('%s %s %s', cvr_navn, sagsbeh, journalnr) as searchtext,
      null as matchstring,
      ST_CollectionExtract(shape, 3) as geometry,-- konverter fra multisurface
      gl_sys_ref,
      cvr_navn,
      sagsbeh,
      aendrbegr,
      besig_dato,
      vedligehold_status,
      vedligehold_tid,
      journalnr
from arealeditering.dai_bes_naturtyper 
where shape && ST_MakeEnvelope(568721,6217622,627191,6259780)

Refresh the view periodically with this command

sql
refresh materialized view septima_pgindex.besk_naturtype

Register featuretype in index ​

sql
select septima_pgindex.registerfeaturetype(
	sourceid:= 'arealeditering',  --source
	typeid:= 'bes_naturtype',               --featuretype id
	singular:= 'Beskyttet naturtype',             --singular
	plural:= 'Beskyttede naturtyper',              --plural
	language:= 'danish',            --ts language
	description:= 'Beskyttede naturtyper fra Arealeditering', --description
	querybehaviour:= 'search',       --'search'|'match'|'none'
	geometrysupport:= 'sq',        --'sq'|'hasgeometry'|'none'
	srid:= 25832,                     --srid of data (must be non-null if geometrySupport = 'sq')
	iconuri:= null,                  --iconuri
	sourcestatement:=                --sql expression retrieving features
    'select * FROM septima_pgindex.besk_naturtype'
);

Define fields ​

Note the use og novaluetext which defines what text is shown when the fields is null og empty string

sql
select septima_pgindex.definefield( 
	typeid:=      'bes_naturtype',  --featuretype id
	field:=       'natyp_navn', --field in feature's sourcestatement
	displayname:= 'Naturtype',  --displayname af field
	description:= 'Naturtypenavn',
	novaluetext:= 'Ikke angivet'
);

select septima_pgindex.definefield( 
	typeid:=      'bes_naturtype',  --featuretype id
	field:=       'gl_sys_ref', --field in feature's sourcestatement
	displayname:= 'Gammel ref',  --displayname af field
	description:= 'Fra gl_sys_ref',
	novaluetext:= 'Ikke angivet'
);
           
select septima_pgindex.definefield( 
	typeid:=      'bes_naturtype',  --featuretype id
	field:=       'cvr_navn', --field in feature's sourcestatement
	displayname:= 'Kommune',  --displayname af field
	description:= 'Beliggenhedskommune',
	novaluetext:= 'Ikke angivet'
);

select septima_pgindex.definefield( 
	typeid:=      'bes_naturtype',  --featuretype id
	field:=       'sagsbeh', --field in feature's sourcestatement
	displayname:= 'Sagsbehandler',  --displayname af field
	description:= 'Sagsbehandler',
	novaluetext:= 'Ikke angivet'
);
select septima_pgindex.definefield( 
	typeid:=      'bes_naturtype',  --featuretype id
	field:=       'aendrbegr', --field in feature's sourcestatement
	displayname:= 'Ændringsbegrundelse',  --displayname af field
	description:= 'Ændringsbegrundelse',
	novaluetext:= 'Ikke angivet'
);

select septima_pgindex.definefield( 
	typeid:=      'bes_naturtype',  --featuretype id
	field:=       'journalnr', --field in feature's sourcestatement
	displayname:= 'Journalnr',  --displayname af field
	description:= 'Journalnr',
	novaluetext:= 'Ikke angivet'
);
select septima_pgindex.definefield( 
	typeid:=      'bes_naturtype',  --featuretype id
	field:=       'vedligehold_status', --field in feature's sourcestatement
	displayname:= 'Status',  --displayname af field
	description:= 'Status',
	novaluetext:= 'Ikke angivet'
);
select septima_pgindex.definefield( 
	typeid:=      'bes_naturtype',  --featuretype id
	field:=       'besig_dato', --field in feature's sourcestatement
	displayname:= 'Besigtigelsesdato',  --displayname af field
	description:= 'Besigtigelsesdato',
	novaluetext:= 'Ikke dato angivet'
);

select septima_pgindex.definefield( 
	typeid:=      'bes_naturtype',  --featuretype id
	field:=       'vedligehold_tid', --field in feature's sourcestatement
	displayname:= 'Vedligeholdstidspunkt',  --displayname af field
	description:= 'Vedligeholdstidspunkt',
	novaluetext:= 'Intet tidspunkt angivet'
);

Populate index

sql
select * from septima_pgindex.populateindex('bes_naturtype');

Query index

sql
select * from septima_pgindex.queryindex('bes_naturtype', 'K466-01-mo', 3, 25832);

Get by id

sql
select * from septima_pgindex.get('bes_naturtype', '053f7594-9101-4208-8699-a58f40fa303f');

Use with OneDoor ​

Add the file pgindex.yml in your configuration folder with the following

yml
pgindex:
  connection:
    _type: pgindex.Connection
    _options:
      host: localhost
      port: 5432
      database: postgres
      user: postgres
      password: postgres
      srid: 25832
  besk_naturtype:
    _type: pgindex.Searcher
    _options:
      connection:
        _ref: "$.pgindex.connection"
      typeId: bes_naturtype
      info: true                      # Show all fields from septima_pgindex.definefield
  sqbesk_naturtype:
    _type: s3.SqDetailsHandler
    _options:
      id: beskyttet_naturtype
      polygonIntersectsBuffer: -1
      buttonText: Beskyttede naturtyper
      noResultsListItem:
        type: labelvalue
        label: Beskyttede naturtyper
        value: Ingen Beskyttede naturtyper fundet
    searchers:
      - _ref: $.pgindex.besk_naturtype

Import into your main configuration file

yml
import:
  sections:
    - name: pgindex
      dir: $.env.configDir

Add searcher to the controller

yml
controller:
  _type: Septima.Search.Controller
  _options:
    blankBehavior: search
  searchers:
    - _ref: $.pgindex.besk_naturtype

Add detailhandler to omJordstykket

yml
  omJordstykket:
    _type: Septima.Search.ComposedDetailsHandler
    _options:
      id: omjordstykke
      more: true
      targets: [{source: '*', typeId: 'matrikelnumre'}]
      buttonText: 'Om jordstykket'
      buttonImage: 'data:image/svg+xml;base64, PHN2ZyB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciIHhtbG5zOnhsaW5rPSJodHRwOi8vd3d3LnczLm9yZy8xOTk5L3hsaW5rIiB2ZXJzaW9uPSIxLjEiIHdpZHRoPSIyNCIgaGVpZ2h0PSIyNCIgdmlld0JveD0iMCAwIDI0IDI0Ij48cGF0aCBkPSJNNSwxNy41OUwxNS41OSw3SDlWNUgxOVYxNUgxN1Y4LjQxTDYuNDEsMTlMNSwxNy41OVoiIC8+PC9zdmc+'
    detailhandlers:
      - _type: Septima.Search.JordStykkeInfoProvider
        _options:          
          fields: ["adminfo", 'landbrugsnotering', 'arealer', 'sekundaer']         
          ejdSearcher:  
            _ref: $.standardkommune.ejendomme
      - _type: datafordeler.SagsoplysningerForJordstykkeProvider
        _options:
          fetcher:
            _ref: $.standardkommune.datafordelerfetcher
      - _ref: $.standardkommune.detailhandlers.sqLokalPlaner
      - _ref: $.standardkommune.detailhandlers.sqKloakoplande
      - _ref: $.pgindex.sqbesk_naturtype
      - _ref: $.standardkommune.detailhandlers.EjdExplorerLinks