Cannot bulk copy GDA2020 SHP files to SQL Server

[jumpinjackie]

Today, FDO Toolbox failed the dogfood test trying to import ABS 2021 SA1 boundary SHP files into SQL Server

https://www.abs.gov.au/statistics/standards/australian-statistical-geography-standard-asgs-edition-3/jul2021-jun2026/access-and-downloads/digital-boundary-files

I've yet to determine the true culprit (as I wasn't debugging FDO Toolbox at the time), but here are the known facts.

1. SQL Server does not natively know about EPSG:7844 in spatial_ref_sys, thus this definition has to be registered in com/ExtendedCoordSys.txt of the FDO Toolbox installation
2. When bulk copying the SHP file, it was set up to copy into a pre-defined table with a geography column
3. Despite mapping the SHP geometry property, the geography column on SQL Server is all NULL values

I speculate that it could be any of the following:

• The SHP geometry does not insert into a SQL Server geography
• The SHP geometry property mapping isn't actually established despite the UI saying so (case sensitivity perhaps?)
• The geometry data is rejected due to non-native support for GDA2020 in SQL Server
  • We could be bulk copying into a SQL Server instance that does not have KB4506023 applied (does it need to be applied?)

[jumpinjackie]

The issue was in 2 places.

1. The SQL Server provider did not know about GDA2020.LL coordinate system without some assistance (by registering this in ExtendedCoordSys.txt file)
2. The default behavior of creating new spatial contexts on-the-fly for a bulk copy does not work well for new/obscure coordinate systems. The recommended strategy is to a. Create the spatial context up-front with the required coordinate system (with CS-Map assisted normalization) b. When setting up the bulk copy, activate the Use Target Spatial Context option and pick the SC that was created. This will skip the whole on-the-fly SC creation process.