//坐标系 EPSG码
//xian 1980 3 Degree GK CM 123 2386
//xian 1980 3 Degree GK CM 126 2387
//xian 1980 3 Degree GK CM 129 2388
//xian 1980 3 Degree GK CM 132 2389
//xian 1980 3 Degree GK CM 135 2390
//xian 1980 3 Degree GK Zone 41 2365
//xian 1980 3 Degree GK Zone 42 2366
//xian 1980 3 Degree GK Zone 43 2367
//xian 1980 3 Degree GK Zone 44 2368
//xian 1980 3 Degree GK Zone 45 2369
//xian 1980 GK CM 123 2346
//xian 1980 GK CM 129 2347
//xian 1980 GK CM 135 2348
//xian 1980 GK Zone 21 2335
//xian 1980 GK Zone 22 2336
//xian 1980 GK Zone 23 2337
//CGCS2000 3 Degree GK CM 123 4550
//CGCS2000 3 Degree GK CM 126 4551
//CGCS2000 3 Degree GK CM 129 4552
//CGCS2000 3 Degree GK CM 132 4553
//CGCS2000 3 Degree GK CM 135 4554
//CGCS2000 3 Degree GK Zone 41 4529
//CGCS2000 3 Degree GK Zone 42 4530
//CGCS2000 3 Degree GK Zone 43 4531
//CGCS2000 3 Degree GK Zone 44 4532
//CGCS2000 3 Degree GK Zone 45 4533
//CGCS2000 GK CM 123 4510
//CGCS2000 GK CM 126 4511
//CGCS2000 GK CM 129 4512
//CGCS2000 GK Zone 21 4499
//CGCS2000 GK Zone 22 4500
//CGCS2000 GK Zone 23 4501
//创建Spatialite数据库库
private void toolStripButtonCreateSpatialiteDB_Click(object sender, EventArgs e)
{
string dbFile = Application.StartupPath + "\\Data\\Test.sqlite";
string dbConnStr = "Data Source=" + dbFile;
//创建库
//SQLiteConnection.CreateFile(dbFile);
//增加图层
string msg = "";
//创建表
using (SQLiteConnection conn = new SQLiteConnection(dbConnStr))
{
conn.Open();
using (SQLiteCommand cmd = conn.CreateCommand())
{
string sql = "";
conn.LoadExtension("mod_spatialite.dll");
//sql = "SELECT load_extension('mod_spatialite.dll');";
sql = "select InitSpatialMetaData()";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
#region 创建点层
sql = "CREATE TABLE Dian (PK_UID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,meas_value DOUBLE NOT NULL)";
cmd.CommandText = sql;
try
{
cmd.ExecuteNonQuery();
}
catch(Exception exc)
{
}
//bRet = ExecuteSql(sql, conn);
/* 添加图形列 */
sql = "SELECT AddGeometryColumn('Dian', 'GEOMETRY',2346, 'POINT', 'XY')"; //2346 为EPSG码
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
/* 添加空间索引 */
sql = "SELECT CreateSpatialIndex('Dian', 'GEOMETRY')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
/* 通过OGC标准的WTK 文件描述格式插入一个点记录*/
sql = "INSERT INTO Dian(PK_UID, name, meas_value, GEOMETRY) VALUES (1, 'first point', 1.23456,GeomFromText('POINT(636000 5209340)',2346))";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
/* 通过OGC标准的WTK 文件描述格式插入一个点记录*/
sql = "INSERT INTO Dian(GEOMETRY, meas_value, name, PK_UID) VALUES (GeomFromText('POINT(636100 5209340)',2346),11.123456789, 'eleventh point', 2)";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
#endregion
#region 创建线层
sql = "CREATE TABLE Xian (PK_UID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,meas_value DOUBLE NOT NULL)";
cmd.CommandText = sql;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception exc)
{
}
//bRet = ExecuteSql(sql, conn);
/* 添加图形列 */
sql = "SELECT AddGeometryColumn('Xian', 'GEOMETRY',2346, 'LINESTRING', 2)"; //2346 为EPSG码
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
/* 添加空间索引 */
sql = "SELECT CreateSpatialIndex('Xian', 'GEOMETRY')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
/* 通过OGC标准的WTK 文件描述格式插入一个点记录*/
sql = "INSERT INTO Xian(PK_UID, name, meas_value, GEOMETRY) VALUES (1, 'line1', 1.23456,GeomFromText('LINESTRING(636000 5209340,633950 5212200,634400 5207800)',2346))";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
/* 通过OGC标准的WTK 文件描述格式插入一个点记录*/
//sql = "INSERT INTO Xian(GEOMETRY, meas_value, name, PK_UID) VALUES (GeomFromText('POINT(636100 5209340)',2346),11.123456789, 'eleventh point', 2)";
//cmd.CommandText = sql;
//cmd.ExecuteNonQuery();
#endregion
#region 创建面层
sql = "CREATE TABLE Mian (PK_UID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,meas_value DOUBLE NOT NULL)";
cmd.CommandText = sql;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception exc)
{
}
//bRet = ExecuteSql(sql, conn);
/* 添加图形列 */
sql = "SELECT AddGeometryColumn('Mian', 'GEOMETRY',2346, 'POLYGON', 2)"; //2346 为EPSG码
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
/* 添加空间索引 */
sql = "SELECT CreateSpatialIndex('Mian', 'GEOMETRY')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
/* 通过OGC标准的WTK 文件描述格式插入一个点记录*/
sql = "INSERT INTO Mian(PK_UID, name, meas_value, GEOMETRY) VALUES (1, 'Poly1', 1.23456,GeomFromText('POLYGON((636000 5209340,633950 5212200,634400 5207800,632409 5209760,636000 5209340))',2346))";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
/* 通过OGC标准的WTK 文件描述格式插入一个点记录*/
//sql = "INSERT INTO Xian(GEOMETRY, meas_value, name, PK_UID) VALUES (GeomFromText('POINT(636100 5209340)',2346),11.123456789, 'eleventh point', 2)";
//cmd.CommandText = sql;
//cmd.ExecuteNonQuery();
#endregion
}
}
//打开或创建Spatialite数据库
//DotSpatial.Plugins.SpatiaLite.SpatiaLiteHelper spatialiteHelper = DotSpatial.Plugins.SpatiaLite.SpatiaLiteHelper.Open(dbConnStr, out msg);
//SpatiaLiteHelper.SetEnvironmentVars();
//List<string> listTableNames = spatialiteHelper.GetTableNames();
//foreach (string tableName in listTableNames)
//{
// MessageBox.Show(tableName);
//}
//IFeatureSet fs = spatialiteHelper.ReadFeatureSet("t");
//mapMain.Layers.Add(fs);
//sql = "insert into TableName values(2, 0, MakePoint(-111.879206, 226.255935, 4326))";
}
