How-to Insert Into & Query A PostGIS Spatial Database From C#

C# is a great language for quickly implementing data gathering tools as there are many off-the-shelf components that you can combine with your our code such as TweetInvi for Twitter, Flickrnet for flickr  and FacebookSDK for facebook.

However once you’ve collected the data you need to export it in a format compatible with your analysis and visualisation packages (such as R or QGIS). A simple approach is to export CSV files; a more complicated approach is to use ShapeFiles; and perhaps the most powerful approach is to export to PostGIS. PostGIS enables you to store your data in one central repository so that all your tools can access the data concurrently and even multiple users at a time 🙂 If this is new to you my previous post about the benefits of and how to install and use PostGIS with R and QGIS may be of interest.

Sending Data To PostGIS Using C#

It turns out that sending your data to PostGIS from C# is a straightforward task once you know how 🙂

Step 0

Download Npgsql using nuget – this can easily be done inside Visual Studio by right clicking on References and clicking on manage NUGet Packages.

Step 1

Establish a connection to your database. This is simple assuming you know your userid, password and database name.

  • Create a connection string detailing your userid, password and database
  • Create an NpgsqlConnection object
  • Open the connection

Step 2

  • Create an SQL command string e.g. INSERT INTO – if you are not familiar with SQL check out W3Schools
  • Create an NpgsqlCommand object based on the SQL command and the connection string
  • Execute the NpgsqlCommand
  • Close the connection

The code to perform this is written below in a method called InsertInTable:

static void InsertInTable()
        {
            try
            {

                // Making connection with Npgsql provider
                NpgsqlConnection conn = new NpgsqlConnection(connstring);
                conn.Open();
                // sql statement
                string unixtime, date, stime, lat, lon, creatorId, twitterId, text, coord;
                unixtime = "5645455";
                date = "11/09/2015";
                stime = "12:00:00";
                lat = "54.0";
                lon = "55.0";
                creatorId = "Terry";
                twitterId = "2131234523";
                text = "Happy times :-)";
                coord = String.Format("ST_MakePoint({0} , {1}, 4326)" , lat , lon);

                string sql = "INSERT INTO twittertest (unixtime, date, time , lat , lon , \"creatorId\" , \"twitterId\" , text , coord) " +
                                         String.Format( "Values ({0}, '{1}' , '{2}' , {3} , {4} , '{5}' , '{6}' , '{7}' , {8} )",
                                         unixtime,
                                         date,
                                         stime,
                                         lat,
                                         lon,
                                         creatorId,
                                         twitterId,
                                         text,
                                         coord);

                // Execute command
                NpgsqlCommand command = new NpgsqlCommand(sql, conn);
                Int32 rowsaffected = command.ExecuteNonQuery();

                // since we only showing the result we don't need connection anymore
                conn.Close();
            }
            
            catch (Exception msg)
            {
                // something went wrong, and you wanna know why
                Console.WriteLine(msg.ToString());

            }
        }

The eagle eyed may have noticed that  to insert a point within the database a non-standard SQL command is used. A geometry constructor called “ST_MakePoint” is used to create the point – you can find the documentation for it here.

Retrieving Data From PostGIS Using C#

Step 1

Establish a connection to your database. This is simple assuming you know your userid, password and database name.

  • Create a connection string
  • Create an NpgsqlConnection object
  • Open the connection

Step 2

  • Create an SQL command string e.g. SELECT * FROM Tablename
  • Create an NpgsqlCommand object based on the SQL command and the connection string
  • Execute the NpgsqlCommand
  • Close the connection

The code to perform this is in the method below called GetTable:

