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