Raster statistics + SIMD === winning

Recently, the .NET framework 4.6 enabled features that would allow us to leverage SIMD (Single Instruction Multiple Data) capabilities that have been present in our CPUs since the 2000s and deliver faster performance for calculating raster statistics.

We were using the .NET bindings for GDAL to calculate a few simple raster statistics for reporting needs. I got curious as to the kind of performance boost that we could be seeing if we leveraged SIMD for this purpose. So, I created a little console app to explore the performance gains. Here is what i observed.

To start off, we need to check if our CPU supports SIMD instructions. Although, I expect that everyone’s computer will support SIMD, here is simple way to check. Download and run CPU-Z. Keep an eye out for the SSE instructions as shown below.

Clone the git repo or just download the executable from here. And run the SIMD.exe file with a GeoTIFF file as a parameter. Needless to say, the console app is pretty rudimentary and was written solely to observe SIMD performance gains. I would highly encourage you to clone the repo and explore further if you are interested. The app currently just reads all the raster image into memory and sums all the values to produce a total sum. Not very useful, but i am hoping it can get you started in the right direction to explore further. The amount of code required or in other words to modify your current app to leverage should be manageable and shouldn’t require a complete rewrite. Also, currently the SIMD support in the .NET framework only works under 64-bit and in release mode. That had me stumped for a bit. But, there is very useful “IsHardwareAccelerated” Flag that helps you find out if your vectorized code is actually reaping the benefits of the SIMD hardware.

Raster X size: 28555
Raster Y size: 17939
Raster cell size: 512248145
Total is: 146097271
Time taken: 4568
SIMD accelerated: True
Another Total is: 146097271
Time taken: 1431

Here is the result from a run on a sample raster image. With the SIMD vectors, the execution went from 4568 to 1431 milliseconds. That’s almost 1/3 of the original time. Pretty tempting isn’t it?

The performance measure doesn’t really prove anything and is purely meant to satisfy my own curiosity. Your mileage may vary. To eek out any gains out of using SIMD vectors, you will need to be processing a large enough array that will justify the overhead of creating the SIMD vectors.

I realize that pretty much everything needs to be available in JavaScript to be relevant these days 🙂 So, if JavaScript is your cup of tea FireFox has experimental support for SIMD vectors. Soon we will be running raster statistics on the browser. Actually, we currently are running some raster statistics but without the SIMD bits. Hopefully, i will get around to writing about that soon.

Clone the repo here

Advertisement

Even though talking about ‘big data’ is the hipster thing to do, let’s talk about ‘large data’ (spatial)

First things first, ‘large data’ is something that I made up for lone sake of discussing spatial data where the individual shapes in every record are by themselves huge 🙂 . Data of this sort pose their own unique problems, even though, these issues are solvable with a little bit of ingenuity. The ‘big data’ problem is much harder to solve and seems to be getting a lot of attention lately.

Some examples of this data is US state boundaries, county boundaries etc. The size of the individual state shapes in the US state boundary dataset that has not been generalized (or simplified) can by themselves be formidable. The size of the state shapes is generally much bigger for the coastal states than for the land locked states. In one of the states boundary datasets I am working with, the size of Alaska’s state shape serialized to JSON by itself was close to 5 MB. Pushing such large data as-is to the client will just slow down the loading and rendering of web maps. And there are also other datasets like eco-regions etc where the individual region shapes are just the union of a multiple state shapes.

As you can guess, the size of the eco-regions shapes are also rather large. Such datasets create problems on multiple fronts. Because of the sheer size of these shapes, they need to generalized before they can be sent to the client (browser) for display purposes. Generalizing such shapes will use up a chunk of your CPU time, so these generalized shapes really to be cached to allow your app to be scalable. Caching such static datasets with such large shapes shouldn’t be too much of an issue but it is something that needs to be additionally done to handle the largeness of the shape 🙂

