Differences

This shows you the differences between two versions of the page.

--- postgis [2014/05/08 17:52]
mstraub [Ubuntu 12.04 (Postgres 9.1)]
+++ postgis [2016/10/06 14:55]
mantis
@@ Line 1: / Line 1: @@
+====== 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
+<code bash>
+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
+</code>
+====== Enabling Postgis ======
+===== Ubuntu 16.04 (Postgres 9.5) =====
+==== Create a PostGIS-enabled database ====
+<code sql>
+CREATE DATABASE dbname;
+\connect dbname
+create extension postgis;
+</code>
+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.
+<code sql>
+\c dbname
+CREATE SCHEMA myschena AUTHORIZATION owner;
+ALTER DATABASE dbname SET search_path=myschena,public;
+</code>
+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.
+<code bash>
+pg_dump -t "data.*" -Fc dbname > "$(date +%Y-%m-%d)_dbname.dmp"
+</code>
+==== Import a dump ====
+To import a dump first create a PostGIS-enabled database as described above.
+Then import the dump:
+<code bash>
+pg_restore -Fc -d dbname dumpname.dmp
+</code>
+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:
+<code sql>
+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;
+$$;
+</code>
+====== Useful Links ======
+  * http://postgis.refractions.net/documentation/manual-1.5/reference.html
+  * http://dhancock.tumblr.com/post/60326756/postgresql-dump-and-restore-with-postgis-data
+  * http://blog.cleverelephant.ca/2010/09/postgis-back-up-restore.html
+====== 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:
+<code sql>
+select populate_Geometry_columns();
+</code>
+===== Add geometry column =====
+<code sql>
+SELECT AddGeometryColumn('myschema', 'mytable', 'the_geom', 4326, 'POINT', 2) ;
+</code>
+===== Create geometries =====
+<code sql>
+UPDATE mytable SET the_geom = ST_SetSRID(ST_MakePoint(longitude,latitude),4326);
+</code>
+===== Select all geometries in an area =====
+<code sql>
+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) )
+</code>

Small heaps of code

User Tools

Site Tools

Differences

Page Tools