NGSIPostGISSink

Content:

Functionality

com.iot.telefonica.cygnus.sinks.NGSIPostGISSink, or simply NGSIPostGISSink is a sink designed to persist NGSI-like context data events within a PostGIS server which is relational database extension of PostgreSQL server which allows store GIS objects (Geographic Information Systems). Usually, such a context data is notified by a Orion Context Broker instance, but could be any other system speaking the NGSI language.

Independently of the data generator, NGSILD context data is always transformed into internal NGSILDEvent objects at Cygnus sources. In the end, the information within these events must be mapped into specific PostgreSQL data structures.

For futher detail please refear to NGSIPostgreSQLSink Basically PostGIS sink translates NGSI-LD attribute value about Geoproperty to PostGIS format and insert it. The available Geoproperty types are:

GeoProperty:point
GeoProperty:multipoint
GeoProperty:linestring
GeoProperty:multilinestrig
GeoProperty:polygon

Colum should be provisioned as type geometry

CREATE TABLE myTable ( geom geometry );

Top

Mapping NGSILDEvents to PostgreSQL data structures

NOTE: by the moment this mapping functionality works only for Postgis sink. However, the same will be implemented for the Postgresql sink itself. In that moment probably this documentation will be moved to the Postgresql sink documentation (as that is the common place for documentation which applies to both sinks)

PostgreSQL organizes the data in schemas inside a database that contain tables of data rows. Such organization is exploited by NGSIPostgreisSink each time a NGSILDEvent is going to be persisted.

Top

PostgreSQL databases naming conventions

Previous to any operation with PostgreSQL you need to create the database to be used.

It must be said PostgreSQL only accepts alphanumeric characters and the underscore (_). This leads to certain encoding is applied depending on the enable_encoding configuration parameter.

PostgreSQL databases name length is limited to 63 characters.

The name of this databases depends on the configured data model (see the Configuration section for more details):

  • Data model by entity database (data_model=dm-by-entity-database). For this datamodel the name of the database will be auto generated by the sink, this name will be the fiware-service found on the headers of the first request incoming to the sink.

  • All the other data models will take the name of the database from the agent properties configuration file as usual.

Top

PostgreSQL schemas naming conventions

A schema named as the notified fiware-service header value (or, in absence of such a header, the defaulted value for the FIWARE service) is created (if not existing yet).

It must be said PostgreSQL only accepts alphanumeric characters and the underscore (_). This leads to certain encoding is applied depending on the enable_encoding configuration parameter.

PostgreSQL schemas name length is limited to 63 characters.

Top

PostgreSQL tables naming conventions

The name of these tables depends on the configured data model (see the Configuration section for more details):

  • Data model by entity (data_model=dm-by-entity). For each entity, the notified/default FIWARE service path is concatenated to the notified entity ID and type in order to compose the table name. If the FIWARE service path is the root one (/) then only the entity ID and type are concatenated.
  • Data model by entity type (data_model=dm-by-entity-type). For each entity, the notified/default FIWARE service path is concatenated to the notified entity type in order to compose the table name. The concatenation character is _ (underscore). If the FIWARE service path is the root one (/) then only the entity type is concatenated.

It must be said PostgreSQL only accepts alphanumeric characters and the underscore (_). This leads to certain encoding is applied depending on the enable_encoding configuration parameter.

PostgreSQL tables name length is limited to 63 characters.

The following table summarizes the table name composition (old encoding):

dm-by-entity dm-by-entity-type
<entityId> <entityType>
Please observe the concatenation of entity ID and type is already given in the notified_entities header value within the NGSIEvent.

Administration guide

Please refear to NGSIPostgreSQLSink since all administration options about PostgreSQLSink applies to PostGISSink.

Configuration

All the notification examples are avilable under the notification-examples directory.

NGSIPostgrisSink can be configured through the same parameters as NGSIPostgreSQLSink except for postgis data_model functions

Parameter Mandatory Default value Comments
. . . .
. . same as NGSIPostgreSQLSink .
. . . .
data_model no dm-by-entity dm-by-entity or dm-by-entity-type .
. . . .

Configuration Example

A configuration example could be:

    cygnus-ngsi-ld.sources = http-source
    cygnus-ngsi-ld.sinks = postgresql-sink
    cygnus-ngsi-ld.channels = test-channel

    cygnus-ngsi-ld.sources.http-source.channels = test-channel
    cygnus-ngsi-ld.sources.http-source.type = org.apache.flume.source.http.HTTPSource
    cygnus-ngsi-ld.sources.http-source.port = 5050
    cygnus-ngsi-ld.sources.http-source.handler = com.telefonica.iot.cygnus.handlers.NGSIRestHandler
    cygnus-ngsi-ld.sources.http-source.handler.notification_target = /notify
    cygnus-ngsi-ld.sources.http-source.handler.default_service = def_serv_ld
    cygnus-ngsi-ld.sources.http-source.handler.events_ttl = 2
    cygnus-ngsi-ld.sources.http-source.interceptors = ts
    cygnus-ngsi-ld.sources.http-source.interceptors.ts.type = timestamp



    cygnus-ngsi-ld.channels.test-channel.type = memory
    cygnus-ngsi-ld.channels.test-channel.capacity = 1000
    cygnus-ngsi-ld.channels.test-channel.transactionCapacity = 100


    cygnus-ngsi-ld.sinks.postgresql-sink.type = com.telefonica.iot.cygnus.sinks.NGSIPostgisSink
    cygnus-ngsi-ld.sinks.postgresql-sink.channel = test-channel
    cygnus-ngsi-ld.sinks.postgresql-sink.enable_encoding = false
    cygnus-ngsi-ld.sinks.postgresql-sink.enable_grouping = false
    cygnus-ngsi-ld.sinks.postgresql-sink.enable_lowercase = false
    cygnus-ngsi-ld.sinks.postgresql-sink.enable_name_mappings = false
    cygnus-ngsi-ld.sinks.postgresql-sink.data_model = dm-by-entity
    cygnus-ngsi-ld.sinks.postgresql-sink.postgis_host = localhost
    cygnus-ngsi-ld.sinks.postgresql-sink.postgis_port = 5432
    cygnus-ngsi-ld.sinks.postgresql-sink.postgis_database = postgres
    cygnus-ngsi-ld.sinks.postgresql-sink.postgis_username = postgres
    cygnus-ngsi-ld.sinks.postgresql-sink.postgis_password = example
    cygnus-ngsi-ld.sinks.postgresql-sink.attr_persistence = column
    cygnus-ngsi-ld.sinks.postgresql-sink.attr_native_types = false
    cygnus-ngsi-ld.sinks.postgresql-sink.batch_size = 1
    cygnus-ngsi-ld.sinks.postgresql-sink.batch_timeout = 30
    cygnus-ngsi-ld.sinks.postgresql-sink.batch_ttl = 10
    cygnus-ngsi-ld.sinks.postgresql-sink.batch_retry_intervals = 5000
    cygnus-ngsi-ld.sinks.postgresql.backend.enable_cache = false

Programmers guide

Please refear to NGSIPostgreSQLSink since all programing details about PostgreSQLSink applies to PostGISSink.

Native types

Regarding the specific data stored within the above table, if attr_native_types parameter is set to true then attribute is inserted using its native type (according with the following table), if false then will be stringify.

Type json Type PostGreSQL/POSTGIS
string text
number double, precision, real, others (numeric, decimal)
boolean boolean (TRUE, FALSE, NULL)
DateTime timestamp, timestamp with time zone, timestamp without time zone
json text o json - it`s treated as String
null NULL

This only applies to Column mode.