>>> from env_helper import info; info()
页面更新时间： 2024-07-23 22:04:11
运行环境：
Linux发行版本: Debian GNU/Linux 12 (bookworm)
操作系统内核: Linux-6.1.0-23-amd64-x86_64-with-glibc2.36
Python版本: 3.11.2
8.7. 管理坐标参考与坐标转换¶
每个坐标值都是完全限定的域名，需要一个有明确标识。
此值标识了几何相关描述、几何对象定义的坐标空间。
作为一种普遍的规则，只有当它们的坐标是在同样的空间参考系统，不同的几何对象才能进行有意义的互操作。
在GIS中，一种常用的操作，是地理坐标重投影（coordinate reprojection）， 是将不同的GIS数据转换成唯一的的空间参考系统，然后进行一些互操作和集成。
当您尝试使用属于不同坐标参考系统的两个GIS数据集时，实体下降得非常远，因为坐标的数值显然在两个不同的空间中。
但是，如果您为一个数据集应用一些时机，则将其放在另一个数据集的相同坐标参考系统中，实体将按预期正确重叠。
欧洲石油调查组[EPSG]维护和分配了一个大的数据集，它描述任何坐标系统和坐标变换都应用于世界各地的GIS数据。
SpatiaLite为任何类型的几何类实现SRID，并支持EPSG数据集来识别坐标参考系。
>>> import sqlite3 as sqlite
>>> db = sqlite.connect(':memory:')
>>> db.enable_load_extension(True)
>>> db.execute('SELECT load_extension("mod_spatialite.so.7")')
>>> cursor = db.cursor()
>>> cursor.execute('BEGIN')
>>> cursor.execute('SELECT InitSpatialMetaData();')
<sqlite3.Cursor at 0x7f412c6070c0>
脚本为了初始化Spatial MetaData
开始新的spatialite.exe会话：
>>> cursor = cursor.execute('SELECT * FROM spatial_ref_sys LIMIT 5;')
>>> [print(rec) for rec in cursor]
(-1, 'NONE', -1, 'Undefined - Cartesian', '', 'Undefined')
(0, 'NONE', 0, 'Undefined - Geographic Long/Lat', '', 'Undefined')
(2000, 'epsg', 2000, 'Anguilla 1957 / British West Indies Grid', '+proj=tmerc +lat_0=0 +lon_0=-62 +k=0.9995000000000001 +x_0=400000 +y_0=0 +ellps=clrk80 +units=m +no_defs', 'PROJCS["Anguilla 1957 / British West Indies Grid",GEOGCS["Anguilla 1957",DATUM["Anguilla_1957",SPHEROID["Clarke 1880 (RGS)",6378249.145,293.465,AUTHORITY["EPSG","7012"]],AUTHORITY["EPSG","6600"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4600"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",-62],PARAMETER["scale_factor",0.9995],PARAMETER["false_easting",400000],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["EPSG","2000"]]')
(2001, 'epsg', 2001, 'Antigua 1943 / British West Indies Grid', '+proj=tmerc +lat_0=0 +lon_0=-62 +k=0.9995000000000001 +x_0=400000 +y_0=0 +ellps=clrk80 +towgs84=-255,-15,71,0,0,0,0 +units=m +no_defs', 'PROJCS["Antigua 1943 / British West Indies Grid",GEOGCS["Antigua 1943",DATUM["Antigua_1943",SPHEROID["Clarke 1880 (RGS)",6378249.145,293.465,AUTHORITY["EPSG","7012"]],AUTHORITY["EPSG","6601"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4601"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",-62],PARAMETER["scale_factor",0.9995],PARAMETER["false_easting",400000],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["EPSG","2001"]]')
(2002, 'epsg', 2002, 'Dominica 1945 / British West Indies Grid', '+proj=tmerc +lat_0=0 +lon_0=-62 +k=0.9995000000000001 +x_0=400000 +y_0=0 +ellps=clrk80 +towgs84=725,685,536,0,0,0,0 +units=m +no_defs', 'PROJCS["Dominica 1945 / British West Indies Grid",GEOGCS["Dominica 1945",DATUM["Dominica_1945",SPHEROID["Clarke 1880 (RGS)",6378249.145,293.465,AUTHORITY["EPSG","7012"]],AUTHORITY["EPSG","6602"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4602"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",-62],PARAMETER["scale_factor",0.9995],PARAMETER["false_easting",400000],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["EPSG","2002"]]')
[None, None, None, None, None]
spatial_ref_sys表是一个空间元数据，
在空间数据库初始化时产生，保存坐标参考系。 auth _name 和
auth _srid 分别标识权限生成这些数据和原始 Srid 。
>>> cursor.close()
>>> db.close()
8.7.1. 查看数据库信息¶
首先我们使用新的数据库，来查看信息：
>>> import os,shutil,stat
>>> import sqlite3 as sqlite
>>> tmp_db = '/tmp/xx_new_db.sqlite'
>>> if os.path.exists(tmp_db):
>>> os.remove(tmp_db)
>>> shutil.copy("spalite.db", tmp_db)
>>> os.chmod(tmp_db, stat.S_IRUSR + stat.S_IWUSR)
>>> conn = sqlite.connect(tmp_db)
>>> conn.enable_load_extension(True)
>>> conn.execute('SELECT load_extension("mod_spatialite.so.7")')
>>> cursor = conn.cursor()
>>> cursor.execute("select name from sqlite_master where type='table' order by name;")
<sqlite3.Cursor at 0x7f412c607b40>
>>> for rec in cursor: print(rec)
('ElementaryGeometries',)
('KNN',)
('SpatialIndex',)
('data_licenses',)
('geometry_columns',)
('geometry_columns_auth',)
('geometry_columns_field_infos',)
('geometry_columns_statistics',)
('geometry_columns_time',)
('hyd2_4l',)
('idx_hyd2_4l_GEOMETRY',)
('idx_hyd2_4l_GEOMETRY_node',)
('idx_hyd2_4l_GEOMETRY_parent',)
('idx_hyd2_4l_GEOMETRY_rowid',)
('idx_prov_capital_GEOMETRY',)
('idx_prov_capital_GEOMETRY_node',)
('idx_prov_capital_GEOMETRY_parent',)
('idx_prov_capital_GEOMETRY_rowid',)
('idx_region_popu_GEOMETRY',)
('idx_region_popu_GEOMETRY_node',)
('idx_region_popu_GEOMETRY_parent',)
('idx_region_popu_GEOMETRY_rowid',)
('prov_capital',)
('region_popu',)
('spatial_ref_sys',)
('spatial_ref_sys_aux',)
('spatialite_history',)
('sql_statements_log',)
('sqlite_sequence',)
('views_geometry_columns',)
('views_geometry_columns_auth',)
('views_geometry_columns_field_infos',)
('views_geometry_columns_statistics',)
('virts_geometry_columns',)
('virts_geometry_columns_auth',)
('virts_geometry_columns_field_infos',)
('virts_geometry_columns_statistics',)
查看数据库中的空间参考：
>>> cursor = cursor.execute('SELECT * FROM spatial_ref_sys LIMIT 5;')
>>> for rec in cursor: print(rec)
(-1, 'NONE', -1, 'Undefined - Cartesian', '', 'Undefined')
(0, 'NONE', 0, 'Undefined - Geographic Long/Lat', '', 'Undefined')
(2000, 'epsg', 2000, 'Anguilla 1957 / British West Indies Grid', '+proj=tmerc +lat_0=0 +lon_0=-62 +k=0.9995000000000001 +x_0=400000 +y_0=0 +ellps=clrk80 +units=m +no_defs', 'PROJCS["Anguilla 1957 / British West Indies Grid",GEOGCS["Anguilla 1957",DATUM["Anguilla_1957",SPHEROID["Clarke 1880 (RGS)",6378249.145,293.465,AUTHORITY["EPSG","7012"]],AUTHORITY["EPSG","6600"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4600"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",-62],PARAMETER["scale_factor",0.9995],PARAMETER["false_easting",400000],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["EPSG","2000"]]')
(2001, 'epsg', 2001, 'Antigua 1943 / British West Indies Grid', '+proj=tmerc +lat_0=0 +lon_0=-62 +k=0.9995000000000001 +x_0=400000 +y_0=0 +ellps=clrk80 +towgs84=-255,-15,71,0,0,0,0 +units=m +no_defs', 'PROJCS["Antigua 1943 / British West Indies Grid",GEOGCS["Antigua 1943",DATUM["Antigua_1943",SPHEROID["Clarke 1880 (RGS)",6378249.145,293.465,AUTHORITY["EPSG","7012"]],AUTHORITY["EPSG","6601"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4601"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",-62],PARAMETER["scale_factor",0.9995],PARAMETER["false_easting",400000],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["EPSG","2001"]]')
(2002, 'epsg', 2002, 'Dominica 1945 / British West Indies Grid', '+proj=tmerc +lat_0=0 +lon_0=-62 +k=0.9995000000000001 +x_0=400000 +y_0=0 +ellps=clrk80 +towgs84=725,685,536,0,0,0,0 +units=m +no_defs', 'PROJCS["Dominica 1945 / British West Indies Grid",GEOGCS["Dominica 1945",DATUM["Dominica_1945",SPHEROID["Clarke 1880 (RGS)",6378249.145,293.465,AUTHORITY["EPSG","7012"]],AUTHORITY["EPSG","6602"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4602"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",-62],PARAMETER["scale_factor",0.9995],PARAMETER["false_easting",400000],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["EPSG","2002"]]')
进一步查看数据库的信息：
>>> cursor = cursor.execute('SELECT DISTINCT Srid(geometry) FROM prov_capital;')
>>> for rec in cursor: print(rec)
(4326,)
>>> cursor = cursor.execute('''SELECT DISTINCT SRID(prov_capital.geometry), spatial_ref_sys.ref_sys_name FROM prov_capital,
>>> spatial_ref_sys WHERE SRID(prov_capital.geometry) = spatial_ref_sys.srid;''')
>>> for rec in cursor: print(rec)
(4326, 'WGS 84')
SpatiaLite SRID（）函数允许您识别标识任何类型的几何srid值。
你可以简单的运行，然后你会发现srid 32632真正的意思。
8.7.2. 创建¶
您可以通过SQL查询，来完成这个非常复杂的任务。
>>> # cursor.execute('BEGIN')
>>> cursor.execute("SELECT AddGeometryColumn('prov_capital', 'wgs84', 4326, 'POINT', 2)")
>>> cursor.execute("UPDATE prov_capital SET wgs84 = Transform(geometry, 4326);")
>>> conn.commit()
>>> cursor.execute('SELECT AsText(geometry), Srid(geometry),AsText(wgs84), Srid(wgs84) FROM prov_capital LIMIT 5;')
>>> for rec in cursor: print(rec)
('POINT(87.576106 43.781766)', 4326, 'POINT(87.576106 43.781766)', 4326)
('POINT(91.163128 29.710353)', 4326, 'POINT(91.163128 29.710353)', 4326)
('POINT(101.797123 36.593385)', 4326, 'POINT(101.797123 36.593385)', 4326)
('POINT(103.584065 36.118845)', 4326, 'POINT(103.584065 36.118845)', 4326)
('POINT(104.035277 30.714088)', 4326, 'POINT(104.035277 30.714088)', 4326)
>>> cursor.execute('SELECT st_buffer(geometry,1), Srid(geometry),AsText(wgs84), Srid(wgs84) FROM prov_capital LIMIT 5;')
<sqlite3.Cursor at 0x7f412c607b40>
WGS 84的
srid是 \(4326\) 。根据需要应用SpatiaLite Transform（）函数，从原始函数获取一个新的几何体。
NewTowns表中有两个替代几何体：
原始的geom列包含32623中的几何。
UTM区32N坐标参考系。
新的wgs84列包含4326中的几何。
WGS84坐标参考系。
您现在可以根据需要和适当的方式随意使用一个或另一个。