pg常用空间函数

SELECT st_srid(geom) FROM qysjsx_qyjcxx where uni_scid = '91440300MA5ER4GH88';--查询坐标系，结果样式：4326

// 按经纬度设置坐标
update temp_gaoxin_qiye_address_location set geom=st_setsrid(st_makepoint(lng,lat), 4326) where geom is null;

// 单点经纬度转换坐标
SELECT ST_GeomFromText('point(114.10374533565522 22.7301637684997)', 4326);

//多点经纬度转换坐标
select st_geomfromtext('multipoint(-92.25672304173123 31.882691687797774, -85.8756260475443 -6.1003601818713875, -78.37903525717024 -43.85523684506681)',4326) ;

//线转换成geom（也就是多个点组成的线）
select st_geomfromtext('linestring (-92.25672304173123 31.882691687797774, -85.8756260475443 -6.1003601818713875, -78.37903525717024 -43.85523684506681)',4326);

//多个线转换成geom
select st_geomfromtext ('multilinestring((-92.25672304173123 31.882691687797774, -85.8756260475443 -6.1003601818713875,-78.37903525717024 -43.85523684506681))',4326);

//面转换geom
SELECTst_geomfromtext ('polygon((10.02 20.01, 11.92 35.64, 25.02 34.15,19.15 33.94, 10.02 20.01))',4326);

//多面转换geom
select st_geomfromtext ('multipolygon (((10.02 20.01, 11.92 35.64,25.02 34.15, 19.15 33.94, 10.02 20.01), (51.71 21.73, 73.36 27.04,71.52 32.87, 52.43 31.90, 51.71 21.73)))',4326);

update qiye_zdsys set geom = st_geomfromtext(ST_AsText(geom),4326) where st_srid(geom)=0;

//查询在多边形区域内的数据，t2.geom是MULTIPLOYGON格式的多边形，t1.geom是坐标点
select t1.id from temp_gaoxin_qiye_address_location t1 LEFT JOIN cyyq_pq t2 on st_contains(t2.geom, t1.geom) where t2.is_deleted = false;
//判断A是否覆盖 B
ST_Covers(geometry A, geometry B)
//判断A是否被B所覆盖
ST_CoveredBy(geometry A, geometry B)

//geom转经纬度
select st_astext(st_geomfromtext('multipolygon(((10.02 20.01,11.92 35.64,25.98 34.25,19.08 34.87,10.02 20.01),(10.02 20.01,11.92 35.64,25.98 34.25,19.08 34.87,10.02 20.01)))',4326))

// 返回坐标，带格式，如{"type":"Point","coordinates":[113.949625252751,22.5424488086305]}
或{"type":"MultiPolygon","coordinates":[[[[113.906828478339,22.7679776325292],[113.906386895734,22.7678553691694],[113.906337577598,22.7680144154907],[113.906776944337,22.7681424929076],[113.906828478339,22.7679776325292]]]]}
select st_asgeojson(geom) geom from building_info where building_id = 84266;

//获取几何对象的中心
ST_Centroid(geometry)