postgresql postgis安装使用

安装依赖环境

# 安装
wget http://ftp.gnu.org/pub/gnu/libiconv/libiconv-1.11.tar.gz
tar -xvzf libiconv-1.11.tar.gz
cd libiconv-1.11
./configure --prefix=/usr/local/libiconv
make
sudo make install

# 安装 geos、 proj、gdal等依赖库
sudo apt update
sudo apt install libgeos++-dev libgeos-3.8.0 libgeos-c1v5 libgeos-dev libgeos-doc libproj-dev libgdal-dev

# 编译安装
tar zxvf postgis-3.1.4.tar.gz
cd postgis-3.1.4
./configure --with-libiconv=/usr/local/libiconv  --without-protobuf
make && sudo make install

# 配置插件
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;

# 查询 postgis 默认版本及已安装的版本
SELECT name, default_version,installed_version FROM pg_available_extensions WHERE name LIKE 'postgis%' or name LIKE 'address%';

测试

# 格式化打印
SELECT pprint_addy(normalize_address('202 East Fremont Street, Las Vegas, Nevada 89101')) As pretty_address;

# 坐标系转换，并计算距离
 SELECT ST_Distance(
        ST_Transform(ST_GeomFromText('POINT(-87.734087560562 43.770129071141)',4326),26986),
        ST_Transform(ST_GeomFromText('POINT(-87.747382933006 43.759234252055)', 4326),26986));