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 ;
原文链接：https://www.f2er.com/postgresql/195095.html