基于Risky Pathak的解决方案,这里提供了SQL(Redshift)计算GeoJSON多边形面积的解决方案(假设linestring 0是最外层多边形)。
create or replace view geo_area_area as
with points as (
select ga.id as key_geo_area
, ga.name, gag.linestring
, gag.position
, radians(gag.longitude) as x
, radians(gag.latitude) as y
from geo_area ga
join geo_area_geometry gag on (gag.key_geo_area = ga.id)
)
, polygons as (
select key_geo_area, name, linestring, position
, x
, lag(x) over (partition by key_geo_area, linestring order by position) as prev_x
, y
, lag(y) over (partition by key_geo_area, linestring order by position) as prev_y
from points
)
, area_linestrings as (
select key_geo_area, name, linestring
, abs( sum( (x - prev_x) * (2 + sin(y) + sin(prev_y)) ) ) * 6378137 * 6378137 / 2 / 10^6 as area_km_squared
from polygons
where position != 0
group by 1, 2, 3
)
select key_geo_area, name
, sum(case when linestring = 0 then area_km_squared else -area_km_squared end) as area_km_squared
from area_linestrings
group by 1, 2
;
p1.Longitude
和p2.Longitude
也转换为弧度。在进行这个修改后,我得到了与google.maps.geometry.spherical.computeArea
函数相似的结果。 - MC XgetGeodesicArea
非常相似(减去投影部分)。请参见:https://github.com/openlayers/openlayers/blob/v2.13.1/lib/OpenLayers/Geometry/LinearRing.js#L251 - Nux