This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision Last revision Both sides next revision | ||
postgis [2014/05/08 17:52] mstraub [Ubuntu 12.04 (Postgres 9.1)] |
postgis [2020/02/12 12:05] mantis [Export table to geojson] |
||
---|---|---|---|
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> | ||
+ | ===== Export table to geojson ===== | ||
+ | |||
+ | Building a FeatureCollection ([[https://postgis.net/docs/ST_AsGeoJSON.html|docs]], [[https://gist.github.com/brambow/889aca48831e189a62eec5a70067bf8e|code snippet]]): | ||
+ | |||
+ | <code psql> | ||
+ | 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> | ||