How to set default value for geography column?

Question

I have this SQL command that creates a table with the GEOGRAPHY-type column for spatial data, designed to hold a latitude / longitude (dot) pair. How to set default value for this column? For example, the point at (0,0)?

CREATE TABLE [dbo].[StationLocations] (
    [Id] [int] NOT NULL,
    [StationId] [int] NOT NULL,
    [Position] [Geography],
CONSTRAINT [PKStationLocations] PRIMARY KEY CLUSTERED ([Id]))

Answer 1

try the following:

CREATE TABLE #TEMP(ID INT,COL1 GEOGRAPHY DEFAULT(CONVERT(GEOGRAPHY,'POINT (0 0)')))
INSERT INTO #TEMP (ID) VALUES(1)
SELECT * FROM #TEMP
ID  COL1
1   0xE6100000010C00000000000000000000000000000000

SELECT ID,CONVERT(VARCHAR,COL1) AS DEF_VALUE FROM #TEMP
ID  DEF_VALUE
1   POINT (0 0)

In your case

CREATE TABLE [dbo].[StationLocations] (
    [Id] [int] NOT NULL,
    [StationId] [int] NOT NULL,
    [Position] [Geography] DEFAULT(CONVERT(GEOGRAPHY,'POINT (0 0)')),
CONSTRAINT [PKStationLocations] PRIMARY KEY CLUSTERED ([Id]))

Answer 2

Not sure if this is the best method, but using this answer as a reference, you can define it like this :

ALTER TABLE dbo.StationLocations
ADD CONSTRAINT DF_StationLocations_Position
DEFAULT geography::STPointFromText('POINT (0 0)', 4326) FOR Position
;

Answer 3

I do not think you can do this directly. You can use the insert trigger in a workaround, but this is not a completely satisfactory solution.