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

或者怎么向PostGIS中上传ESRI Shapefile

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.

我经常被问到怎样把一个图形文件拷贝到PostGIS数据库。PostGIS来自一个命令行工区shp2pgsql.exe，但是它所做的只是产生一个巨大的你后来需要运行的SQL文本文件-我不认为它很高效-特别是对于ASCII代表的几何图形。此外，我在我在处理许多国际信件时也遇到了一些问题。

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

那么为什么不试试让Npgsql and SharpMap 做这个工作？

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.

我坐了一个小工具让使用SharpMap去上传整个图形文件给PostGreSQL/PostGIS 数据库变得简单。

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

以下是一些与PostGIS/SharpMap 有关的代码和解释：

---

First we create a Npgsql connection

首先创建一个Npgsql连接

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

下一步是添加一个几何列（怎样创建完全属性的表参照完整代码）这样我们就设置了一个空的参考ID为“-1”，把这个几何图形命名为“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:dataMyShape.shp", false);

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

我们现在就能通过一个全范围的extents-query （范围查询）去查询所有的特征对象ID，

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

...and then loop through all the features:

然后连接所有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.

他的伟大之处是，用它去上传任何其他SharpMap数据源也容易。使用Christians OGR extension 你可以很容易从一堆数据源上传给PostGIS、

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

在此下载完全代码和汇编源文件：Shape2Pgsql.zip (624,3 KB) （2006年四月26日更新）