Digging a little deeper into Google Fusion Tables – A technical GIS perspective

Before I start getting into too much details about Google Fusion Tables, I will provide a link out to Google Fusion Tables. If you haven’t heard/played around with it yet, i would strongly encourage that you take a little time to do so. It will definitely not disappoint you and is definitely worth the time. It is still in ‘Beta’, but it is looking good.

The ‘Map’ and the ‘Intensity Map’ visualization of the table data should be of special interest to all the GIS folks. It makes the process of mapping data real easy. The ‘Location’ field type in Fusion Tables supports both street address strings and KML string representation of geometries. The street addresses entered into the location field get automatically geocoded and are viewable on the map visualization.

• Even though the documentation doesn’t explicitly state it, the Location field supports the ‘MultiGeometry‘ representation in it’s Location field alongside the Point, LineString and the Polygon representations
• This might be pretty obvious, but it supports only the WGS 84 Geographic coordinate system just like KML.

The map visualization also currently simple thematic rendering of maps based on certain column values. Some of the documentation on how to acheive it and available options are not very easy to find. So, I thought a link to the documentation might help later. The list of the available map markers are here. There are also a very good collection of publicly available data on Fusion Tables also. Check out the USA State and County boundaries here. There is also a wealth of other information out there already on Fusion Tables that are publicly available and can be easily location with a simple search. You can also upload ‘.csv’, ‘.kml’, spreadsheet files to Fusion Tables. The ‘ShpEscape‘ site also allows us to upload ‘.shp’ files to Fusion Tables. Once uploaded, data can simply be shared via an URL rather than emailing ‘.shp’ files around as attachments. Government agencies are also taking to Fusion Tables. Check out data from USDA NRCS, State of California and Natural Earth Vector data. I am hoping that the list gets bigger. Apart from just sharing data, it allows us to easily apply different chart visualizations to the data to glean useful trends and analytics for better informed decision making. Pretty powerful tools to unleash the power of data.

Fusion Tables also allows us to merge/join two tables in Fusion Tables based on a shared key. Fusion Tables also allows us to create views from base tables where only a filtered list of rows or columns are visible.

The views feature in Fusion Tables enables us to set user permissions based on columns and rows. To accomplish this, keep the base table private and create views that display only a filtered set of rows or columns. Now, the views alone can be shared with users. Users get access to different views as per their permission set.

The Google Fusion Tables also provides a simple and powerful API over HTTP to administer and manage your data in Fusion Tables. Public tables can be easily managed via simple HTTP requests to Fusion Tables identifying the table. Private tables can also be managed pretty easily using OAuth authenticated requests. The API does have some missing features also. The major one being that Fusion Tables does not support ‘OR’ queries. This missing functionality arises from the fact that Fusion Tables is built on top of Google’s DataStore.

• Views cannot be created from Merged tables, but only from base tables
• The resulting data from querying data from Fusion Tables is a comma delimited list of field values. Text column values are not normally not inside quotes unless they contain commas as a part of their field value. The Location field values are returned as KML string representations and they can contain commas in them. So, beware of this return format which throws a monkey wrench into the code needed for splitting the field values from the Fusion Tables response.

Google maps api v3 also supports displaying Fusion Tables data as overlays. The maps api pop-up bubble can be customized via Fusion Tables using any built-in templates or by providing custom HTML templates. The data being displayed on the map can also be filtered by providing a query string.

• Note that the Fusion Tables query used in the maps api does not like queries of the type ‘Select * FROM’. It doesn’t like the ‘*’ and requires a column name to be specified
• All Fusion Tables layers on the map get drawn on the map as a single overlay. That is, even if you have 10 Fusion Tables layers added to your google map, the api does not make ‘n’ tile requests for the 10 layers individually making the number of images being requested n*10, but the api only request ‘n’ tiles for all the Fusion Tables layers. This is just like how KML layers are handled in the maps api.

Fusion Tables does cluster your data into points on the map automatically at high scale levels. There are also some data serialization limits built-in to the Fusion Tables API. There is currently no way to display private Fusion Tables data overlayed in the google maps api v3. But that feature is supposed to be coming for google maps premier customer. I have submitted a list of Feature Requests (see below) with the Fusion Tables team, please star them if you would like to see them also.

• Enable mapping private fusion tables with the FusionTablesLayer in the maps API
• Need reporting feature in Fusion Tables
• Would like ability to run multiple queries in single request
• Data projection
• Create MERGED Tables Views through the API
• need ability to set table map styles thru the api

That said, Fusion Tables is teh awesome.

Advertisement

Well Known Text (WKT) representation for MultiPoint

Adding to my experience with Oracle from the last post. Turns out that the ‘Well Known Text’ (WKT) representation of MultiPoint geometries differs between Oracle and MS SQL Server,MySQL. Consider the ‘STMPointFromText‘ method in MS SQL Server, the representation of the MultiPoint geometry it expects is like the example shown below.

MULTIPOINT(-122.360 47.656, -122.343 47.656)

As you can see, the Latitude & Longitudes are separated by a space and the coordinate pairs are separated by a comma and that’s it. Here is how Oracle expects the WKT for a MultiPoint geometry to be represented.

MULTIPOINT((-122.360 47.656), (-122.343 47.656))

Oracle expects the coordinate pairs to also be enclosed with parenthesis. Wikipedia seems to agree with the Oracle representation also. Apparently, the initial OGC specifications were not clear and the community started using the first representation for MultiPoint geometries. Some of the .NET GIS libraries I work with use the first version of the MultiPoint geometry WKT. But OGC has clarified the specifications and accordingly, the second version used by Oracle is the correct one. MS SQL Server and MySQL use the first version of the MultiPoint geometry WKT. This has turned out to be a pain for us and probably for other gis developers out there also.

Oracle spatial and the web mercator spatial reference system

We have been attempting to streamline the way we store and process spatial data in Oracle. Normally, we would create our spatial tables as FeatureClasses from ArcCatalog and create plain old Oracle tables for our business/attribute data that would be joined with the spatial data in the FeatureClasses through a shared key field to create a SDE spatial view. This works just fine, but there are some drawbacks to this approach

• SDE creates unnecessary fields on the FeatureClass table in Oracle, like the field cad annotation that just don’t need to be there.
• Creating FeatureClasses manually from ArcCatalog is a process that cannot be automated as a part of the build process. Ideally, we like to execute a command from our build scripts that will delete our entire model from the database and run another command to recreate the entire model to start off with a clean slate for testing our every build. These scripts will also be used during the deployment process when the database needs to be recreated in another environment.
• FeatureClasses will need to be edited through ArcObjects (versioned/unversioned) or ArcMap(versioned) which would need an ESRI license on any client machine wanting to edit the spatial data. In a web environment, this would mean a ESRI license on the web server.

We wanted our setup to try and work around some of the drawbacks above. So, we wrote SQL scripts that would

1. Create the spatial table
2. Insert metadata about the spatial table into the Oracle geometry metadata table USER_SDO_GEOM_METADATA
3. Create a spatial index on the table
4. Register the Oracle spatial table with ArcSDE as a FeatureClass using the ‘sdelayer -0 register’ command

Here is some sample SQL scripts to perform the first three steps with Oracle

CREATE TABLE customers (
  customer_id NUMBER,
  last_name VARCHAR2(30),
  first_name VARCHAR2(30),
  street_address VARCHAR2(40),
  city VARCHAR2(30),
  state_province_code VARCHAR2(2),
  postal_code VARCHAR2(9),
  cust_geo_location SDO_GEOMETRY);
 
INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
   VALUES ('CUSTOMERS', 'CUST_GEO_LOCATION',
   SDO_DIM_ARRAY
     (SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5),
     SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)),
   8307);

CREATE INDEX customers_sidx ON customers(cust_geo_location)
  INDEXTYPE IS mdsys.spatial_index;

The sample sde command to register the table as a multi-polygon FeatureClass with ArcSDE specifying the bounds

sdelayer -o register -l CUSTOMERS,CUST_GEO_LOCATION -e a+M -C CUSTOMER_ID -i sde:oracle11g -s SERVER_NAME -u XXX -p YYY@orcl -t SDO_GEOMETRY -P High -x -180,-90,11132000

As you can see from the commands above the spatial table is registered with Oracle to have an SRID (spatial reference id) of 8307 which denotes the WGS 84 Geographic Coordinate Systems. Oracle maintains its own CS_SRS table where it maintains a list of Oracle supported spatial reference systems. The SRID specified when adding the geometry metadata with Oracle is expected to be present in the CS_SRS table, else Oracle will insert the metadata into its tables. The bounds specified when registering the spatial table as a FeatureClass with ArcSDE also reflects the WGS 84 GCS.

So far so good. The above solution works like a charm. Now that the ArcGIS Online base maps have moved to the Web Mercator projection system used by google and bing, we also wanted to maintain our data in Web Mercator so that the GIS server doesn’t have to reproject the data when rendering maps and also so that we could serve out spatial data as GeoJSON/ArcJSON from our custom web services to consumed by web clients. Here is where trouble started. Oracle does not have a SRID for the web mercator projection system. We ran through the CS_SRS table to check for it maintained under a different id but with no luck. The process of registering a new SRID with Oracle is not documented anywhere as far as I can tell. Apparently, it is not as simple as adding an entry into the CS_SRS table which we tried unsuccessfully.

So, to work around this issue, we registered our spatial table with the Oracle geometry metadata table without an SRID. So, Oracle thinks the SRID for the spatial table in NULL. This becomes a problem when we try to insert geometries with ESRI’s SRID 102113 into the spatial table. Oracle doesn’t let us insert geometries into the spatial table whose is not NULL to match the entry in its geometry metadata tables. So, we are forced to insert geometries with a NULL SRID into the table. But ArcSDE needs to know that the spatial table is in the Web Mercator projection system. To do this, we registered the spatial table using the ‘sdelayer -o register’ specifying the projection file “WGS 1984 Web Mercator (Auxiliary Sphere).prj” from ESRI. See the sample below

sdelayer -o register -l CUSTOMERS,CUST_GEO_LOCATION -e a+M -C CUSTOMER_ID -i sde:oracle11g -s SERVER_NAME -u XXX -p YYY@orcl -t SDO_GEOMETRY -P High -G file=”C:\TEMP\WGS 1984 Web Mercator (Auxiliary Sphere).prj”

The insert statement for the Oracle geometry metadata tables look like this

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO)

VALUES (‘CUSTOMERS’, ‘CUST_GEO_LOCATION’,

SDO_DIM_ARRAY

(SDO_DIM_ELEMENT(‘X’, -20037700, 20037700, 0.1),

SDO_DIM_ELEMENT(‘Y’, -20037700, 20037700, 0.1)));

This is ugly, but it works. We were able to view and edit the data in ArcMap just fine.

Here are some more things discovered along the way

• Oracle makes all the table names and the field names all upper case by default even when you specify the names in lower case in the SQL statements. We can force Oracle to use lower cases alphabets in the table/field names by specifying the table/field names in the SQL statements by enclosing them in quotes.
• If you use the technique above to use table/field names in lower case for the spatial tables, you will not be able to register the geometry metadata for the table with Oracle. This is because Oracle expects the spatial table/field name to be in all upper case for insertion into the geometry metadata table. This is just a crazy crazy thing and i can’t imagine this requirement being intentional.
• Oracle spatial SQL syntax is ugly. Very ugly. They can definitely learn from the sweet SQL syntax in MS SQL SERVER 2008.
• The free version of Oracle called Oracle XE does support the spatial data types. That is, in Oracle XE we can store columns whose data type is MDSYS.SDO_GEOMETRY. Oracle XE also allows us to perform some spatial operations on these spatial columns but not all spatial operations that are available in the enterprise edition of Oracle. The spatial features that are available in the XE edition is not documented anywhere as far as I can tell. Oracle spatial extensions bring more spatial features to Oracle enterprise edition like raster support etc.
• The MDSYS.Oracle ST_GEOMETRY is different from the ESRI ST_GEOMETRY and is a wrapper around MDSYS.SDO_GEOMETRY

If you are aware of a better way to do things with Oracle and SDE for the web mercator projection, please let me know, I am all ears. I hope this post save some other poor soul some pain and suffering.