postgis 地理数据

[postgres@cdh3 ~]$ vi /data/postgresql-9.3.5/data/pg_hba.conf
host all all 0.0.0.0/0 trust

[postgres@cdh3 ~]$ vi /data/postgresql-9.3.5/data/postgresql.conf
listen_addresses = '*'

[postgres@cdh3 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@cdh3 ~]$ pg_ctl start
server starting
[postgres@cdh3 ~]$ LOG:  database system was shut down at 2015-05-11 10:45:02 CST
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

[postgres@cdh3 ~]$ psql
psql (9.3.5)
Type "help" for help.

postgres=# create user t with login superuser encrypted password 't';
CREATE ROLE
postgres=# create table t1(info json);
CREATE TABLE

postgres=# \d
                  List of relations
 Schema |         Name          |   Type   |  Owner   
--------+-----------------------+----------+----------
 public | geography_columns     | view     | postgres
 public | geometry_columns      | view     | postgres
 public | raster_columns        | view     | postgres
 public | raster_overviews      | view     | postgres
 public | spatial_ref_sys       | table    | postgres
 public | t1                    | table    | postgres
 tiger  | addr                  | table    | postgres
 tiger  | addr_gid_seq          | sequence | postgres
 tiger  | addrfeat              | table    | postgres
 tiger  | addrfeat_gid_seq      | sequence | postgres
 tiger  | bg                    | table    | postgres
 tiger  | bg_gid_seq            | sequence | postgres
 tiger  | county                | table    | postgres
 tiger  | county_gid_seq        | sequence | postgres
 tiger  | county_lookup         | table    | postgres
 tiger  | countysub_lookup      | table    | postgres
 tiger  | cousub                | table    | postgres
 tiger  | cousub_gid_seq        | sequence | postgres
 tiger  | direction_lookup      | table    | postgres
 tiger  | edges                 | table    | postgres
 tiger  | edges_gid_seq         | sequence | postgres
 tiger  | faces                 | table    | postgres
 tiger  | faces_gid_seq         | sequence | postgres
 tiger  | featnames             | table    | postgres
 tiger  | featnames_gid_seq     | sequence | postgres
 tiger  | geocode_settings      | table    | postgres
 tiger  | loader_lookuptables   | table    | postgres
 tiger  | loader_platform       | table    | postgres
 tiger  | loader_variables      | table    | postgres
 tiger  | pagc_gaz              | table    | postgres
 tiger  | pagc_gaz_id_seq       | sequence | postgres
 tiger  | pagc_lex              | table    | postgres
 tiger  | pagc_lex_id_seq       | sequence | postgres
 tiger  | pagc_rules            | table    | postgres
 tiger  | pagc_rules_id_seq     | sequence | postgres
 tiger  | place                 | table    | postgres
 tiger  | place_gid_seq         | sequence | postgres
 tiger  | place_lookup          | table    | postgres
 tiger  | secondary_unit_lookup | table    | postgres
 tiger  | state                 | table    | postgres
 tiger  | state_gid_seq         | sequence | postgres
 tiger  | state_lookup          | table    | postgres
 tiger  | street_type_lookup    | table    | postgres

postgres=# \dn
    List of schemas
    Name    |  Owner   
------------+----------
 public     | postgres
 tiger      | postgres
 tiger_data | postgres
 topology   | postgres
(4 rows)

postgres=# copy t1 from '/data/pgdata/Out_0_0.out' encoding 'utf8';
COPY 10000000
postgres=#
postgres=# select count(*) from t1;
  count   
----------
 10000000
(1 row)

postgres=# select * from t1 limit 4;
                                                                                    info                                     
                                                
-----------------------------------------------------------------------------------------------------------------------------
------------------------------------------------
 {"mdn": "64a71eada29efe54d89780160fbd5efb","occur_time": "20150414231929","lon": "116.958476940893","error_description": nul
l,"lat": "39.9634587990477","city_code": "513"}
 {"mdn": "bc65881b40163844b23e348997f3cc87","occur_time": "20150414082206","lon": "116.550218872726","error_description": nul
l,"lat": "40.8185570138381","city_code": "513"}
 {"mdn": "633a289d7d20a3b009771a58d25a3ff9","occur_time": "20150414174617","lon": "116.327485135756","error_description": nul
l,"lat": "39.8440633488245","city_code": "513"}
 {"mdn": "fa25d5a0f056ba6b96677ac63e0bd3dd","occur_time": "20150414095841","lon": "116.009243716951","error_description": nul
l,"lat": "40.5425805272534","city_code": "513"}
(4 rows)

