Getting started with Informix Spatial

IBM Informix Spatial feature, extends the capability of IBM Informix as an RDBMS, to handle location based datasets, referred as spatial datasets, along with the traditional RDBMS datasets, under the same roof.

The LONGITUDE & LATITUDE values, often referred as X & Y coordinates, together constitute the location dataset.

I would recommend to read through the earlier blog work 'IBM Informix Spatial is best suited for GIS solutions', to understand, what's makes Informix Spatial, so special.


The blog 'Getting started with Informix Spatial' will help the user to get a feel of Hands-On experience to play with the built-in Spatial Data Types, while creating a table and then inserting the data into the columns with these types.

Plan to use the following simple shell script to get to know the built-in spatial types, how to define them against columns in a table, work your way to insert records into the table and finally, use standard SQL queries to query both, non-spatial (i.e traditional) data and spatial data together, in one statement. It takes no more than 10min to run the shell script and understand Informix Spatial syntax.

1.      The shell script is well tested on Linux and might need minor changes on Unix platform. It won’t work on Windows platform. However, you can copy paste SQL statements high lighted in blue in following section and execute on windows manually.

2.      Ensure that Informix engine is online. You can use "onstat –"  command

3.      Downloadthe zip file (size 3KB) or Copy - Paste the following link to the browser:

https://drive.google.com/file/d/0B4mxiMcgUauaM0xFd0tFeWtBY1E/view?usp=sharing

4.      Unzip the file on linux/Unix machine under some test directory

5.      You might need to execute dos2unix command for each file before execution

6.      Execute main.sh and just follow the instructions.

Output of shell script should look like this :

SERVER1:/home/amprasan/spatial_demo> sh main.sh

################################################################
Welcome to Informix Spatial demo.

IBM Informix has Built-In Data Types to store Location Coordinates:
 - Latitude &  Longitude
It has over 80 plus built-in routines to manage and perform analytics:
 ST_Geometry
     - Super Type ( Can hold data for any or all of the following data types
 ST_POINT
     - Stores Individual Location Coordinates
 ST_LINESTRING
     - Stores a set of POINTS that constitute a LINESTRING
 ST_POLYGON
     - Stores a set of POINTS that constitute a POLYGON, i.e closed area
 ST_MULTIPOINT
     - Stores a set of MULTI Location Coordinates
 ST_MULTILINESTRING
     - Stores a set of POINTS that constitute a MULTI LINESTRING
 ST_MULTIPOLYGON
     - Stores a set of POINTS that constitute a MULTI POLYGON, i.e closed areas

 Press ENTER to Continue

################################################################

This Spatial Demo has 2 sections:
A) Setup Database
B) Perform Data Retrieval Queries
If You have already performed steps for (A) and interested in only section (B),
please enter 1 else enter 2:

2
################################################################
Cleaning up old files...
Clean up is over. Please presss Enter to continue

################################################################
Creating dbspace spat_dbspace1
Your evaluation license will expire on 2015-04-01 00:00:00
execute function admin('create dbspace','spat_dbspace1',
'/home/amprasan/IDS1210FC4/storage/spat_dbspace1','200 MB','0')

476fb028         6        0x60001    8        1        2048     N  BA    informi
x spat_dbspace1
47a9c028         8      6      0          102400     102347                PO-B-
D /home/amprasan/IDS1210FC4/storage/spat_dbspace1

dbspace spat_dbspace1 creation .. passed
Please press Enter to continue

################################################################
Creating database spatial_demo
drop database if exists spatial_demo;
create database spatial_demo in spat_dbspace1 with buffered log

database spatial_demo creation .. passed
Please press Enter to continue

################################################################
Creating table test_spatial
create table test_spatial
 (
   geospatial_type varchar(20),
   geospatial_value ST_Geometry
  ) in spat_dbspace1;
create unique index geospatial_type_ix1 on test_spatial (geospatial_type) using
btree;
create index geospatial_value_ix2 on test_spatial (geospatial_value st_geometry_
ops) using rtree;
alter table test_spatial add constraint primary key (geospatial_type) constraint
 geospatial_type_pk;

Spatial Table creation .. passed
Please press Enter to continue

################################################################
Inserting POINT location coordinate
INSERT INTO test_spatial VALUES('Point', ST_PointFromText('point (10.02 20.01)',
4));

Insertion of Point coordinate .. passed
Please press Enter to continue

