Visualization on the distribution map of votes in Moscow in the presidential elections of 2018

Introduction

Elections are an extremely mysterious process, when viewing the values ​​of the results of which the overall picture is not quite clear. I decided to show them on a map of Moscow with division by regions using InterSystems technologies, which provide both data storage and analysis. In this case, the platform was used to integrate and develop InterSystems Ensemble applications, but you could equally well deploy the solution described below on the multi-model InterSystems Caché DBMS and on the new InterSystems IRIS Data Platform product.

Stages:

To display the map, we will perform the following actions:

• collection of voting data that will be displayed on the map;
• bringing the collected data to the required format;
• creating persistent classes and filling them out;
• creating OLAP-cube;
• making pivot (s);
• create and configure dashboards;
• installation of the MDX2JSON and DeepSeeWeb projects;
• collection of polygon coordinates;
• creating thermlists and adding controls.

Data collection

To display the presidential election data, we need a data model.
Create the following classes:

• map.MoscowElections2018 - class with election results data
• map.MoscowRegion - a directory of districts of Moscow to which objects of the class map.MoscowElections2018 will refer

Let's start by creating the map.MoscowRegion class.
Take data from districts and administrative districts from Wikipedia pages, create an XML-enabled class for this data
')

And we give the data to the format of XML


I turned to the website of the Central Election Commission of the Russian Federation for these elections. Unfortunately, I did not find a normal API with the ability to get a suitable data set, so I had to take everything from the summary table of results. There, with the help of filters, one can find the results of the election not only of the President of the Russian Federation, but also the election of deputies and heads of various administrative units of the Russian Federation. Since we are interested in the presidential elections, we find a suitable summary table of election results in Moscow, upload, transform to the appropriate format (for this example, the XML format was chosen, since this format can be easily used to import into our database under the control of the InterSystems platform ).
As a result, we have approximately the following data format:


Created for data XML-enabled class map.MoscowElections2018 on the InterSystems platform:

Creating a cube

To create an OLAP cube, we use the DeepSee Architect web application, to go to it, open the System Management Portal → DeepSee → Select Area → Architect.
In that case, if you do not see your area in the list of available DeepSee areas, go to the System Management Portal → Menu → Web Application Management → / csp / area, and in the “Enabled” field, check the “DeepSee” box and click the save button. After that, the selected area should appear in the list of available DeepSee areas.
Create a new cube.

Clicking on the button “Create” we get to the screen of creating a new cube, there it is necessary to set the following parameters:

• Cube name - the name of the cube used in queries to it
• Display Name - localized cube name (translation is performed by standard InterSystems mechanisms )
• Cube source - use a fact table or another cube as a data source
• Initial class - if in the previous step a class was selected, then we specify the class map.MoscowElections2018 as the fact table.
• The class name for a cube is the name of the class in which the cube definition will be stored. Automatically generated
• Class description - arbitrary description

This is what our new cube looks like:

We define the properties of the cube.

After clicking OK, a new cube will be created:



On the left, the properties of the base and related by the “snowflake” classes are displayed, which can be used when constructing a cube.

The center of the screen is the skeleton of the cube. It can be filled with class properties using drag-n-drop from the base class area, or by adding elements manually. The main elements of the cube are measurements, indicators and lists.

Dimensions (Dimensions)

Dimensions are cube elements that group fact table entries. In the measurement, the “quality” attributes of the base class are usually referred to, which break all the records of the fact table according to one or another cut.

For example, we would like to group all the facts by the municipality and, if deepened, by the district.

Territory property is perfect for splitting facts by territory. Since our hierarchy begins with a municipality, then the first level should have the dimension of the parent of the region, that is, we click the arrow on the region property in the left list, in the drop-down list we also expand the parent property and drag the name to the measurement region — as a result The architect adds a dimension to the cube with one H1 hierarchy and one name level. for convenience, rename the dimension from name to Territory and the first level in Region.

Measurements besides grouping allow you to build hierarchies of nesting of facts from the general to the particular. To do this, add the Subregion level by dragging the region name onto the H1 hierarchy, add the population and name properties to both dimensions and also while the coordsKey magic property with the dimension name value - this property will be used to search for coordinates corresponding to this municipality / region for highlighting on the map. We indicate the displayed names in the signatures to the measurement and level.

Indicators (Measures)

Indicators or metrics are such elements of the cube, to which any “quantitative” data are to be counted, which must be calculated for the “qualitative” dimensions of the cube (Dimensions).

For example, in the fact table such indicators can be properties:

• population (population),
• validBallots (Number of valid ballots),
• votersIncludedInVotersList (Number of Voters),
• separate indicators for each of the eight
• Baburin (Baburin SN),
• Grudinin (Grudinin Mon),
• Putin (VV Putin),
• Sobchak (Sobchak KA),
• Suraykin (Suraikin MA),
• Titov (Titov BYu),
• Yavlinsky (Yavlinsky GA),
• Zhirinovsky (Zhirinovsky explosives).

Drag each property to the area of ​​indicators and create a numeric indicator of the Integer type with the function SUM, which will count the total number of votes in the current slice. Also for each indicator we will indicate the displayed name.

