PostgreSQL & PostGis Operations

View: 1064    Dowload: 0   Comment: 0   Post by: naruto  
Author: none   Category: PostgreSQL   Fields: Computers - Technology

0 point/3 review File has been tested

Download   postgresql-postgis.rar (632.13 KB)

You need to Sign In to download the file postgresql-postgis.rar
If you do not have an account then Sign up for free here

Download error   Report copyright violation

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

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 http://www.codeproject.com/info/cpol10.aspx

PostgreSQL & PostGis Operations

PostgreSQL & PostGis Operations Posted on 21-03-2014  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 5/10 1064

Comment:

To comment you must be logged in members.

Files with category

  • Accessing: MySQL Database using MySQL C API

    View: 2502    Download: 2   Comment: 0   Author: none  

    Accessing: MySQL Database using MySQL C API

    Category: PostgreSQL
    Fields: Other

    0.75/6 review
    This article was written for beginners, developers that do not know how to access MySql database using MySQL C API and would like to have a small introduction.

  • Full Database Abstraction Layer Generator

    View: 2572    Download: 0   Comment: 0   Author: none  

    Full Database Abstraction Layer Generator

    Category: PostgreSQL
    Fields: Other

    0/23 review
    This article presents yet another Data Abstraction Layer generator (popular topic). The DAL structure is directly inspired from the DotNetNuke, DAL document and the SQL generation is done using SQLpp. The generator takes care of pretty much...

  • Code .NET 2.0; Build Java; Run Linux

    View: 2084    Download: 0   Comment: 0   Author: none  

    Code .NET 2.0; Build Java; Run Linux

    Category: PostgreSQL
    Fields: Other

    1.5/3 review
    This article is in the Product Showcase section for our sponsors at CodeProject. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers. This is a showcase review for...

  • Postgres Database Backup/Restore From C#

    View: 3010    Download: 3   Comment: 0   Author: none  

    Postgres Database Backup/Restore From C#

    Category: PostgreSQL
    Fields: Other

    0/10 review
    This article provides Postgres database backup/restore functionality. By using this application we can check whether a Postgres database is installed or not in a system. If a Postgres database is installed then the Postgres databases list is...

  • Installing Redmine on Windows in production

    View: 5800    Download: 0   Comment: 0   Author: none  

    Installing Redmine on Windows in production

    Category: PostgreSQL
    Fields: Other

    0/10 review
    This is a very simple step by step guide to install Redmine on Windows server in production. This guide is applicable to IIS 7+ based servers, which include Windows Server 2008, 2008 R2, Windows Vista and Windows 7. Latter two should not be used for...

  • A SQL Management Console for MSSQL 2000 & 2005, MySQL 5.0,...

    View: 3335    Download: 2   Comment: 0   Author: none  

    A SQL Management Console for MSSQL 2000 & 2005, MySQL 5.0,...

    Category: PostgreSQL
    Fields: Other

    0.5625/8 review
    Since MSSQL is not holding the lion share of the market yet, integration/interaction with other RDBMS like PL/Oracle, MySQL, FireBird, DB2, is inevitable. With the class System.Data.Common in ADO .NET 2.0, different data providers can write the...

  • Creating an Interactive Map in ASP.NET 2.0 Using SharpMap

    View: 4453    Download: 5   Comment: 0   Author: none  

    Creating an Interactive Map in ASP.NET 2.0 Using SharpMap

    Category: PostgreSQL
    Fields: Other

    0/2 review
    Showing maps on the web is becoming more and more common. The most well-known ones are Google Maps and Microsoft's Virtual Earth. Creating your own websites with interactive maps require you to get hold on the map-data and convert this data to...

  • DevShot - Database Snapshots for Developers

    View: 987    Download: 0   Comment: 0   Author: none  

    DevShot - Database Snapshots for Developers

    Category: PostgreSQL
    Fields: Other

    0/3 review
    A few weeks back, I got an idea of writing a simple tool for switching database states between coding cycles. I thought this would be a good tool for anyone who is working on database related projects and have to switch between database states since...

 

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd