[GIS] QGIS : Creating point feature from Lat, Lon SQL Server 2008R2 data import

qgis

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?

Best Answer

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 
SELECT  
        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?)

Related Question