postgis 是pg的一个插件,提供空间函数等的功能
供参考:
ST_Intersects:求点与区域的交集、区域与区域的交集
ST_Area:求边框围成的多边形的面积(收尾要闭合)
4325 、 4527 两个是固定的,可以将经纬度的多边形转成实际的面积,对应资料参考 https://www.jianshu.com/p/be5049ad8884
SELECT
ST_Intersects (
ST_Transform (
st_geometryfromtext (
--'POLYGON(('+ select "WGS84" from v_a_adhoc_cfg_cell_antenna limit 1 +'))',
'POLYGON((116.4679312706 39.9482801227,116.4677961543 39.9486461337,116.4680989087 39.9486998528,116.4682182670 39.9483181633,116.4679312706 39.9482801227))',
4326
),
4527
),
ST_Transform (
st_geometryfromtext (
'POINT(116.4680989087 39.9486998528)',
4326
),
4527
)
);
实际应用:
CREATE TABLE area_and_food AS
SELECT
ST_Area (
ST_AsText (
ST_Intersection (
ST_Transform (
st_geometryfromtext (
'POLYGON((' || A ."WGS84" || '))',
4326
),
4527
),
ST_Transform (
st_geometryfromtext (
'POLYGON((' || b."WGS84" || '))',
4326
),
4527
)
)
)
),
A ."cgi"
FROM
area_relation_scenes A,
(
SELECT
"WGS84"
FROM
food_table
) b