计算业务空间面积
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