If you have used previous versions of QGIS you may have noticed that most of the icons on the abc labelling feature were greyed out. In these earlier versions a number of steps had to be taken to enable a layer to access abc labelling features such as move, rotate and hide. For instance, fields had to be added to the layer’s attribute table for x and y coordinates for the move feature to work. Similarly a rotate field was needed for the rotation functionality. To hide labels required yet another field. Again these fields also had to be in the correct format. . Having created these fields correctly there were several more steps required to make the labelling function work. Each field had to be connected to the labels property function and then finally, if editing was enabled, the various features of the abc labelling application would become activated. Because of the amount of work involved, in getting these features to work for each individual layer, this functionality was mainly used by geographers who needed to place, move, rotate and hide layers where the standard features were insufficient. If you have previously used software such as MapInfo Pro you would have been surprised that manipulating labels required so many additional options. In MapInfo you can click on a label and move or rotate it without any of the previous steps mentioned. Once a label is moved in this way in MapInfo it is referred to as being personalised.    

With the advent of QGIS 3 the labelling functionality, which was previously only within proprietary software, is now available to open source geographers.   

Here, I have opened a project in QGIS 3 containing layers from the Alaska data set which is available for download with the latest versions of QGIS. As you can see I have moved the Label Toolbar to the map section on this occasion for clarity purposes. Normally it would be positioned on the top menu.

In this screen shot of the Label Toolbar only one of the icons is currently enabled. In earlier versions of QGIS, as previously discussed, most of these icons would stay greyed out without a lot of extra work being involved. However, if we now click on the regions layer in the Layers panel all of the icons will be enabled as shown in the next screen shot.

Clicking on the abc icon brings up the Layer Styling dialogue panel. As we have selected a layer with labelling enabled we have a number of options available.   

If we switch to another layer such as airports we no longer have any features enabled as shown in the next screen shot. You can do this either by selecting airports from the drop down section of Layer Styling dialogue panel or by switching from the regions to the airports layer in the Layers panel.

To create labels on a layer first you need to select the Show labels for this layer option from the drop down list as shown in the next screen shot. Then select an attribute from the layer’s attribute table to label the map with.

 On the Label Toolbar there are six buttons for modifying labels. Starting from the left is the Highlight Pinned Labels function which will show those labels which have been modified in some way. The cursor appears as a cross on the map when the Label Toolbar is activated.

Similarly the Pin/Unpin Labels function will enable labels to be modified as well as reverted to their previous state.

If you want to hide a label first click on the Show/Hide Labels function as shown in the next screen shot.  

 Placing the cursor over a label as in the next screen shot. The cursor is represented by a cross.

 Pressing the Shift key together with a mouse click will hide the label as shown in the next screen shot.

To hide several layers follow the above process but create a rectangle around the labels you wish to hide. Similarly, to show labels either click or create a rectangle around the area where the labels are hidden.

The Move Label and Diagram icon enables labels to be moved as necessary. In the next screen shot I have clicked on the Northwest Arctic label and moved it to the left.  

 Once in the desired position letting go of the mouse button will place the label in the new position.

Enabling the Rotate Label and clicking and holding down the mouse whilst rotating as desired will reposition the label.

​ Here we have clicked on label and rotated it 24 degrees.

 Once the mouse is released the label will stay in its new position as shown in the next screen shot.

The Change label option allows you to modify the look of individual labels. Click on a label and the Label Properties dialogue box for the label will appear as shown in the next screen shot.

 As you can see there are numerous options for personalising specific labels with this dialogue box. This option enables a label to have a variety of changes simultaneously rather than using the individual options to move, rotate or hide.

As mentioned earlier the improvements to the labelling functions in QGIS 3 makes modifying labels a much easier task than with earlier versions of QGIS.

Geographic joins

Intersects: this geographic function compares data within two tables.

A point, line or polygon intersects another point, line or polygon when they have at least a node in common: a network crosses another network for example a road or a railway line.

The intersection means a geographical geometry touches or goes through another.

