欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

当ArcGIS10.2遇到SQLite

程序员文章站 2024-02-18 23:19:10
...

在刚刚结束的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数据中创建要素类或者将已有的要素类导入到数据库中

当ArcGIS10.2遇到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。

这里面也有相关的字段类型可以参考如下

当ArcGIS10.2遇到SQLite

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
当ArcGIS10.2遇到SQLite


同样,我们也可以使用数据库管理工具来对SQLite里面的对象进行研究

当ArcGIS10.2遇到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数据库编辑空间数据。

-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------