ArcGIS Pro and SQLServer - An Intro to Spatial Queries
Channel: GIS Coordinated
Date: 2021-06-17

Video #108
This video will get you going with using SQLServer (in this case Express) as the database for all your spatial queries.  Output can be displayed in ArcGIS Pro/ArcMap.

This is not a lesson on SQLServer (or SQL!) but a beginner can follow it - just install SQLServer Express from:

 https://www.microsoft.com/en-gb/sql-server/sql-server-downloads

In the video I:

- Demonstrate a spatial query in ArcGIS Pro
- Create a New Database in SQLServer using Studio
- Connect to that New Database in ArcGIS Pro
- Import 2 feature classes into that Database - points (the pubs) and polygons (the postcodes)
- Demonstrate the following spatial query, using Studio:

MAKE SURE BOTH TABLES HAVE THE SAME PROJECTION e.g. 3857!

SELECT distinct Name from PCODES o, [dbo].[PUBS] p  WHERE  o.Shape.STContains(p.shape) = 1;

- Create a View which gives ArcGIS Pro a TABLE to play with (not mappable!)
- Alter that View using Studio to add the OBJECTID and SHAPE columns (now mappable!)
- Refresh the View in ArcGIS Pro and map it

The final SQL is:

Create View PubsOutsideLeics AS
select 
Pubname, OBJECTID, SHAPE from [dbo].[PUBS]
where Code not in 
    (
    select distinct
    Code
    from [dbo].[PUBS] p
    join [dbo].[PCODES] o
    on p.Shape.STWithin(o.Shape) = 1
    )