0

I am working with spatial data stored in an MSSQL 2012 database as varbinary. I am able to view the spatial data in SSMS with the following query:

SELECT geometry::STGeomFromWKB([column_name], 4955) FROM table_name;

How can I view this data in QGIS? I have successfully connected to the database in QGIS and have tried to run a similar spatialite sql query on the table as a virtual layer, but it does not work.

I have also tried to add the table to the geometry_columns table. QGIS will recognize it as a spatial layer once I drag the table in from the database, but nothing renders. It still shows up as a nonspatial table in the MSSQL connection.

A user with a similar question created a view in MSSQL, but reported that it could not be imported to QGIS.

In SSMS the records look like '0x001110310...'etc., and in QGIS they are all 'BLOB'.

  • Your view will need an OBJECTID built on the row_number() function. What type of spatial data is it - geometry or geography? – DPSSpatial Jul 3 '19 at 16:03
  • From what i understand the data is stored as varbinary instead of geometry or geography - the only way I can view the spatial results is using the query above. The data can also be viewed spatially in a GIS routing software that my company uses to connect to the database. In the database schema the column type is varbinary. – Andrew Jul 3 '19 at 16:17
  • Thank you @DPSSpatial .... I used the unique objectid field to create a view using the SSMS and it shows up in QGIS! – Andrew Jul 3 '19 at 16:31
0

I was able to create a view in sql server using the following:

CREATE VIEW new_table AS SELECT geometry::STGeomFromWKB([column_name], 4955) FROM table_name;

And it showed up in my MSSQL connection in QGIS as a spatial layer.

  • it worked?! can you see it on a map?? – DPSSpatial Jul 3 '19 at 16:50
  • Yes, it worked but I just realized that I did not include the objectid in my answer. The actual query was: CREATE VIEW new_table AS SELECT ObjID, geometry::STGeomFromWKB([column_name], 4955) AS geom FROM table_name; I added that view to QGIS and it shows up on the map. – Andrew Jul 3 '19 at 18:16
  • ok good stuff!! – DPSSpatial Jul 3 '19 at 18:30

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.