如何建立GIS测试环境 - 将openstreetmap的样本数据导入PostgreSQL PostGIS库

16 minute read

背景

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

声明

所有地理位置信息的数据不代表官方,不代表真实数据,仅作为测试使用,请注意。

Flag Counter

digoal’s 大量PostgreSQL文章入口