Contains and its reciprocal Within means geographical geometries are within another geometry.

A geometry A contains a geometry B when it contains the centroid of B.

When B is within A: for example a catchment area contains clients represented by point geometries.

Entirely within and its reciprocal Contains entire.

A geometry A is entirely within a geometry B when the group of nodes of A are within B.

According to the type of the compared geometry, according to needs, depends on which of these operators are chosen.

Generally, use the Intersects function to compare polygons or from polygons to lines. ​
​

The dialogue box in the Info section states that Project layers: Not connected.

In the Providers section double click on the Project layers option.

The various layers are now displayed

Click on the Ilots_Saumur option and view the Info, Table and Preview tabs​

​In the Tables drop down list choose the Ilots_Saumur and Buffer_Stop_Points tables

Click in the Columns section and from the Columns drop down list choose

Ilots_Saumur.PSDC99

Buffer_Stop_Points.ID_PA

Ilots_Saumur.geometry​

Buffer_Stop_Points.geometry

Choose Ilots_Saumur.geometry from the Columns section ​

Put a comma after this entry

Then choose Buffer_Stop_Points.geometry and add a closing bracket​

Click in the Group by section and choose Buffer_Stop_Points.ID_PA from the Columns drop down list as in the following screen shot

Click on the OK button​

Click on the Execute button or Ctrl + R

Select Load as new layer by ticking in the box

The Co-ordinate Reference System dialogue box appears. Click OK to accept the default co-ordinate system​

Name the layer as “Population data where buffer intersects” in the Layer Name section​

The layer is loaded into QGIS

If you wanted the find the population data for the areas which intersect the seven buffers we could modify the SQL as follows

This will create a new layer similar to the previous one but with a different attribute table as shown below.

With virtual layers you can create more complex SQL statements than is possible with the QGIS field calculator.  Now it is possible to use ESRI shape and MapInfo mif files to create SQL statements previously only feasible with  PostGIS, MySQL and Oracle Spatial formats.

Joining an Access table to a table in a personal geodatabase within ArcGIS software is currently only feasible with ArcMap. However, as discussed in a previous blog, you could import your Access database tables into a format which is acceptable to ArcGIS Pro. In this case creating a join to Access tables in ArcGIS Pro would be possible.

Prior to discussing how to join Access tables in ArcGIS Pro it is relevant to quickly review the history of personal geodatabases and a possible reason for ArcGIS Pro currently not supporting Access tables.

Personal geodatabases were introduced with version 8 of ArcMap. Personal geodatabases use the Microsoft Access .mdb format and are only available on the Windows operating system. A personal geodatabase has on average an effective size of up to 500 mb. Whilst the maximum size of Access tables is theoretically 2GB in reality because of performance issues 500 mb is recommended as the optimum size. Additionally, Esri has also now developed the file geodatabase which gives better scalability, performance as well as cross platform use.  

Since ESRI introduced ArcGIS Pro, Access database connection availability has been limited to ArcMap. Some users may choose to use ArcMap for Access connection and ArcGIS Pro for other work. ArcGIS Pro has many attractive features, which take advantage of the new Windows 10 ribbon interface, which are superior to the menu based system used by ArcMap.

ArcGIS Pro may not support Access database connection because of problems experienced with connecting Access databases to ArcMap. This could be because personal geodatabases and older Access tables share the same .mdb format. As the personal geodatabases use .mdb files they could be accidentally corrupted by users modifying these tables within the Access software.

As mentioned in the previous blog you can easily import Access databases into SQL Server and then access these tables in ArcGIS Pro.

Here is a screen shot of the Observatory database, which has been imported from Access, into SQL Server Management Studio. Here we can see the table which we can use within an ArcGIS project.

If you are using ArcMap and regularly need connection to Access databases you should be aware that if you decide to upgrade to ArcGIS Pro there is currently no Access database connection facility.

