PG库操作语句记录

1.修改geom坐标系修改为4326坐标系：

（1）update table tablename set geom = st_geomfromtext(st_astext(geom),4326)

（2）select UpdateGeometrySRID('tablename', 'geom', 4490)

2.PG库空间扩展：

CREATE EXTENSION postgis; CREATE EXTENSION pgrouting; CREATE EXTENSION postgis_topology; CREATE EXTENSION fuzzystrmatch; CREATE EXTENSION postgis_tiger_geocoder; CREATE EXTENSION address_standardizer;

3.获取面数据中心点：

update tablename set dgeom = st_centroid(mgeom)

4.查询两个空间表中相交内容：

select tb1.mc,tb2.mcfrom table1 tb1 left JOIN table2 tb2 on st_intersects(tb1.geom,tb2.geom) order by tb1.mc