PostGreSQL / PostGIS How To

Ubuntu PostGresql Install


https://help.ubuntu.com/community/PostgreSQL

NOTE: for the python PostGreSQL module you also need:

sudo apt-get install python-dev
sudo apt-get install libpq-dev


Ubuntu EWN2 Create POSTGIS Database

createdb -U postgres -W -h localhost -E UTF8 -T template0  gis_test
createlang -U postgres -W -h localhost plpgsql gis_test
psql -U postgres -W -h localhost -d gis_test -f /usr/share/postgresql/8.4/contrib/postgis.sql
psql -U postgres -W -h localhost -d gis_test -f /usr/share/postgresql/8.4/contrib/spatial_ref_sys.sql
psql -U postgres -W -h localhost -d gis_test -f /usr/share/postgresql/8.4/contrib/postgis_comments.sql



OSX Create Spatial Database


To setup a database (create one using your favorite tool) for PostGIS use, in a Terminal:

sudo su - postgres -c '/usr/local/pgsql/bin/createlang plpgsql [dbname]'

Where [dbname] is the name of the database.  If you get an error about some role other than postgres not existing, add " -U postgres" after the dbname, before the closing quote.

Then:

sudo su - postgres -c '/usr/local/pgsql/bin/psql -d [dbname] -f /usr/local/pgsql/share/contrib/postgis-1.5/postgis.sql'
sudo su - postgres -c '/usr/local/pgsql/bin/psql -d [dbname] -f /usr/local/pgsql/share/contrib/postgis-1.5/spatial_ref_sys.sql'

NOTE: createlang and much of the SQL in lwpostgis.sql require a role with Postgres superuser permissions, and ownership of a database is not enough.  So it's best to use the 'su - postgres' as above instead of trying to substitute another role.

Create Spatial Table


http://postgis.refractions.net/docs/ch04.html#Create_Spatial_Table

4326 - WGS 84 Long Lat

CREATE SEQUENCE points_id_seq;
CREATE TABLE points ( id INTEGER PRIMARY KEY DEFAULT NEXTVAL('points_id_seq') );
SELECT AddGeometryColumn('points', 'location', 4326, 'POINT', 2);
CREATE INDEX points_location_idx ON points USING GIST ( location );


Import a Shapefile


shp2pgsql -c -s 4269 -D -i -I HU10/WBD_10points.shp public.hu10_pa  | psql -U postgres -W -h localhost -d gis_test

or you can use the Spit plugin in QGIS

Test for Point in Polygon

select * from hu10_pa where
ST_CONTAINS(
the_geom,
ST_GeomFromText('SRID=4269;POINT(-77.44 40.78)')
);


Python PostGreSQL


http://initd.org/psycopg/

OSX: Download build and install - works as advertised

Ubuntu:
sudo apt-get install python-dev
sudo apt-get install libpq-dev
sudo easy_install psycopg2

Translate a Shapefile

Move a shapefile from one location to another - e.g. move an outline of washington DC to overlay on another part of the world

  • First, save the shapefile in UTM coordinates
  • Determine the new location where you want the centroid of the polygon to be in UTM coordinates
  • Determine the SRID for the target UTM zone - e.g. UTM 17N = 26917, UTM 18N 26918
  • Import the shapefile into POSTGIS using the Spit plugin in QGIS, take the defaults
  • Open PGAdmin and perform the following queries on the newly created table

Change the SRID on the table to the new SRID
select UpdateGeometrySRID ('public', '<TABLE_NAME>', 'the_geom', <SRID>)

Translate the geometry to the new centroid

For a single polygon you can just do this
update    "public"."<Table name>" set the_geom =
ST_Translate (the_geom,  <New Center X>  - ST_X(ST_CENTROID(the_geom)),
<New Center Y> - ST_Y(ST_CENTROID (the_geom)))

If you have multiple polygons then you need to find the centroid of one of the features in the set that will have it's new centroid at the desired location and do this:
update    "public"."<Table name>" set the_geom =
ST_Translate (the_geom,  <New Center X>  - <Old Center X>,
<New Center Y> - <Old Center Y>)


  • Open the POSTGIS table as a layer in QGIS
  • Export the layer as KML
  • Open the layer in Google Earth and check to make sure it's in the right place

Migrate from MySQL To PostGreSQL

Use this tool to  migrate an entire database from MySQL to Postgres

https://bitbucket.org/iceone/mysql2pgsql/

Create a new database in MySql and copy in everything you want to move
Create a new empty database in Postgres
Then run this tool to move everything

Comments