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

IBM Informix v12.10 supports ESRI 10.x Libraries

The Spatial module of IBM Informix is tailored to ESRI’s ArcSDE application server. Informix Spatial is based on ESRI’s geometry engine (Shape library) and the spatial data extension supports the new functions from ESRI SDE 10.1 libraries. It supports additional set of new functions, support overlapping IDs for spatial referencing definitions, provides support for annotation, SDE format, etc. The integration provides consistent results of spatial operations on all software tiers: database, middleware (ArcSDE), client (ArcGIS, ArcIMS).


Support for the latest 10.x libraries eases the porting and migration efforts from other competitor databases in the market to IBM Informix.

IBM Informix Spatial is best suited for GIS solutions

IBM Informix Spatial is optimized for processing location-based data sets, extending the capabilities of database engine to manage Geographic Information Systems (GIS). Informix Spatial is implemented as a data type in a unique way to provide attractive performance and storage characteristics to manage and analyze location based data sets. It is integrated at kernel level and thus helps leverage all the other features of Informix such as Replication, Continuous Availability, SOA, etc to name a few.

Refer to the following document to know in detail as to Why Informix Spatial is best suited for GIS solutions.

A comparison on B-Tree, R-Tree & Quad Tree Indexing technologies

A comparison on B-Tree, R-Tree & Quad Tree Indexing technologies in the field of Spatial

          The following table illustrates a comparison on Indexing technologies B-Tree, R-Tree and Quad Tree in the field of Spatial. Going through the content, the reader should be in a position to understand that each of the indexing technologies have their own set of strengths and weaknesses and have limitations on the areas they play around. Clearly, B-Tree is ill suited in the field of Spatial, while it gels well, when it is applied on Linear data sets. The reader can take his / her perspective as to which of the two indexing technologies are better, i.e between R-Tree and Quad Tree. I'll leave that decision to the reader based on their need and requirement.

	B-Tree	R-Tree	Quad Tree
Definition	In computer science, a B-tree is a tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic time.	R-trees are tree data structures used for spatial access methods, i.e., for indexing multi-dimensional information such as geographical coordinates, rectangles or polygons.	A Quadtree is a tree data structure in which each internal node has exactly four children.
Description	The B-tree is a generalization of a binary search tree in that a node can have more than two children. The B-tree is optimized for systems that read and write large blocks of data. It is commonly used in databases and file system’s.	Similar to the B-tree, the R-tree is also a balanced search tree (so all leaf nodes are at the same height), organizes the data in pages, and is designed for storage on disk (as used in databases)	Quadtrees are most often used to partition a two dimensional space by recursively subdividing it into four quadrants or regions. The regions may be square or rectangular, or may have arbitrary shapes.
Support Spatial Data	The B-tree access method, indexes numeric and character data only. You cannot use the B-tree access method to index spatial data.	Designed for indexing multi-dimensional information such as geographical coordinates, rectangles or polygons	Quadtrees work on two dimensional space / regions that may be square, rectangular or any arbitrary shapes
Approximation	B-Tree is optimized for Linear data set and cannot handle spatial data and features. Hence, not applicable.	The approximation of geometries cannot be fine tuned. (Spatial uses the minimum bounding rectangles.)	The approximation of geometries can be fine tuned by setting the tiling level and number of tiles.
Complexity	B-Tree is optimized for Linear data set and cannot handle spatial data and features. Hence, not applicable.	Index creation and tuning are easier.	Tuning is more complex, and setting the appropriate tuning parameter values can affect performance significantly.
Storage	B-Tree is optimized for Linear data set and cannot handle spatial data and features. Hence, not applicable.	Less storage is required	More storage is required.
Workload	B-Tree is optimized for Linear data set and cannot handle spatial data and features. Hence, not applicable.	If your application workload includes nearest neighbor queries, R-tree indexes are faster, and you can use the sdo_batch_size keyword.	If your application workload includes nearest neighbor queries, quadtree indexes are slower, and you cannot use the sdo_batch_size keyword.
Updates	B-Tree is optimized for Linear data set and cannot handle spatial data and features. Hence, not applicable.	Heavy update activity to the spatial column may decrease the R-tree index performance until the index is rebuilt.	Heavy update activity does not affect the performance of a Quadtree index.
Dimensions supported	B-Tree is optimized for Linear data set and cannot handle spatial data and features. Hence, not applicable.	You can index up to four dimensions.	You can index only two dimensions. If LRS (Linear Referencing System) data is indexed using a spatial Quadtree  index, only the first two dimensions are indexed; the measure dimension and its values are not indexed.
Recommended	B-Tree is optimized for Linear data set and cannot handle spatial data and features. Hence, not applicable.	An R-tree index is recommended for indexing geodetic data if ST_WITHIN queries will be used on it.	A quadtree index is not recommended for Indexing geodetic data if ST_WITHIN queries will be used on it.
Whole-Earth Model	B-Tree is optimized for Linear data set and cannot handle spatial data and features. Hence, not applicable.	An R-tree index is required for a whole-Earth index.	A quadtree index cannot be used for a whole-Earth index.

Reference 1: Wikipedia pages of B-Tree, R-Tree and Quad Tree

Reference 2: World Academy of Science, Engineering and Technology 46 2008