PostGIS
PostGIS spatially enables the PostgreSQL open source DBMS
Page updated: 01 January 2012 HAPPY NEW YEAR!!
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.
On the Internet, a virtual machine at Tektonic.net called a VPS (Virtual Private Server) 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
PostgreSQL 9.1.2 (on Linux systems; using pre-built binary on Mac)
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!
On my Debian and 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.2/postgresql-9.1.2.tar.gz tar xzvf postgresql-9.1.2.tar.gz cd postgresql-9.1.2 ./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 ----- ###############################################################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
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.1 (potential conflict with PostGIS 1.5.3) GDAL 1.9.0 (beta 2) GEOS 3.3.1 (2011/09/27) Might need source for openjpeg and kml
GEOS
GEOS (installed into /usr/local):
sudo apt-get install swig php5-dev phpunit ./configure --enable-python --enable-php make && 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 get version 1.9.
Get GDAL sources
sudo apt-get install subversion cd ~/src/GIS svn checkout https://svn.osgeo.org/gdal/trunk/gdal gdal
After initial checkout 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 libpoppler5 libpoppler-dev sudo apt-get install expat libexpat1-dev libkml-dev libxerces sudo apt-get install libcurl4-openssl-dev
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_1-64.tar.gz
Do the configure and build
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-fgdb=/home/bwilson/src/GIS/FileGDB_API \ --with-java=/usr/local/java/jdk1.7.0_01 --with-mdb make && sudo make install gdalinfo --formats # confirm the formats you need are listed ogrinfo --formats # confirm the formats you need are listed
Note the MrSID and ESRI lines require a complete path for some reason. Probably my fault, a relative path should be good enough.
PostGIS
Postgis 1.5.3 Postgis 2.0
PostGIS 1.5
Ordinarily it is handy to do something like this, but this gets too much! It picks up binaries that we want to build from source, to get the most up to date versions. Then you have to wrestle with having 2 versions of various things and it's too much work.
# sudo apt-get build-dep postgis # Don't do this!
Instead I install the packages individually.
sudo apt-get install libxml2-dev cd src/GIS/ wget http://www.postgis.org/download/postgis-1.5.3.tar.gz tar xzvf postgis-1.5.3.tar.gz cd postgis-1.5.3 ./configure \ --with-pgconfig=/usr/local/pgsql/bin/pg_config \ --with-geosconfig=/usr/local/bin/geos-config make sudo make install
PostGIS 2.0
Building from source
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 Debian
./configure --with-pgconfig=/usr/local/pgsql/bin/pg_config \ --with-geosconfig=/usr/local/bin/geos-config \ --with-topology --with-raster make sudo make install
Building on Mac seemed promising but I failed.
Server management
You have to create the databases etc for Postgres. See the INSTALL file in the sources.
su adduser postgres mkdir $PGDATA chown postgres $PGDATA su - postgres initdb -D /usr/local/pgsql/data exit /etc/init.d/postgresql-9.0 start exit createdb -U postgres test psql -U postgres test
Create user and password for phppgadmin
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;
Now user bwilson should be able to use phppgadmin
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.0 psql -U postgres CREATE DATABASE template_postgis WITH TEMPLATE = template1 ENCODING = 'UTF8'; \c template_postgis \i postgis.sql \i spatial_ref_sys.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
Lots of our data comes in the form of shapefiles, for this I can use shp2pgsql
For ESRI data then I think the best tack is to write a
geoprocessing Python script - It can use the ESRI proprietary code to read any feature class using a cursor and load them into postgis.
To do this I need to be able to write to the PostGIS database from my Python script.
I have more extensive notes on loading data here: Loading data into PostGIS
Load data
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"
