Visualization on the map of the distribution of votes in Moscow at the presidential election 2018

  • Tutorial

Introduction


Elections are an extremely mysterious process, when viewing the values ​​of the results of which the overall picture is not entirely clear. I decided to show them on a map of Moscow divided by regions using InterSystems technologies, which provide both storage and data analysis. In this case, we used the platform for integration and development of InterSystems Ensemble applications, but with the same success you can deploy the solution described below both on the InterSystems Caché multimodel DBMS and on the new InterSystems IRIS Data Platform product.

Stages:


To show the map, we will perform the following steps:

  • collecting voting data that will be displayed on the map;
  • bringing the collected data to the required format;
  • creation of persistent classes and their filling;
  • creating an OLAP cube;
  • creating pivot (s);
  • creating and configuring a dashboard;
  • installation of the MDX2JSON and DeepSeeWeb projects;
  • collection of polygon coordinates;
  • create thermists and add a control.

Data collection


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

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

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

map.MoscowRegion
Class map.MoscowRegion Extends (%Persistent, %XML.Adaptor)
{
Index idIndex On id [ IdKey, PrimaryKey, Unique ];
/// Код
Property id As %String [ Required ];
/// Название админ. единицы
Property name As %String(MAXLEN = 250);
/// Площадь, га
Property area As %Float;
/// Население
Property population As %Integer;
/// Название вышестоящей адм. единицы
Property parentName As %String;
/// Вышестоящая адм. единица
Property parent As map.MoscowRegion;
/// Вышестоящая адм. единица
Property parentId As %Integer;
ClassMethod populateRegions() As %Status
{
	#dim sc As %Status = $$$OK
	#dim stream As %Stream.Object = ##class(%Dictionary.CompiledXData).%OpenId(..%ClassName(1) _ "||" _ "regions").Data
	#dim reader As %XML.Reader = ##class(%XML.Reader).%New()
	set sc = ..%KillExtent()
	if $$$ISERR(sc) quit sc
	set sc = reader.OpenStream(stream, "literal")
	if $$$ISERR(sc) quit sc
	do reader.Correlate("region", ..%ClassName(1))
	#dim obj As map.MoscowRegion
	while reader.Next(.obj, .sc)
	{
		if $$$ISERR(sc) quit
		if (obj.parentId) {
			set obj.parent = ..%OpenId(obj.parentId)
		}
		set sc = obj.%Save()
		if $$$ISERR(sc) quit
		set obj = ""
	}
	quit sc
}
XData regions
{
}
}


And we will bring the received data to the XML format

XData from map.MoscowRegion
Центральный округ-66.18
        7696301001
           ...
    поселение ЩербинкаНовомосковский административный округ7.62
        4750421461012


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

XML selection data
район Богородское201365519588000380561326194181326380035383879100356473820602783316023006681234
    ...
    поселение Сосенское2142103818861450631921123218291123631397966346610439872331104719218603457341063


For the XML-enabled data, I created the map.MoscowElections2018 class on the InterSystems platform:

Class code map.MoscowElections2018
Class map.MoscowElections2018 Extends (%Persistent, %XML.Adaptor)
{
/// Ссылка на регион
Property region As map.MoscowRegion;
/// Имя региона
Property regionName As %String;
/// ID региона
Property regionId As %Integer;
/// Число избирателей, включенных в список избирателей
Property votersIncludedInVotersList As %Integer;
/// Число избирательных бюллетеней, полученных участковой избирательной комиссией
Property ballotsReceivedByPrecinctElectionCommission As %Integer;
/// Число избирательных бюллетеней, выданных избирателям, проголосовавшим досрочно
Property ballotsIssuedToVotersWhoVotedEarly As %Integer;
/// Число избирательных бюллетеней, выданных в помещении для голосования в день голосования
Property ballotsIssuedInPollingStationOnElectionDay As %Integer;
/// Число избирательных бюллетеней, выданных вне помещения для голосования в день голосования
Property ballotsIssuedOutsidePollingStationOnElectionDay As %Integer;
/// Число погашенных избирательных бюллетеней
Property canceledBallots As %Integer;
/// Число избирательных бюллетеней в переносных ящиках для голосования
Property ballotsInMobileBallotBoxes As %Integer;
/// Число бюллетеней в стационарных ящиках для голосования
Property ballotsInStationaryBallotBoxes As %Integer;
/// Число недействительных избирательных бюллетеней
Property invalidBallots As %Integer;
/// Число действительных избирательных бюллетеней
Property validBallots As %Integer;
/// Число утраченных избирательных бюллетеней
Property lostBallots As %Integer;
/// Число избирательных бюллетеней, не учтенных при получении
Property ballotsNotRecorded As %Integer;
/// Бабурин Сергей Николаевич
Property Baburin As %Integer;
/// Грудинин Павел Николаевич
Property Grudinin As %Integer;
/// Жириновский Владимир Вольфович
Property Zhirinovsky As %Integer;
/// Путин Владимир Владимирович
Property Putin As %Integer;
/// Собчак Ксения Анатольевна
Property Sobchak As %Integer;
/// Сурайкин Максим Александрович
Property Suraykin As %Integer;
/// Титов Борис Юрьевич
Property Titov As %Integer;
/// Явлинский Григорий Алексеевич
Property Yavlinsky As %Integer;
ClassMethod populateElectionsData() As %Status
{
	#dim sc As %Status = $$$OK
	#dim stream As %Stream.Object = ##class(%Dictionary.CompiledXData).%OpenId(..%ClassName(1) _ "||" _ "elections").Data
	#dim reader As %XML.Reader = ##class(%XML.Reader).%New()
	set sc = ..%KillExtent()
	if $$$ISERR(sc) quit sc
	set sc = reader.OpenStream(stream, "literal")
	if $$$ISERR(sc) quit sc
	do reader.Correlate("region", ..%ClassName(1))
	#dim obj As map.MoscowElections2018
	while reader.Next(.obj, .sc)
	{
		if $$$ISERR(sc) quit
		if (obj.regionId) {
			set obj.region = ##class(map.MoscowRegion).%OpenId(obj.regionId)
		}
		set sc = obj.%Save()
		if $$$ISERR(sc) quit
		set obj = ""
	}
	quit sc
}
XData elections
{
}
}


Cube creation


To create an OLAP cube, we use the DeepSee Architect web application , to switch to it, open the System Management Portal → DeepSee → Region Selection → Architect.
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 there, in the “Enabled” box, 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.

By clicking on the “Create” button, we get to the screen for 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 - the localized name of the cube (translation is carried out by standard InterSystems mechanisms )
  • Cube source - use a fact table or another cube as a data source
  • Source class - if a class was selected in the previous step, then we specify the map.MoscowElections2018 class as a fact table.
  • The class name for the cube is the name of the class in which the cube definition will be stored. Automatically generated
  • Class Description - Custom Description

Here's what our new cube looks like:



Define the properties of the cube.


After pressing the OK button, a new cube will be created:



On the left, the properties of the base and the classes connected with it by the “snowflake” are displayed, which can be used to construct the cube.

The central part of the screen is the skeleton of a 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 a cube are dimensions, measures, and lists.

Dimensions


Dimensions are cube elements that group fact table entries. Measurements usually include “quality” attributes of the base class, which break down all records in the fact table into one or another section.

For example, we would like to group all the facts by Municipal Formation and, when deepening, by region.

The Territory property is perfect for breaking down facts by territorial affiliation. Since our hierarchy begins with a municipality, the first level should be the measurement of the region’s parent, that is - we click on the arrow of the region property in the left list, in the drop-down list we also open the parent property and drag name to the measurement area - as a result The architect will add the dimension name to the cube with one H1 hierarchy and one level name. For convenience, rename the dimension from name to Territory and the first level to Region.

