Adding SharpMap geometry to a PostGIS database

06 April 2006 Morten SharpMap

...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)

SharpMap v0.9 release moving closer

29 January 2006 Morten SharpMap

A lot have been happening to SharpMap lately. Several new features are already available in the alpha release. Here are some of the features that now readily are available:

  • Gradient Theme now works on not only fill-color, but all numeric style properties, giving even greater flexibility for controlling font-sizes, pen and outline color and widths etc. based on a numeric attribute.
  • Custom Theme: Define your own method that determines the style of a feature based on geometry and feature attribute. Ex. "Make all polygons with an area>100 and whose name starts with "S" green with a blue outline.
  • Symbols can now be scaled.
  • Create a WMS server with only very few lines of code.
  • Labels can now follow lines, or be rotated based on an attribute.
  • Lots of bug-fixes and optimizations.
  • Use any OleDb datasource that holds X and Y columns for creating a point layer.

Diego Guidi - who is the developer of NetTopologySuite has also created a link between SharpMap and NTS. This means that you instantly get all the great functionality of NTS inside SharpMap, including on-the-fly transformation, buffering etc. You can apply any custom method that performs NTS operations on any SharpMap datasource and render the result with SharpMap. Get it at the download section.

Download at http://sharpmap.iter.dk

SharpMap as a WMS server

12 October 2005 Morten SharpMap

Today I was trying to make a WMS implementation using SharpMap, and it turned out to be quite easy. I’ve implemented some helper classes in SharpMap, which I will include in the next release of SharpMap. Actually now you can create a WMS service using only a few lines of code. Here’s an example:

protected void Page_Load(object sender, EventArgs e)
{
    SharpMap.Map myMap = InitializeMap(); //Call method that sets up your map
    SharpMap.Web.Wms.WmsServer.ParseQueryString(Request.QueryString,Response,myMap);
}
And that’s it !!! (well at least besides the InitializeMap method where you set up layers etc…)

It even returns nice little XML exceptions according to the specification. I still need to do some work on the GetCapabilities request, but I think it’s more or less safe to say that SharpMap will work as an OpenGIS compliant WMS Server in the next release.

Shapefile indexing

21 August 2005 Morten SharpMap

I've just finished creating on-the-fly spatial indexing for shapefiles in SharpMap, and what an improvement !!! Rendering a shapefile layer to a map is now 5-6 times faster compared to a spatially indexed PostGIS layer. I'm now focusing on getting ready for the SharpMap v0.8 release, which includes the shapefile indexing, a new improved geometry object model, and a bunch of other changes and enhancements.

SharpMap Sourcecode and WMS support

04 July 2005 Morten SharpMap
The source code for SharpMap is now available too, as well as some feature changes. Biggest change is support for using WMS layers. I you don’t have access to vector data or a PostGreSQL database, you can still aggregate data from several WMS servers and display them in you very own map application. I’m still working on shape file and raster support.

First release of SharpMap

03 April 2005 Morten SharpMap

I have decided to release my .NET Mapping engine. This is an alpha release, but it seems pretty stable. At the moment there are support for most types of vector data 
Take a look at the webpage, try it out, and contribute with ideas, bugs, comments, add-ons etc.: http://sharpmap.iter.dk

For desktop applications, there is a build-in user control that supports dynamic zoom and pan. I think it's pretty cool :-) For ASP.NET there are no user controls yet, but a HttpHandler to make life easier. Check out the FAQ.

You will need Visual Studio 2005 beta 2 to use it, as well as a PostGreSQL database with PostGIS extensions.
You can get a free Beta 2 Express Edition of Visual Studio 2005 here: http://lab.msdn.microsoft.com/express/vcsharp/default.aspx