I am trying to run a basic point in polygon query against a table of polygons. In PostGIS this works:
SELECT * FROM schema.table WHERE ST_Contains( table.geom, ST_PointFromText('POINT(XXX.XXXXXXX YY.YYYYYY)',4283) );
As I understand it, this is the equivalent in SQL Server:
SELECT * FROM schema.table WHERE column.STContains( geography::STPointFromText('POINT(XXX.XXXXXXX YY.YYYYYY)',4283) ) = 1;
Both tables are generated from exactly the same shapefile. For PostGIS I use shp2pgsql and for SQL Server I use ogr2ogr to convert the data. In SQL Server I need to run a query to MakeValid the 8 errors in the source data (confirmed in QGIS).
When I run the above query in PostGIS I am returned a single, correct polygon. When I run the other query in SQL Server I am returned a set of polygons, none of which is correct.
Can someone assist me in getting the query correct for SQL Server?