lifted from here: http://stackoverflow.com/questions/2920948/selecting-a-good-sql-server-2008-spatial-index-with-large-polygons

Basically crate the SPROC listed at the bottom, then setup some example calls (3 or 4 different ones to minimise caching artefacts)

Then set it running and go on holiday …

Setup your parameters:

DECLARE @g1 VARCHAR(MAX)
SET @g1 = 'GEOMETRY::STGeomFromText(''POLYGON((252783.989267352 461095.507712082,260134.010732648 461095.507712082,260134.010732648 465068.492287918,252783.989267352 465068.492287918,252783.989267352 461095.507712082))'', 28992)'
DECLARE @g2 VARCHAR(MAX)
SET @g2 = 'GEOMETRY::STGeomFromText(''POLYGON((5580.146375 5340.100667,655613.186375 5340.100667,655613.186375 1219811.501845,5580.146375 1219811.501845,5580.146375 5340.100667))'',28992)'
exec sp_tune_spatial_index 'OBSVEG.OBS_Locations' ,'sidx_OBS_Locations_geom',2,8,@g1,@g2

Create Stored procedure:

N.B. Assumes geometry field is called geom
N.B. You MUST modify the SPROC to use extents for your SRID

USE [CMSI-OBSVEG]
GO
/****** Object:  StoredProcedure [dbo].[sp_tune_spatial_index]    Script Date: 10/01/2013 11:35:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_tune_spatial_index]
(
  @tabnm                VARCHAR(MAX),    -- This parameter stores the name of the spatial table for which you are tuning the index
    @idxnm                VARCHAR(MAX),    -- This parameter stores the name of the spatial index of the named table
    @min_cells_per_obj    INT,            -- Minimum Cells Per Object to test on. Suggested to start at 2.
    @max_cells_per_obj    INT,            -- Maximum Cells Per Object to test on.
    
    /*    The test requires two geometry instances to use in test query 1 and 2.
        The first one should cover the area of default extent. The second should
        cover an area roughly the size of the area shown when zoomed in, panning
        around. It is required that the variable store a string that will create
        the geometry instance since this will be done within the procedure and 
        cannot be a variable of type: GEOMETRY. The SRID of these instances must
        match that of the table you are testing. */
    @testgeom1            VARCHAR(MAX),    -- This parameter stores the first geometry instance creation string that will be used in the test
    @testgeom2            VARCHAR(MAX)    -- This parameter stores the second geometry instance creation string that will be used in the test
    
)

AS

SET NOCOUNT ON;

/*    Prior to running this procedure, two tables are required. These tables are 
    created here to prepare for running the procedure.    */

PRINT 'Checking for required tables...'
IF EXISTS(SELECT 1 FROM sysobjects WHERE name IN ('cell_opt_perm', 'spat_idx_test_result'))
    BEGIN
        PRINT '... The "cell_opt_perm" and "spat_idx_test_result" tables exist.'
    END
