CalcArea.md 1.6 KB

计算业务空间面积

前置条件

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