This project is read-only.

Introduction

Welcome to the Open Intel project on CodePlex. Open Intel is an accelerator for governments and private companies to rapidly build open data portals and killer business intelligence solutions with an emphasis on spatial analysis, data interaction and visualization. Developers can download the solution, build, and run it immediately in Visual Studio 2010 without having to purchase additional licensing. This is not a demo solution with limited functionality or a trial period. It is fully functional and ready for deployment or for your development team to pick up and extend. The out of the box experience uses datasets from the City of San Francisco hosted in SQL Azure.

Breakdown

There are three projects in the solution: One is the Silverlight application; the second, a web project to host the Silverlight control and provide various services to the client; and finally, an Azure web role for deployment. Because the entire solution including the geospatial and data services can be packaged in a single web role, deployment in Windows Azure is very simple and scalable. The only dependency to the web role and Azure environment is a SQL Azure database. Of course the solution may be deployed in standard non-cloud scenarios as well.

Data Model

All data is stored in SQL 2008 R2 (or SQL Azure). There is a set of tables used for OI application cataloging, metadata and sharing. The rest of the tables in the database store the actual datasets presented in the application. There is a single read-only user defined in the web.config of the web project and the Configuration.cs file in the Silverlight control project. Make sure you update those to point to your database instance.

 

Architecture

The OI architecture is based on MVVM using the Caliburn.Micro framework (CM). CM allowed for a clean and rapid development of the solution and laid the foundation for a very extensible product. The following screenshot shows how one of the more complex screens is composed. Please refer to CM on CodePlex for more information – especially the section on conventions.

 

Setup Steps

Developer Prerequisites

Visual Studio 2010 with C# (.NET 4)
Windows Azure Tools for Visual Studio - Version 1.3
Microsoft Silverlight 4 Tools for Visual Studio
MapDotNet UX 8 (www.mapdotnet.com) – Free Studio tool for map design
SQL Azure  or a local SQL Express instance

Step 1: Download and run the latest Open Intel source code with Visual Studio 2010

Get the latest source from CodePlex SVN or download a snapshot.
http://oi.codeplex.com/SourceControl/list/changesets


 
Open the solution in Visual Studio 2010.


 
Compile and run the application.  


 
! Note: when you download the solution from SVN, Open Intel consumes test data for San Francisco that ISC hosts in the cloud.  The following steps will prepare you to setup your own data and configurations.

Step 2: Provision a SQL Azure instance account

This is where all of your data for Open Intel will be stored.  Follow this link, Microsoft often has great introductory prices.

http://www.microsoft.com/en-us/sqlazure/purchase.aspx

For development, you can also use a local SQL or SQL Express database.  However, if you haven’t had a chance to check out SQL Azure yet, you should…it’s awesome!


 

Step 3: Configure SQL Azure

Setup a read-only account in SQL Azure.  This read-only account will be used by the application to access the database.

From the master database:
CREATE LOGIN readonlylogin WITH password=’??????’;

From your database:
CREATE USER readonlyuser FROM LOGIN readonlylogin;
EXEC sp_addrolemember 'db_datareader', 'readonlyuser'; 

For sharing, we need to give the readonlyuser insert access to the OI_Share table:
GRANT INSERT ON OI_Share TO readonlyuser

SQL Script for OI Metadata Tables

USE [MyDatabase] 

GO 

SET ANSI_PADDING ON 

GO 

CREATE TABLE [dbo].[OI_Jurisdiction](
                [ID] [int] IDENTITY(1,1) NOT NULL,
                [JurisdictionID] [nvarchar](256) NOT NULL,
                [Customer] [nvarchar](256) NULL,
                [TitleBlock] [nvarchar](512) NULL,
                [Country] [nvarchar](256) NULL,
                [State] [nvarchar](256) NULL,
                [StateCode] [char](2) NULL,
                [City] [nvarchar](256) NULL,
                [MashupMapID] [nvarchar](256) NULL,
                [CustomerImageURI] [nvarchar](1024) NULL,
CONSTRAINT [PrimaryKey_OI_Jurisdiction] PRIMARY KEY CLUSTERED 
(
  [ID] ASC
))

GO


CREATE TABLE [dbo].[OI_Category]( 

                        [ID] [int] IDENTITY(1,1) NOT NULL, 

                        [Name] [varchar](256) NULL, 

                        [JurisdictionID] [int] NOT NULL,

CONSTRAINT [PK_oi_category] PRIMARY KEY CLUSTERED 

( 

                        [ID] ASC 

)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) 

) 

GO 

SET ANSI_PADDING OFF 
GO 

