PostGIS

From Seamonster

Jump to: navigation, search


Contents

Introduction

PostGIS is the spatial extension to the postgres relation database management system.


Installing

Packages

Install postgresql 8.2 and the postgis extension from packages. 

 sudo aptitude install postgresql-8.2-postgis

That command will download and install postgres and all it's dependencies.

Initial postgres Config

The pacakge will create a postgres user on the linux system, and a database superuser also named 'postgres'. We need to change the database superuser password, so we can log in.

On the old nsrl1 we had two different postgres installations, the old setup running under postgres 7.4 is listening on port 5432, and the newly installed postgres 8.1 installation on port 5433. You can specify the port number on the comman line using the -p 5433 option to the postgres commands, or set the PGPORT environment variable. Now we are just using 8.2 on all of our servers. 

  sudo su - postgres
  plsql template1
  template1=# ALTER USER postgres WITH PASSWORD '*password*';
  template1=# \q

Now create a regular user to do work with. 

  createuser -DAS seamonster
  plsql template1
  template1=# ALTER USER seamonster WITH PASSWORD '*password*';
  template1=# \q


Create a Spatially Enabled Postgres Table

  sudo su postgres
  createdb dbname
  createlang plpgsql dbname

Now that the table is created, we need to add the spatial component. Use the following command, but make sure to add the path /usr/lib/postgresql/8.2/bin/psql rather than just psql to make sure you are using the 8.2 program. I am ommitting the path here for readability. And don't forget to specify the port, or PGPORT environment var. 

  psql -d dbname -f /usr/share/postgresql-8.2-postgis/lwpostgis.sql
  psql -d dbname -f /usr/share/postgresql-8.2-postgis/spatial_ref_sys.sql

Now add data. In this case we are importing from an ESRI shapefile. 

  shp2pgsql -D myshape.shp mytable dbname | psql dbname

This will add a new table to your existing database called mytable. It will have columns that correspond to the attribute table in the shapefile.

You can see if the process worked by logging into the database and performing a select. 

 psql dbname

And enter the SQL. 


 SELECT * from mytable;

Creating a Geometry Column

First we need to figure out what datum and/or projection we want our geometry to be in. You can choose from around 3000 different ones that are all stored in the spatial_ref_sys table. To find all the entries that contain the text 'WGS 84' we can use the following select statement: 

  SELECT * FROM spatial_ref_sys WHERE srtext LIKE '%WGS 84%'

For this example we pick a WGS 84 datum with this 'srtext': 

  GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS   84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]

The corresponding 'srid' is 4326, which we need to make the geometry column.

Lets say we want to add a geometry column called 'platform_geom' that contains two dimensional points to a table called platform_loc. To do this we just enter the following select statement (We can only add geometry columns to tables that already exist, so first create the table): 

  SELECT AddGeometryColumn('platform_loc', 'platform_geom', 4326, 'POINT', 2 );

The syntax of this select statement is the followingf: 

  SELECT AddGeometryColumn(<table_name>, <column_name>, <srid>, <type>, <dimension>);

Working with Geometry Columns

PostGIS stores features with a binary encoded representation. For example, here is a point record: 


eknuth=# select url,the_geom from lemon_photographs limit 1;
       url       |                      the_geom                      
-----------------+----------------------------------------------------
 Alder+Patch.JPG | 0101000020E6100000A94A29DF5ECC60C05FCC11320A344D40

If you want to display the point as a latlong coordinate you must use the astext() PostGIS function: 

eknuth=# select url,astext(the_geom) from lemon_photographs limit 1;
       url       |                  astext                   
-----------------+-------------------------------------------
 Alder+Patch.JPG | POINT(-134.386581020973 58.4065611445337)

If you want to export the values from a table as a shapefile you can use the pgsql2shp command: 

pgsql2shp -f filename -h localhost -p 5432 -u postgres -P password dbname tablename

In order to insert a point into a database using the binary geometry format, you have to use the ST_PointFromText() function. The arguments for this function are the point and the projection/spatial reference (SRID). For WGS84 the SRID is 4326. 

ST_PointFromText('POINT(-134.452987793328 58.3733332382918)', 4326);

More useful sql commands can be found on the SQL Secrets page.

Resources

http://postgis.refractions.net/pipermail/postgis-users/2002-January/000629.html

Retrieved from "http://robfatland.net/seamonster/index.php?title=PostGIS"
Personal tools