计算业务空间面积 ## 前置条件 outline信息点不为空, 且格式正确 ## 函数 ``` CREATE OR REPLACE FUNCTION calc_area_v3(json_poly jsonb) RETURNS float8 AS $$ from shapely.geometry import Polygon import json def meta_polygon_area(single_poly): try: poly_len = len(single_poly) poly = [] for i in range(poly_len): pair = single_poly[i] poly.append((pair["X"], pair["Y"])) p = Polygon(poly) return p.area except Exception as e: plpy.info(e) return 0.0 def get_area(polygons): length = len(polygons) if length == 0: return 0.0 total_area = 0.0 for j in range(length): polygon = polygons[j] single_area = meta_polygon_area(polygon) if single_area == 0.0: return 0.0 if j == 0: total_area = single_area else: total_area -= single_area return total_area try: polygon_list = json.loads(json_poly) total_len = len(polygon_list) area = 0.0 for index in range(total_len): single_area = get_area(polygon_list[index]) if single_area == 0: return None area += single_area return area except Exception as e: plpy.info(e) return None $$ LANGUAGE 'plpython3u' VOLATILE; ``` ## 入参 1. 业务空间的outline ## 例子 select id, outline, local_name, calc_area_v3(outline)/1000000 as "area(m2)" from public.zone_air_conditioning where outline is not null; ## 其他 Python的Shapely包文档地址 https://shapely.readthedocs.io/en/latest/manual.html