ELSE
BEGIN
    PRINT '... Creating "cell_opt_perm" and "spat_idx_test_result" tables.'
    CREATE TABLE cell_opt_perm(
        [perm_id] [smallint] NOT NULL,
        [permutation] [nvarchar](4) NOT NULL,
        [level1] [nvarchar](6) NOT NULL,
        [level2] [nvarchar](6) NOT NULL,
        [level3] [nvarchar](6) NOT NULL,
        [level4] [nvarchar](6) NOT NULL
    )

    INSERT INTO cell_opt_perm ([perm_id], [permutation], [level1], [level2], [level3], [level4])
    VALUES (1,'LLLL','LOW','LOW','LOW','LOW'),
        (2,'LLLM','LOW','LOW','LOW','MEDIUM'),
        (3,'LLLH','LOW','LOW','LOW','HIGH'),
        (4,'LLML','LOW','LOW','MEDIUM','LOW'),
        (5,'LLMM','LOW','LOW','MEDIUM','MEDIUM'),
        (6,'LLMH','LOW','LOW','MEDIUM','HIGH'),
        (7,'LLHL','LOW','LOW','HIGH','LOW'),
        (8,'LLHM','LOW','LOW','HIGH','MEDIUM'),
        (9,'LLHH','LOW','LOW','HIGH','HIGH'),
        (10,'LMLL','LOW','MEDIUM','LOW','LOW'),
        (11,'LMLM','LOW','MEDIUM','LOW','MEDIUM'),
        (12,'LMLH','LOW','MEDIUM','LOW','HIGH'),
        (13,'LMML','LOW','MEDIUM','MEDIUM','LOW'),
        (14,'LMMM','LOW','MEDIUM','MEDIUM','MEDIUM'),
        (15,'LMMH','LOW','MEDIUM','MEDIUM','HIGH'),
        (16,'LMHL','LOW','MEDIUM','HIGH','LOW'),
        (17,'LMHM','LOW','MEDIUM','HIGH','MEDIUM'),
        (18,'LMHH','LOW','MEDIUM','HIGH','HIGH'),
        (19,'LHLL','LOW','HIGH','LOW','LOW'),
        (20,'LHLM','LOW','HIGH','LOW','MEDIUM'),
        (21,'LHLH','LOW','HIGH','LOW','HIGH'),
        (22,'LHML','LOW','HIGH','MEDIUM','LOW'),
        (23,'LHMM','LOW','HIGH','MEDIUM','MEDIUM'),
        (24,'LHMH','LOW','HIGH','MEDIUM','HIGH'),
        (25,'LHHL','LOW','HIGH','HIGH','LOW'),
        (26,'LHHM','LOW','HIGH','HIGH','MEDIUM'),
        (27,'LHHH','LOW','HIGH','HIGH','HIGH'),
        (28,'MLLL','MEDIUM','LOW','LOW','LOW'),
        (29,'MLLM','MEDIUM','LOW','LOW','MEDIUM'),
        (30,'MLLH','MEDIUM','LOW','LOW','HIGH'),
        (31,'MLML','MEDIUM','LOW','MEDIUM','LOW'),
        (32,'MLMM','MEDIUM','LOW','MEDIUM','MEDIUM'),
        (33,'MLMH','MEDIUM','LOW','MEDIUM','HIGH'),
        (34,'MLHL','MEDIUM','LOW','HIGH','LOW'),
        (35,'MLHM','MEDIUM','LOW','HIGH','MEDIUM'),
        (36,'MLHH','MEDIUM','LOW','HIGH','HIGH'),
        (37,'MMLL','MEDIUM','MEDIUM','LOW','LOW'),
        (38,'MMLM','MEDIUM','MEDIUM','LOW','MEDIUM'),
        (39,'MMLH','MEDIUM','MEDIUM','LOW','HIGH'),
        (40,'MMML','MEDIUM','MEDIUM','MEDIUM','LOW'),
        (41,'MMMM','MEDIUM','MEDIUM','MEDIUM','MEDIUM'),
        (42,'MMMH','MEDIUM','MEDIUM','MEDIUM','HIGH'),
        (43,'MMHL','MEDIUM','MEDIUM','HIGH','LOW'),
        (44,'MMHM','MEDIUM','MEDIUM','HIGH','MEDIUM'),
        (45,'MMHH','MEDIUM','MEDIUM','HIGH','HIGH'),
        (46,'MHLL','MEDIUM','HIGH','LOW','LOW'),
        (47,'MHLM','MEDIUM','HIGH','LOW','MEDIUM'),
        (48,'MHLH','MEDIUM','HIGH','LOW','HIGH'),
        (49,'MHML','MEDIUM','HIGH','MEDIUM','LOW'),
        (50,'MHMM','MEDIUM','HIGH','MEDIUM','MEDIUM'),
        (51,'MHMH','MEDIUM','HIGH','MEDIUM','HIGH'),
        (52,'MHHL','MEDIUM','HIGH','HIGH','LOW'),
        (53,'MHHM','MEDIUM','HIGH','HIGH','MEDIUM'),
        (54,'MHHH','MEDIUM','HIGH','HIGH','HIGH'),
        (55,'HLLL','HIGH','LOW','LOW','LOW'),
        (56,'HLLM','HIGH','LOW','LOW','MEDIUM'),
        (57,'HLLH','HIGH','LOW','LOW','HIGH'),
        (58,'HLML','HIGH','LOW','MEDIUM','LOW'),
        (59,'HLMM','HIGH','LOW','MEDIUM','MEDIUM'),
        (60,'HLMH','HIGH','LOW','MEDIUM','HIGH'),
        (61,'HLHL','HIGH','LOW','HIGH','LOW'),
        (62,'HLHM','HIGH','LOW','HIGH','MEDIUM'),
        (63,'HLHH','HIGH','LOW','HIGH','HIGH'),
        (64,'HMLL','HIGH','MEDIUM','LOW','LOW'),
        (65,'HMLM','HIGH','MEDIUM','LOW','MEDIUM'),
        (66,'HMLH','HIGH','MEDIUM','LOW','HIGH'),
        (67,'HMML','HIGH','MEDIUM','MEDIUM','LOW'),
        (68,'HMMM','HIGH','MEDIUM','MEDIUM','MEDIUM'),
        (69,'HMMH','HIGH','MEDIUM','MEDIUM','HIGH'),
        (70,'HMHL','HIGH','MEDIUM','HIGH','LOW'),
        (71,'HMHM','HIGH','MEDIUM','HIGH','MEDIUM'),
        (72,'HMHH','HIGH','MEDIUM','HIGH','HIGH'),
        (73,'HHLL','HIGH','HIGH','LOW','LOW'),
        (74,'HHLM','HIGH','HIGH','LOW','MEDIUM'),
        (75,'HHLH','HIGH','HIGH','LOW','HIGH'),
        (76,'HHML','HIGH','HIGH','MEDIUM','LOW'),
        (77,'HHMM','HIGH','HIGH','MEDIUM','MEDIUM'),
        (78,'HHMH','HIGH','HIGH','MEDIUM','HIGH'),
        (79,'HHHL','HIGH','HIGH','HIGH','LOW'),
        (80,'HHHM','HIGH','HIGH','HIGH','MEDIUM'),
        (81,'HHHH','HIGH','HIGH','HIGH','HIGH')
    
    CREATE TABLE spat_idx_test_result(
        [perm_id] [int] NOT NULL,
        [num_cells] [int] NOT NULL,
        [permut] [nvarchar](4) NOT NULL,
        [g1t1] [bigint] NULL,
        [g1t2] [bigint] NULL,
        [g1t3] [bigint] NULL,
        [g1t4] [bigint] NULL,
        [g2t1] [bigint] NULL,
        [g2t2] [bigint] NULL,
        [g2t3] [bigint] NULL,
        [g2t4] [bigint] NULL
    )
    
    INSERT INTO dbo.spat_idx_test_result
    VALUES (0,16,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
END


/*    delete all rows from "spat_idx_test_result" table. This makes it ready to stuff in new results.
    !!!WARNING!!! if your test was interupted, the table will be cleared out and the test will
    begin from the beginning. You could try to modify this to start where you left off but
    I didn't have time and this worked well enough for me. */
DELETE FROM spat_idx_test_result
    WHERE perm_id != 0

/* set up counters */
DECLARE @a1 INT
DECLARE @a2 INT
DECLARE @a3 INT
DECLARE @a4 INT

/* set up variables to hold high/medium/low values and permutation to use in rebuilding
   the spatial index and recording stats */
DECLARE @lev1 VARCHAR(6)
DECLARE @lev2 VARCHAR(6)
DECLARE @lev3 VARCHAR(6)
DECLARE @lev4 VARCHAR(6)
DECLARE @permut VARCHAR(6)
DECLARE @num_cell VARCHAR(4)
DECLARE @time_str VARCHAR(20)
DECLARE @perm_id VARCHAR(20)

/* create variables to hold timestamps for beginning and ending of test queries */
DECLARE @start_t DATETIME
DECLARE @end_t DATETIME
DECLARE @elapse_t INT

/* begin looping through cell option permutations */
SET @a1 = @min_cells_per_obj
WHILE @a1 <= @max_cells_per_obj
    BEGIN
        SET @a2 = 1
        PRINT 'Started Testing for ' +CAST(@a1 AS VARCHAR(10)) +' cells per object'
        WHILE @a2 < 82
            BEGIN
                SELECT @lev1 = level1, @lev2 = level2, @lev3 = level3, @lev4 = level4 FROM cell_opt_perm WHERE perm_id = @a2
                SET @permut = '''' +(SELECT permutation FROM cell_opt_perm WHERE perm_id = @a2) +''''
                EXEC
                    ('
                        CREATE SPATIAL INDEX ' +@idxnm +' ON ' +@tabnm +' 
                        (
                            [geom]
                        )
                        USING  GEOMETRY_GRID 
                        WITH
                            (
                                BOUNDING_BOX =(-100, -100, 300000, 650000),
                                GRIDS =(LEVEL_1 = ' +@lev1 +' ,LEVEL_2 = ' +@lev2 +' ,LEVEL_3 = ' +@lev3 +' ,LEVEL_4 = ' +@lev4 +' ), 
                                CELLS_PER_OBJECT = ' +@a1 +' ,
                                PAD_INDEX  = OFF,
                                SORT_IN_TEMPDB = OFF,
                                DROP_EXISTING = ON,
                                ALLOW_ROW_LOCKS  = ON,
                                ALLOW_PAGE_LOCKS  = ON,
                                FILLFACTOR = 100
                            )
                        ON [PRIMARY]'
                    )
                PRINT 'Re-built index to ' +@permut
                SET @a3 = 1
                SET @a4 = 1
                WHILE @a3 < 5
                    BEGIN
                        SET @start_t = GETDATE()
                        EXEC
                            (
                                'CREATE TABLE #tmp_tab (shp GEOMETRY)
                                DECLARE @g1 GEOMETRY
                                SET @g1 = ' +@testgeom1 +'
                                INSERT #tmp_tab (shp)
                                    SELECT
                                        r.geom AS shp
                                    FROM
                                        ' +@tabnm +' r
                                    WHERE
                                        r.geom.STIntersects(@g1) = 1
                                DROP TABLE #tmp_tab'
                            )
                        SET @end_t = GETDATE()
                        SET @elapse_t = (SELECT DATEDIFF(MS, @start_t, @end_t))
                        SET @num_cell = CAST(@a1 AS VARCHAR(6))
                        SET @time_str = CAST(@elapse_t AS VARCHAR(20))
                        IF @a3 = 1
                            BEGIN
                                IF (SELECT TOP 1 perm_id FROM spat_idx_test_result) IS NULL
                                    BEGIN
                                        SET @perm_id = 1
                                    END
                                ELSE
                                    BEGIN
                                        SET @perm_id = CAST((SELECT MAX(perm_id+1) FROM spat_idx_test_result) AS VARCHAR(20))
                                    END
                                EXEC
                                    (
                                        'INSERT INTO spat_idx_test_result (perm_id, num_cells, permut, g1t' +@a3 +')
                                        VALUES (' +@perm_id +', ' +@num_cell +', ' +@permut +', ' +@time_str +')'
                                    )
                            END
                        ELSE
                            EXEC
                                (
                                    'UPDATE spat_idx_test_result
                                    SET
                                    num_cells = ' +@num_cell +',
                                    permut = ' +@permut +',
                                    g1t' +@a3 +' = ' +@time_str +'
                                    WHERE perm_id = ' +@perm_id
                                )
                        SET @a3 = @a3 + 1
                    END
                WHILE @a4 < 5
                    BEGIN
                        SET @start_t = GETDATE()
                        EXEC
                            (
                                'CREATE TABLE #tmp_tab (shp GEOMETRY) 
                                DECLARE @g2 GEOMETRY
                                SET @g2 = ' +@testgeom2 +'
                                INSERT #tmp_tab (shp)
                                    SELECT
                                        r.geom AS shp
                                    FROM
                                        ' +@tabnm +' r
                                    WHERE
                                        r.geom.STIntersects(@g2) = 1
                                DROP TABLE #tmp_tab'
                            )
                        SET @end_t = GETDATE()
                        SET @elapse_t = (SELECT DATEDIFF(MS, @start_t, @end_t))
                        SET @num_cell = CAST(@a1 AS VARCHAR(6))
                        SET @time_str = CAST(@elapse_t AS VARCHAR(20))
                        EXEC
                            (
                                'UPDATE spat_idx_test_result
                                SET
                                num_cells = ' +@num_cell +',
                                permut = ' +@permut +',
                                g2t' +@a4 +' = ' +@time_str +'
                                WHERE perm_id = ' +@perm_id
                            )
                        SET @a4 = @a4 + 1
                    END
                SET @a2 = @a2 + 1
            END
        SET @a1 = @a1 + 1
    END
PRINT 'Testing of ' +@tabnm +' spatial index: ' +@idxnm +' is complete!'

1. setting up and accessing SQL Azure databases
2. using SQL Azure with ArcGIS + ArcSquirrel and MapLink
3. using SQL Azure with Quantum GIS and MapServer.

Azure Management Portal

Although I’ve never set up any cloud services before (other than Arvixe web hosting) it was incredibly easy to setup a trial SQL Azure subscription (“Windows Azure Platform Introductory Special”), create a database, and add some firewall rules to control access. I didn’t dig deep, but the “Management Portal” seems nice and simple:

It helpfully tells you your connection string for ADO.Net, ODBC and PHP (passwords not included!):

See this page for details of the Windows Azure Platform Introductory Special including what you get for no charge and the pricing for the rest.

MapLink, ArcSquirrel and Azure

Hartmut connected up our MapLink to the database and pushed some spatial data into my Azure database from ArcGIS – 20,000 polygons took about 75 minutes.  Hartmut had the layers drawing in ArcMAP, performing as well as in a normal SQL Server instance apparently, with only minor mods to our code to make the initial connection. However editing will not work without some more tweaks - MapLink and ArcSquirrel rely on some SQL metadata that is a little different on Azure.

SQL Server Management Studio and MS Access

Connecting with SSMS is simple, however you get reduced functionality – for example you cannot right-click > edit top 200 records or anything, so quick edits of a small table need to be done with SQL or in MS Access or some other client. Fortunately connecting with MS Access (ODBC + SQL Native Client 10) is also simple, and  the Azure database behaves just like a local SQL instance – remarkable querying speed (all things considered), though slower inserts (10,000 records took something like 15 minutes). NB you cannot directly link a spatial table into Access because it doesn’t recognize the geometry data type; but to update attributes you can link a view.

Also, in SSMS you get no designer tools, e.g. for creating a view; everything has to be done with T-SQL.

SQL Azure Database Manager

After writing the above I found the database manager in the Azure control panel, which has functionality similar to SSMS, including the ability to edit the tables*:

* spatial data types not supported for view or edit.

You get a designer for creating/modifying tables, but views and stored procedures are T-SQL only.

MapServer, OGR and Azure

Next I wanted to try serving the data through MapServer, and viewing/editing the data in Quantum GIS or MapInfo.  MapServer and QGIS require the “geometry_columns” and “spatial_ref_sys” tables, and the easiest way to create these is to push some data in with ogr2ogr.

So I tried this from one of our London servers. I loaded a few hundred linear features, but got four errors like “INSERT command for new feature failed… incorrect syntax…”. On checking the results, a) while the geometries had loaded fine, all the attribute fields were NULL, and b) the spatial_ref_sys table was empty.  I had four attribute fields, so my guess is that each syntax error was one field update across all records using a join on the incoming data.

