PostGIS: Difference between revisions

From Wildsong
Jump to navigationJump to search
Brian Wilson (talk | contribs)
Brian Wilson (talk | contribs)
 
(81 intermediate revisions by the same user not shown)
Line 1: Line 1:
PostGIS spatially enables the [[PostgreSQL]] open source DBMS
PostGIS is an extension for [[PostgreSQL]] that spatially enables databases.


Page updated: 30 Jan 2012 -- topology
Currently I am going to do an install on Bellman (no Docker) for performance reasons. The database will run natively. Other services like GeoServer will still be Dockerized for convenience.


== Goal: Build an enterprise geodatabase server ==
For my day job, I work with ESRI software. I used to be a pariah because I also used PostGIS/PostgreSQL. Those days are over,
now you can go to ESRI for help with installing it, ESRI even uses PostgreSQL inside their Datastore component; times change.


1. I am trying to build a GIS data warehouse with a catalog that can be queried in many different ways.
== PostGIS on Bellman (Ubuntu Server 20.4) ==


2. I want to have a rich suite of spatial tools available to operate on the data in the warehouse.
# Install PostgreSQL
# Install PostGIS 3.1.1


== The hardware ==
I will try using [https://wiki.ubuntu.com/UbuntuGIS UbuntuGIS] because that's the simplest approach and they appear to have the latest PostGIS in their archive. This is what I did.


Places that I have it running now include:
sudo apt-get install software-properties-common
sudo add-apt-repository ppa:ubuntugis/ppa
sudo apt update
sudo apt install postgis


At home, [[Bellman]], a little AMD 350 M-1 based server running Debian 6.  
...and then I waited... It installed postgis 3.1.1 on top of postgresql 12, in about ''2 minutes''. No big deal.


At work, a spiffy Intel i7/RAID 5 box running Debian 6.  
I will put pgadmin, geoserver and geowebcache in dockers though.


For Geo-CEG, a Supermicro based Xeon box with SSD + SAS + SATA drives running CentOS 6
== PostGIS on CC-Testmaps (Debian 10) for use with ESRI ArcGIS Enterprise ==


On the Internet, a virtual machine at Tektonic.net called a VPS (Virtual Private Server) [http://hupi.org HuPI.org]
Start off by realizing that there is an st_geometry.so file you'll need and that it's available only for an older version of PostgreSQL.
You can find it nestled in the desktop software near C:/Program Files (x86)/ArcGIS/Desktop/DatabaseSupport/
and in the Windows server at C:/Program Files/ArcGIS/Server/DatabaseSupport/PostgreSQL/12/Linux64


On the road, [[Stellar]], a MacBook Pro with 8GB of RAM
Be amused they are different versions of the file, the one in Server is probably newer since it's 10.9 and Desktop is 10.8.1.
Find further amusement in the fact that there is no Postgres corresponding support file in Pro 2.8 even though there is a  
Resources/DatabaseSupport/ folder.


It turns out the Tektonic servers do not have enough RAM to do this job.
Following the pattern for Ubuntu, how about [https://wiki.debian.org/DebianGis Debian GIS] ?? Well, it's just Debian.
Mostly due to trying to run [[Geoserver]] and Tomcat.
So, the real question is, are Postgres and PostGIS new enough? I need Postgresql 12+ and PostGIS 3+.
As usual, they lag behind so they are at Postgresql 11 and PostGIS 2.5.


== The software ==
I am going the PostgreSQL route with


Operating system: Ubuntu Server 10.04 and Debian 6
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
I am installing for both PostGIS 1.5 (so I can use [[pgMap]]
echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee  /etc/apt/sources.list.d/pgdg.list
and PostGIS 2.0 (so I can use rasters)
apt install postgresql-12-postgis-3


=== PostgreSQL ===
That gets everything up and running and I can connect as user "postgres" with "psql postgres".


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


(See also my [[PostgreSQL]] page.)
https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-postgresql/database-requirements-postgresql.htm


PostgreSQL 9.1.6 (on older Linux systems; using pre-built binary on Mac and apt-get on Ubuntu 12.04)
Today it says "minimum requirements" include "PostgreSQL 9.6.3 (64 bit) + PostGIS 2.3" and it says
"newer minor versions are supported, but are not certified."


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!
I tried to get the FME "Data Interoperability" extension working. I was able to create an ETL tool, but it went off a cliff then complaining that I did not have a license. (I actually did when I ran this test.)


==== Ubuntu 12.10 ====
If you have a commercial license it's the standard $2500 extra. It comes with the personal and non-profit versions.  


apt-get install postgresql-client libpq-dev pgadminIII
The FME model is ETL -- extract data from native format (PostGIS in this case), transform to ESRI format, load it into an ESRI database.
All this duplication and copying misses the point of using a database in the first place. And also it means GDAL works fine for the same process. QGIS works even better since it lets me use the native formats directly with no commotion.


==== CentOS 6.2 ====
== Vector Tiles ==


./configure --prefix=/opt/postgresql/9.1.6 --with-python
I made a note to check this guy's article: [https://www.zimmi.cz/posts/2017/postgis-as-a-mapbox-vector-tiles-generator/ PostGIS as a MapBox Vector Tile Generator]
# let's build all the extensions
so I did, it's about PostGIS 2.4 so only a bit dated now. See this for news: https://info.crunchydata.com/blog/waiting-for-postgis-3-st_asmvt-performance
# let's whip through this by using 10 CPU cores! Get it over with!
make -j 10 world
sudo make install


# '''service postgresql-9 stop'''
== PostGIS in Docker ==


PGDATA is set in the script /etc/profile.d/postgres.sh
I use Docker Compose to put together PostGIS and Geoserver.
 
I document it in the [[Geoserver]] page.
# '''pg_upgrade --check --verbose -b /usr/local/pgsql/bin -B /opt/postgresql/9.2.3/bin -d /var/lib/postgresql/data -D /var/lib/postgresql-9.2/data'''
Now I can remove the old version


==== Pre 12.04 Linux ====
It looks like I will be doing more work with building my own PostGIS docker container to get support for the latest versions.
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:
I am leaving some notes on building from source here.
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
=== Building PostGIS 3 on the efforts of others ===


cd ~/src/GIS
PostGIS repo: https://git.osgeo.org/gitea/postgis/postgis
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.
The official build environment, owned by postgis account at hub, account created by Sandro
https://hub.docker.com/r/postgis/postgis-build-env


I create a script in /etc/profile.d/postgresql.sh to set PATH etc. like this
Looks like the github for this image is here: https://github.com/postgis/postgis-build-env


<pre>
At OSGEO "Docker images for PostGIS", from Sandro
# Localization for PostgreSQL and PostGIS -----
https://git.osgeo.org/gitea/postgis/postgis-docker
###############################################################3
# POSTGRESQL


PGHOME=/usr/local/pgsql
Another one
export PGBIN=$PGHOME/bin
https://github.com/mattyb/docker-postgis-dev
export PGLIB=$PGHOME/lib
export PGDATA=/var/lib/postgresql/data
export PGPORT=5432


# I wonder what this does. Same as mv -i option maybe.
==== GDAL ====
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
Building GDAL is the most convoluted and time consuming step in installing PostGIS from sources.
wget proj tar gz
But it also gives you the best support for the files you need to use, and it is probably necessary
./configure
if you want the newest version of PostGIS.
make && sudo make install


==== GEOS ====
'''TODO''' might want to add SFCGAL support but I don't need it yet.


GEOS (installed into /usr/local):
  sudo apt install postgresql-server-dev-9.6
  sudo apt-get install swig php5-dev phpunit
  sudo apt install python-cxx-dev
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 ====
I always need features not yet released in packaged form, so install the subversion source code to track the latest.


CentOS
Get GDAL sources for example
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
  cd ~/src/GIS
  svn checkout https://svn.osgeo.org/gdal/trunk/gdal gdal
  wget http://download.osgeo.org/gdal/2.2.4/gdal-2.2.4.tar.gz


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.
"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
  sudo apt install libopenjp2-7 libopenjp2-7-dev
  svn checkout http://libkml.googlecode.com/svn/trunk/ libkml-read-only
  sudo apt install libpoppler-dev
  cd libkml-read-only
  sudo apt install expat libexpat1-dev libkml-dev libxerces-c-dev
./autogen.sh
  sudo apt install libcurl4-openssl-dev
./configure
  sudo apt install libsqlite3-dev
  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.  
'''ESRI FGDB support''': OpenFGDB gives access to version 9 fgdb files and read only, ESRI library only gives version 10 access and write access.  
Download kit from http://resources.arcgis.com/content/geodatabases/10.0/file-gdb-download and unpack it
If you want it anyway, download it from ESRI and unpack it.
They move it around, everytime I need it it's in a new place. Try http://appsforms.esri.com/products/download/


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


ESRI MDB support: See http://www.gdal.org/ogr/drv_mdb.html
'''ESRI MDB support:''' Who cares? Personal geodatabases are so rare these days I no longer bother to include this.
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
Install a Java JDK (I use version 7) and get the jackess library: http://jackcess.sourceforge.net/ and put it in this MDB folder


Line 209: Line 136:


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


  cd ~/src/GIS/gdal
  cd ~/src/GIS/gdal
  ./configure \
  ./configure \
  --with-python --with-poppler \
  --with-python --with-poppler \
  --with-pg \
  --with-pg=/usr/bin/pg_config \
  --with-curl \
  --with-curl \
  --with-openjpeg --with-geos --with-geotiff \
  --with-openjpeg --with-geos --with-geotiff \
  --with-jpeg --with-png \
  --with-jpeg --with-png \
  --with-expat --with-libkml --with-xerces-c \
  --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 \
Optionally add
  --with-fgdb=/home/bwilson/src/GIS/FileGDB_API \
  --with-java=/opt/oracle/jdk1.7.0_75
  --with-spatialite
  --with-fgdb=/home/bwilson/src/GIS/FileGDB_API-64
  --with-mrsid=/home/bwilson/src/GIS/MrSID/Raster_DSDK


Note the MrSID and ESRI lines require a complete path for some reason.
Note the MrSID line requires a complete path for some reason. I don't use MrSID so I don't build it in right now. (2017-02)
Probably my fault somehow, I think a relative path should be good enough.
Probably my fault somehow, I think a relative path should be good enough.


  make
Once configure is done make sure the options you selected show 'yes'. Else adjust and repeat...
 
  make -j 14
  sudo make install
  sudo make install
sudo ldconfig -v


'''Confirm that the version you just built is really the one found.'''
'''Confirm that the version you just built is really the one found.'''
Line 234: Line 164:


  gdalinfo --version
  gdalinfo --version


'''Confirm the formats you need are listed with these commands'''
'''Confirm the formats you need are listed with these commands'''
Line 248: Line 177:


  $ '''python'''
  $ '''python'''
  Python 2.6.6 (r266:84292, Sep 11 2012, 08:34:23)  
  Python 2.7.13 (default, Nov 24 2017, 17:33:09)  
  [GCC 4.4.6 20120305 (Red Hat 4.4.6-4)] on linux2
  [GCC 6.3.0 20170516] on linux2
  Type "help", "copyright", "credits" or "license" for more information.
  Type "help", "copyright", "credits" or "license" for more information.
  >>> '''import osgeo.gdal'''
  >>> '''import osgeo.gdal'''
  >>> '''print osgeo.gdal.__version__'''
  >>> '''print osgeo.gdal.__version__'''
  1.10dev
  2.20dev
  >>>  
  >>>  
YAY!
YAY!


Line 262: Line 190:
==== Installing the prerequisites ====
==== Installing the prerequisites ====


First off don't do what I did:
'''Don't do this''': sudo apt-get build-dep postgis
 
# 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!
Ordinarily it is handy to do something like this to pick up all the build dependencies, but this downloads and installs too much!
Line 274: Line 200:
all I needed to do at this stage was this:
all I needed to do at this stage was this:


  sudo apt-get install libxml2-dev
  sudo apt-get install libxml2-dev libjson-c-dev libproj-dev


My (Debian/Ubuntu) systems already had JSON support but you might need it
The PostGIS trunk wants the latest unreleased GEOS.
As long as I am building it, I opt for the python module too.


  sudo apt-get install libjson0-dev
  sudo apt install swig
cd ~/src/GIS
git clone https://git.osgeo.org/gitea/geos/geos.git
cd geos
sh autogen.sh
./configure --enable-python
make -j 7


CentOS
==== PostGIS 2.4 ====
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 -- I do it to get features and performance.


Building from source
sudo apt-get install xsltproc


(either) Direct checkout to get the very latest including topology and raster
(either) Direct checkout to get the very latest including topology and raster
  svn checkout http://svn.osgeo.org/postgis/trunk postgis-svn
  git clone https://git.osgeo.org/gitea/postgis/postgis
  cd postgis-svn
  cd postgis
sudo apt install autoconf libtool
  sh autogen.sh
  sh autogen.sh


(or) Tarball download to get almost the latest
(or) Do a traditional 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'''
'''Building on Debian and Ubuntu'''
By the time you've made it to this stage the actual PostGIS build seems trivial.


  ./configure \
  ./configure --with-raster
--with-pgconfig=/usr/local/pgsql/bin/pg_config \
  make -j 10
--with-geosconfig=/usr/local/bin/geos-config \
  sudo make install
--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. I use a Linux virtual machine for testing.
 
'''Building on Mac''' seemed promising but I failed. Using binary packages there for now.


== Server management ==
== 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.
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
Create a user to own the postgres files
Line 368: Line 245:
  adduser postgres
  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.
Create your data storage area(s) (what's the Postgres term for that? it's a "cluster".) This assumes you set PGDATA correctly for your system.


  mkdir $PGDATA
  mkdir $PGDATA
Line 385: Line 262:


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


Create a generic database to test postgres.
Create a generic database to test postgres.
Line 397: Line 274:


  dropdb -U postgres test
  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 ===
=== Create another user and password ===
Line 403: Line 291:


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


To see the user table
To see the user table
Line 410: Line 298:
== Install PostGIS Template ==
== Install PostGIS Template ==


Adding a template makes it much easier to create Postgis databases.
  '''createdb -U postgres gis_data'''
Refer to PostGIS in Action Appendix B p424
  '''psql -U postgres gis_data'''
Note this is good for development not for production due to relaxed security.
  # '''CREATE EXTENSION postgis;'''
 
  CREATE EXTENSION
  cd /usr/local/pgsql/share/contrib/postgis-2.1
  # '''CREATE EXTENSION postgis_topology;'''
  psql -U postgres
  CREATE EXTENSION
  CREATE DATABASE template_postgis WITH TEMPLATE = template1 ENCODING = 'UTF8';
  # '''CREATE EXTENSION address_standardizer;'''
  \c template_postgis
  CREATE EXTENSION
\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:
Et voila! You should be able to do this now:


'''psql -U postgres'''
# '''\c my_gisdb'''
  # '''SELECT postgis_full_version();'''
  # '''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
and you will see this result or something similar to this:
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 ==
POSTGIS="3.1.2 cbe925d" [EXTENSION] PGSQL="130" GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 5.2.0, September 15th, 2018" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.3.1" WAGYU="0.5.0 (Internal)" TOPOLOGY
(1 row)


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


I have more extensive notes on loading data here: [[Loading data into PostGIS]]
Suggestions from the switch2osm page,


=== With '''shp2pgsql''' command ===
sudo apt-get install munin munin-plugins-extra munin-node iotop ptop


Convert city polygon shapefile into SQL commands
See also http://aouyar.github.io/PyMunin/plugins/postgresql.html
which I don't have set up yet.


shp2pgsql /export/kilchis/temp/Ann/IncidentView/Data/WA/South_King_Fire/AGI_shapefiles/Cities_revised.shp cities > cities.sql
== Loading data into PostGIS ==


Load the data now by executing the SQL
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]]


psql -U postgres -d WA_South_King < cities.sql
== Exporting data from PostGIS ==


With gdal '''ogr2ogr''' command, I can reproject while loading:
If you want to go back to a plain old shapefile (the horror!), use
pgsql2shp


ogr2ogr -f "PostgreSQL" -t_srs 2991 OR_Corvallis Water.shp
== Accessing data ==


=== Query data ===
=== ArcMap Desktop ===


Did the load work? Try a simple query.
See [[ArcGIS + PostGIS]].


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


The glory! Now try a geometric query
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.


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


=== Setting the SRID (Spatial reference ID) ===
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, and then build up a web map from your own and other services using [[OpenLayers]] or Leaflet.


First I look at the shapefiles to see what the PRJ portion says. Then
I have been working with [[GeoServer]] but many people like [[MapServer]]. There are other choices.
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 ==
== Reprojection ==
Line 559: Line 364:
  ALTER TABLE wa_counties_p ADD CONSTRAINT "enforce_srid_the_geom"CHECK(SRID(the_geom)=102348);
  ALTER TABLE wa_counties_p ADD CONSTRAINT "enforce_srid_the_geom"CHECK(SRID(the_geom)=102348);


== Exporting data from PostGIS ==
== Additional resources ==


pgsql2shp
Postgis wiki http://trac.osgeo.org/postgis/wiki/UsersWikiMain
 
== Integration with ArcGIS Desktop ==
 
RIP ZigGIS.
See [[SpatialKit]] instead.
 
== Integration with a Web server ==


See [[GeoServer]]
The manual https://postgis.net/docs/manual-dev/
 
== Resources ==
 
Postgis wiki http://trac.osgeo.org/postgis/wiki/UsersWikiMain


An article of interest
An article of interest
Line 580: Line 374:
http://www.bostongis.com/PrinterFriendly.aspx?content_name=sqlserver2008_postgis_mysql_compare
http://www.bostongis.com/PrinterFriendly.aspx?content_name=sqlserver2008_postgis_mysql_compare


== Other ==  
=== Other ===


From some forum or other
From some forum or other
Line 608: Line 402:
explain select * from test limit 1000;
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:
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:
Line 647: Line 440:


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

Latest revision as of 17:24, 30 June 2021

PostGIS is an extension for PostgreSQL that spatially enables databases.

Currently I am going to do an install on Bellman (no Docker) for performance reasons. The database will run natively. Other services like GeoServer will still be Dockerized for convenience.

For my day job, I work with ESRI software. I used to be a pariah because I also used PostGIS/PostgreSQL. Those days are over, now you can go to ESRI for help with installing it, ESRI even uses PostgreSQL inside their Datastore component; times change.

PostGIS on Bellman (Ubuntu Server 20.4)

  1. Install PostgreSQL
  2. Install PostGIS 3.1.1

I will try using UbuntuGIS because that's the simplest approach and they appear to have the latest PostGIS in their archive. This is what I did.

sudo apt-get install software-properties-common
sudo add-apt-repository ppa:ubuntugis/ppa
sudo apt update
sudo apt install postgis

...and then I waited... It installed postgis 3.1.1 on top of postgresql 12, in about 2 minutes. No big deal.

I will put pgadmin, geoserver and geowebcache in dockers though.

PostGIS on CC-Testmaps (Debian 10) for use with ESRI ArcGIS Enterprise

Start off by realizing that there is an st_geometry.so file you'll need and that it's available only for an older version of PostgreSQL. You can find it nestled in the desktop software near C:/Program Files (x86)/ArcGIS/Desktop/DatabaseSupport/ and in the Windows server at C:/Program Files/ArcGIS/Server/DatabaseSupport/PostgreSQL/12/Linux64

Be amused they are different versions of the file, the one in Server is probably newer since it's 10.9 and Desktop is 10.8.1. Find further amusement in the fact that there is no Postgres corresponding support file in Pro 2.8 even though there is a Resources/DatabaseSupport/ folder.

Following the pattern for Ubuntu, how about Debian GIS ?? Well, it's just Debian. So, the real question is, are Postgres and PostGIS new enough? I need Postgresql 12+ and PostGIS 3+. As usual, they lag behind so they are at Postgresql 11 and PostGIS 2.5.

I am going the PostgreSQL route with

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee  /etc/apt/sources.list.d/pgdg.list
apt install postgresql-12-postgis-3

That gets everything up and running and I can connect as user "postgres" with "psql postgres".

ArcGIS Desktop

https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-postgresql/database-requirements-postgresql.htm

Today it says "minimum requirements" include "PostgreSQL 9.6.3 (64 bit) + PostGIS 2.3" and it says "newer minor versions are supported, but are not certified."

I tried to get the FME "Data Interoperability" extension working. I was able to create an ETL tool, but it went off a cliff then complaining that I did not have a license. (I actually did when I ran this test.)

If you have a commercial license it's the standard $2500 extra. It comes with the personal and non-profit versions.

The FME model is ETL -- extract data from native format (PostGIS in this case), transform to ESRI format, load it into an ESRI database. All this duplication and copying misses the point of using a database in the first place. And also it means GDAL works fine for the same process. QGIS works even better since it lets me use the native formats directly with no commotion.

Vector Tiles

I made a note to check this guy's article: PostGIS as a MapBox Vector Tile Generator so I did, it's about PostGIS 2.4 so only a bit dated now. See this for news: https://info.crunchydata.com/blog/waiting-for-postgis-3-st_asmvt-performance

PostGIS in Docker

I use Docker Compose to put together PostGIS and Geoserver. I document it in the Geoserver page.

It looks like I will be doing more work with building my own PostGIS docker container to get support for the latest versions. I am leaving some notes on building from source here.

Building PostGIS 3 on the efforts of others

PostGIS repo: https://git.osgeo.org/gitea/postgis/postgis

The official build environment, owned by postgis account at hub, account created by Sandro https://hub.docker.com/r/postgis/postgis-build-env

Looks like the github for this image is here: https://github.com/postgis/postgis-build-env

At OSGEO "Docker images for PostGIS", from Sandro https://git.osgeo.org/gitea/postgis/postgis-docker

Another one https://github.com/mattyb/docker-postgis-dev

GDAL

Building GDAL is the most convoluted and time consuming step in installing PostGIS from sources. But it also gives you the best support for the files you need to use, and it is probably necessary if you want the newest version of PostGIS.

TODO might want to add SFCGAL support but I don't need it yet.

sudo apt install postgresql-server-dev-9.6
sudo apt install python-cxx-dev

I always need features not yet released in packaged form, so install the subversion source code to track the latest.

Get GDAL sources for example

cd ~/src/GIS
wget http://download.osgeo.org/gdal/2.2.4/gdal-2.2.4.tar.gz

"poppler" packages are included for PDF support.

sudo apt install libopenjp2-7 libopenjp2-7-dev
sudo apt install libpoppler-dev
sudo apt install expat libexpat1-dev libkml-dev libxerces-c-dev
sudo apt install libcurl4-openssl-dev
sudo apt install libsqlite3-dev

ESRI FGDB support: OpenFGDB gives access to version 9 fgdb files and read only, ESRI library only gives version 10 access and write access. If you want it anyway, download it from ESRI and unpack it. They move it around, everytime I need it it's in a new place. Try http://appsforms.esri.com/products/download/

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

ESRI MDB support: Who cares? Personal geodatabases are so rare these days I no longer bother to include this. 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.

cd ~/src/GIS/gdal
./configure \
--with-python --with-poppler \
--with-pg=/usr/bin/pg_config \
--with-curl \
--with-openjpeg --with-geos --with-geotiff \
--with-jpeg --with-png \
--with-expat --with-libkml --with-xerces-c

Optionally add

--with-java=/opt/oracle/jdk1.7.0_75
--with-fgdb=/home/bwilson/src/GIS/FileGDB_API-64
--with-mrsid=/home/bwilson/src/GIS/MrSID/Raster_DSDK

Note the MrSID line requires a complete path for some reason. I don't use MrSID so I don't build it in right now. (2017-02) Probably my fault somehow, I think a relative path should be good enough.

Once configure is done make sure the options you selected show 'yes'. Else adjust and repeat...

make -j 14
sudo make install
sudo ldconfig -v

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.7.13 (default, Nov 24 2017, 17:33:09) 
[GCC 6.3.0 20170516] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import osgeo.gdal
>>> print osgeo.gdal.__version__
2.20dev
>>> 

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 libjson-c-dev libproj-dev

The PostGIS trunk wants the latest unreleased GEOS. As long as I am building it, I opt for the python module too.

sudo apt install swig
cd ~/src/GIS
git clone https://git.osgeo.org/gitea/geos/geos.git
cd geos
sh autogen.sh
./configure --enable-python
make -j 7

PostGIS 2.4

Building from source -- I do it to get features and performance.

sudo apt-get install xsltproc

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

git clone https://git.osgeo.org/gitea/postgis/postgis
cd postgis
sudo apt install autoconf libtool
sh autogen.sh

(or) Do a traditional tarball download to get almost the latest

Building on Debian and Ubuntu By the time you've made it to this stage the actual PostGIS build seems trivial.

./configure --with-raster
make -j 10
sudo make install

Building on Mac seemed promising but I failed. I use a Linux virtual machine for testing.

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? 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 systemctl start postgresql

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 gis_owner WITH PASSWORD 'jackalope';

To see the user table

SELECT * FROM pg_authid;

Install PostGIS Template

createdb -U postgres gis_data
psql -U postgres gis_data
# CREATE EXTENSION postgis;
CREATE EXTENSION
# CREATE EXTENSION postgis_topology;
CREATE EXTENSION
# CREATE EXTENSION address_standardizer;
CREATE EXTENSION

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

# SELECT postgis_full_version();

and you will see this result or something similar to this:

POSTGIS="3.1.2 cbe925d" [EXTENSION] PGSQL="130" GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 5.2.0, September 15th, 2018" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.3.1" WAGYU="0.5.0 (Internal)" TOPOLOGY
(1 row)

Some extras

Suggestions from the switch2osm page,

sudo apt-get install munin munin-plugins-extra munin-node iotop ptop

See also http://aouyar.github.io/PyMunin/plugins/postgresql.html which I don't have set up yet.

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 (the horror!), use pgsql2shp

Accessing data

ArcMap Desktop

See ArcGIS + PostGIS.

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, and then build up a web map from your own and other services using OpenLayers or Leaflet.

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

The manual https://postgis.net/docs/manual-dev/

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"