SET ANSI_PADDING ON 
GO 

CREATE TABLE [dbo].[OI_Source]( 

			[ID] [int] IDENTITY(1,1) NOT NULL, 
			
			[Name] [varchar](256) NOT NULL, 

			[Filter] [varchar](256) NULL, 

			[JurisdictionID] [int] NOT NULL,
CONSTRAINT [PK_OGDI_Source] PRIMARY KEY CLUSTERED 
( 
[ID] ASC 
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) 
) 

GO 

SET ANSI_PADDING OFF 

GO 

CREATE TABLE [dbo].[OI_Apps]( 

                        [ID] [int] IDENTITY(1,1) NOT NULL, 

                        [ShareID] [uniqueidentifier] NOT NULL, 

                        [Title] [nvarchar](256) NULL, 

                        [Description] [nvarchar](2048) NULL, 

                        [ThumbnailURI] [nvarchar](512) NULL,
  
		      [JurisdictionID] [int] NOT NULL

[UriOverride] [nvarchar](512) NULL,

CONSTRAINT [PrimaryKey_9c50b766-ea9a-4a5b-8918-c0a5c7fc87c3] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ) GO CREATE TABLE [dbo].[OI_Share]( [ID] [uniqueidentifier] NOT NULL, [State] [nvarchar](max) NOT NULL, CONSTRAINT [PrimaryKey_0b5d55bc-61a4-4c1c-9f11-c056cf3f12c1] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ) GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[OI_Dataset]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](256) NULL, [Description] [varchar](1024) NULL, [ContactEmail] [varchar](256) NULL, [Tags] [varchar](256) NULL, [PublishedDate] [datetime] NULL, [UpdateFrequency] [varchar](64) NULL, [FK_CategoryID] [int] NOT NULL, [FK_SourceID] [int] NOT NULL, [TableName] [varchar](256) NULL, [IsPublished] [bit] NULL, [IsSpatial] [bit] NULL, [LayerID] [varchar](64) NULL, [JurisdictionID] [int] NOT NULL, CONSTRAINT [PK_oi_Dataset] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ) GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[OI_Dataset] WITH CHECK ADD CONSTRAINT [FK_oi_Dataset_oi_category] FOREIGN KEY([FK_CategoryID]) REFERENCES [dbo].[OI_Category] ([ID]) GO ALTER TABLE [dbo].[OI_Dataset] CHECK CONSTRAINT [FK_oi_Dataset_oi_category] GO GO ALTER TABLE [dbo].[OI_Dataset] WITH CHECK ADD CONSTRAINT [FK_oi_Dataset_oi_source] FOREIGN KEY([FK_SourceID]) REFERENCES [dbo].[OI_Source] ([ID]) GO ALTER TABLE [dbo].[OI_Dataset] CHECK CONSTRAINT [FK_oi_Dataset_oi_source] GO ALTER TABLE [dbo].[OI_Apps] WITH CHECK ADD CONSTRAINT [FK_JURIS_APP] FOREIGN KEY([jurisdictionID]) REFERENCES [dbo].[OI_Jurisdiction] ([ID]) GO ALTER TABLE [dbo].[OI_Apps] CHECK CONSTRAINT [FK_JURIS_APP] GO ALTER TABLE [dbo].[OI_Category] WITH CHECK ADD CONSTRAINT [FK_JURIS_CAT] FOREIGN KEY([jurisdictionID]) REFERENCES [dbo].[OI_Jurisdiction] ([ID]) GO ALTER TABLE [dbo].[OI_Category] CHECK CONSTRAINT [FK_JURIS_CAT] GO ALTER TABLE [dbo].[OI_Dataset] WITH CHECK ADD CONSTRAINT [FK_JURIS_DS] FOREIGN KEY([jurisdictionID]) REFERENCES [dbo].[OI_Jurisdiction] ([ID]) GO ALTER TABLE [dbo].[OI_Dataset] CHECK CONSTRAINT [FK_JURIS_DS] GO ALTER TABLE [dbo].[OI_Source] WITH CHECK ADD CONSTRAINT [FK_JURIS_SRS] FOREIGN KEY([jurisdictionID]) REFERENCES [dbo].[OI_Jurisdiction] ([ID]) GO ALTER TABLE [dbo].[OI_Source] CHECK CONSTRAINT [FK_JURIS_SRS] GO
Step 4: Download and Install MapDotNet UX 8.1


Register with www.mapdotnet.com and download the latest version of MapDotNet UX v 8.1.  The default installation directory for MapDotNet is ‘C:\Program Files\MapDotNet’.  Check it out, there’s lots of great stuff in there. 

