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)
- Install PostgreSQL
- 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".
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.
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
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
--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!!)
Confirm the formats you need are listed with these commands
gdalinfo --formats ogrinfo --formats
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 >>>
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
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.
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)
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
See ArcGIS + PostGIS.
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.
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.
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);
Postgis wiki http://trac.osgeo.org/postgis/wiki/UsersWikiMain
The manual https://postgis.net/docs/manual-dev/
An article of interest
From some forum or other
- 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) (
That is about all I can think of for the moment, but I am sure others have more tips.
Hope this helps