postgresql - 如何有效地将大型 PostGIS (PostgreSQL) 表导出到 GeoJSON 文件？

Question

我建立了一个较大的 PostgreSQL 数据库，主要包含地理空间数据。我目前正在尝试将一些数据导出为 GeoJSON 格式，以便我可以将其平铺并将其与一些 map (Mapbox)一起使用。
对于我使用的第一个数据集，我编写了一个快速脚本，使用以下 ogr2ogr命令将数据导出为 GeoJSON 格式。

ogr2ogr -f GeoJSON \
  -progress \
  nhd_$2.json \
  "PG:dbname=$PG_DB host=$PG_HOST port=$PG_PORT user=$PG_USERNAME password=$PG_PASSWORD" \
  -sql "select resolution, geom from nhd_hr_$2"

由于导出的 GeoJSON 文件很大，我使用了 geojsplit将大的 GeoJSON 文件分解为较小的子文件，然后我就可以使用 Mapbox tiling tool创建我的图块，然后为我的 map 创建一个图层。
但是，现在我已经转向更大的数据库，我不断遇到问题，即我与数据库的连接在仅下载 12-15GB 的数据后就会超时。
我最初的想法是将我的查询拆分为子查询，但我不完全确定如何做到这一点。有什么方法可以改变我导出数据的方法吗？或者有没有办法让我将此查询分解为更易于管理的块？

Answer 1

使用 seq生成数字，然后更改您的查询以查询一系列 id 值。
我改了$2至 $suffix在本例中，IRL 将删除 echo一旦您对实际运行命令的输出感到满意(或者您可以将输出复制/粘贴到另一个终端)。你会想要改变 2000安全高于最大 ogc_fid(select ogc_fid from whatever order by 1 desc limit 1 以找到它)，并且可能需要更改 1000到其他一些块大小，具体取决于每行的大小。不要忘记也更改 1000但是，如果您这样做，则在 WHERE 子句中。

$ seq 0 500 1500
0
500
1000
1500
$ for suffix in foo bar; do
> for each in $(seq 0 1000 2000); do
> echo ogr2ogr -f GeoJSON -progress nhd_$suffix.json "PG:dbname=$PG_DB host=$PG_HOST port=$PG_PORT user=$PG_USERNAME password=$PG_PASSWORD" \
>  -sql "select resolution, geom from nhd_hr_$suffix where ogc_fid>=$each and ogc_fid < ($each + 1000)"
>  done
> done
ogr2ogr -f GeoJSON -progress nhd_foo.json PG:dbname= host= port= user= password= -sql select resolution, geom from nhd_hr_foo where ogc_fid>=0 and ogc_fid < (0 + 1000)
ogr2ogr -f GeoJSON -progress nhd_foo.json PG:dbname= host= port= user= password= -sql select resolution, geom from nhd_hr_foo where ogc_fid>=1000 and ogc_fid < (1000 + 1000)
ogr2ogr -f GeoJSON -progress nhd_foo.json PG:dbname= host= port= user= password= -sql select resolution, geom from nhd_hr_foo where ogc_fid>=2000 and ogc_fid < (2000 + 1000)
ogr2ogr -f GeoJSON -progress nhd_bar.json PG:dbname= host= port= user= password= -sql select resolution, geom from nhd_hr_bar where ogc_fid>=0 and ogc_fid < (0 + 1000)
ogr2ogr -f GeoJSON -progress nhd_bar.json PG:dbname= host= port= user= password= -sql select resolution, geom from nhd_hr_bar where ogc_fid>=1000 and ogc_fid < (1000 + 1000)
ogr2ogr -f GeoJSON -progress nhd_bar.json PG:dbname= host= port= user= password= -sql select resolution, geom from nhd_hr_bar where ogc_fid>=2000 and ogc_fid < (2000 + 1000)

下面是一行中的命令，以便于复制/粘贴到您的 shell:

for suffix in foo bar; do for each in $(seq 0 1000 2000); do echo ogr2ogr -f GeoJSON -progress nhd_$suffix.json "PG:dbname=$PG_DB host=$PG_HOST port=$PG_PORT user=$PG_USERNAME password=$PG_PASSWORD"  -sql "select resolution, geom from nhd_hr_$suffix where ogc_fid>=$each and ogc_fid < ($each + 1000)";  done; done