xiaobaoqiu Blog

Think More, Code Less

PostgreSQL

1.什么是PostgreSQL

The World’s most advanced open source database.

PostgreSQL is a powerful, open source object-relational database system.

主页：http://www.postgresql.org/

支持的系统：Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows.

功能：has full support for foreign keys, joins, views, triggers, and stored procedures.

数据类型：It includes most SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video.此外，用户可以创建自定义数据类型，通常通过PostgreSQL的GiST机制，它们也能被很好得索引，比如PostGIS地理信息系统的数据类型.

语言支持：It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation.

用户手册：

1
2

http://www.postgresql.org/docs/manuals/
http://www.highgo.com.cn/docs/docs90cn/index.html

大小限制：

Maximum Database Size Unlimited

Maximum Table Size 32 TB

Maximum Row Size 1.6 TB

Maximum Field Size 1 GB

Maximum Rows per Table Unlimited

Maximum Columns per Table 250 - 1600 depending on column types

Maximum Indexes per Table Unlimited

扩展库：

(1)地理数据对象：PostGIS GPL

(2)全文检索：通过Tsearch2或OpenFTS

人物：老何

2.PostgreSQL历史

参见wiki：

1

http://zh.wikipedia.org/wiki/PostgreSQL

3.PostgreSQL VS MySQL

MySQL：Slashdot、Twitter、Facebook、Wikipedia

PostgreSQL：Yahoo、Disqus

4.PostgreSQL安装

1. 命令行安装（Ubuntu为例子）

(1)安装PostgreSQL客户端
1

sudo apt-get install postgresql-client
(2)安装PostgreSQL服务器
1

sudo apt-get install postgresql

正常情况下，安装完成后，PostgreSQL服务器会自动在本机的5432端口开启。

(3)安装图形管理界面：
1

sudo apt-get install pgadmin3
(4)安装PostGis：
1

sudo apt-get install postgresql-9.1-postgis

本机安装的时候，postgresql-9.1-postgis最终的postgis版本为1.5

2. 可视化安装

(1)安装包：
1

http://www.enterprisedb.com/products-services-training/pgdownload
(2)安装PostgreSQL

下载完成之后，直接安装（Ubuntu为例）：

1

xiaobaoqiu@xiaobaoqiu:~/Download$ sudo ./postgresql-9.3.4-3-linux-x64.run
(3)安装PostGis

在安装快结束的时候要将StackBuilder那个勾打上，可以用来下载安装扩展工具包。Finish之后进入StackBuiler选择本地的PostgreSQL，点next，之后在展开Spatial Extensions下勾选PostGIS，则会下载安装PostGis。

(4)设置环境变量
1

/etc/profile 加上 /opt/PostgreSQL/9.3/bin

5.PostgreSQL简单使用

1. 查看版本
1
2

xiaobaoqiu@xiaobaoqiu:/var/run/postgresql$ psql --version
psql (PostgreSQL) 9.3.4

初次安装后，默认生成一个名为postgres的数据库和一个名为postgres的数据库用户。这里需要注意的是，同时还生成了一个名为postgres的Linux系统用户。

2. 登录 psql -h host -U user -d database_name -p port

PostgreSQL默认端口为5432,-d参数指定数据库名称：

1
2
3
4

xiaobaoqiu@xiaobaoqiu:/var/run/postgresql$ psql -h localhost -U postgres
Password for user postgres: 
psql.bin (9.3.4)
Type "help" for help.
3. 退出
1

\q
4. 所有用户
1
2
3
4
5

postgis_template=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}
4. 连接信息
1
2

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" at port "5432".
4. show databases
1
2
3
4
5

hotel=# \l
                                      List of databases
        Name         |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
---------------------+----------+----------+------------+------------+-----------------------
 fb_stat             | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
5. switch database
1
2

postgres=# \c postgis_template
You are now connected to database "postgis_template" as user "postgres".
6. show tables

\d 显示所有表

1
2
3
4
5
6
7
8
9
10
11

postgis_template=# \d
                 List of relations
  Schema  |       Name        |   Type   |  Owner   
----------+-------------------+----------+----------
 public   | geography_columns | view     | postgres
 public   | geometry_columns  | view     | postgres
 public   | spatial_ref_sys   | table    | postgres
 topology | layer             | table    | postgres
 topology | topology          | table    | postgres
 topology | topology_id_seq   | sequence | postgres
(6 rows)

\d table_name

1
2
3
4
5
6
7
8
9
10
11

postgis_template=# \d geography_columns
     View "public.geography_columns"
       Column       |  Type   | Modifiers 
--------------------+---------+-----------
 f_table_catalog    | name    | 
 f_table_schema     | name    | 
 f_table_name       | name    | 
 f_geography_column | name    | 
 coord_dimension    | integer | 
 srid               | integer | 
 type               | text    |
7. 数据库操作

基本的数据库操作，就是使用一般的SQL语言:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

# 创建表 
CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);

# 插入数据 
INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');

# 选择记录 
SELECT * FROM user_tbl;

