DHI.Services.PostgreSQL for Places — Internal Developer Guide¶

This page documents the PostgreSQL repository implementation for the Places domain. It explains how data is stored, how queries work, how to wire the provider, and what to watch out for in production. For the domain itself (entities, indicators, time handling) see the core Places guide; for API endpoints see the Web API guide; and for shared RDBMS practices see PostgreSQL Providers.

What this provider does¶

DHI.Services.Provider.PostgreSQL.PlaceRepository persists places and their indicators in PostgreSQL and implements the IPlaceRepository<string> contract:

Create/read/update/delete Place entities (ID/Name/Group + FeatureId)
Persist Indicator objects per place (DataSource, TimeInterval, AggregationType, StyleCode)
Query by group (regex-based path matching)
List ids/full names, fetch indicators by place, type, or group
Enforces FK ON DELETE CASCADE from places → indicators

It deliberately keeps the schema lean (no JSON metadata/permissions columns). If you need metadata persistence, extend the schema & mapper (see §10 Extensibility).

Data model (tables & columns)¶

By default the repository creates two tables (if missing) in public:

1) `public.places`¶

Column	Type	Null	Notes
`id`	`varchar(255)`	NO	Place ID (also FullName)
`name`	`varchar(255)`	NO	Leaf name
`groupname`	`varchar(255)`	YES	Group path (e.g., `Stations/…`)
`featureid`	`varchar(500)`	YES	GIS FeatureCollectionId (e.g., `.shp` file name)
`attributekey`	`varchar(255)`	YES	GIS attribute key (e.g., `StatId`)
`attributevalue`	`varchar(255)`	YES	GIS attribute value

PK: (id).

DDL created by the repo:

CREATE TABLE IF NOT EXISTS public.places (
  id            character varying(255)  NOT NULL,
  name          character varying(255)  NOT NULL,
  groupname     character varying(255),
  featureid     character varying(500),
  attributekey  character varying(255),
  attributevalue character varying(255),
  CONSTRAINT places_pk PRIMARY KEY (id)
);

2) `public.indicators`¶

Column	Type	Null	Notes
`id`	`uuid`	NO	Row key
`type`	`varchar(255)`	NO	Indicator type (e.g., `Rainfall`)
`connectionid`	`varchar(255)`	NO	DataSource.ConnectionId (e.g., `csv`)
`entityid`	`varchar(255)`	YES	DataSource.EntityId (TS id or scalar id)
`datasourcetype`	`varchar(255)`	YES	`Scalar` / `TimeSeries` / `EnsembleTimeSeries`
`timeinterval`	`varchar(255)`	YES	`All` / `Fixed` / `RelativeToNow` / `RelativeToDateTime`
`starttime`	`double precision`	YES	Start numeric per TimeInterval contract
`endtime`	`double precision`	YES	End numeric per TimeInterval contract
`aggregationtype`	`varchar(100)`	YES	DisplayName of aggregation (`Maximum`, …)
`stylecode`	`varchar(500)`	YES	Palette thresholds (e.g., `0:green \| 10:red`)
`placeid`	`varchar(255)`	YES	FK → `places(id)`

PK: (id) FK: (placeid) → public.places(id) ON DELETE CASCADE

DDL created by the repo:

