gis spatial definitions

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Contents: __gis_spatial_definitions.sql
-- Statement count: 13
 
-------------------------------------------------
--   Create user types
-------------------------------------------------
 
 
-------------------------------------------------
--   Create spatial units of measure
-------------------------------------------------
 
CREATE  SPATIAL UNIT OF MEASURE IF NOT EXISTS  "degree"
	TYPE ANGULAR
	CONVERT USING .017453292519943
GO
 
 
CREATE  SPATIAL UNIT OF MEASURE IF NOT EXISTS  "Foot"
	TYPE LINEAR
	CONVERT USING .3048
GO
 
 
CREATE  SPATIAL UNIT OF MEASURE IF NOT EXISTS  "Kilometre"
	TYPE LINEAR
	CONVERT USING 1000
GO
 
 
CREATE  SPATIAL UNIT OF MEASURE IF NOT EXISTS  "meter"
	TYPE LINEAR
	CONVERT USING 1
GO
 
 
CREATE  SPATIAL UNIT OF MEASURE IF NOT EXISTS  "planar degree"
	TYPE LINEAR
	CONVERT USING 111120
GO
 
 
CREATE  SPATIAL UNIT OF MEASURE IF NOT EXISTS  "Statute Mile"
	TYPE LINEAR
	CONVERT USING 1609.344
GO
 
 
CREATE  SPATIAL UNIT OF MEASURE IF NOT EXISTS  "Yard"
	TYPE LINEAR
	CONVERT USING .9144
GO
 
 
-------------------------------------------------
--   Create spatial reference systems
-------------------------------------------------
 
CREATE  SPATIAL REFERENCE SYSTEM IF NOT EXISTS  "Default"
	IDENTIFIED BY 0
	ORGANIZATION 'SAP' IDENTIFIED BY 0
	LINEAR UNIT OF MEASURE "metre"
	TYPE PLANAR
	SNAP TO GRID .000001
	TOLERANCE .000001
	AXIS ORDER 'x/y/z/m'
	POLYGON FORMAT 'EvenOdd'
	COORDINATE X BETWEEN -1000000 AND 1000000
	COORDINATE Y BETWEEN -1000000 AND 1000000
GO
 
 
CREATE  SPATIAL REFERENCE SYSTEM IF NOT EXISTS  "OSGB IQX Modified"
	IDENTIFIED BY 999999
	ORGANIZATION 'EPSG' IDENTIFIED BY 999999
	LINEAR UNIT OF MEASURE "metre"
	TYPE PLANAR
	SNAP TO GRID .00001
	TOLERANCE .00001
	AXIS ORDER 'x/y/z/m'
	POLYGON FORMAT 'EvenOdd'
	COORDINATE X BETWEEN 0 AND 660000
	COORDINATE Y BETWEEN -820000 AND 1223382.6240472
	DEFINITION 'PROJCS["OSGB 1936 / British National Grid",
GEOGCS["OSGB 1936",
	   DATUM["OSGB_1936",
			  SPHEROID["Airy 1830",6377563.396,299.3249646,AUTHORITY["EPSG","7001"]],
			  AUTHORITY["EPSG","6277"]],
	   PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],
	   UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],
	   AUTHORITY["EPSG","4277"]],
UNIT["metre",1,AUTHORITY["EPSG","9001"]],
PROJECTION["Transverse_Mercator"],
PARAMETER["latitude_of_origin",49],
PARAMETER["central_meridian",-2],
PARAMETER["scale_factor",0.9996012717],
PARAMETER["false_easting",400000],
PARAMETER["false_northing",-100000],
AUTHORITY["EPSG","27700"],
AXIS["Easting",EAST],
AXIS["Northing",NORTH]]'
	TRANSFORM DEFINITION '+proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +datum=OSGB36 +units=m +no_defs +towgs84=446.448,-125.157,542.060,0.1502,0.2470,0.8421,-20.4894 +no_defs'
GO
 
 
CREATE  SPATIAL REFERENCE SYSTEM IF NOT EXISTS  "sa_octahedral_gnomonic"
	IDENTIFIED BY 2147483647
	ORGANIZATION 'SAP' IDENTIFIED BY 2147483647
	LINEAR UNIT OF MEASURE "metre"
	TYPE PLANAR
	SNAP TO GRID .000000000001
	TOLERANCE .000000000001
	AXIS ORDER 'x/y/z/m'
	POLYGON FORMAT 'EvenOdd'
	COORDINATE X BETWEEN 0 AND 1
	COORDINATE Y BETWEEN -1 AND 1
GO
 
 
CREATE  SPATIAL REFERENCE SYSTEM IF NOT EXISTS  "sa_planar_unbounded"
	IDENTIFIED BY 2147483646
	ORGANIZATION 'SAP' IDENTIFIED BY 2147483646
	LINEAR UNIT OF MEASURE "metre"
	TYPE PLANAR
	SNAP TO GRID 0
	TOLERANCE 0
	AXIS ORDER 'x/y/z/m'
	POLYGON FORMAT 'EvenOdd'
	COORDINATE X BETWEEN -1.79769313E308 AND 1.79769313E308
	COORDINATE Y BETWEEN -1.79769313E308 AND 1.79769313E308
GO
 
 
CREATE  SPATIAL REFERENCE SYSTEM IF NOT EXISTS  "WGS 84"
	IDENTIFIED BY 4326
	ORGANIZATION 'EPSG' IDENTIFIED BY 4326
	LINEAR UNIT OF MEASURE "metre"
	ANGULAR UNIT OF MEASURE "degree"
	TYPE ROUND EARTH
	SNAP TO GRID 0
	TOLERANCE 0
	ELLIPSOID SEMI MAJOR AXIS 6378137 INVERSE FLATTENING 298.257223563
	AXIS ORDER 'long/lat/z/m'
	POLYGON FORMAT 'EvenOdd'
	COORDINATE LATITUDE BETWEEN -90 AND 90
	COORDINATE LONGITUDE BETWEEN -180 AND 180
	DEFINITION 'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]'
	TRANSFORM DEFINITION '+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs'
GO
 
 
CREATE  SPATIAL REFERENCE SYSTEM IF NOT EXISTS  "WGS 84 (planar)"
	IDENTIFIED BY 1000004326
	ORGANIZATION 'EPSG' IDENTIFIED BY 4326
	LINEAR UNIT OF MEASURE "planar degree"
	ANGULAR UNIT OF MEASURE "degree"
	TYPE PLANAR
	SNAP TO GRID .000000001
	TOLERANCE .000000001
	ELLIPSOID SEMI MAJOR AXIS 6378137 INVERSE FLATTENING 298.257223563
	AXIS ORDER 'long/lat/z/m'
	POLYGON FORMAT 'EvenOdd'
	COORDINATE LATITUDE BETWEEN -90 AND 90
	COORDINATE LONGITUDE BETWEEN -180 AND 180
	DEFINITION 'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]'
	TRANSFORM DEFINITION '+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs'
GO