如何将含XY列的SQLite/Pandas表转换为SpatiaLite表？

需求背景 #

SQLite数据库中存在一个含40+列的Pandas表，包含XY坐标数据，已通过Python代码获取到创建点要素所需的EPSG编码，需要将该表转换为SpatiaLite表，确保所有列都能在QGIS中正常查询。

已配置的Spatialite环境 #

import os
import sqlite3

spatialite_path = 'C:\Program Files (x86)\Spatialite'
os.environ['PATH'] = spatialite_path + ';' + os.environ['PATH']

# 连接数据库并启用Spatialite扩展
con = sqlite3.connect(db_name)
con.enable_load_extension(True)
con.load_extension("mod_spatialite")
con.execute("SELECT InitSpatialMetaData();")

# 涉及的表与变量
# 表：table_clean_wGIS, pandas_table_wXY, db_table_wXY
# 变量：db_name (字符串), EPSG_Code (整数)

已尝试的方法及问题 #

1. 旧版SpatiaLite Cookbook方法：手动创建仅含少量列的新表，添加geometry列并插入数据，此方法可行但无法保留原表的40+列。

   con = sqlite3.connect(db_name)
   cur = con.cursor()
   cur.execute('CREATE TABLE IF NOT EXISTS table_clean_wGIS (ID REAL PRIMARY KEY, UniquePointName TEXT DEFAULT 0, X DOUBLE DEFAULT 0, Y DOUBLE DEFAULT 0, Date TEXT )')
   
   cur.execute('SELECT AddGeometryColumn("table_clean_wGIS", "geometry",(?),"POINT",0)', (EPSG_Code,))
   cur.execute('SELECT CreateSpatialIndex("table_clean_wGIS","geometry")')
   cur.execute('INSERT INTO table_clean_wGIS(ID, UniquePointName,X,Y, Date, geometry) SELECT ID, UniquePointName, X,Y,Date,MakePoint(X,Y,?) FROM pandas_table_wXY', (EPSG_Code,))
   con.commit()
   con.close()
   

2. JOIN尝试：尝试用FULL OUTER JOIN合并表，但SQLite不支持该语法；改用双重LEFT JOIN后丢失了空间属性。

3. 直接INSERT几何列：为原表添加geometry列后执行INSERT语句，导致表行数翻倍，几何要素与行数据不匹配。

   con = sqlite3.connect(db_name)
   cur = con.cursor()        
   
   cur.execute('SELECT AddGeometryColumn ("db_table_wXY", "geometry",(?),"POINT",0)', (EPSG_Code,))
   cur.execute('SELECT CreateSpatialIndex("db_table_wXY","geometry")')
   cur.execute('INSERT INTO db_table_wXY(geometry) SELECT MakePoint(X,Y,?) FROM db_table_wXY', (EPSG_Code,))
   con.commit()
   con.close()
   

4. UPDATE设置几何：执行UPDATE语句看似成功，但生成的表无法在QGIS中显示。

   cur.execute('SELECT AddGeometryColumn ("db_pss", "geometry",(?),"POINT",0)', (EPSG_Code,))
   cur.execute('UPDATE db_pss SET geometry =MakePoint(X,Y,?)', (EPSG_Code,))
   cur.execute('SELECT CreateSpatialIndex("db_pss","geometry")')
   con.commit()
   con.close()
   

正确解决方案 #

方案1：从Pandas表直接生成SpatiaLite表 #

如果pandas_table_wXY尚未写入SQLite，可先写入再添加空间属性：

import pandas as pd
import sqlite3
import os

# 配置Spatialite环境
spatialite_path = 'C:\Program Files (x86)\Spatialite'
os.environ['PATH'] = spatialite_path + ';' + os.environ['PATH']

# 将Pandas表写入SQLite
con = sqlite3.connect(db_name)
pandas_table_wXY.to_sql('db_table_wXY', con, if_exists='replace', index=False)

# 启用Spatialite扩展并初始化元数据（仅需执行一次）
con.enable_load_extension(True)
con.load_extension("mod_spatialite")
con.execute("SELECT InitSpatialMetaData();")

cur = con.cursor()

# 确保表有主键（若原表无主键，先添加）
cur.execute('ALTER TABLE db_table_wXY ADD COLUMN id INTEGER PRIMARY KEY AUTOINCREMENT;')

# 添加geometry列
cur.execute('SELECT AddGeometryColumn("db_table_wXY", "geometry", ?, "POINT", 0)', (EPSG_Code,))

# 更新geometry列，将XY转换为点要素
cur.execute('UPDATE db_table_wXY SET geometry = MakePoint(X, Y, ?)', (EPSG_Code,))

# 创建空间索引
cur.execute('SELECT CreateSpatialIndex("db_table_wXY", "geometry")')

con.commit()
con.close()

方案2：处理已存在的SQLite表 #

针对已存在的db_table_wXY表，按以下步骤操作：

1. 确保表有主键：若原表无主键，先添加自增主键（避免更新时出现匹配错误）：

   ALTER TABLE db_table_wXY ADD COLUMN id INTEGER PRIMARY KEY AUTOINCREMENT;
   

2. 添加geometry列：使用AddGeometryColumn规范添加空间列，确保参数正确（EPSG编码、要素类型、维度）。
3. 更新几何属性：用UPDATE语句将每行的XY转换为点要素，确保行与几何一一对应。
4. 创建空间索引：提升QGIS中的查询性能。
5. 验证空间元数据：可查询geometry_columns表确认列配置正确：

   SELECT * FROM geometry_columns WHERE f_table_name = 'db_table_wXY';
   

关键注意事项 #

• 确保原表的X、Y列为数值类型（DOUBLE/FLOAT），否则MakePoint函数无法生成有效几何。
• InitSpatialMetaData()仅需在数据库首次使用Spatialite时执行一次，重复执行不会报错但没必要。
• QGIS无法显示时，可尝试：
  • 刷新图层列表或重新导入图层；
  • 检查geometry_columns表中该表的空间配置是否正确；
  • 验证几何要素是否有效（可执行SELECT ST_IsValid(geometry) FROM db_table_wXY;检查）。

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