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'.

Improve this question
3
  • Your view will need an OBJECTID built on the row_number() function. What type of spatial data is it - geometry or geography?
    – Inactivated Account
    Commented Jul 3, 2019 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.
    – Andriy
    Commented Jul 3, 2019 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!
    – Andriy
    Commented Jul 3, 2019 at 16:31

1 Answer 1

Reset to default
1

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

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

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

Improve this answer
3
  • it worked?! can you see it on a map??
    – Inactivated Account
    Commented Jul 3, 2019 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.
    – Andriy
    Commented Jul 3, 2019 at 18:16
  • ok good stuff!!
    – Inactivated Account
    Commented Jul 3, 2019 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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