Click here to Skip to main content
15,860,861 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

 
Questionwinform backup not working (string cmd = "-i -h localhost -p 5432 -u postgres -F c -b -v -f " + targetpath + _dbName + ".backup " + _dbName;) Pin
odimaxxx23-Aug-23 19:12
odimaxxx23-Aug-23 19:12 
Questionpgsql2shp Pin
Member 1100388223-Dec-14 15:17
Member 1100388223-Dec-14 15:17 
QuestionPassword Pin
Ejrr10851-Aug-13 9:48
Ejrr10851-Aug-13 9:48 
AnswerRe: Password Pin
ssikky7-Sep-16 17:24
professionalssikky7-Sep-16 17:24 
Questionmy vote of 4 Pin
velt_9919-Jan-13 13:29
velt_9919-Jan-13 13:29 
Questionnot working Pin
swetakat27-Jun-12 0:18
swetakat27-Jun-12 0:18 
Questionbackup database from postgres using c#.net Pin
rnfjmygfk,u.hdkhgyjdt25-Jun-12 19:03
rnfjmygfk,u.hdkhgyjdt25-Jun-12 19:03 
Questionshp2pgsql.exe Pin
kay3126-Jan-12 9:40
kay3126-Jan-12 9:40 
AnswerRe: shp2pgsql.exe Pin
Aram Heidari27-Feb-15 9:38
Aram Heidari27-Feb-15 9:38 
GeneralRe: shp2pgsql.exe Pin
Member 1247388923-Apr-16 23:45
Member 1247388923-Apr-16 23:45 
Questionpassword issue Pin
ydramkumar9-Aug-11 7:09
ydramkumar9-Aug-11 7:09 
Generalwrong quotation :-) Pin
Adrian Pasik26-May-11 8:34
Adrian Pasik26-May-11 8:34 
Generaldont work Pin
cazadoor10-Mar-11 4:45
cazadoor10-Mar-11 4:45 
Generalthis tool does not work on my computer Pin
takudzwamushonga4-Nov-10 3:35
takudzwamushonga4-Nov-10 3:35 
GeneralHelp someone please Pin
zizi_hr5-May-10 2:44
zizi_hr5-May-10 2:44 
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 
GeneralMy vote of 1 Pin
jhumman10-Dec-09 2:36
jhumman10-Dec-09 2:36 
GeneralRe: My vote of 1 Pin
jgauffin3-Jul-10 22:23
jgauffin3-Jul-10 22:23 
GeneralI'd like your work Pin
Wisam E. Mohammed12-Jul-09 9:08
Wisam E. Mohammed12-Jul-09 9:08 
Generalwell done Pin
Mostafa__Salem10-Jun-09 23:53
Mostafa__Salem10-Jun-09 23:53 
well done

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.