Compilation cube

So, we added eight indicators to the cube, one measurement is enough and you can already see what happened.

Compile the cube class (button "Compile"). If there are no compilation errors, then the cube is created correctly and you can fill it with data.

To do this, click “Build a cube” - as a result, DeepSee will load data from the fact table into the cube data store.

To work with the cube data we need another web application - DeepSee Analyzer.

Pivot table construction (Pivot)

DeepSee Analyzer is a visual tool for directly analyzing cube data and preparing data sources for further visualization. To go to DeepSee Analyzer, open the System Management Portal → DeepSee → Area Selection → Analyzer, you can also go from the cube creation page by clicking on the “Tools” tab in the left pane and then on the “Analyzer” button. The Analyzer working window opens.

In the working analyzer window on the left we see the elements of the created cube: indicators and measurements. Combining them, we build queries to the cube in MDX , an analogue of the SQL language for multidimensional OLAP cubes.

To create a pivot table, drag the Baburin SN, Grudinin PN, Putin VV, Sobchak KA, Suraykin MA, Titov BYu, Yavlinsky GA, Zhirinovsky VV to the field of measurement columns. Indicator choose "Administrative District". As a result, we will get a table of the number of administrative districts with the possibility of deepening (DrillDown - the transition through the measurement hierarchy from the general to the particular) to the districts.



In the Analyzer, double clicking on the dimension heading leads to the next dimension in the hierarchy (DrillDown). In this case, a double click on the administrative district will lead to the transition to the areas of this administrative district. As a result, you can see how many votes were cast by region.

Also, to highlight certain AO / districts, we need an indicator by which the color of this polygon will be calculated - this value will be taken from the column with specials. Name ColorHSLValue (link to instructions for setting up widgets using maps is at the end of the article) - the higher the value, the closer the color will be to red and green in the opposite case. To derive the name of the polygon when you hover the cursor on it uses special. the name of the TooltipValue column. For the text in the pop-up window that appears when you click on a particular polygon, use the dimension name PopupValue, it is possible to use html to mark the value.

These specials. we will create fields as calculated values. To do this, click on the button with a calculator in the left menu. , select the element type, the name of the dimension, the name of the element and the expression by which the value of this element will be calculated.

List of special fields for maps

ColorHSLValue:
Item Type: Indicator
Item Name: ColorHSLValue
Type: Number
Description: Determines the color of the polygon from green to red depending on the value.
Expression:

 [Measures].[attandance]/[Measures].[votersIncludedInVotersList] 

is the number of voters who came to the polls is the result of the ratio of the number of voters in a given AO / district, so the higher this proportion is, the closer the color is to red.

PopupValue:
Item Type: Dimension
Dimension Name: custom
Item Name: PopupValue
Type: String
Description: Determines the color of the polygon from green to red depending on the value.
Expression:

 "<b>" + [Territory].[H1].[Region].CurrentMember.Properties("name") + "</b><br/>: " + [Measures].[Population] + " .<br/> : " + [Measures].[votersIncludedInVotersList] + "." 

the name of the given AO / region, population, number of voters who voted will be displayed

TooltipValue:
Item Type: Dimension
Dimension Name: custom
Item Name: TooltipValue
Type: String
Description: Specifies the message that appears when you hover over a polygon.
Expression:

 [Territory].[H1].[Region].CurrentMember.Properties("name") 

And after adding these pivot columns, it looks like this:



Save this pivot as Moscow Elections / mainPivot2018.

We will also create another pivot with information on all elected candidates for breeding as pay-char.

Transfer the indicators of all candidates to the "Indicators" column and select in the parameters of indicators - “Place indicators on” - “lines”



Save the resulting pivot as Moscow Elections / countPivot2018 and proceed to the creation of the display panel

Building a panel of indicators (Dashboard)

User Portal is a web application for creating and using dashboards ( indicator panels ). Dashboards contain widgets: tables, graphs and maps based on summary tables created by analysts in the Analyzer.

To go to the DeepSee User Portal, open the System Management Portal → DeepSee → Select Region → User Portal.



Create a new dashboard by clicking on the arrow on the right → add → add display panel.



Create three widgets.

To create, click on the arrow on the right → Widgets → "+" → select the type of widget in the left list → data source and the name of the widget:

Map widget:

Type of widget - Map → Map
Data source - MoscowElections / mainPivot2018
The widget name is moscowElectionsMap

Tabular widget with map data:

Widget type - Pivot tables and charts → Table
Link to - moscowElectionsMap
The widget name is tableWidget

Pay chart widget with data of candidates:

Widget Type - Pivot Tables & Charts → Pie Chart
Data source - MoscowElections / countPivot2018

For the second and third widgets you need to create controls. For a table widget, the control with which we will determine which of the incoming columns we will display (I recall that in the pivot we defined special columns that are needed for the map, but which should not be displayed in the table widget). For the third - the filter by AO / areas, the value of which will be set automatically when you click on any AO / area. You can do this as follows - click the arrow on the right → Widgets → tableWidget → Controls → "+"

