PostgreSQL-CSV-POSTGIS相关

导出数据库中指定的行到CSV文件

copy (select * from "BH"."BHpoint_" LIMIT 50000000) to 'D:\CSV_sateg03\csv_BOHAI_POINT\BH_p01.CSV' with csv;
copy (select * from "BH"."BHpoint_" LIMIT 56438087 OFFSET 50000000) to 'D:\CSV_sateg03\csv_BOHAI_POINT\BH_p02.CSV' with csv;

查询当前表单中有多少行数据 

select count(imo) FROM "BH"."BHpoint_";

在postgreSQL数据库中创建POSTGIS扩展（前提先安装好 POSTGIS插件 使用PostgreSQL数据库的Stack Builder下载安装即可  但 要注意版本的对应  比如本例子中的 PostgreSQL  11 对应使用 POSTGIS2.5 版本）

CREATE EXTENSION postgis;

查看当前数据库的用户名

SELECT * FROM current_user;

把csv文件导入到pg数据库的指定表单中

copy pg_table(表字段1,表字段2,表字段3,...,表字段n) from '/home/*/needtoinputfile.csv' delimiter ',' csv header;

把pg数据库的指定表单数据导出到csv文件中

COPY (SELECT * FROM "BH"."BHpoint" LIMIT 30000000) to 'D:\CSV\BH_3KW.CSV' WITH csv HEADER;

表1："BH_WXQHC"

表2："BH_3KW"

查询在"BH_WXQHC"面要素内部的"BH_3KW"点要素 ，ST_CONTAINS函数。

select ST_CONTAINS("BH_WXQHC".geom,"BH_3KW".geom) from "BH_WXQHC","BH_3KW"

将查询到符合条件的点导出至CSV文件

create view inarea AS select ST_CONTAINS("BH_WXQHC".geom,"BH_3KW".geom) as mm, imo,lon,lat from "BH_WXQHC","BH_3KW";
-- SELECT mm,imo,lat,lon from inarea where mm='t';
-- select count(*) from 
-- COPY (select ST_CONTAINS("BH_WXQHC".geom,"BH_3KW".geom), imo,lon,lat from "BH_WXQHC","BH_3KW") TO 'D:\csv_test\CONTAINS.CSV' WITH CSV HEADER;
COPY (SELECT mm,imo,lat,lon from inarea where mm='t') TO 'D:\csv_test\CONTAINS.CSV' WITH CSV HEADER