Adding SharpMap geometry to a PostGIS database

...or how to upload an ESRI Shapefile to PostGIS.

I've often been asked how to copy data from a shapefile to a PostGIS database. PostGIS comes with a commandline-tool for this (shp2pgsql.exe), but all it does is generate a huge SQL-textfile that you will need to run afterwards - Not very efficient I think - especially with the ASCII-representation of the geometry. Furthermore I've had several problems with it regarding many international letters in the attributes.

So why not try to let Npgsql and SharpMap do the job?

I've been working a bit with a small tool that makes it easy to upload an entire shapefile to a PostGreSQL/PostGIS database using SharpMap.

Below are some of the PostGIS/SharpMap related code explained:


 

 

First we create a Npgsql connection

NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432;User Id=username;Password=password;Database=myGisDB;")
NpgsqlCommand command = new NpgsqlCommand
();
command.Connection = conn;

The next step is to add a geometry column (see in the full source on how you create the table with all the attributes). In this case we set the spatial reference ID to '-1' and name the geometry column 'geom'.

command.CommandText = "SELECT AddGeometryColumn('','myTable','geom','-1','GEOMETRY',2);";
command.ExecuteNonQuery();

Now we are ready to upload to the database, so lets get hold of that shapefile! First we set up a datasource:

SharpMap.Data.Providers.ShapeFile shp = new SharpMap.Data.Providers.ShapeFile(@"C:\data\MyShape.shp", false);

We can now query all the feature object IDs, by using an extents-query on the full extents:

conn.Open();
List<uint> indexes = shp.GetObjectIDsInView(shp.GetExtents());

...and then loop through all the features:

foreach (uint idx in indexes)
{
   SharpMap.Data.FeatureDataRow
feature = shp.GetFeature(idx);
   
command.CommandText = "INSERT INTO \"myTable\" (\"geom\") VALUES (GeomFromWKB(:geom,:srid));"
;
   command.Parameters.Add(":geom", NpgsqlTypes.NpgsqlDbType
.Bytea);
   command.Parameters[":geom"
].Value = feature.Geometry.AsBinary(); //Add the geometry as Well-Known Binary
   command.Parameters.Add(":srid", NpgsqlTypes.NpgsqlDbType
.Integer);
   //Set the SRID of the geometry - this must match the SRID we used when the column was created
   command.Parameters[":srid"].Value = -1;

   
//TODO: Add parameters for remaining columns if nessesary (in that case alter the INSERT commandtext accordingly) 
 
   command.ExecuteNonQuery();
}
//Clean up
conn.Close();
shp.Close();

...and that is all there is to it !

The great thing about this, is that it is easy to change this to take any other SharpMap datasource and upload as well. And with Christians OGR extension you can suddenly upload a bunch of datasource directly to PostGIS.

Download the full source and compiled binaries here: Shape2Pgsql.zip (624,3 KB) (updated April 26, 2006)

Comments (1) -

  • Hi morten, great tool.

    Every time i use the Shape2Pgsql.exe with a shape file i get the following error
    at this line of code

    //Retrieve the first feature to determine geometrytype and columns
    SharpMap.Data.FeatureDataRow geom = shp.GetFeature(indexes[0]);

    String was not recognized as a valid DateTime.

       at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
       at System.DateTime.Parse(String s, IFormatProvider provider)
       at SharpMap.Data.Providers.DbaseReader.GetFeature(UInt32 oid, FeatureDataTable table)
       at SharpMap.Data.Providers.ShapeFile.GetFeature(UInt32 RowID)
       at Shape2Pgsql.Form1.btnUpload_Click(Object sender, EventArgs e) in C:\Documents and Settings\Administrator\Desktop\Shape Installer\Form1.cs:line 191
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at Shape2Pgsql.Program.Main() in C:\Documents and Settings\Administrator\Desktop\Shape Installer\Program.cs:line 17
       at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()

    Thanks

Pingbacks and trackbacks (3)+

Add comment