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

 

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)NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432;User Id=username;Password=password;Database=myGisDB;")
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = conn;