====== 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>
===== Export table to geojson =====

Building a FeatureCollection ([[https://postgis.net/docs/ST_AsGeoJSON.html|docs]], [[https://gist.github.com/brambow/889aca48831e189a62eec5a70067bf8e|code snippet]]):

<code sql>
 SELECT json_build_object(
    'type', 'FeatureCollection',
    'crs',  json_build_object(
        'type',      'name', 
        'properties', json_build_object(
            'name', 'EPSG:4326'  
        )
    ), 
    'features', json_agg(
        json_build_object(
              'type',       'Feature',
    'id',         id,
    'geometry',   ST_AsGeoJSON(the_geom)::json,
    'properties', json_build_object(
        'name', name,
        'frc', frc,
        'fromjunction', fromjunction,
        'tojunction', tojunction,
          )
       )
    )
)
 FROM roadmap_table;
</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>