Erste Schritte in postgres

Communities Deutschland (Germany)
, ,
1

Was macht man am Sonntag, wenn das Wetter zu blöd zum Radeln ist? Installiere ich mir mal postgres auf meinem UBUNTU Laptop, dachte ich mir.

Also: Folgendes ausgeführt:

sudo apt install postgresql
sudo apt install postgresql-client
sudo apt install postgresql-doc

sudo -u postgres createuser chris
sudo -u postgres createdb mydb

$ psql mydb

Ok, hat soweit geklappt. Ist die Version 16 aktuell genug?

Könnte ich mir in dieser Stage schon eine OSM DB erstellen und mit osm2psql befüllen, oder muss dazu erst PostGIS installiert sein?

2

Und schon klemmt es, darf keine Table im “public schema” erstellen, och menno.

3

Dazu brauchst die PostGIS-Erweiterung. Das Debian-/Ubuntu-Paket heißt “postgresql-16-postgis-3”. Diese muss danach in der Datenbank noch aktiviert werden: psql -d $DB_NAME -c "CREATE EXTENSION postgis;"

4

Dein Datenbank-User ist nicht der Eigentümer der Datenbank. Das lässt sich beim Aufruf von createdb mit --owner chris angeben.

5

Musste zusätzlich ein
GRANT ALL PRIVILEGES ON SCHEMA public TO chris;
absetzen, nun geht es.

6

Ein paar ganz allgemeine Tipps dazu:

PostgreSQL hat “Datenbank-Cluster”, in jedem kann es mehrere “Datenbanken” geben, in einer Datenbank mehrere “Schemata” und in jedem Schema mehrere “Tabellen” (und Views, Funktionen usw.). In der Praxis spielen aber die Ebene “Cluster” und die Ebene “Schema” keine Rolle - Debian/Ubuntu erzeugen Dir bei der Installation automatisch einen Cluster, und beim Schema kommt, wenn Du nichts anderes angibst, immer das Schema “public” zum Einsatz, und das reicht normalerweise auch. Das heisst, Du musst nur mit “createdb” eine Datenbank erstellen und mit create extension postgis und ggf. auch create extension hstore die notwendigen Extensions installieren, dann kannst Du schon das osm2pgsql laufen lassen.

PostgreSQL hat eine eigene Benutzerverwaltung. Wenn Du am Linux als Benutzer chris angemeldet bist und psql eintippst, wird er versuchen, dich als Benutzer chris mit einer Datenbank chris zu verbinden. Der Standard-Admin-User heisst postgres. Eine einfache Methode, um Dir selber Adminrechte zu geben, ist sowas wie

su postgres
psql -c "create user chris with superuser"
exit

Danach kannst Du dann von Deinem Unix-Account aus problemlos createdb und alles mögliche ausführen. PostgreSQL wird automatisch annehmen, dass der Unix-User X sich als PostgreSQL-User X verbinden darf, dazu braucht der dann kein Password. Man kann dieses Verhalten über die Datei pg_hba.conf steuern.

7
  • DB-User “gis” mit ‘-s’ (Superuser) erstellt
  • Datenbank gis mit Owner gis, in UTF8 erstellt
  • EXTENSION postgis geladen (hstore wird von postpass nicht benötigt?)

Wäre dies nun der korrekte osm2pgsql-Befehl?
$ osm2pgsql -d gis -U gis -S postpass.lua -O flex --number-processes 4 nrw.osm.pbf

(Ubuntu 24.04.1)

EDIT: Leider Fehlermeldung: Peer authentication failed for user "gis"

@Jochen_Topf

8

Laut einem stackexchange-Post sollte ich mal versuchen in hba_conf von “peer” auf “md5” umzustellen.

Wenn ich nun ein “psql mydb” mache verlangt er ein Passwort, wo ich dann mein Ubuntu-User-Passwort eingebe.

ERROR:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: password authentication failed for user "chris"

?

9

Juchu, mit auth-method TRUST in hba_conf klappt der Import. NRW wurde in 7,5 Minuten importiert.

gis=# select osm_id, osm_type from postpass_line LIMIT 10;
   osm_id   | osm_type