postgres=# select t1.info->'lon' from t1 limit 4;
      ?column?      
--------------------
 "116.958476940893"
 "116.550218872726"
 "116.327485135756"
 "116.009243716951"
(4 rows)

postgres=# SELECT trim(both '"' from cast(t.info->'lon' as varchar(100))),trim(both '"' from cast(t.info->'lat' as varchar(100))) FROM t1 t limit 4;
      btrim       |      btrim       
------------------+------------------
 116.958476940893 | 39.9634587990477
 116.550218872726 | 40.8185570138381
 116.327485135756 | 39.8440633488245
 116.009243716951 | 40.5425805272534
(4 rows)

postgres=# SELECT ST_GeogFromText('SRID=4326;POINT(' ||trim(both '"' from cast(t.info->'lon' as varchar(100)))|| ' ' || trim(both '"' from cast(t.info->'lat' as varchar(100)))|| ')') FROM t1 t limit 4;

                  st_geogfromtext                   
----------------------------------------------------
 0101000020E6100000C1C6AAAF573D5D40067A309E52FB4340
 0101000020E6100000030038C936235D404C2CEA79C6684440
 0101000020E6100000E2FF3684F5145D400B7A8F440AEC4340
 0101000020E6100000E07FF57297005D40F9FF594773454440
(4 rows)

