Find latitude and longitude point in geometry field

Question

I have a SQL Server reference table that stores statistical areas for Australia. To build this I had to import a .shp (shape) file using QGIS (I was provided an Excel file but it didn't have any geometry fields in it)

This created a table automatically upon importing the .shp file into SQL Server 2019 (standard edt)

CREATE TABLE [dbo].[sa2_2021_aust_gda2020](
    [ogr_fid] [int] IDENTITY(1,1) NOT NULL,
    [ogr_geometry] [geometry] NULL,
    [sa2_code21] [nvarchar](max) NULL,
    [sa2_name21] [nvarchar](max) NULL,
    .......
CREATE SPATIAL INDEX [ogr_dbo_sa2_2021_aust_gda2020_ogr_geometry_sidx] ON [dbo].[sa2_2021_aust_gda2020]
(
    [ogr_geometry]
)USING  GEOMETRY_GRID 

Sample output

I then have a transactional table with latitude and longitude in it, and I want to join the below table to the above table, to find the geo row from above table where the latitude and longitude combination set falls within the geometry field. This would then allow me to bring back say the sa2_name21/Region field/name from the reference table for reporting purposes.

I have tried using something like below but I get no results

Select top 100 casenumber, g1.SA2_NAME21, g1.SA3_NAME21, Arrive_Long, Arrive_Lat 
FROM            BI_PRD.dbo.Activity_Case A1
Left join GEO_data..sa2_2021_aust_gda2020 G1 
ON              G1.ogr_geometry.STContains(geometry::Point(A1.Arrive_Long, A1.Arrive_Lat, 4326)) = 1
Order by CaseNumber desc

Answer 1

The coordinate systems of the datasets need to match when using STContains.

https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/stcontains-geometry-data-type?view=sql-server-ver15

"STContains() always returns null if the spatial reference IDs (SRIDs) of the geometry instances do not match."

Given that your G1 dataset is called 'sa2_2021_aust_gda2020' id wager that its in GDA202, whereas your A1 dataset is being specified as 4326 (which is WGS84).

However the measurement differences (in Latitude and Longitude) between WGS84 and GDA2020 are going to be in the order of about 20cms or thereabouts. So depending upon your accuracy requirements, you could just specify the GDA2020 ESPG number for your A1 geometry. (its 7844)

This assumes of course that your imported shapefile was imported into SQL as that coord system. You can check this by doing something like

select SHAPE.STSrid from dbo.STATES

where SHAPE is the geometry column and dbo.states is the table name. In your instance something like

ogr_geometry.STSRID from sa2_2021_aust_gda2020

If the shapefile has imported as a different ESPG, then probably report back here, because your solution approach will be different.

But the answer is, your comparing two geometries with different coordinate systems. You will need to fix this somehow.