最近邻以及点和线之间的距离

Question

在oracle空间中，我有两个表(AVALREGULACAO和ATROCOADUTOR(分别表示点和线。

两个表的结构如下：

AVALREGULACAO (295 点记录(
IPID [编号(10(]
几何 [MDSYS.SDO_GEOMETRY]

ATROCOADUTOR (12536 行记录(
IPID [编号(10(]
几何 [MDSYS.SDO_GEOMETRY]

我需要从每个AVALREGULACAO中找到最近的ATROCOADUTOR邻居并计算它们之间的距离

AVALREGULACAO_IPID |ATROCOADUTOR _IPID |距离

我使用了 2 个选项

1

SELECT /*+ ORDERED */ A.IPID, B.IPID, MIN(SDO_GEOM.SDO_DISTANCE(sdo_cs.make_2d(A.GEOMETRY), sdo_cs.make_2d(B.GEOMETRY), 0.005)) as DISTANCE
FROM AVALREGULACAO A, ATROCOADUTOR B 
GROUP BY c_b.IPID,c_d.IPID;

计算需要相当长的时间 - 它生成了 295 x 12536 = 3 698 120 种可能组合(笛卡尔积(的巨大输出。 此外，csv 文件输出无法容纳所有这些记录(限制 1 048 576 行(

我只需要 295 条记录，对应于 295 AVALREGULACAO。

2 我还尝试/改编了最近邻 (nn( 运算符的另一个查询

PROMPT IPID, nearest_IPID, distance  
select /*+ ORDERED USE_NL(s,s2)*/
s.IPID,
s2.IPID as nearest_IPID,
TO_CHAR(REPLACE(mdsys.sdo_geom.sdo_distance(sdo_cs.make_2d(s.GEOMETRY),sdo_cs.make_2d(s2.GEOMETRY),0.05), ',','.')) as distance
from AVALREGULACAO s,
ATROCOADUTOR s2
where s2.IPID in (select IPID
from AVALREGULACAO s3
where sdo_nn(s3.GEOMETRY,s.GEOMETRY,'sdo_batch_size=10',1) = 'TRUE'
and s3.IPID <> s.IPID
and rownum < 2)
order by 1,2;

此查询需要很长时间 - 我需要在结束之前关闭进程。

我想我错过了如何优化/过滤所需结果的重点。

有关如何有效解决此问题的任何提示将不胜感激。

提前感谢， 佩德罗

附注： @Boneist。非常感谢您的输入。

不幸的是，应用您的查询后我遇到了错误(仍在尝试处理新命令 KEEP，dense_rank 的语义/语法(

SELECT a.ipid a_ipid,
MIN(b.ipid) KEEP (dense_rank FIRST order by sdo_nn(a.GEOMETRY,b.GEOMETRY,'sdo_batch_size=10',1)) b_ipid,
MIN(sdo_geom.sdo_distance(sdo_cs.make_2d(a.geometry), sdo_cs.make_2d(b.geometry), 0.005)) AS distance
FROM   avalregulacao a
INNER JOIN atrocoadutor b ON sdo_nn(a.GEOMETRY,b.GEOMETRY,'sdo_batch_size=10',1) = 'TRUE'
GROUP  BY a.ipid;

错误

Error starting at line : 1 in command -
SELECT a.ipid a_ipid,
MIN(b.ipid) KEEP (dense_rank FIRST order by sdo_nn(a.GEOMETRY,b.GEOMETRY,'sdo_batch_size=10',1)) b_ipid,
MIN(sdo_geom.sdo_distance(sdo_cs.make_2d(a.geometry), sdo_cs.make_2d(b.geometry), 0.005)) AS distance 
FROM avalregulacao a 
INNER JOIN atrocoadutor b ON sdo_nn(a.GEOMETRY,b.GEOMETRY,'sdo_batch_size=10',1) = 'TRUE'
GROUP  BY a.ipid
Error at Command Line : 2 Column : 45
Error report -
SQL Error: ORA-29907: foram encontradas etiquetas em duplicado em invocações primárias
29907. 00000 -  "found duplicate labels in primary invocations"
*Cause:    There are multiple primary invocations of operators with
the same number as the label.
*Action:   Use distinct labels in primary invocations.

Answer 1

我想你可能在追求这样的东西：

SELECT a.ipid a_ipid,
MIN(b.ipid) KEEP (dense_rank FIRST order by sdo_nn(a.GEOMETRY,b.GEOMETRY,'sdo_batch_size=10',1)) b_ipid,
MIN(sdo_geom.sdo_distance(sdo_cs.make_2d(a.geometry), sdo_cs.make_2d(b.geometry), 0.005)) AS distance
FROM   avalregulacao a
INNER JOIN atrocoadutor b ON sdo_nn(a.GEOMETRY,b.GEOMETRY,'sdo_batch_size=10',1) = 'TRUE'
GROUP  BY a.ipid;

这会在最近邻函数上联接两个表，这应该会减少返回的行数。

MIN(b.ipid) KEEP (dense_rank first order by sdo_nn(a.GEOMETRY,b.GEOMETRY,'sdo_batch_size=10',1))仅返回最低差值的最低 b.ipid 值。

(我认为此查询将按原样工作，但我无法对其进行测试。您可能必须执行联接并将sdo_nn(a.GEOMETRY,b.GEOMETRY,'sdo_batch_size=10',1)作为子查询中的列，然后在外部查询中执行分组依据。