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