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
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
https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/sql-access-to-geodatabase-data.htm
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
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?
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)
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!
Signed in members can post, follow updates, and more. New here? Register a free account.
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
