如何导入PostgreSQL数据库数据

如何导入PostgreSQL数据库数据,第1张

说起数据,大家耳熟能详的商业数据库产品当推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库。

DABAN RP主题是一个优秀的主题,极致后台体验,无插件,集成会员系统
网站模板库 » 如何导入PostgreSQL数据库数据

0条评论

发表评论

提供最优质的资源集合

立即查看 了解详情