PostGIS

(See also: BostonGIS's PostGIS User Guide »)

(See also: Practical PostgreSQL with dozens of examples »)

Setting up PostGIS always makes me want to hurt someone. -- Aaron

To initially set up PostGIS we need to create a database called: template_postgis. After creating this template, all new databases that need spatial support can be cloned from this one. This one database will add support for special GIS functions as well as CRS coordinate reference systems for supporting a wide range of map projections. Reusing it as a template for each project will get you up to speed faster.

##To create new PostGIS databases:

###PostGIS 2.x

psql -d [yourdatabase] -c "CREATE EXTENSION postgis;"

###PostGIS 1.x

Schuyler says: Follow Dane's setup below once, then:

createdb -U postgres -O <user_name> -T template_postgis <my_new_postgis_db_name>

##Initial PostGIS template setup

From Dane. Assumes Linux and Mac OS X, for Windows see SOURCE link below.

Instructions: Run all these on the command line, even the ones that don't look like commands. Copy, paste is your friend.

###PostGIS 2.x and 1.x

First, become the postgres user:
```
 sudo su - postgres
```

Then allow yourself to connect to postgres:

# allow your unix user to connect to postgres
createuser osm                                              # or your project specific username
# prompt: "Shall the new role be a superuser? (y/n)" y

###PostGIS 1.x only

Then create the template_postgis:

 # For PostGIS 1.5:
 POSTGIS_SQL_PATH=`pg_config --sharedir`/contrib/postgis-1.5
 
 # For PostGIS 1.4:
 POSTGIS_SQL_PATH=`pg_config --sharedir`/contrib/
 
 # Creating the template spatial database.
 createdb -E UTF8 template_postgis
 createlang -d template_postgis plpgsql # Adding PLPGSQL language support.
 
 # Allows non-superusers the ability to create from this template
 psql -q -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis';"
 
 # Loading the PostGIS SQL routines
 psql -q -d template_postgis -f $POSTGIS_SQL_PATH/postgis.sql
 psql -q -d template_postgis -f $POSTGIS_SQL_PATH/spatial_ref_sys.sql
 
 # Enabling users to alter spatial tables.
 psql -d template_postgis -c "GRANT ALL ON geometry_columns TO PUBLIC;"
 psql -d template_postgis -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"
 
 # If running PostGIS 1.5:
 psql -d template_postgis -c "GRANT ALL ON geography_columns TO PUBLIC;"
 
 # now switch back to your normal user
 exit

TROUBLESHOOTING: If you still are getting permission errors around table ownerships (eg: using Imposm), try this:

 psql -U postgres [databasename]
 # alter table geometry_columns owner to [projectuser];
 ALTER TABLE
 # alter table spatial_ref_sys owner to [projectuser];
 ALTER TABLE
 \q

SOURCE: Dane's tips, plus Windows »

###Further technical reading:

Other sources, less helpful, only for debugging: Aaron's tips here » also Tokumine's tips »

#Example workflow

Create a new user account for this project: (or use your default user)

As the postgres user, create a new user account. This will require super-user (sudo) access on your machine.

 sudo -u postgres createuser user_name
 Shall the new role be a superuser? (y/n) n
 Shall the new role be allowed to create databases? (y/n) n
 Shall the new role be allowed to create more new roles? (y/n) n

Create a new project database using the PostGIS template database.

Assumes you've followed the instructions above for setting up the template_postgis database owned by your user_name created in step 1, created as the postgres -U user.
```
 createdb -U postgres -O <user_name> -T template_postgis <my_new_db_name>
```
Import spatial data into that project database as a new table.

Pro tip: Make sure your data is already in web Mercator projection (900913) before importing.

Need help reprojecting your data? See OGR guide »

OUTDATED, use ogr2ogr instead! shp2pgsql -dID -s 900913 import.shp <destination_table> | psql -U -d <my_new_db_name>

#Importing data into PostGIS

###Loading OSM data

Read more at importing Natural Earth »

###Load shapefiles (SHP) into PosGIS:

Gist demoing SHP > PostGIS using OGR bindings » Importing SHPs of Windows 1252 into UTF PostGIS using ORG's PostGIS driver (not shp2pgsql). Assumes web Mercator. If not web Mercator, then use -t_srs "EPSG:4326" and remove -clipsrc -180 -85.05112878 180 85.05112878.

ogr2ogr -t_srs EPSG:900913 -f PostgreSQL \
  -overwrite \
  -lco GEOMETRY_NAME=geometry -lco ENCODING="Windows 1252" \
  -clipsrc -180 -85.05112878 180 85.05112878 \
  -nlt MULTILINESTRING \
  -nln <destination_table> \
  PG:"dbname='<db_name>' user='<user_name>'" \
  infile_path_name

outdated! shp2pgsql -dID -s 900913 countyp020.shp destination_table_name | psql -U psql_username -d psql_databasename

Note: 4326 is geographic. 900913 is web mercator. This the the input (existing) projection of the data.

shp2pgsql HINTS | Further reading »

-d drops the database table before creating a new one.
-I creates a GiST index on the geometry column.
-D uses the PostgreSQL "dump" format for the output data.
-s creates and populates the geometry tables with the specified SRID.
-W Specify encoding of the input data (dbf file). When used, all attributes of the dbf are converted from the specified encoding to UTF8.

###Loading CSV point data into PostGIS

Mapbox has a tutorial »

Another good tutorial from KevFoo »

  # launch psql
  psql
  
  #let's create a new table to store our data; assumes you've already got a database
  #CREATE TABLE photos (lat float, long float, date varchar(10), unixdate integer);

  #import the text file (this file doesn't have a header) into that table
  \copy photos from 'imagecoordinates.csv' with delimiter as ','
  
  #create a bonifide geometry column instead of loose geom in two columns
  #http://postgis.org/docs/AddGeometryColumn.html
  SELECT AddGeometryColumn('smugmug', 'photo_geom', 4326, 'POINT', 2, true);
  
  #Populate that new field with POINT, in geographic projection
  UPDATE photos SET photo_geom = ST_SetSRID(ST_MakePoint(long, lat),4326);
  
  #Make it fast
  #http://postgis.refractions.net/documentation/manual-1.5/ch04.html#id2670643
  CREATE INDEX photo_index ON photos USING GIST ( photo_geom );

##Exporting data from PostGIS

###OGR/GDAL

If you want your resulting SHP to be UTF-8, you must use ogr2ogr as pgsql2shp will convert to Windows-1252. The -s_srs and -t_srs must be specified if you want a PRJ file, too.

ogr2ogr -f "ESRI Shapefile" -lco ENCODING="UTF-8" -s_srs EPSG:4326 -t_srs EPSG:4326 outfile.shp PG:"host=local user=username dbname=databasename" -sql "SELECT * FROM tablename"

###pgsql2shp

**WARNING: not UTF-8 compliant. Use ogr2ogr, above. ***

Useful for sharing the results of your analysis with non-PostGIS users, or visualizing results in another application.

pgsql2shp -u user_name -f path_to_shapefile_to_export.shp source_database_name source_table_name

###Dumping entire databases

Useful for backing up PostGIS databases or moving PostGIS databases between machines.

pg_dump -C -v -U <user_name> <db_name> > db_dump.sql

pg_dump HINTS | Further reading »

-C = create new copy of DB on the other end (might not be a good idea since this is PostGIS
-v = verbose to track progress since these are big databases
-U = username
toner = database name
> = pipe result
toner_dump.sql = name of file to save the dump in

###Dumping just a table from the database

Useful for backing up PostGIS databases or moving PostGIS databases between machines.

pg_dump -C -v -U <user_name> <db_name> -t <table_name> > db_table_dump.sql

###Restoring databases

Paired with dumping, see above.

psql -U <user_name> -d <db_name> < db_table_dump.sql

This is confusing. I think you can just pipe the dump back in, as shown in the code snippet above. pgRestore seems to be something else beyond this.

pgrestore HINTS | Further reading »

#PSQL hints

Connecting as a user

Helpful when your project's psql user is different than your unix account user name.

-U Connect to the database as the user username instead of the default.

Connecting to a specific database

-d Specifies the name of the database to connect to. This is equivalent to specifying dbname as the first non-option argument on the command line.

###Rename columns in a database table

ALTER TABLE tablename RENAME TO newtablename;

###Delete a table:

DROP TABLE tablename;

###Delete all rows from a table:

DELETE FROM tablename;

###RegEx for PostSQL quick prep insert

Find (with GREP turned on in BBEdit):

^(\d+)\t.+\t.+\t(.+)$

Replace:

UPDATE planet_osm_point SET country='\2' WHERE osm_id=\1;

Then upload that file to Geo

Get into that database

psql -U user_name

###Describe the schema of a database (list the tables)

    \d+    # Note: the plus gives the size of the table in KB.

    \d
    
                         List of relations
     Schema |             Name              |   Type   | Owner 
    --------+-------------------------------+----------+-------
     public | coastline                     | table    | vpro
     public | coastline_gid_seq             | sequence | vpro
     public | geometry_columns              | table    | vpro
     public | planet_osm_line               | table    | vpro
     public | planet_osm_line_z10           | view     | vpro
     public | planet_osm_line_z11           | view     | vpro
     public | planet_osm_line_z12           | view     | vpro
     public | planet_osm_line_z13           | view     | vpro
     public | planet_osm_line_z14           | view     | vpro
     public | planet_osm_line_z15plus       | view     | vpro
     public | planet_osm_line_z15plus_big   | view     | vpro
     public | planet_osm_line_z15plus_small | view     | vpro
     public | planet_osm_nodes              | table    | vpro
     public | planet_osm_point              | table    | vpro
     public | planet_osm_polygon            | table    | vpro
     public | planet_osm_rels               | table    | vpro
     public | planet_osm_roads              | table    | vpro
     public | planet_osm_ways               | table    | vpro
     public | spatial_ref_sys               | table    | vpro
    (19 rows)

###Describe the schema of a specific table in a database:

\d planet_osm_point;

###Add a column (field) in table:

alter table planet_osm_point  add column country varchar(16);

###More complicated example:

db_name=> UPDATE planet_osm_point SET country='Elsewhere' WHERE osm_id=1133916606;

UPDATE 1

Now run the big file:

\i ~/osm_places_netherlands.pgsql

A lot of update statements whirl by.

Test result:

select country, count(osm_id) from planet_osm_point group by country;

country   |  count  
-------------+---------
              | 1110038
 Elsewhere   |   41961
 Netherlands |    6152
  (3 rows)

#Advanced

Creating Indexes

Sometimes an additional index will make PostGIS much faster, besides the default geometry spatial index. This is useful for doing vector Rollcall index tiles and feature bundling in Tilestache. For each table used in the Rollcall:

create index table_uniquekeys on table(uniquekey);

You'd reference them in the Rollcall as follows:

"(SELECT ('county:'||geoid) AS id, the_geom AS geometry, COALESCE(ltv, -9999) AS ltv FROM counties LEFT OUTER JOIN client_data ON client_data.uniquekey = counties.geoid) AS stuff": "4-8",

#Troubleshooting

###Adding psql to your path:

If you can't launch psql from the command line, make sure it's in your path:

    export PATH=/usr/local/pgsql/bin/:$PATH

###Changing the port Postgres is listening to and the access permissions:

The default port for Postgres should be 5432. You'll also need to make sure it has the right access permissions.

Sometimes when you access Postgres, you'll get an error like:

psql: could not connect to server: Permission denied

or:

psql: FATAL: Ident authentication failed for user "username"

In that case, we'll want to edit our configuration file:

sudo pico /var/lib/pgsql/data/pg_hba.conf

The configuration file for 9.1 is at : /etc/postgresql/9.1/main/pg_hba.conf

This file controls:

1. Which hosts are allowed to connect 2. How clients are authenticated 3. Which PostgreSQL user names they can use 4. Which databases they can access

By default Postgresql uses IDENT-based authentication. All you have to do is allow username and password based authentication for your network or webserver. IDENT will never allow you to login via -U and -W options. Append following to allow login via localhost only:

local	all	all	    trust
host	all	127.0.0.1/32	trust

Save and close the file. Restart Postgresql server:

service postgresql restart

Now, you should able to login using following command:

psql -d myDb -U username -W

###If your PostGres user is borked, edit:

sudo pico /etc/postgresql/8.4/main/postgresql.conf

Note: The location of this file will change depending on your version of Postgres, mine is 8.4 above.

Use the settings referenced here:

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

PostGIS

Connecting as a user

Connecting to a specific database

Creating Indexes

Clone this wiki locally