MapDotNet UX Studio is a utility for configuring your map and importing/exporting data to SQL Azure can be launched from the start menu.

Step 5: Use MapDotNet UX Studio to import spatial data into SQL Azure

MapDotNet UX Studio can be used to import data from common geospatial formats such as, Shapefiles, KML, ArcSDE, Oracle Spatial or PostGIS into SQL Azure.  To import data follow these simple steps.

  1. Launch MapDotNet UX Studio from the Start Menu
  2. Add a connection to your local spatial data
  3. Add a connection to your SQL Azure database (Note: You’ll want to use your administrator username and password or an account that has write access  for this connection string)
  4. Right click on the source layer and choose ‘Export Data’
  5. In the ‘Export Data’ dialog box, specify your target database (SQL Azure) Note: You’ll want to reproject your data going into SQL Azure to ‘Popular Spherical Mercator’.   Popular Spherical Mercator is the projection used by Bing Maps.
  6. Click ‘Start’ Note: When you use MapDotNet UX Studio to import your spatial data into SQL Azure, it will setup the appropriate spatial indexes for you. (That will help with querying and rendering performance)
  7. Once you have one or more datasets loaded into SQL Azure, you can proceed to Step 6.
  8. If you’d like to load datasets that don’t have a spatial component, you can use the standard SQL Server Management Studio tools or the SQL Migration wizard: http://sqlazuremw.codeplex.com/ 

Step 6: Use MapDotNet UX Studio to Design your MapFiles

Once you’ve loaded your datasets into SQL Azure, you’re ready to design your map configurations and register the layers with the Open Intel container.  In the .sln under the ‘\Content\UXServiceMapFiles’ folder of the OI.Web project there are two files ‘OIMashupSample.mapx’ and ‘OIMashup.mapx’ for configuring your map cartography and database connections.   The ‘OIMashupSample.mapx’ contains the sample San Francisco data.  The ‘OIMashup.mapx’ is a clean map file that you’ll want to add your spatial layer configurations to.

