View source for PostGIS

PostGIS spatially enables the [[PostgreSQL]] open source DBMS

Page updated: 30 Jan 2012 -- topology

== Goal: Build an enterprise geodatabase server ==

1. I am trying to build a GIS data warehouse with a catalog that can be queried in many different ways.

2. I want to have a rich suite of spatial tools available to operate on the data in the warehouse.

== The hardware ==

Places that I have it running now include:

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

At work, a spiffy Intel i7/RAID 5 box running Debian 6. 

For Geo-CEG, a Supermicro based Xeon box with SSD + SAS + SATA drives running CentOS 6

On the Internet, a virtual machine at Tektonic.net called a VPS (Virtual Private Server) [http://hupi.org HuPI.org]

On the road, [[Stellar]], a MacBook Pro with 8GB of RAM

It turns out the Tektonic servers do not have enough RAM to do this job.
Mostly due to trying to run [[Geoserver]] and Tomcat.

== The software ==

Operating system: Ubuntu Server 10.04 and Debian 6
I am installing for both PostGIS 1.5 (so I can use [[pgMap]] 
and PostGIS 2.0 (so I can use rasters)

=== 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.1.6 (on older Linux systems; using pre-built binary on Mac and apt-get on Ubuntu 12.04)

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

==== Ubuntu 12.10 ====

 apt-get install postgresql-client libpq-dev pgadminIII

==== CentOS 6.2 ====

Download the PostgreSQL package from EnterpriseDB.com and install it. Shut down the old version.

To migrate my data from the previous locally compiled 9.1 I used pg_upgrade.

Old version is installed in /usr/local/pgsql
New version is in /opt/postgres

 service postgresql-9 stop

 
 pg_upgrade --check --verbose -b /usr/local/pgsql/bin -B /opt/ -d /var/lib/postgresql/data -D /var/lib/postgresql-9.2/data
 

Now I can remove the old version

==== 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

<pre>
# Localization for PostgreSQL and PostGIS -----
###############################################################3
# POSTGRESQL

PGHOME=/usr/local/pgsql
export PGBIN=$PGHOME/bin
export PGLIB=$PGHOME/lib
export PGDATA=/var/lib/postgresql/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

###############################################################3
# POSTGIS

##
export PATH
</pre>

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 ====

GEOS (installed into /usr/local):
 sudo apt-get install swig php5-dev phpunit
 wget http://download.osgeo.org/geos/geos-3.3.7.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

==== 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 \
 --with-curl \
 --with-openjpeg --with-geos --with-geotiff \
 --with-jpeg --with-png \
 --with-expat --with-libkml --with-xerces-c \
 --with-mrsid=/home/bwilson/src/GIS/MrSID/Raster_DSDK \
 --with-java=/opt/oracle/jdk1.7.0_09 --with-mdb \
 --with-fgdb=/home/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 ====

First off don't do what I did:

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

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.0 ====

Building from source

(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'''

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

'''Building on Debian'''

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

Today the output from "configure" on Dart looks like this

  PostGIS is now configured for x86_64-unknown-linux-gnu

 -------------- Compiler Info ------------- 
  C compiler:           gcc -g -O2
  C++ compiler:         g++ -g -O2
  SQL preprocessor:     /usr/bin/cpp -traditional-cpp -P

 -------------- Dependencies -------------- 
  GEOS config:          /usr/local/bin/geos-config
  GEOS version:         3.3.6
  GDAL config:          /usr/local/bin/gdal-config
  GDAL version:         1.10.0
  PostgreSQL config:    /usr/local/pgsql/bin/pg_config
  PostgreSQL version:   PostgreSQL 9.1.6
  PROJ4 version:        48
  Libxml2 config:       /usr/bin/xml2-config
  Libxml2 version:      2.7.6
  JSON-C support:       yes
  PostGIS debug level:  0
  Perl:                 /usr/bin/perl

 --------------- Extensions --------------- 
  PostGIS Raster:       enabled
  PostGIS Topology:     enabled

 -------- Documentation Generation -------- 
  xsltproc:             /usr/bin/xsltproc
  xsl style sheets:     /usr/share/sgml/docbook/xsl-stylesheets
  dblatex:              
  convert:              
  mathml2.dtd:          http://www.w3.org/Math/DTD/mathml2/mathml2.dtd

This all looks good to me so I press on

 make -j 10 && sudo make install

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

== Server management ==

You have to create the databases etc 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 a data storage area (what's the Postgres term for that? Umm... a cluster? I forget.) 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

=== 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 ==

Adding a template makes it much easier to create Postgis databases.
Refer to PostGIS in Action Appendix B p424 
Note this is good for development not for production due to relaxed security.

 cd /usr/local/pgsql/share/contrib/postgis-2.1
 psql -U postgres
 CREATE DATABASE template_postgis WITH TEMPLATE = template1 ENCODING = 'UTF8';
 \c template_postgis
 \i postgis.sql
 \i spatial_ref_sys.sql
 # For 2.0+, add raster and topology support too
 \i topology.sql
 \i rtpostgis.sql
 UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template_postgis';
 GRANT ALL ON geometry_columns TO PUBLIC;
 GRANT ALL ON spatial_ref_sys TO PUBLIC;
 \q

Once the template is installed you can easily create a PostGIS database:

 createdb -U postgres my_gisdb -W -T template_postgis

You should be able to do this now:

 '''psql -U postgres'''
 # '''\c my_gisdb'''
 # '''SELECT postgis_full_version();'''
  POSTGIS="1.5.3" GEOS="3.3.1-CAPI-1.7.1" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.6" USE_STATS

or if you installed PostGIS 2.0, you will see this result
 POSTGIS="2.0.0SVN" GEOS="3.3.1-CAPI-1.7.1" PROJ="Rel. 4.7.1, 23 September 200
9" LIBXML="2.7.8" USE_STATS

== Loading data into PostGIS ==

Just a bit here so you can do some initial tests.

I have more extensive notes on loading data here: [[Loading data into PostGIS]]

=== With '''shp2pgsql''' command ===

Convert city polygon shapefile into SQL commands

 shp2pgsql /export/kilchis/temp/Ann/IncidentView/Data/WA/South_King_Fire/AGI_shapefiles/Cities_revised.shp cities > cities.sql

Load the data now by executing the SQL

 psql -U postgres -d WA_South_King < cities.sql

With gdal '''ogr2ogr''' command, I can reproject while loading:

 ogr2ogr -f "PostgreSQL" -t_srs 2991 OR_Corvallis Water.shp

=== Query data ===

Did the load work? Try a simple query.

 '''psql -U postgres -d WA_South_King
 SELECT name FROM cities;'''
      name
 ------------------
 Shoreline
 Lake Forest Park
 Kenmore
 Bothell
 ...

The glory! Now try a geometric query

 '''SELECT name, AREA(the_geom) FROM cities ORDER BY AREA(the_geom);'''
       name       |       area
 ------------------+------------------
 Beaux Arts       | 2308400.44773863
 RUSTON           | 7069686.79758783
 Hunts Point      | 8537598.97906794
 Skykomish        | 9349760.74397126
 ...

=== Setting the SRID (Spatial reference ID) ===

First I look at the shapefiles to see what the PRJ portion says. Then 
I look up the SRID in the Proj file /usr/share/proj/esri

Cities is WA North = '''102348''' and Counties is WA South = '''102349'''

Rivers is Oregon State Lambert (Feet) = '''2992'''

NAD_1983_HARN_StatePlane_Oregon_North_FIPS_3601_Feet_Intl = EPSG:2913
(See http://www.ittvis.com/services/Files/envi_pe/envi_pe_v10/EnviPEReferenceDocs/EnviPEProjcsStrings_v10.txt)

New short way

 UpdateGeometrySRID([<schema_name>], <table_name>, <column_name>, <srid>)

for example, 
 psql -d crbc_spatial
 crbc_spatial=> '''SELECT UpdateGeometrySRID('rivers', 'the_geom', 2992);'''
 crbc_spatial=> 

Long old way

 ALTER TABLE cities DROP CONSTRAINT "enforce_srid_the_geom" RESTRICT;
 UPDATE cities SET the_geom=ST_SetSRID(the_geom, 102348);
 ALTER TABLE cities ADD CONSTRAINT "enforce_srid_the_geom" CHECK(SRID(the_geom)=102348);
 UPDATE geometry_columns set SRID=102348 WHERE f_table_name='cities';

 ALTER TABLE WA_Counties DROP CONSTRAINT "enforce_srid_the_geom" RESTRICT;
 UPDATE wa_counties SET the_geom=ST_SetSRID(the_geom, 102349);
 ALTER TABLE wa_counties ADD CONSTRAINT "enforce_srid_the_geom" CHECK(SRID(the_geom)=102349);
 UPDATE geometry_columns set SRID=102349 WHERE f_table_name='wa_counties';

=== Spatial queries ===

I have also added counties, WA_Counties table. So I can see what cities fall inside King county. The problem is that these tables are now in different projections, so the query fails. 

 SELECT cities.name, WA_Counties.county_nm FROM cities INNER JOIN WA_Counties ON (cities.the_geom && WA_Counties.the_geom AND intersects(cities.the_geom, WA_Counties.the_geom));
 ERROR:  Operation on two geometries with different SRIDs

I cannot use the transform() function though because my SPATIAL_REF_SYS table is not loaded with the ESRI values! Que mal!

Use this GDAL util to convert the ESRI codes to Proj.4 codes
 
 cd src/FWTools-2.0.6
 export PYTHONPATH=`pwd`/pymod
 export LD_LIBRARY_PATH=`pwd`/lib
 cd share
 ../bin/epsg_tr.py -postgis -list esri_extra.wkt > ~/esri.sql

Load them into the database.

 su postgres
 psql -U postgres -d WA_South_King < /home/AGI/bwilson/esri.sql

Now transform() should work.

== 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);

== Exporting data from PostGIS ==

pgsql2shp

== Integration with ArcGIS Desktop ==

RIP ZigGIS.
See [[SpatialKit]] instead.

== Integration with a Web server ==

See [[GeoServer]]

== 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)
	
#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"

[[Category:GIS]]
[[Category:PostgreSQL]]