There have been some suggestions that since ESRI introduced a ribbon based interface, issues with Windows 10 are the reason for not having an Access connection. However, MapInfo Pro is now also using the same new ribbon based interface with Windows 10 and has no problems with connecting to Access databases.
​
Microsoft Access 2007 introduced the new  .accdb file extensions which replaced the .mdb file extension.  Currently ArcGIS Pro does not connect to the .accdb format.  However,  ArcMap can connect to this newer Access format.

You can work with Access tables of an .mdb format in ArcMap via an OLE DB connection. OLE DB is the standard for sharing data between applications. This enables connection to Access databases in ArcMap. However to ensure data is not corrupted changes should only be made within Access and not via ArcMap.  

For the same reason, modifying a personal geodatabase should only take place within ArcMap. Modifying these files, which are in an Access .mdb format, within Access could lead to the geodatabase file being corrupted.

Access is not recommended for editing personal geodatabases as it was not created for use with the ArcMap geodatabase format. In addition, unlike many other relational databases Access does not support geometry. The ArcMap geodatabase has to keep track of the table changes whenever a user update occurs. For this reason modifying these tables in Access could corrupt the geodatabase.

As there have been problems with Access connections to ArcMap geodatabase files, there is some logicality in ESRI currently not providing an Access database connection in ArcGIS Pro.

This obviously creates a dilemma for current ArcMap users who want to upgrade to ArcGIS Pro but also need to continue to connect to Access databases. One solution would be to use ArcMap for Access connection and use ArcGIS Pro where Access is not required. Running two different versions of gis software could lead to compatibility issues so this is not an ideal solution. The other problem is that ArcMap will not be supported forever and there will come a point where a decision to switch to ArcGIS Pro will become inevitable to avoid working with unsupported software. Currently, there is no information as to whether ESRI will provide Access database connection in ArcGIS Pro.

An interim solution could be to convert Access databases to another database format that ArcGIS Pro does support. Access is a Microsoft product and therefore the obvious route would be to use a Microsoft conversion tool for moving Access tables to another relational database such as SQL Server 17 Express.  

According to Microsoft the maximum size of an Access database is 2GB whilst the max size of SQL Server 17 Express is 10GB. So any current Access database should be well within the limits of the SQL Server 17 Express database.

SQL Server 2017 Express is a free edition of SQL Server, ideal for development and production for desktop, web, and small server applications. You can download the Express version here.  ​You should also download SQL Server Management Studio (SSMS) which you can use to query, design, and manage your databases, either on your local computer, or in the cloud.

Most importantly SSMS is also free! You can download it here.

Start up SQL Express and then SSMS. A dialogue box for connecting to SQL Express will appear as shown in the following screen shot.

Accept the default settings and you will be connected to the server as shown in the next screen shot.

The next step is to create a database for your Access tables. Right click in the Object Explorer pane on the Database folder. Select and click on the New Database option. 

Click on the Next button and choose the Access database connection option and browse to your Access database location as shown in the next screen shot.

Select Destination, Server Name and the Observatory database as shown in the next screen shot.

Click on the Next button and accept the default as in the next screen shot.

Next select the table or tables you wish to import as shown in the next screen shot.

Click on Next to show the next dialogue box as shown in the next screen shot.

The final dialogue box confirms the actions requested and imports the data into the SQL Server database as shown on the next screen shot.

Here is a screen shot of the Observatory tables within SQL Server Management Studio.

The Access tables are now available within SQL Server Management Studio. Now we can connect the imported Access tables to ArcGIS Pro.

Create a new project in ArcGIS Pro called Observatory and click on the Add New Item icon in the Favorites section in the Insert tab. Next choose the New Database Connection option as shown in the next screen shot.

If you are familiar with the Atlas feature in QGIS you will be pleased with the new Report feature available in QGIS 3, which is the latest version. With this new Report feature, as well as attribute driven pages, you can also add headers, footers and static pages to your pdf report. In this way your report will become more readable and meaningful to those you send it to.   

For instance, with the Atlas function you can iterate through a series of polygons, lines or points to create a map series based upon an attribute field of a specified layer. However, with the Report function, in addition to headers and footers, you can also have nested map series as well as static pages in your final report. Because you can generate a much more complex report with many sub sections, the benefits of headers and footers and their inclusion within the report, to make it meaningful, becomes more apparent.

