PostGIS

PostGIS spatially enables the PostgreSQL open source DBMS

Neat stuff

I just caught the tail end of Paul Ramsey's talk "Magical PostGIS in three brief movements" and now I need to watch it here: http://blog.cleverelephant.ca/2015/03/magical-postgis.html

Goal: Build an enterprise geodatabase server

1. I am building a GIS data warehouse with a catalog that can be queried in many different ways.
2. I need access to the a rich suite of spatial tools available in PostGIS

My PostGIS hardware

At home, Bellman, a little AMD 350 M-1 based server running Debian 6.

On the Internet, Dart, a Supermicro based Xeon box with SSD + SAS + SATA drives running Debian

On the road, Plover, a MacBook Pro with 16GB of RAM

I tried to use a base level Tektonic.net server but it did not have enough RAM to do this job. Mostly due to trying to run Geoserver and Tomcat. They use a lot of RAM!

The software

Operating system: Ubuntu Server 14.10 and Debian 7

PostgreSQL

NOTE NOTE When upgrading from 9.0 you have to dump and reload, because the data formats are not compatible.

(See also my PostgreSQL page.)

PostgreSQL 9.x using pre-built binary on Mac and apt-get on Ubuntu and Debian

On the Mac, I installed PostgreSQL 9.4 from the DMG package and used the Stack Builder to install PostGIS 1.5 and Apache. This was really easy!

Ubuntu

apt-get install postgresql-client libpq-dev pgadminIII

CentOS 6.2

I did not build my own on CentOS in the interests of being more production oriented. See the PostgreSQL page. Here is how I used to do it though.

./configure --prefix=/opt/postgresql/9.1.6 --with-python
# let's build all the extensions
# let's whip through this by using 10 CPU cores! Get it over with!
make -j 10 world
sudo make install

# service postgresql-9 restart

PGDATA is set in the script /etc/profile.d/postgres.sh

Pre 12.04 Linux

On my Debian and (older) Ubuntu servers, I ended up building PostgreSQL from source because available binaries of 9.0 don't have the development headers so PostGIS can't be built! Someone (THX1138) suggested I try:

sudo apt-get install postgresql-server-dev-9.0

but I have already moved on to building 9.1.2 so I am not going to try right now.

sudo apt-get install python-dev libreadline-dev

cd ~/src/GIS
wget http://ftp.postgresql.org/pub/source/v9.1.6/postgresql-9.1.6.tar.bz2
tar xjf postgresql-*bz2
cd postgresql-9.1.?
./configure --with-python
make
sudo make install

This installs into /usr/local, so the scripts have to be adjusted accordingly.

I create a script in /etc/profile.d/postgresql.sh to set PATH etc. like this

# Localization for PostgreSQL and PostGIS -----
###############################################################
# POSTGRESQL

PGHOME=/usr/local/pgsql
export PGBIN=$PGHOME/bin
export PGLIB=$PGHOME/lib
export PGDATA=/var/lib/postgresql/9.3/data
export PGPORT=5432

# I wonder what this does. Same as mv -i option maybe.
export PGOPTS="-i"

PATH=$PATH:$PGBIN
export LD_LIBRARY_PATH=$PGLIB

###############################################################
# POSTGIS

##
export PATH

Once you have installed and sourced the above script then you should be able to run the following commands without having to specify the location for pg_config.

Supporting players

Proj 4.7.0 (currently in Debian 6)
GDAL 1.10.0 (built from SVN)
GEOS 3.3.7
Might need source for openjpeg and kml

PROJ

Debian, Ubuntu

sudo apt-get install libproj-dev proj-bin

CentOS

wget proj tar gz
./configure
make && sudo make install

GEOS

(When building for a desktpop I skip the php references)

GEOS (installed into /usr/local):

sudo apt-get install swig python-dev php5-dev phpunit
wget http://download.osgeo.org/geos/geos-3.3.8.tar.bz2
tar xjvf geos-3.3.?.tar.bz2 
cd geos-3.3.?
./configure --enable-python --enable-php
make
sudo make install

Spatialite

CentOS

sudo yum -y install sqlite-devel

wget http://www.gaia-gis.it/gaia-sins/freexl-1.0.0d.tar.gz
tar xzvf freexl-1.0.0d.tar.gz 
cd freexl-1.0.0d
./configure
make -j 10 && sudo make install
cd ..

wget http://www.gaia-gis.it/gaia-sins/libspatialite-4.0.0.tar.gz
tar xzvf libspatialite-4.0.0.tar.gz 
cd libspatialite-4.0.0
./configure
make -j 10 && sudo make install

