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(); } } }