Inserting lat/long data into geography column of SQL Server 2012?

Question

is it possible to insert lat/long data into geography column just by typing the coordinates lat/long as a double or string:

INSERT INTO LOT_EXPLOITATION_LOT VALUES (3621,'oui','Forêt Boréale',560000,'Interdiction d''exploiter le bois, aux alentours du Lac Achinuseskasich, dans un rayon de 15 km.',(**'-75.45','45.36'**))

Answer 1

You should create a GEOMETRY data type column in your table to store the coordinates as spatial objects.

And I'll assume you have your coordinate pair values in columns called LAT and LON... and I'm naming your source and destination columns arbitrarily based on your data above...see the SQL below.

You should be able to do this:

alter table LOT_EXPLOITATION_LOT
add column geom geometry

Then when you insert the points, you can insert them into the GEOG column as spatial data by using the STGeomFromText function - you also want to use the correct syntax for the INSERT as well, so It might look something like this:

INSERT INTO LOT_EXPLOITATION_LOT
(dest_column1
, dest_column2
, dest_column3
, dest_column4
, dest_column5
, geom)

select
source_column1
, source_column2
, source_column3
, source_column4
, source_column5
, geometry::STGeomFromText('POINT('+convert(varchar(20),data.lon)+' '+convert(varchar(20),data.lat)+')',4326) as geom

4326 is the SRID (coordinate system) for your data as it looks like it's in decimal degrees... is that correct?

Anyway, let me know what you think.

Some would also say to use the GEOGRAPHY data type for your data as it is in decimal degrees - Geometry also works for decimal degree data as far as I know.

Answer 2

Make a user-defined function (UDF):

CREATE FUNCTION [dbo].[GetGeoFromLatLng]
(
    @lat decimal(9,6), 
    @lng decimal(9,6)
)
RETURNS geography WITH SCHEMABINDING
AS
BEGIN
    DECLARE @ret geography

    select @ret = geography::STPointFromText('POINT(' + convert(nvarchar(25), @lng) +' ' + convert(nvarchar(25), @lat) + ')', 4326);        

    RETURN @ret
END

Use like this:

SELECT dbo.GetGeoFromLatLng(35,-118)