3

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

enter image description here

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

1 Answer 1

5

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.

3
  • Hi thanks for your reply. I managed to import the shape file into SQL server as EPSG:4326 using QGIS, by passing it another parameter/argument in the command string. This imported the geometry field in this spatial format, where the value starts with 0xE61 rather than 0xA41E. I am no spatial expert but it does appear as you've said you need to get it into the right format type.
    – Tony
    Dec 9, 2021 at 4:10
  • My select statement above, joining the transactional table and reference table together now works, and I get results returned. I couldn't run Select ogr_geometry.STSRID from sa2_2021_aust_gda2020 as noted above, as I get the error Could not find property or field 'STSRID' for type 'Microsoft.SqlServer.Types.SqlGeometry' in assembly 'Microsoft.SqlServer.Types'. So might be missing some component perhaps.
    – Tony
    Dec 9, 2021 at 4:11
  • not sure, here is the doco on STSRID - docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/…
    – nr_aus
    Dec 9, 2021 at 4:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.