Click here to Skip to main content
Click here to Skip to main content

PostgreSQL & PostGis Operations

, 10 Jun 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
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

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.

License

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

Share

About the Author

Thabet Tech
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
Follow on   Twitter

Comments and Discussions

 
QuestionPassword PinmemberEdison J Romo R1-Aug-13 10:48 
Questionmy vote of 4 Pinmembervelt_9919-Jan-13 14:29 
Questionnot working Pinmemberswetakat27-Jun-12 1:18 
Questionbackup database from postgres using c#.net Pinmemberrnfjmygfk,u.hdkhgyjdt25-Jun-12 20:03 
your code is not working .
it doesnt making any .backup file.
Questionshp2pgsql.exe Pinmemberkay3126-Jan-12 10:40 
Questionpassword issue Pinmemberydramkumar9-Aug-11 8:09 
Generalwrong quotation :-) PinmemberAdrian Pasik26-May-11 9:34 
Generaldont work Pinmembercazadoor10-Mar-11 5:45 
Generalthis tool does not work on my computer Pinmembertakudzwamushonga4-Nov-10 4:35 
GeneralHelp someone please Pinmemberzizi_hr5-May-10 3:44 
GeneralNote Convering shape file to PostgreSQL 8.4 Data plz help...... Pinmemberjhumman10-Dec-09 3:38 
GeneralRe: Note Convering shape file to PostgreSQL 8.4 Data plz help...... Pinmembersinokyo13-Sep-10 17:44 
GeneralMy vote of 1 Pinmemberjhumman10-Dec-09 3:36 
GeneralRe: My vote of 1 Pinmemberjgauffin3-Jul-10 23:23 
GeneralI'd like your work PinmemberWisam E. Mohammed12-Jul-09 10:08 
Generalwell done PinmemberMostafa__Salem11-Jun-09 0:53 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141220.1 | Last Updated 11 Jun 2009
Article Copyright 2009 by Thabet Tech
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid