关于Oracle Spatial 三维数据支持得坑

存储

说起Oracle Spatial 三维数据就得先说Oracle得数据SDO_GEOMETRY, 先直接看结构截图:

一个SDO_GEOMETRY 在数据库 有一下属性

SDO_GTYPE, 这是整个数据对象得类型, 这个类型代表大类,注意和ETYPE区分

SDO_SRID, 空间参考id

SDO_POINT.X , 如果是单点可以存在这里,否则为NULL

SDO_POINT.Y, 如果是单点可以存在这里否则为NULL

SDO_POINT.Z, 如果是单点可以存在这里否则为NULL

SDO_ELEM_INFO(描述,SDO_ORDINATES数组中坐标得组织方式和几何类型, 每三个int一组, 第一位是偏移量从1开始, 第二位是类型ETYPE(注意这里和GTYPE不相同,这里得类型更加细致, 并且这里没有维度区分) ,第三位是子对象个数. 每三个一组可以有多组,)

SDO_ORDINATES, 坐标数组(内部就是存得坐标x,y 或者x,y,z 或者 x,y,z ,m)

SDO_GTYPE详细描述 如下:

SDO_GTYPE(4位数,

第一位是维度,  x,y 对应2,x,y,z 对应三, 相应得SDO_ORDINATES内得数组也要严格对应, 否则无法创建空间索引, 或者查询失败

第二位 是什么线性参考, 不懂, 暂时没用到

第三位和第四位是类型位, 有效值01 -09  ,  10 及以后保留未用

GTYPE   描述
DL00     UNKNOWN_GEOMETRY Spatial and Graph ignores this geometry.(无效几何)
DL01     POINT Geometry contains one point.(单点)
DL02     LINE or CURVE Geometry contains one line string that can contain straight or circular arc segments, or both. (LINE and CURVE are synonymous in this context.)(线或曲线)
DL03     POLYGON or SURFACE Geometry contains one polygon with or without holes, 1 or one surface consisting of one or more polygons.In a three - dimensional polygon, all points must be on the same plane.(面或曲面,只有一个外圈得面,对应OGC简单面)
DL04     COLLECTION Geometry is a heterogeneous collection of elements.COLLECTION is a superset that includes all other types..(几何对象集合, 理论都可以存)
DL05     MULTIPOINT Geometry has one or more points. (多点)
DL06     MULTILINE or MULTICURVE Geometry has one or more line strings. (MULTILINE and MULTICURVE are synonymous in this context, and each is a superset of both LINE and CURVE.))(多段线或多段曲线)
DL07     MULTIPOLYGON or MULTISURFACE Geometry can have multiple, disjoint polygons(more than one exterior boundary). or surfaces(MULTIPOLYGON is a superset of POLYGON, and MULTISURFACE is a superset of SURFACE.)(多面或多曲面)
DL08     SOLID Geometry consists of multiple surfaces and is completely enclosed in a three - dimensional space.Can be a cuboid or a frustum.(多曲面组成得闭合体)
DL09     MULTISOLID Geometry can have multiple, disjoint solids(more than one exterior boundary). (MULTISOLID is a superset of SOLID.)(多闭合体)

SDO_ETYPE(如果是面第一位表示内外圈意思不是维数, 不是维数, 不是维数 1003 表示外圈, 2003 表示内圈, 也就是1为外,2为内圈,)

ETYPE     N                                   描述

0                 (any numeric value)        Type 0 (zero)element.Used to model geometry types not supported by Oracle Spatial and Graph.For more information, see Type 0 (Zero)Element.

1                 1                            Point type. (单点)

1                 0                            Orientation for an oriented point.(定向点)

1                 n > 1                        Point cluster with n points.(多点)

2                 1                            Line string whose vertices are connected (就是一条不间断得线)

2                 2                            Line string made up of a connected sequence of circular arcs.Each circular arc is described using three coordinates : the start point of the arc, any point on the arc, and the end point of the arc.The coordinates for a point designating the end of one arc and the start of the next arc are not repeated.For example, five coordinates are used to describe a line string made up of two connected circular arcs.Points 1, 2, and 3 define the first arc, and points 3, 4, and 5 define the second arc, where point 3 is only stored once.(三点圆弧线,可以是多段三点圆弧连接得线)

2                 3                            NURBS(non - uniform rational B - spline) curve.(非均匀有理B样条曲线)

1003 or 2003     1                            Simple polygon whose vertices are connected by straight line segments.You must specify a point for each vertex; and the last point specified must be exactly the same point as the first(within the tolerance value), to close the polygon.For example, for a 4 - sided polygon, specify 5 points, with point 5 the same as point 1.(单面)

1003 or 2003     2                            Polygon made up of a connected sequence of circular arcs that closes on itself.The end point of the last arc is the same as the start point of the first arc.Each circular arc is described using three coordinates : the start point of the arc, any point on the arc, and the end point of the arc.The coordinates for a point designating the end of one arc and the start of the next arc are not repeated.For example, five coordinates are used to describe a polygon made up of two connected circular arcs.Points 1, 2, and 3 define the first arc, and points 3, 4, and 5 define the second arc.The coordinates for points 1 and 5 must be the same(tolerance is not considered), and point 3 is not repeated.(三点圆弧组成得面, 也是单面得一种)

1003 or 2003     3                            Rectangle type(sometimes called optimized rectangle).A bounding rectangle such that only two points, the lower - left and the upper - right, are required to describe it.The rectangle type can be used with geodetic or non - geodetic data.However, with geodetic data, use this type only to create a query window(not for storing objects in the database).For information about using this type with geodetic data, including examples, see Geodetic MBRs.For information about creating three - dimensional optimized rectangles, see ThreeDimensional Optimized Rectangles.(矩形)

1003 or 2003     4                            Circle type.Described by three distinct non - colinear points, all on the circumference of the circle.(圆面)

4                 n > 1                        Compound line string with some vertices connected by straight line segments and some by circular arcs.The value n in the Interpretation column specifies the number of contiguous subelements that make up the line string.The next n triplets in the SDO_ELEM_INFO array describe each of these subelements.The subelements can only be of SDO_ETYPE 2. The last point of a subelement is the first point of the next subelement, and must not be repeated.See Compound Line String and Figure 2 - 5 for an example of a compound line string geometry.  (圆弧和普通线组合成得一条线, 如果数据有曲线可以内查成非曲线, 这样直接用2,1表示)

1005 or 2005     n > 1                        Compound polygon with some vertices connected by straight line segments and some by circular arcs.The value n in the Interpretation column specifies the number of contiguous subelements that make up the polygon.The next n triplets in the SDO_ELEM_INFO array describe each of these subelements.The subelements can only be of SDO_ETYPE 2. The end point of a subelement is the start point of the next subelement, and it must not be repeated.The start and end points of the polygon must be exactly the same point(tolerance is ignored).See Compound Polygon and Figure 2 - 6 for an example of a compound polygon geometry.(圆弧和普通线组合成得一个面, 如果数据有曲线可以内查成非曲线, 这样直接用2,1表示)

1006 or 2006     n > 1                        Surface consisting of one or more polygons, with each edge shared by no more than two polygons.A surface contains an area but not a volume.The value n in the Interpretation column specifies the number of polygons that make up the surface.The next n triplets in the SDO_ELEM_INFO array describe each of these polygon subelements.A surface must be three - dimensional.For an explanation of three - dimensional support in Spatial and Graph, see ThreeDimensional Spatial Objects.  (由一个或多个多边形组成的曲面，每个边最多由两个多边形共享。)

