最近学习了Spatialite,如何使用Sql语句去操作空间数据,Sqlite属于轻量级的数据库,但也能满足我们正常简单的数据查询等操作,接下来我将会附上我这几天操作的心得和代码分享:
注意:From后面直接跟表名查询结果即就是表的乘积基础上查询,如果想查单个可以把其中一个表图形先进行融合(A*1=A)再进行判断。
Sql代码:
判断点是否在线内查询相交:
select asText(geometry) from point where
ST_Intersects(point.geometry,GeomFromText('POLYGON((322.372427 113.231539, 230.763178 -34.295044, 49.924143 -20.018278, -141.622469 0.20714, -208.247377 109.662348, 83.236597 259.568392, 86.805788 398.766859, 216.486414 426.130659, 310.475122 334.52141, 322.372427 113.231539))'))=1
找出A和B相交的部分
select asText(A.geometry) from A,B where
ST_Intersects(A.geometry,B.geometry)=1
A和B相交为空则返回1,反之返回0
select ST_Disjoint(A.geometry,B.geometry) from A,B
返回A和B的距离集合
select *,ST_Distance(A.geometry,B.geometry) from A,B
擦除(从A中擦除B)
select asWKT(ST_Difference(A.geometry,B.geometry)) from A,B
where ST_Difference(A.geometry,B.geometry) is not null
将A中所有要素融合成一个
select asWKT(ST_Union(A.geometry)) from A
将A和B融合成一个形状
select asWKT(ST_Union(point.geometry,polygon.geometry)) from point,polygon
定义A以3.6为缓冲单位的缓冲区(3.6为对应其空间参考的距离单位)
select asText(ST_Buffer(A.geometry,3.6)) from A
判断A是不是简单的
select IsSimple(A.geometry) from A
返回了A边界上的所有点集合LineString
select AsText(Boundary(polygon.geometry)) from polygon
返回A上所有的顶点和对应的基本点
select AsText(ST_DissolvePoints(A.geometry)) from A
返回A的面积、返回A的质心(可能在面外)
select ST_Area(A.geometry) from A
select AsText(ST_Centroid(A.geometry)) from A
获取点的XYZM参数
select ST_X(point.geometry) as X,ST_Y(point.geometry) as Y,ST_Z(point.geometry) as Z,ST_M(point.geometry) as M from point
获取其对应的SRID参考
select ST_SRID(geometry) from ..TableName..
创建空间表
(1)首先创建一个对应不含空间信息列的空表
create table ..TableName..(
ID integer primary key autoincrement,
Name text not null)
(2)增加存储空间信息的列
select AddGeometryColumn('..TableName..','Geometry',4326,'Point','XY');
(表名,列名字,srid,数据类型,坐标类型)
(3)增加其空间索引
select CreateSpatialIndex('..TableName..','Geometry');
(4)插入空间数据(需要填写与之对应的SRID)
insert into ..TableName.. values(2,0,geomfromtext('POINT(-111.8720226.255935)',4326));
insert into ..TableName.. values(2,0,MakePoint(-111.879206,226.255935,4326));
从其它表查询获取获取数据
insert into pr (geometry) select geometry from prt
连接Sqlite数据库代码:
/// <summary>
/// 数据库文件路径
/// </summary>
public string DbPath { get; set; }
public SQLiteConnection conn { get; set; }
/// <summary>
/// 构造
/// </summary>
/// <param name="dbPath"></param>
public void OpenSqliteDataBase(string dbPath)
{
conn = new SQLiteConnection(
string.Format("Data Source={0};Initial Catalog=sqlite;Integrated Security=True;Max Pool Size=10", dbPath));
DbPath = dbPath;
conn.Open();
}
源码链接:Spatialite.zip_spatialitedll-C#代码类资源-CSDN下载
QQ群(GIS开发交流、数据共享、软件使用):993836992