SQL Server production load example log
-- ************************************************************************
--
-- Description:
--
-- Rapid Enquiry Facility (RIF) - Tile maker
--
-- Copyright:
--
-- The Rapid Inquiry Facility (RIF) is an automated tool devised by SAHSU
-- that rapidly addresses epidemiological and public health questions using
-- routinely collected health and population data and generates standardised
-- rates and relative risks for any given health outcome, for specified age
-- and year ranges, for any given geographical area.
--
-- Copyright 2014 Imperial College London, developed by the Small Area
-- Health Statistics Unit. The work of the Small Area Health Statistics Unit
-- is funded by the Public Health England as part of the MRC-PHE Centre for
-- Environment and Health. Funding for this project has also been received
-- from the Centers for Disease Control and Prevention.
--
-- This file is part of the Rapid Inquiry Facility (RIF) project.
-- RIF is free software: you can redistribute it and/or modify
-- it under the terms of the GNU Lesser General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
--
-- RIF is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU Lesser General Public License for more details.
--
-- You should have received a copy of the GNU Lesser General Public License
-- along with RIF. If not, see <http://www.gnu.org/licenses/>; or write
-- to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
-- Boston, MA 02110-1301 USA
--
-- Author:
--
-- Peter Hambly, SAHSU
--
-- This script is autogenerated.
--
--
-- Load processed geomtry and tiles tables into production database:
--
-- a) integrate with RIF40 control tables, e.g:
-- * geography_usa_2014
-- * geolevels_usa_2014
-- b) Processed geometry data (partitioned in PostGres), e.g:
-- * geometry_usa_2014
-- c) Hierarchy table, e.g:
-- * hierarchy_usa_2014
-- d) Lookup tables, e.g:
-- * lookup_cb_2014_us_county_500k
-- * lookup_cb_2014_us_nation_5m
-- * lookup_cb_2014_us_state_500k
-- e) Tiles table and view
-- * t_tiles_usa_2014
-- * tiles_usa_2014
--
--
-- MS SQL Server specific parameters
--
-- Usage: sqlcmd -U rif40 -d <database name> -b -m-1 -e -r1 -i rif_mssql_USA_2014.sql -v pwd="%cd%"
-- Connect flags if required: -P <password> -S<myServerinstanceName>
--
-- You must set the current schema if you cannot write to the default schema!
-- You need create privilege for the various object and the bulkadmin role
--
-- USE <my database>;
--
SET QUOTED_IDENTIFIER ON;
-- SET STATISTICS TIME ON;
--
-- Set schema variable used by scripts etc to RIF_DATA
--
--
-- SQL statement 0: Start transaction >>>
BEGIN TRANSACTION;
-- SQL statement 1: RIF initialisation >>>
/*
* SQL statement name: rif_startup.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters: None
*
* Description: Run RIF startup script
* Note: % becomes % after substitution
*/
--
-- Eof;
-- SQL statement 2: Check if geography is in use in studies. Raise error if it is. >>>
/*
* SQL statement name: in_use_check.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: Geography; e.g. SAHSULAND
*
* Description: Check if geography is in use in studies. Raise error if it is.
* To prevent accidental replacement
* Note: % becomes % after substitution
*/
DECLARE c1 CURSOR FOR
SELECT COUNT(DISTINCT(a.study_id)) AS total
FROM t_rif40_studies a
WHERE a.geography = 'USA_2014';
DECLARE @c1_total AS int;
OPEN c1;
FETCH NEXT FROM c1 INTO @c1_total;
IF @c1_total = 0
PRINT 'Geography: USA_2014 is not used by any studies';
ELSE
RAISERROR('Geography: USA_2014 is used by: %d studies', 16, 1, @c1_total);
CLOSE c1;
DEALLOCATE c1;
Geography: USA_2014 is not used by any studies
--
-- Create Geolevels lookup tables
--
-- SQL statement 4: Drop table lookup_cb_2014_us_nation_5m >>>
IF OBJECT_ID('rif_data.lookup_cb_2014_us_nation_5m', 'U') IS NOT NULL DROP TABLE rif_data.lookup_cb_2014_us_nation_5m;
-- SQL statement 5: Create table lookup_cb_2014_us_nation_5m >>>
/*
* SQL statement name: create_lookup_table.sql
* Type: Microsoft SQL Server T/sql
* Parameters:
* 1: table; e.g. lookup_sahsu_grd_level1
* 2: Lookup column - shapefile table name, e.g. sahsu_grd_level1
* 3: Schema; e.g. rif_data. or ""
*
* Description: Create lookup table
* Note: %% becomes % after substitution
*/
CREATE TABLE rif_data.lookup_cb_2014_us_nation_5m (
cb_2014_us_nation_5m NVARCHAR(100) NOT NULL,
areaname NVARCHAR(1000),
gid INTEGER NOT NULL,
geographic_centroid VARCHAR(1000),
PRIMARY KEY (cb_2014_us_nation_5m)
);
-- SQL statement 6: Comment table lookup_cb_2014_us_nation_5m >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_table.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. cb_2014_us_county_500k
* 2: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
*
* Description: Comment table
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.lookup_cb_2014_us_nation_5m'
ELSE
SELECT @tableName = 'rif_data.lookup_cb_2014_us_nation_5m';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = 0)
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Lookup table for The County at a scale of 1:500,000',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_nation_5m'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Lookup table for The County at a scale of 1:500,000',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_nation_5m';
-- SQL statement 7: Comment lookup_cb_2014_us_nation_5m columns >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.lookup_cb_2014_us_nation_5m'
ELSE
SELECT @tableName = 'rif_data.lookup_cb_2014_us_nation_5m';
SELECT @columnName = 'cb_2014_us_nation_5m';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Area ID field',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_nation_5m',
@level2type = N'Column', @level2name = 'cb_2014_us_nation_5m'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Area ID field',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_nation_5m',
@level2type = N'Column', @level2name = 'cb_2014_us_nation_5m';
-- SQL statement 8: Comment lookup_cb_2014_us_nation_5m columns >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.lookup_cb_2014_us_nation_5m'
ELSE
SELECT @tableName = 'rif_data.lookup_cb_2014_us_nation_5m';
SELECT @columnName = 'gid';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'GID field',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_nation_5m',
@level2type = N'Column', @level2name = 'gid'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'GID field',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_nation_5m',
@level2type = N'Column', @level2name = 'gid';
-- SQL statement 9: Comment lookup_cb_2014_us_nation_5m columns >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.lookup_cb_2014_us_nation_5m'
ELSE
SELECT @tableName = 'rif_data.lookup_cb_2014_us_nation_5m';
SELECT @columnName = 'areaname';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Area Name field',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_nation_5m',
@level2type = N'Column', @level2name = 'areaname'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Area Name field',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_nation_5m',
@level2type = N'Column', @level2name = 'areaname';
-- SQL statement 10: Comment lookup_cb_2014_us_nation_5m columns >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.lookup_cb_2014_us_nation_5m'
ELSE
SELECT @tableName = 'rif_data.lookup_cb_2014_us_nation_5m';
SELECT @columnName = 'geographic_centroid';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Geographic centroid',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_nation_5m',
@level2type = N'Column', @level2name = 'geographic_centroid'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Geographic centroid',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_nation_5m',
@level2type = N'Column', @level2name = 'geographic_centroid';
-- SQL statement 11: Drop table lookup_cb_2014_us_state_500k >>>
IF OBJECT_ID('rif_data.lookup_cb_2014_us_state_500k', 'U') IS NOT NULL DROP TABLE rif_data.lookup_cb_2014_us_state_500k;
-- SQL statement 12: Create table lookup_cb_2014_us_state_500k >>>
/*
* SQL statement name: create_lookup_table.sql
* Type: Microsoft SQL Server T/sql
* Parameters:
* 1: table; e.g. lookup_sahsu_grd_level1
* 2: Lookup column - shapefile table name, e.g. sahsu_grd_level1
* 3: Schema; e.g. rif_data. or ""
*
* Description: Create lookup table
* Note: %% becomes % after substitution
*/
CREATE TABLE rif_data.lookup_cb_2014_us_state_500k (
cb_2014_us_state_500k NVARCHAR(100) NOT NULL,
areaname NVARCHAR(1000),
gid INTEGER NOT NULL,
geographic_centroid VARCHAR(1000),
PRIMARY KEY (cb_2014_us_state_500k)
);
-- SQL statement 13: Comment table lookup_cb_2014_us_state_500k >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_table.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. cb_2014_us_county_500k
* 2: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
*
* Description: Comment table
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.lookup_cb_2014_us_state_500k'
ELSE
SELECT @tableName = 'rif_data.lookup_cb_2014_us_state_500k';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = 0)
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Lookup table for The nation at a scale of 1:5,000,000',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_state_500k'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Lookup table for The nation at a scale of 1:5,000,000',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_state_500k';
-- SQL statement 14: Comment lookup_cb_2014_us_state_500k columns >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.lookup_cb_2014_us_state_500k'
ELSE
SELECT @tableName = 'rif_data.lookup_cb_2014_us_state_500k';
SELECT @columnName = 'cb_2014_us_state_500k';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Area ID field',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_state_500k',
@level2type = N'Column', @level2name = 'cb_2014_us_state_500k'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Area ID field',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_state_500k',
@level2type = N'Column', @level2name = 'cb_2014_us_state_500k';
-- SQL statement 15: Comment lookup_cb_2014_us_state_500k columns >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.lookup_cb_2014_us_state_500k'
ELSE
SELECT @tableName = 'rif_data.lookup_cb_2014_us_state_500k';
SELECT @columnName = 'gid';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'GID field',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_state_500k',
@level2type = N'Column', @level2name = 'gid'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'GID field',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_state_500k',
@level2type = N'Column', @level2name = 'gid';
-- SQL statement 16: Comment lookup_cb_2014_us_state_500k columns >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.lookup_cb_2014_us_state_500k'
ELSE
SELECT @tableName = 'rif_data.lookup_cb_2014_us_state_500k';
SELECT @columnName = 'areaname';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Area Name field',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_state_500k',
@level2type = N'Column', @level2name = 'areaname'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Area Name field',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_state_500k',
@level2type = N'Column', @level2name = 'areaname';
-- SQL statement 17: Comment lookup_cb_2014_us_state_500k columns >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.lookup_cb_2014_us_state_500k'
ELSE
SELECT @tableName = 'rif_data.lookup_cb_2014_us_state_500k';
SELECT @columnName = 'geographic_centroid';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Geographic centroid',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_state_500k',
@level2type = N'Column', @level2name = 'geographic_centroid'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Geographic centroid',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_state_500k',
@level2type = N'Column', @level2name = 'geographic_centroid';
-- SQL statement 18: Drop table lookup_cb_2014_us_county_500k >>>
IF OBJECT_ID('rif_data.lookup_cb_2014_us_county_500k', 'U') IS NOT NULL DROP TABLE rif_data.lookup_cb_2014_us_county_500k;
-- SQL statement 19: Create table lookup_cb_2014_us_county_500k >>>
/*
* SQL statement name: create_lookup_table.sql
* Type: Microsoft SQL Server T/sql
* Parameters:
* 1: table; e.g. lookup_sahsu_grd_level1
* 2: Lookup column - shapefile table name, e.g. sahsu_grd_level1
* 3: Schema; e.g. rif_data. or ""
*
* Description: Create lookup table
* Note: %% becomes % after substitution
*/
CREATE TABLE rif_data.lookup_cb_2014_us_county_500k (
cb_2014_us_county_500k NVARCHAR(100) NOT NULL,
areaname NVARCHAR(1000),
gid INTEGER NOT NULL,
geographic_centroid VARCHAR(1000),
PRIMARY KEY (cb_2014_us_county_500k)
);
-- SQL statement 20: Comment table lookup_cb_2014_us_county_500k >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_table.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. cb_2014_us_county_500k
* 2: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
*
* Description: Comment table
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.lookup_cb_2014_us_county_500k'
ELSE
SELECT @tableName = 'rif_data.lookup_cb_2014_us_county_500k';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = 0)
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Lookup table for The State at a scale of 1:500,000',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_county_500k'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Lookup table for The State at a scale of 1:500,000',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_county_500k';
-- SQL statement 21: Comment lookup_cb_2014_us_county_500k columns >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.lookup_cb_2014_us_county_500k'
ELSE
SELECT @tableName = 'rif_data.lookup_cb_2014_us_county_500k';
SELECT @columnName = 'cb_2014_us_county_500k';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Area ID field',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_county_500k',
@level2type = N'Column', @level2name = 'cb_2014_us_county_500k'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Area ID field',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_county_500k',
@level2type = N'Column', @level2name = 'cb_2014_us_county_500k';
-- SQL statement 22: Comment lookup_cb_2014_us_county_500k columns >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.lookup_cb_2014_us_county_500k'
ELSE
SELECT @tableName = 'rif_data.lookup_cb_2014_us_county_500k';
SELECT @columnName = 'gid';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'GID field',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_county_500k',
@level2type = N'Column', @level2name = 'gid'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'GID field',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_county_500k',
@level2type = N'Column', @level2name = 'gid';
-- SQL statement 23: Comment lookup_cb_2014_us_county_500k columns >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.lookup_cb_2014_us_county_500k'
ELSE
SELECT @tableName = 'rif_data.lookup_cb_2014_us_county_500k';
SELECT @columnName = 'areaname';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Area Name field',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_county_500k',
@level2type = N'Column', @level2name = 'areaname'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Area Name field',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_county_500k',
@level2type = N'Column', @level2name = 'areaname';
-- SQL statement 24: Comment lookup_cb_2014_us_county_500k columns >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.lookup_cb_2014_us_county_500k'
ELSE
SELECT @tableName = 'rif_data.lookup_cb_2014_us_county_500k';
SELECT @columnName = 'geographic_centroid';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Geographic centroid',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_county_500k',
@level2type = N'Column', @level2name = 'geographic_centroid'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Geographic centroid',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'lookup_cb_2014_us_county_500k',
@level2type = N'Column', @level2name = 'geographic_centroid';
--
-- Load geolevel lookup tables
--
-- SQL statement 26: Load DB specific geolevel lookup table: (mssql_/pg_)lookup_cb_2014_us_nation_5m >>>
BULK INSERT rif_data.lookup_cb_2014_us_nation_5m
FROM 'C:\Users\phamb\Documents\Local Data Loading\Tile maker USA/mssql_lookup_cb_2014_us_nation_5m.csv' -- Note use of pwd; set via -v pwd="%cd%" in the sqlcmd command line
WITH
(
FORMATFILE = 'C:\Users\phamb\Documents\Local Data Loading\Tile maker USA/mssql_lookup_cb_2014_us_nation_5m.fmt', -- Use a format file
TABLOCK -- Table lock
);
(1 rows affected)
-- SQL statement 27: Load DB specific geolevel lookup table: (mssql_/pg_)lookup_cb_2014_us_state_500k >>>
BULK INSERT rif_data.lookup_cb_2014_us_state_500k
FROM 'C:\Users\phamb\Documents\Local Data Loading\Tile maker USA/mssql_lookup_cb_2014_us_state_500k.csv' -- Note use of pwd; set via -v pwd="%cd%" in the sqlcmd command line
WITH
(
FORMATFILE = 'C:\Users\phamb\Documents\Local Data Loading\Tile maker USA/mssql_lookup_cb_2014_us_state_500k.fmt', -- Use a format file
TABLOCK -- Table lock
);
(56 rows affected)
-- SQL statement 28: Load DB specific geolevel lookup table: (mssql_/pg_)lookup_cb_2014_us_county_500k >>>
BULK INSERT rif_data.lookup_cb_2014_us_county_500k
FROM 'C:\Users\phamb\Documents\Local Data Loading\Tile maker USA/mssql_lookup_cb_2014_us_county_500k.csv' -- Note use of pwd; set via -v pwd="%cd%" in the sqlcmd command line
WITH
(
FORMATFILE = 'C:\Users\phamb\Documents\Local Data Loading\Tile maker USA/mssql_lookup_cb_2014_us_county_500k.fmt', -- Use a format file
TABLOCK -- Table lock
);
(3233 rows affected)
--
-- Hierarchy table
--
-- SQL statement 30: Drop table hierarchy_usa_2014 >>>
IF OBJECT_ID('rif_data.hierarchy_usa_2014', 'U') IS NOT NULL DROP TABLE rif_data.hierarchy_usa_2014;
-- SQL statement 31: Create table hierarchy_usa_2014 >>>
CREATE TABLE rif_data.hierarchy_usa_2014 (
cb_2014_us_county_500k VARCHAR(100) NOT NULL,
cb_2014_us_nation_5m VARCHAR(100) NOT NULL,
cb_2014_us_state_500k VARCHAR(100) NOT NULL);
-- SQL statement 32: Add primary key hierarchy_usa_2014 >>>
ALTER TABLE rif_data.hierarchy_usa_2014 ADD PRIMARY KEY (cb_2014_us_county_500k);
-- SQL statement 33: Add index key hierarchy_usa_2014_cb_2014_us_state_500k >>>
CREATE INDEX hierarchy_usa_2014_cb_2014_us_state_500k ON rif_data.hierarchy_usa_2014 (cb_2014_us_state_500k);
-- SQL statement 34: Comment table: hierarchy_usa_2014 >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_table.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. cb_2014_us_county_500k
* 2: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
*
* Description: Comment table
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.hierarchy_usa_2014'
ELSE
SELECT @tableName = 'rif_data.hierarchy_usa_2014';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = 0)
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Hierarchy lookup table for US 2014 Census geography to county level',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'hierarchy_usa_2014'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Hierarchy lookup table for US 2014 Census geography to county level',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'hierarchy_usa_2014';
-- SQL statement 35: Comment column: hierarchy_usa_2014.cb_2014_us_county_500k >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.hierarchy_usa_2014'
ELSE
SELECT @tableName = 'rif_data.hierarchy_usa_2014';
SELECT @columnName = 'cb_2014_us_county_500k';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Hierarchy lookup for The County at a scale of 1:500,000',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'hierarchy_usa_2014',
@level2type = N'Column', @level2name = 'cb_2014_us_county_500k'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Hierarchy lookup for The County at a scale of 1:500,000',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'hierarchy_usa_2014',
@level2type = N'Column', @level2name = 'cb_2014_us_county_500k';
-- SQL statement 36: Comment column: hierarchy_usa_2014.cb_2014_us_nation_5m >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.hierarchy_usa_2014'
ELSE
SELECT @tableName = 'rif_data.hierarchy_usa_2014';
SELECT @columnName = 'cb_2014_us_nation_5m';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Hierarchy lookup for The nation at a scale of 1:5,000,000',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'hierarchy_usa_2014',
@level2type = N'Column', @level2name = 'cb_2014_us_nation_5m'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Hierarchy lookup for The nation at a scale of 1:5,000,000',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'hierarchy_usa_2014',
@level2type = N'Column', @level2name = 'cb_2014_us_nation_5m';
-- SQL statement 37: Comment column: hierarchy_usa_2014.cb_2014_us_state_500k >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.hierarchy_usa_2014'
ELSE
SELECT @tableName = 'rif_data.hierarchy_usa_2014';
SELECT @columnName = 'cb_2014_us_state_500k';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Hierarchy lookup for The State at a scale of 1:500,000',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'hierarchy_usa_2014',
@level2type = N'Column', @level2name = 'cb_2014_us_state_500k'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Hierarchy lookup for The State at a scale of 1:500,000',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'hierarchy_usa_2014',
@level2type = N'Column', @level2name = 'cb_2014_us_state_500k';
--
-- Load hierarchy table
--
-- SQL statement 39: Load DB dependent hierarchy table from CSV file >>>
BULK INSERT rif_data.hierarchy_usa_2014
FROM 'C:\Users\phamb\Documents\Local Data Loading\Tile maker USA/mssql_hierarchy_usa_2014.csv' -- Note use of pwd; set via -v pwd="%cd%" in the sqlcmd command line
WITH
(
FORMATFILE = 'C:\Users\phamb\Documents\Local Data Loading\Tile maker USA/mssql_hierarchy_usa_2014.fmt', -- Use a format file
TABLOCK -- Table lock
);
(3233 rows affected)
--
-- Create geometry table
--
-- SQL statement 41: Drop geometry table geometry_usa_2014 >>>
IF OBJECT_ID('rif_data.geometry_usa_2014', 'U') IS NOT NULL DROP TABLE rif_data.geometry_usa_2014;
-- SQL statement 42: Create geometry table geometry_usa_2014 >>>
/*
* SQL statement name: create_geometry_table.sql
* Type: Common SQL statement
* Parameters:
* 1: table; e.g. geometry_cb_2014_us_500k
* 2: schema; e.g.rif_data. or ""
*
* Description: Create geometry table
* Note: % becomes % after substitution
*/
CREATE TABLE rif_data.geometry_usa_2014 (
geolevel_id INTEGER NOT NULL,
areaid VARCHAR(200) NOT NULL,
zoomlevel INTEGER NOT NULL);
-- SQL statement 43: Add geom geometry column >>>
/*
* SQL statement name: add_geometry_column2.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: Table name; e.g. geometry_cb_2014_us_500k
* 2: column name; e.g. geom
* 3: Column SRID; e.g. 4326 [NEVER USED IN SQL SERVER, set during WKT conversion]
* 4: Spatial geometry type: e.g. POINT, MULTIPOLYGON [NEVER USED IN SQL SERVER]
* 5: Schema (rif_data. or "")
*
* Description: Add *** geometry *** column to table
* Note: %% becomes % after substitution
*/
ALTER TABLE rif_data.geometry_usa_2014 ADD geom geometry;
-- SQL statement 44: Add bbox geometry column >>>
/*
* SQL statement name: add_geometry_column2.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: Table name; e.g. geometry_cb_2014_us_500k
* 2: column name; e.g. geom
* 3: Column SRID; e.g. 4326 [NEVER USED IN SQL SERVER, set during WKT conversion]
* 4: Spatial geometry type: e.g. POINT, MULTIPOLYGON [NEVER USED IN SQL SERVER]
* 5: Schema (rif_data. or "")
*
* Description: Add *** geometry *** column to table
* Note: %% becomes % after substitution
*/
ALTER TABLE rif_data.geometry_usa_2014 ADD bbox geometry;
-- SQL statement 45: Comment geometry table column >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.geometry_usa_2014'
ELSE
SELECT @tableName = 'rif_data.geometry_usa_2014';
SELECT @columnName = 'bbox';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Bounding box',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'geometry_usa_2014',
@level2type = N'Column', @level2name = 'bbox'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Bounding box',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'geometry_usa_2014',
@level2type = N'Column', @level2name = 'bbox';
-- SQL statement 46: Comment geometry table >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_table.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. cb_2014_us_county_500k
* 2: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
*
* Description: Comment table
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.geometry_usa_2014'
ELSE
SELECT @tableName = 'rif_data.geometry_usa_2014';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = 0)
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'All geolevels geometry combined into a single table for a single geography',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'geometry_usa_2014'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'All geolevels geometry combined into a single table for a single geography',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'geometry_usa_2014';
-- SQL statement 47: Comment geometry table column >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.geometry_usa_2014'
ELSE
SELECT @tableName = 'rif_data.geometry_usa_2014';
SELECT @columnName = 'geolevel_id';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'ID for ordering (1=lowest resolution). Up to 99 supported.',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'geometry_usa_2014',
@level2type = N'Column', @level2name = 'geolevel_id'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'ID for ordering (1=lowest resolution). Up to 99 supported.',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'geometry_usa_2014',
@level2type = N'Column', @level2name = 'geolevel_id';
-- SQL statement 48: Comment geometry table column >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.geometry_usa_2014'
ELSE
SELECT @tableName = 'rif_data.geometry_usa_2014';
SELECT @columnName = 'zoomlevel';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Zoom level: 0 to maxoomlevel (11). Number of tiles is 2**<zoom level> * 2**<zoom level>; i.e. 1, 2x2, 4x4 ... 2048x2048 at zoomlevel 11',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'geometry_usa_2014',
@level2type = N'Column', @level2name = 'zoomlevel'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Zoom level: 0 to maxoomlevel (11). Number of tiles is 2**<zoom level> * 2**<zoom level>; i.e. 1, 2x2, 4x4 ... 2048x2048 at zoomlevel 11',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'geometry_usa_2014',
@level2type = N'Column', @level2name = 'zoomlevel';
-- SQL statement 49: Comment geometry table column >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.geometry_usa_2014'
ELSE
SELECT @tableName = 'rif_data.geometry_usa_2014';
SELECT @columnName = 'areaid';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Area ID.',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'geometry_usa_2014',
@level2type = N'Column', @level2name = 'areaid'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Area ID.',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'geometry_usa_2014',
@level2type = N'Column', @level2name = 'areaid';
-- SQL statement 50: Comment geometry table column >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.geometry_usa_2014'
ELSE
SELECT @tableName = 'rif_data.geometry_usa_2014';
SELECT @columnName = 'geom';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Geometry data in SRID 4326 (WGS84).',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'geometry_usa_2014',
@level2type = N'Column', @level2name = 'geom'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Geometry data in SRID 4326 (WGS84).',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'geometry_usa_2014',
@level2type = N'Column', @level2name = 'geom';
--
-- Load geometry table
--
-- SQL statement 52: Add WKT column >>>
/*
* SQL statement name: add_column.sql
* Type: Common SQL
* Parameters:
* 1: Table name; e.g. geometry_usa_2014
* 2: column name; e.g. wkt
* 3: Column datatype; e.g. Text or VARCHAR(MAX)
*
* Description: Add column to table
* Note: % becomes % after substitution
*/
ALTER TABLE rif_data.geometry_usa_2014
ADD WKT VARCHAR(MAX);
-- SQL statement 53: Comment geometry WKT column >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.geometry_usa_2014'
ELSE
SELECT @tableName = 'rif_data.geometry_usa_2014';
SELECT @columnName = 'wkt';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Well known text',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'geometry_usa_2014',
@level2type = N'Column', @level2name = 'wkt'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Well known text',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'geometry_usa_2014',
@level2type = N'Column', @level2name = 'wkt';
-- SQL statement 54: Create load geometry view >>>
CREATE VIEW rif_data.v_geometry_usa_2014
AS
SELECT geolevel_id,areaid,zoomlevel,wkt
FROM rif_data.geometry_usa_2014;
-- SQL statement 55: Load DB dependent geometry table from CSV file >>>
BULK INSERT rif_data.v_geometry_usa_2014
FROM 'C:\Users\phamb\Documents\Local Data Loading\Tile maker USA/mssql_geometry_usa_2014.csv' -- Note use of pwd; set via -v pwd="%cd%" in the sqlcmd command line
WITH
(
FORMATFILE = 'C:\Users\phamb\Documents\Local Data Loading\Tile maker USA/mssql_geometry_usa_2014.fmt', -- Use a format file
TABLOCK -- Table lock
);
(13160 rows affected)
-- SQL statement 56: Drop load geometry view >>>
DROP VIEW rif_data.v_geometry_usa_2014;
-- SQL statement 57: Add WKT column >>>
/*
* SQL statement name: update_geometry.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: Table name; e.g. geometry_usa_2014
* 2: srid; e.g. 4326
*
* Description: Update geometry column in table
* Note: % becomes % after substitution
*/
UPDATE rif_data.geometry_usa_2014
SET geom = Geometry::STGeomFromText(wkt, 4326);
(13160 rows affected)
--
-- No partitioning on SQL Server as it requires an Enterprise license; which
--
--
-- means you have to do it yourself using the generated scripts as a start.
--
-- SQL statement 60: Add primary key >>>
ALTER TABLE rif_data.geometry_usa_2014 ADD PRIMARY KEY (geolevel_id, areaid, zoomlevel);
-- SQL statement 61: Create spatial index on geom >>>
/*
* SQL statement name: create_spatial_geometry_index.sql
* Type: MS SQL Server SQL statement
* Parameters:
* 1: index name;e.g. geometry_cb_2014_us_500k_gix
* 2: table name; e.g. geometry_cb_2014_us_500k
* 3: Geometry field name; e.g. geom
* 4: Xmin (4326); e.g. -179.13729006727
* 5: Ymin (4326); e.g. -14.3737802873213
* 6: Xmax (4326); e.g. 179.773803959804
* 7: Ymax (4326); e.g. 71.352561
*
* Description: Create geometry table
* Note: % becomes % after substitution
*/
CREATE SPATIAL INDEX geometry_usa_2014_gix ON rif_data.geometry_usa_2014 (geom)
WITH ( BOUNDING_BOX = (xmin=-179.148909, ymin=-14.548699000000001, xmax=179.77847, ymax=71.36516200000001));
-- SQL statement 62: Create spatial index on bbox >>>
/*
* SQL statement name: create_spatial_geometry_index.sql
* Type: MS SQL Server SQL statement
* Parameters:
* 1: index name;e.g. geometry_cb_2014_us_500k_gix
* 2: table name; e.g. geometry_cb_2014_us_500k
* 3: Geometry field name; e.g. geom
* 4: Xmin (4326); e.g. -179.13729006727
* 5: Ymin (4326); e.g. -14.3737802873213
* 6: Xmax (4326); e.g. 179.773803959804
* 7: Ymax (4326); e.g. 71.352561
*
* Description: Create geometry table
* Note: % becomes % after substitution
*/
CREATE SPATIAL INDEX geometry_usa_2014_gix2 ON rif_data.geometry_usa_2014 (bbox)
WITH ( BOUNDING_BOX = (xmin=-179.148909, ymin=-14.548699000000001, xmax=179.77847, ymax=71.36516200000001));
-- SQL statement 63: Analyze table >>>
UPDATE STATISTICS rif_data.geometry_usa_2014;
--
-- Adjacency table
--
-- SQL statement 65: Drop table adjacency_usa_2014 >>>
IF OBJECT_ID('rif_data.adjacency_usa_2014', 'U') IS NOT NULL DROP TABLE rif_data.adjacency_usa_2014;
-- SQL statement 66: Create table adjacency_usa_2014 >>>
/*
* SQL statement name: create_adjacency_table.sql
* Type: Common SQL statement
* Parameters:
* 1: adjacency table; e.g. adjacency_cb_2014_us_500k
* 2: schema; e.g.rif_data. or ""
*
* Description: Create adjacency table
* Note: % becomes % after substitution
*/
CREATE TABLE rif_data.adjacency_usa_2014 (
geolevel_id INTEGER NOT NULL,
areaid VARCHAR(200) NOT NULL,
num_adjacencies INTEGER NOT NULL,
adjacency_list VARCHAR(8000) NOT NULL,
CONSTRAINT adjacency_usa_2014_pk PRIMARY KEY (geolevel_id, areaid)
);
-- SQL statement 67: Comment table: adjacency_usa_2014 >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_table.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. cb_2014_us_county_500k
* 2: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
*
* Description: Comment table
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.adjacency_usa_2014'
ELSE
SELECT @tableName = 'rif_data.adjacency_usa_2014';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = 0)
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Adjacency lookup table for US 2014 Census geography to county level',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'adjacency_usa_2014'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Adjacency lookup table for US 2014 Census geography to county level',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'adjacency_usa_2014';
-- SQL statement 68: Comment column: adjacency_usa_2014.geolevel_id >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.adjacency_usa_2014'
ELSE
SELECT @tableName = 'rif_data.adjacency_usa_2014';
SELECT @columnName = 'geolevel_id';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'ID for ordering (1=lowest resolution). Up to 99 supported.',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'adjacency_usa_2014',
@level2type = N'Column', @level2name = 'geolevel_id'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'ID for ordering (1=lowest resolution). Up to 99 supported.',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'adjacency_usa_2014',
@level2type = N'Column', @level2name = 'geolevel_id';
-- SQL statement 69: Comment column: adjacency_usa_2014.areaid >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.adjacency_usa_2014'
ELSE
SELECT @tableName = 'rif_data.adjacency_usa_2014';
SELECT @columnName = 'areaid';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Area Id',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'adjacency_usa_2014',
@level2type = N'Column', @level2name = 'areaid'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Area Id',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'adjacency_usa_2014',
@level2type = N'Column', @level2name = 'areaid';
-- SQL statement 70: Comment column: adjacency_usa_2014.num_adjacencies >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.adjacency_usa_2014'
ELSE
SELECT @tableName = 'rif_data.adjacency_usa_2014';
SELECT @columnName = 'num_adjacencies';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Number of adjacencies',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'adjacency_usa_2014',
@level2type = N'Column', @level2name = 'num_adjacencies'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Number of adjacencies',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'adjacency_usa_2014',
@level2type = N'Column', @level2name = 'num_adjacencies';
-- SQL statement 71: Comment column: adjacency_usa_2014.adjacency_list >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.adjacency_usa_2014'
ELSE
SELECT @tableName = 'rif_data.adjacency_usa_2014';
SELECT @columnName = 'adjacency_list';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Adjacent area Ids',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'adjacency_usa_2014',
@level2type = N'Column', @level2name = 'adjacency_list'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Adjacent area Ids',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'adjacency_usa_2014',
@level2type = N'Column', @level2name = 'adjacency_list';
-- SQL statement 72: Drop function usa_2014_GetAdjacencyMatrix() >>>
/*
* SQL statement name: drop_GetAdjacencyMatrix.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: geography; e.g. cb_2014_us_500k
*
* Description: Drop <geography>_GetAdjacencyMatrix() function
* Note: % becomes % after substitution
*/
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[rif40].[usa_2014_GetAdjacencyMatrix]')
AND type IN ( N'TF' )) /* SQL table-valued-function */
DROP FUNCTION [rif40].[usa_2014_GetAdjacencyMatrix];
-- SQL statement 73: Create function usa_2014_GetAdjacencyMatrix() >>>
/*
* SQL statement name: create_GetAdjacencyMatrix.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: geography; e.g. cb_2014_us_500k
* 2: adjacency table; e.g. adjacency_cb_2014_us_500k
*
* Description: Create <geography>_GetAdjacencyMatrix() function
* Note: % becomes % after substitution
*
* DECLARE @study_id INTEGER=[rif40].[rif40_sequence_current_value] ('rif40.rif40_study_id_seq') -* Get current sequence *-;
* SELECT TOP 10 SUBSTRING(areaid, 1, 20) AS areaid, num_adjacencies, SUBSTRING(adjacency_list, 1, 90) AS adjacency_list_truncated
* FROM [rif40].[sahsuland_GetAdjacencyMatrix](@study_id);
* GO
*/
CREATE FUNCTION [rif40].[usa_2014_GetAdjacencyMatrix](@study_id INTEGER)
RETURNS @rtnTable TABLE
(
--
-- Columns returned by the function
--
geolevel_id INTEGER NOT NULL,
areaid VARCHAR(200) NOT NULL,
num_adjacencies INTEGER NOT NULL,
adjacency_list VARCHAR(8000) NOT NULL
)
AS
BEGIN
--
WITH b AS ( /* Tilemaker: has adjacency table */
SELECT b1.area_id, b3.geolevel_id
FROM [rif40].[rif40_study_areas] b1, [rif40].[rif40_studies] b2, [rif40].[rif40_geolevels] b3
WHERE b1.study_id = @study_id
AND b1.study_id = b2.study_id
AND b2.geography = b3.geography
)
INSERT INTO @rtnTable(geolevel_id, areaid, num_adjacencies, adjacency_list)
SELECT c1.geolevel_id, c1.areaid, c1.num_adjacencies, c1.adjacency_list
FROM [rif_data].[adjacency_usa_2014] c1, b
WHERE c1.geolevel_id = b.geolevel_id
AND c1.areaid = b.area_id;
--
RETURN;
END;
-- SQL statement 74: Grant function usa_2014_GetAdjacencyMatrix() >>>
/*
* SQL statement name: grant_function.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: function; e.g. adjacency_GetAdjacencyMatrix
*
* Description: Create <geography>_GetAdjacencyMatrix() function
* Note: % becomes % after substitution
*/
GRANT SELECT, REFERENCES ON [rif40].[usa_2014_GetAdjacencyMatrix] TO rif_user, rif_manager;
--
-- Load adjacency table
--
-- SQL statement 76: Load DB dependent adjacency table from CSV file >>>
BULK INSERT rif_data.adjacency_usa_2014
FROM 'C:\Users\phamb\Documents\Local Data Loading\Tile maker USA/mssql_adjacency_usa_2014.csv' -- Note use of pwd; set via -v pwd="%cd%" in the sqlcmd command line
WITH
(
FORMATFILE = 'C:\Users\phamb\Documents\Local Data Loading\Tile maker USA/mssql_adjacency_usa_2014.fmt', -- Use a format file
TABLOCK -- Table lock
);
(3262 rows affected)
-- SQLCMD statement 77: Set comment schema path to rif_data >>>
-- SQL statement 78: Remove old geolevels meta data table >>>
DELETE FROM t_rif40_geolevels WHERE geography = 'USA_2014';
(3 rows affected)
-- SQL statement 79: Remove old geography meta data table >>>
DELETE FROM rif40_geographies WHERE geography = 'USA_2014';
(1 rows affected)
-- SQL statement 80: Setup geography meta data table column: geometrytable >>>
/*
* SQL statement name: add_column.sql
* Type: Microsoft SQL Server SQL statement
* Parameters:
* 1: Table name; e.g. geometry_usa_2014
* 2: column name; e.g. wkt
* 3: Column datatype; e.g. Text or VARCHAR(MAX)
*
* Description: Add column to table if it does not exist
* Note: % becomes % after substitution
*/
IF COL_LENGTH('rif40_geographies', 'geometrytable') IS NULL
BEGIN
ALTER TABLE rif40_geographies ADD geometrytable VARCHAR(30);
END
;
-- SQL statement 81: Comment geography meta data table columngeometrytable >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = '@CurrentUser';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.rif40_geographies'
ELSE
SELECT @tableName = '@CurrentUser.rif40_geographies';
SELECT @columnName = 'geometrytable';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Geometry table name',
@level0type = N'Schema', @level0name = @CurrentUser,
@level1type = N'Table', @level1name = 'rif40_geographies',
@level2type = N'Column', @level2name = 'geometrytable'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Geometry table name',
@level0type = N'Schema', @level0name = @CurrentUser,
@level1type = N'Table', @level1name = 'rif40_geographies',
@level2type = N'Column', @level2name = 'geometrytable';
-- SQL statement 82: Setup geography meta data table column: tiletable >>>
/*
* SQL statement name: add_column.sql
* Type: Microsoft SQL Server SQL statement
* Parameters:
* 1: Table name; e.g. geometry_usa_2014
* 2: column name; e.g. wkt
* 3: Column datatype; e.g. Text or VARCHAR(MAX)
*
* Description: Add column to table if it does not exist
* Note: % becomes % after substitution
*/
IF COL_LENGTH('rif40_geographies', 'tiletable') IS NULL
BEGIN
ALTER TABLE rif40_geographies ADD tiletable VARCHAR(30);
END
;
-- SQL statement 83: Comment geography meta data table columntiletable >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = '@CurrentUser';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.rif40_geographies'
ELSE
SELECT @tableName = '@CurrentUser.rif40_geographies';
SELECT @columnName = 'tiletable';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Tile table name',
@level0type = N'Schema', @level0name = @CurrentUser,
@level1type = N'Table', @level1name = 'rif40_geographies',
@level2type = N'Column', @level2name = 'tiletable'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Tile table name',
@level0type = N'Schema', @level0name = @CurrentUser,
@level1type = N'Table', @level1name = 'rif40_geographies',
@level2type = N'Column', @level2name = 'tiletable';
-- SQL statement 84: Setup geography meta data table column: minzoomlevel >>>
/*
* SQL statement name: add_column.sql
* Type: Microsoft SQL Server SQL statement
* Parameters:
* 1: Table name; e.g. geometry_usa_2014
* 2: column name; e.g. wkt
* 3: Column datatype; e.g. Text or VARCHAR(MAX)
*
* Description: Add column to table if it does not exist
* Note: % becomes % after substitution
*/
IF COL_LENGTH('rif40_geographies', 'minzoomlevel') IS NULL
BEGIN
ALTER TABLE rif40_geographies ADD minzoomlevel INTEGER;
END
;
-- SQL statement 85: Comment geography meta data table columnminzoomlevel >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = '@CurrentUser';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.rif40_geographies'
ELSE
SELECT @tableName = '@CurrentUser.rif40_geographies';
SELECT @columnName = 'minzoomlevel';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Minimum zoomlevel',
@level0type = N'Schema', @level0name = @CurrentUser,
@level1type = N'Table', @level1name = 'rif40_geographies',
@level2type = N'Column', @level2name = 'minzoomlevel'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Minimum zoomlevel',
@level0type = N'Schema', @level0name = @CurrentUser,
@level1type = N'Table', @level1name = 'rif40_geographies',
@level2type = N'Column', @level2name = 'minzoomlevel';
-- SQL statement 86: Setup geography meta data table column: maxzoomlevel >>>
/*
* SQL statement name: add_column.sql
* Type: Microsoft SQL Server SQL statement
* Parameters:
* 1: Table name; e.g. geometry_usa_2014
* 2: column name; e.g. wkt
* 3: Column datatype; e.g. Text or VARCHAR(MAX)
*
* Description: Add column to table if it does not exist
* Note: % becomes % after substitution
*/
IF COL_LENGTH('rif40_geographies', 'maxzoomlevel') IS NULL
BEGIN
ALTER TABLE rif40_geographies ADD maxzoomlevel INTEGER;
END
;
-- SQL statement 87: Comment geography meta data table columnmaxzoomlevel >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = '@CurrentUser';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.rif40_geographies'
ELSE
SELECT @tableName = '@CurrentUser.rif40_geographies';
SELECT @columnName = 'maxzoomlevel';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Maximum zoomlevel',
@level0type = N'Schema', @level0name = @CurrentUser,
@level1type = N'Table', @level1name = 'rif40_geographies',
@level2type = N'Column', @level2name = 'maxzoomlevel'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Maximum zoomlevel',
@level0type = N'Schema', @level0name = @CurrentUser,
@level1type = N'Table', @level1name = 'rif40_geographies',
@level2type = N'Column', @level2name = 'maxzoomlevel';
-- SQL statement 88: Setup geography meta data table column: adjacencytable >>>
/*
* SQL statement name: add_column.sql
* Type: Microsoft SQL Server SQL statement
* Parameters:
* 1: Table name; e.g. geometry_usa_2014
* 2: column name; e.g. wkt
* 3: Column datatype; e.g. Text or VARCHAR(MAX)
*
* Description: Add column to table if it does not exist
* Note: % becomes % after substitution
*/
IF COL_LENGTH('rif40_geographies', 'adjacencytable') IS NULL
BEGIN
ALTER TABLE rif40_geographies ADD adjacencytable VARCHAR(30);
END
;
-- SQL statement 89: Comment geography meta data table columnadjacencytable >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = '@CurrentUser';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.rif40_geographies'
ELSE
SELECT @tableName = '@CurrentUser.rif40_geographies';
SELECT @columnName = 'adjacencytable';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Adjacency table',
@level0type = N'Schema', @level0name = @CurrentUser,
@level1type = N'Table', @level1name = 'rif40_geographies',
@level2type = N'Column', @level2name = 'adjacencytable'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Adjacency table',
@level0type = N'Schema', @level0name = @CurrentUser,
@level1type = N'Table', @level1name = 'rif40_geographies',
@level2type = N'Column', @level2name = 'adjacencytable';
-- SQL statement 90: Setup geolevels meta data table column: areaid_count >>>
/*
* SQL statement name: add_column.sql
* Type: Microsoft SQL Server SQL statement
* Parameters:
* 1: Table name; e.g. geometry_usa_2014
* 2: column name; e.g. wkt
* 3: Column datatype; e.g. Text or VARCHAR(MAX)
*
* Description: Add column to table if it does not exist
* Note: % becomes % after substitution
*/
IF COL_LENGTH('t_rif40_geolevels', 'areaid_count') IS NULL
BEGIN
ALTER TABLE t_rif40_geolevels ADD areaid_count INTEGER;
END
;
-- SQL statement 91: Comment geolevels meta data table columnareaid_count >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = '@CurrentUser';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.t_rif40_geolevels'
ELSE
SELECT @tableName = '@CurrentUser.t_rif40_geolevels';
SELECT @columnName = 'areaid_count';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Area ID count',
@level0type = N'Schema', @level0name = @CurrentUser,
@level1type = N'Table', @level1name = 't_rif40_geolevels',
@level2type = N'Column', @level2name = 'areaid_count'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Area ID count',
@level0type = N'Schema', @level0name = @CurrentUser,
@level1type = N'Table', @level1name = 't_rif40_geolevels',
@level2type = N'Column', @level2name = 'areaid_count';
-- SQL statement 92: Recreate rif40_geolevels view with new columns >>>
/*
* SQL statement name: rif40_geolevels_view.sql
* Type: MS SQL Server SQL
* Parameters: NONE
*
* Description: Recreate rif40_geolevels
* Note: % becomes % after substitution
*/
DROP VIEW [rif40].[rif40_geolevels]
CREATE VIEW [rif40].[rif40_geolevels] AS
SELECT a.geography,
a.geolevel_name,
a.geolevel_id,
a.description,
a.lookup_table,
a.lookup_desc_column,
a.shapefile,
a.centroidsfile,
a.shapefile_table,
a.shapefile_area_id_column,
a.shapefile_desc_column,
a.centroids_table,
a.centroids_area_id_column,
a.avg_npoints_geom,
a.avg_npoints_opt,
a.file_geojson_len,
a.leg_geom,
a.leg_opt,
a.covariate_table,
a.resolution,
a.comparea,
a.listing,
a.restricted,
a.centroidxcoordinate_column,
a.centroidycoordinate_column,
a.areaid_count
FROM [rif40].[t_rif40_geolevels] a
WHERE IS_MEMBER(N'[rif_student]') = 1 AND a.restricted <> 1
UNION
SELECT a.geography,
a.geolevel_name,
a.geolevel_id,
a.description,
a.lookup_table,
a.lookup_desc_column,
a.shapefile,
a.centroidsfile,
a.shapefile_table,
a.shapefile_area_id_column,
a.shapefile_desc_column,
a.centroids_table,
a.centroids_area_id_column,
a.avg_npoints_geom,
a.avg_npoints_opt,
a.file_geojson_len,
a.leg_geom,
a.leg_opt,
a.covariate_table,
a.resolution,
a.comparea,
a.listing,
a.restricted,
a.centroidxcoordinate_column,
a.centroidycoordinate_column,
a.areaid_count
FROM [rif40].[t_rif40_geolevels] a
WHERE IS_MEMBER(N'[rif_student]') IS NULL
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Geography (e.g EW2001)' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'geography'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Name of geolevel. This will be a column name in the numerator/denominator tables' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'geolevel_name'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'ID for ordering (1=lowest resolution). Up to 99 supported.' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'geolevel_id'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Description' ,
@level0type=N'SCHEMA',@level0name=N'rif40',
@level1type=N'VIEW',@level1name=N'rif40_geolevels', @level2type=N'COLUMN',@level2name=N'description'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Lookup table name. This is used to translate codes to the common names, e.g a LADUA of 00BK is "Westminster"' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'lookup_table'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Lookup table description column name.' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'lookup_desc_column'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Location of the GIS shape file. NULL if PostGress/PostGIS used. Can also use SHAPEFILE_GEOMETRY instead.' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'shapefile'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Location of the GIS centroids file. Can also use CENTROIDXCOORDINATE_COLUMN, CENTROIDYCOORDINATE_COLUMN instead.' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'centroidsfile'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Table containing GIS shape file data (created using shp2pgsql).' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'shapefile_table'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Column containing the AREA_IDs in SHAPEFILE_TABLE' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'shapefile_area_id_column'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Column containing the AREA_ID descriptions in SHAPEFILE_TABLE' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'shapefile_desc_column'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Table containing GIS shape file data with Arc GIS calculated population weighted centroids (created using shp2pgsql). PostGIS does not support population weighted centroids.' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'centroids_table'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Column containing the AREA_IDs in CENTROIDS_TABLE. X and Y co-ordinates ciolumns are asummed to be named after CENTROIDXCOORDINATE_COLUMN and CENTROIDYCOORDINATE_COLUMN.' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'centroids_area_id_column'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Average number of points in a geometry object (AREA_ID). Used to evaluation the impact of ST_SIMPLIFY_TOLERANCE.' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'avg_npoints_geom'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Average number of points in a ST_SimplifyPreserveTopology() optimsed geometry object (AREA_ID). Used to evaluation the impact of ST_SIMPLIFY_TOLERANCE.' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'avg_npoints_opt'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'File length estimate (in bytes) for conversion of the entire geolevel geometry to GeoJSON. Used to evaluation the impact of ST_SIMPLIFY_TOLERANCE.' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'file_geojson_len'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'The average length (in projection units - usually metres) of a vector leg. Used to evaluation the impact of ST_SIMPLIFY_TOLERANCE.' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'leg_geom'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'The average length (in projection units - usually metres) of a ST_SimplifyPreserveTopology() optimsed geometryvector leg. Used to evaluation the impact of ST_SIMPLIFY_TOLERANCE.' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'leg_opt'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Name of table used for covariates at this geolevel' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'covariate_table'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Can use a map for selection at this resolution (0/1)' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'resolution'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Able to be used as a comparison area (0/1)' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'comparea'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Able to be used in a disease map listing (0/1)' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'listing'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Is geolevel access rectricted by Inforamtion Governance restrictions (0/1). If 1 (Yes) then a) students cannot access this geolevel and b) if the system parameter ExtractControl=1 then the user must be granted permission by a RIF_MANAGER to extract from the database the results, data extract and maps tables. This is enforced by the RIF application.' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'restricted'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Lookup table centroid X co-ordinate column name. Can also use CENTROIDSFILE instead.' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'centroidxcoordinate_column'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Lookup table centroid Y co-ordinate column name.' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'centroidycoordinate_column'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Geolevels: hierarchy of level with a geography. Use this table for INSERT/UPDATE/DELETE; use RIF40_GEOLEVELS for SELECT. In RIF40_GEOLEVELS if the user has the RIF_STUDENT role the geolevels are restricted to LADUA/DISTRICT level resolution or lower.' ,
@level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels'
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Area ID count' , @level0type=N'SCHEMA',@level0name=N'rif40', @level1type=N'VIEW',@level1name=N'rif40_geolevels',
@level2type=N'COLUMN',@level2name=N'areaid_count'
GRANT SELECT ON rif40_geolevels TO rif_user, rif_manager;;
-- SQLCMD statement 93: Set comment schema path to rif_data >>>
-- SQL statement 94: Populate geography meta data table >>>
/*
* SQL statement name: insert_geography.sql
* Type: Common SQL statement
* Parameters:
* 1: table; e.g. GEOGRAPHY_CB_2014_US_COUNTY_500K
* 2: geography; e.g. CB_2014_US_500K
* 3: geography description; e.g. United states to county level
* 4: hierarchytable; e.g. HIERARCHY_CB_2014_US_500K
* 5: geometrytable; e.g. GEOMETRY_CB_2014_US_500K
* 6: tiletable; e.g. TILES_CB_2014_US_500K
* 7: SRID; e.g. 4269
* 8: Default comparision area, e.g. GEOID
* 9: Default study area, e.g. STATENS
* 10: Min zoomlevel
* 11: Max zoomlevel
* 12: Postal population table (quote enclosed or NULL)
* 13: Postal point column (quote enclosed or NULL)
* 14: Partition (0/1)
* 15: Max geojson digits
* 16: adjacencytable; e.g. ADJACENCY_CB_2014_US_500K
*
* Description: Insert into geography table
* Note: %% becomes % after substitution
*/
INSERT INTO rif40_geographies (
geography, description, hierarchytable, geometrytable, tiletable, adjacencytable, srid, defaultcomparea, defaultstudyarea, minzoomlevel, maxzoomlevel,
postal_population_table, postal_point_column, partition, max_geojson_digits)
SELECT 'USA_2014' AS geography,
'US 2014 Census geography to county level' AS description,
'HIERARCHY_USA_2014' AS hierarchytable,
'GEOMETRY_USA_2014' AS geometrytable,
'TILES_USA_2014' AS tiletable,
'ADJACENCY_USA_2014' AS adjacencytable,
4269 AS srid,
NULL AS defaultcomparea, /* See: update_geography.sql */
NULL AS defaultstudyarea,
6 AS minzoomlevel,
9 AS maxzoomlevel,
NULL AS postal_population_table,
NULL AS postal_point_column,
0 AS partition,
6 AS max_geojson_digits;
(1 rows affected)
-- SQL statement 95: Insert geolevels meta data for: cb_2014_us_nation_5m >>>
/*
* SQL statement name: insert_geolevel.sql
* Type: Common SQL statement
* Parameters:
* 1: table; e.g. GEOLEVELS_CB_2014_US_COUNTY_500K
* 2: geography; e.g. CB_2014_US_500K
* 3: Geolevel name; e.g. CB_2014_US_COUNTY_500K
* 4: Geolevel id; e.g. 3
* 5: Geolevel description; e.g. "The State-County at a scale of 1:500,000"
* 6: lookup table; e.g. LOOKUP_CB_2014_US_COUNTY_500K
* 7: shapefile; e.g. cb_2014_us_county_500k.shp
* 8: shapefile table; e.g. CB_2014_US_COUNTY_500K
* 9: covariate_table; e.g. CB_2014_US_500K_COVARIATES_CB_2014_US_COUNTY_500K
* 10: shapefile_area_id_column; e.g. COUNTYNS
* 11: shapefile_desc_column; e.g. NAME
* 12: lookup_desc_column; e.g. AREANAME
* 13: resolution: Can use a map for selection at this resolution (0/1)
* 14: comparea: Able to be used as a comparison area (0/1)
* 15: listing: Able to be used in a disease map listing (0/1)
*
* Description: Insert into geography table
* Note: %% becomes % after substitution
*/
INSERT INTO t_rif40_geolevels (
geography, geolevel_name, geolevel_id, description, lookup_table,
lookup_desc_column, shapefile, shapefile_table, shapefile_area_id_column, shapefile_desc_column,
resolution, comparea, listing, covariate_table)
SELECT 'USA_2014' AS geography,
'CB_2014_US_NATION_5M' AS geolevel_name,
1 AS geolevel_id,
'The nation at a scale of 1:5,000,000' AS description,
'LOOKUP_CB_2014_US_NATION_5M' AS lookup_table,
'AREANAME' AS lookup_desc_column,
'cb_2014_us_nation_5m.shp' AS shapefile,
'CB_2014_US_NATION_5M' AS shapefile_table,
'GEOID' AS shapefile_area_id_column,
'NAME' AS shapefile_desc_column,
1 AS resolution,
1 AS comparea,
1 AS listing,
NULL AS covariate_table;
(1 rows affected)
-- SQL statement 96: Create (if required) geolevels covariate table for: cb_2014_us_state_500k >>>
/*
* SQL statement name: create_covariate_table.sql
* Type: Microsoft SQL Server SQL statement
* Parameters:
* 1: covariate_table; e.g. COV_CB_2014_US_STATE_500K
* 2: Geolevel name: CB_2014_US_STATE_500K
* 3: Schema; e.g. rif_data. or ""
*
* Description: Create example covariate table if it does not exist
* Note: %% becomes % after substitution
*/
IF COL_LENGTH('rif_data.cov_cb_2014_us_state_500k', 'cb_2014_us_state_500k') IS NULL
BEGIN
CREATE TABLE rif_data.cov_cb_2014_us_state_500k (
year INTEGER NOT NULL,
cb_2014_us_state_500k VARCHAR(30) NOT NULL,
PRIMARY KEY (year, cb_2014_us_state_500k)
);
END
;
-- SQL statement 97: Comment covariate table >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_table.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. cb_2014_us_county_500k
* 2: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
*
* Description: Comment table
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.cov_cb_2014_us_state_500k'
ELSE
SELECT @tableName = 'rif_data.cov_cb_2014_us_state_500k';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = 0)
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Example covariate table for: The State at a scale of 1:500,000',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'cov_cb_2014_us_state_500k'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Example covariate table for: The State at a scale of 1:500,000',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'cov_cb_2014_us_state_500k';
-- SQL statement 98: Comment covariate year column >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.cov_cb_2014_us_state_500k'
ELSE
SELECT @tableName = 'rif_data.cov_cb_2014_us_state_500k';
SELECT @columnName = 'year';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Year',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'cov_cb_2014_us_state_500k',
@level2type = N'Column', @level2name = 'year'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Year',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'cov_cb_2014_us_state_500k',
@level2type = N'Column', @level2name = 'year';
-- SQL statement 99: Comment covariate year column >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.cov_cb_2014_us_state_500k'
ELSE
SELECT @tableName = 'rif_data.cov_cb_2014_us_state_500k';
SELECT @columnName = 'cb_2014_us_state_500k';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Geolevel name',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'cov_cb_2014_us_state_500k',
@level2type = N'Column', @level2name = 'cb_2014_us_state_500k'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Geolevel name',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'cov_cb_2014_us_state_500k',
@level2type = N'Column', @level2name = 'cb_2014_us_state_500k';
-- SQL statement 100: Insert geolevels meta data for: cb_2014_us_state_500k >>>
/*
* SQL statement name: insert_geolevel.sql
* Type: Common SQL statement
* Parameters:
* 1: table; e.g. GEOLEVELS_CB_2014_US_COUNTY_500K
* 2: geography; e.g. CB_2014_US_500K
* 3: Geolevel name; e.g. CB_2014_US_COUNTY_500K
* 4: Geolevel id; e.g. 3
* 5: Geolevel description; e.g. "The State-County at a scale of 1:500,000"
* 6: lookup table; e.g. LOOKUP_CB_2014_US_COUNTY_500K
* 7: shapefile; e.g. cb_2014_us_county_500k.shp
* 8: shapefile table; e.g. CB_2014_US_COUNTY_500K
* 9: covariate_table; e.g. CB_2014_US_500K_COVARIATES_CB_2014_US_COUNTY_500K
* 10: shapefile_area_id_column; e.g. COUNTYNS
* 11: shapefile_desc_column; e.g. NAME
* 12: lookup_desc_column; e.g. AREANAME
* 13: resolution: Can use a map for selection at this resolution (0/1)
* 14: comparea: Able to be used as a comparison area (0/1)
* 15: listing: Able to be used in a disease map listing (0/1)
*
* Description: Insert into geography table
* Note: %% becomes % after substitution
*/
INSERT INTO t_rif40_geolevels (
geography, geolevel_name, geolevel_id, description, lookup_table,
lookup_desc_column, shapefile, shapefile_table, shapefile_area_id_column, shapefile_desc_column,
resolution, comparea, listing, covariate_table)
SELECT 'USA_2014' AS geography,
'CB_2014_US_STATE_500K' AS geolevel_name,
2 AS geolevel_id,
'The State at a scale of 1:500,000' AS description,
'LOOKUP_CB_2014_US_STATE_500K' AS lookup_table,
'AREANAME' AS lookup_desc_column,
'cb_2014_us_state_500k.shp' AS shapefile,
'CB_2014_US_STATE_500K' AS shapefile_table,
'STATENS' AS shapefile_area_id_column,
'NAME' AS shapefile_desc_column,
1 AS resolution,
1 AS comparea,
1 AS listing,
'COV_CB_2014_US_STATE_500K' AS covariate_table;
(1 rows affected)
-- SQL statement 101: Create (if required) geolevels covariate table for: cb_2014_us_county_500k >>>
/*
* SQL statement name: create_covariate_table.sql
* Type: Microsoft SQL Server SQL statement
* Parameters:
* 1: covariate_table; e.g. COV_CB_2014_US_STATE_500K
* 2: Geolevel name: CB_2014_US_STATE_500K
* 3: Schema; e.g. rif_data. or ""
*
* Description: Create example covariate table if it does not exist
* Note: %% becomes % after substitution
*/
IF COL_LENGTH('rif_data.cov_cb_2014_us_county_500k', 'cb_2014_us_county_500k') IS NULL
BEGIN
CREATE TABLE rif_data.cov_cb_2014_us_county_500k (
year INTEGER NOT NULL,
cb_2014_us_county_500k VARCHAR(30) NOT NULL,
PRIMARY KEY (year, cb_2014_us_county_500k)
);
END
;
-- SQL statement 102: Comment covariate table >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_table.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. cb_2014_us_county_500k
* 2: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
*
* Description: Comment table
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.cov_cb_2014_us_county_500k'
ELSE
SELECT @tableName = 'rif_data.cov_cb_2014_us_county_500k';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = 0)
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Example covariate table for: The County at a scale of 1:500,000',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'cov_cb_2014_us_county_500k'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Example covariate table for: The County at a scale of 1:500,000',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'cov_cb_2014_us_county_500k';
-- SQL statement 103: Comment covariate year column >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.cov_cb_2014_us_county_500k'
ELSE
SELECT @tableName = 'rif_data.cov_cb_2014_us_county_500k';
SELECT @columnName = 'year';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Year',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'cov_cb_2014_us_county_500k',
@level2type = N'Column', @level2name = 'year'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Year',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'cov_cb_2014_us_county_500k',
@level2type = N'Column', @level2name = 'year';
-- SQL statement 104: Comment covariate year column >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.cov_cb_2014_us_county_500k'
ELSE
SELECT @tableName = 'rif_data.cov_cb_2014_us_county_500k';
SELECT @columnName = 'cb_2014_us_county_500k';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Geolevel name',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'cov_cb_2014_us_county_500k',
@level2type = N'Column', @level2name = 'cb_2014_us_county_500k'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Geolevel name',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 'cov_cb_2014_us_county_500k',
@level2type = N'Column', @level2name = 'cb_2014_us_county_500k';
-- SQL statement 105: Insert geolevels meta data for: cb_2014_us_county_500k >>>
/*
* SQL statement name: insert_geolevel.sql
* Type: Common SQL statement
* Parameters:
* 1: table; e.g. GEOLEVELS_CB_2014_US_COUNTY_500K
* 2: geography; e.g. CB_2014_US_500K
* 3: Geolevel name; e.g. CB_2014_US_COUNTY_500K
* 4: Geolevel id; e.g. 3
* 5: Geolevel description; e.g. "The State-County at a scale of 1:500,000"
* 6: lookup table; e.g. LOOKUP_CB_2014_US_COUNTY_500K
* 7: shapefile; e.g. cb_2014_us_county_500k.shp
* 8: shapefile table; e.g. CB_2014_US_COUNTY_500K
* 9: covariate_table; e.g. CB_2014_US_500K_COVARIATES_CB_2014_US_COUNTY_500K
* 10: shapefile_area_id_column; e.g. COUNTYNS
* 11: shapefile_desc_column; e.g. NAME
* 12: lookup_desc_column; e.g. AREANAME
* 13: resolution: Can use a map for selection at this resolution (0/1)
* 14: comparea: Able to be used as a comparison area (0/1)
* 15: listing: Able to be used in a disease map listing (0/1)
*
* Description: Insert into geography table
* Note: %% becomes % after substitution
*/
INSERT INTO t_rif40_geolevels (
geography, geolevel_name, geolevel_id, description, lookup_table,
lookup_desc_column, shapefile, shapefile_table, shapefile_area_id_column, shapefile_desc_column,
resolution, comparea, listing, covariate_table)
SELECT 'USA_2014' AS geography,
'CB_2014_US_COUNTY_500K' AS geolevel_name,
3 AS geolevel_id,
'The County at a scale of 1:500,000' AS description,
'LOOKUP_CB_2014_US_COUNTY_500K' AS lookup_table,
'AREANAME' AS lookup_desc_column,
'cb_2014_us_county_500k.shp' AS shapefile,
'CB_2014_US_COUNTY_500K' AS shapefile_table,
'COUNTYNS' AS shapefile_area_id_column,
'NAME' AS shapefile_desc_column,
1 AS resolution,
1 AS comparea,
1 AS listing,
'COV_CB_2014_US_COUNTY_500K' AS covariate_table;
(1 rows affected)
-- SQL statement 106: Populate geography meta data table >>>
/*
* SQL statement name: update_geography.sql
* Type: Common SQL statement
* Parameters:
* 1: table; e.g. GEOGRAPHY_CB_2014_US_COUNTY_500K
* 2: geography; e.g. CB_2014_US_500K
* 3: Default comparision area, e.g. GEOID
* 4: Default study area, e.g. STATENS
*
* Description: Insert into geography table
* Note: %% becomes % after substitution
*/
UPDATE rif40_geographies
SET defaultcomparea = 'CB_2014_US_NATION_5M',
defaultstudyarea = 'CB_2014_US_STATE_500K'
WHERE geography = 'USA_2014';
(1 rows affected)
-- SQL statement 107: Update areaid_count column in geolevels table using geometry table >>>
/*
* SQL statement name: geolevels_areaid_update.sql
* Type: MS SQL Server SQL statement
* Parameters:
* 1: Geolevels table; e.g. geolevels_cb_2014_us_500k
* 2: Geometry table geometry_cb_2014_us_500k
* 3: Schema; e.g. rif_data. or ""
*
* Description: Update areaid_count column in geolevels table using geometry table
* Note: % becomes % after substitution
*/
WITH b AS (
SELECT geolevel_id, COUNT(DISTINCT(areaid)) AS areaid_count
FROM rif_data.geometry_usa_2014
GROUP BY geolevel_id
)
UPDATE a
SET areaid_count = b.areaid_count
FROM t_rif40_geolevels a
JOIN b ON a.geolevel_id = b.geolevel_id
WHERE geography = 'USA_2014';
(3 rows affected)
-- SQL statement 108: Drop dependent object - view tiles_usa_2014 >>>
IF OBJECT_ID('rif_data.tiles_usa_2014', 'V') IS NOT NULL DROP VIEW rif_data.tiles_usa_2014;
--
-- Drop and recreate dependent objects required by tiles view: generate_series() [MS SQL Server only]
--
-- SQL statement 110: Drop generate_series() function >>>
/*
* SQL statement name: drop_generate_series.sql
* Type: MS SQL Server SQL statement
* Parameters: None
* Description: Drop generate_series() function (TF = SQL table-valued-function)
*/
IF OBJECT_ID (N'generate_series', N'TF') IS NOT NULL
DROP FUNCTION generate_series; ;
-- SQL statement 111: Create generate_series() function >>>
/*
* SQL statement name: generate_series.sql
* Type: MS SQL Server SQL statement
* Parameters: None
* Description: Generate a series of values, from start to stop with a step size of step
* Original by: Simon Greener, Independent GeoSpatial Solutions Architect
* http://www.spatialdbadvisor.com/sql_server_blog/86/generate_series-for-sql-server-2008
*/
CREATE FUNCTION generate_series ( @p_start INT, @p_end INT, @p_step INT=1 )
RETURNS @Integers TABLE ( [IntValue] INT )
AS
BEGIN
DECLARE
@v_i INT,
@v_step INT,
@v_terminating_value INT;
BEGIN
SET @v_i = CASE WHEN @p_start IS NULL THEN 1 ELSE @p_start END;
SET @v_step = CASE WHEN @p_step IS NULL OR @p_step = 0 THEN 1 ELSE @p_step END;
SET @v_terminating_value = @p_start + CONVERT(INT,ABS(@p_start-@p_end) / ABS(@v_step) ) * @v_step;
-- Check for impossible combinations
IF NOT ( ( @p_start > @p_end AND SIGN(@p_step) = 1 )
OR
( @p_start < @p_end AND SIGN(@p_step) = -1 ))
BEGIN
-- Generate values
WHILE ( 1 = 1 )
BEGIN
INSERT INTO @Integers ( [IntValue] ) VALUES ( @v_i )
IF ( @v_i = @v_terminating_value )
BREAK
SET @v_i = @v_i + @v_step;
END;
END;
END;
RETURN
END;
--
-- Create tiles functions
--
-- SQL statement 113: Create function: longitude2tile.sql >>>
/*
* SQL statement name: longitude2tile.sql
* Type: Postgres/PostGIS PL/pgsql function
* Parameters: None
*
* Description: Convert longitude (WGS84 - 4326) to OSM tile x
* Note: % becomes % after substitution
*/
IF OBJECT_ID (N'tileMaker_longitude2tile', N'FN') IS NOT NULL
DROP FUNCTION tileMaker_longitude2tile;
CREATE FUNCTION tileMaker_longitude2tile(@longitude DOUBLE PRECISION, @zoom_level INTEGER)
RETURNS INTEGER AS
BEGIN
DECLARE @tileX INTEGER;
SET @tileX=CAST(
FLOOR( (@longitude + 180) / 360 * POWER(2, @zoom_level) ) AS INTEGER);
RETURN @tileX;
END;
DECLARE @CurrentUser sysname;
SELECT @CurrentUser = user_name();
EXECUTE sp_addextendedproperty 'MS_Description', 'Function: tileMaker_longitude2tile()
Parameters: Longitude, zoom level
Returns: OSM Tile x
Description: Convert longitude (WGS84 - 4326) to OSM tile x
Derivation of the tile X/Y
* Reproject the coordinates to the Mercator projection (from EPSG:4326 to EPSG:3857):
x = lon
y = arsinh(tan(lat)) = log[tan(lat) + sec(lat)]
(lat and lon are in radians)
* Transform range of x and y to 0 � 1 and shift origin to top left corner:
x = [1 + (x / p)] / 2
y = [1 - (y / p)] / 2
* Calculate the number of tiles across the map, n, using 2**zoom
* Multiply x and y by n. Round results down to give tilex and tiley.
',
'user', @CurrentUser,
'function', 'tileMaker_longitude2tile';
-- SQL statement 114: Create function: latitude2tile.sql >>>
/*
* SQL statement name: latitude2tile.sql
* Type: Microsoft SQL Server T/sql function
* Parameters: None
*
* Description: Convert latitude (WGS84 - 4326) to OSM tile y
* Note: % becomes % after substitution
*/
IF OBJECT_ID (N'tileMaker_latitude2tile', N'FN') IS NOT NULL
DROP FUNCTION tileMaker_latitude2tile;
CREATE FUNCTION tileMaker_latitude2tile(@latitude DOUBLE PRECISION, @zoom_level INTEGER)
RETURNS INTEGER
AS
BEGIN
DECLARE @tileY INTEGER;
SET @tileY=CAST(
FLOOR(
(1.0 - LOG /* Natural Log */
(TAN(RADIANS(@latitude)) + 1.0 / COS(RADIANS(@latitude))) / PI()) / 2.0 * POWER(2, @zoom_level)
)
AS INTEGER);
RETURN @tileY;
END;
DECLARE @CurrentUser sysname;
SELECT @CurrentUser = user_name();
EXECUTE sp_addextendedproperty 'MS_Description', 'Function: tileMaker_latitude2tile()
Parameters: Latitude, zoom level
Returns: OSM Tile y
Description: Convert latitude (WGS84 - 4326) to OSM tile x
Derivation of the tile X/Y
* Reproject the coordinates to the Mercator projection (from EPSG:4326 to EPSG:3857):
x = lon
y = arsinh(tan(lat)) = log[tan(lat) + sec(lat)]
(lat and lon are in radians)
* Transform range of x and y to 0 � 1 and shift origin to top left corner:
x = [1 + (x / p)] / 2
y = [1 - (y / p)] / 2
* Calculate the number of tiles across the map, n, using 2**zoom
* Multiply x and y by n. Round results down to give tilex and tiley.
',
'user', @CurrentUser,
'function', 'tileMaker_latitude2tile';
-- SQL statement 115: Create function: tile2longitude.sql >>>
/*
* SQL statement name: tile2longitude.sql
* Type: Postgres/PostGIS PL/pgsql function
* Parameters: None
*
* Description: Convert OSM tile x to longitude (WGS84 - 4326)
* Note: % becomes % after substitution
*/
IF OBJECT_ID (N'tileMaker_tile2longitude', N'FN') IS NOT NULL
DROP FUNCTION tileMaker_tile2longitude;
CREATE FUNCTION tileMaker_tile2longitude(@x INTEGER, @zoom_level INTEGER)
RETURNS DOUBLE PRECISION AS
BEGIN
DECLARE @longitude DOUBLE PRECISION;
SET @longitude=CAST( ( (@x * 1.0) / POWER(2, @zoom_level) * 360.0) - 180.0 AS DOUBLE PRECISION);
RETURN @longitude;
END;
DECLARE @CurrentUser sysname;
SELECT @CurrentUser = user_name();
EXECUTE sp_addextendedproperty 'MS_Description', 'Function: tileMaker_tile2longitude()
Parameters: OSM Tile x, zoom level
Returns: Longitude
Description: Convert OSM tile x to longitude (WGS84 - 4326)
',
'user', @CurrentUser,
'function', 'tileMaker_tile2longitude';
-- SQL statement 116: Create function: tile2latitude.sql >>>
/*
* SQL statement name: tileMaker_tile2latitude.sql
* Type: Postgres/PostGIS PL/pgsql function
* Parameters: None
*
* Description: Convert OSM tile y to latitude (WGS84 - 4326)
* Note: % becomes % after substitution
*/
IF OBJECT_ID (N'tileMaker_tile2latitude', N'FN') IS NOT NULL
DROP FUNCTION tileMaker_tile2latitude;
CREATE FUNCTION tileMaker_tile2latitude(@y INTEGER, @zoom_level INTEGER)
RETURNS DOUBLE PRECISION AS
BEGIN
DECLARE @latitude DOUBLE PRECISION;
DECLARE @n FLOAT;
DECLARE @sinh FLOAT;
DECLARE @E FLOAT = 2.7182818284;
SET @n = PI() - (2.0 * PI() * @y) / POWER(2.0, @zoom_level);
SET @sinh = (1 - POWER(@E, -2*@n)) / (2 * POWER(@E, -@n));
SET @latitude = DEGREES(ATAN(@sinh));
RETURN @latitude;
END;
DECLARE @CurrentUser sysname;
SELECT @CurrentUser = user_name();
EXECUTE sp_addextendedproperty 'MS_Description', 'Function: tileMaker_tile2latitude()
Parameters: OSM Tile y, zoom level
Returns: Latitude
Description: Convert OSM tile y to latitude (WGS84 - 4326)
',
'user', @CurrentUser,
'function', 'tileMaker_tile2latitude';
--
-- Create tiles tables
--
-- SQL statement 118: Drop table t_tiles_usa_2014 >>>
IF OBJECT_ID('rif_data.t_tiles_usa_2014', 'U') IS NOT NULL DROP TABLE rif_data.t_tiles_usa_2014;
-- SQL statement 119: Create tiles table >>>
/*
* SQL statement name: create_tiles_table.sql
* Type: Common SQL statement
* Parameters:
* 1: table; e.g. t_tiles_cb_2014_us_county_500k
* 2: JSON datatype (Postgres JSON, SQL server Text)
* 3: Schema; e.g. rif_data. or ""
*
* Description: Create tiles table
* Note: %% becomes % after substitution
*/
CREATE TABLE rif_data.t_tiles_usa_2014 (
geolevel_id INTEGER NOT NULL,
zoomlevel INTEGER NOT NULL,
x INTEGER NOT NULL,
y INTEGER NOT NULL,
optimised_topojson NVARCHAR(MAX),
tile_id VARCHAR(200) NOT NULL,
areaid_count INTEGER NOT NULL,
PRIMARY KEY (tile_id));
-- SQL statement 120: Comment tiles table >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_table.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. cb_2014_us_county_500k
* 2: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
*
* Description: Comment table
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.t_tiles_usa_2014'
ELSE
SELECT @tableName = 'rif_data.t_tiles_usa_2014';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = 0)
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Maptiles for geography; empty tiles are added to complete zoomlevels for zoomlevels 0 to 11',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 't_tiles_usa_2014'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Maptiles for geography; empty tiles are added to complete zoomlevels for zoomlevels 0 to 11',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 't_tiles_usa_2014';
-- SQL statement 121: Comment tiles table column >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.t_tiles_usa_2014'
ELSE
SELECT @tableName = 'rif_data.t_tiles_usa_2014';
SELECT @columnName = 'geolevel_id';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'ID for ordering (1=lowest resolution). Up to 99 supported.',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 't_tiles_usa_2014',
@level2type = N'Column', @level2name = 'geolevel_id'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'ID for ordering (1=lowest resolution). Up to 99 supported.',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 't_tiles_usa_2014',
@level2type = N'Column', @level2name = 'geolevel_id';
-- SQL statement 122: Comment tiles table column >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.t_tiles_usa_2014'
ELSE
SELECT @tableName = 'rif_data.t_tiles_usa_2014';
SELECT @columnName = 'zoomlevel';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Zoom level: 0 to 11. Number of tiles is 2**<zoom level> * 2**<zoom level>; i.e. 1, 2x2, 4x4 ... 2048x2048 at zoomlevel 11',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 't_tiles_usa_2014',
@level2type = N'Column', @level2name = 'zoomlevel'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Zoom level: 0 to 11. Number of tiles is 2**<zoom level> * 2**<zoom level>; i.e. 1, 2x2, 4x4 ... 2048x2048 at zoomlevel 11',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 't_tiles_usa_2014',
@level2type = N'Column', @level2name = 'zoomlevel';
-- SQL statement 123: Comment tiles table column >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.t_tiles_usa_2014'
ELSE
SELECT @tableName = 'rif_data.t_tiles_usa_2014';
SELECT @columnName = 'x';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'X tile number. From 0 to (2**<zoomlevel>)-1',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 't_tiles_usa_2014',
@level2type = N'Column', @level2name = 'x'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'X tile number. From 0 to (2**<zoomlevel>)-1',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 't_tiles_usa_2014',
@level2type = N'Column', @level2name = 'x';
-- SQL statement 124: Comment tiles table column >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.t_tiles_usa_2014'
ELSE
SELECT @tableName = 'rif_data.t_tiles_usa_2014';
SELECT @columnName = 'y';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Y tile number. From 0 to (2**<zoomlevel>)-1',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 't_tiles_usa_2014',
@level2type = N'Column', @level2name = 'y'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Y tile number. From 0 to (2**<zoomlevel>)-1',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 't_tiles_usa_2014',
@level2type = N'Column', @level2name = 'y';
-- SQL statement 125: Comment tiles table column >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.t_tiles_usa_2014'
ELSE
SELECT @tableName = 'rif_data.t_tiles_usa_2014';
SELECT @columnName = 'optimised_topojson';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Tile multipolygon in TopoJSON format, optimised for zoomlevel N. The SRID is always 4326.',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 't_tiles_usa_2014',
@level2type = N'Column', @level2name = 'optimised_topojson'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Tile multipolygon in TopoJSON format, optimised for zoomlevel N. The SRID is always 4326.',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 't_tiles_usa_2014',
@level2type = N'Column', @level2name = 'optimised_topojson';
-- SQL statement 126: Comment tiles table column >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.t_tiles_usa_2014'
ELSE
SELECT @tableName = 'rif_data.t_tiles_usa_2014';
SELECT @columnName = 'tile_id';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Tile ID in the format <geolevel number>_<geolevel name>_<zoomlevel>_<X tile number>_<Y tile number>',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 't_tiles_usa_2014',
@level2type = N'Column', @level2name = 'tile_id'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Tile ID in the format <geolevel number>_<geolevel name>_<zoomlevel>_<X tile number>_<Y tile number>',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 't_tiles_usa_2014',
@level2type = N'Column', @level2name = 'tile_id';
-- SQL statement 127: Comment tiles table column >>>
DECLARE @CurrentUser sysname
DECLARE @columnName sysname
DECLARE @tableName sysname /*
* SQL statement name: comment_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: table; e.g. geolevels_cb_2014_us_county_500k
* 2: column; e.g. geolevel_name
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
* Description: Comment table column
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
SELECT @tableName = 'rif_data';
IF (@tableName = '@CurrentUser')
SELECT @tableName = @CurrentUser + '.t_tiles_usa_2014'
ELSE
SELECT @tableName = 'rif_data.t_tiles_usa_2014';
SELECT @columnName = 'areaid_count';
IF EXISTS (
SELECT class_desc
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@tableName)
AND [name] = N'MS_Description'
AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@tableName)))
EXECUTE sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Total number of areaIDs (geoJSON features)',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 't_tiles_usa_2014',
@level2type = N'Column', @level2name = 'areaid_count'
ELSE
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Total number of areaIDs (geoJSON features)',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'Table', @level1name = 't_tiles_usa_2014',
@level2type = N'Column', @level2name = 'areaid_count';
-- SQL statement 128: Add tiles index: t_tiles_usa_2014_x_tile >>>
CREATE INDEX t_tiles_usa_2014_x_tile ON rif_data.t_tiles_usa_2014 (geolevel_id, zoomlevel, x);
-- SQL statement 129: Add tiles index: t_tiles_usa_2014_y_tile >>>
CREATE INDEX t_tiles_usa_2014_y_tile ON rif_data.t_tiles_usa_2014 (geolevel_id, zoomlevel, x);
-- SQL statement 130: Add tiles index: t_tiles_usa_2014_xy_tile >>>
CREATE INDEX t_tiles_usa_2014_xy_tile ON rif_data.t_tiles_usa_2014 (geolevel_id, zoomlevel, x, y);
-- SQL statement 131: Add tiles index: t_tiles_usa_2014_areaid_count >>>
CREATE INDEX t_tiles_usa_2014_areaid_count ON rif_data.t_tiles_usa_2014 (areaid_count);
-- SQL statement 132: Create tiles view >>>
/*
* SQL statement name: create_tiles_view.sql
* Type: Microsoft SQL Server SQL statement
* Parameters:
* 1: tiles view; e.g. tiles_cb_2014_us_county_500k
* 2: geolevel table; e.g. geolevels_cb_2014_us_county_500k
* 3: JSON datatype (Postgres JSON, SQL server VARCHAR) [No longer used]
* 4: tiles table; e.g. t_tiles_cb_2014_us_500k
* 5: Max zoomlevel; e.g. 11
* 6: Data schema; e.g. rif_data. or ""
* 7: RIF or user schema; e.g. $(USERNAME) or rif40
* 8: Geography; e.g. USA_2014
*
* Description: Create tiles view
* Note: %% becomes % after substitution
*/
CREATE VIEW rif_data.tiles_usa_2014 AS
WITH a AS (
SELECT geography,
MAX(geolevel_id) AS max_geolevel_id
FROM rif40.t_rif40_geolevels
WHERE geography = 'USA_2014'
GROUP BY geography
), b AS (
SELECT a.geography, z.IntValue AS geolevel_id
FROM a CROSS APPLY rif40.generate_series(0, CAST(a.max_geolevel_id AS INTEGER), 1) z
), c AS (
SELECT b2.geolevel_name,
b.geolevel_id,
b.geography,
b2.areaid_count
FROM b, rif40.t_rif40_geolevels b2
WHERE b.geolevel_id = b2.geolevel_id
AND b.geography = b2.geography
), d AS (
SELECT z.IntValue AS zoomlevel
FROM rif40.generate_series(0, 9, 1) z /* RIF or user schema; e.g. $(USERNAME) or rif40 */
), ex AS (
SELECT d.zoomlevel, z.IntValue AS xy_series
FROM d CROSS APPLY rif40.generate_series(0, CAST(POWER(2, d.zoomlevel) AS INTEGER) - 1, 1) z
), ey AS (
SELECT c.geolevel_name,
c.areaid_count,
c.geolevel_id,
c.geography,
ex.zoomlevel,
ex.xy_series
FROM c,
ex
)
SELECT z.geography,
z.geolevel_id,
z.geolevel_name,
CASE
WHEN h1.tile_id IS NULL AND h2.tile_id IS NULL THEN 1
ELSE 0
END AS no_area_ids,
COALESCE(h1.tile_id,
CAST(z.geolevel_id AS VARCHAR) +
'_' +
z.geolevel_name +
'_' +
CAST(z.zoomlevel AS VARCHAR) +
'_' +
CAST(z.x AS VARCHAR) +
'_' +
CAST(z.y AS VARCHAR)
) AS tile_id,
z.x,
z.y,
z.zoomlevel,
COALESCE(h1.optimised_topojson,
h2.optimised_topojson,
'{"type": "FeatureCollection","features":[]}' /* NULL geojson */) AS optimised_topojson
FROM (
SELECT ey.geolevel_name,
ey.areaid_count,
ey.geolevel_id,
ey.geography,
ex.zoomlevel,
ex.xy_series AS x,
ey.xy_series AS y
FROM ey, ex /* Cross join */
WHERE ex.zoomlevel = ey.zoomlevel
) z
LEFT JOIN rif_data.t_tiles_usa_2014 h1 ON ( /* Multiple area ids in the geolevel */
z.areaid_count > 1 AND
z.zoomlevel = h1.zoomlevel AND
z.x = h1.x AND
z.y = h1.y AND
z.geolevel_id = h1.geolevel_id)
LEFT JOIN rif_data.t_tiles_usa_2014 h2 ON ( /* Single area ids in the geolevel */
z.areaid_count = 1 AND
h2.zoomlevel = 0 AND
h2.x = 0 AND
h2.y = 0 AND
h2.geolevel_id = 1);
-- SQL statement 133: Comment tiles view >>>
DECLARE @CurrentUser sysname /*
* SQL statement name: comment_view.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: view; e.g. tiles_cb_us_county_500k
* 2: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
*
* Description: Comment view
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Maptiles view for geography; empty tiles are added to complete zoomlevels for zoomlevels 0 to 11. This view is efficent!',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'View', @level1name = 'tiles_usa_2014' ;
-- SQL statement 134: Comment tiles view column >>>
DECLARE @CurrentUser sysname /*
* SQL statement name: comment_view_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: view; e.g. tiles_cb_2014_us_500k
* 2: column; e.g. geography
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
*
* Description: Comment table
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Geography',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'View', @level1name = 'tiles_usa_2014',
@level2type = N'Column', @level2name = 'geography';
-- SQL statement 135: Comment tiles view column >>>
DECLARE @CurrentUser sysname /*
* SQL statement name: comment_view_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: view; e.g. tiles_cb_2014_us_500k
* 2: column; e.g. geography
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
*
* Description: Comment table
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'ID for ordering (1=lowest resolution). Up to 99 supported.',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'View', @level1name = 'tiles_usa_2014',
@level2type = N'Column', @level2name = 'geolevel_id';
-- SQL statement 136: Comment tiles view column >>>
DECLARE @CurrentUser sysname /*
* SQL statement name: comment_view_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: view; e.g. tiles_cb_2014_us_500k
* 2: column; e.g. geography
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
*
* Description: Comment table
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Zoom level: 0 to 11. Number of tiles is 2**<zoom level> * 2**<zoom level>; i.e. 1, 2x2, 4x4 ... 2048x2048 at zoomlevel 11',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'View', @level1name = 'tiles_usa_2014',
@level2type = N'Column', @level2name = 'zoomlevel';
-- SQL statement 137: Comment tiles view column >>>
DECLARE @CurrentUser sysname /*
* SQL statement name: comment_view_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: view; e.g. tiles_cb_2014_us_500k
* 2: column; e.g. geography
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
*
* Description: Comment table
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'X tile number. From 0 to (2**<zoomlevel>)-1',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'View', @level1name = 'tiles_usa_2014',
@level2type = N'Column', @level2name = 'x';
-- SQL statement 138: Comment tiles view column >>>
DECLARE @CurrentUser sysname /*
* SQL statement name: comment_view_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: view; e.g. tiles_cb_2014_us_500k
* 2: column; e.g. geography
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
*
* Description: Comment table
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Y tile number. From 0 to (2**<zoomlevel>)-1',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'View', @level1name = 'tiles_usa_2014',
@level2type = N'Column', @level2name = 'y';
-- SQL statement 139: Comment tiles view column >>>
DECLARE @CurrentUser sysname /*
* SQL statement name: comment_view_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: view; e.g. tiles_cb_2014_us_500k
* 2: column; e.g. geography
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
*
* Description: Comment table
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Tile multipolygon in TopoJSON format, optimised for zoomlevel N. The SRID is always 4326.',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'View', @level1name = 'tiles_usa_2014',
@level2type = N'Column', @level2name = 'optimised_topojson';
-- SQL statement 140: Comment tiles view column >>>
DECLARE @CurrentUser sysname /*
* SQL statement name: comment_view_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: view; e.g. tiles_cb_2014_us_500k
* 2: column; e.g. geography
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
*
* Description: Comment table
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Tile ID in the format <geolevel number>_<geolevel name>_<zoomlevel>_<X tile number>_<Y tile number>',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'View', @level1name = 'tiles_usa_2014',
@level2type = N'Column', @level2name = 'tile_id';
-- SQL statement 141: Comment tiles view column >>>
DECLARE @CurrentUser sysname /*
* SQL statement name: comment_view_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: view; e.g. tiles_cb_2014_us_500k
* 2: column; e.g. geography
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
*
* Description: Comment table
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Name of geolevel. This will be a column name in the numerator/denominator tables',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'View', @level1name = 'tiles_usa_2014',
@level2type = N'Column', @level2name = 'geolevel_name';
-- SQL statement 142: Comment tiles view column >>>
DECLARE @CurrentUser sysname /*
* SQL statement name: comment_view_column.sql
* Type: Microsoft SQL Server T/sql anonymous block
* Parameters:
* 1: view; e.g. tiles_cb_2014_us_500k
* 2: column; e.g. geography
* 3: comment. Usual rules for comment text in SQK - single
* quotes (') need to be double ('')
*
* SchemaName is set to either @CurrentUser (build) or 'rif_data' for rif40
*
* Description: Comment table
* Note: %% becomes % after substitution
*/
SELECT @CurrentUser = user_name();
EXECUTE sp_addextendedproperty
@name = N'MS_Description',
@value = N'Tile contains no area_ids flag: 0/1',
@level0type = N'Schema', @level0name = rif_data,
@level1type = N'View', @level1name = 'tiles_usa_2014',
@level2type = N'Column', @level2name = 'no_area_ids';
--
-- Load tiles table
--
-- SQL statement 144: Create load tiles view >>>
CREATE VIEW rif_data.v_tiles_usa_2014
AS
SELECT geolevel_id, zoomlevel, x, y, tile_id, areaid_count, optimised_topojson
FROM rif_data.t_tiles_usa_2014;
-- SQL statement 145: Load DB dependent tiles table from geolevel CSV files >>>
BULK INSERT rif_data.v_tiles_usa_2014
FROM 'C:\Users\phamb\Documents\Local Data Loading\Tile maker USA/mssql_t_tiles_cb_2014_us_nation_5m.csv' -- Note use of pwd; set via -v pwd="%cd%" in the sqlcmd command line
WITH
(
FORMATFILE = 'C:\Users\phamb\Documents\Local Data Loading\Tile maker USA/mssql_t_tiles_cb_2014_us_nation_5m.fmt', -- Use a format file
TABLOCK -- Table lock
);
(1 rows affected)
-- SQL statement 146: Create load tiles view >>>
DROP VIEW rif_data.v_tiles_usa_2014;
-- SQL statement 147: Create load tiles view >>>
CREATE VIEW rif_data.v_tiles_usa_2014
AS
SELECT geolevel_id, zoomlevel, x, y, tile_id, areaid_count, optimised_topojson
FROM rif_data.t_tiles_usa_2014;
-- SQL statement 148: Load DB dependent tiles table from geolevel CSV files >>>
BULK INSERT rif_data.v_tiles_usa_2014
FROM 'C:\Users\phamb\Documents\Local Data Loading\Tile maker USA/mssql_t_tiles_cb_2014_us_state_500k.csv' -- Note use of pwd; set via -v pwd="%cd%" in the sqlcmd command line
WITH
(
FORMATFILE = 'C:\Users\phamb\Documents\Local Data Loading\Tile maker USA/mssql_t_tiles_cb_2014_us_state_500k.fmt', -- Use a format file
TABLOCK -- Table lock
);
(2713 rows affected)
-- SQL statement 149: Create load tiles view >>>
DROP VIEW rif_data.v_tiles_usa_2014;
-- SQL statement 150: Create load tiles view >>>
CREATE VIEW rif_data.v_tiles_usa_2014
AS
SELECT geolevel_id, zoomlevel, x, y, tile_id, areaid_count, optimised_topojson
FROM rif_data.t_tiles_usa_2014;
-- SQL statement 151: Load DB dependent tiles table from geolevel CSV files >>>
BULK INSERT rif_data.v_tiles_usa_2014
FROM 'C:\Users\phamb\Documents\Local Data Loading\Tile maker USA/mssql_t_tiles_cb_2014_us_county_500k.csv' -- Note use of pwd; set via -v pwd="%cd%" in the sqlcmd command line
WITH
(
FORMATFILE = 'C:\Users\phamb\Documents\Local Data Loading\Tile maker USA/mssql_t_tiles_cb_2014_us_county_500k.fmt', -- Use a format file
TABLOCK -- Table lock
);
(4671 rows affected)
-- SQL statement 152: Create load tiles view >>>
DROP VIEW rif_data.v_tiles_usa_2014;
--
-- Analyze tables
--
-- SQL statement 154: Grant table/view lookup_cb_2014_us_nation_5m >>>
/*
* SQL statement name: grant_table.sql
* Type: Common SQL statement
* Parameters:
* 1: table/view; e.g. tiles_cb_2014_us_county_500k
* 2: Privileges; e.g. SELECT
* 3: Roles; e.g. rif_user, rif_manager
*
* Description: Create tiles view
* Note: %% becomes % after substitution
*/
GRANT SELECT ON rif_data.lookup_cb_2014_us_nation_5m TO rif_user, rif_manager;
-- SQL statement 155: Grant table/view lookup_cb_2014_us_state_500k >>>
/*
* SQL statement name: grant_table.sql
* Type: Common SQL statement
* Parameters:
* 1: table/view; e.g. tiles_cb_2014_us_county_500k
* 2: Privileges; e.g. SELECT
* 3: Roles; e.g. rif_user, rif_manager
*
* Description: Create tiles view
* Note: %% becomes % after substitution
*/
GRANT SELECT ON rif_data.lookup_cb_2014_us_state_500k TO rif_user, rif_manager;
-- SQL statement 156: Grant table/view lookup_cb_2014_us_county_500k >>>
/*
* SQL statement name: grant_table.sql
* Type: Common SQL statement
* Parameters:
* 1: table/view; e.g. tiles_cb_2014_us_county_500k
* 2: Privileges; e.g. SELECT
* 3: Roles; e.g. rif_user, rif_manager
*
* Description: Create tiles view
* Note: %% becomes % after substitution
*/
GRANT SELECT ON rif_data.lookup_cb_2014_us_county_500k TO rif_user, rif_manager;
-- SQL statement 157: Grant table/view hierarchy_usa_2014 >>>
/*
* SQL statement name: grant_table.sql
* Type: Common SQL statement
* Parameters:
* 1: table/view; e.g. tiles_cb_2014_us_county_500k
* 2: Privileges; e.g. SELECT
* 3: Roles; e.g. rif_user, rif_manager
*
* Description: Create tiles view
* Note: %% becomes % after substitution
*/
GRANT SELECT ON rif_data.hierarchy_usa_2014 TO rif_user, rif_manager;
-- SQL statement 158: Grant table/view geometry_usa_2014 >>>
/*
* SQL statement name: grant_table.sql
* Type: Common SQL statement
* Parameters:
* 1: table/view; e.g. tiles_cb_2014_us_county_500k
* 2: Privileges; e.g. SELECT
* 3: Roles; e.g. rif_user, rif_manager
*
* Description: Create tiles view
* Note: %% becomes % after substitution
*/
GRANT SELECT ON rif_data.geometry_usa_2014 TO rif_user, rif_manager;
-- SQL statement 159: Grant table/view adjacency_usa_2014 >>>
/*
* SQL statement name: grant_table.sql
* Type: Common SQL statement
* Parameters:
* 1: table/view; e.g. tiles_cb_2014_us_county_500k
* 2: Privileges; e.g. SELECT
* 3: Roles; e.g. rif_user, rif_manager
*
* Description: Create tiles view
* Note: %% becomes % after substitution
*/
GRANT SELECT ON rif_data.adjacency_usa_2014 TO rif_user, rif_manager;
-- SQL statement 160: Grant table/view t_tiles_usa_2014 >>>
/*
* SQL statement name: grant_table.sql
* Type: Common SQL statement
* Parameters:
* 1: table/view; e.g. tiles_cb_2014_us_county_500k
* 2: Privileges; e.g. SELECT
* 3: Roles; e.g. rif_user, rif_manager
*
* Description: Create tiles view
* Note: %% becomes % after substitution
*/
GRANT SELECT ON rif_data.t_tiles_usa_2014 TO rif_user, rif_manager;
-- SQL statement 161: Grant table/view tiles_usa_2014 >>>
/*
* SQL statement name: grant_table.sql
* Type: Common SQL statement
* Parameters:
* 1: table/view; e.g. tiles_cb_2014_us_county_500k
* 2: Privileges; e.g. SELECT
* 3: Roles; e.g. rif_user, rif_manager
*
* Description: Create tiles view
* Note: %% becomes % after substitution
*/
GRANT SELECT ON rif_data.tiles_usa_2014 TO rif_user, rif_manager;
-- SQL statement 162: Grant table/view adjacency_usa_2014 >>>
/*
* SQL statement name: grant_table.sql
* Type: Common SQL statement
* Parameters:
* 1: table/view; e.g. tiles_cb_2014_us_county_500k
* 2: Privileges; e.g. SELECT
* 3: Roles; e.g. rif_user, rif_manager
*
* Description: Create tiles view
* Note: %% becomes % after substitution
*/
GRANT SELECT ON rif_data.adjacency_usa_2014 TO rif_user, rif_manager;
-- SQL statement 163: Commit transaction >>>
COMMIT;
--
-- Analyze tables
--
-- SQL statement 165: Describe table lookup_cb_2014_us_nation_5m >>>
-- EXEC sp_help rif_data.lookup_cb_2014_us_nation_5m;
-- SQL statement 166: Analyze table lookup_cb_2014_us_nation_5m >>>
UPDATE STATISTICS rif_data.lookup_cb_2014_us_nation_5m;
-- SQL statement 167: Describe table lookup_cb_2014_us_state_500k >>>
-- EXEC sp_help rif_data.lookup_cb_2014_us_state_500k;
-- SQL statement 168: Analyze table lookup_cb_2014_us_state_500k >>>
UPDATE STATISTICS rif_data.lookup_cb_2014_us_state_500k;
-- SQL statement 169: Describe table lookup_cb_2014_us_county_500k >>>
-- EXEC sp_help rif_data.lookup_cb_2014_us_county_500k;
-- SQL statement 170: Analyze table lookup_cb_2014_us_county_500k >>>
UPDATE STATISTICS rif_data.lookup_cb_2014_us_county_500k;
-- SQL statement 171: Describe table hierarchy_usa_2014 >>>
-- EXEC sp_help rif_data.hierarchy_usa_2014;
-- SQL statement 172: Analyze table hierarchy_usa_2014 >>>
UPDATE STATISTICS rif_data.hierarchy_usa_2014;
-- SQL statement 173: Describe table geometry_usa_2014 >>>
-- EXEC sp_help rif_data.geometry_usa_2014;
-- SQL statement 174: Analyze table geometry_usa_2014 >>>
UPDATE STATISTICS rif_data.geometry_usa_2014;
-- SQL statement 175: Describe table t_tiles_usa_2014 >>>
-- EXEC sp_help rif_data.t_tiles_usa_2014;
-- SQL statement 176: Analyze table t_tiles_usa_2014 >>>
UPDATE STATISTICS rif_data.t_tiles_usa_2014;
--
-- EOF