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
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.