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)
