...or how to upload an ESRI Shapefile to PostGIS.
I've often been asked how to copy data from a shapefile to a PostGISdatabase. 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 letNpgsql and SharpMapdo 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.
Beloware some of the PostGIS/SharpMaprelated code explained:
First we create a Npgsql connection
The next step is to add a geometry column (see in thefullsource 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:Addparameters for remaining columns if nessesary (in thatcase alter theINSERTcommandtext 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 OGRextension you can suddenly upload a bunch ofdatasource 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;
