时间戳

首页 > 解决方案 > 如何按 SDO_GEOMETRY 数据类型分组

sql - 如何按 SDO_GEOMETRY 数据类型分组

问题描述

我在一个表中有 3 列：

我想分组SDO_GEOMETRY以摆脱重复shapes

然而，每次我这样做

SELECT
  p_id, user_id, shape
FROM table1
GROUP BY shape

我得到错误

ORA-22901: cannot compare VARRAY or LOB attributes of an object type

标签： sqloracleoracle10goracle-spatial

解决方案

对，那行不通。这是一个小演练。

表格内容：

SQL> select id, geom from test;

ID   GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
---- -------------------------------------------------------------------------------------
4026 SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(16,9176596, 46,2173069, NULL), NULL, NULL)
4027 SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(16,9184437, 46,2219955, NULL), NULL, NULL)
4028 SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(16,9826714, 46,2176214, NULL), NULL, NULL)
5000 SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(16,9176596, 46,2173069, NULL), NULL, NULL)

SQL>

ID 4026 和 5000 具有相同的几何形状，所以 - 正如你所说 - 你想摆脱其中一个。

以下没有任何作用：

清楚的：

SQL> select distinct id, geom from test;
select distinct id, geom from test
                    *
ERROR at line 1:
ORA-22901: cannot compare VARRAY or LOB attributes of an object type

您的尝试：

SQL> select id, geom from test group by geom;
select id, geom from test group by geom
       *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

当然，GROUP BY 子句中缺少 ID，所以让我们添加它：

SQL> select id, geom from test group by id, geom;
select id, geom from test group by id, geom
                                       *
ERROR at line 1:
ORA-22901: cannot compare VARRAY or LOB attributes of an object type

那么该怎么办？使用 self-join withSDO_RELATE来查找“重复项”：

SQL> select a.id, a.geom
  2  from test a join test b
  3    on sdo_relate(a.geom, b.geom, 'mask=equal') = 'TRUE'
  4   and a.id < b.id;

ID   GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
---- -------------------------------------------------------------------------------------
4026 SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(16,9176596, 46,2173069, NULL), NULL, NULL)

SQL>

对; 众所周知，4026 和 5000 是一样的。由于第 #4 ( a.id < b.id) 行，返回 4026。

现在，将上述查询用作子查询（或 CTE，或任何您认为合适的）来获取不同的数据集是一项简单的任务：

SQL> with duplicates as
  2    (select a.id, a.geom
  3     from test a join test b
  4       on sdo_relate(a.geom, b.geom, 'mask=equal') = 'TRUE'
  5      and a.id < b.id
  6    )
  7  select t.id, t.geom
  8  from test t
  9  where not exists (select null
 10                    from duplicates d
 11                    where d.id = t.id
 12                   );

ID   GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
---- -------------------------------------------------------------------------------------
4027 SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(16,9184437, 46,2219955, NULL), NULL, NULL)
4028 SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(16,9826714, 46,2176214, NULL), NULL, NULL)
5000 SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(16,9176596, 46,2173069, NULL), NULL, NULL)

SQL>

或者：

SQL> select t.id, t.geom
  2  from test t
  3  where t.id not in (select a.id
  4                     from test a join test b
  5                       on sdo_relate(a.geom, b.geom, 'mask=equal') = 'TRUE'
  6                      and a.id < b.id
  7                    );

ID   GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
---- -------------------------------------------------------------------------------------
4027 SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(16,9184437, 46,2219955, NULL), NULL, NULL)
4028 SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(16,9826714, 46,2176214, NULL), NULL, NULL)
5000 SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(16,9176596, 46,2173069, NULL), NULL, NULL)

SQL>

推荐阅读