介绍
在本文中，我将解释如何访问与DuckDB的Overture Maps Foundation发表的兴趣点（POI）数据以及如何进行空间分析。
什么是DuckDB？
DuckDB旨在支持分析查询工作负载，也称为在线分析处理（OLAP）。它包括一个柱状查询执行引擎。这比传统系统（例如PostgreSQL，MySQL或SQLite）更具性能，该系统顺序处理每行。有许多可用的插件。您可以在使用插件的环境中轻松地传输数据，例如Amazon S3，Google Cloud Storage，PostgreSQL。您可以通过安装空间插件来执行空间分析。
环境设置
pip install duckdb==0.8.1
创建数据库
import duckdb
db = duckdb.connect("data.db")
安装插件供数据访问和空间分析
我们安装“空间”插件以执行空间分析。
我们正在安装“ HTTPFS”插件，以在Amazon S3中访问POI数据。然后我们将该区域定义为“ us-west-2”。
db.sql("""
INSTALL spatial;
INSTALL httpfs;
LOAD spatial;
LOAD httpfs;
SET s3_region='us-west-2';
""")
我们在数据库中创建一个表，并以镶木格式传输数据。大约34分钟内将5900万行数据传输到数据库。
db.sql("""
create table places as
select * from read_parquet('s3://overturemaps-us-west-2/release/2023-07-26-alpha.0/theme=places/type=*/*')
""")
db.sql("""
select count(*) as count from places
""").show()
db.sql("""
select * from places limit 5
""").show()
您可以找到poi数据here的图。数据中有一些我们需要预处理的列。
db.sql("""
select names, categories, confidence,brand,addresses from places limit 5
""").show()
例如，为了找出类别列中的类别，我们需要从“ struct”类型中保存的数据中获取信息。您可以查看文档以了解DuckDB数据类型。
例如，提取您位于“地址”列中的哪个国家：
db.sql("""
select replace(json_extract(CAST(addresses AS JSON), '$[0].country')::varchar,'"','') as country from places limit 5
""").show()
在创建了一个名为“国家提取国家短名称”的列后，我们添加了提取的数据。
db.sql("""ALTER TABLE places ADD COLUMN country VARCHAR;
update places set country = replace(json_extract(CAST(places.addresses AS JSON), '$[0].country')::varchar,'"','')
""")
我们运行以下查询，将土耳其中的POI数据添加到单独的表格并获取地址，类别，名称，几何信息。
db.sql("""
create or replace table turkey_places as (
select
replace(json_extract(places.addresses::json,'$[0].locality'),'"','')::varchar as locality,
replace(json_extract(places.addresses::json,'$[0].region'),'"','')::varchar as region,
replace(json_extract(places.addresses::json,'$[0].postcode'),'"','')::varchar as postcode,
replace(json_extract(places.addresses::json,'$[0].freeform'),'"','')::varchar as freeform,
categories.main as categories_main,
replace(json_extract(places.names::json,'$.common[0].value'),'"','')::varchar as names,
confidence,
bbox,
st_transform(st_point(st_y(st_geomfromwkb(geometry)),st_x(st_geomfromwkb(geometry))),'EPSG:4326','EPSG:3857') as geom
from places
where country ='TR'
)
""")
创建的表：
db.sql("""
select * from turkey_places limit 5
""").df()
举例来说，我将检查伊斯坦布尔的POI数据。我创建了两个桌子，以获取指定公园点500 m以内的POI点。
db.sql("""
create or replace table park_ist as (
select * from turkey_places where locality = 'İstanbul' and categories_main='park'
);
create or replace table poi_ist as (
select * from turkey_places where locality = 'İstanbul' and categories_main <> 'park'
)
""")
伊斯坦布尔的毒药数量：
db.sql(
"""
select count(*) from poi_ist
"""
)
'''
count
181959
'''
在伊斯坦布尔指定为公园的毒药数量：
db.sql(
"""
select count(*) from park_ist
""")
'''
count
492
'''
在公园类别中包含的点的500 m内查询POI点：
df = db.sql("""
select poi_ist.region as poi_ist_region,poi_ist.freeform as poi_ist_freeform,poi_ist.categories_main as poi_ist_categori ,
park_ist.categories_main as park_categori , park_ist.names as park_names, park_ist.freeform as park_ist_freeform,
st_distance(poi_ist.geom,park_ist.geom) as dist,
ST_AsText(poi_ist.geom) as geom,
ST_AsText(park_ist.geom) as geom2
from poi_ist, park_ist
where ST_DWithin(poi_ist.geom, park_ist.geom,500)
""").to_df()
gdf = gpd.GeoDataFrame(df,geometry= gpd.GeoSeries.from_wkt(df['geom']),crs="EPSG:3857")
gdf.to_file("export/poi.geojson",driver="GeoJSON")
我将结果表转换为PANDAS DataFrame格式。然后，我使用geopandas将表作为Geojson格式保存。我通过QGIS通过类别信息进行了可视化。
POI数据可以更详细地检查。我想告诉您如何与DuckDB一起使用空间数据。我希望这很有用。希望在下一篇文章中见到你。
来源：
Overture Maps Foundation
POI Data
DuckDB
DuckDB Spatial extension
DuckDB HTTPFS extension