How to set a point as default value for a geography column?

Question

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

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

score 4 · Accepted Answer · answered Jul 17 '14 at 08:53

try this:

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

Just not to create double answer... can be also `DEFAULT geography::Point(0, 0, 4326)` — Darka, Jul 17 '14 at 08:56

score 1 · Answer 2 · edited May 23 '17 at 12:33

1

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

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

edited May 23 '17 at 12:33

Community

1
1

answered Jul 17 '14 at 08:46

Andriy M

71,352
17
87
142

score 0 · Answer 3 · answered Jul 17 '14 at 08:39

0

I don't think you can do it directly. You could use an insert trigger in a roundabout way, but it's not a completely satisfactory solution.

answered Jul 17 '14 at 08:39

podiluska

49,221
7
86
96

How to set a point as default value for a geography column?

3 Answers3