如何导入PostgreSQL数据库数据
说起数据库,大家耳熟能详的商业数据库产品当推Oracle、微软的SqlServer和IBM的
DB2等,而开源数据库中则有两大产品MySQL和PostgreSQL。
PostgreSQL 是一种对象-关系型数据库管理系统(ORDBMS),也是目前功能最强大、
特性最丰富和最复杂的自由软件数据库系统。它起源于伯克利(BSD)的数据库研究计划,
目前是最重要的开源数据库产品开发项目之一,有着非常广泛的用户。
PostgreSQL 可以说是最富特色的自由数据库管理系统,也有人认为可以是最强大的自由
软件数据库管理系统。PostgreSQL 是唯一支持事务、子查询、多版本并行控制系统、数据完
整性检查等特性的唯一的一种自由软件的数据库管理系统。能在多平台下---包括Linux、
FreeBSD和Windows等---运行,并且支持多语言的开发。
在两大开源数据库产品的对比中,一般认为MySQL速度更快,所以得到更为广泛的使
用;而PostgreSQL性能更为先进,PostgreSQL 提供很多 MySQL 目前所不支持的特性,比
如触发器、视图、存储过程等等,在记录数超千万之后性能表现尤其出色。
当前的最新版本是PostgreSQL 823,官方网站是:
wwwpostgresqlorg
12 什么是PostGIS
PostGIS在对象关系型数据库PostgreSQL上增加了存储管理空间数据的能力,相当于
Oracle的spatial部分。PostGIS最大的特点是符合并且实现了OpenGIS的一些规范,是最著
名的开源GIS数据库。
当前的最新版本是PostGIS 121,官方网站是:
wwwpostgisorg
二 PostgreSQL和PostGIS的安装
21 在windows下安装。
新版本的PostgreSQL在其安装程序中集成了PostGIS,只需要在安装过程中选中
PostGIS和pgsql项就可以了。
22 在Debian下安装PostGIS
# apt-get install postgresql-81 postgresql-81-postgis
当前Etch中的版本:PostgreSQL是817,而PostGIS是116,虽然不是最新的版本,
但已经足够了。
还需要做的工作是:
1) 创建一个专门用于PostGIS的数据库并为专用数据库导入PostGIS支持:
# su – postgres
$ cd /usr/share/postgresql-81-postgis
$ createdb wen1
$ createlang plpgsql wen1
$ psql -d wen1 -f lwpostgissql
$ psql -d wen1 -f spatial_ref_syssql
2) 创建一个专门的用户,并把相应的数据库和数据表的属主设置为该用户:
# su – postgres
$ psql
$ create user wen1 password ‘123456’;
$ alter database wen1 owner to wen1;
$ alter table spatial_ref_sys owner to wen1;
$ alter table geometry_columns owner to wen1 ;
3) 修改PostgreSQL配置文件以便可以远程使用。
修改PostgreSQL的配置文件/etc/postgresql/81/main/Postgresqlconf,将其中的一句:
listen_address=’localhost’
前面的注释去掉,并把’localhost’该为’’。
修改Postgresql的配置文件/etc/postgresql/81/main/pg_hbaconf,在文件后面加一句:
host all all 19216800/24 password
这句的意思是:同网络中1921680的机器可以以密码的形式使用所有的数据库。更具
体的参数意义直接看该配置文件中的注释就可以了。
这里一定要配置正确,否则无法在远程连接PostgreSQL数据库。
23 源码安装PostgreSQL和PostGIS
参阅以前写的老文章。
三 PostGIS的使用
要使用PostGIS,需要两方面的预备知识,一是要熟悉基本的SQL语法;二是熟悉
PostgreSQL数据库的基本使用。
(一) 快速入门
我们使用前面创建的数据库wen2,用户为wen1,现在创建一个包含三个点的数据库
test1:
首先在Etch下以wen1登陆,然后打开wen2数据库:
$ psql -d wen2 ;
再在PostgreSQL的控制台下输入以下命令:
wen2=> create table test1 (myID int4, pt geometry, myName
varchar );
wen2=> insert into test1 values (1, 'POINT(0 0)', 'beijing'
);
wen2=> insert into test1 values (2, 'POINT(315 6087)',
'shanghai' );
wen2=> insert into test1 values (3, 'POINT(1077 85902)',
'tianjin' );
这样我们利用PostGIS创建了一个包含三个点的GIS数据表。
为了能在QGIS中打开这一图层,我们还需要为数据表创建一个主键:
wen2=>alter table test1 add primary key (myID);
可以看到PostGIS的使用其实就相当于使用一个经过扩展的SQL语法,上述语句熟悉
SQL语法的人一看都很熟悉,都是普通的SQL语句,不同的只不过是增加了PostGIS特殊的
geometry数据类型。
你可以再试试这些SQL语句:
select from test1;
select myID,AsText(pt) from test1;
select Distance(pt, 'POINT(0 0)') from test1;
(二) PostGIS的Geometry数据类型
Geometry可以说是PostGIS最重要的一个概念,是“几何体”的意思,由于PostGIS很
好地遵守OGC的”Simple Feature for Specification for
SQL”规范,目前支持的几何体类型包
含其实例有:
POINT(1 1)
MULTIPOINT(1 1, 3 4, -1 3)
LINESTRING(1 1, 2 2, 3 4)
POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))
MULTIPOLYGON((0 0, 0 1, 1 1, 1 0, 0 0), (5 5, 5 6, 6 6, 6 5, 5
5))
MULTILINESTRING((1 1, 2 2, 3 4),(2 2, 3 3, 4 5))
而geometry具体表现又有两种形式,一种叫做WKT(Well Known Text)形式,如上面的
例子。或者使用如下SQL语句浏览:
select AsText(pt) from test1;
另一种叫做“Canonical Form”形式,看上去是一串古怪的数字,其实是一种增强的十六
进制编码,使用如下SQL语句就可以浏览了:
select pt from test1;
(三) 读写PostGIS数据
建设好PostGIS数据库之后,我们现在需要进行读写GIS数据了,包括把GIS写入到
PostGIS数据库中以及在应用程序中使用PostGIS数据库的数据。读写PostGIS目前主要有以
下四种方式:
31 使用psql语言
Psql语言是PostgreSQL内嵌的一个命令行工具,其语法基本上和标准的SQL语法是一
致的,可以使用Psql工具,结合标准SQL语法和一些PostGIS的扩展对PostGIS数据库进行
读写操作。
具体例子如上面“快速入门”中的例子。
这种方式功能强大,但全部需要手工操作,烦琐且繁重。
32 使用一些小工具
有两个很有用的小的转换工具,一是shp2pg;一是ogr2ogr
321 shp2pgsql和pgsql2shp
shp2pgsql和pgsql2shp是PostGIS自身携带的一对在Shape文件和PostGIS数据库之间进
行转换的工具,在Debian中安装好PostGIS之后,这两个程序已经位于可执行文件的搜索路
径上,因此可以在系统中任何位置使用。
假如当前目录下有完整的chinashp文件(应该有三个同名不同扩展名的文件),需要把其
中数据输入到上述的wen2数据库中的数据表china_postgis中,具体操作为(操作用户为
wen1):
$ shp2pgsql china china_postgis > tmpsql
$ psql -d wen2 -f roadssql
这个工具很好用,缺点在于使用范围有限,只针对Shape文件格式。
322 ogr2ogr
PostGIS本身的shp2pg工具只把shape文件转换到PostGIS 数据库中,那如何把其他的
GIS数据加入呢?比如说MapInfo的mid格式,ESRI的e00格式呢?这就要使用ogr这个工
具了。
Ogr目前是gdal的一个组成部分,那什么是gdal呢?其官方主页(http://wwwgdalorg)上
如此介绍:
GDAL is a translator library for raster geospatial data formats
that is released under an X/MIT
style Open Source license by the Open Source Geospatial
Foundation As a library, it presents a
single abstract data model to the calling application for all
supported formats It also comes with a
variety of useful commandline utilties for data translation and
processing
简单地说,Gdal是一个各种Gis数据格式的转换软件库,ogr则是转换矢量GIS数据的
软件库。
目前ogr能够支持的数据格式包括:
Arc/Info Binary Coverage、DWG、ESRI Personal
GeoDatabase、ArcSDE、ESRI
Shapefile、GML、GRASS、Mapinfo File、Microstation DGN、ODBC、Oracle
Spatial和
PostgreSQL等。应该说,这就基本包括了我们平常用到的所有矢量型GIS文件格式了。
Gdal支持的栅格数据格式参阅http: //wwwgdalorg/formats_listhtml
下面我们从源码编译安装gdal---因为我在Debian下使用apt方式安装的gdal并不支持
postgresql数据库,其实更为简便的方法也许是去gdalorg下载一个Fwtools的工具包,可以
直接运行不用繁琐的编译,既有Windows的,也有Linux下用的,只不过这个包有些大。
1) 下载最新的gdal-132
2) 解压
3) /configure --prefix=/opt/gdal
--with-pg=/opt/pg/bin/pg_config --without-ogdi
这里我习惯把软件安装在/opt目录下。
--with-pg参数很重要,使ogr可以支持PostGIS,后面的参数是我的PostGIS安装的地方。
我在这里编译很多次失败,经过仔细查找,发现问题出在ogdi上,所以暂时我只好使
它不支持ogdi。
4) make
5) make install
下面是使用过程,假设现在我要把一个raimid文件导入到数据库data1中:
$ ogr2ogr –f Postgresql PG:dbname=data1 raimid
注意,f参数后面跟的是导入的数据类型,最后那个文件才是要导出的文件。PG后面说
明的是数据库的名字,需要是已经创建好的数据库,而数据表则由程序自动创建。还要特别
注意权限问题,以上命令我是由postgres用户执行的。
还有一个要注意的是主键问题。一般情况下,你使用shp2pg或者 ogr2ogr向postgis中
增加了Gis数据后,在GIS客户端添加postgis图层的时候,常常会因为数据表没有定义主键
而出错,解决的办法是,在服务器上使用psql或者其他sql工具,使用命令:
alter table table_name add primary key (column_name);
另外我们发现ogr竟然是不支持最常见的GIS格式e00格式,好象是因为版权方面的原
因吧。解决的方法就是去sfnet查找相关工具。我在上面就找到一个e002pg工具,支持将
e00文件导入到postGIS数据库中。
33 在其他GIS软件中读写PostGIS数据
比如在QGIS中,能够打开PostGIS图层,还有SPIT插件可以把Shape文件输入到
PostGIS数据库中。
其他GIS软件如uDig,Grass等,甚至连ArcInfo都支持或部分支持读写PostGIS数据。
34 利用接口在应用程序中读写PostGIS数据
广大的开源GIS程序员几乎为每一种程序设计语言设计好了读写PostGIS的接口,如利
用PostgreSQL的JDBC库,可以使用Java语言在程序中读写PostGIS数据;利用libpq库,
可以使用C语言读写PostGIS数据。
(四) 使用PostGIS函数
41 PostGIS函数
要能熟练使用PostGIS,掌握PostGIS的所有函数是关键。通过掌握这些PostGIS函数,
我们能够高效、稳定地处理各种地理数据。由于PostGIS的函数设计时都遵守OpenGIS规范,
我们也可以把这些PostGIS叫做OpenGIS函数。
42 PostGIS函数的分类
PostGIS函数大致可以分为以下四类
1) 字段处理函数
这类函数当前一共有3个,分别是:
AddGeometryColumn(var1,var2,var3,var4,var5,var6):为已有的数据表增加一个地理几何
数据字段。Var1代表数据表的模式(schema)的名字,一般是public,也可以省略,则使用当
前的缺省模式;var2是已有的数据表的名字;var3是新的地理数据字段的名字;var4是
SRID值,不确定的话就取-1吧;var5是地理数据的类型,可以是POINT等;var6是指该几
何数据是二维还是三维数据。
前面的SQL语句 create table test1 (myID int4, pt geometry, myName
varchar )更规范的写
法为:
create table test1 (myID int4, myName varchar );
select AddGeometryColumn('test1','pt',-1,'GEOMETRY',2);
DropGeometryColumn函数显然是删除一个地理数据字段的;
SetSRID函数显然是设置SRID值的。
2) 几何关系函数
这类函数目前共有10个,分别是:
Distance Equals Disjoint Intersects Touches Crosses Within
Overlaps Contains Relate
3) 几何分析函数
这类函数目前共有12个,分别是:Centroid Area Lenth PointOnSurface Boundary
Buffer
ConvexHull Intersection SymDifference Difference GeomUnion
MemGeomUnion
4) 读写函数
这类函数很多,主要是用于在各种数据类型之间的转换,尤其是在于Geometry数据类
型与其他如字符型等数据类型之间的转换,函数名如AsText、GeomFromText等,其作用是
显然的。
43 PostGIS函数使用示例。
假设在我们的wen2数据库中,有两张表,一张为guojia,是从“国家基础地理数据”
网站下载的国界数据表,数据类型为LINE;二为shengqu_polygon,也从同一个网站下载,
地理数据类型为多边形。
1) 查询我国边境线的长度:
wen1=> select sum(length(the_geom)) as lenth from guojie;
2) 查询我国面积最大的省区名字:
wen1=> select name area(the_geom) as myarea
from shengqu_polygon
order by myarea DESC
LIMIT 1;
(五) 使用PostGIS扩展函数
除了上述遵循OpenGIS的函数之外,PostGIS还自行扩展了一些当前OpenGIS规范之外
的函数,主要包括以下几类:
51 管理类函数
扩展的管理类函数主要包括一些软件版本查询函数,如
postgis_version()、postgis_geos_version()、postgis_proj_version()函数等,分别查询当前的
PostGIS的版本及其使用的Geos和Proj库的版本。
52 数据类型的输入输出函数
除了OpenGIS定义的地理数据类型之外,PostGIS还对数据类型进行了扩展,这种扩展
主要是两方面的扩展,一是把二维的数据向三维和四维扩展;二就是在WKT和WKB数据
类型基础上扩展出EWKT和EWKB数据类型。
PostGIS提供了在这些地理数据类型和常用数据类型如字符型、浮点型数据之间进行转
换的函数。
53 量算函数
如length3d函数是对length2d函数的扩展。
54 几何操作函数
如addBBox(geometry)函数给所给的几何体加上一个边框。
如simplify(geometry,tolerance)函数可以对折线和多边形利用Douglas-Peuker算法进行一
些节点进行删除,从而使表现的图形更简单而清晰,在网络传输数据时具有更高的效率。
55 操作符号
56 其他扩展函数
(六) 建立PostGIS索引
当数据库的记录增大的时候,如果没有建立索引的话,操作的效率就显著下降。
POstGIS建议当记录数超过几千的时候就应该建立索引,而GIS数据库一般都是海量数据,
所以对PostGIS而言,索引就非常重要。
Shapfile文件为ESRI公司的文件存储格式,并且得到了业界广泛的支持。Shapfile格式是一种简单的,用非拓朴关系的形式存储几何位置和地
理特征的属性信息的格式。虽然GeoServer采用Shapfile文件可以快速的创建网上地图服务,但它的缺点确很明显:
1、Shapefile只支持一个图层,在实际中没有意义。
2、直接保用SHP文件不安全,Shapfile文件很容易被病毒或其他原因误删除。
3、GeoServer中用Shapfile文件作数据源的效率是很低的。
4、Shapfile中的汉字GeoServer不能解析,会出现乱码。
5、数据库可以方便的对地理信息进行查询。
用PostGIS管理空间数据
PostGIS支持GIST空间索引(附录1)、规范窗体,能很大的提高处理效率。
OGC格式只提供二维的几何体,且相关联的SRID从未深入的用于输入输出请求,PostGIS支持OpenGIS组织"Simple
Features for
SQL"规范指定的所有GIS对象和函数,并进行了扩展,格式是EWKB、EWKT,其中增加了对3DZ,3DM和4D
坐标系的支持(当然三维、四维数据的OGC标准还未完全制定),深入引进了SRID信息。
空间数据表结构:PostGIS中存在两个必需的元数据表格, SPATIAL_REF_SYS(空间参考表格) 和
GEOMETRY_COLUMNS(几何体属性列),两个表用于存储空间数据库使用的坐标系统数字ID和文本描述。
PostGIS的shp2pgsql命令可以将Shapfile直接导入到数据库中也可以导出为SQL文件,推荐先导出为SQL文件再将此文件在SQL运行窗口中执行可将数据导入数据库。
Shapfile到SQL语句:
shp2pgsql 路径shp数据文件名 新建的数据表名 >
路径SQL文件名sql
Shapfile直接入库:
shp2pgsql -c 路径shp数据文件名 新建的数据表名 数据库名|psql -d 数据库名
举例说明:
如将一Shapfile文件“c:roadshp”导入到数据表“road”中,数据库为“sjzmap”。
1、运行“命令提示符”。
2、切换至PostgreSQL数据库安装目录中的bin目录下。
3、执行此目录下的shp2pgsql命令:“shp2pgsql c:roadshp road >
c:roadsql”。
4、如将此文件直接导入数据库(不推荐):“shp2pgsql -c c:roadshp road
sjzmap | psql -d sjzmap”。
5、使用pgAdmin3
选择数据库,再导入表。
把防火墙关掉
用PUTTY连接你的服务器,后输入如下命令:
psql -d test 回车
alter role role2 with login password 'role2'; 回车
然后试试用pgadmin连接一下
天互数据 为您解答,希望能帮到你
1安装PostgreSQL
首先根据你的服务器架构添加PostgreSQL库:
使用以下命令来更新库:
yum update
使用以下命令来安装PostgreSQL:
yum install postgresql93-server postgresql93-contrib
使用以下命令来初始化PostgreSQL数据库:
在CentOS 6x 系统中:
service postgresql-93 initdb
在CentOS 7系统中:
/usr/pgsql-93/bin/postgresql93-setup initdb
然后启动PostgreSQL服务并使之开机自启:
在CentOS 6x 系统中:
service postgresql-93 start
chkconfig postgresql-93 on
在CentOS 7系统中:
systemctl enable postgresql-93
systemctl start postgresql-93
2调整Iptables/Firewall
接下来调整防火墙出站规则:
在CentOS 6x系统中:
vi /etc/sysconfig/iptables
并添加以下行
-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT
退出并保存文件。重启iptables服务:
service iptables restart
在CentOS系统中:
firewall-cmd --permanent –add-port=5432/tcp
firewall-cmd --permanent –add-port=80/tcp
firewall-cmd --reload
3访问PostgreSQL常用的命令提示符
默认情况下数据库名和用户名都是postgres。切换至用户以执行相关操作:
su – postgres
输入以下命令登陆:
psql
会有以下样例输出:
psql (935)
Type "help" for help
Postgres=#
可通过输入\q退出postgresql返回命令终端:
4设置用户密码
登陆至postgres命令提示符界面
su – postgres
psql
然后使用命令设置密码
postgres=# \password postgres
Enter new password:
Enter it again:
postgres=# \q
输入命令以建立PostgreSQL系统管理工具
postgres=# CREATE EXTENSION adminpack;
CREATE EXTENSION
5创建用户和数据库
例如:用户名:senthil 密码:centos 数据库名:mydb
转到postgres用户
su – postgres
创建用户senthil
$ createuser senthil
创建数据库
$ createdb mydb
现在登陆至psql提示符界面,为用户senthil设置密码及授权对数据库mydb的访问:
$ psql
psql (935)
Type "help" for help
postgres=# alter user senthil with encrypted password 'centos';
ALTER ROLE
postgres=# grant all privileges on database mydb to senthil;
GRANT
postgres=#
6删除用户和数据库
首先转到postgres界面
su – postgres
输入命令
$ dropdb <database-name>
删除用户名可输入
$ dropuser <user-name>
7配置PostgreSQL-MD5认证
MD5认证需要客户端提供一个MD5-encrypted 密码以便身份验证。你需要编辑 /var/lib/pgsql/93/data/pg_hbaconf文件:
vi /var/lib/pgsql/93/data/pg_hbaconf
添加或修改的行如下:
[]
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127001/32 md5
host all all 19216810/24 md5
# IPv6 local connections:
host all all ::1/128 md5
[]
重启postgresql服务以应用更改
在CentOS 6x系统中
service postgresql-93 restart
在CentOS 7系统中
systemctl restart postgresql-93
8配置PostgreSQL-Configure TCP/IP
默认情况下,TCP/IP连接是不可行的,所以其他计算机用户不能连接到postgresql。编辑文件 /var/lib/pgsql/93/data/postgresqlconf可以允许连接:
vi /var/lib/pgsql/93/data/postgresqlconf
找到下面的行:
[]
#listen_addresses = 'localhost’
[]
#port = 5432
[]
把两行都取消并设置为你的postgresql服务器IP地址或设置为“”监听所有客户端。如下所示:
listen_addresses = ''
port = 5432
重启以应用更改
在CentOS6x系统中:
/etc/initd/postgresql-93 restart
在CentOS7系统中:
systemctl restart postgresql-93
9使用phpPgAdmin管理PostgreSQL
phpPgAdmin是使用PHP编写的基于web的管理工具,用于管理PostgreSQL。它只适用与PostgreSQL RPM库。
0条评论