Spatial SQL -> MS-SQL 2008. Create and populate table with spatial data

In the following example we will create table with geography column and will add to it points based on coordinates of city attractions (points of interest).
/* create table with geography column and spatial index */

CREATE TABLE [dbo].[milano_pnt](
[id] [int] NOT NULL,
[name] [nvarchar](100) NULL,
[type] [nvarchar](50) NULL,
[lat] FLOAT NULL,
[lon] FLOAT NULL,
[g] GEOGRAPHY
CONSTRAINT [PK_milano_pnt] PRIMARY KEY CLUSTERED
([id] ASC))
ON [PRIMARY];

/*
spatial index have to be created alongside unique clustered index (in our case we have one on id column)
*/


CREATE SPATIAL INDEX [geo_sidx] ON [dbo].[milano_pnt]
([g]) USING GEOGRAPHY_GRID
WITH (
GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

/*
add SRID constraint to ensure use of "WGS 84" spatial reference system
*/


ALTER TABLE [dbo].[milano_pnt] WITH CHECK
ADD CONSTRAINT [enforce_srid_geography_milano_pnt]
CHECK ([g].[STSrid]=4326);

ALTER TABLE [dbo].[milano_pnt] CHECK CONSTRAINT [enforce_srid_geography_milano_pnt];



Create spatial data from coordinates of milano points of interest is possible using one of three methods:

GEOGRAPHY::STGeomFromText( 'POINT(' + lat + ' ' + lon + ')',4326)
GEOGRAPHY::Parse( 'POINT(' + lat + ' ' + lon + ')')
'POINT(' + lat + ' ' + lon + ')'

INSERT INTO milano_pnt (id, name, [type], lat, lon, g)
SELECT id, name, [type], CAST(lat AS FLOAT), CAST(lon AS FLOAT),
'POINT(' + lat + ' ' + lon + ')'
FROM dbo.milan_pnt_src
ORDER BY id;



sqlexamples.info