Next I hooked up a MapServer WMS to my SQL Azure tables. MapServer had no trouble connecting to my Azure tables, but initially wouldn’t draw anything…  on investigating I found that ogr2ogr had not correctly set the SRID of the geometries, so I had to update them “manually” with:

update esdm_waters set ogr_geometry.STSrid = 4326

(which, incidentally, took 1 minute 52 seconds for 788 rows, demonstrating that updating an Azure database ain’t quick).

Clearly OGR needs a little work to do on SQL Azure support, but this was enough to get me going.

The WMS worked, so to challenge it a little more I set up another WMS onto a table with about 8000 sub-tidal habitat polygons, showing red in this OpenLayers map:

And here showing the rivers WMS as well (blue lines):

Performance of these two layers was perfectly acceptable, though actually about 100% slower than the same WMS working from a local SQL Server 2008 Express instance. It would be interesting to see what it could do with MapServer also running on Azure.

I also tried a WFS – again, performance was acceptable though significantly slower than running with a local SQL instance (though I didn’t do strict comparisons).

Quantum GIS and SQL Azure

I found this really helpful blog post to get started: http://woostuff.wordpress.com/2011/03/13/opening-ms-sql-spatial-in-qgis/

Adding the layers requires some python script, but I’m sure someone will write a GUI plugin for QGIS soon.

