Oracle Spatial-选择区域内的对象 [英] Oracle Spatial - select objects falling within area

问题描述

这对认识的人可能很简单(我希望！)

this is probably simple to those who know (I hope!)

我有一个Oracle空间数据库，其中的几何列包含北向/东向格式的节点点列表(如果有关系的话！)

I have an Oracle spatial database with a geometry column containing a list of node points, in northing/easting format (if it's relevent!)

我需要选择那些落在给定点的给定半径内的对象.

I need to select those objects which fall within a given radius of a given point.

Northings和Eastings相距1米，这使它变得容易一些.

Northings and Eastings are 1 meter apart which makes it a bit easier.

理想情况下，这应该包括穿过该区域的对象，即使它们的节点位于该区域之外.

Ideally this should include objects which cross the area even if their node points fall outside it.

这是一个简单的查询吗?也许使用SDO_WITHIN_DISTANCE?

Is this an easy-ish query? Maybe using SDO_WITHIN_DISTANCE?

该表如下所示:

MyTable
ID NUMBER
NAME VARCHAR2(20)
DESC VARCHAR2(50)
GEOM SDO_GEOMETRY

感谢您的帮助！

推荐答案

您可以通过以下两种方法之一来执行此操作.首先，正如您提到的， SDO_WITHIN_DISTANCE 是一种有效的方法.

You can do this one of two ways. First, as you mentioned, SDO_WITHIN_DISTANCE is a valid approach.

select 
    *
from center_point a
inner join target_points b
    on a.id = 1
    and sdo_within_distance( b.shape, a.shape, 'distance = 10' ) = 'TRUE'
;

在这种情况下，距离是以a的空间参考定义的线性单位. Oracle将坐标视为笛卡尔坐标，因此在使用此运算符之前(与角度纬度/经度单位相对)，您需要确保您拥有线性坐标系.由于您正在使用Northings/Eastings，因此，只要您要比较的点在同一空间参考中，我认为您会没事的.

In this case, the distance is in linear units defined by a's spatial reference. Oracle treats the coordinates as Cartesian so you will need to make sure you have a linear coordinate system before using this operator (as opposed to angular lat/lon units). Since you are working with northings/eastings, I think you'll be okay as long as the points you are comparing against are in the same spatial reference.

这种方法使用内部循环来解决查询，因此如果您有很多要比较的要点，效率将不高.另外，Oracle Spatial对SDO函数中操作数的顺序非常挑剔，因此您可能需要反复使用参数顺序才能找到最佳位置.如果查询运行时间较长，请尝试切换sdo运算符的第一个和第二个参数.您还可以使用SELECT之后的/*+ ORDERED */后缀来处理'from'和'inner join'表的顺序.

This approach uses an inner-loop to solve the query so not very efficient if you have a lot of points to compare against. Also, Oracle Spatial is VERY picky about the order of operands in the SDO functions so you might need to play around with parameter order to find the sweetspot. If your query runs for a long period, try switching the first and second parameter of your sdo operator. You can also play with the order of the 'from' and 'inner join' tables using the /*+ ORDERED */ hind after SELECT.

另一种方法是对几何图形进行缓冲并与该缓冲区进行比较

Another approach is to buffer the geometry and compare against the buffer.

select 
    *
from center_point a
inner join target_points b
    on a.id = 1
    and sdo_relate( b.shape, sdo_buffer(a.shape, 0.05 ), 'mask=anyinteract' ) = 'TRUE'
;

请记住，如果像我们在这里使用缓冲区那样进行转换，则SDO_RELATE的第二个参数(称为窗口)中的任何内容都将不具有空间索引.

Keep in mind that whatever is in the second parameter of the SDO_RELATE (called the window) will not have a spatial index if you transform it like we are here with the buffer.

如果计划使用多个点进行此操作，建议构建一个表，其中所有源点都将被缓冲.然后针对缓冲区域创建空间索引，并将其与目标点进行比较.

If you plan on doing this with several points, it is recommended to build a table where all of the source points are buffered. Then create a spatial index against the buffered areas and compare that to your target points.

例如:

create table point_bufs unrecoverable as
select sdo_buffer (a.shape, b.diminfo, 1.35)
from centerpoint a, user_sdo_geom_metadata b
where table_name='CENTERPOINT'
  and column_name='SHAPE';

select
    a.gif,
    b.gid 
from target_points a, 
     point_bufs b
where sdo_relate(a.shape, b.shape, 'mask=anyinteract querytype=join') = 'TRUE'
;

注意:与多边形相交时，您总是希望多边形位于sdo_relate(第二个参数)的窗口位置中.这样可以确保正确使用空间索引.

NOTE: When intersecting points with polygons, you always want to polygon to be in the window position of the sdo_relate (which is the second parameter). This will ensure your spatial index is used correctly.

这篇关于Oracle Spatial-选择区域内的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！