PostgreSQL/Postgis

Ermöglicht den Zugriff auf PostgreSQL Datenbanken mit Postgis Geometriedaten. Die Implementierung ist über die PostgreSQL C-API realisiert. D.H. die Installation von zusätzlichen Treiber ist nicht erforderlich.

Abfragen mittels GeoSQL sind möglich.

Nutzung der alternativeRenderSources sind möglich.

Die Geometrien werden als EWKB abgerufen, die unterstützen Formate finden Sie hier.

Beachten Sie, dass derzeit in Postgis selber nicht alle Typen für erweiterte Geometriefunktionen verwendet werden können (z.B. ST_Intersects mit einer CURVE, es kommt ein Fehler in der Art "ERROR: Unknown geometry type: 10 - CurvePolygon"). Bei Abfragen über GeoSQL besteht diese Einschränkung nicht.

Das Einbinden einer Quelle ohne Geometriespalte ist derzeit nicht vorgesehen.

Argumente

Typname: PostgresLayer

  • connectionString: Die Verbindungszeichenfolge zum Datenbankserver. Die Notation wird im Stile der C-Api erwartet, Details sind dort zu entnehmen.

    Der Aufbau einer typischen Verbindungszeichenfolge:
    host=servername port=5432 dbname=my_db user=cardo password=geheim

    Intern werden immer noch folgende Argumente zusätzlich mit angefügt:

    keepalives=1 client_encoding=UNICODE

    Initial wird immer folgendes Statement mit ausgeführt (das ist hier mitRücksicht auf PgBouncer, wo das im Connection String nicht zulässig ist):

    SET cursor_tuple_fraction = 1;
    SET application_name = 'Iwan7::<computername>';
    

    Die Liste der erzwungenen Parameter kann bei Bedarf angepasst werden. Aktuell sind dies die besten Optionen für den Betrieb.

    Die Informationen zu Benutzer und Kennwort (user, password) könne auch separat übergeben werden.

  • userName (null): Benutzername zur Datenbank, überschreibt die in connectionString evtl. vorhandene Angabe, kann verschlüsselt angegeben werden.

  • passWord (null): Kennwort zur Datenbank, überschreibt die in connectionString evtl. vorhandene Angabe, kann verschlüsselt angegeben werden.

  • source: Name einer Abfrage oder Tabelle. Es kann sich dabei um jedes Datenbankstatement handeln, vom dem ein SELECT * FROM ... möglich ist. Handelt es sich um eine Abfrage, wird immer automatisch ein Alias angefügt. Bsp. für gültige Quellen:

    • TABELLEA
    • SELECT * FROM TABELLEA
    • (SELECT a,b,* FROM TABELLEA)
  • geomColumnName: (""): wenn nicht angegeben, dann wird aus der Abfrage {source} die erste Spalte vom Typ Geometry verwendet, ist keine Geometriespalte vorhanden, wird ein Fehler ausgelöst. Sonst wird geprüft, dass die Spalte vorhanden und vom Typ Geometrie ist.

  • quickLoad (false): Wenn false, dann werden Count, Srid und BBox über diese Abfrage ermittelt

  • searchPath (""): optional, wenn angegeben, dann wird beim Verbindungsaufbau SET search_path TO {searchPath} abgesetzt.

    Der SearchPath als Connectionstring-Argument geht z.B. über PgBouncer nicht, daher die separate Angabe diese Argumentes

  • idColumnName ("_auto_"): optionaler Name einer Spalte die eindeutige Werte enthält, wenn angegeben, dann muss die Spalte auch in {source} mit enthalten sein.

    Die Spaltennamen "_auto_" und "_none_" haben eine besondere Bedeutung. Bei _auto_ (bzw. keiner Angabe), wird versucht eine RowId per Abfrage zu generieren.

    Derzeit kann die auto. Ermittelung nur durch Angabe von _none_ unterbunden werden.

    Mit Version 7.5.5 wird die CTID nur noch eingesezt, wenn als ID Spalte der Wert _auto_ angegeben wird.

    Es wird dringend empfohlen eine RowId Spalte anzugeben, diese sollte einen numerischen Typ haben (z.B Int8)

  • lastModColumnName (null): Name einer Spalte, die das Änderungsdatum der Datenzeile als DateTime enthält. Diese Information wird in der Beschreibung der Datenquelle ausgegeben (mit dem Wert, der zum Zeitpunkt des Ladens der Ebene vorhanden war).

    Beachte: Wenn angegeben, dann wird diese Information auch bei quickLoad:true ermittelt, d.h. es erfolgt dann immer ein Abfrage der Art select count(*), max(modCol) from ....

  • srid (-1):

  • epsgCode (-1): Epsg-Code der Daten, wenn nicht definiert, dann wird eine automatische Ermittlung aus der Bounding-Box versucht

  • style|cssFile: hier immer vom Typ Vektor CSS.

PostgreSQL eigene Angaben

Ab Version 7.0.16 können weitere Laufzeitargumente für PostgreSQL mit übergeben werden. Diese entsprechen exakt dem Namen aus dem Dokumentationsbereich 19.7. Query Planning. Die Werte werden als Initial-Statement beim herstellen der Connection direkt an PostgreSQL übergeben.

Relevant sind bspw. "enable_nestloop", dieses würde dann in der Ebenendefinition als enable_nestloop:false übergeben werden.

Durch das Absetzen als Init-SQL Statement sind keine Probleme bei der Verwendung von PGBouncer zu erwarten.

Ladevorgang

Der Ladevorgang wird im Wesentlichen durch die Argumente beeinflusst. In jedem Fall wird eine Abfrage SELECT * FROM {source} durchgeführt (u.U. erweitert um RowId).

