当ArcGIS10.2遇到SQLite
在刚刚结束的2013Esri开发者大会中，有好多用户都会咨询ArcGIS10.2什么时候推出，可以见得大家对ArcGIS新版本的期待，今天就给大家介绍一下一个新特性，使用SQLite来存储GIS数据。 大家都知道ArcGIS支持传统的关系型数据库（oracle、SQL Server、IBM DB2、In
在刚刚结束的2013Esri开发者大会中，有好多用户都会咨询ArcGIS10.2什么时候推出，可以见得大家对ArcGIS新版本的期待，今天就给大家介绍一下一个新特性，使用SQLite来存储GIS数据。
大家都知道ArcGIS支持传统的关系型数据库（oracle、SQL Server、IBM DB2、Informix、PostgreSQL），还支持现在比较火的云数据库（SQL Azure），而且还有一些轻量级的数据库比如这次讲到的SQlite，而且还支持数据仓库（IBM Netszza、TeraData Database）。
首先看看SQLite数据库，这个数据库一般应用在手机开发中。
SQLite，是一款轻型的数据库，是遵守ACID的关联式数据库管理系统，它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源世界著名的数据库管理系统来讲，它的处理速度比他们都快。SQLite第一个Alpha版本诞生于2000年5月。 至今已经有12个年头，SQLite也迎来了一个版本 SQLite 3已经发布。
下载软件点击
ArcGIS10.2可以支持将数据存储在SQLite上，ArcGIS提供了相关的GP工具（ArcPy，支持在SQLite上创建空间数据存储）
import arcpy # Set local variables sqlite_database_path = 'C:\sqlite-3_6_22\ex4.sqlite' # Execute CreateSQLiteDatabase arcpy.gp.CreateSQLiteDatabase(sqlite_database_path, "ST_GEOMETRY") u'C:\\sqlite-3_6_22\\ex4.sqlite'
注意：虽然说SQLite默认的文件后缀名是*.db，但是ArcGIS只支持*.sqlite后缀名
创建完毕之后，系统会默认创建四个表
sqlite> .tables st_aux_spatial_reference_systems st_spatial_reference_systems st_geometry_columns st_vtspindex_interface
通过控制 SQLite 数据库的存储位置的文件夹上的权限来控制对 SQLite 数据库的访问。与其他数据库不同的是您没有创建该数据库，通过进行身份验证的用户和你不要向其他用户授予对特定的数据集的权限。
SQLite 可以读取由多个用户，但您应不进行任何更新到数据库而另一个用户正在更新。例如，不要将数据追加到现有的表或另一个用户正在将数据追加到现有的表或数据库中创建表时在数据库中创建表。
创建完毕之后，我们就可以使用桌面在SQlite数据中创建要素类或者将已有的要素类导入到数据库中
上面的ext4.sqlite里面有三个要素类：
dd是一个空要素类（Unknown)
ff是一个有数据的要素类（Unknown)
gg是一个空要素类（Xian_1980_3_Degree_GK_CM_120E）
将要素类导入之后就很好奇，这些数据是怎么存储到SQLite里面的，先查看一下里面的表
C:\sqlite-3_6_22>sqlite3 ex4.sqlite SQLite version 3.6.22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .tables dd st_spindex__ff_Shape ff st_spindex__ff_Shape_node gg st_spindex__ff_Shape_parent st_aux_spatial_reference_systems st_spindex__ff_Shape_rowid st_geometry_columns st_spindex__gg_SHAPE st_spatial_reference_systems st_spindex__gg_SHAPE_node st_spindex__dd_SHAPE st_spindex__gg_SHAPE_parent st_spindex__dd_SHAPE_node st_spindex__gg_SHAPE_rowid st_spindex__dd_SHAPE_parent st_vtspindex_interface st_spindex__dd_SHAPE_rowid我们可以看到不光有同名的dd、ff、gg表，还有一些系统表（我们习惯于称呼为支持这种数据库的系统表），这就是为什么GIS数据与其他数据的区别，因为GIS数据有地理的概念，这无疑就需要知道投影的信息，在以前我们研究NoSQL（MongoDB）也看到了有存储投影的相关表。
ArcGIS支持MongoDB数据源 - ArcGIS技术研究 - 博客频道 - CSDN.NET
我们可以看看这些表里面的相关信息
sqlite> select * from st_geometry_columns; 1|dd|SHAPE|2006|3|300001|RTREEXY 2|ff|Shape|6|2|300002|RTREEXY 3|gg|SHAPE|6|2|2385|RTREEXY sqlite> select * from st_aux_spatial_reference_systems; 300000|ESRI|300000|UNKNOWN|-450359962737.05|-450359962737.05|10000.0|0.0|1.0|0.0|1.0||||1 300001|ESRI|500000|UNKNOWN|-450359962737.05|-450359962737.05|10000.0|0.0|1.0|-100000.0|10000.0|0.001|0.0|0.001|1 300002|EPSG|4326|GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137.0,298.257223563]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]|e-09|0.001|0.001|1 sqlite>2385就是指向的Xian_1980_3_Degree_GK_CM_120E，这个都是符合OGC标准的，ArcGIS10.1之前还是用内部的SRID，现在好了 只需要知道这个标准的编码即可（比如WGS84——4326），具体查询可以参考如下链接：
http://help.arcgis.com/en/arcgisserver/10.0/apis/rest/pcs.html
可以看到St_geometry_columns表是存储空间字段的投影信息的类似于Oracle数据的St_geometry_columns，这个st_aux_spatial_reference_systems类似于st_spatial_reference。
查看一下ff数据的信息
sqlite> .mode column sqlite> select * from ff; OBJECTID Shape CONTINENT ---------- ---------- ---------- 1 d鈸伐 Asia 2 d鈸? North Amer 3 d鈸-k Europe 4 d鈸 Africa 5 d鈸,- South Amer 6 d鈸 Oceania 7 d鈸? Australia 8 d鈸鞱 AntarcticaShape字段是乱码形式
同样，我们可以看看相关表的Schema
sqlite> .schema
CREATE TABLE dd (
OBJECTID integer primary key autoincrement not null,
SHAPE geometryblob check((typeof(SHAPE) = 'blob' and length(SHAPE) >= 18 and cast(hex(substr(SHAPE,1,1)) as integer) = 64) or typeof(SHAPE) = 'null'));
CREATE TABLE ff (
OBJECTID integer primary key autoincrement not null,
Shape geometryblob check((typeof(Shape) = 'blob' and length(Shape) >= 18 and cast(hex(substr(Shape,1,1)) as integer) = 64) or typeof(Shape) = 'null'),
CONTINENT text(13) check((typeof(CONTINENT) = 'text' or typeof(CONTINENT) = 'null') and not length(CONTINENT) > 13));
CREATE TABLE gg (
OBJECTID integer primary key autoincrement not null,
SHAPE geometryblob check((typeof(SHAPE) = 'blob' and length(SHAPE) >= 18 and cast(hex(substr(SHAPE,1,1)) as integer) = 64) or typeof(SHAPE) = 'null'));
CREATE TABLE st_aux_spatial_reference_systems (
srid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
auth_name TEXT(256),
auth_srid INT32,
srtext TEXT(2048),
falsex FLOAT64 NOT NULL,
falsey FLOAT64 NOT NULL,
xyunits FLOAT64 NOT NULL,
falsez FLOAT64 DEFAULT 0.0,
zunits FLOAT64 DEFAULT 1.0,
falsem FLOAT64 DEFAULT 0.0,
munits FLOAT64 DEFAULT 1.0,
xycluster_tol FLOAT64,
zcluster_tol FLOAT64,
mcluster_tol FLOAT64,
object_flags INT32 DEFAULT 0);
CREATE TABLE st_geometry_columns (
gcid INTEGER PRIMARY KEY AUTOINCREMENT,
f_table_name CLOB NOT NULL,
f_geometry_column CLOB NOT NULL,
geometry_type INT32,
coord_dimension INT32,
srid INTEGER NOT NULL,
spatial_index_type CLOB,
CONSTRAINT gc_uk UNIQUE (f_table_name,f_geometry_column));
CREATE VIRTUAL TABLE st_spatial_reference_systems USING VSRS();
CREATE VIRTUAL TABLE st_spindex__dd_SHAPE USING RTREE (pkid,minx,maxx,miny,maxy);
CREATE TABLE "st_spindex__dd_SHAPE_node"(nodeno INTEGER PRIMARY KEY, data BLOB);
CREATE TABLE "st_spindex__dd_SHAPE_parent"(nodeno INTEGER PRIMARY KEY, parentnode INTEGER);
CREATE TABLE "st_spindex__dd_SHAPE_rowid"(rowid INTEGER PRIMARY KEY, nodeno INTEGER);
CREATE VIRTUAL TABLE st_spindex__ff_Shape USING RTREE (pkid,minx,maxx,miny,maxy);
CREATE TABLE "st_spindex__ff_Shape_node"(nodeno INTEGER PRIMARY KEY, data BLOB);
CREATE TABLE "st_spindex__ff_Shape_parent"(nodeno INTEGER PRIMARY KEY, parentnode INTEGER);
CREATE TABLE "st_spindex__ff_Shape_rowid"(rowid INTEGER PRIMARY KEY, nodeno INTEGER);
CREATE VIRTUAL TABLE st_vtspindex_interface USING VTSpindex();
CREATE TRIGGER st_delete_trigger_dd_SHAPE AFTER DELETE ON dd FOR EACH ROW BEGIN DELETE FROM st_spindex__dd_SHAPE WHERE pkid = OLD._ROWID_; END;
CREATE TRIGGER st_delete_trigger_ff_Shape AFTER DELETE ON ff FOR EACH ROW BEGIN DELETE FROM st_spindex__ff_Shape WHERE pkid = OLD._ROWID_; END;
CREATE TRIGGER st_insert_trigger_dd_SHAPE AFTER INSERT ON dd FOR EACH ROW BEGIN SELECT InsertIndexEntry ('st_spindex__dd_SHAPE',NEW.SHAPE,NEW._ROWID_,2); END;
CREATE TRIGGER st_insert_trigger_ff_Shape AFTER INSERT ON ff FOR EACH ROW BEGIN SELECT InsertIndexEntry ('st_spindex__ff_Shape',NEW.Shape,NEW._ROWID_,2); END;
CREATE TRIGGER st_update_trigger_dd_SHAPE AFTER UPDATE ON dd FOR EACH ROW BEGIN SELECT UpdateIndexEntry ('st_spindex__dd_SHAPE',NEW.SHAPE,NEW._ROWID_,2); END;
CREATE TRIGGER st_update_trigger_ff_Shape AFTER UPDATE ON ff FOR EACH ROW BEGIN SELECT UpdateIndexEntry ('st_spindex__ff_Shape',NEW.Shape,NEW._ROWID_,2); END;
从上面可以看出，其实也是建立的Table、Virtual Table、Trigger等。而且Shape字段的类型是geometryblob，核心还是BLOB。
这里面也有相关的字段类型可以参考如下
SQLite 也是不同于其他的数据库中字段未分配特定的数据类型和数据类型定义没有得到严格执行。相反，SQLite 使用要素类可以在其中存储不同的数据类型的值。
然而，ArcGIS 可以只与一个每个字段的数据类型和不会严格执行数据类型。在 ArcGIS 中查看 SQLite 数据时，您应该意识到这一差异的数据类型强制执行。
同样，我们也可以直接使用SQL语句创建表，创建坐标，使用ArcMap提供的Add XY坐标来显示，基本上ArcGIS10.1之前的版本，Android开发都是使用这种方式
C:\sqlite-3_6_22>sqlite3 ex3.sqlite SQLite version 3.6.22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table tbl1(x smallint,y smallint); sqlite> insert into tbl1 vaules(10,10); Error: near "vaules": syntax error sqlite> insert into tbl1 values(10,10); sqlite> insert into tbl1 values(10,20); sqlite> insert into tbl1 values(20,20); sqlite> insert into tbl1 values(20,10); sqlite> select * from tbl1; 10|10 10|20 20|20 20|10
同样，我们也可以使用数据库管理工具来对SQLite里面的对象进行研究
同样，我们也可以使用代码来操作SQLite，以下是C#来操作SQLite，需要引用System.Data.SQLite（可能需要用户自己下载dll)
public void test()
{
string connstring = "Data Source=C:\\Documents and Settings\\esrichina\\桌面\\sqlite\\sqlite-3_6_22\\ex4.sqlite";
SQLiteConnection conn = new SQLiteConnection(connstring);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(conn);
//查询ff要素类的表
cmd.CommandText = "select * from ff";
SQLiteDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
//获得ObjectID值
int a = reader.GetInt32(0);
//获得SHAPE值
var aaa = reader.GetValue(1);
//获得NAME字段值
string aa = reader.GetString(2);
}
}
同样，使用ArcGIS Desktop10.2不仅仅能够创建空间字段为ST_Geometry类型的（Esri支持），也提供SQLite原生提供的Spatialite类型的
>>> arcpy.gp.CreateSQLiteDatabase(r'C:\c.sqlite',"SPATIALITE") u'C:\\c.sqlite'
那么，我在已经创建好的SQLite的原生Statialite存储也同样创建了相关系统表
sqlite> .tables SpatialIndex vector_layers_auth geom_cols_ref_sys vector_layers_field_infos geometry_columns vector_layers_statistics geometry_columns_auth views_geometry_columns geometry_columns_field_infos views_geometry_columns_auth geometry_columns_statistics views_geometry_columns_field_infos geometry_columns_time views_geometry_columns_statistics spatial_ref_sys virts_geometry_columns spatialite_history virts_geometry_columns_auth sql_statements_log virts_geometry_columns_field_infos vector_layers virts_geometry_columns_statistics我们查看一下相关表的记录
sqlite> select * from geometry_columns; a|shape|6|2|4326|1 sqlite> .mode column sqlite> select * from a; 1 Asia 2 North Amer 3 Europe 4 Africa 5 South Amer 6 Oceania 7 Australia 8 Antarctica
大数据量导入测试：
我将一个面状要素类（3000000）记录的数据导入到SQLite里面，系统会自动创建索引，在1：10000比例尺下浏览速度在秒级延迟，可见效果还是不错的。
Executing: FeatureClassToFeatureClass "C:\New File Geodatabase (3).gdb\a" C:\sqlite-3_6_22\ex4.sqlite aa # "DB2GSE_ST_ "DB2GSE_ST_" true true false 8 Double 0 0 ,First,#,C:\New File Geodatabase (3).gdb\a,DB2GSE_ST_,-1,-1;DB2GSE_SDE "DB2GSE_Sde" true true false 8 Double 0 0 ,First,#,C:\New File Geodatabase (3).gdb\a,DB2GSE_SDE,-1,-1;Shape_Length "Shape_Length" false true true 8 Double 0 0 ,First,#,C:\New File Geodatabase (3).gdb\a,Shape_Length,-1,-1;Shape_Area "Shape_Area" false true true 8 Double 0 0 ,First,#,C:\New File Geodatabase (3).gdb\a,Shape_Area,-1,-1" # Start Time: Thu Jun 13 03:04:35 2013 Succeeded at Thu Jun 13 03:27:29 2013 (Elapsed Time: 22 minutes 54 seconds)
假想：
咨询过Android工程师，好像Windows生成的SQLite文件（*.sqlite）可以直接拷贝到Android手机上使用，如果ArcGIS10.2推出后，也不排除android的ArcGIS API有直接读取数据库里面的Shape字段也就是读取成图形信息，这个应用也很广泛吧。
未完待续：
还有一个问题没有弄清楚，ArcGIS10.2桌面也提供了关于SQLite的Dll，这个现在还不太清楚干什么用
1：有可能使用桌面操作SQLite就是调用这个dll
2：使用SQL可以直接操作SQLite类似SQL 操作Oracle数据库编辑空间数据。
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------