Sqlite/Spatialite Geometry格式(不是WKB)

2022-11-29 16:49:44 浏览数 (3)

网上以讹传讹的说法比较多,大多说称Spatialite Geometry为标准WKB格式,实际上按照官方文档解释并非如此

代码语言：javascript复制
SpatiaLite internally stores geometry values 
using ordinary SQLite's BLOB columns in a format
that is very closely related to WKB format but 
not exactly identical.

不同的存储格式

WKT

代码语言：javascript复制
LINESTRING Z (113.530643603811 34.7597662611779 106.213826217689,113.530643271043 34.7597058782661 106.250836674124,113.530642964671 34.7596706162388 106.272155821154)

WKB

代码语言：javascript复制
1,-22,3,0,0,3,0,0,0,-54,12,-105,16,-10,97,92,64,-128,46,86,5,64,97,65,64,27,0,41,84,-81,-115,90,64,56,-66,49,15,-10,97,92,64,-83,-35,-50,10,62,97,65,64,0,0,68,-75,13,-112,90,64,83,-57,-24,13,-10,97,92,64,41,61,2,-29,60,97,65,64,112,-47,63,0,107,-111,90,64

SQlite/Spatialite Geometry格式

代码语言：javascript复制
0,1,-26,16,0,0,83,-57,-24,13,-10,97,92,64,41,61,2,-29,60,97,65,64,-54,12,-105,16,-10,97,92,64,-128,46,86,5,64,97,65,64,124,-22,3,0,0,3,0,0,0,-54,12,-105,16,-10,97,92,64,-128,46,86,5,64,97,65,64,27,0,41,84,-81,-115,90,64,56,-66,49,15,-10,97,92,64,-83,-35,-50,10,62,97,65,64,0,0,68,-75,13,-112,90,64,83,-57,-24,13,-10,97,92,64,41,61,2,-29,60,97,65,64,112,-47,63,0,107,-111,90,64,-2

对比二进制内容

分析WKB:

代码语言：javascript复制
ByteOrder XDR/NDR(1)   1           
geomtery类型(2,5)      -22,3,0,0   
双浮点精度坐标值(6,)      3,0,0,0,-54,12,-105,16,-10,97,92,64,-128,46,86,5,64,97,65,64,27,0,41,84,-81,-115,90,64,56,-66,49,15,-10,97,92,64,-83,-35,-50,10,62,97,65,64,0,0,68,-75,13,-112,90,64,83,-57,-24,13,-10,97,92,64,41,61,2,-29,60,97,65,64,112,-47,63,0,107,-111,90,64

分析Spatialite Geometry:

代码语言：javascript复制
START(0)         0      

ENDIAN(1)        1      

SRID=(2,5位)     -26,16,0,0

BBOX(6,37位)     83,-57,-24,13,-10,97,92,64,41,61,2,-29,60,97,65,64,-54,12,-105,16,-10,97,92,64,-128,46,86,5,64,97,65,64

END(38位,固定值)  124    

----缺省byteOrder的WKB----

geomtery类型(39,42) -22,3,0,0 

双浮点精度坐标值(43,)  3,0,0,0,-54,12,-105,16,-10,97,92,64,-128,46,86,5,64,97,65,64,27,0,41,84,-81,-115,90,64,56,-66,49,15,-10,97,92,64,-83,-35,-50,10,62,97,65,64,0,0,68,-75,13,-112,90,64,83,-57,-24,13,-10,97,92,64,41,61,2,-29,60,97,65,64,112,-47,63,0,107,-111,90,64

LAST(最后一位,固定值) -2

结论

如果不想或者无法使用spatialite插件,直接截取sql3 Blob字节数组的[39- 倒数第二位],首位增加byteOrder,即为标准WKB

示例代码:

代码语言：javascript复制
# 输入geometry byte数组
byte[] bytes = new byte[geom.length - 39];
bytes[0] = geom[1];
for (int i = 1; i < bytes.length; i  ) {
    bytes[i] = geom[i   38];
 }
 Geometry geometry = new WKBReader().read(bytes);
 return geometry;
 # 输出jts geometery

参考文档

  • SpatiaLite internal BLOB-Geometry format (gaia-gis.it):
  • Well-Known Binary (WKB) | GEOS (libgeos.org)
sqlite binary blob geometry gis

0 人点赞