ArcGIS10.2与SQLite
在刚刚结束的2013Esri开发者大会中,有好多用户都会咨询ArcGIS10.2什么时候推出,可以见得大家对ArcGIS新版本的期待,今天就给大家介绍一下一个新特性,使用SQLite来存储GIS数据。
大家都知道ArcGIS支持传统的关系型数据库(oracle、SQL Server、IBM DB2、Informix、PostgreSQL),还支持现在比较火的云数据库(SQL Azure、IBM Netszza),而且还有一些轻量级的数据库比如这次讲到的SQlite,而且还有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> .tables
- st_aux_spatial_reference_systems st_spatial_reference_systems
- st_geometry_columns st_vtspindex_interface
创建完毕之后,我们就可以使用桌面在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鈸鞱 Antarctica
Shape字段是乱码形式
同样,我们可以看看相关表的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。
这里面也有相关的字段类型可以参考如下
同样,我们也可以直接使用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数据库编辑空间数据。