PostGIS批量导入导出shapefile文件

目前，数据库中有表global_24h 和 countries，分别记录了热点信息和国家信息。

在pgAdmin中查看global_24h如下：

1.查询坐标系信息。

SELECT ST_SRID(the_geom) from global_24h;
SELECT ST_SRID(the_geom) from countries;

查询结果表明：countries的坐标系为EPSG:4326，global_24h 的几何坐标系为EPSG:3857，

首先，需要将表global_24h转换到坐标系EPSG:4326。

2.转换坐标系

ALTER TABLE  global_24h  
ALTER COLUMN the_geom
SET DATA TYPE geometry(Point,4326)
USING ST_Transform(the_geom,4326)

现在，两张表的坐标系都为EPSG:4326。

3.统计每个国家的热点个数

select c.name, MIN(c.iso2) as ios2,count(*) as spot_count 
from global_24h as f 
join countries as c 
on ST_Contains(c.the_geom, f.the_geom) group by c.name order by c.name

在pgAdmin中的显示如下：

4. 将查询结果写入csv文件

ogr2ogr -f  CSV  hs_countries.csv -sql "select c.name, MIN(c.iso2) as ios2,count(*) as spot_count from global_24h as f join countries as c on ST_Contains(c.the_geom, f.the_geom) group by c.name order by c.name"  PG:"host=localhost port=5432 dbname=postgis_32_sample user=postgres password=postgres schemas=public" 

5.批量生成每个国家热点信息的shapefile

先从hs_countries.csv中获取name, ios2 和 count，分别赋值给%%a, %%b，%%c。然后用一个for循环遍历hs_countries.csv中的每一行记录，分别生成对应的shapefile。

@echo off
for /f "tokens=1-3 delims=, skip=1" %%a in (hs_countries.csv) do (
    echo "Generating shapefile %%b.shp for country %%a (%%b) containing %%c features"
    ogr2ogr out_shapefiles/%%b.shp PG:"dbname='postgis_32_sample' user='postgres' password='postgres'"  -sql "SELECT ST_Transform(hs.the_geom, 4326), hs.acq_date, hs.acq_time, hs.bright_t31 FROM global_24h as hs JOIN countries as c ON ST_Contains(c.the_geom, ST_Transform(hs.the_geom, 4326)) WHERE c.iso2 = '%%b'"
)

生成的结果如下：

      在QGIS中查看国家Brazil（BR）中的热点。

6.批量上传每个国家热点信息的shapefile

      遍历文件夹中的shp文件，依次上传到postgis。

@echo off
for %%I in (out_shapefiles\*.shp*) do (
    echo Importing shapefile %%~nxI to public.hs_uploaded PostGIS table...

ogr2ogr -append -update  -f PostgreSQL PG:"dbname='postgis_32_sample' user='postgres' password='postgres'" out_shapefiles/%%~nxI -nln public.hs_uploaded -sql "SELECT acq_date, acq_time, bright_t31, '%%~nI' AS iso2, '%date%' AS upload_datetime, 'out_shapefiles/%%~nxI' as shapefile FROM %%~nI"
)

7.查询上传结果

select * from hs_uploaded where ISO2='AT'

在pgAdmin中的显示如下：