如何建立GIS测试环境 - 将openstreetmap的样本数据导入PostgreSQL PostGIS库
背景
PostgreSQL在GIS领域的用户非常多，这得益于PostgreSQL的开放性，PostGIS即是使用PostgreSQL开放的索引访问、数据类型、函数，操作符等接口打造的一款功能非常强大的GIS数据管理插件。
除此之外，还有pgrouting, pgcloudpoint等相关的GIS插件。当然也不乏其他领域如基因工程，化学，太空探索等，常规的数据类型无法满足业务对数据管理的需求，PostgreSQL在这种新兴领域起到了非常重要的作用(有点扯远了)。
在GIS数据处理生态中，几乎所有的软件都支持PostGIS，因此使用PostgreSQL + PostGIS进行地理位置信息的管理，教学，应用，科研等都是非常方便的。
GIS领域有一个很流行的集成平台OSGeo-Live ， 已经集成了PostgreSQL与PostGIS的环境。
这是一套基于 Lubuntu 操作系统建立的，可从 DVD、USB 盘或虚拟机启动并独立运行的演示环境。
它让用户可以不用预先安装任何软件系统就使用众多的开源空间信息软件。
OSGeo-Live 完全是由自由软件构成，可以自由地部署、复制和分发。
OSGeo-Live 提供一系列预先配置的应用程序，并包含了许多地理空间信息的使用案例，涵盖了数据的存储、显示、发布、分析和管理等各个方面。
它还带有简单的示例数据集和文档以供参考。
用户可以直接使用OSGeo-Live，也可以单独对PostgreSQL+PostGIS进行生产部署，如果需要样本数据，可以从openstreetmap下载导入。
为了降低PG用户使用GIS数据的门槛，本文将介绍PostgreSQL+PostGIS的部署，以及在哪里能找到GIS的样本数据，如何将样本数据导入PostgreSQL数据库。
环境
1. CentOS 7.x x64
2. PostgreSQL 9.6
3. PostGIS 2.2.2
4. 样本数据来自openstreetmap公开的cn.pbf中国的地理位置信息数据。
部署PostgreSQL 9.6
https://www.postgresql.org/
本文不包括OS的参数优化部分。
$ wget https://ftp.postgresql.org/pub/source/v9.6rc1/postgresql-9.6rc1.tar.bz2
$ tar -jxvf postgresql-9.6rc1.tar.bz2
$ cd postgresql-9.6rc1
$ ./configure --prefix=/home/postgres/pgsql9.6rc1 --enable-debug
$ gmake world -j 32
$ gmake install-world
环境变量配置
$ vi ~/env_pg.sh
# add by digoal
export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=1921
export PGDATA=/data01/pgdata/pg_root_96
export LANG=en_US.utf8
export PGHOME=/home/postgres/pgsql9.6rc1
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA
export PGDATABASE=postgres
alias rm='rm -i'
alias ll='ls -lh'
unalias vi
$ . ~/env_pg.sh
PostGIS 部署
pre-requirement
geos
http://trac.osgeo.org/geos
$ cd ~
$ wget http://download.osgeo.org/geos/geos-3.5.0.tar.bz2
$ tar -jxvf geos-3.5.0.tar.bz2
$ cd geos-3.5.0
$ ./configure --prefix=/home/postgres/geos
$ make -j 32
$ make install
proj
https://trac.osgeo.org/proj/
$ cd ~
$ wget http://download.osgeo.org/proj/proj-4.9.2.tar.gz
$ tar -zxvf proj-4.9.2.tar.gz
$ cd proj-4.9.2
$ ./configure --prefix=/home/postgres/proj4
$ make -j 32
$ make install
GDAL
http://gdal.org/
$ cd ~
$ wget http://download.osgeo.org/gdal/2.1.1/gdal-2.1.1.tar.gz
$ tar -zxvf gdal-2.1.1.tar.gz
$ cd gdal-2.1.1
$ ./configure --prefix=/home/postgres/gdal --with-pg=/home/postgres/pgsql9.6rc1/bin/pg_config
$ make -j 32
$ make install
LibXML2 libxslt json-c cmake …
http://www.xmlsoft.org/
# yum install -y libtool libxml2 libxml2-devel libxslt libxslt-devel json-c json-c-devel cmake gmp gmp-devel mpfr mpfr-devel boost-devel pcre-devel
cgal
http://www.cgal.org/download.html
$ cd ~
$ git clone https://github.com/CGAL/cgal
$ cd cgal
$ git checkout releases/CGAL-4.8-branch
$ mkdir build
$ cd build
$ cmake -D CMAKE_INSTALL_PREFIX=/home/postgres/cgalhome ../
$ make -j 32
$ make install
postgis 2.2.2
http://postgis.net/source/
库
# vi /etc/ld.so.conf
/home/postgres/pgsql9.6rc1/lib
/home/postgres/geos/lib
/home/postgres/proj4/lib
/home/postgres/gdal/lib
/home/postgres/cgalhome/lib
# ldconfig
环境变量
# su - postgres
$ vi ~/env_pg.sh
# add by digoal
export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=1921
export PGDATA=/data01/pgdata/pg_root_96
export LANG=en_US.utf8
export PGHOME=/home/postgres/pgsql9.6rc1
export LD_LIBRARY_PATH=/home/postgres/geos/lib:/home/postgres/proj4/lib:/home/postgres/gdal/lib:/home/postgres/cgalhome/lib:$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA
export PGDATABASE=postgres
alias rm='rm -i'
alias ll='ls -lh'
unalias vi
$ . ~/env_pg.sh
部署PostGIS
$ wget http://download.osgeo.org/postgis/source/postgis-2.2.2.tar.gz
$ tar -zxvf postgis-2.2.2.tar.gz
$ cd postgis-2.2.2
$ ./configure --prefix=/home/postgres/postgis \
--with-gdalconfig=/home/postgres/gdal/bin/gdal-config \
--with-pgconfig=/home/postgres/pgsql9.6rc1/bin/pg_config \
--with-geosconfig=/home/postgres/geos/bin/geos-config \
--with-projdir=/home/postgres/proj4
$ make -j 32
$ make install
初始化数据库集群
$ initdb -D $PGDATA -E UTF8 --locale=C -U postgres
$ cd $PGDATA
$ vi postgresql.conf
listen_addresses = '0.0.0.0' # what IP address(es) to listen on;
port = 1921 # (change requires restart)
max_connections = 100 # (change requires restart)
unix_socket_directories = '.' # comma-separated list of directories
shared_buffers = 1GB # min 128kB
maintenance_work_mem = 256MB # min 1MB
dynamic_shared_memory_type = posix # the default is the first option
bgwriter_delay = 20ms # 10-10000ms between rounds
bgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/round
max_worker_processes = 8 # (change requires restart)
max_parallel_workers_per_gather = 4 # taken from max_worker_processes
old_snapshot_threshold = 1h # 1min-60d; -1 disables; 0 is immediate
synchronous_commit = off # synchronization level;
log_destination = 'csvlog' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_truncate_on_rotation = on # If on, an existing log file with the
log_error_verbosity = verbose # terse, default, or verbose messages
log_timezone = 'PRC'
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'C' # locale for system error message
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting
default_text_search_config = 'pg_catalog.english'
启动数据库集群
$ pg_ctl start
安装插件
在需要导入样本数据的数据库中，必须安装PostGIS插件
$ psql
psql (9.6rc1)
Type "help" for help.
postgres=# create extension postgis;
CREATE EXTENSION
postgres=# create extension fuzzystrmatch;
CREATE EXTENSION
postgres=# create extension postgis_tiger_geocoder;
CREATE EXTENSION
postgres=# create extension postgis_topology;
CREATE EXTENSION
postgres=# create extension address_standardizer;
CREATE EXTENSION
安装osm2pgsql
osm2pgsql是地理位置信息数据的导入工具
http://wiki.openstreetmap.org/wiki/Osm2pgsql
https://github.com/openstreetmap/osm2pgsql
epel-release
http://fedoraproject.org/wiki/EPEL
# wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
# rpm -ivh epel-release-latest-7.noarch.rpm
# yum install -y osm2pgsql
安装目标
rpm -ql osm2pgsql
/usr/bin/osm2pgsql
/usr/share/doc/osm2pgsql-0.90.0
/usr/share/doc/osm2pgsql-0.90.0/AUTHORS
/usr/share/doc/osm2pgsql-0.90.0/ChangeLog
/usr/share/doc/osm2pgsql-0.90.0/README.md
/usr/share/licenses/osm2pgsql-0.90.0
/usr/share/licenses/osm2pgsql-0.90.0/COPYING
/usr/share/man/man1/osm2pgsql.1.gz
/usr/share/osm2pgsql
/usr/share/osm2pgsql/900913.sql
/usr/share/osm2pgsql/default.style
/usr/share/osm2pgsql/empty.style
使用帮助
osm2pgsql --help
osm2pgsql SVN version 0.90.0 (64 bit id space)
Usage:
osm2pgsql [options] planet.osm
osm2pgsql [options] planet.osm.{pbf,gz,bz2}
osm2pgsql [options] file1.osm file2.osm file3.osm
This will import the data from the OSM file(s) into a PostgreSQL database
suitable for use by the Mapnik renderer.
Common options:
-a|--append Add the OSM file into the database without removing
existing data.
-c|--create Remove existing data from the database. This is the
default if --append is not specified.
-l|--latlong Store data in degrees of latitude & longitude.
-m|--merc Store data in proper spherical mercator (default).
-E|--proj num Use projection EPSG:num.
-s|--slim Store temporary data in the database. This greatly
reduces the RAM usage but is much slower. This switch is
required if you want to update with --append later.
-S|--style Location of the style file. Defaults to
/usr/share/osm2pgsql/default.style.
-C|--cache Use up to this many MB for caching nodes (default: 800)
Database options:
-d|--database The name of the PostgreSQL database to connect
to (default: gis).
-U|--username PostgreSQL user name (specify passsword in PGPASS
environment variable or use -W).
-W|--password Force password prompt.
-H|--host Database server host name or socket location.
-P|--port Database server port.
A typical command to import a full planet is
osm2pgsql -c -d gis --slim -C <cache size> -k \
--flat-nodes <flat nodes> planet-latest.osm.pbf
where
<cache size> is 20000 on machines with 24GB or more RAM
or about 75% of memory in MB on machines with less
<flat nodes> is a location where a 19GB file can be saved.
A typical command to update a database imported with the above command is
osmosis --rri workingDirectory=<osmosis dir> --simc --wx - \
| osm2pgsql -a -d gis --slim -k --flat-nodes <flat nodes>
where
<flat nodes> is the same location as above.
<osmosis dir> is the location osmosis replication was initialized to.
Run osm2pgsql --help --verbose (-h -v) for a full list of options.
man osm2pgsql
样本数据下载
OSM openstreetmap
介绍postgis使用的文档
http://live.osgeo.org/zh/quickstart/postgis_quickstart.html
osgeo LIVE 平台的中文介绍
http://live.osgeo.org/zh/index.html
osgeo 的PPT介绍，建议熟悉一下，对GIS入门很有帮助
http://live.osgeo.org/en/presentation/index.html
osgeo 中国社区首页
http://www.osgeo.cn/
openstreetmap是一个开放的GIS信息数据共享库，一直都在更新，现在全球的数据有几十GB，中国的数据有几百MB。
https://www.openstreetmap.org
https://planet.openstreetmap.org/
http://wiki.openstreetmap.org/wiki/Planet.osm
可以从镜像站点下载共享的pbf数据
http://download.gisgraphy.com/openstreetmap/pbf/
下载中国的PBF数据
$ wget http://download.gisgraphy.com/openstreetmap/pbf/CN.tar.bz2
-rw-r--r-- 1 root root 265M Sep 3 16:40 CN.tar.bz2
下载亚洲的PBF数据
$ wget http://download.geofabrik.de/asia-latest.osm.pbf
-rw-r--r-- 1 root root 4.4G Sep 6 08:04 asia-latest.osm.pbf
$ tar -jxvf CN.tar.bz2
-rw-r--r-- 1 root root 263M Sep 2 09:17 CN
导入样本数据
确保postgis插件已安装
$ su - postgres
$ psql postgres
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
------------------------+---------+------------+---------------------------------------------------------------------------------------------------------------------
address_standardizer | 2.2.2 | public | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
fuzzystrmatch | 1.1 | public | determine similarities and distance between strings
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 2.2.2 | public | PostGIS geometry, geography, and raster spatial types and functions
postgis_tiger_geocoder | 2.2.2 | tiger | PostGIS tiger geocoder and reverse geocoder
postgis_topology | 2.2.2 | topology | PostGIS topology spatial types and functions
(6 rows)
使用osm2pgsql将下载的中国PBF数据导入PostgreSQL数据库
$ export PGPASS=postgres
$ osm2pgsql -H 127.0.0.1 -P 1921 -U postgres -d postgres -c -l -C 2000 --number-processes 8 -p digoal -r pbf /data01/CN
数据库连接相关
export PGPASS=postgres
-d|--database The name of the PostgreSQL database to connect
to (default: gis).
-U|--username PostgreSQL user name (specify passsword in PGPASS
environment variable or use -W).
-H|--host Database server host name or socket location.
-P|--port Database server port.
-c|--create
Remove existing data from the database. This is the default if --append is not specified.
-l|--latlong
Store data in degrees of latitude & longitude.
Latlong (-l) SRS: 4326 (none)
内存足够时不建议使用 -s --drop. 速度较慢.
-s|--slim
Store temporary data in the database. Without this mode, all temporary data is stored in RAM and if you do not have enough the import will not work successfully. With slim mode, you should be able to import the data
even on a system with limited RAM, although if you do not have enough RAM to cache at least all of the nodes, the time to import the data will likely be greatly increased.
--drop
Drop the slim mode tables from the database once the import is complete. This can greatly reduce the size of the database, as the slim mode tables typically are the same size, if not slightly bigger than the main
tables. It does not, however, reduce the maximum spike of disk usage during import. It can furthermore increase the import speed, as no indices need to be created for the slim mode tables, which (depending on hard‐
ware) can nearly halve import time. Slim mode tables however have to be persistent if you want to be able to update your database, as these tables are needed for diff processing.
--number-processes num
Specifies the number of parallel processes used for certain operations. If disks are fast enough e.g. if you have an SSD, then this can greatly increase speed of the "going over pending ways" and "going over pending
relations" stages on a multi-core server.
-p|--prefix prefix_string
Prefix for table names (default: planet_osm).
-r|--input-reader format
Select format of the input file. Available choices are auto (default) for autodetecting the format, xml for OSM XML format files, o5m for o5m formatted files and pbf for OSM PBF binary format.
输出
osm2pgsql SVN version 0.90.0 (64 bit id space)
Using built-in tag processing pipeline
Using projection SRS 4326 (Latlong)
Setting up table: digoal_point
Setting up table: digoal_line
Setting up table: digoal_polygon
Setting up table: digoal_roads
Allocating memory for dense node cache
Allocating dense node cache in one big chunk
Allocating memory for sparse node cache
Sharing dense sparse
Node-cache: cache=2000MB, maxblocks=32000*65536, allocation method=3
Mid: Ram, scale=10000000
Reading in file: /data01/cn.pbf
Using PBF parser.
Processing: Node(32887k 657.8k/s) Way(2409k 20.25k/s) Relation(29450 669.32/s) parse time: 213s
Node stats: total(32887809), max(4373037112) in 50s
Way stats: total(2409943), max(439626731) in 119s
Relation stats: total(29459), max(6537660) in 44s
Committing transaction for digoal_point
Committing transaction for digoal_line
Committing transaction for digoal_polygon
Committing transaction for digoal_roads
Using built-in tag processing pipeline
Using built-in tag processing pipeline
Using built-in tag processing pipeline
Using built-in tag processing pipeline
Using built-in tag processing pipeline
Using built-in tag processing pipeline
Using built-in tag processing pipeline
Using built-in tag processing pipeline
Going over pending ways...
824623 ways are pending
Using 8 helper-processes
Finished processing 824623 ways in 44 s
824623 Pending ways took 44s at a rate of 18741.43/s
Committing transaction for digoal_point
Committing transaction for digoal_line
Committing transaction for digoal_polygon
Committing transaction for digoal_roads
Committing transaction for digoal_point
Committing transaction for digoal_line
Committing transaction for digoal_polygon
Committing transaction for digoal_roads
Committing transaction for digoal_point
Committing transaction for digoal_line
Committing transaction for digoal_polygon
Committing transaction for digoal_roads
Committing transaction for digoal_point
Committing transaction for digoal_line
Committing transaction for digoal_polygon
Committing transaction for digoal_roads
Committing transaction for digoal_point
Committing transaction for digoal_line
Committing transaction for digoal_polygon
Committing transaction for digoal_roads
Committing transaction for digoal_point
Committing transaction for digoal_line
Committing transaction for digoal_polygon
Committing transaction for digoal_roads
Committing transaction for digoal_point
Committing transaction for digoal_line
Committing transaction for digoal_polygon
Committing transaction for digoal_roads
Committing transaction for digoal_point
Committing transaction for digoal_line
Committing transaction for digoal_polygon
Committing transaction for digoal_roads
Going over pending relations...
0 relations are pending
Using 8 helper-processes
Finished processing 0 relations in 0 s
Committing transaction for digoal_point
WARNING: there is no transaction in progress
Committing transaction for digoal_line
WARNING: there is no transaction in progress
Committing transaction for digoal_polygon
WARNING: there is no transaction in progress
Committing transaction for digoal_roads
WARNING: there is no transaction in progress
Committing transaction for digoal_point
WARNING: there is no transaction in progress
Committing transaction for digoal_line
WARNING: there is no transaction in progress
Committing transaction for digoal_polygon
WARNING: there is no transaction in progress
Committing transaction for digoal_roads
WARNING: there is no transaction in progress
Committing transaction for digoal_point
WARNING: there is no transaction in progress
Committing transaction for digoal_line
WARNING: there is no transaction in progress
Committing transaction for digoal_polygon
WARNING: there is no transaction in progress
Committing transaction for digoal_roads
WARNING: there is no transaction in progress
Committing transaction for digoal_point
WARNING: there is no transaction in progress
Committing transaction for digoal_line
WARNING: there is no transaction in progress
Committing transaction for digoal_polygon
WARNING: there is no transaction in progress
Committing transaction for digoal_roads
WARNING: there is no transaction in progress
Committing transaction for digoal_point
WARNING: there is no transaction in progress
Committing transaction for digoal_line
WARNING: there is no transaction in progress
Committing transaction for digoal_polygon
WARNING: there is no transaction in progress
Committing transaction for digoal_roads
WARNING: there is no transaction in progress
Committing transaction for digoal_point
WARNING: there is no transaction in progress
Committing transaction for digoal_line
WARNING: there is no transaction in progress
Committing transaction for digoal_polygon
WARNING: there is no transaction in progress
Committing transaction for digoal_roads
WARNING: there is no transaction in progress
Committing transaction for digoal_point
WARNING: there is no transaction in progress
Committing transaction for digoal_line
WARNING: there is no transaction in progress
Committing transaction for digoal_polygon
WARNING: there is no transaction in progress
Committing transaction for digoal_roads
WARNING: there is no transaction in progress
Committing transaction for digoal_point
WARNING: there is no transaction in progress
Committing transaction for digoal_line
WARNING: there is no transaction in progress
Committing transaction for digoal_polygon
WARNING: there is no transaction in progress
Committing transaction for digoal_roads
WARNING: there is no transaction in progress
Sorting data and creating indexes for digoal_point
Sorting data and creating indexes for digoal_line
Sorting data and creating indexes for digoal_polygon
Sorting data and creating indexes for digoal_roads
Copying digoal_point to cluster by geometry finished
Creating geometry index on digoal_point
Copying digoal_polygon to cluster by geometry finished
Creating geometry index on digoal_polygon
Copying digoal_roads to cluster by geometry finished
Creating geometry index on digoal_roads
Copying digoal_line to cluster by geometry finished
Creating geometry index on digoal_line
Creating indexes on digoal_point finished
All indexes on digoal_point created in 56s
Completed digoal_point
Creating indexes on digoal_roads finished
All indexes on digoal_roads created in 65s
Completed digoal_roads
Creating indexes on digoal_polygon finished
All indexes on digoal_polygon created in 71s
Completed digoal_polygon
Creating indexes on digoal_line finished
All indexes on digoal_line created in 90s
Completed digoal_line
node cache: stored: 32887809(100.00%), storage efficiency: 50.14% (dense blocks: 116, sparse nodes: 32323305), hit rate: 100.18%
Osm2pgsql took 349s overall
导入后数据如下，数据分为点，线段，道路，区域。
postgres=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
----------+-----------------+-------+----------+------------+-------------
public | digoal_line | table | postgres | 702 MB |
public | digoal_point | table | postgres | 70 MB |
public | digoal_polygon | table | postgres | 310 MB |
public | digoal_roads | table | postgres | 303 MB |
postgres=# \di+
List of relations
Schema | Name | Type | Owner | Table | Size | Description
----------+-------------------------------------------------+-------+----------+-----------------+------------+-------------
public | digoal_line_index | index | postgres | digoal_line | 184 MB |
public | digoal_point_index | index | postgres | digoal_point | 40 MB |
public | digoal_polygon_index | index | postgres | digoal_polygon | 81 MB |
public | digoal_roads_index | index | postgres | digoal_roads | 67 MB |
表结构举例
postgres=# \d digoal_line
Table "public.digoal_line"
Column | Type | Modifiers
--------------------+---------------------------+-----------
osm_id | bigint |
access | text |
addr:housename | text |
addr:housenumber | text |
addr:interpolation | text |
admin_level | text |
aerialway | text |
aeroway | text |
amenity | text |
area | text |
barrier | text |
bicycle | text |
brand | text |
bridge | text |
boundary | text |
building | text |
construction | text |
covered | text |
culvert | text |
cutting | text |
denomination | text |
disused | text |
embankment | text |
foot | text |
generator:source | text |
harbour | text |
highway | text |
historic | text |
horse | text |
intermittent | text |
junction | text |
landuse | text |
layer | text |
leisure | text |
lock | text |
man_made | text |
military | text |
motorcar | text |
name | text |
natural | text |
office | text |
oneway | text |
operator | text |
place | text |
population | text |
power | text |
power_source | text |
public_transport | text |
railway | text |
ref | text |
religion | text |
route | text |
service | text |
shop | text |
sport | text |
surface | text |
toll | text |
tourism | text |
tower:type | text |
tracktype | text |
tunnel | text |
water | text |
waterway | text |
wetland | text |
width | text |
wood | text |
z_order | integer |
way_area | real |
way | geometry(LineString,4326) |
Indexes:
"digoal_line_index" gist (way) WITH (fillfactor='100')
GIS数据用法举例
http://wiki.openstreetmap.org/wiki/Osm2pgsql/schema
http://live.osgeo.org/zh/quickstart/postgis_quickstart.html
1. 在point表中，查询城市对应的坐标
这里的坐标是无法阅读的 16 进制格式。要以 WKT 文本显示，使用 ST_AsText(the_geom) 或 ST_AsEwkt(the_geom) 函数。也可以使用 ST_X(the_geom) 和 ST_Y(the_geom) 显示一个维度的坐标
postgres=# select name,way from digoal_point where place='city';
双湖县 | 0101000020E61000003C180C0973355640F3846FAB6A984040
叶城县 | 0101000020E610000058AB764D485C53406511D43ABBF24240
皮山县 | 0101000020E6100000CA479C5FDE9153408ABCF61620CF4240
泽普县 | 0101000020E6100000D8E9ACBB1E535340B1B3D771B2184340
英吉沙县 | 0101000020E6100000A271F26C200B5340684DE262FB774340
喀什市 | 0101000020E6100000DD3532D909FF52407187F2AA73BC4340
阿图什市 | 0101000020E61000009AA95A1CDF0A5340902111644EDB4340
和田县/Hotan | 0101000020E6100000301576ACADFB5340E72CFD95DF8C4240
和田市 | 0101000020E610000054EF5F0FDCFA5340220A1CBFA68E4240
策勒县 | 0101000020E61000008C53083F4C335440277E3100F97F4240
且末县 | 0101000020E6100000AFE0C84D1E625540EDF549496A114340
若羌县 | 0101000020E61000002491C71D800A564030D05A762D824340
图木舒克市 | 0101000020E61000001338C8DA95C5534044B1CBA612EF4340
postgres=# SELECT name, ST_AsText(way), ST_AsEwkt(way), ST_X(way), ST_Y(way) FROM digoal_point where place='city' order by 4,5;
....
无锡市 | POINT(120.2954534 31.5756347) | SRID=4326;POINT(120.2954534 31.5756347) | 120.2954534 | 31.5756347
余杭区 | POINT(120.3 30.416667) | SRID=4326;POINT(120.3 30.416667) | 120.3 | 30.416667
东台市 | POINT(120.3122464 32.8536216) | SRID=4326;POINT(120.3122464 32.8536216) | 120.3122464 | 32.8536216
绥中县 | POINT(120.3331747 40.329263) | SRID=4326;POINT(120.3331747 40.329263) | 120.3331747 | 40.329263
青岛市 | POINT(120.3497193 36.0895093) | SRID=4326;POINT(120.3497193 36.0895093) | 120.3497193 | 36.0895093
招远市 | POINT(120.402222 37.354722) | SRID=4326;POINT(120.402222 37.354722) | 120.402222 | 37.354722
苍南 | POINT(120.4167898 27.5165682) | SRID=4326;POINT(120.4167898 27.5165682) | 120.4167898 | 27.5165682
朝阳市 | POINT(120.4390738 41.5754767) | SRID=4326;POINT(120.4390738 41.5754767) | 120.4390738 | 41.5754767
磐安 | POINT(120.4447816 29.0557511) | SRID=4326;POINT(120.4447816 29.0557511) | 120.4447816 | 29.0557511
即墨市 | POINT(120.4502879 36.3912177) | SRID=4326;POINT(120.4502879 36.3912177) | 120.4502879 | 36.3912177
大丰市 | POINT(120.4564759 33.2019957) | SRID=4326;POINT(120.4564759 33.2019957) | 120.4564759 | 33.2019957
海安县 | POINT(120.4629239 32.5348763) | SRID=4326;POINT(120.4629239 32.5348763) | 120.4629239 | 32.5348763
柯桥区 | POINT(120.489086 30.0812532) | SRID=4326;POINT(120.489086 30.0812532) | 120.489086 | 30.0812532
....
2. 空间查询
PostGIS 为 PostgreSQL 扩展了许多空间操作功能。以上已经涉及了转换空间坐标格式的 ST_GeomFromText 。
多数空间操作以 ST（spatial type）开头，在 PostGIS 文档相应章节有罗列。
这里回答一个具体的问题：以米为单位并假设地球是完美椭球，城市间的相互的距离是多少？
SELECT p1.name,p2.name,ST_Distance_Sphere(p1.way,p2.way) FROM
(select * from digoal_point where place='city' and name ~ '宜春') p1 ,
(select * from digoal_point where place='city' and name ~ '杭州') p2
where p1.name <> p2.name;
name | name | st_distance_sphere
--------+--------+--------------------
宜春市 | 杭州市 | 623574.67310136
(1 row)
采取不同的椭球参数（椭球体名、半主轴长、扁率）计算
SELECT p1.name,p2.name,ST_Distance_Spheroid(p1.way, p2.way, 'SPHEROID["GRS_1980",6378137,298.257222]')
FROM
(select * from digoal_point where place='city' and name ~ '宜春') p1 ,
(select * from digoal_point where place='city' and name ~ '杭州') p2
where p1.name <> p2.name;
name | name | st_distance_spheroid
--------+--------+----------------------
宜春市 | 杭州市 | 624144.288437696
(1 row)
3. 区域类型查询
postgres=# select name from digoal_polygon where name ~ '杭州';
name
----------------------------------
杭州世纪华联超市
杭州樓 Hangchow House
杭州国际学校
杭州天目外国语学校
杭州市 / Hangzhou
杭州野生动物世界
浙江省杭州第二中学
网易杭州
杭州邮政大楼
....
4. 点面判断举例
postgres=# select name from digoal_polygon where name ~ '杭州' and way ~ ST_GeomFromEWKT('SRID=4326;POINT(120.3 30.416667)');
name
-------------------
杭州市 / Hangzhou
(1 row)
5. 区域统计举例
select t1.name,count(*) from digoal_polygon t1, digoal_point t2 where t1.way ~ t2.way group by t1.name order by 2 desc limit 100;
postgres=# select t1.name,count(*) from digoal_polygon t1, digoal_point t2 where t1.way ~ t2.way group by t1.name order by 2 desc limit 100;
name | count
----------------------------------------------------------------+--------
内蒙古自治区 / Inner Mongolia | 262464
| 243692
甘肃省 | 125988
河北省 | 113878
广东省 | 107363
新疆维吾尔自治区 | 87043
青海省 | 45976
陕西省 | 45119
吉林省 | 43395
浙江省 | 42464
北京市 | 42385
6. 求区域面积
声明，不代表真实数据
postgres=# select name,ST_AREA(way)/POWER(0.3048,2) As sqft_spheroid, ST_AREA(way,false)/POWER(0.3048,2) As sqft_sphere, ST_Area(way) As sqm_spheroid from digoal_polygon order by 2 desc limit 10;
name | sqft_spheroid | sqft_sphere | sqm_spheroid
-------------------------------+------------------+------------------+------------------
新疆维吾尔自治区 | 1886.69386901133 | 17545758222578 | 175.279595980515
内蒙古自治区 / Inner Mongolia | 1388.45334818658 | 12303880320749.7 | 128.991536944712
西藏自治区 (???????????????????) | 1157.79354491985 | 12172952810487.3 | 107.56254001543
青海省 | 747.111046519574 | 7501178053415.87 | 69.4088874392498
黑龙江省 | 586.158263912179 | 4858310748074.14 | 54.4558846385637
巴音郭楞蒙古自治州 (Bayingol) | 530.069700073705 | 5055365519125.31 | 49.2450865487355
四川省 | 492.339435917579 | 5235712822057.58 | 45.7398303086283
甘肃省 | 468.745196462264 | 4571217336421.43 | 43.5478537367416
云南省 | 368.878048351166 | 4132569814712.27 | 34.2698920810903
7. 更多的例子可参考
http://postgis.net/docs/manual-2.2/
其他
除了使用pbf，我们还可以使用shp2pgsql
http://postgis.net/docs/manual-2.2/using_postgis_dbmanagement.html#shp2pgsql_usage
shapefile 是一种 Esri 矢量数据存储格式，用于存储地理要素的位置、形状和属性。
其存储为一组相关文件，并包含一个要素类。
Shapefile 经常包含具有很多关联数据的大型要素，并一直用于 GIS 桌面应用程序（例如 ArcGIS for Desktop 和 ArcGIS Explorer Desktop）。
阿里云用户可以更简便的使用GIS
如果使用阿里云提供的RDS for PostgreSQL，因为已经集成了PostGIS插件，所以使用起来更加方便。
导入地理位置信息测试数据将简化为3步骤
简化步骤如下
1. 购买实例, 并在对应的数据库中创建extension
create extension postgis;
2. 下载pbf
$ wget http://download.gisgraphy.com/openstreetmap/pbf/CN.tar.bz2
$ tar -jxvf CN.tar.bz2
3. 导入
找一台ECS，安装osm2pgsql，并将下载的PBF数据导入RDS PostgreSQL
$ export PGPASS=$pwd
$ osm2pgsql -H $ip -P $port -U $user -d $dbname -c -l -C 2000 --number-processes 8 -p digoal -r pbf /data01/CN
参考
介绍postgis使用的文档
http://live.osgeo.org/zh/quickstart/postgis_quickstart.html
osgeo LIVE 平台的中文介绍
http://live.osgeo.org/zh/index.html
osgeo 的PPT介绍，建议熟悉一下，对GIS入门很有帮助
http://live.osgeo.org/en/presentation/index.html
osgeo 中国社区首页
http://www.osgeo.cn/
openstreetmap是一个开放的GIS信息数据共享库，一直都在更新，现在全球的数据有几十GB，中国的数据有几百MB。
https://www.openstreetmap.org
http://planet.openstreetmap.org/
http://wiki.openstreetmap.org/wiki/Planet.osm
可以从镜像站点下载共享的pbf数据
http://download.gisgraphy.com/openstreetmap/pbf/
http://postgis.net/docs/manual-2.2/
http://wiki.openstreetmap.org/wiki/Osm2pgsql/schema
http://live.osgeo.org/zh/quickstart/postgis_quickstart.html
http://postgis.net/docs/manual-2.2/using_postgis_dbmanagement.html#shp2pgsql_usage
声明
所有地理位置信息的数据不代表官方，不代表真实数据，仅作为测试使用，请注意。