User Tools

Site Tools


postgis

Installing

Ubuntu 12.04

Included in this release are Postgres 9.1 and PostGIS 1.5.3. Installation of the following packages is recommended by https://gist.github.com/2589641

sudo apt-get -y install postgis postgresql-9.1 postgresql-server-dev-9.1 postgresql-contrib-9.1 postgis gdal-bin binutils libgeos-3.2.2 libgeos-c1 libgeos-dev libgdal1-dev libxml2 libxml2-dev libxml2-dev checkinstall proj libpq-dev postgresql-9.1-postgis

Enabling Postgis

Ubuntu 16.04 (Postgres 9.5)

Create a PostGIS-enabled database

CREATE DATABASE dbname;
\CONNECT dbname
CREATE extension postgis;

To allow for easy dumping of the database it is a good idea to leave PostGIS-related tables in the public-schema and put all your real data into an extra schema, e.g. data. Such a PostGIS-free-dump can then be used to restore (import) the data into a database with a newer version of PostGIS.

\c dbname
CREATE SCHEMA myschena AUTHORIZATION owner;
ALTER DATABASE dbname SET search_path=myschena,public;

Now all newly created tables will automatically belong to the schema data.

Dump

As noted before, only export your data, not tables belonging to PostGIS. -Fc stands for the custom format, which is already compressed.

pg_dump -t "data.*" -Fc dbname > "$(date +%Y-%m-%d)_dbname.dmp"

Import a dump

To import a dump first create a PostGIS-enabled database as described above.

Then import the dump:

pg_restore -Fc -d dbname dumpname.dmp

In the case that the dump only contained non-PostGIS tables the import should work without errors and you are finished.

Otherwise, there will be many errors, which hopefully have no bad effect. In that case, use this script to set the schema of all non-PostGIS tables to data to make a clean dump next time:

DO
$$
DECLARE
    ROW record;
BEGIN
    FOR ROW IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND  ( quote_ident(tablename) NOT IN ('geography_columns', 'geometry_columns', 'spatial_ref_sys'))
    LOOP
        EXECUTE 'ALTER TABLE public.' || quote_ident(ROW.tablename) || ' SET SCHEMA myschema;';
    END LOOP;
END;
$$;
 
DO
$$
DECLARE
    ROW record;
BEGIN
    FOR ROW IN SELECT viewname FROM pg_views WHERE schemaname = 'public' AND  ( quote_ident(viewname) NOT IN ('geography_columns', 'geometry_columns', 'spatial_ref_sys'))
    LOOP
        EXECUTE 'ALTER VIEW public.' || quote_ident(ROW.viewname) || ' SET SCHEMA myschema;';
    END LOOP;
END;
$$;

Useful Links

Cookbook

Unless noted otherwise, WGS84 is assumed (SRID 4326)

Update gis table registry

In table spatial_ref_sys all tables containing PostGIS types are listed. To refresh this cache:

SELECT populate_Geometry_columns();

Add geometry column

SELECT AddGeometryColumn('myschema', 'mytable', 'the_geom', 4326, 'POINT', 2) ;

Create geometries

UPDATE mytable SET the_geom = ST_SetSRID(ST_MakePoint(longitude,latitude),4326);

Select all geometries in an area

SELECT link_id
FROM my_links
WHERE ST_INTERSECTS(the_geom,ST_GeomFromText('POLYGON( (16.37856 48.21199,16.38611 48.21143, 16.37563 48.19879,16.36826 48.20036 , 16.37856 48.21199)) ',4326) )
postgis.txt · Last modified: 2016/10/06 14:55 by mantis