Synchronization of spatial data

The MobiLink server supports synchronization of tables containing columns with spatial data types. The following types of consolidated databases are supported for spatial data synchronization:

SQL Anywhere
Oracle
Microsoft SQL Server
IBM DB2 LUW
MySQL

The MobiLink server uses a Well Known Binary (WKB) representation of spatial data along with its Spatial Reference Identifier (SRID) to upload data from remote databases to a consolidated database. It also uses WKB and SRID for downloading spatial data from the consolidated database to the remote databases. Therefore, the upload and download table scripts must be able to handle spatial data in the WKB format.

Dimensional restrictions

The MobiLink server is able to synchronize two-dimensional, three-dimensional and four-dimensional spatial data between the consolidated and remote databases if the consolidated database is running on a SQL Anywhere server. However, the MobiLink server is only able to synchronize two-dimensional data between the consolidated and remote databases if the consolidated database is one of the other supported RDBMS types.

If an upload stream contains three-dimensional and/or four-dimensional dimensional spatial data and the consolidated database is running on a non-SQL Anywhere database server, the MobiLink server generates a warning message and then drops the third and/or fourth dimension value before sending the spatial data to the consolidated database.

SRID requirements

The MobiLink server does not automatically find a mapping between the SRIDs used in the consolidated and remote databases. You must make sure that either the SRIDs used in the remote databases match those defined in the consolidated databases, or user defined upload and download table scripts must be able to convert the SRIDs back and forth between the SRIDs defined in the consolidated and remote databases.

Named parameters

Upload table scripts can be written using named parameters or question marks (?). The use of question marks for SQL scripts is deprecated and it is recommended that you use named parameters. If question marks are used, each spatial column must contain two questions marks. The first one is for the WKB value and the second is for the SRID value.

If named parameters are used, the named parameters must have the following convention:

{ml r.column_name:data}

{ml r.column_name:srid}

The first named parameter represents the WKB value and the second named parameter is for the SRID.

Oracle considerations

When downloading spatial data from Oracle, keep the following in mind:

Oracle server does not generate Well-Known-Binary (WKB) values if a geometry value is entered into an Oracle database using the Oracle native format. The following example shows a work-around.

For data entered in the native Oracle spatial format, like this:

CREATE TABLE cola_markets (
  mkt_id NUMBER PRIMARY KEY,
  name VARCHAR2(32),
  shape SDO_GEOMETRY);

INSERT INTO cola_markets VALUES(
  1,
  'cola_a',
  SDO_GEOMETRY(
    2003,  -- two-dimensional polygon
    NULL,
    NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior)
    SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to
          -- define rectangle (lower left and upper right) with
          -- Cartesian-coordinate data
  )
);

The download cursor must be written as follows or the default download cursor returns 1 instead of the actual binary value:

COLA_MARKETS (SCOTT): download_cursor
SELECT t.MKT_ID,
		 t.NAME,
		 SDO_UTIL.TO_WKBGEOMETRY(SDO_UTIL.FROM_WKTGEOMETRY(SDO_UTIL.TO_WKTGEOMETRY(t.SHAPE))),
    t.SHAPE.sdo_srid
FROM SCOTT.COLA_MARKETS t

The default download script works correctly if the data has been entered in WKT format, as follows:

INSERT INTO cola_markets VALUES(
  1,
  'cola_a',
  SDO_GEOMETRY( 'polygon (( 1 1, 5 1, 5 7, 1 7, 1 1))',4326));
INSERT INTO SCOTT.cola_markets VALUES(
  2,
  'cola_b',
  SDO_GEOMETRY( 'polygon (( 5 1, 8 1, 8 6, 5 7, 5 1))',4326));
INSERT INTO SCOTT.cola_markets VALUES(
  3,
  'cola_c',
  SDO_GEOMETRY( 'polygon (( 3 3, 6 3, 6 5, 4 5, 3 3))',4326));

Oracle does not have a SRID of 0, so all flat Cartesian plane data should be entered using a SRID of 4326.