应用场景,在使用sde连接数据库的时候,sde对数据库版本局限性太大,比如arcmap10.2仅仅支持postgresql11之下的数据库。
实现步骤:在postgis中提供很多函数将wkb转成它的geometry类型,同样也有将geometry转成binary的函数。当然更兴奋的是ArcGis中有针对标准wkb的转换,这样就可以通过数据库查询出来wkb数据,然后根据wkb数据创建内存图层,就可以对内存图层进行操作,例如增删查改。
第一步:从postgreSQL中通过postgis读取GIS数据显示在AxMapControl中。
- 首先通过C#连接数据库,操作pgsql的共用代码,首先引入Npgsql.dll
public class PgsqlHelper { private static string connStr = @"PORT=5432;DATABASE=textGIS;HOST=localhost;PASSWORD="密码";USER ID=postgres"; #region 查询操作 public static DataTable ExecuteQuery(string sqrstr) { NpgsqlConnection sqlConn = new NpgsqlConnection(connStr); DataTable ds = new DataTable(); try { using (NpgsqlDataAdapter sqldap = new NpgsqlDataAdapter(sqrstr, sqlConn)) { sqldap.Fill(ds); } return ds; } catch (System.Exception ex) { throw ex; //return ds; } } #endregion #region 增删改操作 public static int ExecuteNonQuery(string sqrstr, params NpgsqlParameter[] npgsqlParameters) { NpgsqlConnection sqlConn = new NpgsqlConnection(connStr); try { sqlConn.Open(); using (NpgsqlCommand pgsqlCommand = new NpgsqlCommand(sqrstr, sqlConn)) { foreach (NpgsqlParameter parm in npgsqlParameters) pgsqlCommand.Parameters.Add(parm); int r = pgsqlCommand.ExecuteNonQuery(); //执行查询并返回受影响的行数 sqlConn.Close(); return r; //r如果是>0操作成功! } } catch (System.Exception ex) { return 0; } } #endregion public static List<Byte[]> executeRouteQuery(string sqlstr) { List<Byte[]> bytes = new List<Byte[]>(); NpgsqlConnection sqlConn = new NpgsqlConnection(connStr); try { sqlConn.Open(); NpgsqlCommand objCommand = new NpgsqlCommand(sqlstr, sqlConn); NpgsqlDataReader myReader; myReader =objCommand.ExecuteReader(); while (myReader.Read()) { Byte[] routeWKB1= (byte[])myReader["route"]; bytes.Add(routeWKB1); } // Byte[] routeWKB = (byte[])objCommand.ExecuteScalar(); return bytes; } catch (Exception ee) { MessageBox.Show(ee.Message); return null; } finally { sqlConn.Close(); } } }
注意点: 要注意的是因为ArcEngine中对wkb的支持是byte[]之间的转换,所以在进行数据库取数据时用postgis的这个函数将GIS数据转成byte[],这样写 asbinary(the_geom)
- 调用方式 将得到的byte[](也就是wkb数据)通过ArcEngine的接口得到IGeometry,如下所示
private void pgsql加载非SDEToolStripMenuItem_Click(object sender, EventArgs e) { Byte[] bytes = PgsqlHelper.executeRouteQueryOne("select st_asbinary(st_union(geom)) as route from public.gis_osm_railways_free_1 where name='京沪线'"); IGeometry geom = null; int countin; IGeometryFactory3 factory = new GeometryEnvironment() as IGeometryFactory3; factory.CreateGeometryFromWkbVariant(bytes, out geom, out countin); IMap pMap = axMapControl1.Map; IActiveView pActiveView = pMap as IActiveView; IGraphicsContainer pGraphicsContainer = pMap as IGraphicsContainer; IPolyline pLine = (IPolyline)geom; ILineElement pLineElement = new LineElementClass(); //pLineElement.Symbol = pLineSym; //添加geom pElement = pLineElement as IElement; pElement.Geometry = pLine; pGraphicsContainer.AddElement(pElement, 0); axMapControl1.FullExtent = pElement.Geometry.Envelope; axMapControl1.Refresh(); }
第二步:将上一步从pg中显示出来的GIS数据进行相应的修改后保存回pg中
1. 修改指定的要素后通过 geometry得到wkb
要对修改操作进行保存,就需要将指定的要素中的geometry再次转回wkb然后通过sql语句修改pg中的指定记录即可,思路很简单,问题在于拼接SQL字符串上面,因为arcengine通过geometry得到的仍然是一个byte[],这个东西是没办法拼到sql语句中的。
2. 用到的ArcEngine接口如下:
IGeometryFactory3 factory = new GeometryEnvironment() as IGeometryFactory3;
byte[] geoBytes = factory.CreateWkbVariantFromGeometry(geometry) as byte[];
- 将byte[]数组转换成16进制字符串
public string ToHexStrFromByte( byte[] byteDatas) { StringBuilder builder = new StringBuilder(); for (int i = 0; i < byteDatas.Length; i++) { builder.Append(string.Format("{0:X2}", byteDatas[i])); } return builder.ToString().Trim(); }
4. 拼接SQL时要将上面的byte[]数组转码
其实postgis处理存到pg中的是一长串的16进制字符串,相信大家打开数据库能看到,要想拼接好SQL串就得这样来一下:st_geomfromwkb(decode('" + geoByteStr + "', 'hex'))其中的geoByteStr是byte[]转成16进制的字串
private void apgsql保存非sdeToolStripMenuItem_Click(object sender, EventArgs e)
{
IGeometryFactory3 factory = new GeometryEnvironment() as IGeometryFactory3;
byte[] geoBytes = factory.CreateWkbVariantFromGeometry(geometry) as byte[];
string sss = ToHexStrFromByte(geoBytes);
string sql = @"insert into geomfromwkb(decode('" + geoBytes + "', 'hex'))";
string sql1 = @"INSERT INTO public.gis_osm_railways_free_1(
osm_id, code, fclass, name, layer, bridge, tunnel, geom)
VALUES ( '444', 4444, '3333', '333', 3 ,'3', '3' ,ST_GeomFromText('st_astext(geomfromwkb(decode('" + sss + "', 'hex')))',4326));";
PgsqlHelper.ExecuteQuery(sql1);
}
附加NetTopologySuite
说明:wkb和geometry 转换的时候,即可以使用arcengine自带的封装函数,也可以使用NetTopologySuit。
NetTopoLogySuite:基于.net平台处理GIS空间数据及其空间关系的类库。开源代码库地址为:https://github.com/NetTopologySuite/NetTopologySuite
API文档:
Class NtsGeometryServices | NetTopologySuite
OGC标准: