Miss is as good as a mile

A few days ago, I was reviewing my prior post and something unusual caught my attention.

How come those densely populated suburbs in Sydney are not covered by the thematic view for postal areas??? In order to further confirm the issue, I moved the map around to another state and I found some thematic views are put on Tasman sea (see below)!!! I've got an alignment issue.

This is a critical issue for a BI Mapview as Business Intelligence are not accurately aligned with the real location on map. A few centimeters shifting of map theme (geometries that represents regions) on Google Map will make users look at the business information that is actually relevant with area/spot a few kilometers away. This makes the whole spatial analysis meaningless.

After spending some time searching and studying, I found the root cause of this problem - the inconsistency between SRIDs for Google Map and spatial data in database. SRID represents "Spatial Reference system IDentifier" which is a unique value used to unambiguously identify projected, unprojected, and local spatial coordinate system definitions. These coordinate systems form the heart of all GIS applications. Older map uses old reference system while newer maps use more accurate reference system made using satellites and sophisticated electronic equipment. The coordinates for a point on the earth can vary significantly depending on the SRID used. Consequently, if all spatial data is not in the same spatial reference system, the geographic layers will not overlay and the analysis will become misleading.

In our case, the map service provider Google is using SRID 3785.

 However,when import spatial data from shapefiles into Oracle database, the SRID is set to 8307 by default. It can be retrieved by running the SQL Script below:

select table_name, column_name, srid from user_sdo_geom_metadata

where table_name = 'NSW_ELB_REGION';

Because it's not practical for us to change the SRID of Google Map, we need to do something on our spatial data in Oracle database so that it is compatible with SRID 3785.

In Oracle Spatial Developer's guide, three approaches are suggested for fixing this issue:

1, Use SRID 4055 data;

2,   Declare an EPSG rule between SRID 8307 and 3785;

3,   Call oracle stored procedures to convert spatial data that physically stored in database;

I've tested the first two approaches in my environment. Either of them fixed the alignment issue perfectly for me.

For the first approach, all you have to do is specify the SRID value as 4055 while import the shapefile data with Oracle Mapbuilder (step in my last post). Then the data will be loaded into Oracle database as SRID 4055 which is compatible with Google's SRID 3785.

 

However, if reloading the data is not a feasible option for you, you can run the following SQL script to declare a transformation rule between SRID 8307 and 3785. As the result, when Oracle retrieve the spatial data that you loaded, it will apply the rule to transfer it to SRID 3785 and send it to Oracle Mapviewer for rendering.

CALL sdo_cs.create_pref_concatenated_op(830723785,
'CONCATENATED OPERATION',
TFM_PLAN(SDO_TFM_CHAIN(8307, 1000000000, 4055, 19847, 3785)),
NULL);

Now, the SRIDs between the spatial data and Google Map are consistent and I can have my Business Intelligence perfectly overlay with Google Map. No more CBD region displayed as middle of no where or resident postal area sitting on the surface of sea.