analytics/informix/extend/spatial.8.22.FC2/examples/sql/st_transform.sql

-- This example demonstrates various ways to use the ST_Transform function.

----------------------------------------------------------------------------
-- Example 1: Changing the false origin of a dataset
----------------------------------------------------------------------------

-- Step 1. Suppose you created a spatial_references table entry suitable
-- for Australia.  You can do this with the SE_CreateSrid function
-- as follows:

EXECUTE FUNCTION SE_CreateSrid (110, -45, 156, -10,
                                "Australia: lat/lon coords");

-- Step 2. Load all of your data for Australia.
-- In this example we will just create a table with a few points.
CREATE TABLE aus_locns (name varchar(128), locn ST_Point);

INSERT INTO aus_locns VALUES ("Adelaide",    '1002 point(139.14 -34.87)');
INSERT INTO aus_locns VALUES ("Brisbane",    '1002 point(153.36 -27.86)');
INSERT INTO aus_locns VALUES ("Canberra",    '1002 point(148.84 -35.56)');
INSERT INTO aus_locns VALUES ("Melbourne",   '1002 point(145.01 -37.94)');
INSERT INTO aus_locns VALUES ("Perth",       '1002 point(116.04 -32.12)');
INSERT INTO aus_locns VALUES ("Sydney",      '1002 point(151.37 -33.77)');

-- Step 3. After loading all of your data for the Australian mainland,
-- you realize you need to include data for some of the outlying
-- islands, like Norfolk Island and the Cocos Islands, but the false origin
-- and scale factor that you chose for SRID 1002 will not work:

INSERT INTO aus_locns VALUES ("Norfolk Is.", '1002 point(167.83 -29.24)');
INSERT INTO aus_locns VALUES ("Cocos Is.",   '1002 point( 96.52 -12.08)');

-- Step 4. The solution is to create a new spatial_references table 
-- entry with a false origin and scale factor that will accommodate
-- both the old data and new data, and then update the old data:


EXECUTE FUNCTION SE_CreateSrid (95, -55, 170, -10,
                                "Australia + outer islands: lat/lon coords");


INSERT INTO aus_locns VALUES ("Norfolk Is.", '1003 point(167.83 -29.24)');
INSERT INTO aus_locns VALUES ("Cocos Is.",   '1003 point( 96.52 -12.08)');

UPDATE aus_locns
   SET locn = ST_Transform(locn, 1003)::ST_Point
   WHERE ST_Srid(locn) = 1002;


----------------------------------------------------------------------------
-- Example 2: Projecting data on the fly
----------------------------------------------------------------------------

-- In a typical application, spatial data are stored in unprojected
-- lat/lon format.  Then, when you want to draw a map, you can 
-- retrieve the data in a particular projection, letting the Spatial
-- DataBlade do all the transformations as it retrieves data from
-- a table.

-- Step 1: Create a spatial_references table entry which is suitable
-- for your unprojected data.  For this example we will use the
-- 1983 North American Datum.  Because this is a well-known,
-- standard datum we can use the SE_CreateSrtext function to create
-- the srtext field:


INSERT INTO spatial_references 
  (srid, description, falsex, falsey, xyunits,
   falsez, zunits, falsem, munits, srtext)
VALUES (1004, "Unprojected lat/lon, NAD 83 datum",
        -180, -90, 5000000, 0, 1000, 0, 1000, 
        SE_CreateSrtext(4269));


-- Step 2: Create a table and load your data.

CREATE TABLE airports (id    char(4),
                       name  varchar(128),
                       locn  ST_Point);

INSERT INTO airports VALUES(
   'BTM', 'Bert Mooney',          '1004 point(-112.4975 45.9548)');
INSERT INTO airports VALUES(
   'BZN', 'Gallatin Field',       '1004 point(-111.1530 45.7769)');
INSERT INTO airports VALUES(
   'COD', 'Yellowstone Regional', '1004 point(-109.0238 44.5202)');
INSERT INTO airports VALUES(
   'JAC', 'Jackson Hole',         '1004 point(-110.7377 43.6073)');
INSERT INTO airports VALUES(
   'IDA', 'Fanning Field',        '1004 point(-112.0702 43.5146)');


-- Step 3: Create one or more spatial_references table entries for
-- any projections which you will need.  Be sure that the underlying
-- geographic coordinate system (in this case, NAD 83) is the same;
-- this version of the Spatial DataBlade does not perform datum
-- conversions.

INSERT INTO spatial_references 
  (srid, description, falsex, falsey, xyunits,
   falsez, zunits, falsem, munits, srtext)
VALUES (1005, "UTM zone 12N, NAD 83 datum",
        336000, 4760000, 1000, 0, 1000, 0, 1000, 
        SE_CreateSrtext(26912));


-- Step 4: Transform the data to a projected coordinate system on
-- as needed basis.

SELECT id, ST_Transform(locn, 1005) as utm FROM airports;