GEOJSON文件导入各类数据库的方式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

INSTALL spatial;
LOAD spatial;
CREATE TABLE world AS SELECT * FROM ST_Read('C:\Users\......\Desktop\testspatialite\world.geojson');
SELECT name_zh FROM world WHERE ST_Contains(geom,ST_Point(92.5976562500021, 42.58220940617247));
┌────────────────┐
│    name_zh                       │
│    varchar                         │
├────────────────┤
│ 中华人民共和国               │
└────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

import sqlite3
import pandas as pd
import geopandas as gpd
import uuid


pd.options.display.max_rows = 10
gdf = gpd.read_file('world.geojson') # `world.geojson`(大小：`23+MB`) 生成自 `https://geojson-maps.ash.ms`，选择 `High resolution` 加 `All`。

# 为每条记录生成一个唯一的 UUID，便于后面更新时找到关联点
gdf['uuid'] = gdf.apply(lambda _: "".join(str(uuid.uuid4()).split('-')), axis=1)

DB_PATH = "test_save.db"
# 先创建普通 df
df = gdf.drop(columns=['geometry'])


with sqlite3.connect(DB_PATH) as conn:
    # 在数据库中创建普通表
    df.to_sql("world_geo", conn, if_exists='replace', index=False)

    # 新增地理列
    conn.enable_load_extension(True)
    conn.load_extension("mod_spatialite")
    conn.execute("SELECT InitSpatialMetaData(1);")
    conn.execute(
        """
        SELECT AddGeometryColumn('world_geo', 'geometry', 4326, 'GEOMETRY', 2);
        """
    )


with sqlite3.connect(DB_PATH) as conn:
    conn.enable_load_extension(True)
    conn.load_extension("mod_spatialite")

    # 所有要插入的数据的列表
    data_to_update = [(row['geometry'].wkt, row['uuid']) for _, row in gdf.iterrows()]

    # 使用 executemany 一次性执行所有插入
    conn.executemany("UPDATE world_geo SET geometry = GeomFromText(?, 4326) WHERE uuid = ?", data_to_update)
    conn.commit()

    # 写入完毕创建空间索引
    conn.execute("SELECT CreateSpatialIndex('your_table', 'geometry_column');")

    # 如有需要删除uuid列（过程中新增的列）
    conn.execute("ALTER TABLE world_geo DROP COLUMN uuid;")

    conn.commit()
conn.close()

1

pip install sqlalchemy psycopg2-binary geoalchemy2 geopandas psycopg2 -i https://pypi.tuna.tsinghua.edu.cn/simple

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

import geopandas as gpd
from sqlalchemy import create_engine


gdf = gpd.read_file('world.geojson',encoding='utf-8')
# 连接字符串
db_string = "postgresql://user:xxxxx@host........./dbname?sslmode=require"

# 创建数据库引擎
engine = create_engine(db_string)
gdf.to_postgis(name='geo_table', con=engine, if_exists='replace', index=False)

1

SELECT name_zh FROM public.geo_table WHERE ST_Contains(geometry,ST_SetSRID(ST_MakePoint(92.5976562500021, 42.58220940617247),4326));