Je nach Quickload wird eine weitere Abfrage für die Statistik ausgeführt.

Beispiel

Hier wird auf den (materialisierten) View atkis.nas_pp_ax_waldtype_flaeche zugegriffen. (Bsp. auf Basis ALKIS NAS Daten, Konvertiert mit unserem Konverter) zugegriffen.

{
"ATKIS_Wald": {
    "type": "PostgresLayer",
    "ConnectionString": "host=amy dbname=dbName port=5434 user=cardo3 password=geheim",
    "Source": "atkis.nas_pp_ax_waldtype_flaeche",
    "geomColumnName": "geom",
    "idColumnName": "gml_identifier",
    "epsgCode": 25832,
    "cssfile": "D:\\IduIT\\Native\\Tester\\ATKIS_Wald.css",
    "quickLoad": false
  }
}

Implementierungsdetails / Features und Einschränkungen

Wir empfehlen den Einsatz eines Connection-Poolers, z.B. pgBouncer, Iwan selber führt kein Connection-Pooling für PostgreSQL Verbindungen durch. D.H. jeder Aufruf initiiert eine neue Verbindung zur Datenbank. Dieser Verbindungsaufbau kann bis zu 30ms dauern.

Dem Connection-String werden immer folgende Argumente mit angefügt:

keepalives=1 client_encoding=UNICODE application_name=Iwan7 sslmode=disable options='-c cursor_tuple_fraction=1

Alle Abfragen erfolgen immer per BINARY CURSOR. Dies ist nach unseren Messungen die performanteste Möglichkeit Daten aus einer PostgreSQL Datenbank abzurufen.

Vor allem beim Nachstellen von Abfragen, z.B. für Analysezweck via EXPLAIN, ist dies zu berücksichtigen.

Dies ist auch der Grund, warum im Connectionstring immer die Option cursor_tuple_fraction=1 angegeben wird.

D.H. ein Abfrage wird bspw. in folgender Form abgesetzt:

BEGIN;
 DECLARE iduitcursor BINARY CURSOR 
   FOR SELECT* FROM public.spatial_ref_sys;

FETCH FORWARD 100 FROM iduitcursor;
-- bis EOF ...
END;
Beim Verbinden mit der Datenbank...

.... werden verschiedene Parameter ermittelt:

  • 2 oder 3-stellige Postgres Version (SHOW SERVER_VERSION)
  • 3-stellige Postgis Version (durch SELECT PostGIS_Lib_Version())
  • Timestamp Binary Format : Int64 oder Double (binärer Zauber ..)
  • Oid's der Typen geometry,raster, json und bson (select oid,typname from pg_type where typname in('raster','geometry','json','jsonb'))

Nicht unterstützte Datentypen

Liste der Geometrieformate: siehe hier

Arrays werden derzeit nicht unterstützt, ebenso nicht die folgenden Datentypen:

  • ACLITEMOID
  • ANYELEMENTOID
  • ANYENUMOID
  • ANYOID
  • ANYRANGEOID
  • BITOID
  • BOXOID
  • CASHOID
  • CIDOID
  • CIDROID
  • CIRCLEOID
  • CSTRINGOID
  • DATERANGEOID
  • EVTTRIGGEROID
  • FDW_HANDLEROID
  • INDEX_AM_HANDLEROID
  • INETOID
  • INT4RANGEOID
  • INT8RANGEOID
  • INTERNALOID
  • INTERVALOID
  • JSONPATHOID
  • LANGUAGE_HANDLEROID
  • LINEOID
  • LSEGOID
  • LSNOID
  • MACADDR8OID
  • MACADDROID
  • NUMRANGEOID
  • OPAQUEOID
  • PATHOID
  • PGDDLCOMMANDOID
  • PGDEPENDENCIESOID
  • PGMCVLISTOID
  • PGNDISTINCTOID
  • PGNODETREEOID
  • POINTOID
  • POLYGONOID
  • RECORDOID
  • REFCURSOROID
  • REGCLASSOID
  • REGCONFIGOID
  • REGDICTIONARYOID
  • REGNAMESPACEOID
  • REGOPERATOROID
  • REGOPEROID
  • REGPROCEDUREOID
  • REGPROCOID
  • REGROLEOID
  • REGTYPEOID
  • TABLE_AM_HANDLEROID
  • TIDOID
  • TIMETZOID
  • TRIGGEROID
  • TSM_HANDLEROID
  • TSQUERYOID
  • TSRANGEOID
  • TSTZRANGEOID
  • TXID_SNAPSHOTOID
  • VARBITOID
  • VOIDOID
  • XIDOID
  • XMLOID
SQL Statments für intern durchgeführte Abfragen

Ermittlung von Count/BBox (siehe auch Parameter quickLoad):

(
WITH 
stat as (
  SELECT count(*) cnt,
	 ST_Extent({geom}) as bb, 
	  min(ST_Srid(%s)) as min_srid,max(ST_Srid({geom})) as max_srid
	FROM {source} WHERE NOT {geom} IS NULL
 ) 
SELECT 
  cnt,
  case when stat.min_srid = stat.max_srid then stat.min_srid  else null end as srid,
  st_xMin(stat.bb),
  st_yMin(stat.bb),
  st_xMax(stat.bb),
  st_yMax(stat.bb) 
FROM stat
)

Ermittlung einer Zeile-Id (siehe auch Parameter rowid):

((ctid::text::point)[0]::bigint << 32) | (ctid::text::point)[1]::bigint

Zuletzt geändert: 20.09.2024 07:22:06 (erstmals erstellt 14.03.2017)