When you include a nested map series your report will iterate through the attributes and group them based on a linked attribute in the top group layer. If your nested layer does not have an attribute field that can be linked you can create a link with the “Join by location” algorithm from the Tool Box.  

For example, if you wanted to link a towns table to a regions table you would use the algorithm to group the towns which are within in regional polygon to create a new table which can then be linked to the top group layer.

If you start a new project in QGIS 3 you can create a new report quite easily. Below I have created a new project with three layers. A French town’s point layer, a polygon regions layer and a graduated polygon regions layer.

To create a report click on Project on the top menu and select New Report as in the following graphic.

Next complete the Create report Title dialogue box and click OK.

This brings up the Reports dialogue box.

Here you can create both a header and footer for your report. When both header and footer are enabled you can click on the Edit buttons to create the relevant pages for your report. Below is a sample header page containing two text boxes.   

If you want to have a static layout page click on the green plus button. Choose the Static layout section option as in the following screen shot.

When you click on the Edit button a blank static layout page is created as shown below.

You can include text, images or legends in this section as shown in the following screen shot.

By clicking at the top level on the Report heading and the creating a Field group section we can then edit the Body section. We can create maps related to regional population by including attribute driven text fields together with a map zoomed in to region level and a legend. An example is shown in the next screen shot.

We can now add a nested section by leaving the Group section highlighted rather than going back to the top Report level. This nested section has a header page which is shown below.

Having created a header page related to each set of regional towns we can now create individual maps of each town within a region. You can see from the following screen shot that the map is using an OpenStreet map layer. As this report has both graduated symbol layers and OpenStreet layers ensure that Lock Layers and Lock Styles are enabled within the Items Properties section for each Field group section as shown in the next screen shot.

Also note that within each Field group section the maps are controlled by the report so you need to ensure that this option is also enabled.   

By clicking on the Report option on the top menu you can create a pdf for your report as shown in the next screen shot.

Depending on how many pages are included in your report and whether OpenStreet data is included will determine how long the report takes to be created. Below are some screen shots from the pdf generated by the above report.

Here is the static page screen shot.

Next the report will generate a map for each region of the French map as shown in the next screen shot.

Next the nested header page is printed as in the next screen shot.

Next the report will produce maps of the individual towns within the Alsace region as shown in the next screen shot.  

Note that the nested Prefecture sections show an OpenStreet layer whilst the Regional sections show a graduated symbol layer.  

This final screen shot shows the final page of the report.

As the above example shows the new Report feature has potential to produce both simple and complex reports which can contain a range of map formats. Because of its flexibility it represents a considerable improvement over the previous Atlas program.

​We could enhance this map by adding a mask as in the next example.

Some improvements to the program would include including an ability to recognise when the user closes one set of maps and opens new ones. This would also include closing any elements in memory which could impact adversely on the next set of maps. Also the cartographic element is very rudimentary.

The program currently is hard coded and only works with base maps. If a style override is applied this is not included in the maps generated by the program. The ability to be able to read this kind of data regarding layer information and apply it to the map series would be very useful.

You can obtain the Atlas program and the related MapInfo Pro tab files from the Pitney Bowes community download page with this link
​

​CartoLegend then calls ThematicName.

Then a SQL query is run to select the region to be printed. ​

In this final blog about automatically creating  a series of maps in an atlas type program,  we will look at adding some text options for the user.

The first option, regarding adding text to a series of maps, is to add a text box so the user can put text on to one or more maps.  This could be achieved by adding an additional ribbon tab for adding text to the map.  In previous versions of the Atlas program updating the organisation details was an option in which a dialogue box automatically appeared when a new selection was created.  Whilst this option enabled updates to occur to organisational data it may be overkill if changes rarely occur.  By adding a tab, where this option could be accessed as an when necessary,  would therefore be an acceptable alternative.  This is option is shown in the graphics below.  

