postgresql和postgis

1，空间函数转经纬度

‘{"type":"FeatureCollection","features":[{"type":"Feature","properties":{},"geometry":‘||ST_AsGeoJSON(coverage)||‘}]}‘ coverage,

‘{ "geometry": ‘ || ST_AsGeoJSON(coverage) || ‘, "type":"Feature"}‘ coverage,

2，经纬度转空间函数

select st_setsrid(ST_GeomFromGeoJSON(‘

{"type": "Polygon", "coordinates": [[[120.80761195570969, 31.329696975424678], [120.80649536392833, 31.296134937732152], [120.80646400492863, 31.296046376357033], [120.80646400492863, 31.296046376357037], [120.80635839563071, 31.29574812371067], [120.80611854270235, 31.296411435576953], [120.80611854270235, 31.296411435576942], [120.79069918934609, 31.33905356682529], [120.79016551413488, 31.350078785609714], [120.81061067459304, 31.35526732981206], [120.81189797033552, 31.355496907765236], [120.82072206653757, 31.357308878135473], [120.80779904791106, 31.332198958168505], [120.80761195570969, 31.329696975424678]]]}

‘), 4326)

3，多列转一列

row(列名,列名,...)

4，转对象(json)

row_to_json(row(列名,列名,...))

5，一列多行转成一行

array_agg(row_to_json(row(列名,列名,...)))

6，把数组转json

array_to_json(array_agg(row_to_json(row(列名,列名,...)))) ::jsonb

7，替换字符串并把替换后的字符串转成json格式如果不转成json 可能在返回到java的是会有转义字符的

replace(replace(array_to_json(array_agg(row_to_json(row(id,name)))):: varchar,‘f1‘,‘id‘),‘f2‘,‘name‘) ::json

8，行转列

regexp_split_to_table(列名, ‘用什么拆分‘);

9，空值替换函数

COALESCE(col, ‘replacement‘) ：如果col列的值为null，则col的值将被替换为‘replacement‘

10，一列多行转成一行

string_agg(列名,‘分隔符比如 , - ‘order by 列名)

11，字符串或者非字符串拼接

1000||‘-‘||abc||‘+‘||200

12，边界求并集

select ST_union(gdGeom) gdGeom

FROM (

SELECT st_setsrid(ST_MakeValid(ST_UnaryUnion(ST_GeomFromGeoJSON(‘{"type":"Polygon","coordinates":[[[120.975638859303,31.3283192036255],[120.970778743191,31.3256145147033],[120.95857452115,31.3322375250399],[120.957987523149,31.3330245538613],[120.957330529509,31.334168580807],[120.953603682517,31.3462786322075],[120.94005602555,31.3612369822518],[120.950500009608,31.3735147729202],[120.9654383331,31.3813001827813],[120.982623372859,31.4101638657093],[120.97882130061,31.3646903719643],[120.977531230492,31.3603893943331],[120.973724988797,31.3483324737896],[120.975638859303,31.3283192036255]]]}‘::jsonb))),4326) gdGeom

UNION

SELECT st_setsrid(ST_MakeValid(ST_UnaryUnion(ST_GeomFromGeoJSON(‘{"type":"Polygon","coordinates":[[[121.028525687066,31.3832837168027],[121.0112065046,31.3830728706246],[121.004907657887,31.3843916777245],[120.983330471907,31.3930130747498],[120.977824498246,31.3972531187829],[120.977791498025,31.3972751200602],[120.977691410858,31.4062410329178],[120.974605209348,31.4164931702297],[120.979113193293,31.4221548919732],[120.981680159137,31.4265938447168],[121.017168116006,31.4494788604357],[121.018689975332,31.4220455451131],[121.039621070261,31.409067314981],[121.055906923596,31.4061392623603],[121.054691959784,31.3972050262552],[121.055987996826,31.3870949704915],[121.033364811293,31.3844573150417],[121.028525687066,31.3832837168027]]]}‘::jsonb))),4326) gdGeom

) as foo

13，查两个结果的差集

select unistore_id from retail.t_gaia_brp_precinct_conf_store where config_detail_id=‘ef5f26a35edf4da185fb44d1e146c936‘

except

select unistore_id from retail.t_gaia_brp_precinct_conf_store where config_detail_id=‘2fd189a8c321479d93f2b0b89c70aa6e‘

14，区域划分（分割）

ST_SubDivide ( ST_Transform(gd_geom, 4326), 2000 ) AS gd_geom

15，插入边界

