多边形表示方式:geom (lng lat, lng lat)
多边形向外等距扩展50m 0.0005表示50
SELECT
ST_AsText(
st_buffer (
'POLYGON((' || A .WGS84 || '))',
0.0005
)),a.id
FROM
hospital_area_new_result_id A
以经纬度一点画半径为50m的圆
SELECT
ST_AsText(
st_buffer (
'POINT('|| b.longitude || ' ' || b.latitude ||')',
0.0005
)),a.id
FROM
hospital_area_new_result_id A
判断经纬度点是否在多边形内
SELECT
ST_Contains (
ST_Transform (
st_geometryfromtext (
'POLYGON((' || A .WGS84 || '))',
4326
),
4527
),
ST_Transform (
st_geometryfromtext (
'POINT(' || b.longitude || ' ' || b.latitude || ')',
4326
),
4527
)
)
FROM
hospital_area_new_result_id_center A ,food_hospital_table_20191212 b
点是否在指定半径的圆内
SELECT
ST_DWithin (
ST_Transform (
st_geometryfromtext (
--圆心
'POINT(113 30)',
4326
),
4527
),
ST_Transform (
st_geometryfromtext (
--点
'POINT(116.4680989087 39.9486998528)',
4326
),
4527
),
--半径
500
)
使用图层展示
SELECT
ST_AsText ('POLYGON((' || "WGS84" || '))')
FROM
reptile
求两个多边形相交的区域面积
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,
hospital_table b
LIMIT 100
求多边形的面积
SELECT
ST_Area (
ST_AsText (
ST_Transform (
st_geometryfromtext (
'POLYGON((' || wgs84 || '))',
4326
),
4527
)
)
)
FROM
hospital_area_new_result_id
求多边形的中心位置SELECT
st_astext (
ST_Centroid ('POLYGON((' || wgs84 || '))')
)
FROM
hospital_area_new_result_id