################################################################
Inserting LINESTRING location coordinate
INSERT INTO test_spatial VALUES('Linestring',ST_LineFromText('linestring (10.02
20.01,10.32 23.98,11.92 25.64)',4));

Insertion of Linestring .. passed
Please press Enter to continue

################################################################
Inserting POLYGON (Closed Area) location coordinate
INSERT INTO test_spatial VALUES('Polygon',ST_PolyFromText('polygon ((10.02 20.01
,11.92 35.64,25.02 34.15,19.15 33.94, 10.02 20.01))',4));

Insertion of Polygon .. passed
Please press Enter to continue

################################################################
Inserting MULTIPOINT location coordinates
INSERT INTO test_spatial VALUES('Multipoint',ST_MPointFromText('multipoint (10.0
2 20.01,10.32 23.98,11.92 25.64)',4));

Insertion of Multi Point coordinates .. passed
Please press Enter to continue

################################################################
Inserting MULTILINESTRING location coordinates
INSERT INTO test_spatial VALUES('Multilinestring',ST_MLineFromText('multilinestr
ing ((10.02 20.01,10.32 23.98,11.92 25.64), (9.55 23.75,15.36 30.11))',4));

Insertion of Multi Linestring .. passed
Please press Enter to continue

################################################################
Inserting MULTIPOLYGON (Closed Area) location coordinates
INSERT INTO test_spatial VALUES('Multipolygon',ST_MPolyFromText('multipolygon ((
(10.02 20.01,11.92 35.64,25.02 34.15,19.15 33.94,10.02 20.01)),((51.71 21.73,73.
36 27.04,71.52 32.87,52.43 31.90,51.71 21.73)))',4));

Insertion of Multi Polygon .. passed
Please press Enter to continue

Congratulations!!! Your setup is successful. Please press Enter to refresh the screen and be ready to see the data retrieval queries

################################################################
Simple select on test_spatial table to see POINT coordinates
select * from test_spatial where geospatial_type='Point';
Press enter to see output

 Database selected.

geospatial_type   Point
geospatial_value  4 POINT (10.0200000603 20.0099999464)

1 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Simple select on test_spatial table to see MULTIPOINT coordinates
select * from test_spatial where geospatial_type='Multipoint';
Press enter to see output

Database selected.

geospatial_type   Multipoint
geospatial_value  4 MULTIPOINT (10.0200000603 20.0099999464, 10.3199999598 23.9
                  799999397, 11.9199999262 25.6399999195)

1 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Simple select on test_spatial table to see LINESTRING coordinates
select * from test_spatial where geospatial_type='Linestring';
Press enter to see output

Database selected.

geospatial_type   Linestring
geospatial_value  4 LINESTRING (10.0200000603 20.0099999464, 10.3199999598 23.9
                  799999397, 11.9199999262 25.6399999195)

1 row(s) retrieved.

Database closed.

Press enter to continue


################################################################
Simple select on test_spatial table to see MULTILINESTRING coordinates
select * from test_spatial where geospatial_type='Multilinestring';
Press enter to see output

Database selected.

geospatial_type   Multilinestring
geospatial_value  4 MULTILINESTRING ((10.0200000603 20.0099999464, 10.319999959
                  8 23.9799999397, 11.9199999262 25.6399999195),(9.55000006706
                  23.75, 15.3600000805 30.1100000805))

1 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Simple select on test_spatial table to see POLYGON coordinates
select * from test_spatial where geospatial_type='Polygon';
Press enter to see output

Database selected.

geospatial_type   Polygon
geospatial_value  4 POLYGON ((10.0200000603 20.0099999464, 19.1500000335 33.939
                  9999866, 25.0200000603 34.1500000335, 11.9199999262 35.639999
                  9195, 10.0200000603 20.0099999464))

1 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Simple select on test_spatial table to see MULTIPOLYGON coordinates
select * from test_spatial where geospatial_type='Multipolygon';
Press enter to see output

Database selected.

geospatial_type   Multipolygon
geospatial_value  4 MULTIPOLYGON (((10.0200000603 20.0099999464, 19.1500000335
                  33.9399999866, 25.0200000603 34.1500000335, 11.9199999262 35.
                  6399999195, 10.0200000603 20.0099999464)),((51.7100000469 21.
                  7299999397, 73.3600000805 27.0399999531, 71.5200000603 32.870
                  0000268, 52.4300000402 31.9000000335, 51.7100000469 21.729999
                  9397)))

1 row(s) retrieved.

Database closed.

Press enter to continue

Congratulations!!! You have successfully queried the Spatial Table. Please press  Enter to exit the Spatial Demo