st_setsrid(ST_GeomFromGeoJSON(#{gdGeom}),4326)

边界类型：

{\"type\": \"Polygon\", \"coordinates\": [[[120.85062407101668, 31.11844756766111], [120.8303575052602, 31.134457313892252], [120.83063856653047, 31.135044185845526], [120.89614290278149, 31.162664996771397], [120.89462190490472, 31.162730246718166], [120.85062407101668, 31.11844756766111]]]}

16，根据经纬度匹配边界

-- 这种经纬度可能会落在边界上

st_within (

ST_Transform ( ST_SetSRID ( ST_Point ( a.store_lng :: NUMERIC, a.store_lat :: NUMERIC ), 4326 ), 3857 ),

b.gdgeom

)

-- 这种经纬度只会落在边界内

ST_intersects (

ST_Transform ( ST_SetSRID ( ST_Point ( a.lng :: NUMERIC, a.lat :: NUMERIC ), 4326 ), 3857 ), -- ST_intersects

c.gd_geom

)

-- store_coordinate转换成4326，或者3857 格式后的空间函数

(  SELECT    ST_SubDivide ( ST_Transform(gd_geom, 4326), 2000 ) AS gd_geom  FROM retail.t_gaia_retail_bms_organize_info t1  WHERE t1.parent_id = #{organizeId}) aa  left join retail.t_gaia_brp_store_v3 ss  ON ST_intersects ( ss.store_coordinate, aa.gd_geom )

17，根据边界获取中心点

一般求边界中心点或者经纬度不建议使用ST_Centroid 该用ST_PointOnSurface

ST_X ( ST_Centroid ( ST_TRANSFORM ( area.gd_geom, 4326 ))) "gdLng",

ST_Y ( ST_Centroid ( ST_TRANSFORM ( area.gd_geom, 4326 ))) "gdLat"

ST_X ( ST_PointOnSurface( ST_TRANSFORM ( area.gd_geom, 4326 ))) "gdLng",

ST_Y ( ST_PointOnSurface( ST_TRANSFORM ( area.gd_geom, 4326 ))) "gdLat"

18，把查询出来的所有的点合成一个边界

　　　　　　select            st_Union((ta.gaode_polygon)) gdgeom        from retail.t_gaia_dcp_ai_tile ta        inner join retail.t_gaia_retail_bms_tradearea bb on bb.id= ‘72d7e3a54f774fbfabb6e9f07b03ed32‘            and st_within (            st_transform(st_setsrid(ST_Centroid(ta.gaode_polygon),3857),4326),            st_transform(bb.gdgeom,4326)            )

19，把点按照一定数值稀化（去掉多余的点）

select

a."name",

ST_Npoints(a.gd_boundar),

a.gd_boundar,

ST_Npoints(ST_SimplifyPreserveTopology(a.gd_boundar,100)),

ST_SimplifyPreserveTopology(a.gd_boundar,100),

ST_Npoints(ST_SimplifyPreserveTopology(a.gd_boundar,1000)),

ST_SimplifyPreserveTopology(a.gd_boundar,1000)

from retail.gaia_ocm_area a

limit 100

20，筛选汉字

select "substring"(emp_name, ‘[\u4e00-\u9fa5]*‘) as 中文名, emp_name from dataman.ods_org_emp limit 10

select regexp_replace(emp_name,‘[\d|a-z|A-Z]+‘,‘‘) as 中文名, emp_name from dataman.ods_org_emp limit 10

21，查询时间段之间的时间

-- 方式一

select date(t) as day from

generate_series(‘2019-11-11‘::date,‘2019-11-17‘, ‘1 days‘) as t;

-- 方式二

with recursive t(n) as (

select date(‘2019-11-11‘)

union all

select n+1 from t where n < date(‘2019-11-17‘)

)select n as day from t;

22，边界转成点

ST_AsGeoJSON(gd_geom)

类型是 MultiPolygon

23， json数组根据key取value值

SELECT a.store_id,b->>‘labelValue‘ AS label

FROM data_alo.store_label_monthly a,

json_array_elements(store_labels) AS b

WHERE b->>‘labelName‘=‘门店产值‘

24，pgsql 根据时间字符串算出是周几

SELECT extract(DOW FROM cast(‘2019-11-30‘ as TIMESTAMP));

周日是0

25，json和jsonb的区别

postgresql支持两种json数据类型： json和jsonb

区别：

而两者唯一的区别在于效率,

json是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。

而jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。

相同：

两者对重复键的处理都是保留最后一个键值对。效率的差别：json类型存储快，使用慢，jsonb类型存储稍慢，使用较快

26，边界精度计算

select

-- 正常情况下

a.gdgeom,

ST_Npoints ( a.gdgeom) ,

-- 缩小1000情况下

ST_SimplifyPreserveTopology(a.gdgeom,1000),

ST_Npoints ( ST_SimplifyPreserveTopology(a.gdgeom,1000)) ,

-- 缩小10000情况下

ST_SimplifyPreserveTopology(a.gdgeom,10000) ,

ST_Npoints ( ST_SimplifyPreserveTopology(a.gdgeom,10000))

FROM

retail.t_gaia_retail_bms_tradearea a

postgresql和postgis

83911930

相关推荐

CentOS7使用yum安装PostgreSQL和PostGIS的方法

Linux下安装Postgis数据库

PostgreSQL数据库的安装与PostGIS的安装

PostgreSQL PostGIS 的5种空间距离排序(knn)算法

[原] RStudio Spark/Leaflet 与 GIS 最佳实践

postgis导入shp数据

PostgreSQL PostGIS 的5种空间距离排序(knn)算法

RedHat7下源码安装PostGIS

在Ubuntu 12.04上部署PostGIS 2.1

详解Linux中PostgreSQL和PostGIS的安装和使用

GIS on CentOS 7 之 PostgreSQL & PostGIS