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
