java postgis环境搭建_CentOS 7 yum 安装postgis和简单查询

最新推荐文章于 2022-08-25 09:56:18 发布

林葭音

最新推荐文章于 2022-08-25 09:56:18 发布

阅读量345

点赞数

文章标签： java postgis环境搭建

本文链接：https://blog.csdn.net/weixin_28481133/article/details/114203483

版权

本文安装环境是 CentOS7.6。安装版本是 postgresql-11 。推荐关闭SELinux和防火墙打开相应端口。

安装 postgresql-server。postgesql-server 官方安装教程。

#设置安装源

[root@promote ~]# yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-ppc64le/pgdg-centos11-11-2.noarch.rpm

#安装postgresql11-server

[root@promote ~]# yum install postgresql11-server postgresql11-client postgresql11-libs postgresql11-contrib postgresql11-devel

#初始化数据库

[root@promote ~]# /usr/pgsql-11/bin/postgresql-11-setup initdb

#添加开机启动

[root@promote ~]# systemctl enable postgresql-11

安装 postgis 。参考postgis 官方安装教程Red Hat / Centos / Scientific Linux 选项。

#安装epel-release

[root@promote ~]# yum -y install epel-release

#安装postgis

[root@promote ~]# sudo yum install postgis25_11 postgis25_11-devel postgis25_11-client postgis25_11-debuginfo postgis25_11-utils

#安装ogr_fdw11扩展

[root@promote ~]# yum install ogr_fdw11 ogr_fdw11-debuginfo pgrouting_11 pgrouting_11-debuginfo

#可选添加开机启动

[root@promote ~]# systemctl enable postgresql-11

#启动和查看postgresql-11服务

[root@promote ~]# systemctl start postgresql-11

[root@promote ~]# systemctl status postgresql-11

[root@promote ~]# systemctl list-unit-files | grep postgresql

postgresql-11.service enabled

#查看开机启动

[root@promote ~]# systemctl list-unit-files | grep postgresql

postgresql-11.service enabled

[root@promote ~]#

#查看数据库服务端口开启情况

[root@promote ~]# netstat -ntlp | grep post

tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 10592/postmaster

tcp6 0 0 ::1:5432 :::* LISTEN 10592/postmaster

[root@promote ~]#

创建用户，创建数据库gistest，切换用户到postgres，查看postgis版本信息。

[root@promote ~]# su - postgres

#非第一次登录

[root@promote ~]# su - postgres

上一次登录：五 4月 12 16:51:07 CST 2019pts/1 上

-bash-4.2$ psql

psql (11.2)

输入 "help" 来获取帮助信息.

postgres=#

#安装EXTENSION

postgres=# CREATE EXTENSION postgis;

CREATE EXTENSION

postgres=# CREATE EXTENSION postgis_topology;

CREATE EXTENSION

postgres=# CREATE EXTENSION adminpack;

CREATE EXTENSION

postgres=# CREATE EXTENSION postgis_sfcgal;

CREATE EXTENSION

postgres=# CREATE EXTENSION fuzzystrmatch;

CREATE EXTENSION

postgres=# CREATE EXTENSION address_standardizer;

CREATE EXTENSION

postgres=# CREATE EXTENSION address_standardizer_data_us;

CREATE EXTENSION

postgres=# CREATE EXTENSION postgis_tiger_geocoder;

CREATE EXTENSION

postgres=#

#查看安装扩展

postgres=# \dx

List of installed extensions

Name | Version | Schema | Description

------------------+---------+------------+---------------------------------------------------------------------

adminpack | 2.0 | pg_catalog | administrative functions for PostgreSQL

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

postgis | 2.5.2 | public | PostGIS geometry, geography, and raster spatial types and functions

postgis_topology | 2.5.2 | topology | PostGIS topology spatial types and functions

(4 rows)

#\x 开启或关闭扩展显示

gistest=# \x

扩展显示已关闭。

gistest=# \d

关联列表

架构模式 | 名称 | 类型 | 拥有者

----------+-------------------+--------+----------

public | geography_columns | 视图 | postgres

public | geometry_columns | 视图 | postgres

public | raster_columns | 视图 | postgres

public | raster_overviews | 视图 | postgres

public | spatial_ref_sys | 数据表 | postgres

topology | layer | 数据表 | postgres

topology | topology | 数据表 | postgres

topology | topology_id_seq | 序列数 | postgres

(8 行记录)

gistest=# \l

数据库列表

-----------+----------+----------+-------------+-------------+-----------------------

(4 行记录)

#查看版本信息

gistest=# SELECT postgis_full_version();

postgis_full_version

--------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------

POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="110" GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/2

5" LIBXML="2.9.1" LIBJSON="0.11" TOPOLOGY RASTER

(1 行记录)

gistest=# exit

-bash-4.2$ exit

登出

[root@promote ~]#

连接postgres数据库，创建测试表city。添加测算表和值。

postgres=# \c postgres

postgres=# \x

