PostgreSQL & PostGis Operations

• Download project - 320.8 KB
• Download source - 320.66 KB

Introduction 

In this article, I want to show some operations in both PostgreSQL & PostGIS:

• PostgreSQL: Create a back up of your DB and restore a backup file to your DB
• PostGIS: Create a shape file from PostGIS and convert shape file to PostGIS

Background

I assume you have basic knowledge of PostgreSQL. If you need help, please read:

• Using PostgreSQL in your C# (.NET) application (An introduction)  by Adrian basic

PostGIS is an extension to the PostgreSQL object-relational database system that allows GIS (Geographic Information System) objects to be stored in the database. PostGIS does for PostgreSQL what Oracle Spatial does for Oracle, ArcSDE does for Microsoft SQL Server/Oracle.

I assumed that you have installed postgreSQL(version 8.3) in (Driver C), and also installed PostGIS (version 1.3.6).

Please check the following path C:\\Program Files\\PostgreSQL\\8.3\\bin contains these executable files (pg_dump.exe, pg_restore.exe, shp2pgsql.exe, pgsql2shp.exe).

Using the Code

Connection to PostgreSQL

Npgsql.NpgsqlConnection npgConnection = null;
npgConnection = new NpgsqlConnection("Server="+hostName+";UID=" + 
	userName + ";PWD=" + password + ";Database=" + txtDB.Text + ";Port=5432;");

I used Npgsql library to create a connection to postgreSQL. 

Execute Command

I create a method called executeCommand to play the same role of command prompt:

private void executeCommand(string commandType,string commandSentence )
        {
            try
            {
                System.Diagnostics.ProcessStartInfo info = 
			new System.Diagnostics.ProcessStartInfo();
                info.FileName = "C:\\Program Files\\PostgreSQL\\8.3\\bin\\" + 
							commandType + ".exe ";
                info.Arguments = commandSentence;
                info.CreateNoWindow = true;
                info.UseShellExecute = false;
                System.Diagnostics.Process proc = new System.Diagnostics.Process();
                proc.StartInfo = info;
                proc.Start();
                proc.WaitForExit();

                if (commandType == "pg_dump")
                    toolStripStatusLabel1.Text = "Backup successfully created";
                else if (commandType == "pg_restore")
                    toolStripStatusLabel1.Text = "Restore successfully executed";
                else if(commandType=="shp2pgsql")
                    toolStripStatusLabel1.Text = 
		    "Your selected shape file successfully transferred to PostGIS";
                else if (commandType == "pgsql2shp")
                    toolStripStatusLabel1.Text = "Your selected layer from 
			PostGIS successfully converted to shape file";

            }
            catch (Exception ex)
            {
                toolStripStatusLabel1.Text = ex.ToString();
            }
        }

I pass to method executeCommand two parameters commandType which tell me file of processStartInfo and commandSentence which tell me the command to be executed.

Backup of your DB

Here I can create a backup of my DB to be easily loaded to another machine: 

string cmd = "-i -h "+txtHost.Text+" -p "+txtPort.Text+" -U "+txtUserName.Text+" 
	-F c -b -v -f " + tempPath + txtDB.Text + ".backup " + txtDB.Text;
executeCommand("pg_dump", cmd); 

• -p, –port=PORT database server port number
• -i, –ignore-version proceed even when server version mismatches
• -h, –host=HOSTNAME database server host or socket directory
• -U, –username=NAME connect as specified database user
• -W, –password force password prompt (should happen automatically)
• -d, –dbname=NAME connect to database name
• -v, –verbose verbose mode
• -F, –format=c|t|p output file format (custom, tar, plain text)
• -c, –clean clean (drop) schema prior to create
• -b, –blobs include large objects in dump
• -v, –verbose verbose mode
• -f, –file=FILENAME output file name 

Then I call executeCommand()  method with command type pg_dump that enables me to create a backup as the path which I selected using FolderBrowserDialog.

Restore your DB 

Here, I can load a backup file to my DB:

string cmd = "-i -h "+txtHost.Text+" -p "+txtPort.Text+" -U 
	"+txtUserName.Text+" -d " + txtDB.Text + " -v " + path;
executeCommand("pg_restore", cmd);

• -p, –port=PORT database server port number
• -i, –ignore-version proceed even when server version mismatches
• -h, –host=HOSTNAME database server host or socket directory
• -U, –username=NAME connect as specified database user
• -d, –dbname=NAME connect to database name
• -v, –verbose verbose mode

Then I call executeCommand() method with command type pg_restore that enables me to load a backup file to PostgreSQL.

Shape to PostGIS(Loader) 

I can load a shape file to my spatial DB(PostGIS) as table(or layer) to enable me to make all operations of spatial DB. Then after that you can check this layer actually contains all data of original shapefile by using a simple GIS desktop application as Quantum GIS or MapWindow that both of them connect easily to PostGIS.

string cmd = "–I –D "+path+" "+fileName[0]+" | psql "+txtDB.Text+" "+ userName;
executeCommand("shp2pgsql", cmd);

• -I: This means create a spatial index
• –D: Load using dump format (faster)?
• path: The path to the shape file
• fileName[0]: The table name to load the file into.
• The pipe (|): Send the output of shp2pgsql to psql
• Psql: The command line sql client for PostgreSQL
• txtDB.txt: The database you want to load the shape file into

PostGIS to Shape(Dumper) 

I can create a shape file from specified layer you select from available layers list which have gotten from PostGIS.

string Cmd = " -u " + userName + " -P " + password + " " + 
	txtDB.Text + " " + layerName + " -f " + path + "\\" + layerName + ".shp";
executeCommand("pgsql2shp", Cmd);

• -h, –host=HOSTNAME database server host or socket directory
• -u, –username=NAME connect as specified database user
• -f, –file=FILENAME output file name

Then, you can easily use this shape file in your custom application or in another thing.

Final Words

My application is not a unique one, but it provides you with some functions both PostgreSQL & PostGIS can do.

Also my application assumes that all operations are executed through command prompt.

I wish this application will be helpful for all audiences.