ARCGIS Spatial SQL: Check is a feature shape is with an other

Question

Background:

Oracle 11g R2 database

Arcgis server 10.3.1

SRID: 300017 (of both tables)

I would like to use spatial sql to check is a given Features shape (A) is found to be with in or the majority of the shape is touching, another Feature Classes features(B) set.

I was thinking something like below, but I'm always getting back 'no data'. I have checked, visually, in arcmap and layers, what feature of B that A falls inside. So I know what the expected results should be. I'm not sure what I'm doing wrong.

select r.regionCode
 into rCode
 from Regions r, analysis an
 where 
 sde.st_intersects( an.shape,r.shape) = 1;

Any help in either correcting my sql above or a suggestion on an alternative would be great.

TedKowal · Accepted Answer

I have done something similar in SQL Server.... the code may differ slightly but the concept should be the same.... I buffered (in my case points in order to account for the "nearby")..... Hopefully this will be helpful! USE [GeoTest] GO /****** Object: StoredProcedure [dbo].[usp_MDXLights_Intersection]    Script Date: 10/17/2016 11:02:05 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:          Ted Kowal -- Create date: 2/1/2015 -- Description:     Get Lights within a bounding area -- ============================================= ALTER PROCEDURE [dbo].[usp_MDXLights_Intersection]      -- Add the parameters for the stored procedure here      @latitude float,      @longitude float,      @BufferSize float AS BEGIN      -- SET NOCOUNT ON added to prevent extra result sets from      -- interfering with SELECT statements.      SET NOCOUNT ON;     Declare @Point geography      SET @Point = geography::Point(@latitude,@longitude, 4326)      -- Use STBuffer() to greate a buffer around the point to BufferSize in Feet      Declare @SearchArea geometry      Declare @G geography      -- This was done using Geometry (Have not been able to get it working using      --                                      Geography)      Set @G = @Point.STBuffer(@BufferSize *.3048)      -- Convert Search Area to Geometry from Geography      Set @SearchArea = geometry::STGeomFromWKB((Select @G.STAsBinary()), 4326)      -- Select any lights that intersect the search area      Select         Shape.STGeometryType() as GeometryType,         Shape.STAsText() as WKT,         'Light ID: ' + LSAIT as Title,         'Pole Type: ' + dPoleType + '
' +         'Fixture: ' + dFixtureType + '
' +         'Support: ' + dSupportType + '
' +         'Number of Lights: ' + cast(NumberLights as nvarchar(5)) + '
' as Description,         MDXLights.FixtureType as FT      From         MDXLights INNER JOIN           lkLightsFixtureType ON MDXLIGHTS.FixtureType = lkLightsFixtureType.FixtureType INNER JOIN           lkLightsPoleType ON MDXLIGHTS.PoleType = lkLightsPoleType.PoleType INNER JOIN           lkLightsSupportType ON MDXLIGHTS.SupportType = lkLightsSupportType.SupportType      Where         Shape.STIntersects(@SearchArea) = 1      -- Also select the search area itself      Union ALL Select         @SearchArea.STGeometryType() as GeometryType,         @SearchArea.STAsText() as WKT,         'Search Area' as Title,         cast(@BufferSize as nvarchar(5)) + 'foot Search radius' as Description,         cast('100' as int) as FT END ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