-- You should be connected to a DB2 database before running this.

-- Clean up tables
DROP TABLE TEST.ZIPPOLY;
DROP TABLE TEST.ZIPPOINT;
DROP TABLE TEST.NOFID;

-- To run this on DB2 for z/OS, change the schema for ST_Create_SRS to SYSPROC
-- If the SRS already exists, there will be an error but it can be ignored
--   
-- create an EPSG standard srs for NAD83
-- change the schema from db2gse to SYSPROC for DB2 on z/OS
call db2gse.ST_Drop_srs( 'NAD83_SRS_4269' , ?, ?);

call db2gse.ST_Create_srs(
 'NAD83_SRS_4269' ,
 4269,
 -180.0,
 5000000.0,
 -90.0,
 5000000,
 0,
 1000,
 0,
 1000,
 'GCS_NORTH_AMERICAN_1983',
 'EPSG4269',
     ?,
     ?) ;

-- create an EPSG standard srs for WGS84
-- change the schema from db2gse to SYSPROC for DB2 on z/OS
call db2gse.ST_Drop_srs( 'NAD83_SRS_4326' , ?, ?);
call db2gse.ST_Create_srs(

 'WGS84_SRS_4326' ,
 4326,
 -180.0,
 5000000.0,
 -90.0,
 5000000,
 0,
 1000,
 0,
 1000,
 'GCS_WGS_1984',
 'EPSG4326',
     ?,
     ?) ;

-- create an EPSG standard srs for NAD27 UTM zone 11N
-- used by autotest\gdrivers\db2.py for byte.tiff
-- change the schema from db2gse to SYSPROC for DB2 on z/O
call db2gse.ST_Drop_srs( 'NAD27_SRS_26711' , ?, ?);
call db2gse.ST_Create_srs(
 'NAD27_SRS_26711' ,
 26711,
 0,
 1000,
 0,
 1000,
 0,
 1000,
 0,
 1000,
 'NAD_1927_UTM_ZONE_11N',
 'EPSG26711',
     ?,
     ?) ;
-- create a table of polygons that has a FID column

CREATE TABLE TEST.ZIPPOLY(
   objectid     BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
  ,wkt          VARCHAR(128)
  ,zip          CHAR(5)
  ,population   INTEGER
  ,shape        db2gse.st_polygon
  );

INSERT INTO TEST.ZIPPOLY(objectid, wkt, zip, population) VALUES (
1,'POLYGON ((-122.061890 37.351950, -121.982680 37.351950, -121.982680 37.400610, -122.061890 37.400610, -122.061890 37.351950))','94086',56205
);
INSERT INTO TEST.ZIPPOLY(objectid, wkt, zip, population) VALUES (
2,'POLYGON ((-122.065560 37.332930, -121.995930 37.332930, -121.995930 37.374340, -122.065560 37.374340, -122.065560 37.332930))','94087',47499
);
INSERT INTO TEST.ZIPPOLY(objectid, wkt, zip, population) VALUES (
3,'POLYGON ((-122.059870 37.388420, -121.974720 37.388420, -121.974720 37.461520, -122.059870 37.461520, -122.059870 37.388420))','94089',13184
);
INSERT INTO TEST.ZIPPOLY(objectid, wkt, zip, population) VALUES (
4,'POLYGON ((-122.020870 37.417820, -121.961960 37.417820, -121.961960 37.463950, -122.020870 37.463950, -122.020870 37.417820))','95002',1452
);
INSERT INTO TEST.ZIPPOLY(objectid, wkt, zip, population) VALUES (
5,'POLYGON ((-121.990950 37.255880, -121.922490 37.255880, -121.922490 37.301450, -121.990950 37.301450, -121.990950 37.255880))','95008',43296
);


-- Use WGS84 standard srid
UPDATE TEST.ZIPPOLY SET shape = db2gse.st_polygon(wkt, 1003);

SELECT COUNT(*) from TEST.ZIPPOLY;

-- Register the spatial column with WGS84 to set the SRS_NAME and SRS_ID
-- columns in DB2GSE.ST_GEOMETRY_COLUMNS

call db2gse.ST_Register_Spatial_Column(
 'TEST' ,
 'ZIPPOLY',
 'SHAPE',
 'WGS84_SRS_1003',
 1,
     ?,
     ?) ;

-- create a table of points with a primary key

CREATE TABLE TEST.ZIPPOINT(
   objectid     BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY
  ,wkt          VARCHAR(128)
  ,zip          CHAR(5)
  ,population   INTEGER
  ,location     db2gse.st_point
  );

INSERT INTO TEST.ZIPPOINT(wkt, zip, population) VALUES (
'POINT (-122.022285 37.376280)','94086',56205
);
INSERT INTO TEST.ZIPPOINT(wkt, zip, population) VALUES (
'POINT (-122.030745 37.353635)','94087',47499
);
INSERT INTO TEST.ZIPPOINT(wkt, zip, population) VALUES (
'POINT (-122.017295 37.424970)','94089',13184
);
INSERT INTO TEST.ZIPPOINT(wkt, zip, population) VALUES (
'POINT (-121.991415 37.440885)','95002',1452
);
INSERT INTO TEST.ZIPPOINT(wkt, zip, population) VALUES (
'POINT (-121.956720 37.278665)','95008',43296
);

-- Use WGS84 standard srid
UPDATE TEST.ZIPPOINT SET location = db2gse.st_point(wkt, 4326);

-- Register the spatial column with WGS84 to set the SRS_NAME and SRS_ID
-- columns in DB2GSE.ST_GEOMETRY_COLUMNS

call db2gse.ST_Register_Spatial_Column(
 'TEST' ,
 'ZIPPOINT',
 'LOCATION',
 'WGS84_SRS_4326',
 1,
     ?,
     ?) ;

SELECT objectid, VARCHAR(db2gse.st_astext(location),35), zip, population from TEST.ZIPPOINT;

-- create a table that doesn't have a FID column - objectid isn't a primary key

CREATE TABLE TEST.NOFID(
   objectid     BIGINT
  ,wkt          VARCHAR(128)
  ,zip          CHAR(5)
  ,population   INTEGER
  ,location     db2gse.st_point
  );

INSERT INTO TEST.NOFID(objectid, wkt, zip, population) VALUES (
1, 'POINT (-122.022285 37.376280)','94086',56205
);
INSERT INTO TEST.NOFID(objectid, wkt, zip, population) VALUES (
2, 'POINT (-122.030745 37.353635)','94087',47499
);
INSERT INTO TEST.NOFID(objectid, wkt, zip, population) VALUES (
3, 'POINT (-122.017295 37.424970)','94089',13184
);
INSERT INTO TEST.NOFID(objectid, wkt, zip, population) VALUES (
4,'POINT (-121.991415 37.440885)','95002',1452
);
INSERT INTO TEST.NOFID(objectid, wkt, zip, population) VALUES (
9999,'POINT (-121.956720 37.278665)','95008',43296
);

-- Use NAD83 standard srid
UPDATE TEST.NOFID SET location = db2gse.st_point(wkt, 4269);

SELECT objectid, VARCHAR(db2gse.st_astext(location),35), zip, population from TEST.NOFID;

SELECT 
  VARCHAR(table_name, 16) AS table_name,
  VARCHAR(column_name, 16) AS column_name,
  VARCHAR(type_name, 12) AS type_name,
  VARCHAR(srs_name, 16) AS srs_name,
  srs_id
FROM db2gse.st_geometry_columns
WHERE table_schema = 'TEST'
;  