Location - Widget
Target - tableWidget
Action - Install specials. column
Type - hidden

After confirming the creation of the control - we define the displayed columns - the required format is MDX

 {[Measures].[Baburin],[Measures].[Grudinin],[Measures].[Zhirinovsky],[Measures].[Putin],[Measures].[Sobchak],[Measures].[Suraykin],[Measures].[Titov],[Measures].[Yavlinsky]} 

We will create the widget control for the widget in the map widget.

Location - Click
Target - Widget3
Action - Apply Filter

After that, save the dashboard.

Installing MDX2JSON and DeepSeeWeb

To visualize the created dashboard, you can use the following OpenSource solutions:

• MDX2JSON - REST API provides information about cubes, pivots, dashboards and many other DeepSee elements, in particular, the results of MDX queries, which allows you to embed the user interface of the analytical solution on the DeepSee into any modern Web or mobile application.
• DeepSeeWeb - AngularJS application that provides an alternative implementation of the DeepSee user portal. Can be easily customized. Uses MDX2JSON as a backend.

Installing MDX2JSON

To install MDX2JSON:

1. Download Installer.xml and import it to any area using Studio, System Management Portal or Do $ System.OBJ.Load (file).
2. Run in terminal (user with% ALL role): Do ## class (MDX2JSON.Installer) .setup ()

To check the installation, you need to open the page http://server:port/MDX2JSON/Test?Debug in the browser. You may need to enter a username and password (depending on server security settings). A server page should open. If you get an error, you can read on the Readme and Wiki .

Install DeepSeeWeb

To install DeepSeeWeb you need:

1. Download the installer and import it to any area using Studio, System Management Portal or Do $ System.OBJ.Load (file).
2. Run in terminal (user with% ALL role): Do ## class (DSW.Installer) .setup ()

To check the installation, open the browser page http://server:port/dsw/index.html . The login page should open. The SAMPLES area contains many ready-made dashboards and all of them are automatically displayed in DeepSeeWeb.

Collection of polygon coordinates

To display the collected data on the map, we need a set of coordinates of polygons of administrative districts and districts of Moscow. You can take them for example on the gis-lab website. Further, since this data will be used by DeepSeeWeb, we need to bring them to a format that DSW can process:


Let's save the received js file in the web application folder of our area and voila.

Creating a list of terms

We can also add the ability to determine the coloring criteria of the map. if at us now it is colored according to the relation of the total to the population of the AO / district, we can make a coloring by the ratio of votes for the candidate to the population. You can do this as follows - we will create a list of terms - a list of key-value pairs; when a key is selected, the value will be inserted into the MDX query expression. The mdx expression of our pivot is as follows:

 SELECT NON EMPTY {[Measures].[Putin],[Measures].[Zhirinovsky],[Measures].[Baburin],[Measures].[Grudinin],[Measures].[Sobchak],[Measures].[Suraykin],[Measures].[Titov],[Measures].[Yavlinsky],[MEASURES].[COLORRGBVALUE],[CUSTOM].[TooltipValue],[CUSTOM].[PopupValue]} ON 0,NON EMPTY [Territory].[H1].[Region].Members ON 1 FROM [ELECTIONS2018CUBE] 

Replaced will be the part that is surrounded by curly braces.

You can create a list of terms in the Manager of the list of terms - open the user portal → Tools → Manager of the list of terms

For Putin will be the next line

Key: 2. Putin
Value:

 {[Measures].[Putin]\,%LABEL([Measures].[Putin]/[Measures].[votersIncludedInVotersList]\,"ColorRGBValue")\,[CUSTOM].[TooltipValue]\,%LABEL("<b>" + [Territory].[H1].[Region].CurrentMember.Properties("name") + "</b><br/>: " + [Measures].[Population] + " .<br/> : " + [Measures].[votersIncludedInVotersList] + " .<br/>  : " + [Measures].[Putin] + "."\,"PopupValue")} 

Let's keep this list of terms as Moscow Elections2018.

Next, create a control on the dashboard settings page for a pivot with a map:

Location - Mouse Click
Action - Select specials. lines
Click "OK"

We define a name and a list of specials. columns
—
. — MoscowElections2018.termlist

Done!

: URL -

, DeepSeeWeb , () — https://{ac}.tile.openstreetmap.org/{z}/{x}/{y}.png .

, . . .

:

1. json DeepSeeWeb , DeepSeeWeb
2. “app.tileServer” — url
3. json

json DeepSeeWeb

json . DeepSeeWeb — DeepSeeWeb.



“Export settings”. json «tileServer» «app». URL OSM Wikipedia Maps — https://maps.wikimedia.org/osm-intl/{z}/{x}/{y}.png . . DeepSeeWeb json DeepSeeWeb. Done!

Findings:

InterSystems Caché, OLAP DeepSee, DeepSeeWeb, MDX2JSON. , , , InterSystems Ensemble, InterSystems Caché InterSystems IRIS Data Platform.

:

→

References:

• InterSystems IRIS Data platform
• DeepSeeWeb
• DeepSeeWeb
• MDX2JSON
• gis-lab
• OSM