​When the Data option is selected the dialogue box appears with an additional option of a text box for inputting additional information in the map.

This text box could be used to input text relevant to all the maps in a series.  However, this option would not be suitable for customised statements which would be relevant to individual maps.  For this option you will need to add MapBasic code into the Atlas program.  This option would be appropriate if you have data in your tables which you would like to display in your map series.  We can achieve this by updating the code in the LayoutPortrait and LayoutLandscape modules.  
​
Here is the result of some standard text included within a section from the map of French departments.  This French departmental map was used in the original version of the program called PrintLayout developed by Egge-Jan Pollé.

​We could also add some text into a map series which contains variable data by creating code with MapBasic.  Here is an example using data from the map's browser.

​Here is a close up of the variable text.

​Here is the browser data from which this information is derived.

So far in previous blogs we have used MapBasic to create an Atlas program which can iterate through the regions of a specified layer in order to create individual customised maps. The Atlas program could be further enhanced if the user could have cartographic and thematic legends automatically created as required. Additionally the thematic layer should also be made available for the user to include in the printouts.

In the following screen shot a workspace has just been loaded into MapInfo Pro. As you can see the Legend Designer window is in focus. (As seen by the blue band at the top of its window).   

The Atlas program requires the map window to be in focus in order to create a cartographic or thematic legend for each selected region. In the next screen shot a Note message informs the user of the need to change the focus if they require a cartographic or thematic legend. If they ignore this message a map or maps can still be created but without a legend being included in the output.
Here is a screen shot of the Note message.

Assuming that the focus is on the map window, output can be obtained like the following screen shots which are based on the Canadian map provided in the MapInfo Professional trial data. Note that in this map the capital and highways data is not particularly informative.  It is recommended that you save the layers with more meaningful names as is shown in further examples.

Here we have saved the capital and highway layers with more meaningful names:-

The cartographic legend can also be incorporated into maps which have a mask element as in the following screen shots:-

Having created cartographic legends automatically from a workspace the next stage is to also be able to automatically create output from thematic maps with an appropriate legend.

Here is another workspace of the Canadian regions which has a thematic theme and legend.

The Atlas program has MapBasic code to check whether a thematic layer is present. In that case the program creates maps which reflect the theme and also includes a thematic legend as in the following screen shots.

Here is another example:-

As with the cartographic examples, maps with a thematic element can also can be enhanced with a mask. Here are some examples:-

And here is another example:-

The Atlas program needs several new modules in order to be able to create maps which have thematic layers or cartographic legends.

Here is the project code showing the respective modules.

Project file: Atlas.mbp

In all we are adding four new modules, ShadeInfo1, SearchReplace, ThematicName and CartoLegend.

​Additionally, Atlas.def has also been updated.

''************************************************************************************
'**  Project Atlas
'**  
'**  Definition file
'**  
'**  Author: Joseph Short
'**  Date: 14 September 2017
'**  Version: AppVersion (see below)
'************************************************************************************
Include "MAPBASIC.DEF"
Include "ICONS.DEF"
Include "MENU.DEF"

Declare Sub Main
Declare Sub End_Program
Declare Sub Endhandler
Declare Sub InputAtlasDefaultSettings
Declare Sub MLB
Declare Sub LayoutPortrait
Declare Sub LayoutLandscape
Declare Sub MLBOKButton
Declare Sub SelectAllFromMLB
Declare Sub HighLightSelectedRegions
Declare Sub PopulateRegionsArray
Declare Sub DlgHandler
Declare Sub Atlas_dialog
Declare Sub AddMaps
Declare Sub DialogHandler
Declare Sub AtlasAbout
Declare Sub RibbonMaskSettings
Declare Sub CreateMaskMaps
Declare Sub CreateRibbonMask
Declare Sub ShadeInfo1
Declare Sub ThematicName
Declare Sub LegendInfo
Declare Sub CartoLegend
Declare Sub WinFocusChangedHandler 
Declare Function LongDate(ByVal dDate As Date) As String
Declare Function SearchReplace(ByVal strInput as String, ByVal strReplace as String, ByVal strReplacement as String) as String

'Function to check whether a table is open or not
Declare Function TableIsOpen (ByVal sTabName As String) As Logical

' /////     Class "MBExtensions.MBDateAndTime"     /////
' Allows you to retreive information from a MapBasic Date (i.e. YYYYMMDD) or
' DateTime (i.e. YYYYMMDDHHMMSSFFF) string
 
' Convert the string to it's equivalent long date string representation
' (depends on Control Panel > Regional and Language Options)
Declare Method RegionalLongDate
    Class "MBExtensions.MBDateAndTime"
    Lib "MBExtensions.dll" (ByVal sDateString as string) As String

Define AppName "Ribbon Based Atlas Sample Code"
Define AppVersion "0.70"
Define MLBID 1001
Define SelectAllMLBID 1002 

'Defining Dialog Width and Height
'This allows you to specify with and height clauses in terms of characters (i.e., Width 30dW, Height 10dH).
Define dW *4 'Four dialog units equals one character in width
Define dH *8 'Eight dialog units equals one character in height

Global tTime as Time
Global dDate as Date
Global sTabFile, sTable, sColA, sColB, sRegionsArray(), sAddressArray(5), sPrintDate, sCopyrightText, sFont, sRegion, sRegionTitle, sPrinterName, sDocTitle, sPapersize, sOutputFolder, sThematicLayer, sShadeText, sTotalString,sFirstLine, sFirstPart, sSecondPart,sFirstPartThematic, sSecondPartThematic, s_title, sColumn,sRec1, sLegendText As String 
Global iRegionsSelectedArray(), iPaperChoice, iPapersize, iLogoWindowID, iMapWindowID, iInitialMapWindowID,iLayoutWindowID, iLayoutFontSize1, iLayoutFontSize2, iLayoutFontSize3, iLayoutFontSize4, iOrientation as Integer
Global fZoom, fMp, fPaper1, fPaper2, fPaperWidth, fPaperHeight, fHorizontalDistance, fVerticalDistance, fPos1, fPos2 As Float
Global lRegionObjectsSelected As Logical
Global l_create_mask, l_shadeInfo, is_thematic, is_legend, is_cartographic As Logical 'TRUE means include mask 
Global sMaskTable, sMaskTabFilePath As String
Global sPctTranslucencyArray(5) As String
Global fX1, fY1, fX2, fY2 As Float
Global oRectangle, oCutter, oMask, ObjRegion As Object
Global iPctTranslucency As Integer
Global pPenStyle As Pen
Global alAlias,alTable As Alias

In the last blog we developed an atlas program to create A4 and A3 printable maps from tables and workspaces. We could further enhance the program by adding a mask facility. The code for the mask was originally developed by Egge-Jan Pollé.

Here is a screen shot of the updated ribbon based atlas program including the mask option.

If we now use the Canadian maps provided in the MapInfo Pro trial data folder we can see how the mask works. Here is a screen shot after the Mask Settings OK button is pressed.

The Create Mask Settings dialogue box allows the user to select a Border style and Translucency level if the Create Mask Settings option is enabled. If the OK button is pressed with the default settings a map will be printed with the following border style.

The translucency and border options are shown below:-

Having clicked the OK button these settings will be reflected in the maps selected. If we choose the Atlas Example button a dialogue box appears. Her you can choose a table and a column for your map/s as shown in the next screenshot:-

Clicking OK brings up the Default Settings dialogue box. Here you can make changes as required. Here is a screen shot of the dialogue box.

Clicking on OK will bring up the Ribbon Based Atlas Sample Code dialogue box as shown in this screen shot.

Having chosen one or more regions clicking on the Print button will produce output like the following screen shot.

We could change the translucency level or the borders by clicking on the Mask button. The screen shot shows the proposed changes.

Here is a screen shot of a printed map after making these changes to the border type.

If you require a map without a mask you can do this by clicking on the Mask Settings button and deselecting the mask option. Here is a map created after deselecting the mask option:_