Click here to Skip to main content
15,861,168 members
Articles / Programming Languages / C#

PostgreSQL & PostGis Operations

Rate me:
Please Sign up or sign in to vote.
4.08/5 (8 votes)
10 Jun 2009CPOL3 min read 171.3K   4.2K   27   23
In this article, I would like to show you how to (backup, restore) DB & converting between PostGIS & Esri shape file

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:

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

C#
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:

C#
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: 

C#
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:

C#
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.

C#
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.

C#
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.

    Image 1

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer HP Enterprise Services , Egypt
Egypt Egypt
My name is Mohammed Thabet Zaky
I have graduated from faculty of Computer & Information system at Helwan Universty since 2007
I worked as GIS developer and .Net developer since Feb-2009 until now

Comments and Discussions

 
GeneralNote Convering shape file to PostgreSQL 8.4 Data plz help...... Pin
jhumman10-Dec-09 2:38
jhumman10-Dec-09 2:38 
GeneralRe: Note Convering shape file to PostgreSQL 8.4 Data plz help...... Pin
sinokyo13-Sep-10 16:44
sinokyo13-Sep-10 16:44 
GeneralRe: Note Convering shape file to PostgreSQL 8.4 Data plz help...... Pin
Member 1247388924-Apr-16 1:48
Member 1247388924-Apr-16 1:48 
General[SOLVED] Converting shape file to PostgreSQL Pin
yetiraj11-Oct-18 18:12
yetiraj11-Oct-18 18:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.