地理栅栏：如何使用Oracle空间查找点或形状是否在多边形内

问题描述

如何使用oracle空间SQL查询来查找点或多边形是否位于另一个多边形内

这是场景;

我有一个包含空间类型（sdo_geometry）的表格（STATE_TABLE），它是一个多边形（称为状态），我有另一个表格（UNIVERSITY_TABLE），它包含包含大学的空间数据（sdo_geometry）（点/多边形）;

现在如何查找选定的大学（'s）是否在使用SQL select的给定状态声明。

主要我想找到geofence中给定对象的存在。

谢谢。

解决方案

您需要使用SDO_CONTAINS或SDO_RELATE以及'CONTAINS'掩码

请参阅位于

SDO_CONTAINS
for

或

SDO_RELATE
http://download.oracle.com/docs/cd /E11882_01/appdev.112/e11830/sdo_operat.htm#i78531

其中之一您可以执行以下操作（假设您的空间信息包含在一个名为'GEOM'的空间索引列中）：

  select 
 ST.NAME，UT。 UNIVERSITY_NAME 
 from 
 STATE_TABLE ST 
 INNER JOIN UNIVERSITY_TABLE UT 
 ON SDO_CONTAINS（ST.GEOM，UT.GEOM）='TRUE'
  

您必须原谅我，因为我没有特别记住正确的语法，我不知道上述连接是否可以正常工作在所有。这应该足以让你指向正确的方向。

How do i find if a point or a polygon is inside another polygon using oracle spatial SQL query

Here is the scenario;

I have a table (STATE_TABLE) which contains a spatial type (sdo_geometry) which is a polygon (of say a State), I have another table (UNIVERSITY_TABLE) which contains spatial data (sdo_geometry) (point/polygon) which contains Universities;

Now how do i find if a selected University('s) are in a given State using SQL select statement.

Primarily i want to locate existence of given object(s) in a geofence.

Thanks.

解决方案

You're going to need to use either SDO_CONTAINS or SDO_RELATE with a mask of 'CONTAINS'

Please see documentation located at

SDO_CONTAINShttp://download.oracle.com/docs/cd/E11882_01/appdev.112/e11830/sdo_operat.htm#sthref1064 for

or

SDO_RELATEhttp://download.oracle.com/docs/cd/E11882_01/appdev.112/e11830/sdo_operat.htm#i78531

Either one of these you'd do something like the following (assuming your spatial information is contained in a column called 'GEOM' that is spatially indexed):

select
    ST.NAME, UT.UNIVERSITY_NAME
from
    STATE_TABLE ST
    INNER JOIN UNIVERSITY_TABLE UT
      ON SDO_CONTAINS(ST.GEOM, UT.GEOM) = 'TRUE'

You'll have to forgive me as I don't specifically remember the correct syntax for this and I don't know if the above join will work properly at all. This should be enough to point you in the right direction though.

这篇关于地理栅栏：如何使用Oracle空间查找点或形状是否在多边形内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！