Transcript Installing PostgreSQL/PostGIS

Installing PostgreSQL/PostGIS
Loading data manually
Gerrit Hendriksen
Installing PostgreSQL/PostGIS
Step 1. Get PostgreSQL from
https://www.postgresql.org/
(Tip, get the Interactive installer by EnterpriseDB for Windows,
for Linux, follow the recipe from the website)
Step 2. Install via the
installer and use the
stackbuilder to
install PostGIS
Tip! Remember your
credentials
Manuals
PostgreSQL
- PDF format: https://www.postgresql.org/docs/manuals/
- Online: https://www.postgresql.org/docs/manuals/
PostGIS:
- PDF format:
- Online:
Check the cheat sheets of Boston GIS
http://www.bostongis.com/
Also check
https://publicwiki.deltares.nl/display/OET/PostgreSQL
Create your first database
1. Start pgAdminIII (there is a 4 version which is a bit
different, for now we use pgAdminIII
2. Create new database
3. Open the query window ()
4. Install postgis by typing
Create extension postgis;
Create extension postgis_topology;
Press
or Ctrl + E
Explore what you have right now (> 1000 GIS functions)
Datamodel
Create tables and relations
--start DDL
CREATE SEQUENCE public.parameter_parameterid_seq;
CREATE TABLE public.parameter (
parameterid INTEGER NOT NULL DEFAULT
nextval('public.parameter_parameterid_seq'),
name VARCHAR(50) NOT NULL,
unit VARCHAR(20) NOT NULL,
CONSTRAINT parameter_pk PRIMARY KEY
(parameterid)
);
Complete query can be retrieved form https://OpenEarth.eu
search for PostgreSQL DSD2016
How to load data manually
1. Check the data you have
2. Create temporary table using the header of the dataset
3. Create table tempdata ()
Create Table tempdata(
cruise character varying(50),
station character varying(50),
type character varying(2),
adate timestamp,
latitude_degrees_north float,
longitude_degrees_east float,
bot_depth_m float,
pres_db float,
temp_deg_c float,
psal_psu float)
Load the data
4. Right click the table
5. Choose import
Load data manually
6.
7.
8.
9.
Fill Table parameter (with unique parameter list)
Fill Table icesstations (with unique stations)
Add geometry to the icesstations table
Fill Table oceanography
The process
Demo:
• Install PostgreSQL/PostGIS
• Create database
• Enable PostGIS extension
• Show what you get
• Create tables using the datamodel published on OpenEarth
site
• Load data in database
• Transform data to datamodel
• Do some queries
Afternoon session
Various sessions possible:
- Further handling of data
- Advanced queries (spatial queries)
- Combinations with PostgreSQL/PostGIS:
- QGIS
- Scripting with Python/R/Matlab
- Further ETL’ing
- Anything you want …