SDE Query under SQL Server

[JeanDesormeaux]

Hi guys,

Under SQL Server 2014 registered as SDE geodatabase, is there any way to query geometries via SQL?

I need to select SDE geometries that are within another SDE geometry but I can't with no luck find any examples on web.

Thank you,

Jean

[MarceloMarques]

https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/sql-access-to-geodatabas...

[VinceAngelo · Accepted solution]

An ArcGIS Query Layer will return the contents of a SQL query. That query can contain a spatial predicate, as would be valid from a query in SQL Server Management Studio.

If you're looking for SQL examples using Microsoft geometry and/or geography columns, you should probably be reviewing Microsoft documentation, not Esri sources (Esri hasn't supported their own geometry model in SQL Server in a decade, so there isn't really anything called an "SDE geometry" anymore).

The OGC "Within" operator (STWithin) is tricky because the definition of "within" doesn't include overlap at boundaries (you might need to use STIntersects then filter from there).

The principal limitation for a Query Layer is that you cannot return more than one geometry column; returning CLOB/NCLOB columns in the result set might be problematic, as well (depending on the release and exact query).

Without a concrete attempt at a query, with a description of the tables involved, it's difficult to provide a more specific response.

- V

[JeanDesormeaux]

Hi Vince,

Thank you for fast reply, really appreciated. You are right, with SQL Query Layer is working well. I didn't realize one of the table wasn't in the right Spatial Reference, so nothing was working well.

I recreated my table in right Spatial Reference and STWithin/STIntersects working fine.

Thank you!

Jean

[DominicRoberge2]

here an example: I have Burial sites flagged as Limited Sales. I want to know where in the Cemetery Which Sections) So I use this query to create custom reports.

Is that what you were looking for?

select SECTION_NAME, COUNT(*) as RecCount
from
(
select l.*, s.SECTION_NAME,s.SUB_SECTION_NAME,s.SUB_SUB_SECTION_NAME,s.OBJECTID as UID
from 
(select 
	OBJECTID,Shape,Name
	from COA_CEMETERY_BURIAL_SITES where Name='Limited Sale') as l
	cross JOIN
(select * from COA_CEMETERY_SECTIONS) as s
WHERE        (s.Shape.STContains(l.shape.STCentroid()) = 1)) t
group by t.SECTION_NAME

[VinceAngelo]

That query doesn't return a geometry. Query Tables are also a thing, but you can't map them. If you isolated the 't' virtual table as a Query Layer, it might work.  Have you tried?

- V

[DominicRoberge2]

I didn't realized he needed the geometry... this one will returns all the records 

select * from COA_CEMETERY_SECTIONS
where OBJECTID in(select UID  from
(
select l.*, s.SECTION_NAME,s.SUB_SECTION_NAME,s.SUB_SUB_SECTION_NAME,s.OBJECTID as UID
from 
(select 
	OBJECTID,Shape,Name
	from COA_CEMETERY_BURIAL_SITES where Name='Limited Sale') as l
	cross JOIN
(select * from COA_CEMETERY_SECTIONS) as s
WHERE        (s.Shape.STContains(l.shape.STCentroid()) = 1)) t
group by t.UID)