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

Question

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?

Answer 1

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

• Prayut0831: 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: Point layer from table is what I was looking for. It works!!!