Step by step

To uninstall

sql

drop schema septima_pgindex;

Usage

Examples taken from demo.sql

Register your feature type

sql

-- Creates feature type "author"
select septima_pgindex.registerfeaturetype(
	sourceid:= 'pg_index_demodata',  --source
	typeid:= 'author',               --featuretype id
	singular:= 'Author',             --singular
	plural:= 'Authors',              --plural
	language:= 'english',            --ts language
	description:= 'Author of posts', --description
	querybehaviour:= 'search',       --'search'|'match'|'none'
	geometrysupport:= 'none',        --'sq'|'hasgeometry'|'none'
	srid:= null,                     --srid of data (must be non-null if geometrySupport = 'sq')
	iconuri:= null,                  --iconuri
	sourcestatement:=                --sql expression retrieving features
    'select                 
      a.id::varchar as id,
      a.name as title,
      ''Author'' as description,
      null as searchtext,
      null as matchstring,
      null as geometry,
	  country,
	  homepage,
	  birthday::varchar as birthday	
	 from author a'
);

septima_pgindex.registerfeaturetype redefines the featuretype, clears the index, and removes all previously defined fields and details

No need to call septima_pgindex.unregisterfeaturetype

Define the fields from the source statement that serve as info

sql

select septima_pgindex.definefield( 
	typeid:=      'author',  --featuretype id
	field:=       'country', --field in feature's sourcestatement
	displayname:= 'Country',  --displayname af field
	description:= 'Country of Birth'
);

select septima_pgindex.definefield(
	typeid:=      'author',  --featuretype id
	field:=       'birthday', --field in feature's sourcestatement
	displayname:= 'Birthday',  --displaytext of field,
	datatype:=    'iso-date'
);

select septima_pgindex.definefield(
	typeid:=      'author',  --featuretype id
	field:=       'homepage', --field in feature's sourcestatement
	displayname:= 'Web Site of [title] ',  --displaytext of field, use the contents of the title field to construct displayname
	datatype:=    'link',
	description:= 'Professional web site',
	label:=		  'Web site'
);```  

### Define relations between features

```sql  
select from septima_pgindex.registermasterdetail(
    'author',    --master featuretype
    'id',        --field
    null,        --relation may be null
    'post',      --detail featuretype
    'author_id'  --field
);

Populate your index (must be done when underlying data is updated)

sql

select * from septima_pgindex.populateindex('author');

API

Define index

Register a feature type

sql

select *
from septima_pgindex.registerfeaturetype(
    sourceid varchar,         --source
    typeid varchar,           --feature type id
    singular varchar,         --singular
    plural varchar,           --plural
    language varchar,         --ts language
    description varchar,      --description
    querybehaviour varchar,   --querybehaviour ('search'|'match'|'none')
    geometrysupport varchar,  --geometrySupport ('sq'|'hasgeometry'|'none')
    srid int,                 --srid of the indexed geometries (must be non-null if geometrySupport != 'none')
    iconuri varchar,          --iconuri, may be null
    sourcestatement varchar   --select statement used to populate the index
);

Where _sourcestatement is a select which as a minimum returns the following fields

select                    -- Fields used by the index:
    id::varchar,               -- id, mandatory, varchar
    skolenavn as title,        -- title, mandatory, varchar
    bydel as description,      -- description, varchar. May be null
    null as searchtext,        -- varchar, used in free text search if querybehaviour = "search". May be null
    null as matchstring,       -- varchar, used in free text search if querybehaviour = "match". May be null
    wkb_geometry as geometry   -- geometry, mandatory if geometrySupport != "none". Else null

Any other fields are included in the document property of features.

You may re-register by calling septima_pgindex.registerfeaturetype again. No need to call unregister manually

Define fields that make up info of feature

sql

select septima_pgindex.definefield(
    typeid varchar,
    field varchar,
    displayname varchar,       -- displaytext. Include value of a field like this '[fieldname]'
    datatype = 'auto'          -- optional. may be set to 'link' or 'iso-date'. If 'iso-date' then field must contain varchar in iso format ('1987-12-05')
    novaluetext varchar        -- optional. Create labelvalue if field value is null   
);

Define relations between features

sql

select from septima_pgindex.registermasterdetail(
    'author',    --master featuretype
    'id',        --field
    null,        --relation may be null
    'post',      --detail featuretype
    'author_id'  --field
);

To unregister a type

sql

select *
from septima_pgindex.unregisterfeaturetype(featuretypeid varchar)

Populate your index

sql

select *
from septima_pgindex.populateindex(featuretypeid varchar);

Query your index

sql

select * 
from septima_pgindex.queryindex(
    featuretypeid varchar,     -- feature type being queried
    querystring varchar,       -- query string. May contain one or more words
    _limit int,                -- maximum number of features to return
    srid int = 4326            -- return geometries in this srid
);

Example:
select *
from septima_pgindex.queryindex('skoledistrikt', 'indre', 3, 25832);

