4

We have several hundred datasets/spatial layers within SQL Server 2016 which has the geometry datum/projection of GDA1994 Zone 54 (EPSG:28354) and we wish to reproject/shift the spatial locations to the new Australia GDA2020 datum, Zone 54 (EPSG:7854), how is this done?

Or does the spatial data need to be reprojected in say FME and re-loaded into SQL?

  • As GDA2020 is a relatively new datum, I don't think ST_Transform will support it in SQL Server 2016. It may be supported in 2017, however. I would definitely use FME 2018 or higher to reproject the datasets back into SQL Server 2016. – Fezter Oct 9 '18 at 2:02
  • There is a GSB transformation available but I don't know if the EPSG code is supported in your database. QGIS 3.2 does support EPSG:7854 but ArcGIS 10.2.1 does not. I'm not sure this information will help as I know very little about spatial types in SQL Server. – Michael Stimson Oct 9 '18 at 2:30
  • 2
    +1 to the original question before being edited by the mod. It was clear, easy to understand, and connected us with a real person who identified himself as working for the council (so for the community). It is much nicer to read questions like these than impersonal questions written by/for computers. – jberrio Oct 9 '18 at 13:32
2

This may be personal opinion, a bit, but I would not recommend modifying your data's coordinates until the production database that you store the data in can handle the new coordinate reference system.

Here's why:

  1. Discoverability: if you store data in there now, you can't attribute the data with the correct CRS, you'll be saying it's GDA94 still, which is wrong, misleading and dangerous. Sure, the coordinates are 'better' if they're GDA2020, but they're not going to be right if you label them with GDA94.
  2. Deadlines: sure, people are starting to deliver data in GDA2020, and that's great, and software like FME and QGIS support the new CRSs based on the new datum, but still, there's going to be lots of data around in the old CRS for a long time. So there's no looming deadline.
  3. Do it once, do it right: working with complex systems is ... complex, and you don't want to have to redo it in your new SQL Server 2020, or whenever Microsoft supports the new CRSs. So just wait. Also, you can use this time to complain to a vendor about it and to encourage them to fix the root problem!

So, what alternatives do you have? Either wait for a version of SQL Server that supports GDA2020, or perhaps explore using something like PostGIS, which already does!

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.