CREATE TABLE IF NOT EXISTS public.indicators (
  id              uuid                    NOT NULL,
  type            character varying(255)  NOT NULL,
  connectionid    character varying(255)  NOT NULL,
  entityid        character varying(255),
  datasourcetype  character varying(255),
  timeinterval    character varying(255),
  starttime       double precision,
  endtime         double precision,
  aggregationtype character varying(100),
  stylecode       character varying(500),
  placeid         character varying(255),
  CONSTRAINT indicators_pk PRIMARY KEY (id),
  CONSTRAINT indicators_placeid_fk FOREIGN KEY (placeid)
    REFERENCES public.places (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE
);

TimeInterval encoding:

All → timeinterval='All', starttime/endtime NULL

Fixed → OLE automation dates in starttime/endtime

RelativeToNow / RelativeToDateTime → day offsets in starttime/endtime (e.g., -7, 0)

Connection string & table overrides¶

Constructor:

new DHI.Services.Provider.PostgreSQL.PlaceRepository("<connectionString>");

The provider also supports overriding table names using repeated Table= additions parsed out (first match applies to places, second to indicators). Example:

Server=localhost;Port=5432;Database=ProviderTest;User Id=postgres;Password=*****;
Table=customschema.places_v2;Table=customschema.indicators_v2

Order matters: the first Table= value is used for places, the second for indicators. If you provide only one, only places is overridden.

For shared patterns and operational guidance, see PostgreSQL Providers.

Repository behaviors (high level)¶

Add(place): inserts the place, then inserts all indicators for that place.
Update(place): DELETE the existing place (cascades indicators), then Add again.
Remove(id): deletes a place; indicators are cascaded away.
Get(id): materializes a Place and joins its indicators via a second query.
GetAll() / GetByGroup(group): reads places and then pulls indicators per place.
ContainsGroup(group): regex ~ '^{group}(\/|$)' against groupname. (Case-sensitive by default—use consistent casing in data & queries.)
GetIndicatorsByType(type) and ByGroupAndType(group, type): efficient server-side filters.
Safety: all scalar inputs are parameterized via NpgsqlParameter.

Wiring it up¶

You can wire the PostgreSQL provider into the Places service manually or via Connections.

A) Manual registration (`ServiceLocator.Register`)¶

// Dependencies registered elsewhere:
ServiceLocator.Register(
  new DiscreteTimeSeriesService<string,double>(
      new DHI.Services.TimeSeries.CSV.TimeSeriesRepository("[AppData]".Resolve())),
  "csv");

ServiceLocator.Register(
  new DHI.Services.GIS.GisService<string>(
      new DHI.Services.Provider.ShapeFile.FeatureRepository("[AppData]shp".Resolve())),
  "shp");

// Compose Places with PostgreSQL repository
var ts = new Dictionary<string, IDiscreteTimeSeriesService<string,double>> {
  ["csv"] = Services.Get<IDiscreteTimeSeriesService<string,double>>("csv")
};
var scalars = new Dictionary<string, IScalarService<string,int>>(); // optional
var gis = Services.Get<IGisService<string>>("shp");

var pgRepo = new DHI.Services.Provider.PostgreSQL.PlaceRepository(
  "Server=localhost;Port=5432;Database=ProviderTest;User Id=postgres;Password=Solutions!"
  // optional: + ";Table=public.places;Table=public.indicators"
);

ServiceLocator.Register(
  new DHI.Services.Places.PlaceService(pgRepo, ts, scalars, gis),
  "postgresql"  // ← your {connectionId}
);

Your Web API base then becomes: GET /api/places/postgresql/...

B) Config-driven (Connections module)¶

{
  "$type": "System.Collections.Generic.Dictionary`2[[System.String, mscorlib],[DHI.Services.IConnection, DHI.Services]], mscorlib",

  "csv": {
    "$type": "DHI.Services.TimeSeries.WebApi.DiscreteTimeSeriesServiceConnection, DHI.Services.TimeSeries.WebApi",
    "ConnectionString": "[AppData]",
    "RepositoryType": "DHI.Services.TimeSeries.CSV.TimeSeriesRepository, DHI.Services.TimeSeries",
    "Name": "CSV time series service connection",
    "Id": "csv"
  },

  "shp": {
    "$type": "DHI.Services.GIS.WebApi.GisServiceConnection, DHI.Services.GIS.WebApi",
    "ConnectionString": "[AppData]shp",
    "RepositoryType": "DHI.Services.Provider.ShapeFile.FeatureRepository, DHI.Services.Provider.ShapeFile",
    "Name": "Shape file GIS service connection",
    "Id": "shp"
  },

  "postgresql": {
    "$type": "DHI.Services.Places.WebApi.PlaceServiceConnection, DHI.Services.Places.WebApi",
    "ConnectionString": "Server=localhost;Port=5432;Database=ProviderTest;User Id=postgres;Password=Solutions!",
    "RepositoryType": "DHI.Services.Provider.PostgreSQL.PlaceRepository, DHI.Services.Provider.PostgreSQL",
    "GisServiceConnectionId": "shp",
    "TimeSeriesServiceConnectionIds": [ "csv" ],
    "Name": "Place service connection",
    "Id": "postgresql"
  }
}

You can still override table names by appending ;Table=…;Table=… to the postgresql connection string entry.

For an end-to-end host including auth, Swagger, and sample data, see the sample app in GitHub: Host/Places.