Another issue posed by the large datasets is with respect to the spatial operations that need to be done using them. Spatial indexes in databases make simple spatial operations like point-in-polygon really fast even against a large and complex polygon shape. But calculating the intersections of two or more large and complex polygons can still be a CPU intensive operation that takes a while. For example, if we have a couple of large datasets like let’s say eco-regions and watersheds, finding all forest stand areas that lie within eco-region ‘A’ and watershed area ‘2’ can still be a time-consuming operation as determining if an area completely lies within other large shapes is more time-consuming than checking if it intersects. And for more complex spatial operations that span across multiple large datasets, the number of CPU cycles needed increases drastically.

Most databases these days like MS SQL Server, Oracle, PostGIS all come packaged with really great spatial data types and spatial operation capabilities that makes the lives of application developers much easier. Saying that these databases have greatly simplified things would actually be an understatement. For transaction processing applications, the databases should be highly available, should be very fast, scale and support multiple concurrent users. But here comes the dilemma, if we perform such taxing spatial operations in the database, the spatial operations will use all the CPU cycles and keep the CPU utilization at 100% for the duration of the spatial operation. This means that other requests coming into the database will either be processed slower since they have to wait for the spatial operation to free up some CPU cycles or just timeout because the database has been unavailable for too long. This is not a good thing to have happen to an online transaction processing database. One other huge culprit here is also the serialization or de-serialization of large geometries into WKT/JSON/WKB to get shapes in and out of the database. Some databases MS SQL Server provide CLR data types that eliminates the need for serialization/deserialization, for other databases you might be out of luck based on the technologies you have to use. So, yes these ST_GeomFromText, ST_GeomFromGeoJSON, ST_AsGeoJSON are amazingly useful, but it is good to be aware of the side effects.

With good caching solutions, a lot of the problems described here can be alleviated, but they are problems nonetheless and don’t make your life easier 🙂 Let me know your thoughts…

Using client side routing in web mapping applications

Thought I would share how I have been using client side routing in a web mapping application that I have been working on for a little while now. The application is coming along nicely and I have been having quite a blast writing CoffeeScript for the app. If you haven’t looked into CoffeeScript yet, please consider doing so. I found it to be very very helpful.

But CoffeeScript is not the point of this post. There are a lot more resources out there on the web to help you pick up CoffeeScript. Client side routing is something that I have been doing with backbone.js. backbone.js is a neat little library that helps users build cleaner web apps by encouraging the MVC style development in web pages. backbone.js might seems a little intimidating at first but if you stick with it, you will find it very helpful. Although, I wouldn’t suggest using backbone.js for simple web pages. It is mainly targeted towards single page web apps where you are writing a lot of javascript and updating only some sections of the web page based on ajax requests.

Speaking of single page web apps, i think a lot of web based mapping apps fall in that bucket, especially since a lot of GIS users expect to see all their favorite ArcMAP functionality in their web apps also. That is a battle that I have been fighting for a while now and have a sneaky suspicion that there is a long way to go. Although, I should mention that I have been able to push a ‘workflow driven app’ over ‘map driven workflow’ a little bit further along than I have been able to do in my earlier projects. The feedback so far has not been too shabby either. As far as I can tell, no user has reported the ‘Map Toolbar’ missing 🙂

The client side routing techniques uses the hash tag to refresh sub sections of a web pages optionally thru AJAX and without the need for a full page postback. Ok, so, what does all this stuff have to do with web mapping apps? Well, I am leveraging the client side routing technique to allow users to select features on the map via the URL. Hopefully, the screenshots below will shed more light on it

The above screenshot shows the URL in the browser address bar reflecting the selected feature on the map. The URL in the address bar reads “…/StewardshipPlan/18701/Stands#/Stand/1904”. The initial “…/StewardshipPlan/18701/Stands” portion of the URL indicates the page that is currently loaded by the browser. The page contains the map that the user can interact with. The latter “#/Stand/1904” portion of the URL is the client side route and allows backbone.js to call our javascript that triggers the selection of the feature on the map. The selected feature on the map and the client side route of the URL in the address bar is synchronized. That is, the user can type in the URL in the address bar and the page will open up with the stated feature selected on the map, or the user can browse around the map and select other features by clicking them and the client side route will update to reflect the current feature selection. Here is another screenshot with another feature selected