扩展显示已打开。

#创建测试表

postgres=# CREATE TABLE city (id int4, name varchar(50));

CREATE TABLE

postgres=# SELECT AddGeometryColumn ('city', 'the_geom', 4326, 'POINT', 2);

-[ RECORD 1 ]-----+--------------------------------------------------

addgeometrycolumn | public.city.the_geom SRID:4326 TYPE:POINT DIMS:2

postgres=# select * from city;

(0 行记录)

postgres=# INSERT INTO city (id, the_geom, name) VALUES (1,ST_GeomFromText('POINT(-0.1257 51.508)',4326),'London, England');

INSERT 0 1

postgres=# INSERT INTO city (id, the_geom, name) VALUES (2,ST_GeomFromText('POINT(-81.233 42.983)',4326),'London, Ontario');

INSERT 0 1

postgres=# INSERT INTO city (id, the_geom, name) VALUES (3,ST_GeomFromText('POINT(27.91162491 -33.01529)',4326),'East London,SA');

INSERT 0 1

postgres=#

简单查询和空间查询。

postgres=# SELECT * FROM city;

-[ RECORD 1 ]------------------------------------------------

id | 1

name | London, England

the_geom | 0101000020E6100000BBB88D06F016C0BF1B2FDD2406C14940

-[ RECORD 2 ]------------------------------------------------

id | 2

name | London, Ontario

the_geom | 0101000020E6100000F4FDD478E94E54C0E7FBA9F1D27D4540

-[ RECORD 3 ]------------------------------------------------

id | 3

name | East London,SA

the_geom | 0101000020E610000040AB064060E93B4059FAD005F58140C0

postgres=# SELECT id, ST_AsText(the_geom), ST_AsEwkt(the_geom), ST_X(the_geom), ST_Y(the_geom) FROM city;

-[ RECORD 1 ]-------------------------------------

id | 1

st_astext | POINT(-0.1257 51.508)

st_asewkt | SRID=4326;POINT(-0.1257 51.508)

st_x | -0.1257

st_y | 51.508

-[ RECORD 2 ]-------------------------------------

id | 2

st_astext | POINT(-81.233 42.983)

st_asewkt | SRID=4326;POINT(-81.233 42.983)

st_x | -81.233

st_y | 42.983

-[ RECORD 3 ]-------------------------------------

id | 3

st_astext | POINT(27.91162491 -33.01529)

st_asewkt | SRID=4326;POINT(27.91162491 -33.01529)

st_x | 27.91162491

st_y | -33.01529

postgres=# SELECT p1.name,p2.name,ST_Distance_Sphere(p1.the_geom,p2.the_geom) FROM city AS p1, city AS p2 WHERE p1.id > p2.id;

警告: ST_Distance_Sphere signature was deprecated in 2.2.0. Please use ST_DistanceSphere

-[ RECORD 1 ]------+-----------------

name | London, Ontario

name | London, England

st_distance_sphere | 5875787.03777356

-[ RECORD 2 ]------+-----------------

name | East London,SA

name | London, England

st_distance_sphere | 9789680.59961472

-[ RECORD 3 ]------+-----------------

name | East London,SA

name | London, Ontario

st_distance_sphere | 13892208.6782928

postgres=#

postgres=# SELECT p1.name,p2.name,ST_DistanceSphere(p1.the_geom,p2.the_geom) FROM city AS p1, city AS p2 WHERE p1.id > p2.id;

警告: ST_Distance_Sphere signature was deprecated in 2.2.0. Please use ST_DistanceSphere

-[ RECORD 1 ]------+-----------------

name | London, Ontario

name | London, England

st_distance_sphere | 5875787.03777356

-[ RECORD 2 ]------+-----------------

name | East London,SA

name | London, England

st_distance_sphere | 9789680.59961472

-[ RECORD 3 ]------+-----------------

name | East London,SA

name | London, Ontario

st_distance_sphere | 13892208.6782928

postgres=# SELECT p1.name,p2.name,ST_DistanceSphere(p1.the_geom,p2.the_geom) FROM city AS p1, city AS p2 WHERE p1.id > p2.id;

-[ RECORD 1 ]-----+-----------------

name | London, Ontario

name | London, England

st_distancesphere | 5875787.03777356

-[ RECORD 2 ]-----+-----------------

name | East London,SA

name | London, England

st_distancesphere | 9789680.59961472

-[ RECORD 3 ]-----+-----------------

name | East London,SA

name | London, Ontario

st_distancesphere | 13892208.6782928

postgres=#

林葭音

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
java postgis环境搭建_CentOS 7 yum 安装postgis和简单查询

本文安装环境是 CentOS7.6。安装版本是 postgresql-11 。推荐关闭SELinux和防火墙打开相应端口。安装 postgresql-server。postgesql-server 官方安装教程。#设置安装源[root@promote ~]# yum install https://download.postgresql.org/pub/repos/yum/11/redhat/r...
复制链接

扫一扫