Create Database View with ST_Within Query

Question

I see several other questions similar but none that apply directly to mine.

I am using ArcGIS Desktop 10.6.1 with SQL Server running my Enterprise Geodatabase.

I have a very large point feature and a smaller polygon feature class. My goal is to create a view to only show points that fall within the polygon.

The documentation I found for ST_Within on ESRIs page does not exactly help me. In addition, when I tried to run a basic ST-Within query I get an error stating ST-Within is not a recognized function.

• Carlos MSF: What syntax are you using? You should use a condition like point_geometry.STWithin( polygon_geometry ) = 1
• NULL.Dude: @CarlosMSF originally I used Select * FROM points.table WHERE ST_Within(points.table, polygon.table) but that did not work
• Vince: You're barking up the wrong tree. Esri doesn't implement the Geometry type of SQL Server, so you need to use the Microsoft documentation for SQL functions. Not only are they used differently, they're also case-sensitive. Please edit the question to contain your exact syntax.

Answer 1

If I understood well your intent, you can create a spatial view in SQL Server using this SQL:

CREATE VIEW my_spatial_view
AS 
SELECT pt.*
FROM point_table pt
 JOIN polygon_table plg 
 ON pt.Shape.STWithin(plg.Shape) = 1
;

Note: this assumes the geometry column is named Shape, as is the default is ESRI's data models.

You can then add this spatial view to your map in ArcMap, even if you don't register it in the geodatabase.

---

If the one thing you want to do is to filter out objects from one table based on a spatial relation, and assuming that the polygon table is on the same database then the point table, you can also use the Definition Query property of the point layer in ArcMap, setting a filtering condition like:

Shape.STWithin( (SELECT geometry::UnionAggregate(plg.Shape) FROM CML_FREG_PL plg) ) = 1

Definition Query for point layer

NOTE: This can potentially be less interesting in terms of performance, but lets you use the point layer as an editable layer (as long as it was originally register with the geodatabase).

NOTE 2: If your polygon layer only as one record, i.e., one polygon, you don't need to do the UnionAggregate, therefore it will be more efficient in terms of performance

• NULL.Dude: not sure why but this table view recently broke, it shows 0 results.... no table names have changed...