postgresql + postgis 空间数据库

阳光穿透心脏的1/2处 2022-03-31 07:30 459阅读 0赞

记录一些常用语句，随时会增加

postgresql9.4, postgis2.1

## 创建postgis数据库 ##

1.在postgresql的bin目录下打开命令行，输入：

createdb -U postgres -E UTF8 osm

其中osm是数据库的名称，postgres是数据库的用户名。执行完成后我们打开pgAdmin III客户端刷新，就可以看到刚才创建的数据库。

2.createlang -U postgres plpgsql osm

一般这一步都不需要，默认已经安装上去了。

3.添加PostGIS功能到数据库，执行如下命令：

psql -U postgres -d osm -f “你PostgreSQL安装路径/share/contrib/postgis-2.1/postgis.sql”

控制台会打印如下一些命令：

!\[在这里插入图片描述\](https://img-blog.csdn.net/20160403192317583?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center)

4.往spatial\_ref\_sys表中输入你需要使用的空间坐标系，如epsg4326：

INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 4326, 'epsg', 4326, '+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs ', 'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]');

不同坐标系的insert语句可以在这个网站找到 [http://spatialreference.org/ref/epsg/4326/][http_spatialreference.org_ref_epsg_4326]

!\[在这里插入图片描述\](https://img-blog.csdn.net/20160403193252461?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center)

点击PostGIS spatial\_ref\_sys INSERT statement链接可以得到insert语句（这里有一个问题是主键是94326，在geoserver中不能自动识别，所以手动改成了4326）  
!\[在这里插入图片描述\](https://img-blog.csdn.net/20160403193424227?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center)

## 创建数据表 ##

CREATE TABLE stations ( id varchar(12) primary key, lonlat geometry(Point,4326), area float8, polygon geometry(Polygon,4326) );

## 插入数据 ##

INSERT INTO stations (id, lonlat, area, polygon) VALUES ('A12345',ST_GeomFromText('POINT(121.50 31.22)',4326),22222.1234,ST_GeomFromText('POLYGON ((121.71436091670316 31.38080337674083, 121.70173615072089 31.388605486784197, 121.70751628677645 31.4199972067024, 121.71832867120243 31.437168981489133, 121.73193985623796 31.37984624401758, 121.71436091670316 31.38080337674083))',4326));

## 查询数据 ##

直接select \* 查询的话，lonlat和polygon的坐标是无法阅读的16进制格式。

要以 WKT 文本显示，使用 ST\_AsText(the\_geom) 或 ST\_AsEwkt(the\_geom) 函数。

也可以使用 ST\_X(the\_geom) 和 ST\_Y(the\_geom) 显示一个维度的坐标。

select id, area, ST_AsText(lonlat), ST_AsEwkt(polygon) from stations

!\[在这里插入图片描述\](https://img-blog.csdn.net/20160403204447928?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center)

## 打开postgresql远程连接 ##

!\[在这里插入图片描述\](https://img-blog.csdn.net/20160404192406069?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center)

1.postgresql.conf

将该文件中的listen\_addresses项值设定为“\*”，在9.0 Windows版中，该项配置已经是“\*”无需修改。

2.pg\_hba.conf  
在该配置文件的host all all 127.0.0.1/32 md5行下添加以下配置，或者直接将这一行修改为以下配置  
host    all    all    0.0.0.0/0    md5

如果不希望允许所有IP远程访问，You can use your network/mask instead of 0.0.0.0/0 to only allow access from certain IP addresses.

3.关闭windows防火墙

## 导出数据库 ##

pg\_dump -h localhost -U postgres(用户名) -d 数据库名(缺省时同用户名) > /data/dum.sql(导出路径)

## 导出某个表 ##

pg\_dump -h localhost -U postgres(用户名) -d 数据库名(缺省时同用户名)  -t table(表名) > /data/dum.sql(导出路径)

## 导入数据库 ##

psql -U postgres(用户名)  -d 数据库名(缺省时同用户名) < /data/dum.sql(路径)

[http_spatialreference.org_ref_epsg_4326]: http://spatialreference.org/ref/epsg/4326/