topic Re: SDE Query under SQL Server in Geodatabase Questions

SDE Query under SQL Server

JeanDesormeaux — Tue, 04 Oct 2022 13:10:02 GMT

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

Re: SDE Query under SQL Server

MarceloMarques — Tue, 04 Oct 2022 17:44:42 GMT

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

Re: SDE Query under SQL Server

VinceAngelo — Tue, 04 Oct 2022 18:16:49 GMT

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

Re: SDE Query under SQL Server

DominicRobergeIADOT — Tue, 04 Oct 2022 18:36:10 GMT

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

Re: SDE Query under SQL Server

VinceAngelo — Tue, 04 Oct 2022 18:55:14 GMT

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

Re: SDE Query under SQL Server

DominicRobergeIADOT — Tue, 04 Oct 2022 19:17:32 GMT

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)

Re: SDE Query under SQL Server

JeanDesormeaux — Wed, 05 Oct 2022 11:49:41 GMT

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