pgsql 常用语句

pgsql 常用语句

添加postgis插件

-- postgis
CREATE EXTENSION postgis;
-- uuid
create extension "uuid-ossp";

postgis 空间函数

-- 将线转为空间geom 
SELECT st_geomfromtext('MULTILINESTRING((121.471552 31.251867,121.468552 31.252916))', 4490);

-- 将点转为空间geom
select st_geomfromtext('point(121.471552 31.251867)',4490);

-- 求两个点的距离 单位米
select ST_Distance(geom1,geom2,false) from table_geom;

-- 获取两条线的相交点
select ST_Intersection(geom1, geom2,4490) from table_geom;

-- 获取geom中心点
select st_centroid(geom) from table_geom;

-- 获取WKT字符串
select st_astext(geom) from table_geom;

-- 查询 geom 的WKID
select st_srid(geom) from table_geom GROUP BY st_srid(geom);

-- 修改 geom 的WKID
select UpdateGeometrySRID('table_geom', 'geom', 4490);

-- 包含关系 A 包含 B
select st_within(geomA,geomB) from table_geom;

-- 已113.428713 23.185553为中心点 缓冲10000 缓冲区
select ST_BUFFER(Geography((st_geomfromtext('POINT (113.428713 23.185553)'))),10000)

-- 两个geom 相交部分 （获取航线）
SELECT ST_ASTEXT(( SELECT ST_intersection((select ST_BUFFER(Geography((st_geomfromtext('POINT (113.428713 23.185553)'))),10000)),(select geom from data_bghj))))

查询表字段-注释、表名-表注释

-- (data_tx_xx_point)表字段-注释
SELECT a.attname as "key", col_description(a.attrelid,a.attnum) as "value"
FROM pg_class as c, pg_attribute as a, pg_type as t
WHERE c.relname = 'data_tx_xx_point'
and a.atttypid = t.oid and a.attrelid = c.oid and a.attnum>0

-- （data_%_%_line） 表名-表注释
select relname as tabname,cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c 
where  relkind = 'r' and relname  like 'data_%_%_line' or relname  like 'data_%_%_point' and relchecks=0 order by relname

pgsql库信息

--查看数据库
select * from pg_database;

--查看表空间
select * from pg_tablespace;

--查看语言
select * from pg_language;

--查看角色用户
select * from pg_user;
select * from pg_shadow;
select * from pg_roles;

--查看会话进程
select * from pg_stat_activity;

--查看表
SELECT * FROM pg_tables where schemaname = 'public';

--查看表字段
select * from information_schema.columns where table_schema = 'public' and table_name = 'pf_vip_org';

--查看视图
select * from pg_views where schemaname = 'public';
select * from information_schema.views where table_schema = 'public';

--查看触发器
select * from information_schema.triggers;