That is a neat trick, but what does it buy me? Well, for starters, you can email around URLs for features to the people you are collaborating with and when they browse to the link, they will arrive at the specific feature itself on the map and with it’s attribute window open and ready for editing it needed. Also, not sure how useful this is, but if you want to go back to your previously selected feature, just click te browser back button. But as a developer, building my app this way helped me get rid of a lot of gluey jquery event binding to button click events and replace them with just links on the page which trigger the right javascript  via client side routing. Also, it almost forces me to write modular javascript which is very important for maintenance.

I have taken this line of thought a little further out and have enabled our split and merge functionalities to be routable too. Check the screenshots

The Merge and the split URLs perform the right feature selection and enable all the right map tools when the user navigates to the URL shown in the screenshots.

This is the first time I have used client side routing to build web mapping features like the ones described above. So far, i would say that it has worked out pretty good from a developer’s perspective. Any thoughts/suggestions about it is welcome and would be appreciated.

MBTilesViewer

I also created this bare bones MBTiles cache viewer to view the tile cache in MBTiles format. This application does not do much, just display the tilecache on a map. To view a MBTiles file, just fire up the viewer and start dragging and dropping MBTiles cache files on to it. You can drag and drop multiple MBTiles cache files at the same time if needed. And also, you can create MBTile caches with the TileCutter. The viewer was created using the ESRI WPF map control. If you would like to see this viewer do more, let me know 🙂

MBTilesViewer can be downloaded here.

TileCutter update – With support for OSM and WMS map services

I just added support for creating MBTiles caches for WMS map services and also to download OSM tiles into the MBTiles format. MBTiles cache for WMS map services would improve map rendering performance, but why did i add support for OSM tile sets? Well, they will come in handy for disconnected/offline use cases. So, here are some usage examples

ArcGIS Dynamic Map Service:

TileCutter.exe -z=7 -Z=9 -x=-95.844727 -y=35.978006 -X=-88.989258 -Y=40.563895 -o=”C:\LocalCache\ags.s3db” -t=agsd -m=”http://server.arcgisonline.com/ArcGIS/rest/services/I3_Imagery_Prime_World_2D/MapServer”

WMS Map Service 1.1.1:

TileCutter.exe -z=7 -Z=9 -x=-95.844727 -y=35.978006 -X=-88.989258 -Y=40.563895 -o=”C:\LocalCache\wms111.s3db” -t=wms1.1.1 -m=”http://sampleserver1-350487546.us-east-1.elb.amazonaws.com/ArcGIS/services/Specialty/ESRI_StateCityHighway_USA/MapServer/WMSServer”

WMS Map Service 1.3.0:

TileCutter.exe -z=7 -Z=9 -x=-95.844727 -y=35.978006 -X=-88.989258 -Y=40.563895 -o=”C:\LocalCache\wms130.s3db” -t=wms1.3.0 -m=”http://sampleserver1-350487546.us-east-1.elb.amazonaws.com/ArcGIS/services/Specialty/ESRI_StateCityHighway_USA/MapServer/WMSServer”

OSM:

TileCutter.exe -z=7 -Z=9 -x=-95.844727 -y=35.978006 -X=-88.989258 -Y=40.563895 -o=”C:\LocalCache\osm.s3db” -t=osm -m=”http://tile.openstreetmap.org”

And always just type “TileCutter -h” for usage information.

Want to customize the parameters with which the maps are being generated? Just use the “-s” command line option ans specify the setting in a query string format.

TileCutter.exe -z=7 -Z=9 -x=-95.844727 -y=35.978006 -X=-88.989258 -Y=40.563895 -o=”C:\LocalCache\ags.s3db” -t=agsd -m=”http://server.arcgisonline.com/ArcGIS/rest/services/I3_Imagery_Prime_World_2D/MapServer” -s=”transparent=true&format=jpeg”

Also, if some of the tile requests result in errors, the level, column, row and error message information would be logged into a text file in the same directory as the MBTiles cache.

And now, for the best new feature of TileCutter, the program does not store duplicate tiles. That is, if the area you are caching has a lot of empty tiles in the ocean etc, the MBTiles cache created by TileCutter will only store one tile for all those duplicated tile images. Should help save disk space 🙂

TileCutter can be downloaded here

TileCutter – A small utility to generate tile cache in the MBTiles format from ArcGIS Dynamic Map Services

Thought I would share a little utility I had written up to generate tile caches in the MBTiles format for ArcGIS Dynamic Map Services. The MBTiles cache format is very simple and makes moving caches between machines very easy since you just have to transfer one file instead of the thousands of files that need to be copied for normal tile caches. The TileCutter is a console utility and accepts the scale range and the extent in latitude/longitude for which the cache should be generated. It also takes a few other options listed below.

Options:
  -h, --help                 Show this message and exits
  -m, --mapservice=VALUE     Url of the ArcGIS Dynamic Map Service to be
                               cached
  -o, --output=VALUE         Location on disk where the tile cache will be
                               stored
  -z, --minz=VALUE           Minimum zoom scale at which to begin caching
  -Z, --maxz=VALUE           Maximum zoom scale at which to end caching
  -x, --minx=VALUE           Minimum X coordinate value of the extent to cache
  -y, --miny=VALUE           Minimum Y coordinate value of the extent to cache
  -X, --maxx=VALUE           Maximum X coordinate value of the extent to cache
  -Y, --maxy=VALUE           Maximum Y coordinate value of the extent to cache
  -p, --parallelops=VALUE    Limits the number of concurrent operations run
                               by TileCutter
  -r, --replace=VALUE        Delete existing tile cache MBTiles database if
                               already present and create a new one.

Example Usage:

To just try it out, just run TileCutter.exe, it will download tiles for some default extents from an ESRI sample server

TileCutter -z=7 -Z=10 -x=-95.844727 -y=35.978006 -X=-88.989258 -Y=40.563895 -o=”C:\LocalCache” -m=”http://sampleserver1.arcgisonline.com/ArcGIS/rest/services/Demographics/ESRI_Population_World/MapServer”

The TileCutter does request tiles in parallel threads and allows to you controls the number of concurrent operations. I don’t want to write too much about it yet since i am still working to add more capabilities to it. I am planning on providing the ability to generate MBTile caches for WMS services, OSM tiles etc in the future and more if there is interest. Also, planning on implementing  a way to avoid storing duplicate tiles(for example, empty tiles in the ocean etc). I just wanted to get the tool out there early to get feedback and guage interest. So, if you have queries/interest/feature requests, let me know 🙂

Also, I will blog about a little piece of code for a IHttpHandler that will serve up the MBTiles to web clients pretty soon. Stay tuned…

TileCutter can be download here.

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.

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.

Issues encountered and solved while building a comprehensive web-based map printing solution

Web-based map printing has been one of those problems that, so far, doesn’t have a COMPLETE solution that meets all the needs of the different users out there. We had created various solutions in the past to meet web-based printing needs on a per-project basis. But, we didn’t have one single comprehensive solution that was capable enough to meet all requirements regardless of the unique complexities involved in each of them. A little while ago, we set out to build one such comprehensive web-based map printing solution and ran into some issues along the way that I thought might be worth sharing here. I am not going to go into what we built here, but just the issues/oddities we encountered…

Here are the issues we faced while developing the printing component and some details into how we worked-around them.