------------+----------
  162544332 | W
   25815520 | W
   59397374 | W
   25984444 | W
   59397376 | W
  550912264 | W
   24654752 | W
 1042023845 | W
  164733299 | W
   25328363 | W
(10 rows)

Sollte ich nach dem Import wieder auf peer stellen? Bei trust findet nach meinem Verständnis quasi keine Authentification mehr statt.

EDIT: Die KombiViews wie postpass_pointpolygon fehlten noch, habe ich manuell erstellt.

10

Standardmäßig wird deine PostGIS nicht von außen erreichbar sein, solange sich also auf Deinem Rechner keine Benutzer tummeln, denen Du nicht vertraust und die die geheimen OSM-Daten stehlen könnten…

1 Like
11

{{bbox}} funktioniert in meiner lokalen Instanz natürlich nicht.

Könnte ich mir Funktionen wie “bbcoe()” basteln, die mir feste BBoxen zurückliefern?

12

Und selbst wenn: Ich habe ja nur die Zeile für local connections geändert. :wink:

13

Ja,

create function mybb() 
returns geometry as 
$$ 
begin 
   return st_makeenvelope(8.34,48.97,8.46,49.03,4326); 
end; 
$$ language plpgsql;

und dann kannst Du einfach sowas schreiben wie where geom && mybb().

1 Like
14

Danke, ich denke ich werde da noch einen Parameter einbauen, so dass ich bb(‘coe’) oder bb(‘lh’) schreiben kann.

EDIT: Ok, habe den Parameter von meinem Programmiergesellen (gemini) einbauen lassen. :wink:

15

Wie kann ich denn die Spalte geom lesbar (lat/lon) anzeigen?

  osm_id   |                        geom
-----------+----------------------------------------------------
 324738761 | 0101000020E6100000CABED0C88CD41D407D3ECA880BDE4940
 324735937 | 0101000020E6100000B30F0DE60AD21D40FB534F8B66DE4940
 324735935 | 0101000020E6100000947FD2F5E9CD1D4031009EFEFDDE4940
 324735936 | 0101000020E6100000B78608EEF6CF1D4087EC7200B3DE4940
 324735934 | 0101000020E610000044E2790492CB1D405810148953DF4940
16

Wenn du mit Lesbar WKT, also sowas wie POLYGON((0 0,0 1,1 1,1 0,0 0) meinst dürfte das reichen

ST_AsText(geom)

Abgelegt ist das als HexWKB

1 Like
17

Wenn Du nett zu ihm bist, sagt er Dir auch, wie Du eine Tabelle mit lauter Boundingboxen anlegen kannst, aus der die Funktion dann immer die passende raussucht ;)

18

Hab erstmal nur eine CASE Fallunterscheidung für ‘coe’, ‘ms’, ‘lh’ und ‘nrw’ einbauen lassen, wobei er die Koords ziemlich geraten hat. :wink:

Wird die Funktion nur einmal pro SELECT ausgewertet, so dass sie keinen Performancedrop verursacht, egal wieviele bboxen man da drin hat?

PS: Bin mir gerade Germany am bauen.
EDIT: Done in 52 Minuten. numprocs auf 4 gelassen, da 8 nicht viel gebracht hat. DB-Größe: 36 GiB.

19

Die 0.1 Kompabilitätsviews benötigen das wohl, aber die brauche ich erstmal nicht.

20

Ja. PostgreSQL hat einen “Query Planner”, der studiert erstmal Deine Abfrage und überlegt sich dann, wie er die voraussichtlich am schnellsten beantworten kann. Bei einem einfachen “select foo from bar where baz” ist das meistens trivial, aber sobald mehrere Tabellen beteiligt sind, explodieren oft die Kombinationsmöglichkeiten: Nehme ich jetzt jeden Eintrag aus A und suche mir jeweils den passenden aus B, oder umgekehrt, oder… das ist eine Wissenschaft für sich (siehe Doku zu EXPLAIN). Aber in dem von Dir skizzierten Fall wird der Query Planner merken, dass der Eingabewert der Funktion ja nicht von den Tabellenzeilen abhängt und sie daher nur einmal ausführen.

1 Like