Close

October 8, 2013

Uploading ESRI Shapefile into Microsoft SQL Server 2008 Spatial Database

Microsoft SQL Server from release 2008, 2008 R2, 2012 and above, support spatial data types.

Tool used for this – shape2sql – by Morten Nielsen/SharpGIS can be found by clicking here.

Sample test data used was downloaded from here – OpenStreetMap and  here – Processed Coastline Data

Screenshot

Capture_spatial

After import into SQL, this translated in rows/size as:

Capture_spatial2

T-SQL code for table generated:

USE [Spatial] GO
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATE TABLE [dbo].[Processed_p]
(  [ID] [int] IDENTITY(1,1) NOT NULL,
[error] [smallint] NULL,
[tile_x] [int] NULL,
[tile_y] [int] NULL,
[geom] [geometry] NULL,
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
ALTER TABLE [dbo].[Processed_p]
WITH CHECK ADD  CONSTRAINT [enforce_srid_geometry_Processed_p] CHECK  (([geom].[STSrid]=(0))) GO

ALTER TABLE [dbo].[Processed_p]
CHECK CONSTRAINT [enforce_srid_geometry_Processed_p] GO