SQL Server Express – starting to work with Geometry Functions in SQL Server

Firstly I created a database.

This is essentially me trying to implement in SQL Server what I had written about in Postgres here – Using ST_Within in Postgres

Now I create the starting tables.

CREATE TABLE [dbo].[t00001fields](
	[pkid] [int] IDENTITY(1,1) NOT NULL,
	[fieldname] [varchar](50) NULL,
	[geom] [geometry] NULL

Then

CREATE TABLE [dbo].[t00002plots](
	[pkid] [int] IDENTITY(1,1) NOT NULL,
	[plotname] [varchar](50) NULL,
	[geom] [geometry] NULL

Now a magic junction table that will identify what plot is within which field when it is run

CREATE VIEW v001FieldPlotJunction AS
SELECT t00002plots.pkid as Plotspkid,t00001fields.pkid as Fieldspkid
FROM
t00002plots,
t00001fields
WHERE
t00002plots.GEOM.STWithin(t00001fields.GEOM) = 1;

and here’s an example of it running on local in SQL Express

And this is what I am seeing when viewed through QGIS = many of the fields are smaller than the plots and so although 1 2 look like they should be in the view because the plot is not WITHIN the field it does not show which is the correct response in this case.