Changes between Initial Version and Version 1 of UsersWikiPostGIS24UbuntuPGSQL10Apt


Ignore:
Timestamp:
Feb 10, 2018 1:06:13 PM (18 months ago)
Author:
robe
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • UsersWikiPostGIS24UbuntuPGSQL10Apt

    v1 v1  
     1The [http://wiki.postgresql.org/wiki/Apt PostgreSQL Apt Repository]  now hosts installs of PostGIS, pgRouting, in addition to Postgresql and PGAdmin.
     2
     3The following describes how to install Postgresql 10, PostGIS 2.4, pgRouting 2.5, PGAdmin on Ubuntu version 16.04. It is assumed to also work on Linux Mint, Lubuntu, and Xubuntu.
     4
     5Run these in terminal:
     6== Verify what version of Ubuntu you are running ==
     7{{{
     8sudo lsb_release -a
     9}}}
     10
     11
     12== Add Respository to sources.list ==
     13
     14You'll need to replace the nickname below with what you are running.
     15Note to replace the word trusty with whatever version lsb states
     16
     17Helper on [http://www.postgresql.org/download/linux/ubuntu/]
     18
     19For xenial (16.04.2 LTS)
     20
     21{{{
     22sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt xenial-pgdg main" >> /etc/apt/sources.list'
     23}}}
     24
     25
     26== Add Keys ==
     27{{{
     28wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
     29}}}
     30
     31{{{
     32sudo apt-get update
     33}}}
     34
     35== Install ==
     36The following will install postgresql 10, PostGIS 2.4, PGAdmin4, pgRouting 2.5 :
     37
     38
     39{{{
     40sudo apt-get install postgresql-10
     41sudo apt-get install postgresql-10-postgis-2.4 postgresql-contrib-10 postgresql-10-postgis-scripts
     42
     43#to get the commandline tools shp2pgsql, raster2pgsql you need to do this
     44sudo apt-get install postgis
     45}}}
     46
     47== To get pgRouting ==
     48{{{
     49# Install pgRouting  package
     50sudo apt-get install postgresql-10-pgrouting
     51
     52}}}
     53
     54Never install PostGIS in the postgres database, create a user database
     55You can also enable the PostGIS extension here (or with the GUI as described below):
     56
     57{{{
     58sudo -u postgres psql
     59}}}
     60
     61{{{
     62CREATE DATABASE gisdb;
     63\connect gisdb;
     64
     65CREATE SCHEMA postgis;
     66ALTER DATABASE gisdb SET search_path=public, postgis, contrib;
     67\connect gisdb;  -- this is to force new search path to take effect
     68CREATE EXTENSION postgis SCHEMA postgis;
     69SELECT postgis_full_version();
     70}}}
     71
     72should give you output something like this:
     73
     74
     75{{{
     76                                                                                           postgis_full_version
     77
     78------------------------------------------------------------------------------------------------------------------------------------------------------
     79-----------------------------------------------------
     80 POSTGIS="2.4.3 r16312" PGSQL="100" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML=
     81"2.9.3" LIBJSON="0.11.99" LIBPROTOBUF="1.2.1" RASTER
     82(1 row)
     83}}}
     84
     85Installing postgis_sfcgal, if you need advanced 3D support
     86#note this was not available for me
     87
     88{{{
     89CREATE EXTENSION postgis_sfcgal SCHEMA postgis;
     90SELECT postgis_full_version();
     91}}}
     92
     93should give you:
     94
     95
     96{{{
     97                                                                                   postgis_full_version
     98------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     99 POSTGIS="2.3.2 r15302" GEOS="3.5.0-CAPI-1.9.0 r4084" SFCGAL="1.2.2" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" RASTER
     100(1 row)
     101}}}
     102
     103
     104
     105
     106Install pgRouting
     107
     108{{{
     109CREATE  EXTENSION pgrouting SCHEMA postgis;
     110SELECT * FROM pgr_version();
     111}}}
     112
     113should give you:
     114
     115
     116{{{
     117 version |  tag   |   hash    | branch | boost
     118---------+--------+-----------+--------+--------
     119 2.5.2   | v2.5.2 | 60585f1f7 | master | 1.58.0
     120(1 row)
     121}}}
     122
     123
     124
     125
     126Exit the psql console:
     127
     128{{{
     129\q
     130}}}
     131
     132
     133== Open Access to Clients ==
     134
     135If you need to allow access from external, you can do this as well
     136{{{
     137sudo -u postgres psql
     138}}}
     139
     140{{{
     141ALTER SYSTEM SET listen_addresses='*';
     142\q
     143}}}
     144
     145You may need to edit to pg_hba.conf and/or pg_ident.conf to allow external access
     146
     147{{{
     148sudo nano /etc/postgresql/10/main/pg_hba.conf
     149}}}
     150
     151If you need external access, scroll to the bottom of the pg_hba.conf file and add a line like this (which willa llow all clients with md5 password encrypt authentication (right after the local rules):
     152
     153{{{
     154hostssl    all             all             0.0.0.0/0               md5
     155}}}
     156
     157Click CTRL-X to save your changes, Y to write them to the file, and enter to exit.
     158
     159If you change ip or port, you need to do a service restart.
     160
     161{{{
     162sudo service postgresql restart
     163}}}
     164
     165Note: you can also do from postgres psql console with below - only for changes that don't require restart)
     166
     167{{{
     168SELECT pg_reload_conf();
     169}}}
     170
     171
     172== Optional: check location of configuration files: ==
     173
     174From the psql console(see above):
     175 
     176{{{
     177SELECT name, setting FROM pg_settings where category='File Locations';
     178}}}
     179
     180Which will output something like:
     181
     182{{{
     183
     184       name        |                 setting
     185-------------------+-----------------------------------------
     186 config_file       | /etc/postgresql/10/main/postgresql.conf
     187 data_directory    | /var/lib/postgresql/10/main
     188 external_pid_file | /var/run/postgresql/10-main.pid
     189 hba_file          | /etc/postgresql/10/main/pg_hba.conf
     190 ident_file        | /etc/postgresql/10/main/pg_ident.conf
     191(5 rows)
     192}}}
     193
     194
     195
     196== Create new PGSQL user ==
     197
     198You can create a new database super user to use instead of the default {{{postgres}}} user.
     199
     200While in terminal, run:
     201
     202{{{
     203sudo -u postgres psql
     204CREATE ROLE mysuperuser LOGIN PASSWORD 'whatever' SUPERUSER;
     205}}}
     206
     207
     208
     209== Import SHP files using shp2pgsql-gui ==
     210
     211Another handy piece of software {{{shp2pgsql-gui}}} tool. This will allow you to quickly connect to your new PostGIS database and import a Shapefile.
     212Note this will only work if you have Ubuntu with a desktop (not a headless Ubuntu)
     213
     214Open terminal, and type:
     215
     216{{{
     217sudo apt-get install postgis-gui
     218}}}
     219
     220(Note: this is coming from the main Ubuntu software repository, as it seems the PostgreSQL APT repository doesn't package SHP2PGSQL-GUI anymore...)
     221
     222Now open the SHP2PGSQL application:
     223
     224{{{
     225shp2pgsql-gui
     226}}}
     227
     228Follow the on-screen prompts to load your data.
     229
     230For more information, visit the [http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01 Boston GIS] tutorial in the section "Load Towns Data"
     231
     232== ogr_fdw foreign data wrapper for spatial data ==
     233
     234Unfortunately seems PostgreSQL Apt does not include ogr_fdw spatial data wrapper.
     235The wrapper utilizes GDAL, so much of plumbing needed is already installed for you as part of PostGIS.
     236Compiling isn't too difficult.  On a vanilla Ubuntu, I was able to compile and install with following:
     237
     238
     239{{{
     240#for compiling ogr_fdw
     241cd ~/
     242mkdir sources
     243cd sources
     244sudo apt-get install git
     245sudo apt-get install postgresql-server-dev-10
     246sudo apt-get install make
     247sudo apt-get install gcc
     248sudo apt-get install libgdal-dev
     249
     250sudo git clone -b master https://github.com/pramsey/pgsql-ogr-fdw.git pgsql_ogr_fdw
     251cd pgsql_ogr_fdw
     252export PATH=/usr/lib/postgresql/10/bin:$PATH
     253make && make install
     254}}}
     255
     256After you're done compiling and installing the binary, you can install the ogr_fdw extension in your database
     257
     258
     259{{{
     260sudo -u postgres psql
     261}}}
     262
     263{{{
     264
     265\connect gisdb;
     266CREATE EXTENSION ogr_fdw SCHEMA postgis;
     267CREATE SCHEMA IF NOT EXISTS staging ;
     268
     269-- here I assume you have a file in root of /gis_data folder that postgres process has read rights to
     270-- any file that gdal can read will do e.g shape file, CSV, etc
     271CREATE SERVER svr_shp
     272  FOREIGN DATA WRAPPER ogr_fdw
     273  OPTIONS (
     274    datasource '/gis_data',
     275    format 'ESRI Shapefile' );
     276
     277
     278-- this will link in all your shapefile tables in folder gis_data as foreign tables
     279IMPORT FOREIGN SCHEMA ogr_all
     280FROM SERVER svr_shp INTO staging;
     281
     282
     283}}}