First make sure you are running a version of QGIS that has GDAL/OGR 1.8 libraries – I’m on 1.7.0 which is OK.

Open the Plugins menu > Python Console

and type in these two commands (modified for your data):

uri = "MSSQL:server={serverName};database={databaseName};tables={tableName};trusted_connection=yes"
qgis.utils.iface.addVectorLayer(uri,'{yourLayerNameHere}','ogr')

In my case:

uri = "MSSQL:server=tcp:a0awtn7gg9.database.windows.net;database=ArcSquirrel1;tables=esdm_waters;Uid=exegesis@a0awtn7gg9;Pwd=myPassword;"
qgis.utils.iface.addVectorLayer(uri,'Rivers','ogr')

Without any fuss my rivers dataset appeared:

And I can create and edit features and their attributes, here adding a test line off the Hebrides:

And to prove it, here’s my new feature in OpenLayers…

And finally, here we have the habitat polygons (EPSG:27700) colour coded by habitat type, as well as the rivers (EPSG:4326), in QGIS. Polygon editing also worked fine, once I’d worked out why my new polygons kept vanishing (doh, classified symbols).

Lots to think about, and I look forward to editing the same data with ArcGIS and ArcSquirrel soon…

• If you have our ArcGIS Maplink product (or ArcSquirrel) then loading data in using this into a temporary SQL table is probably best as it will do a reasonable amount of checking.
• MapInfo has its own SQL loader tool ("Easy Loader" - find it in the Tool Manager)
• If you are just using open source you can use OGR2OGR

ogr2ogr -overwrite -f "MSSQLSpatial" "MSSQL:server=MyServer;database=MyDatabase;Trusted_Connection=yes;" "C:\Temp\MyShapeFile.shp" -a_srs "EPSG:27700"

Once the data is in SQL there can still be issues:

Incorrect SRID

• Make sure you specified the correct SRID when the data was loaded (if you didn’t specify it – it may well be 0). You can check this with the SQL: SELECT geom.STSrid from SitesGeom Where geom.STSrid <> 27700

Invalid ring

• You may also have invalid ring geometries – a good post on this is 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 but the key SQL command is:

UPDATE foo SET geom = geom.STUnion(geom.STStartPoint());

Invalid geometries

UPDATE foo SET geom = geom.MakeValid()

SRID not imported

Import geometry data into temp SQL table (e.g. foo) – with tempGeom column

Then try to populate a geometry column from the WKB of the validated column:

ALTER TABLE foo ADD geomFinal geometry;

SET geomFinal = geometry::STGeomFromWKB(geomTemp.STAsBinary(), 27700)