Ubuntu

 sudo apt-get install libspatialite-dev

GDAL

I always seem to end up wanting features not yet released (ESRI FGDB in this case.) So install the subversion source code to track the latest.

Get GDAL sources

sudo apt-get install subversion
cd ~/src/GIS
svn checkout https://svn.osgeo.org/gdal/trunk/gdal gdal

After the initial checkout, later on to update just do

cd ~/src/GIS/gdal
svn update

GDAL (installed into /usr/local): "poppler" packages are included for PDF support.

sudo apt-get install openjpeg-tools libopenjpeg-dev
sudo apt-get install libpoppler-dev
sudo apt-get install expat libexpat1-dev libkml-dev libxerces-c-dev
sudo apt-get install libcurl4-openssl-dev

CentOS

svn checkout http://libkml.googlecode.com/svn/trunk/ libkml-read-only
cd libkml-read-only
./autogen.sh
./configure
make -j 10 && sudo make install
wget http://apache.claz.org//xerces/c/3/sources/xerces-c-3.1.1.tar.gz
./configure
for poppler
yum install fontconfig-devel

ESRI FGDB support: I am adding ESRI file geodatabase (version 10 only) support. Download kit from http://resources.arcgis.com/content/geodatabases/10.0/file-gdb-download and unpack it

