Postgis
2012年2月29日
2015年6月8日重构
2015年7月4日添加数据组织、数据操作
2015年7月6日添加运算符
1目标:使用postgis扩展Postgresql的空间存储能力
2方法:安装、使用postgis。
2.1 安装postgis数据库
下载与postgresql对应的postgis安装文件,按照提示进行安装;
postgis-pg91-setup-1.5.3-2.exe
安装完成postgis后,会出现template_postgis模板库和postgis数据库;
2.2 扩展Postgresql
扩展sql文件应该在安装目录下,share/extension文件夹中。
可以通过以下几种方式加入扩展。
参考:http://www.xuebuyuan.com/1655300.html
2.2.1 创建扩展(推荐):CREATE EXTENSION postgis
或者使用PgAdmin,拓展-》添加拓展,添加sql文件名,自动生成sql,点击确定添加扩展。
安装完成后,可以看到扩展中的内容,postgis中导入大量函数。所有函数都可以在此处查看。
函数的定义可以架构-》函数中查看定义,详细说明请参见帮助文件。
2.2.2 加载sql:pgsql -f ../share/extension/postgis.sql
加载postgis,创建相应的空间数据表。
2.2.3 新建一个以template_postgis为模板的数据库
在定义中选择模板,其它的选项根据需要填写;
成功创建后会在数据表中出现geometry_columns(地理数据)和spatial_ref_sys(所有的地理参考系统)两个表;
3方法:数据转储
目标:将shp格式的矢量数据,存储到postgres中。
3.1 方法:使用命令行工具shp2pgsql
3.1.1 shp2pgsql工具用法
D:\Program Files\Postgresql\9.1\bin>shp2pgsql
RCSID: $Id: shp2pgsql-core.h 5983 2010-09-1911:27:05Z mcayland $ RELEASE: 1.5
SE_GEOS=1 USE_PROJ=1 USE_STATS=1
USAGE: shp2pgsql[<options>] <shapefile> [<schema>.]<table>
OPTIONS:
-s <srid> Set the SRID field. Defaults to -1.
(-d|a|c|p) These are mutually exclusiveoptions:
-dDrops the table,then recreates it and populates
it with current shape file data.
-aAppends shape file into current table,must be
exactly the same table schema.
-cCreates a new table and populates it,this is the
default if you do not specify anyoptions.
-pPrepare mode,only creates the table.
-g <geocolumn> Specify the name of thegeometry/geography column.
(mostly useful in append mode).
-D Usepostgresql dump format (defaults to sql insert statments).
-G Usegeography type (requires lon/lat data).
-kKeep postgresql identifiers case.
-i Useint4 type for all integer dbf fields.
-ICreate a spatial index on the geocolumn.
-SGenerate simple geometries instead of MULTI geometries.
-wOutput WKT format (drops M and introduces coordinate drifts).
-W <encoding> Specify the characterencoding of Shape's
attribute column. (default :"WINDOWS-1252").
-N <policy> NULL geometries handlingpolicy (insert*,skip,abort).
-nOnly import DBF file.
-?Display this help screen.
3.1.2 生成导入sql语句
3.1.3 执行sql语句
这里的空间数据被以字符串的形式存储;将这生成的sql语句以\i或者gui执行,就会生成数据表;
3.2 方法:在postgresql的pgAdmin中使用插件shp2pgsql导入
安装完成postgis后,会在pgAdminIII的插件中出现PostGIS Shapefile and DBF loader插件;点击数据库中架构,可以激活该插件。
3.2.1 连接数据库
点击出现导入对话框;在其中输入postgis数据库的连接参数;然后点击Test connection,测试连接;最下方的Import Log出现提示:
Connecting: host=localhost port=5432 user=postgresdbname=hainan password='***'
Connection succeeded.
说明连接成功;否则根据提示重新配置;
3.2.2 选择shape文件
连接成功后,点击最上方的shapefile文件选择,出现shapefile文件选择对话框,然后找到需要的shape文件,点击Open就可以加载地图;如果要加载同一目录的多个shapefile文件,可以使用左侧的add按钮,将目录加入左侧的快捷目录中,但是每次只能加载一个文件;
3.2.3 配置地图属性
导入之前,在options按钮打开的选项对话框中,选择要使用的编码;因为这里的文件中要使用中文属性,所以要使用gb2312;配置SRID为地图的投影EPSG编号;
3.2.4 导入地图
点击Import导入地图,成功后出现提示:
…
shapefile import completed.
否则根据提示重新设置;
3.3 方法:在qgis中使用spit导入
使用database->split->import shapefile topostgresql,建立连接,添加shp,确定后就可以在postgresql中看到增加的shp表。
4方法: postgis数据组织
目标:在数据表中组织矢量元素的属性和几何。
方法:postgis使用一组postgres函数来处理数据。对空间数据的操作一般用ST_前缀。
参考:Postgis help chaper4-DataManagement。
http://www.jb51.cc/cata/421962
4.1 数据组织:属性数据和几何数据统一存储(与sql92的分离存储不同???)。
数据类型为geometry,存储格式为WKB,元数据可由geometry_columns视图查看。
坐标系统由spatial_ref_sys表维护。
注意:geometry默认为平面坐标,如果设置为球面坐标系统将自动转换为geography类型。
参考:http://www.cnblogs.com/fre2technic/archive/2011/05/09/2041500.html
http://123.125.114.20/view/e52cdcd428ea81c758f578ac.html?re=view
http://wenku.baidu.com/view/5d29b55f312b3169a551a405.html
4.2 几何数据类型:支持WKT,WKB,KML,GML,geohash,geojson等。
以WKT为例,其它方式与此相似。
将postgis数据转换为WKT,WKB:ST_asText(the_geom),ST_asBinary。
示例:将数据转换为WKT,WKB
select st_asewkt(the_geom),
st_asText(the_geom),
st_asBinary(the_geom),
the_geom as"Origin"
from "Noded" wheregid=2;
将WKT转换为geom。
selectst_asText(st_geomfromtext('POINT(114 40)',4326));
4.2.1 EWKT:WKT扩展SRID。
如:SRID=4326;POINTM(114,40,0.02)
5方法:操作几何数据
目标:通过sql能够进行空间数据操作。
参考:http://www.jb51.cc/article/p-ayidybiu-ek.html
5.1 几何字段操作:addgeometry,dropgeometry。
5.2 坐标系统操作:st_srid(),st_setsrid()。
查看坐标系统
selectst_asewkt(st_setsrid(ST_POINT(114,40),4326))
select st_srid(the_geom) fromroad_noded;
更改坐标系统:
update road_noded set the_geom= st_geomfromtext(ST_AsText(the_geom),4326)
参考:http://www.jb51.cc/article/p-mghqxfgz-qm.html
5.3 几何关系:st_intersects等。
5.4 测量:st_distance,st_length,st_area等。
5.5 几何操作:st_x,st_y,st_startpoint,st_endpoint等。
5.6 运行算:<->,&&,&&&
参考:帮助文档。
http://www.cnblogs.com/LCGIS/archive/2013/03/12/2954898.html
5.6.1 距离
5.6.1.1 几何体距离:<->。
selectst_point(-2,1)<->st_point(-3,2);
5.6.1.2 边框距离:<#>。
5.6.2 相交与方位:边框的关系
参考:http://www.cnblogs.com/LCGIS/archive/2013/03/12/2954898.html
5.6.2.1 相交:&&,&&&(3D)
5.6.2.2 左侧不相交:<<
5.6.2.3 右侧不相交:>>
5.6.2.4 下方不相交:<<|
5.6.2.5 上方不相交:>>|
5.6.2.6 相交或左侧:&<
5.6.2.7 相交或右侧:&>
5.6.2.8 相交或下方:&<|
5.6.2.9 相交或上方:|&>
5.6.3 相同性:边框关系
5.6.3.1 相同:=
5.6.3.2 边框相同:~=
5.6.4 包含关系:边框关系
5.6.4.1 包含于:@
5.6.4.2 包含:~
5.7 应用
5.7.1 根据坐标查询节点:使用-进行距离测算,取最近距离的点。
SELECT id::integer FROMroad_noded_vertices_pgr
ORDER BY the_geom <->ST_GeometryFromText('POINT(-3 1.6)',4326) LIMIT 1;
6方法:查看postgis数据
6.1 从pg中查看
6.1.1 查看属性数据
可以直接查看属性表;
6.1.2 查看空间数据
可以使用postgis中内置的函数查看空间数据,如asewkt(geom);
注意:表名必须使用双引号引起来;
6.2 从qgis中可以查看包括地图数据和属性数据的导入的所有内容;
可以通过AddPostgis Layer或者Add vectorlayer两种方式查看postgis的数据,推荐使用addpostlayer,这种方式不用进行编码等的选项选择,可以直接读取源数据库的内容,更加容易操作;
6.2.1 Add Postgis Layer
点击add postgislayer 打开添加数据对话框
6.2.1.1 连接数据库
首先配置postgis数据库参数,配置完成后,点击test connect出现connection to db was successful提示,表明连接成功.
6.2.1.2 点击connect,出现所有的数据表,选择需要加载的数据表,点击add,就会显示在qgis中;
6.2.2 Add Vector Layer
点击add postgislayer 打开添加数据对话框
6.2.2.1 配置数据来源
选择数据源类型为数据库,编码为gb2312,数据库类型为postgresql;
6.2.2.2 连接数据库
首先配置postgis数据库参数,点击test connect出现连接成功的提示,表明连接成功.
6.2.2.3 选择数据
点击Open会出现所有的图层,选择需要的图层,点击OK就会加载到qgis中;
7方法:索引postgis数据
7.1 索引类型
postgresql中存在三种形式的索引,BTrees,RTrees,GiST;
7.1.1 BTrees:适合于连续的数据,如时间,数字和字符;
7.1.2 RTrees:将数据分块存储,有些gis以此种方式索引数据,不过postgresql数据库的rtrees方法没有gist方法优秀;
7.1.3 GiST:Generalized Search Tree,适用范围广,并且postgis将RTrees建立在GiST之上,适合于建立gis索引;
7.1.4 注意
7.1.4.1 RTrees不允许使用NULL,也不允许超过8K的对象;而GiST不受限制.
7.1.4.2 使用索引时应注意经常进行Vacuum Analysis进行数据统计与整理,可以删除系统中无效的统计与索引文件,加快查询速度;
7.2 创建索引
7.2.1 语法
create index indexName on tabname using indextype(indexColumn);
7.2.2 方法
选择要创建索引的表,选择索引类型(这里应使用gist),索引字段(空间字段),就可以创建索引了;
也可以直接使用sql语句创建
CREATE INDEX geom_gist
ONpoint_airdrome_fly USING gist (the_geom);
7.3 注意:默认已经建立Gist索引
系统在加载时,默认为每个图层的空间数据都建立了gist索引,所以一般情况不需要特别再创建索引;
8 方法:部署pg数据库
在没有postgresql数据库的机器部署程序时,如果无法正确连接到所需的数据库,会造成程序假死;这时应对数据库进行配置,以防止无法连接数据库而造成问题;
在postgresql数据中,有三个配置文件,其中pg_hba.conf文件是用于客户端连接权限设定的.如果文件中不包含对客户端的设置,则无法连接;
8.1 配置pg_hba.conf文件:
在安装目录->data目录下,可以找到pg_hba.conf文件;
用记事本打开,并添加客户端地址及设置;
保存后,用配置编辑器打开;
9方法:将分析数据保存为新表
目标:将postgis的分析数据保存为新表,并提取出结果数据为不同的几何类型。
方法:
查询保存为新表参见:..\postgresql\postgresql.docx中相关章节。
提取分析结果使用,st_CollectionExtract(collection,type),分析结果如果为collecion类型,可以将其抽取不指定的类,1=point,2=linestring,3=polygon。
参考:http://postgis.net/docs/ST_CollectionExtract.html
示例:
SELECT
st_astext(ST_CollectionExtract(st_split(r.geom,s.the_geom),2)),ST_CollectionExtract(st_split(r.geom,2)
into stops3
FROM
public.road r,
public.roadwithstop2 s ;