1

I am trying insert a record in t-sql as below. I am struck in adding a value to a geography datatype column.

This is a value for geography:

data = 0xE6100000010C8FE4F21FD26F49406002B7EEE6A9E13F

Please advise me how to add value to geography value in t-sql statement.

Insert statement

    GO

INSERT INTO [dbo].[GeographicLocation]
           ([RecordGUID]
           ,[Postcode]
           ,[Eastings]
           ,[Northings]
           ,[Longitude]
           ,[Latitude]
           ,[MilesFromWisley]
           ,[MilesFromRosemoor]
           ,[MilesFromHarlowCarr]
           ,[MilesFromHydeHall]
           ,[NearestRHSGardenId]
           ,[Geolocation]
           ,[CreatedBy]
           ,[CreatedOn]
           ,[UpdatedBy]
           ,[UpdatedOn])
     VALUES
           (newId()
           ,'A1 3cc'
           ,'579648'
           ,'111337'
           ,'0.55199'
           ,'0.55199'
           ,'0.55199'
           ,'0.55199'
           ,'0.55199'
           ,'0.55199'
           ,1
           ,<Geolocation, geography,> 
           ,'Admin'
           ,GETDATE()
           ,'Admin'
           ,GETDATE())
GO

Table

    GO

/****** Object:  Table [dbo].[GeographicLocation]    Script Date: 29/07/2014 17:23:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[GeographicLocation](
    [Id] [int] IDENTITY(1,1) NOT NULL,  
    [Postcode] [nvarchar](10) NOT NULL,
    [Eastings] [varchar](6) NOT NULL,
    [Northings] [varchar](7) NOT NULL,
    [Longitude] [float] NOT NULL,
    [Latitude] [float] NOT NULL,
    [MilesFromWisley] [float] NOT NULL,
    [MilesFromRosemoor] [float] NOT NULL,
    [MilesFromHarlowCarr] [float] NOT NULL,
    [MilesFromHydeHall] [float] NOT NULL,
    [NearestRHSGardenId] [int] NOT NULL,
    [Geolocation] [geography] NOT NULL,
    [CreatedBy] [varchar](50) NOT NULL,
    [CreatedOn] [datetime] NOT NULL,
    [UpdatedBy] [varchar](50) NOT NULL,
    [UpdatedOn] [datetime] NOT NULL,
 CONSTRAINT [PK_GeographicLocation] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Please see data to insert below in csv file format:

    RecordGUID,Postcode,Eastings,Northings,Longitude,Latitude,MilesFromWisley,MilesFromRosemoor,MilesFromHarlowCarr,MilesFromHydeHall,NearestRHSGarden,Geolocation,CreatedBy,CreatedOn,UpdatedBy,UpdatedOn
3EA36A53-4A3B-423D-9EE8-00000D7D7D10,TN38 9DU,579648,111337,0.55199,50.8736,54.00910156,204.9701573,232.9195992,54.82085336,4,0xE6100000010C8FE4F21FD26F49406002B7EEE6A9E13F,Tun,29/07/2014 16:11,NULL,NULL
C913CE04-CE58-45D3-A50E-000011B6E7D5,OX20 1RF,444949,215133,-1.349094,51.83299,52.07852776,135.3794838,148.9129785,83.33775562,4,0xE6100000010C91F2936A9FEA49405CAD1397E395F5BF,Tun,29/07/2014 16:11,NULL,NULL

1 Answer 1

1

Check this: Where X put your longitude value, Y put latitude value and Z put altitude value.

 INSERT INTO [dbo].[GeographicLocation]
               ([RecordGUID]
               ,[Postcode]
               ,[Eastings]
               ,[Northings]
               ,[Longitude]
               ,[Latitude]
               ,[MilesFromWisley]
               ,[MilesFromRosemoor]
               ,[MilesFromHarlowCarr]
               ,[MilesFromHydeHall]
               ,[NearestRHSGardenId]
               ,[Geolocation]
               ,[CreatedBy]
               ,[CreatedOn]
               ,[UpdatedBy]
               ,[UpdatedOn])
         VALUES
               (newId()
               ,'A1 3cc'
               ,'579648'
               ,'111337'
               ,'0.55199'
               ,'0.55199'
               ,'0.55199'
               ,'0.55199'
               ,'0.55199'
               ,'0.55199'
               ,1
               ,STGeomFromWKB(0xE6100000010C8FE4F21FD26F49406002B7EEE6A9E13F, 4326)
               ,'Admin'
               ,GETDATE()
               ,'Admin'
               ,GETDATE())
    GO
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.