PostGIS vs ArcSDE: raster load speed test (part 1)

22.06.2009 09:02 · GIS · postgis, wktraster

Before moving on to the actual testing, I will describe the process of configuring PostgreSQL + PostGIS + WKTRaster. Since PostgreSQL has been built from source, all the work normally performed by the installer (creating users, initialising database clusters, etc.) has to be done manually.

The compilation process was described in the previous post. Here I will assume that you are using Windows XP Pro; PostgreSQL, GEOS, Proj, PostGIS and WKTRaster are already compiled, and everything is in the c:\postgres directory.

Let’s go!

To make life easier, we need to add the c:\postgres\bin directory to the PATH environment variable.

Then copy all the dlls from the c:\postgres\lib directory to the c:\postgres\bin directory.

Next, we need to create a user under whose name the PostgreSQL server will be started. Go to “Computer Management → Local Users and Groups → Users”, create the postgres user and set a password for it.

Almost all further actions are performed from the Windows command line.

Create a directory for the database cluster, in my case it is f:\pgdata. There is one thing to keep in mind: while creating the cluster, the initdb program will try to change the permissions on this directory. If the directory was not created by the postgres user, we will get a message about the lack of permissions. There are two ways to fix this:

create the directory as postgres user
```
runas /user:postgres "mkdir f:\pgdata"
```
create a directory using any account, then right-click on the directory and select ‘Properties’ from the context menu. Go to the “Security” tab (“simple” sharing should be turned off) and add the user postgres to the list and give him full access.

Then initialise the database cluster (here the default encoding is set to CP1251).

runas /user:postgres "c:\postgres\bin\initdb.exe -E WIN1251 -D f:\pgdata"

Now we can start the database server with one of the following commands:

runas /user:postgres "c:\postgres\bin\postmaster.exe -D f:\pgdata"

runas /user:postgres "c:\postgres\bin\pg_ctl.exe start -D f:\pgdata"

If the server starts successfully, we can (should) make PostgreSQL start as a service. First, we stop the running server

runas /user:postgres "c:\postgres\bin\pg_ctl.exe stop -D f:\pgdata"

and register our service in the system

pg_ctl.exe register -N "PostgreSQL 8.3.7" -U postgres -D f:\pgdata

Then go to “Computer Management → Services and Applications → Services”, open the properties of the newly created service, and on the “Log On” tab, enter the password for the user postgres. After clicking the ‘Apply’ button, we should get a message that the user postgres is allowed to log in as a service. Start the service and go to the next step.

The cluster is created, let’s create the base (CAUTION! The server must be running!).

createdb -U postgres postgis

Here postgis is the name of the new database. The next step is to add spatial data support to our database, or, in other words, to enable the PostGIS extension for the database. First, we need to add PL/PgSQL language support

createlang plpgsql -U postgres postgis

then load the PostGIS extension itself

psql -а c:\postgres\share\contrib\postgis.sql -U postgres -d postgis

and coordinate system definitions

psql -а c:\postgres\share\contrib\spatial_ref_sys.sql -U postgres -d postgis

The next step is to add WKTRaster extension to the database

psql -а c:\postgres\share\contrib\rtpostgis.sql -U postgres -d postgis

And finally, the last thing - it is necessary to create a table for storing the metadata of so-called overviews (reduced copies of the original image, used for faster zooming and rendering). This table is part of the WKTRaster specification, but it is not created automatically. More details can be found here and here.

The table definition

CREATE TABLE raster_overviews (
  o_table_catalog character varying(256) NOT NULL,
  o_table_schema character varying(256) NOT NULL,
  o_table_name character varying(256) NOT NULL,
  o_column character varying(256) NOT NULL,
  r_table_catalog character varying(256) NOT NULL,
  r_table_schema character varying(256) NOT NULL,
  r_table_name character varying(256) NOT NULL,
  r_column character varying(256) NOT NULL,
  out_db boolean NOT NULL,
  overview_factor integer NOT NULL,
  CONSTRAINT raster_overviews_pk
  PRIMARY KEY (o_table_catalog, o_table_schema, o_table_name, o_column))

I had this code written to a file, so to create the table the following command is used

psql -f d:\install\wktraster\overviews.sql -U postgres -d postgis

This completes the configuration and the database is ready for use.

It remains to say that this post is based on the PostgreSQL, PostGIS, and WKTRaster manuals, as well as some information from the Mateusz’s blog.

⮜ Prev

Next ⮞