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

[JanaMartin]

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 solution]

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  + '<br/>' +
        'Fixture: ' + dFixtureType  + '<br/>' + 
        'Support: ' + dSupportType + '<br/>' +
        'Number of Lights: ' + cast(NumberLights as nvarchar(5)) + '<br/>' 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
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[JanaMartin]

Thanks for posting this. I'll check it out and reply back.