Each spatial dataset has a single layer in the map with a layer ID listed in the OI_Dataset table. The map file is contained on the solution and is therefore read-only once deployed in the cloud. If you want to use Azure blob storage to store your map configurations and allow for editing while in production, you will need to configure your MapDotNet service to use Azure blob storage. Out-of-the-box however, you will modify the ‘OIMashup.mapx’ map locally and publish the entire package to Azure.

  1. Run the OGDI solution in Visual Studio
  2. Open MapDotNet UX Studio and add a reference in the server explorer (e.g. http://localhost/OI.Web/MDNUXServices)
  3. Edit OIMashup.mapx in UX Studio and save your changes
  4. Deploy a new version of OI to the cloud (publish)
  5. Update the OI_Dataset table using the OI Manager.  See Step 7 for details on using the OI Manager.

 

Step 7: Use the management UI to configure your datasets and publish them in the OI container.

  1. To access the OI Manager, navigate to this URL:  http://localhost/OI.Web/OIManager.aspx
  2. The app will ask you to enter a SQL Connection string.  You should enter a SQL connection string with write privileges, this will authenticate you and allow updates to the dataset configurations.
  3. On the ‘Setup Catalog’ tab, you can add and new dataset configuration or update and existing on.
  4. On the ‘Setup Apps’ tab, you can add a GUID from a created mashup.

Step 8: Compile your solution and deploy it to Windows Azure

Step 9: Advanced map file configurations

Layer Metadata

The OI application uses key/value pairs defined in the map file for configuring a clean column names for the data stored in SQL Azure.  In MapDotNet UX Studio, you can define the following key/value pairs for each layer.

LayerName – This is the friendly name of the layer that will be displayed in the Mashup layer/legend list. If LayerName is not defined, OI will use the default name of the layer provided in the OIMashup map file.

 


DrilldownHeaders – These are the friendly column header names that will be displayed in the Mashup data grid and drilldown callout.  Multiple values should be comma separated. DrilldownHeaders must have an accompanying DrilldownColumns entry. 


DrilldownColumns – These are the actual column names in the data table. Multiple values should be comma separated.  DrilldownColumns must have an accompanying DrilldownHeaders entry.  Only defined columns will be visual to the end user on a query.

 

HyperlinkColumns – [column 1],[display text 1]$[column 2],[display text 2]$ ... layer level metadata specifying the columns which will be displayed as hyperlinks in the data grid along with their hyperlink text.  If you have defined clean columns names using DrilldownHeaders then use those names as the hyperlink column name.

SearchColumns – These are the columns that the end user will be able to search by in the Mashup view.

 

Filter Metadata

For each layer in your map file you may optionally set up metadata to define custom filters. These filters show up in the right-click menu on a layer in the mash-up legend panel. The metadata key is Filters and the value is formatted as follows: [name],[xor group],[where clause],[enabled flag],[optional parameter]$ ... There are five comma-delimited fields per record and each record is dollar sign delimited. There is also a special placeholder string for the where clause {NOW} which will be replaced with a simple date string (using today). The optional parameter can be used to add an integer number of days to the date. For example, the filter record: show only past 10 days,dategroup,issue_date>{NOW},true,-10 would be the script for a hypothetical filter labeled "show only past 10 days" and would get selected exclusively against other "dategroup" filters using a where clause with the field "issue_date". The where clause would get translated at runtime to be the current day minus 10 days. This filter would initially be enabled at start-up (only one in a given xor group should be flagged "true").

You can also append a custom filter via the query string by using the following syntax: &filterLayerID=[your layer id]&filterMetadata=[filter string using the above listed format]. Note, only one filter and one layer is supported. All other filters on the specified layer will be initially disabled allowing this filter to override their initial state.

Density Mapping Metadata

It's very easy to add density mapping support to a layer in your map file. The metadata key is DensityMapping and the value is a comma-delimited string formatted as follows: [column name],[operation],[is visible]... The column name attribute defines which column in the Layer's underlying table is used in the density mapping computation. For counts, this may be set to the table's primary key. The operation attribute may be either "count" or "sum". If count is used, the density computation merely counts features in each grid cell. If the operation is sum, then the values for each record found in the "column name" column are summed. The final attribute specifies whether density mapping is turned on initially for the layer or not.

GeoRSS Feed Metadata

To add a GeoRSS feed to a dataset, simply add an RSSEnable metadata key to your layer metadata. The value is formatted as follows: [id column],[datetime column],[title column],[content column],[uri column],[feed record count]...The id column (required) is a unique identifier for the record. The datetime column (required) is the feed item last update timestamp. The feed record count (required) defines how many records are returned in the feed at one time. The rest of the fields are optional.

Other Metadata

DrilldownPhotoColumn - layer level metadata specifies a column in the drilldown that contains an image reference
DrilldownIgnore - layer level metadata specifies this layer is not included in a drilldown identify
HideLayersOutOfScale - map level metadata specifies legend will hide all layers out of scale (instead of display not available icon)
SuppressDataDownload - map level metadata specifies data download is disabled

Additional Notes

  • All spatial data must be in the Spherical Mercator projection. It is recommended you use MapDotNet UX Studio to set up your spatial tables so the proper spatial index and constraints are created.
  • When you download the solution and run it locally you are connecting to a SQL Azure data source over the internet. All map rendering will be substantially slower as you download all geometry for each tile to your local machine. The solution will run much faster when you update the data source connection to an instance running on your local network switch or when you deploy to Azure with your SQL Azure instance in the same affinity group.
  • In the web project the SQL2008 connector is a content item at the root copied to the bin folder. This is necessary because MapDotNet dynamically loads its data connectors and we always need it at /bin – but there is no reference to it in the project references.  
  • Steps to setup .mapx storage in Azure Storage

    1. Create an Azure Storage account
    2. Create a folder/container in Azure Storage call “mapfiles”
    3. Add a reference to ISC.MapDotNetServer.Core.Services.AzureBlobStorage.dll to OI.Web projects
      1. A copy of ISC.MapDotNetServer.Core.Services.AzureBlobStorage.dll can be found here: $:\Program Files\MapDotNet\8.1\UXSDK
    4. Update the following items in the OI web.config

      1. Uncomment the tags pertaining to the ‘Azure Blob Storage Support’
      2. Configure these two values:

                             <add key="AzureStorageFactoryAccountName" value="???"/>
                             <add key="AzureStorageFactoryAccessKey" value="???????...” />
                   
    3.   Comment the following lines:  
                              <!--
    <add key="IStorageFactoryAssemblyName" value="ISC.MapDotNetServer.Common" /><add key="IStorageFactoryClassName" value="ISC.MapDotNetServer.Common.NTFSStorageFactory" />-->

         
    5.  Copy the files located in “[project_location]\OI.Web\Content\UXServiceMapFiles” to the new “mapfiles” container. This includes any .mapx files and the mapfiles.xml document.

  • Last edited Jul 15, 2013 at 6:24 PM by benton, version 31

    Comments

    brianhearn Feb 24, 2011 at 10:12 PM 
    Docs are looking good!