GeoServer/SQLServer cheatsheet

gistfile1.txt

-- Before call to ogr2ogr, drop table

DROP table wellington_buildings
DROP table wellington_buildings_geog
DROP TABLE buildings_subset_small
DROP TABLE buildings_subset_small_geog

-- Call ogr2ogr to import AS GEOMETRY

SELECT * FROM wellington_buildings

-- Modified version of http://blogs.msdn.com/b/edkatibah/archive/2008/08/19/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1b.aspx

UPDATE buildings_subset_small 
  SET ogr_geometry = ogr_geometry.STUnion(ogr_geometry.STStartPoint()); 


UPDATE wellington_buildings 
  SET ogr_geometry = ogr_geometry.STUnion(ogr_geometry.STStartPoint()); 

-- 
-- Create table with Geography column 
-- 


CREATE TABLE buildings_subset_small_geog ( 
  id   INTEGER, 
  geog GEOGRAPHY); 

CREATE TABLE wellington_buildings_geog ( 
  id   INTEGER NOT NULL, 
  geog GEOGRAPHY NOT NULL); 

ALTER TABLE [dbo].[wellington_buildings_geog] ADD  CONSTRAINT [PK_wellington_buildings_geog] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE SPATIAL INDEX [SpatialIndex-20141014-233619] ON [dbo].[wellington_buildings_geog]
(
	[geog]
)USING  GEOGRAPHY_AUTO_GRID 
WITH (
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO



-- 
-- Convert from Geometry to Geography using Well Known Text 
-- 

INSERT INTO buildings_subset_small_geog 
  SELECT buildings_subset_small.ogr_fid, GEOGRAPHY::STGeomFromText(buildings_subset_small.ogr_geometry.STAsText(),4326) 
  FROM buildings_subset_small; 

  

INSERT INTO wellington_buildings_geog 
  SELECT wellington_buildings.ogr_fid, GEOGRAPHY::STGeomFromText(wellington_buildings.ogr_geometry.STAsText(),4326) 
  FROM wellington_buildings; 

SELECT ogr_geometry.STArea(), * from wellington_buildings
SELECT geog.STArea(), * from wellington_buildings_geog




-- Geometry spatial index


CREATE SPATIAL INDEX [SpatialIndex-20141015-010247] ON [dbo].[wellington_buildings]
(
	[ogr_geometry]
)USING  GEOMETRY_GRID 
WITH (BOUNDING_BOX =(-176, -42, -172, -40), GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), 
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO