ArcGIS学习笔记（十四）

今天学习下SQL语句在ArcMap中的应用

SQL(Structured Query Language)结构化查询语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

1.SQL基础

SQL 语言大致包含 4 个部分： 
（1） 数据定义语言(DDL)，例如：CREATE、DROP、ALTER 等语句。 
（2） 数据操作语言(DML)，例如：INSERT（插入） 、UPDATE（修改） 、DELETE（删除）语句。 
（3） 数据查询语言(DQL)，例如：SELECT语句。 
（4） 数据控制语言(DCL)，例如：GRANT、REVOKE、COMMIT、ROLLBACK 等语句。
在 ArcMap 中最长用到的为（2）和（3） ，其中 select 查询用的最多。

（1）常用SQL语句
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ‘%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1

（2）几个高级查询运算符
UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL） ，不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 
EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。  
INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。 
注：使用运算词的几个查询结果行必须是一致的。

（3）连接

分为 1. INNER JOIN 2. LEFT JOIN   3. RIGHT JOIN 4.LEFT OUTER JOIN
首先设定一个我们要用的两个表
表A              表B
ID  NAME   ID    CLASS  
1    IBM       1    C1
2    SONY   3    C3
3    BMW    4    C4
INNER JOIN:  SELECT * FROM   A      INNER JOIN B ON A.ID=B.ID
得表    
ID NAME   CLASS
1  IBM        C1
3  BMW     C3  
对了 就是 两个表的 ID 都存在并相同 得到这两个表的组合表
LEFT JOIN :  SELECT * FROM   A      LEFT JOIN B ON A.ID=B.ID

得表                   
ID  NAME  CLASS
1   IBM       C1
2   SONY   null
3   BMW    C3      
得到的是 A(left 语句的左边的表)的所有记录 而 B表对应的记录没有的话也要补齐  
RIGHT JOIN :   SELECT * FROM   A      RIGHT JOIN B ON A.ID=B.ID
得表                  
ID  NAME   CLASS
1  IBM        C1
3  BMW     C3 
4   null       C4
RIGHT(右)   得到的是 B(right 语句的右边的表)的所有记录 而A 表对应的记录没有的话也要补齐  
FULL OUTER JOIN:   SELECT * FROM   A      FULL OUTER JOIN B ON A.ID=B.ID
得表

ID  NAME  CLASS
1   IBM      C1 
2  SONY  null
3  BMW   C3
4  null      C4

2.SQL Expression在ArcMap中的应用 

（1）数据管理

提取(Extract)

通过 Query Builder 实现自定义条件的数据提取，主要包含以下三个工具
工具名                            操作数据类型     所在工具箱
Select                               矢量              Analysis\Extract
Table Select                     表格              Analysis\Extract
Extract by Attributes         栅格              Spatial\Analyst 

转换(Conversion)
工具名                                            操作数据类型         所在工具箱
Feature Class To Feature Class            矢量           Conversion\To Geodatabase
Table To Table                                      表格            Conversion\To Geodatabase
CADLinestoPolygonFeatures                CAD             Samples\Conversion\To From CAD
说明：
A.  Feature Class To FeatureClass 在 ArcToolbox 中可用，同时也是 ArcCatalog 中 import\Feature Class(Single)调用的工具
B.  CADLinestoPolygonFeatures 是 ESRI 自带的用 Model Builder 搭建的 Model，其中借助 Query Builder 实现对 CAD 中满足某些条件的线、标注匹配生成面，同时把标注内容作为属性值的功能。比如特定CAD图层的线和本层内点生成带标注属性的面就是在polyline和annotation都设置”layer” = xx

更新 
工具名                    所在位置                      备注
Select By Attribut  菜单栏Tools         借助Field Calculator实现自定义数据量更新
set Null                 Spatial Analyst\Conditional  

（2）地图制图

显示

Layer Properties\ Definition Query，实现自定义数据显示。在使用 Distributed Geodatabase等工具条或工具箱上工具的时候，会只对满足 Query 部分处理，比如，Definition Query后用 Get Cout 可以统计图层符合条件的要素数目。

标注

Layer Properties\ Labels \ classes labels，可以实现分级标注，应用 SQL Expression 设置每一级标注的数据。

对道路图层按照“路面质量”进行分级显示 

打开Layer Properties\ Labels面板，勾选label features in this layer，在method选择“Define classes of features and label each class differently”

