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.

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

Then

1
2
3
4
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

1
2
3
4
5
6
7
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.