转自http://www.cnblogs.com/yichengbo/archive/2011/08/12/2135892.html 创建数据库的代码---创建promary表  create table promary  (  proID int primary key,  proName varchar(50) not null  ) 出现上图所示内容是因为proName varchar(50) not null这个地方有问题。一般来说，如果含有中文字符，用nchar/nvarchar，如果纯英文和数字，用char/varchar 但即使你按照我上面说的做了也可能出现乱码，一般的在进行中文字符插入时在中文字符串前面加上一个大写字母N。如下： insert into promary values(1,N'北京市')  insert into promary values(2,N'天津市')  insert into promary values(3,N'上海市')  insert into promary values(4,N'重庆市')  insert into promary values(5,N'河北省')  insert into promary values(6,N'山西省')  insert into promary values(7,N'台湾省')  insert into promary values(8,N'辽宁省')  insert into promary values(9,N'吉林省')  insert into promary values(10,N'黑龙江省')  insert into promary values(11,N'江苏省') 这样就不会出现乱码了。

如何建立GIS测试环境 - 将openstreetmap的样本数据导入PostgreSQL PostGIS库 作者 digoal 日期 2016-09-06 标签 PostgreSQL , openstreetmap , PostGIS , osm2pgsql , pbf , osm 背景 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/Osm2pgsqlhttps://github.com/openstreetmap/osm2pgsql epel-releasehttp://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 http://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/schemahttp://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，我们还可以使用shp2pgsqlhttp://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 声明 所有地理位置信息的数据不代表官方，不代表真实数据，仅作为测试使用，请注意。 Count