点击Class中的Add按钮，添加标注名称（个数和分类数相同），不勾选“label features in this class”，否则标注会重复。

选择标注类，点击SQL Qurey，根据标注条件写入SQL查询语句

对每个等级的标注符号进行修改

结果如图 

 （3）地理处理

Layer  相关 
地理处理中，许多工具的输入项可以是数据，也可以是图层（含图层文件及内存图层） 。ArcGIS 环境下，如果输入时图层，一般只对图层中、或者图层被选中部分进行处理。可以借助如下 layer 工具制造图层（2种图层均可）参与地理处理。
工具名                                    功能                                                    所在工具箱
select layer by attribute  同菜单栏Tools\Select by Attribute             Layers and Table Views
Make Feature Layer       创建矢量内存图层，参与地理处理                     同上
Make Query Table          同上，操作数据为属性                                      同上  

Model Builder 
借助 SQL Expression 实现相同 CAD 层的 polyline 加上 annotation 生成带标注属性的多边形  CAD Lines to Polygon Features (Samples
SQL Expression 可以作为单独的变量，如下图，右键 Create Variable新建

 线性参考(Linear Referencing)

主要运用Selct by Attributes

3. SQL Expression概览 

主要有“Selct by Attributes”和“Query Builder”两个查询器，都是 SELECT * FROM layer WHERE————————语句查询

（1）超简单语句-直接查询
选中 dltb 层 dlbm是”011”的要素
[DLBM] =‘011’
选中 dltb 层 dlbm与 dlbm1 不相等的要素
[DLBM] <> [DLBM1]
注意：不同格式的数据字段的写法不一样，[]为 PGDB 中的写法

（2）简单语句-简单函数、运算符的使用
选中 dltb层 dlbm 前两位是 01 的要素
Left(TRIM([dlbm]),2)=‘01’
选中 dlmc 里包含 ‘果’的要素
[dlmc] like ‘*果*'

模糊查询

shapefile
cast ( 字段名 as character) 是shapefile里转为字符类型的函数，注意的是这里的字段名不需要引号
实例：

cast ( test_like as character) like '%1%'

pGDB

cstr( [test_like] ) like "*1*"

fGDB

CAST("OBJECTID" AS CHARACTER) = '1'

（3）"and" 和 "or"
选中当dlbm 为’011’并且 dlmc 为’水田’的要素
[dlbm]=‘0110’ and [dlmc] =‘水田’
选中当dlbm 为’011’ 或者 dlmc 为’水田’的要素
[dlbm]=‘0110’ or dlmc =‘水田’
（4）"in" 和 "not in"
选中 dlbm 是’0110’或者’0120’或者’0130’的要素 

可以写成
[dlbm]= ‘011’ or [dlbm]= ‘012’ or [dlbm]= ‘013’
这种情况用 in 显得方便的多
[dlbm] in (‘011’,’ 012’,’013’)
与in相反的为not in

（5）嵌套 SQL语句
随机选中 10 条记录
[OBJECTID]  in (select top 10 [OBJECTID] from dltb order by rnd( [OBJECTID] ) )
选中 OBJECTID 从 11 到15 的记录
[OBJECTID]  in  (select top 5 OBJECTID from (select top 15 * from dltb order by OBJECTID asc) b
order by OBJECTID desc)
选择地类图斑编码重复的记录记录
方法 1：选择重复要素但是会保留一个重复记录，适用于删除重复的要素
[OBJECTID] not in (select max(OBJECTID) from dltb group by dlbm)
方法 2：只要存在重复的即选中
[dlbm] in (select dlbm from dltb  group by dlbm having count(*) >1) 

从其他mdb 中查询
这个只适用于 pgdb 格式。这里嵌套的 sql 语句均为同一个 mdb 中的数据，如果要连接其他 mdb 中的数据，可以用
[dlbm] in (select dlbm from [c:\tempdata.mdb].代码名称对照表)
这句话的意思是选中地类图斑层里面地类代码在tempdata.mdb中代码名称对照表里的dlbm字段中有的所有要素。以查询储存在Excel表里的县为例，很多，肯定不能一个个依字段查询，（1）把Excel表Join到Shapefile文件，关联字就是“县名”；（2）如果数据是放在 Personal Geodatabase 里的话，直接用 select by attribute，把excel数据导入到任何一个 mdb里，作为一张表（假设名字为 county），存放区县名的字段为 name，写入表达式“[NAME] in (select name from [f:\testdata\china.mdb].county)”，这里 "  [mdb路径].表名   " 这种格式来应用某路径mdb的表。这句话的意思就是，select * from 全国区县 where 区县名 in (select 区县名 from 另外一张表)。
在多个点中删除属性值最大的10个点

方法1：启动编辑，在属性表中按照属性值进行降序排序，选中进行删除；

方法2：SQL 语句：
select * from tablename where
[columnname] in (select top 10 * from (select [columnname]  from tablename order by [columnname]  desc))
选择所有城市道路联系强度排名前五

方法1：用ACCESD打开数据表，执行SQL查询

select BSM,QSXZ from [E:\test.mdb].temp a where bsm in (select top 5 bsm from [E:\test.mdb].temp where qsxz=a.qsxz order by bsm desc)  ORDER BY QSXZ        bsm就是联系强度，qsxz是城市分类

方法2：ArcMap中Select查询

select * from tablename where

[联系强度] in (select top 5 B.[联系强度] from tablename AS B where [城市类别]=tablename.[城市类别] order by B.[联系强度] desc)

（6）NULL

空，在属性表里显示为 <Null>，注意和 "Null" 、 ""、0 的区别：Null 是什么都没有，各种类型的字段都可以为 Null，判断是否为 Null 用的是 [字段] is Null 而不是 [字段] = Null

（7）通配符

内置模式匹配提供了一个功能丰富的工具用于比较字符串。 下表显示可以与 Like 运算符一起使用的通配符以及它们匹配的数字或字符串的数目。

 匹配模式的一些规则：

字符列表开头的感叹号 (!) 表示如果表达式中发现除字符列表中字符以外的任何字符，将生成一个匹配。在方括号外面使用时，感叹号匹配其自身。 
您可以在字符列表的开头（如果使用了感叹号，则在感叹号之后）或结尾使用连字符 (-) 来匹配连字符自身。在任何其他位置，连字符标识某一范围的 ANSI 字符。 
指定某一范围的字符时，字符必须以升序（A-Z 或 0-100）显示。[A-Z] 是有效模式，而 [Z-A] 是无效模式。 
字符序列 [ ] 被忽略；它被认为是零长度字符串（零长度字符串：不含字符的字符串。可以使用零长度字符串来表明您知道该字段没有值。输入零长度字符串的方法是键入两个彼此之间没有空格的双引号 (" ")。 ） （零长度字符串：不含字符的字符串。可以使用零长度字符串来表明您知道该字段没有值。输入零长度字符串的方法是键入两个彼此之间没有空格的双引号 (" ")。 ） ("")。   
 
以下示例返回以字母 P 开头、后跟 A 和 F 之间任何字母以及三个数字的数据：
Like “P[A-F]###”

（8）复杂SQL语句

以土地利用现状数据库为例，要通过属性选择出地类图斑层扣除线状地物面积和线状地物层坐落在该图斑内的现状地物面积的和不相等的地类图斑

[OBJECTID]  in (select objectid from 
(select objectid, xzqdm as 坐落单位单位代码,tbh as Dltbbh,dltb_xzdwmj as 地类图斑层线状地物面积,xzdw_mj as 线状地物层线状地物面积,
if(abs((dltb_xzdwmj-xzdw_mj))>0.0001,abs(dltb_xzdwmj-xzdw_mj),0) as DIfferent  from 
(select a.objectid, a.zldwdm asxzqdm,a.xzdwmj as dltb_xzdwmj,a.tbbh as tbh,b.xzdwmj as xzdw_mj from dltb a inner join 
(select sum(xwmj) as xzdwmj,kctbbh1,kctbdwdm1 from 
(SELECT sum(xzdwmj*kcbl) as xwmj,kctbbh1,kctbdwdm1 from xzdw   group by kctbbh1,kctbdwdm1  union all
SELECT sum(xzdwmj*(1-kcbl)) as xwmj,kctbbh2 as kctbbh1,kctbdwdm2 as kctbdwdm1 from xzdw   group by kctbbh2,kctbdwdm2 ）

group by kctbbh1,kctbdwdm1 order by kctbbh1) b on a.zldwdm=b.kctbdwdm1 and a.tbbh=b.kctbbh1))
where abs(DIfferent)>0.0001 order by objectid)
SQL Expression 中可以写出写出非常复杂的语句，来完成表内表间的复杂查询。