1007             n = 1 or 3                    Solid consisting of multiple surfaces that are completely enclosed in a three - dimensional space, so that the solid has an interior volume.A solid element can have one exterior surface defined by the 1006 elements and zero or more interior boundaries defined by the 2006 elements.The value n in the Interpretation column must be 1 or 3. Subsequent triplets in the SDO_ELEM_INFO array describe the exterior 1006 and optional interior 2006 surfaces that make up the solid element.If n is 3, the solid is an optimized box, such that only two three - dimensional points are required to define it : one with minimum values for the box in the X, Y, and Z dimensions and another with maximum values for the box in the X, Y, and Z dimensions.For example : SDO_GEOMETRY(3008, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1007, 3), SDO_ORDINATE_ARRAY(1, 1, 1, 3, 3, 3)) For an explanation of three - dimensional support in Spatial and Graph, see Three - Dimensional Spatial Objects.(由完全封闭在三维空间中的多个表面组成的实体，因此该实体具有内部体积)

由以上官方解释, 然后对照目前普通GIS平台来看,  对应GTYPE这些都是针对GIS中得Geometry Type.  具体得实施建议如下:

GTYPE  DL01 - DL07 在目前GIS中都有一一对应关系, D取值2或者3,表示二维数据还是三维数据  L取0, 最后一位直接对应类型即可

ETYPE 中具体有些曲线和GIS对不上,也可以是或GIS内部不擅长存储曲线,  存储得时候都可以内插,  那么ETYPE 真实对与GIS来说只用使用 1, 2, 1003或者2003就够了.   又因为1003和2003 需要区分内外圈, 这是一种很耗时得操作, 经过实验直接写3得兼容性比较好,(PS  OCI官方文档说可以写3003, 但是这种表示不确定内外圈, 三维数据建立空间索引会失败)   

空间索引创建:

    ss << "create index " << strIndexName << " on " << m_Meta.TableName << "(" << vGeoNames.front() << ") INDEXTYPE IS MDSYS.SPATIAL_INDEX "
        << " PARAMETERS('SDO_COMMIT_INTERVAL=1000 , SDO_INDX_DIMS  =" << (m_ColInfo.HasZ ? 3 : 2) << "' ) "; //维数一定要对
 

存储就总结为下面5条:

1: 创建得维数必须对  在写 user_sdo_geom_metadata得:diminfo时候需要写三维范围;

2: SDO_GEOMETRY得GTYPE第一位必须是3;

3: SDO_GEOMETRY得坐标必须是三维;

4: SDO_GEOMETRY.SDO_ELEM_TYPE 第一位代表内外圈不要填, 直接, 用1,2,3  不用其他任何值,  除非你得系统需要精确曲线, 否则存储得时候内插一下即可.

5:空间索引创建得维数一定要一一对应

查询

三维数据存好了 , 现在做基本查询, 其中碰到得最大得障碍就是 说数据维数不匹配.

然后用Oracle 19版本可以用二维数据查询三维存储得数据,  但是用11gr1 就不能,  可见19版本在用户体验或者api上可能做了改善优化, 所以最好我们将二维矩形查询和三维矩形查询分开操作, 创建了不同得查询SDO_GEOMETRY对象,主要区别代码如下

总结

最后总结三维数据经验如下: 

1: 创建得维数必须对  在写 user_sdo_geom_metadata得:diminfo时候需要写三维范围;

2: SDO_GEOMETRY得GTYPE第一位必须是3;

3: SDO_GEOMETRY得坐标必须是三维;

4: SDO_GEOMETRY.SDO_ELEM_TYPE 第一位代表内外圈不要填, 直接, 用1,2,3  不用其他任何值,  除非你得系统需要精确曲线, 否则存储得时候内插一下即可.

5:  空间索引创建得维数一定要一一对应

6:  查询对象输入也应该创建为三维对象, 这样就可以兼容更多数据库版本.

一句话总结, 三维数据读写所有得操作都最好考虑维数, 都严格对应了那么就可以解决.

另外再次温习发现Oracle Spatial 竟然三维曲面和三维体都支持,之前大意了