cd ~/src/GIS
tar xzvf FileGDB_API_1_2-64.tar.gz
cd FileGDB_API
sudo cp lib/*.so /usr/local/lib
sudo ldconfig -v

ESRI MDB support: See http://www.gdal.org/ogr/drv_mdb.html Install a Java JDK (I use version 7) and get the jackess library: http://jackcess.sourceforge.net/ and put it in this MDB folder

mkdir MDB
cd MDB
wget http://apache.deathculture.net//commons/lang/binaries/commons-lang3-3.1-bin.tar.gz
wget http://apache.cs.utah.edu//commons/logging/binaries/commons-logging-1.1.1-bin.tar.gz
wget http://downloads.sourceforge.net/project/jackcess/jackcess/1.2.9/jackcess-1.2.9.jar?r=http%3A%2F%2Fsourceforge.net%2Fprojects%2Fjackcess%2Ffiles%2F&ts=1355080366&use_mirror=superb-dca3
tar xzvf commons-lang3-3.1-bin.tar.gz 
tar xzvf commons-logging-1.1.1-bin.tar.gz
sudo cp commons-logging-1.1.1/commons-logging-1.1.1.jar commons-lang3-3.1/commons-lang3-3.1.jar jackcess-1.2.9.jar /opt/oracle/jdk1.7.0_09/jre/lib/ext/

Do the configure and build. This version (03 Jan) is not building KML support. Don't know why right now.

cd ~/src/GIS/gdal
./configure \
--with-python --with-poppler \
--with-pg=/usr/pgsql-9.2/bin/pg_config \
--with-curl \
--with-openjpeg --with-geos --with-geotiff \
--with-jpeg --with-png \
--with-expat --with-libkml --with-xerces-c \
--with-mrsid=/var/home/AGI/bwilson/src/GIS/MrSID/Raster_DSDK \
--with-java=/opt/oracle/jdk1.7.0_09 --with-mdb \
--with-fgdb=/var/home/AGI/bwilson/src/GIS/FileGDB_API \
--with-spatialite

Note the MrSID and ESRI lines require a complete path for some reason. Probably my fault somehow, I think a relative path should be good enough.

make
sudo make install

Confirm that the version you just built is really the one found. (There might be an older version for example in /usr/bin! Surprise!!)

gdalinfo --version

Confirm the formats you need are listed with these commands

gdalinfo --formats
ogrinfo --formats

For example.

ogrinfo --formats | grep GDB
 -> "FileGDB" (read/write)

Confirm you can load it from Python, too.

$ python
Python 2.6.6 (r266:84292, Sep 11 2012, 08:34:23) 
[GCC 4.4.6 20120305 (Red Hat 4.4.6-4)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import osgeo.gdal
>>> print osgeo.gdal.__version__
1.10dev
>>> 

YAY!

PostGIS

Installing the prerequisites

Don't do this: sudo apt-get build-dep postgis

Ordinarily it is handy to do something like this to pick up all the build dependencies, but this downloads and installs too much!

It picks up binaries that we want to build from source which is bad because we want to get the most up-to-date versions. Then you have to wrestle with having 2 versions of various things (some in /usr and some in /usr/local) and it's too much work.

Instead I install the supporting package individually. I have already installed PostgreSQL and GEOS at this point so all I needed to do at this stage was this:

sudo apt-get install libxml2-dev

My (Debian/Ubuntu) systems already had JSON support but you might need it

sudo apt-get install libjson0-dev

CentOS

sudo yum install libxml2-devel
wget https://github.com/downloads/json-c/json-c/json-c-0.10.tar.gz
tar xzvf json-c-0.10.tar.gz
cd json-c-0.10
make -j 10 && sudo make install
sudo cp json_object_iterator.h /usr/local/include/json/
sudo ldconfig

PostGIS 2.1

Building from source -- not really enterprise production ready! But I do it anyway to get features and performance.

(either) Direct checkout to get the very latest including topology and raster

svn checkout http://svn.osgeo.org/postgis/trunk postgis-svn
cd postgis-svn
sh autogen.sh

(or) Tarball download to get almost the latest

wget http://www.postgis.org/download/postgis-2.0.0SVN.tar.gz
tar xzvf postgis-2.0.0SVN.tar.gz
cd postgis-2.0.0SVN

Building on CentOS

sudo yum install postgresql92-devel
./configure \
--with-pgconfig=/usr/pgsql-9.2/bin/pg_config \
--with-geosconfig=/usr/local/bin/geos-config \
--with-jsondir=/usr/local \
--with-topology --with-raster \
--with-gdalconfig=/usr/local/bin/gdal-config

Building on Debian and Ubuntu

./configure \
--with-pgconfig=/usr/local/pgsql/bin/pg_config \
--with-geosconfig=/usr/local/bin/geos-config \
--with-jsondir=/usr \
--with-topology --with-raster

make
sudo make install

Building on Mac seemed promising but I failed. Using binary packages there for now.

Server management

You have to create the initial databases for Postgres. See the INSTALL file in the sources for more information. Here is how I do it.

Create a user to own the postgres files

su
adduser postgres

Create your data storage area(s) (what's the Postgres term for that? Umm... it's a "cluster".) This assumes you set PGDATA correctly for your system.

mkdir $PGDATA
chown postgres $PGDATA

Create the initial database tables

su - postgres
initdb -D $PGDATA
exit

You probably want to edit the $PGDATA/pg_hba.conf and postgresql.conf files. I add lines to allow my computers to connect in pg_hba.conf and I tell the server to listen for network connections in postgresql.conf

Start the server. There is a sample startup script for Redhat/Centos in the source tree

sudo cp contrib/start-scripts/linux /etc/init.d/postgresql-9
sudo service postgresql-9 start

Create a generic database to test postgres.

createdb -U postgres test
psql -U postgres test
\d
\q

If it works you can drop it.

dropdb -U postgres test

Tuning your system for PostGIS

Tips from a SOTM Mapnik doc. http://www.geofabrik.de/media/2010-07-10-rendering-toolchain-performance.pdf

Adjust settings in your postgresql.conf file

• shared_buffers 128MB -> 768MB
• work_mem 1MB -> 512MB
• maintenance_work_mem 16 -> 512MB
• fsync on -> off

Create another user and password

From command line... if you want to use database authentication

psql -U postgres
CREATE USER bwilson WITH PASSWORD 'jackalope';

To see the user table

SELECT * FROM pg_authid;

Install PostGIS Template

Old way: Adding a template makes it much easier to create Postgis databases. New way:

createdb -U postgres my_gisdb
psql -U postgres my_gisdb
# CREATE EXTENSION postgis;
CREATE EXTENSION

Et voila! You should be able to do this now:

# SELECT postgis_full_version();

and if you installed PostGIS 2.1.6, you will see this result

POSTGIS="2.1.6 r13384" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.7.1, 23 September 2009" GDAL="GDAL 2.0.0dev, released 2014/04/16" LIBXML="2.8.0" LIBJSON="UNKNOWN" (core procs from "2.1.0 r11822" need upgrade) RASTER (raster procs from "2.1.0 r11822" need upgrade)

Loading data into PostGIS

I have moved everything that used to be here to its own page, because this is a pretty big topic. Go look at Loading data into PostGIS

Exporting data from PostGIS

If you want to go back to a plain old shapefile, use pgsql2shp

Accessing data

ArcMap Desktop 10.3

I have moved this to its own page. ArcGIS + PostGIS is an awesome (and affordable) combination.

QGIS

QGIS and PostGIS are made for each other, so any guide to QGIS will tell you how to use PostGIS as repository. You can use QGIS to read and write data stored in PostGIS.

Web sites

You can read data directly from PostGIS or you can try using an intermediary. This allows more flexibility. For example you can keep raw spatial data in PostGIS, and create layers in GeoServer complete with styles aka symbologies, and then build up a web map from your own and other services using OpenLayers.

I have been working with GeoServer but many people like MapServer. There are other choices.

Reprojection

CREATE TABLE "wa_counties_p" (gid serial PRIMARY KEY,
"county_cod" int2,"county_fip" varchar(3),"county_nm" varchar(15),
"ecy_region" varchar(4),"air_region" varchar(46));

SELECT AddGeometryColumn(,'wa_counties_s','the_geom','-1','MULTIPOLYGON',2);

ALTER TABLE wa_counties_p DROP CONSTRAINT "enforce_srid_the_geom" RESTRICT;

INSERT INTO wa_counties_p SELECT gid,county_cod, county_fip, county_nm, ecy_region, air_region, transform(the_geom, 102348) FROM wa_counties;

ALTER TABLE wa_counties_p ADD CONSTRAINT "enforce_srid_the_geom"CHECK(SRID(the_geom)=102348);

Additional resources

Postgis wiki http://trac.osgeo.org/postgis/wiki/UsersWikiMain

An article of interest

http://www.bostongis.com/PrinterFriendly.aspx?content_name=sqlserver2008_postgis_mysql_compare

== Other ==

From some forum or other

"574 post(s)

1. 23-Mar-09 20:06

I suppose I could share a few things that I have found out through trial and error. I am certainly no expert.

I am running on windows server 2003 32 bit, so if you are running 64 bit or *nix versions, the experiences could be very different.

1. Make sure you have the latest version of postgres / postGIS. In another thread you mentioned that you have only just installed it, so I would think that this is the case. Version Postgres 8.3 has a nice easy windows installer that makes it a piece of cake to install. The application Stack Builder picks up the latest postGIS version, so there is no need to worry too much about installing postGIS seperately.

2. I found that installing the Tuning wizard that can be found in the Application Stack Builder helps to configure the memory setting quite well. For my purposes, I didn't need to tweak any of the memory setting into the postgresql.conf file.

3. If you are planning on doing spatial queries on any datasets that you export to postgres, then make sure you have spatial indexes on the geometry field. When you export from manifold using the Postgres type, then these indexes are created automatically. I would suggest reading the postgis user manual, it is pretty comprehensive, and fairly easy to follow. It will give you a good idea of what is possible with spatial queries. It is available http://postgis.refractions.net/download/postgis-1.3.5.pdf

4. The postGIS email list is very helpful. See http://postgis.refractions.net/mailman/listinfo/postgis-users to join.

5. If you are doing joins between tables in the database, then you will have to manually set up the indexes. If you are using pgAdmin III (which I like), then before you run queries, you can see if your indexes are being used by using explain.

--sql (for use within postgresql (or in the database manager console in manifold)

explain select * from test limit 1000;

This will tell you what type of scan is being done. In the above case it is always going to be a sequential scan, due to the nature of the query. If you are doing a query like:

select * from "PARCEL_MAPPING" INNER JOIN "100K index" ON st_intersects("PARCEL_MAPPING".geometry, "100K index".geometry)

where "100K index"."Sheet_Name" = 'DANYO'

There is an explain button in the query editor in pgAdmin III which shows this in a graphic form so you can see pretty quickly if you aren't taking advantage of your indexes.

6. Also as mentioned above, it is a good idea to do vaccuum analyzes often as these help indexes perform better.

7. If you are using large drawings that have lots of drawing objects, then make use of the AOI windowing when linking the drawings. Also see http://forum.manifold.net/forum/t64537.18 for a handy tool to help out.

8. Change the extents() aggregate function in postgres. Manifold uses this when linking drawings (for which reason I don't know). It is VERY slow on large datasets. If you use the tool mentioned above, you don't need the extents function at all. All I did was rename the function so I could still use it if I had to in code and postGIS, but Manifold wasn't automatically trying to use it.

--sql (in postgres)

DROP AGGREGATE extent(geometry);

CREATE AGGREGATE _extent(geometry) (

 SFUNC=public.st_combine_bbox,

 STYPE=box2d

);

That is about all I can think of for the moment, but I am sure others have more tips.

Hope this helps

James"