static DataTable GetTable()
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();

            try
            {
            
                // Making connection with Npgsql provider
                NpgsqlConnection conn = new NpgsqlConnection(connstring);
                conn.Open();
                // sql statement
                string sql = "SELECT unixtime , \"twitterId\" , CAST ( coord AS text) FROM twittertest";

                NpgsqlCommand cmd = new NpgsqlCommand(sql);
                cmd.AllResultTypesAreUnknown = true;

                // data adapter making request from our connection
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);

                // since we only showing the result we don't need connection anymore
                conn.Close();

                ds.Reset();
                da.Fill(ds); // filling DataSet with result from NpgsqlDataAdapter
                dt = ds.Tables[0]; // since it C# DataSet can handle multiple tables, we will select first
                return dt;
            }
            catch (Exception msg)
            {
                // something went wrong, and you wanna know why
                Console.WriteLine(msg.ToString());
                return (dt);
            }
        }

You may have noted that I cast the the coord (spatial object) as text so that it can be read into C# as a string. A useful alternative is to use the ST_AsText command to convert the coord into a WKT formatted string – see the documentation here.

The full code to create a sample application is supplied below as an example for completeness.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

using Npgsql;

namespace SQLPostGIS
{
    class Program
    {
        // PostgeSQL-style connection string
        static string connstring = String.Format("Server={0};Port={1};" +
            "User Id={2};Password={3};Database={4};",
            "localhost", "5432", "postgres",
            "password", "gisdb");

        static void InsertInTable()
        {
            try
            {

                // Making connection with Npgsql provider
                NpgsqlConnection conn = new NpgsqlConnection(connstring);
                conn.Open();
                // sql statement
                string unixtime, date, stime, lat, lon, creatorId, twitterId, text, coord;
                unixtime = "5645455";
                date = "11/09/2015";
                stime = "12:00:00";
                lat = "54.0";
                lon = "55.0";
                creatorId = "Terry";
                twitterId = "2131234523";
                text = "Happy times :-)";
                coord = String.Format("ST_MakePoint({0} , {1}, 4326)" , lat , lon);

                string sql = "INSERT INTO twittertest (unixtime, date, time , lat , lon , \"creatorId\" , \"twitterId\" , text , coord) " +
                                         String.Format( "Values ({0}, '{1}' , '{2}' , {3} , {4} , '{5}' , '{6}' , '{7}' , {8} )",
                                         unixtime,
                                         date,
                                         stime,
                                         lat,
                                         lon,
                                         creatorId,
                                         twitterId,
                                         text,
                                         coord);

                // Execute command
                NpgsqlCommand command = new NpgsqlCommand(sql, conn);
                Int32 rowsaffected = command.ExecuteNonQuery();

                // since we only showing the result we don't need connection anymore
                conn.Close();
            }
            
            catch (Exception msg)
            {
                // something went wrong, and you wanna know why
                Console.WriteLine(msg.ToString());

            }
        }

        
        static DataTable GetTable()
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();

            try
            {
            
                // Making connection with Npgsql provider
                NpgsqlConnection conn = new NpgsqlConnection(connstring);
                conn.Open();
                // sql statement
                string sql = "SELECT unixtime , \"twitterId\" , CAST ( coord AS text) FROM twittertest";

                NpgsqlCommand cmd = new NpgsqlCommand(sql);
                cmd.AllResultTypesAreUnknown = true;

                // data adapter making request from our connection
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);

                // since we only showing the result we don't need connection anymore
                conn.Close();

                ds.Reset();
                da.Fill(ds); // filling DataSet with result from NpgsqlDataAdapter
                dt = ds.Tables[0]; // since it C# DataSet can handle multiple tables, we will select first
                return dt;
            }
            catch (Exception msg)
            {
                // something went wrong, and you wanna know why
                Console.WriteLine(msg.ToString());
                return (dt);
            }
        }

        static void Main(string[] args)
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();

            

            try
            {
                InsertInTable();
                dt = GetTable();
                foreach (DataRow row in dt.Rows)
                {
                    Console.WriteLine(row["unixtime"].ToString() + "," + row["twitterID"].ToString());
                }
                
                
            }
            catch (Exception msg)
            {
                // something went wrong, and you wanna know why
                Console.WriteLine(msg.ToString()); 
            }

            Console.ReadLine();
        }
    }
}
Advertisements

2 thoughts on “How-to Insert Into & Query A PostGIS Spatial Database From C#

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s