I am having problems loading a 3D layer from MS SQL Server to PostgreSQL PostGIS database with QGIS. The PostGIS data loader in DB Manager and other import tools in QGIS have problems with 3D export to PostGIS. Since I don't need the Z component, I wanted to remove it. PostGIS has a ST_Force2D function. I can't find such a function for SQL Server. I don't want to use external ETL tools. How can I strip Z values from SQL Server geometries?

  • You've got a conflict between not wanting to use third-party tools and using QGIS. Either you do want to use QGIS, in which case you should probably detail the exact procedure and error, or you only want to use SQL Server, in which case you might need to write some .Net code. – Vince Jun 13 at 11:42
  • It seems like there is no function in SQL server dealing with 3D->2D transformation of geometry. I know nothing about .net so I will use ETL and sync everything to PostGIS. I don't have time to deal with next "missing" spatial function I know from PostGIS in MSSQL. Apart from that I think QGIS data export should have 3D capabilities. Thanks for help! – Greg Jun 13 at 12:07
  • 1
    You will have to parse the actual texttual representation and rebuild with just the x and y parts, ie, you will have to write a TSQL function. – John Powell Jun 14 at 8:26
  • 1
    With ogr2ogr gdal.org/programs/ogr2ogr.html#ogr2ogr and -dim 2 it is rather simple, though not pure QGIS solution. You may call it ETL if you wish. – user30184 Jun 16 at 21:20

I guess migrating whole database to my PostgreSQL is just easier for the things I do. Thanks anyway.

  • Migrating to Postgres is definitely a good move. Overall, it has way more spatial support that SQL Server. – John Powell Jun 18 at 11:09
  • 1
    Sure, exactly what I did. I moved all the data with ETL to PostGIS database. Spatial functions in SQL server are terrible to put it mildly. From insane spatial indexing, lack of basic spatial functions to terrible performance. For now my only limitation is overnight sync with our coorporate spatial database. I use my database for analytics and reporting. Now that I have all data in my personal database I just might create a web GIS for attribute data collection. – Greg Jun 19 at 6:52
  • Yes, plus with Postgis you get raster, routing, 3D, transforms and a bunch of other things. Good call. – John Powell Jun 19 at 8:33

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.