# 更新数据 
UPDATE user_tbl set name = '李四' WHERE name = '张三';

# 删除记录 
DELETE FROM user_tbl WHERE name = '李四' ;

# 添加列
ALTER TABLE user_tbl ADD email VARCHAR(40);

# 更新表结构 
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;

# 更名列 
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;

# 删除列 
ALTER TABLE user_tbl DROP COLUMN email;

# 表格更名 
ALTER TABLE user_tbl RENAME TO backup_tbl;

# 删除表格 
DROP TABLE IF EXISTS backup_tbl;
8. PostgreSQL Client支持的一些命令（直接在shell运行）：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

clusterdb -- cluster a PostgreSQL database
createdb -- create a new PostgreSQL database
createlang -- install a PostgreSQL procedural language
createuser -- define a new PostgreSQL user account
dropdb -- remove a PostgreSQL database
droplang -- remove a PostgreSQL procedural language
dropuser -- remove a PostgreSQL user account
ecpg -- embedded SQL C preprocessor
pg_basebackup -- take a base backup of a PostgreSQL cluster
pg_config -- retrieve information about the installed version of PostgreSQL
pg_dump --  extract a PostgreSQL database into a script file or other archive file
pg_dumpall -- extract a PostgreSQL database cluster into a script file
pg_receivexlog -- streams transaction logs from a PostgreSQL cluster
pg_restore --  restore a PostgreSQL database from an archive file created by pg_dump
psql --  PostgreSQL interactive terminal
reindexdb -- reindex a PostgreSQL database
vacuumdb -- garbage-collect and analyze a PostgreSQL database

6.Client

1.JDBC Driver
1

http://jdbc.postgresql.org/
2.maven package:
1
2
3
4
5

<!--postgresql-->
<dependency>
    <groupId>postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>

7.PostGis

PostGIS是一个重要的开源GIS基础软件，因为目前它是为数不多的开源空间数据库存储方案之一，它为PostgreSQL提供了存储空间地理数据的支持，使PostgreSQL成为了一个空间数据库，能够进行空间数据管理、数量测量与几何拓扑分析。

支持空间对象模型：点、折线、多边形、多点、多折线、多多边形与几何集合。

空间分析功能：如求交、求并、求差、缓冲区等;

空间索引：利用R-tree与GiST实现了空间索引，大大加快了正交查询的速度;

存储：Well-known text与well-known binary，前者是一种用文本表示空间对象的注记方法，后者是一种用二进制流表示空间对象的存储方法。PostGIS使用中两种格式在数据库中存储空间对象。

手册：

1
2

http://postgis.net/docs/manual-2.0/
http://workshops.boundlessgeo.com/postgis-intro/

Geography：latitude/longitude，球坐标

常用4326坐标系统，4326坐标系统指的是WGS 84（World Geodetic System，世界坐标系）标准提供地球的球体参照面，是全球定位系统（Global Positioning System，简称 GPS）使用的空间参照系。WGS 84的坐标原点是地球的质心，精度可达到 ±1 米。WGS 84坐标单位是度，其中，第一个坐标是经度，范围是 -180 到 180；第二个坐标是纬度，范围是 -90 到 90。

1
2
3
4
5
6
7
8
9
10
11
12
13
14

Los Angeles: POINT(-118.4079 33.9434)
Paris: POINT(2.3490 48.8533)
#距离
SELECT ST_Distance(
  ST_GeometryFromText('POINT(-118.4079 33.9434)', 4326), -- Los Angeles (LAX)
  ST_GeometryFromText('POINT(2.5559 49.0083)', 4326)     -- Paris (CDG)
  );
121.898285970107度
#距离米
SELECT ST_Distance(
  ST_GeographyFromText('POINT(-118.4079 33.9434)'), -- Los Angeles (LAX)
  ST_GeographyFromText('POINT(2.5559 49.0083)')     -- Paris (CDG)
  );
9124665.26917268米

Geometry ：几何坐标

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

ST_AsText

#点
ST_X ST_Y
#线
ST_Length 线长度
ST_StartPoint 起点
ST_EndPoint 终点
ST_NPoints 点数
#多边形
ST_Area(geometry)面积
ST_NRings(geometry)环个数
ST_ExteriorRing(geometry) 外接环
ST_InteriorRingN(geometry,n) 内接环
ST_Perimeter(geometry) 所有环的长度
#集合
MultiPoint, a collection of points
MultiLineString, a collection of linestrings
MultiPolygon, a collection of polygons
GeometryCollection, a heterogeneous collection of any geometry (including other collections)

实用case： gpoint表示坐标：

1

 gpoint       | geography(Point,4326)       |

距离坐标(117.28243891065 39.096105298613)最近的10个地标数据（在1km以内搜索）：

1
2
3
4

SELECT id, name, city_code, st_astext(gpoint) FROM poi
WHERE ST_DWithin(GeomFromText('POINT(117.28243891065 39.096105298613)',4326), gpoint, 1000)
ORDER BY ST_Distance(GeomFromText('POINT(117.28243891065 39.096105298613)',4326), gpoint)
LIMIT 10;