每个坐标值都是完全限定的域名，需要一个有明确标识。
此值标识了几何相关描述、几何对象定义的坐标空间。
作为一种普遍的规则，只有当它们的坐标是在同样的空间参考系统，不同的几何对象才能进行有意义的互操作。
在GIS中，一种常用的操作，是地理坐标重投影（coordinate reprojection）， 是将不同的GIS数据转换成唯一的的空间参考系统，然后进行一些互操作和集成。
当您尝试使用属于不同坐标参考系统的两个GIS数据集时，实体下降得非常远，因为坐标的数值显然在两个不同的空间中。
但是，如果您为一个数据集应用一些时机，则将其放在另一个数据集的相同坐标参考系统中，实体将按预期正确重叠。
欧洲石油调查组[EPSG]维护和分配了一个大的数据集，它描述任何坐标系统和坐标变换都应用于世界各地的GIS数据。
SpatiaLite为任何类型的几何类实现SRID，并支持EPSG数据集来识别坐标参考系。
In [13]:
import sqlite3 as sqlite
db = sqlite.connect(':memory:')
db.enable_load_extension(True)
db.execute('SELECT load_extension("mod_spatialite.so")')
cursor = db.cursor()
cursor.execute('BEGIN')
cursor.execute('SELECT InitSpatialMetaData();')
Out[13]:
<sqlite3.Cursor at 0x7f0287f84f40>
脚本为了初始化Spatial MetaData
开始新的spatialite.exe会话：
In [14]:
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"]]')
Out[14]:
[None, None, None, None, None]
spatial_ref_sys表是一个空间元数据，
在空间数据库初始化时产生，保存坐标参考系。
auth _name 和 auth _srid 分别标识权限生成这些数据和原始 Srid 。
In [15]:
cursor.close()
db.close()
In [16]:
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")')
cursor = conn.cursor()
cursor.execute("select name from sqlite_master where type='table' order by name;")
Out[16]:
<sqlite3.Cursor at 0x7f0287f84d40>
In [17]:
for rec in cursor: print(rec)
('ElementaryGeometries',)
('KNN2',)
('SpatialIndex',)
('data_licenses',)
('geometry_columns',)
('geometry_columns_auth',)
('geometry_columns_field_infos',)
('geometry_columns_statistics',)
('geometry_columns_time',)
('idx_prov_capital_GEOMETRY',)
('idx_prov_capital_GEOMETRY_node',)
('idx_prov_capital_GEOMETRY_parent',)
('idx_prov_capital_GEOMETRY_rowid',)
('prov_capital',)
('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',)
查看数据库中的空间参考：
In [18]:
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"]]')
进一步查看数据库的信息：
In [19]:
cursor = cursor.execute('SELECT DISTINCT Srid(geometry) FROM prov_capital;')
for rec in cursor: print(rec)
(4326,)
In [20]:
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')
In [21]:
# 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)
In [22]:
cursor.execute('SELECT st_buffer(geometry,1), Srid(geometry),AsText(wgs84), Srid(wgs84) FROM prov_capital LIMIT 5;')
Out[22]:
<sqlite3.Cursor at 0x7f0287f84d40>
- WGS 84的
srid是 $4326$ 。 - 根据需要应用SpatiaLite Transform（）函数，从原始函数获取一个新的几何体。
- NewTowns表中有两个替代几何体：
- 原始的geom列包含32623中的几何。
- UTM区32N坐标参考系。
- 新的wgs84列包含4326中的几何。
- WGS84坐标参考系。
- 您现在可以根据需要和适当的方式随意使用一个或另一个。