• Printing token secure layers – This is a problem that we initially didn’t see coming because we were using the Silverlight client api to print. When using token layers, the client (which is the browser) requests a token from the GIS server using it’s IP address (or a web address) as the optional ClientID. When using a token generated with a ClientID, the AGS server checks for the origin of the request to confirm identity. So, when we tried to use the token generated by the client browser with it’s IP address as the ClientID in the server-side printing component, the requests were denied by AGS as it rightfully should since the server’s IP address doesn’t match the one in the token. We did not initially see this problem with Silverlight clients because, Silverlight clients currently request tokens without the optional ClientID. To work around this, we had to request token without the ClientID or had to spoof the Referrer in the HTTP request for the image.
• Max Image Size constraints – The size of map image requests that need to be made can get quite large depending on the size of the map on the print layout and also on the DPI required on the map print output. AGS has default max image size limits set to 2048 X 2048. Bing maps maximum image size is around ~800. Increasing the maximum image size limit in AGS will only take you so far. Eventually, your image size requests can be big enough (think plotter size) to either cause AGS to crash or just take an unacceptably long time to return. So, to work around this limitation, we had to resort to cutting the big image requests into a series reasonably sized tile requests. Once all the tiles to cover the big area arrive, the tiles can be stitched back together using GDI+ to produce a seamless big image that the map print layout needs.
• Bing logo – The above solution to split big image requests into works for AGS MapServices and WMS services, but Bing map layers add another twist to the problem. Image responses from Bing contain the Bing logo on the bottom right corner of the image. This caused the Bing logo to appear multiple times on the map when the numerous smaller tile images were stitched together. To solve this issue, we had to get special permission from Bing to access their tile images directly which do not have the Bing logo on them and stitch those together to produce the seamless image required.
• Custom legends – The swatches for legend image for an AGS MapService can be obtained pretty easily using the AGS SOAP API. But more custom work is needed to stitch together the swatch and legend text information from multiple map services. Also, to add to it, there was no easy way to generate swatches for graphics layers. So, we ended up writing a custom Server Object Extension (we call it LegendServer) exposed over SOAP that takes in the information needed to produce swatches for the graphics layers and produces swatch images. The legend service consumed the swatches information from the AGS MapService and the custom SOE and stitched them together into one legend image handling the font styles etc and wrapping as necessary. We still have the issues here that ArcObjects is not able to generate the swatches at the required DPI. For e.g. we can’t request for swatches in 300 DPI etc.
• Missing legend symbol markers in AGS – When writing the custom Server Object Extension described above to produce legend swatches, we discovered that ArcObjects doesn’t support triangle markers. But triangle markers were supported on the client-side APIs. So, to overcome that limitation, we can handle just the triangle markers as picture marker symbols and handle it with a special image service that produces a triangle image in the required dimensions, fill and border color.
• Overflowing legends – Sometimes the legend for a map just can’t fit on a single page. In those cases, we had to make sure that we build the legend in parts that can fit on the page and stick the overflowing legend into new pages as needed. The trick here is to not build one single legend image and chop it to overflow to the next page. Because, we decide to chop off the legend at an arbitrary height, we might end up chopping the text or swatch on the legend. So, we will have to build the legend in parts and then assemble them into the different pages.
• Printing Graphics layers – Printing graphics layers on the map turned out to be a little tricker than expected. We went down the path of rendering the graphic layers as PDF graphics on top of the map. It seemed to do everything we needed until we had to print polygons with holes in it. Then we used the AGS SOAP API to generate an image for the graphics on the map and overlay it on top of the map. We ended up pulling back that solution because that technique did not support transparency in graphics. So, eventually, we ended up writing a custom Server Object Extension (we call it GraphicsServer) that produces images from the graphics layer geometries and symbology respecting their transparency.
• Overview Map – Printing overview map doesn’t sound too complicated until you consider the fact that we might have a totally different set of layers on the overview map than we do on the map itself. Also, the overview map can be static or dynamic, meaning it can always be at the same extent (world extent for example) or it may have it’s extent set at levels that closely follow the extent of the map itself. Also, keep in mind that the overview map will also need to have a small rectangle graphic inside it that highlights to current extent of the map.
• Print Rendering – In most cases, we will want to have the option of being able to render to PDF or an image as the user requires. When implementing these renderers, please keep in mind that the co-ordinates axis for the PDF and the image GDI graphics are reversed. PDF is bottom-up and image GDI graphics is top-down.

I am pretty sure that I am leaving out some more. I will add them to the list above as and when I remember them. But I am happy to say that we did solve/work-around all the problems that came our way and have been re-using the printing component in various projects with great success. The printing component is also being used by all Silverlight, Flex and Javascript clients.

Please let me know, if you ran across other issues when you implemented your print feature or if you solved any of the issues above in a different way.