Syntax to use when entering geographic data into geography columns via SQLSERVER Management Studio

Question

I am starting to learn about spatially aware databases.  I have QGIS 2.61 and SQLserver express 2012.  I can set up a table using the  SQL Server Management studio GUI and insert a geography column.  What syntax should I use when entering data into that column to store, for instance, the coordinates for a polygon or linestring etc.  Try as I might I cannot find an answer other than using Transact SQL which is not what I want to do.

jport · Answer

If you have lat long point locations in a sql table and you've added a geometry field you can convert ths Lat Long to geometry with this code:

UPDATE tablename 
 Set geographyfield = geography::STGeomFromText('POINT(' + 
 CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS 
 VARCHAR(20)) + ')', 4326)

It's much more complicated with polygons and line strings and best to use other software to write the layers to sql tables. You can also use ogr and python if you have shape files or other formats and want to store them in sql server

Syntax to use when entering geographic data into geography columns via SQLSERVER Management Studio

One Answer

Add your own answers!

Ask a Question