Usage tips & examples¶

Add a place programmatically (service layer)¶

var featureId = new FeatureId("Stationer.shp", "StatId", "ID92_M16");
var place = new Place("Stations/MyStation", "MyStation", featureId, "Stations");

place.Indicators.Add("WaterLevel", new Indicator(
  new DataSource(DataSourceType.TimeSeries, "csv", "timeseries.csv;TimeSeries1"),
  styleCode: "0:green|10:red",
  timeInterval: TimeInterval.CreateAll(),
  aggregationType: DHI.Services.TimeSeries.AggregationType.Maximum));

place.Indicators.Add("Rainfall", new Indicator(
  new DataSource(DataSourceType.TimeSeries, "csv", "timeseries.csv;TimeSeries2"),
  styleCode: "0~15:#800080,#5500AB,#2A00D5,#0000FF",
  timeInterval: TimeInterval.CreateRelativeToDateTime(-1, 0),
  aggregationType: DHI.Services.TimeSeries.AggregationType.Sum));

placeService.Add(place);

Query by group (recursive)¶

foreach (var p in repo.GetByGroup("Stations"))
  Console.WriteLine(p.FullName);

Bulk status by type (service)¶

var statuses = placeService.GetIndicatorStatusByType(
  type: "Rainfall",
  group: "Stations",
  dateTime: DateTime.UtcNow);

foreach (var kv in statuses)
  Console.WriteLine($"{kv.Key} -> {kv.Value.Status}");

Behavior details & semantics¶

Groups: group filtering uses a regex anchored to the start of groupname: groupname ~ '^{group}(\/|$)' — i.e., exact match at start, then either / or end. Keep casing consistent; ~ is case-sensitive in PostgreSQL.
AggregationType: we persist the DisplayName (e.g., "Maximum", "Average"). Changing display names in upstream libs will affect deserialization.
EntityId size: varchar(255) — ensure your time series or scalar IDs fit.
TimeInterval nulls: if timeinterval is not All and start/end are NULL, the repo will return null for TimeInterval (which will be rejected by PlaceService for time-series indicators). Populate both values.

Operational & performance notes¶

N+1 reads: GetAll() / GetByGroup() fetch places then issue one query per place for indicators. For large repositories, prefer targeted reads: GetIndicatorsByType, or add batch SQL paths if needed.
Transactions: Add does two phases (place, then indicators) without an explicit transaction. If you need atomicity across both inserts, wrap the calls in a transaction at a higher layer or extend the repo.
Indexes:
- Primary keys already cover join paths.
- For heavy group filtering, consider an index on (groupname) (the regex still benefits).
- For indicator lookups, optional indexes on (type), (placeid).
ON DELETE CASCADE: deleting a place cleans up its indicators automatically.
Security: all queries use NpgsqlParameter; avoid string interpolation for user inputs.

Web API pairing¶

Once registered (manually or via connections), the Places Web API automatically exposes the PostgreSQL-backed data at:

/api/places/{connectionId}/...

Use the controllers and DTOs documented in the Web API guide for:

CRUD on places and indicators
GIS feature export with status colors
Thresholds & palette PNGs

Extensibility¶

Metadata/Permissions: the PostgreSQL repo does not persist Metadata or Permissions from BaseGroupedEntity. If needed, add JSONB columns and extend InsertPlace / GetPlace.
Bigger IDs: if entityid or featureid requires more than 255/500 chars, widen the columns and adjust DDL accordingly.
Case-insensitive groups: switch to ~* in ContainsGroup / GetByGroup if your data requires it.

Quick reference¶

Need…	Use…	Notes
Start quickly	`PlaceRepository(connectionString)`	Creates tables if needed
Override table names	`…;Table=myschema.places;Table=myschema.indicators`	Order matters (places first, indicators second)
Wire manually	`ServiceLocator.Register(new PlaceService(new Provider.PostgreSQL.PlaceRepository(...), ...), "postgresql")`	Then call `/api/places/postgresql/...`
Wire via connections	`PlaceServiceConnection` JSON (see §5B)	Also register GIS+TimeSeries connections
Bulk indicators by type	`GetIndicatorsByType(type)`	Server-side filtered
Group subtree	`GetByGroup("Stations")`	Regex anchored match
Delete place	`Remove(id)`	Indicators are cascaded