postgres=#
postgres=# select ST_distance_spheroid(ST_GeomFromText('point(115.459742440741 40.5444471813613)',4326),
postgres(# ST_GeomFromText('point(115.459742440741 41.5444471813613)',4326), 'SPHEROID["WGS 84",6378137,298.257223563]') ;
 st_distance_spheroid
----------------------
     111054.771250692
(1 row)

postgres=# SELECT ST_Distance(
postgres(#    ST_Transform(ST_GeomFromText('point(115.459742440741 40.5444471813613)',4326),26986),
postgres(#    ST_Transform(ST_GeomFromText('point(115.459742440741 41.5444471813613)', 4326),26986)
postgres(#   );
   st_distance    
------------------
 111074.606825957
(1 row)

postgres=# SELECT ST_Distance(
postgres(#    ST_GeomFromText('point(115.459742440741 40.5444471813613)',4326),
postgres(#    ST_GeomFromText('point(115.459742440741 41.5444471813613)', 4326)
postgres(#   );
 st_distance
-------------
           1

postgres=# select ST_distance_spheroid(ST_GeomFromText('point(115.459742440741 41.5444471813613)',4326),
postgres(# ST_GeomFromText('point(116.459742440741 41.5444471813613)',4326), 'SPHEROID["WGS 84",6378137,298.257223563]') ;
 st_distance_spheroid
----------------------
     83438.5878963763

其中，用到了几个函数ST_distance_spheroid，ST_GeomFromText，ST_distance_spheroid，srid=4326表明使用经纬度坐标系计算，ST_Transform函数是转换度量值，26986是返回距离（以米为单位），如果不加此转换，将返回角度。
还有n多函数，此处仅仅是随便做了几个样例，具体函数使用和说明可以查看postgis官方手册。

add 一些测试：
alter table t1 add lon decimal(16,13);
alter table t1 add lat decimal(16,13);
ALTER TABLE t1 ADD COLUMN geog geography(POINT,4326);

update t1 set lon = cast(trim(both '"' from cast(t1.info->'lon' as varchar(100))) as decimal(16,13)),
   lat=cast(trim(both '"' from cast(t1.info->'lat' as varchar(100))) as decimal(16,13))
UPDATE t1 SET geog = ST_GeogFromText('SRID=4326;POINT(' || lon || ' ' || lat || ')') ;

---select ST_distance_spheroid(geog :: geometry, ST_GeomFromText('point(115.459742440741 41.5444471813613)',4326), 'SPHEROID["WGS 84",6378137,298.257223563]')  from t1 t limit 5;

postgres=# select ST_distance_spheroid(geog :: geometry,
postgres(# ST_GeomFromText('point(115.459742440741 41.5444471813613)',4326), 'SPHEROID["WGS 84",6378137,298.257223563]')
postgres-# from t1 t limit 5;
 st_distance_spheroid
----------------------
     169773.022880992
     142641.947409624
      184422.81235003
     116197.897566521
     146279.927355358
(5 rows)

CREATE INDEX t1_gix ON t1 USING GIST ( geog );


-- Show a distance query and note, London is outside the 1km tolerance
SELECT * FROM t1 WHERE ST_DWithin(geog, ST_GeographyFromText('SRID=4326;POINT(116 41)'), 1000) ;

postgres=# SELECT * FROM t1 WHERE ST_DWithin(geog, ST_GeographyFromText('SRID=4326;POINT(116 41)'), 1000) ;
                                                                                    info                                     
                                                |        lon        |       lat        |                        geog         
               | geom
-----------------------------------------------------------------------------------------------------------------------------
------------------------------------------------+-------------------+------------------+-------------------------------------
---------------+------
 {"mdn": "37be0d0cd088ce95973061efa04dd290","occur_time": "20150414205803","lon": "116.010224590908","error_description": nul
l,"lat": "41.0038618502081","city_code": "513"} | 116.0102245909080 | 41.0038618502081 | 0101000020E610000026E40A85A7005D4044
2C8C8B7E804440 |
 {"mdn": "bdc22e3039e9e1bdd2a09e227f7f6b51","occur_time": "20150414043131","lon": "116.008376838538","error_description": nul
l,"lat": "41.0046266471841","city_code": "513"} | 116.0083768385380 | 41.0046266471841 | 0101000020E610000022E4013F89005D4044
2C219B97804440 |
 {"mdn": "8d0818c072f7af34775a8e961b12ded5","occur_time": "20150414133221","lon": "116.010963625639","error_description": nul
l,"lat": "41.0032401802824","city_code": "513"} | 116.0109636256390 | 41.0032401802824 | 0101000020E61000002A64C7A0B3005D404B
2C9A2C6A804440 |
 {"mdn": "f9b0fb14a93d315862f41f36202f7307","occur_time": "20150414081704","lon": "116.010668354575","error_description": nul
l,"lat": "41.0035526068756","city_code": "513"} | 116.0106683545750 | 41.0035526068756 | 0101000020E61000001D8052CAAE005D4052
2C6D6974804440 |
 {"mdn": "d3705d57bef7f00de61f89c98496f7cd","occur_time": "20150414153616","lon": "115.997631196297","error_description": nul
l,"lat": "40.99553353386","city_code": "513"}   | 115.9976311962970 | 40.9955335338600 | 0101000020E610000028648430D9FF5C4000
0091A46D7F4440 |
 {"mdn": "d37284be6272c5b0057f11a5e3e63451","occur_time": "20150414114856","lon": "116.00093427756","error_description": null
,"lat": "40.9941057320684","city_code": "513"}  | 116.0009342775600 | 40.9941057320684 | 0101000020E610000033E4A44E0F005D40FD
FF4BDB3E7F4440 |

-- Distance calculation using GEOGRAPHY (122.2km)
SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography, 'POINT(-21.96   -64.15)':: geography);

-- Distance calculation using GEOMETRY (13.3 "degrees")
SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geometry, 'POINT(-21.96 64.15)':: geometry);

ALTER TABLE t1 ADD COLUMN geom geometry(POINT,4326);
UPDATE t1 SET geom = ST_GeomFromEWKT('SRID=4326;POINT(' || lon || ' ' || lat || ')') ;
select Geog,geom  from t1 limit 4;
CREATE INDEX t1_geom_gix ON t1 USING GIST ( geom );

select ST_distance_spheroid(geom,ST_GeomFromText('point(115.459742440741 41.5444471813613)',4326), 'SPHEROID["WGS 84",6378137,298.257223563]')
from t1 t limit 5;

-- Show a distance query and note, London is outside the 1km tolerance
SELECT * FROM t1 WHERE ST_DWithin(geom, ST_GeographyFromText('SRID=4326;POINT(116 41)'), 1000) limit 5;

SELECT * from t1 where ST_Covers(ST_GeomFromText('SRID=4326;POLYGON((115 40,115 -40,117 40,117 -40,115 40))'),geog)=true limit 5;
SELECT * from t1 where ST_Contains(ST_GeomFromText('SRID=4326;POLYGON((115 40,115 -40,117 40,117 -40,115 40))'),geom)=true limit 5;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10037372/viewspace-1649964/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10037372/viewspace-1649964/