如何从Bounding Box提取含相交/包含的几何数据并复制到新空数据库？

Hey there! Since you're new to PostgreSQL + PostGIS and already have your full OSM Germany data loaded into germanyPG, here are a few straightforward, reliable approaches to extract geometries that intersect or are contained within your target bounding box and copy them over to your empty subsetGermanyPG database:

方案1：跨数据库SQL复制（使用dblink扩展） #

This method lets you directly pull filtered data from germanyPG into subsetGermanyPG using SQL, no intermediate files needed.

步骤： #

1. 启用dblink扩展（跨数据库连接必需），在目标库执行：

   -- 运行在subsetGermanyPG中
   CREATE EXTENSION IF NOT EXISTS dblink;
   

2. 复刻源库表结构：osm2pgsql会生成planet_osm_point、planet_osm_line等标准表，先在目标库创建匹配的表：

   -- 复制源库表的完整结构
   CREATE TABLE IF NOT EXISTS planet_osm_point (LIKE germanyPG.public.planet_osm_point INCLUDING ALL);
   CREATE TABLE IF NOT EXISTS planet_osm_line (LIKE germanyPG.public.planet_osm_line INCLUDING ALL);
   CREATE TABLE IF NOT EXISTS planet_osm_polygon (LIKE germanyPG.public.planet_osm_polygon INCLUDING ALL);
   -- 如果需要其他OSM表（如planet_osm_roads），同理添加
   

3. 插入过滤后的数据：替换ST_MakeEnvelope(minlon, minlat, maxlon, maxlat, 4326)中的坐标为你的目标边界框（最后一个参数4326是OSM默认的WGS84坐标系EPSG编码）：

   -- 插入与边界框相交/包含的点数据
   INSERT INTO planet_osm_point
   SELECT *
   FROM dblink(
     'dbname=germanyPG user=你的数据库用户名 password=你的密码 host=localhost',
     'SELECT * FROM planet_osm_point WHERE ST_Intersects(way, ST_MakeEnvelope(8.0, 50.0, 9.0, 51.0, 4326))'
   ) AS t(LIKE planet_osm_point);
   
   -- 对线表重复操作
   INSERT INTO planet_osm_line
   SELECT *
   FROM dblink(
     'dbname=germanyPG user=你的数据库用户名 password=你的密码 host=localhost',
     'SELECT * FROM planet_osm_line WHERE ST_Intersects(way, ST_MakeEnvelope(8.0, 50.0, 9.0, 51.0, 4326))'
   ) AS t(LIKE planet_osm_line);
   
   -- 对多边形表重复操作
   INSERT INTO planet_osm_polygon
   SELECT *
   FROM dblink(
     'dbname=germanyPG user=你的数据库用户名 password=你的密码 host=localhost',
     'SELECT * FROM planet_osm_polygon WHERE ST_Intersects(way, ST_MakeEnvelope(8.0, 50.0, 9.0, 51.0, 4326))'
   ) AS t(LIKE planet_osm_polygon);
   

   提示：ST_Intersects会同时匹配完全包含在边界框内和与边界框相交的几何数据，刚好满足你的需求。

方案2：pg_dump子集导出 + 导入 #

这是更简单的命令行方案，适合不想处理跨库连接的情况。它只导出germanyPG中你需要的数据，再导入到目标库。

步骤： #

1. 导出过滤后的子集：替换边界框坐标，按需添加/移除表名：

   pg_dump -d germanyPG \
     -t planet_osm_point -t planet_osm_line -t planet_osm_polygon \
     --where="ST_Intersects(way, ST_MakeEnvelope(8.0, 50.0, 9.0, 51.0, 4326))" \
     -f osm_germany_subset.sql
   

2. 将子集导入目标库：

   psql -d subsetGermanyPG -f osm_germany_subset.sql
   

方案3：补充：从原始OSM数据直接生成子集（后续需求） #

如果你以后需要从头创建子集（而非从全量库提取），可以先从原始OSM PBF中提取边界框数据再导入：

# 用osmconvert从原始PBF提取边界框子集
osmconvert germany.osm.pbf -b=8.0,50.0,9.0,51.0 --out-pbf > germany_subset.pbf

# 用osm2pgsql将子集导入目标库（和你导入全量数据的方式一致）
osm2pgsql -d subsetGermanyPG germany_subset.pbf

关键注意事项 #

• 坐标系匹配：确保你的边界框使用与OSM数据相同的EPSG编码（几乎都是4326），如果数据用的是其他坐标系（如3857Web墨卡托），请修改ST_MakeEnvelope的最后一个参数。
• 重建空间索引：数据导入完成后，给目标库的表重建空间索引，提升后续查询速度：

  CREATE INDEX IF NOT EXISTS idx_planet_osm_point_way ON planet_osm_point USING GIST(way);
  CREATE INDEX IF NOT EXISTS idx_planet_osm_line_way ON planet_osm_line USING GIST(way);
  CREATE INDEX IF NOT EXISTS idx_planet_osm_polygon_way ON planet_osm_polygon USING GIST(way);
  

• 权限检查：确保你的数据库用户拥有germanyPG的查询权限，以及subsetGermanyPG的创建和插入权限。
• 小范围测试：先尝试用一个小边界框验证数据复制是否正确，再处理大面积数据。

内容的提问来源于stack exchange，提问作者Peter