In addition to grouping, measurements make it possible to build hierarchies of nesting of facts from general to particular. To do this, add the Subregion level by dragging the name of the region onto the H1 hierarchy, add the population and name properties to both dimensions, and also the coordsKey magic property with the measurement name value - this property will be used to search for coordinates corresponding to the given municipality / region of the polygon for highlighting on the map. Specify the displayed names in the captions for the dimension and level.



Indicators (Measures)


Indicators or metrics are those elements of the cube that include any "quantitative" data that must be calculated for the "qualitative" dimensions of the cube (Dimensions).

For example, in a fact table, such indicators may be properties:

  • population
  • validBallots (Number of valid ballots)
  • votersIncludedInVotersList (Number of voters),
  • individual indicators for each of the eight condates
  • Baburin (Baburin CH),
  • Grudinin (Grudinin PN),
  • Putin (Putin VV),
  • Sobchak (Sobchak KA),
  • Suraykin (Suraykin MA),
  • Titov (Titov BY)
  • Yavlinsky (Yavlinsky GA),
  • Zhirinovsky (Zhirinovsky VV).

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



Cube compilation


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

Compile the cube class (“Compile” button). 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 the data from the fact table into the cube data warehouse.

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

Building a PivotTable (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 → Select Area → Analyzer, you can also go to the cube creation page by clicking on the "Tools" tab in the left panel and then on the "Analyzer" button. The analyzer popup window opens.

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

To create a pivot table, we drag Baburin SN, Grudinin PN, Putin VV, Sobchak KA, Suraikin MA, Titov BY, Yavlinsky GA, Zhirinovsky VV in the column of measurement columns. The indicator will choose "Administrative district". As a result, we get a table of quantities for the administrative district with the possibility of deepening (DrillDown - the transition according to the hierarchy of measurement from general to particular) to the districts.



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

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. by the name ColorHSLValue (a link to instructions for setting widgets using cards is given 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 display the name of the polygon when you hover over it, a special is used. column name TooltipValue. For the text in the pop-up window that appears when you click on a specific polygon, use the name of the PopupValue dimension, it is possible to use html to mark the value.

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

List of special fields for maps


ColorHSLValue:
Element type: Indicator
Element name: ColorHSLValue
Type: Number
Description: Defines 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 as a result of the ratio of the number of voters in a given AO / district, so the higher the proportion, the closer the color to red.

PopupValue:
Element type: Dimension Dimension
name: custom
Element name: PopupValue
Type: String
Description: Defines the color of the polygon from green to red, depending on the value.
Expression:

"" + [Territory].[H1].[Region].CurrentMember.Properties("name") + "
Население: " + [Measures].[Population] + " чел.
Число избирателей: " + [Measures].[votersIncludedInVotersList] + "чел."

the name of the given AO / region, population, the number of voters voted

TooltipValue:
Element type: Measurement
Name of measurement: custom
Element name: TooltipValue
Type: String
Description: Defines the message that appears when you hover over the polygon.
Expression:

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

And after adding these columns, the pivot looks as follows:



Save this pivot as MoscowElections / mainPivot2018.

We will also create another pivot with information on all the selected candidates for withdrawal in the form of a pay-char.

We transfer the indicators of all candidates to the “Indicators” column and select “Parameters on” - “lines” in the parameters parameters .



We will save the resulting pivot as MoscowElections / countPivot2018 and proceed to creating the indicator panel

Building a dashboard


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

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



Create a new dashboard by clicking on the arrow on the right → add → Add indicator 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 widget name:

Widget with map:

Widget type - Map → Map
Data source - MoscowElections / mainPivot2018
Widget name - moscowElectionsMap

Table widget with data from the map:

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

Pay-chart widget with candidate data:

Widget type - Pivot tables and charts → Pie chart
Data source - MoscowElections / countPivot2018

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

Location - Widget
Purpose - tableWidget
Action - Set special.
Type column - hidden

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

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



We will create a control for the pay-char of the widget in the widget-map.

Location - Mouse click
Purpose - Widget 3
Action - Apply filter

After that, save the dashboard.

Install MDX2JSON and DeepSeeWeb


The following OpenSource solutions can be used to visualize the created dashboard:

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

Install MDX2JSON


To install MDX2JSON you need:

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

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

Install DeepSeeWeb


To install DeepSeeWeb you need:

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

To verify the installation, you need to open the page in the browser http://server:port/dsw/index.html. The login page should open. In the SAMPLES area, there are 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 the 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 it to a format that DSW can process:

The shortened code moscowElectionsMap.js
function loadCoordinates(polygonCoordsArray)
	{
	polygonCoordsArray['Троицкий административный округ'] =
	 '36.8031,55.44083,0 ... 37.37279,55.80868,0'
	...
	polygonCoordsArray['район Некрасовка'] =
	 '37.90613,55.70626,0 ... 37.37296,55.80745,0'
	}


Save the resulting js file in the web folder of the application of our region and voila.



Create a list of terms


We can also add the ability to determine the color criteria of the map. if we are now colored by the ratio of all voters to the population of the AO / district, we can colorize 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 choosing a key - the value will be substituted 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]

The part that is surrounded by braces will be replaced.

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

For Putin there will be the following line

Key: 2. Putin
Value:

{[Measures].[Putin]\,%LABEL([Measures].[Putin]/[Measures].[votersIncludedInVotersList]\,"ColorRGBValue")\,[CUSTOM].[TooltipValue]\,%LABEL("" + [Territory].[H1].[Region].CurrentMember.Properties("name") + "
Население: " + [Measures].[Population] + " чел.
Число избирателей: " + [Measures].[votersIncludedInVotersList] + " чел.
За Путина проголосовало: " + [Measures].[Putin] + "чел."\,"PopupValue")}

Save this list of terms as MoscowElections2018.

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

Location - Mouse click
Action - Select special. line
Click "OK"

Define the name and list of special. Column
Label - Coloring Criteria
List of spec. columns - MoscowElections2018.termlist

Done!

Additional Feature: Tile Server URL Definition


I would also like to point out that DeepSeeWeb supports the ability to specify a specific path for the server tile that will be used when requesting tiles (pictures) for the map instead of the default one https://{a-c}.tile.openstreetmap.org/{z}/{x}/{y}.png.

We will lower the server tile setting, because this information has been described more than once on the Internet .

To set the path to the desired tile server, you must perform the following steps:

  1. Create a json file with DeepSeeWeb settings or generate a ready-made one that is used by DeepSeeWeb
  2. Specify the node “app.tileServer” with the value - url of the desired server tile
  3. Import the resulting json settings file

Creating json file with DeepSeeWeb settings


The best way to create this is to edit the json file that is already in use by your application. To receive it, click on at the top of the DeepSeeWeb window - a window with DeepSeeWeb settings will open.



Click “Export settings”. Open the downloaded json file and add the tileServer node as a subnode of the app node. For this example, the OSM URL Wikipedia Maps - will be used https://maps.wikimedia.org/osm-intl/{z}/{x}/{y}.png. Save the settings file. Open a window with DeepSeeWeb and load the resulting json file in the DeepSeeWeb settings window. Done!

Conclusions:


We showed an example of using the multi-model DBMS InterSystems Caché, OLAP DeepSee, DeepSeeWeb, MDX2JSON projects. You learned how to create stored classes, cubes, pivots, indicator panels and realized how beautiful and convenient components are available both in the InterSystems Ensemble application integration and development platform, in the multimodel DBMS InterSystems Caché and on the new InterSystems IRIS Data Platform product.

The developed indicator panels were posted on the demo server:

Demo server

Links:


Also popular now: