Oracle和Postgis数据库地理坐标系下面积计算

要求：计算投影面积，单位公顷，保留4位小数

Oracle

round(
(st_area(
st_transform(shape,
(
floor((st_x(st_centroid(shape))+1.5)/3)
-25+4513))
)
/10000),
4)

Postgis

round(
(st_area(
st_transform(geom,
(
floor((st_x(st_centroid(geom))+1.5)/3)
-25+4513)::int4)
)
/10000)::numeric,
4)

计算思路：

1、获取图形中心点X坐标

Centroid_X = st_x(st_centroid(geom))

2、计算3度带带号

3_degree=floor((Centroid_X+1.5)/3)

3、计算3度带带号对应SRID

SRID	COORD_REF_SYS_NAME	X
4513	CGCS2000 / 3-degree Gauss-Kruger zone 25	75
4514	CGCS2000 / 3-degree Gauss-Kruger zone 26	78
4515	CGCS2000 / 3-degree Gauss-Kruger zone 27	81
4516	CGCS2000 / 3-degree Gauss-Kruger zone 28	84
4517	CGCS2000 / 3-degree Gauss-Kruger zone 29	87
4518	CGCS2000 / 3-degree Gauss-Kruger zone 30	90
4519	CGCS2000 / 3-degree Gauss-Kruger zone 31	93
4520	CGCS2000 / 3-degree Gauss-Kruger zone 32	96
4521	CGCS2000 / 3-degree Gauss-Kruger zone 33	99
4522	CGCS2000 / 3-degree Gauss-Kruger zone 34	102
4523	CGCS2000 / 3-degree Gauss-Kruger zone 35	105
4524	CGCS2000 / 3-degree Gauss-Kruger zone 36	108
4525	CGCS2000 / 3-degree Gauss-Kruger zone 37	111
4526	CGCS2000 / 3-degree Gauss-Kruger zone 38	114
4527	CGCS2000 / 3-degree Gauss-Kruger zone 39	117
4528	CGCS2000 / 3-degree Gauss-Kruger zone 40	120
4529	CGCS2000 / 3-degree Gauss-Kruger zone 41	123
4530	CGCS2000 / 3-degree Gauss-Kruger zone 42	126
4531	CGCS2000 / 3-degree Gauss-Kruger zone 43	129
4532	CGCS2000 / 3-degree Gauss-Kruger zone 44	132
4533	CGCS2000 / 3-degree Gauss-Kruger zone 45	135

srid=3_degree-25+4513

4、地理坐标系转投影坐标系

Oracle

geometry=st_transform(shape,srid)

Postgis

geometry=st_transform(shape,srid::int4)

注：srid需要类型转换，否则报错误: 函数 st_transform(geometry, double precision) 不存在

因为floor()结果为double类型，postgis支持的函数为st_transform(geometry, int4)

5、计算面积

area=st_area(geometry)

6、平方米转公顷，保留4位小数

Oracle

area_hectares=round(area/10000,4)

Postgis

area_hectares=round((area/10000)::numeric,4)

注：(area/10000)需要类型转换，否则报错误: 函数 round(double precision, integer) 不存在

因为(area/10000)为double类型，postgis支持的函数为round(numeric, int4)