1

I am trying to import point data from a MSSQL database to QGIS. Is it possible to declare some SQL to create the geometry (X,Y) on the fly? The MSSQL table importer does have an SQL column on the side, but i'm not sure of the correct syntax to get this to work or if its even possible?

('POINT('+convert(nvarchar(24),convert(decimal(18,4), p.y)) + '  ' + convert(nvarchar(24),convert(decimal(18,4), p.x))+')') as geomtext

enter image description here

2

Couldn't use the MSSQL table importer, but alternatively achieved a similar result with a DSN connection and a VRT file.

  1. Add DSN connections using "C:\Windows\SysWOW64\odbcad32.exe"
  2. User DSN/Add Tab..
  3. "SQL Server Native Client 10.0"
  4. Name:
  5. Description:
  6. Server:
  7. Login: "Not all SQL Servers will have Intergrated Windows authentication, so type username and password here if you know it"
  8. Change the default database to: "You must chose the database you wish to use, otherwise QGIS gets all the system databases instead"
  9. Click Finish and then test the data source "TEST COMPLETED SUCCESSFULLY"
  10. You could also create this DSN with a *.bat file using the code below
REG ADD "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources" /V "<ConnectionName>" /T reg_sz /D "SQL Server Native Client 10.0" /F
REG ADD "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\<ConnectionName>" /V "Database" /T reg_sz /D "<SqlDatabaseItem>" /F
REG ADD "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\<ConnectionName>" /V "Description" /T reg_sz /D "<ConnectionName>" /F
REG ADD "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\<ConnectionName>" /V "Driver" /T reg_sz /D "C:\WINDOWS\SysWOW64\sqlncli10.dll" /F
REG ADD "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\<ConnectionName>" /V "LastUser" /T reg_sz /D "" /F
REG ADD "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\<ConnectionName>" /V "Server" /T reg_sz /D "<SQLHostName>" /F
REG ADD "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\<ConnectionName>" /V "Trusted_Connection" /T reg_sz /D "Yes" /F
  1. Now create a VRT file as shown below. Copy the CODE into notepad and save as *.vrt
<OGRVRTDataSource>
<OGRVRTLayer name="--Chose Layer Name--">
    <SrcDataSource>ODBC:"DatabaseName","DatabaseLayer"</SrcDataSource>
<SrcLayer>"DatabaseLayer"</SrcLayer>
<GeometryType>wkbPoint</GeometryType>
    <LayerSRS>"EPSG:23586(CRS or EPSG code)"</LayerSRS>
<GeometryField encoding="PointFromColumns" x="X field Name" y="Y FieldName"/>
</OGRVRTLayer>
</OGRVRTDataSource>

Finally drag in the VRT to QGIS to see your points enter image description here

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.