Search code examples
sql-server-2008geolocationsql-insertgeography

SQL Geography Data type doesn't recognize latitude and longitude

I created a table

CREATE TABLE [dbo].[Geographic](
    [SEPM_Code] [varchar](50) NOT NULL,
    [Rack_Code] [varchar](50) NOT NULL,
    [Team] [varchar](50) NOT NULL,
    [Address] [varchar](max) NOT NULL,
    [Longitude] [geography] NOT NULL,
    [Latitude] [geography] NOT NULL,
 CONSTRAINT [PK_Geographic] PRIMARY KEY CLUSTERED 
(
    [SEPM_Code] ASC
)

And when I try the following insert

insert into dbo.Geographic
values ('31R001','31R001','Staten Island','100 Cool Ave',-74.243950,40.508638)

I get

Msg 206, Level 16, State 2, Line 1
Operand type clash: numeric is incompatible with geography

I wish to create a database of codes, addresses, with latitude and longitude to eventually import into R programming and create a heat map.

Right now I am trying to understand geography data type, as it may help with this.

I would think that latitude and longitude float values are compatible with geography, yet I get error.

Please advise

Solution

  • Latitude and longitude are floating point numbers. If you want to store them in the database as separate columns, use float data type. Keep in mind that you loose the ability to utilize spatial features of the database in this case, such as spatial indexes.

    Geography data type is used for objects like point, line, polyline, etc. When a geography column stores a point, it includes both latitude and longitude in it.