I am importing a table having no spatial columns from SQL Server 2008R2 database into QGIS 2.10.1. The table has latitude & longitude values. How can I create point features using the latitude, longitude data in the table in QGIS? CSV import is one way but can I do it without CSV import directly from imported data?


Edit: QGIS only way , without CSV

In QGIS, import table from DB, select Processing Toolbox, Qgis Geoalgorithms -> Vector Creation Tools ->points layer from table -> select x and y

MS SQL Server way

one easy way is to create view in SQL where you add computed column. something like this STGeomFromText('POINT(' +x +' ' +y+')')

CREATE VIEW [dbo].[point_geom_text] as 
        row_number() over ( order by k.id) as gid
        , k.id as oid

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

  FROM   dbo.points k

For xy point use

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

Simple WKT for POINT is 'POINT(1 2)'

convert code is there to convert float to decimal and then to text

That will create table with geometry text column , row_number() creates unique row number.

CREATE VIEW geoms as 
SELECT *, geom::STGeomFromText(geomtext, 4326) as geom FROM points_geom_text

Creates view which has geometry in your srid (4326? in your case?)

  • 1
    View would still be created in the SQL server database. I wanted to know whether we can create point from Lat, Long in QGIS. – Prayut0831 Nov 6 '15 at 11:22
  • 1
    Point layer from table is what I was looking for. It works!!! – Prayut0831 Nov 6 '15 at 11:49

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.