greenplum使用postgis例子
来源:互联网 发布:星星知我心幼年弯弯 编辑:程序博客网 时间:2024/05/16 07:43
greenplum中使用postgis的版本是v2.0.3
安装
安装插件
在如下位置下载postgis gppkg
https://network.pivotal.io/products/pivotal-gpdb/#/releases/4540/file_groups/493
安装插件
gppkg -i /home/gpadmin/postgis-ossv2.0.3_pv2.0.1_gpdb4.3orca-rhel5-x86_64.gppkg
注入函数和表
在数据库中注入函数,下面两个sql文件可以在源码中找到https://github.com/greenplum-db/gpdb
psql -d db2 -f postgis.sqlpsql -d db2 -f spatial_ref_sys.sql
postgis.sql在安装后的环境gpdb/share/postgresql/contrib/postgis-2.0/postgis.sql
可以找到
spatial_ref_sys.sql在源码中可以找到
完成后,可以在数据库db2中看到如下表spatial_ref_sys和另外2张视图
db2=# \d List of relations Schema | Name | Type | Owner | Storage --------+-------------------+-------+---------+--------- public | geography_columns | view | gpadmin | none public | geometry_columns | view | gpadmin | none public | spatial_ref_sys | table | gpadmin | heap(3 rows)
其中 spatial_ref_sys 存储着合法的空间坐标系统
db2=# SELECT srid,auth_name,proj4text FROM spatial_ref_sys LIMIT 10; srid | auth_name | proj4text ------+-----------+------------------------------------------------------------------- 3889 | EPSG | +proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +no_defs 4001 | EPSG | +proj=longlat +ellps=airy +no_defs 4009 | EPSG | +proj=longlat +a=6378450.047548896 +b=6356826.621488444 +no_defs 4025 | EPSG | +proj=longlat +ellps=WGS66 +no_defs 4033 | EPSG | +proj=longlat +a=6378136.3 +b=6356751.616592146 +no_defs 4041 | EPSG | +proj=longlat +a=6378135 +b=6356750.304921594 +no_defs 4081 | EPSG | +proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +no_defs 4120 | EPSG | +proj=longlat +ellps=bessel +no_defs 4128 | EPSG | +proj=longlat +ellps=clrk66 +no_defs 4136 | EPSG | +proj=longlat +ellps=clrk66 +no_defs (10 rows)
基础测试
创建表
db2=# CREATE TABLE cities ( id int4, name varchar(50) );NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.CREATE TABLEdb2=# SELECT AddGeometryColumn ('cities', 'the_geom', 4326, 'POINT', 2); addgeometrycolumn ----------------------------------------------------- public.cities.the_geom SRID:4326 TYPE:POINT DIMS:2 (1 row)db2=# SELECT * from cities; id | name | the_geom ----+------+----------(0 rows)
插入数据并查看
INSERT INTO cities (id, the_geom, name) VALUES (1,ST_GeomFromText('POINT(-0.1257 51.508)',4326),'London, England');INSERT INTO cities (id, the_geom, name) VALUES (2,ST_GeomFromText('POINT(-81.233 42.983)',4326),'London, Ontario');INSERT INTO cities (id, the_geom, name) VALUES (3,ST_GeomFromText('POINT(27.91162491 -33.01529)',4326),'East London,SA');db2=# SELECT * FROM cities; id | name | the_geom ----+-----------------+---------------------------------------------------- 3 | East London,SA | 0101000020E610000040AB064060E93B4059FAD005F58140C0 1 | London, England | 0101000020E6100000BBB88D06F016C0BF1B2FDD2406C14940 2 | London, Ontario | 0101000020E6100000F4FDD478E94E54C0E7FBA9F1D27D4540(3 rows)
空间查询
多数空间操作以 ST(spatial type)开头,以米为单位并假设地球是完美椭球,上面三个城市相互的距离是多少?
db2=# SELECT p1.name,p2.name,ST_Distance_Sphere(p1.the_geom,p2.the_geom) FROM cities AS p1, cities AS p2 WHERE p1.id > p2.id; name | name | st_distance_sphere -----------------+-----------------+-------------------- East London,SA | London, England | 9789680.59961472 East London,SA | London, Ontario | 13892208.6782928 London, Ontario | London, England | 5875787.03777356(3 rows)
参考:http://blog.csdn.net/gyfang/article/details/11661575
阅读全文
0 0
- greenplum使用postgis例子
- Greenplum 4.3.x 安装postgis
- Chapter 13 Greenplum PostGIS Extension
- greenplum一例子
- PostgreSQL+PostGIS的使用
- PostgreSQL+PostGIS的使用
- postGIS简单使用
- PostgreSQL+PostGIS的使用
- PostGres+postGis 初级使用
- Greenplum使用简明手册
- Greenplum中使用regexp_like
- Greenplum client Tools使用
- Greenplum索引使用
- Greenplum gpfdist使用
- Greenplum 基本使用命令
- postgis
- PostGis
- postgis
- spring boot 注册类型转换 (转载)
- linux 服务器部署 lnamp 三、php安装
- C++保留小数点后两位
- LeetCode 257 Binary Tree Paths (DFS)
- 暑期学习 DCGAN 笔记
- greenplum使用postgis例子
- linux环境搭建
- Python发送请求代token
- 中国移动申请FDD牌照加码物联网_你怎么看?
- codility PermCheck
- android RecyclerView 通用适配器
- C# DataGridView控件中点击选中行颜色更改
- EL表达式
- 定义线程并启动它的三种方法