queryindex                                                                                                               
--------------------------------------------------------------------------------------------------------------------
{"count":5,"features":[{"id":"1","title":"Den Classenske Legatskole","description":"INDRE BY","geometry":{"type":...

Where count is the total count of features matching the query. Maximum number of features returned is determined by _limit

Get a feature

sql

select *
from septima_pgindex.get(
    _featuretypeid varchar,    -- feature type 
    _id varchar                -- id of feature to get
);

Example:
select id, title, description, document, geometry from septima_pgindex.get('skoledistrikt', '2');
id|title           |description|document                                                                                          
--|----------------|-----------|-----------------------------------------------------------------------------------
2 |Kildevældsskolen|ØSTERBRO   |{"id":"2","title":"Kildevældsskolen","description":"ØSTERBRO","searchtext":null,...

Spatial query against a feature type

sql

select *
from septima_pgindex.sq(
    featuretypeid varchar,     -- feature type 
    geometry json,             -- geojson. Default crs, if none included in the json: 4326
    _limit int                 -- maximum number of features to return
);

Example:
select *
from septima_pgindex.sq(
 'skoledistrikt',
 '{"type":"Point","crs":{"type":"name","properties":{"name":"EPSG:25832"}},"coordinates":[724837.68,6175474.03]}'::json,
 3);

sq                                                                                                                                
-----------------------------------------------------------------------------------------------------------------------
{"count":1,"features":[{"id":"1","title":"Den Classenske Legatskole","description":"INDRE BY","geometry":{"type":"Po...

Get all registered feature types

sql

select *
from septima_pgindex.getfeaturetypes();

Example output:
source |id           |singular     |plural         |description        |querybehaviour|geometrysupport|iconuri|featurecount|
-------|-------------|-------------|---------------|-------------------|--------------|---------------|-------|------------|
kbh_buf|skoledistrikt|Skoledistrikt|Skoledistrikter|Buf skoledistrikter|search        |sq             |       |          56|

Use in Septima Search (Server only)

Create a connection to the database

yaml

    _type: pgindex.Connection
    _options:
      host: 'HOST'
      port: PORT
      database: 'DB'
      user: 'USER'
      password: 'PASSWORD'
      srid: 25832

Create a searcher

Minimal searcher

yaml

    _type: pgindex.Searcher
    _options:
      connection:
        _ref: "$.pgindex.connection"
      typeId: author

Detail handlers

InfoProvider

Use pgindex.InfoProvider to show the fields that were defined by

sql

select septima_pgindex.definefield(...)

Short form

Use info: true to attach an InfoProvider

yaml

    _type: pgindex.Searcher
    _options:
      info: true
      connection:
        _ref: "$.pgindex.connection"
      typeId: author

which is short for

yaml

   detailhandlers:
     - _type: pgindex.InfoProvider
     - _options:
         more: true
         id: "info"
         showParents: true

pgindex.InfoProvider options

yaml

   detailhandlers:
     - _type: pgindex.InfoProvider
     - _options:
         more: boolean #default true
         id: string #deafult "info"
         showParents: boolean #default false
         asList: boolean #default true

RelationsProvider

For parent features; show the children defined by

sql

select septima_pgindex.registermasterdetail(...)

Short form

Use relations: true to attach a RelationsProvider for each child type

yaml

    _type: pgindex.Searcher
    _options:
      relations: true
      connection:
        _ref: "$.pgindex.connection"
      typeId: author

which is short for

yaml

   detailhandlers:
     - _type: pgindex.RelationsProvider
       _options:
         detailType: book 
       detailhandlers:
         - _type: pgindex.InfoProvider
         - _options:
             asList: false

RelationsProvider options

Show the books by the author

yaml

   detailhandlers:
     - _type: pgindex.RelationsProvider
       _options:
         detailType: book

Full example

yaml

controller:
  _type: Septima.Search.Controller
  _options:
    blankBehavior: search
  searchers:
    - _ref: "$.pgindex.authors"
    - _ref: "$.pgindex.posts"
    - _ref: "$.pgindex.books"
pgindex:
  connection:
    _type: pgindex.Connection
    _options:
      host: 'HOST'
      port: PORT
      database: 'DB'
      user: 'USER'
      password: 'PASSWORD'
      srid: 25832
  authors:
    _type: pgindex.Searcher
    _options:
      connection:
        _ref: "$.pgindex.connection"
      typeId: author
      info: true                      # Show all fields from septima_pgindex.definefield
      relations: true                 # Show all relations from septima_pgindex.registermasterdetail (on separate tabs)
  posts:
    _type: pgindex.Searcher
    _options:
      connection:
        _ref: "$.pgindex.connection"
      typeId: post
    detailhandlers:
      - _type: pgindex.InfoProvider  # Show fields from septima_pgindex.definefield
  books:
    _type: pgindex.Searcher
    _options:
      connection:
        _ref: "$.pgindex.connection"
      typeId: book
      relations: true
      detailhandlers:
          - _type: pgindex.InfoProvider
            _options:
              showParents: true

Step by step ​

Usage ​

Register your feature type ​

Define the fields from the source statement that serve as info ​

Populate your index (must be done when underlying data is updated) ​

API ​

Define index ​

Register a feature type ​

Define fields that make up info of feature ​

Define relations between features ​

To unregister a type ​

Populate your index ​

Query your index ​

Get a feature ​

Spatial query against a feature type ​

Get all registered feature types ​

Use in Septima Search (Server only) ​

Create a connection to the database ​

Create a searcher ​

Minimal searcher ​

Detail handlers ​

InfoProvider ​

Short form ​

pgindex.InfoProvider options ​

RelationsProvider ​

Short form ​

RelationsProvider options ​

Full example ​

Step by step

Usage

Register your feature type

Define the fields from the source statement that serve as info

Populate your index (must be done when underlying data is updated)

API

Define index

Register a feature type

Define fields that make up info of feature

Define relations between features

To unregister a type

Populate your index

Query your index

Get a feature

Spatial query against a feature type

Get all registered feature types

Use in Septima Search (Server only)

Create a connection to the database

Create a searcher

Minimal searcher

Detail handlers

InfoProvider

Short form

pgindex.InfoProvider options

RelationsProvider

Short form

RelationsProvider options

Full example