Printer Friendly

Using excel to map boundaries: a new example.

The cost and complexity of GIS and other specialized mapping software can be discouraging to those of limited means or training who want to produce relatively simple cartographic representations of data. One potential solution to this problem is to use the graphing functionalities of spreadsheets, such as Microsoft's ubiquitous Excel software. These notes are a follow-up to a paper by the author recently published elsewhere (Whyte, 2011) on the use of spreadsheets to produce maps.

In that paper Excel's graphing capabilities were used to map an enclave on the Belgo-German boundary, using survey traverse and offset data taken directly from the 1922 atlas produced by the boundary commission which delimited and demarcated the boundary defined in the 1919 Treaty of Versailles. The survey data was converted into XY coordinates with an arbitrary origin, and plotted using Excel's 'XY (scatter)' graph type. The perimeter and area of the enclave were also calculated.

In the present paper, another set of enclaves will be mapped in Excel, again using data taken directly from a boundary treaty. The 1995 boundary Convention between the Netherlands and Belgium (Moniteur Belge, 1996) defined 30 enclaves in and around the village of Baarle in terms of the national grid of each country. Excel is used to plot the 959 coordinate pairs given in the Convention, and to calculate the areas and perimeters of each enclave. These calculations are then compared with the areas and perimeters given in the Convention. While in this case the data do not need conversion before they can be plotted in Excel, the data set is much larger and more complex. Additionally, this example shows how to plot a number of disjoint polygons on the same map-graph. Where necessary, specific instructions are given for both Excel 2003 and Excel 2007.

As a full literature review was given in the earlier paper, it will not be repeated here. As in the earlier example, the area involved is small enough that a rectangular Cartesian plane can be safely used, and the issue of the earth's curvature, and thus of projection, can be ignored.

SURVEY DATA: THE BELGO-DUTCH BOUNDARY AT BAARLE

The village of Baarle is located between Turnhout in Belgium and Tilburg in the Netherlands. While a single unit in general urban form, the village and its surrounding farmland are divided by the international boundary into a jigsaw puzzle, some parts belonging to the Belgian commune of Baarle-Hertog and some to the Dutch commune of Baarle-Nassau. There are a total of 30 enclaves, besides several non-enclave fragments of Baarle-Hertog commune connected to mainland Belgium (Fig. 1.). Twenty-two of the enclaves, officially designated H1 to H22, are Belgian enclaves inside the Netherlands. Of eight Dutch enclaves (designated N1 to N8), one, N8, is within Belgium proper; the other seven are Dutch counter-enclaves (enclaves within enclaves) inside the two largest Belgian enclaves (H1 and H8).

The enclaves were created circa 1198 as the result of a feudal agreement between neighbouring lords (for a full historical geography and detailed maps of the enclaves see Whyte, 2002 & 2004). After

[FIGURE 1 OMITTED]

Belgian independence from the Netherlands in 1830, a boundary commission was tasked with delimiting and demarcating the Belgo-Dutch boundary in 1842-43; but due both to the complexity of the enclaves, and to optimistic plans to exchange or remove them, neither the enclaves nor a significant segment of the main Belgo-Dutch boundary around Baarle were demarcated or even delimited. Rather, the status quo was explicitly maintained. The main Belgo-Dutch boundary at Baarle was only delimited in 1974 (demarcation was explicitly avoided due to expense). It was another 20 years before the enclaves themselves would be delimited in 1995. The boundary Convention of that year explicitly acknowledged that demarcation was almost impossible for practical as well as financial reasons:

3. Principles governing the delimitation of the boundary

[...]

c) [...]

Given that the boundaries of the enclaves have been established in a precise manner in the Belgian and Dutch national coordinate systems, that the annexed maps are drawn up in these systems, and that the manufacture and placement of boundary pillars in cast iron or in natural stone conforming to regulation will entail great expense, it has been decided to dispense with placement of boundary pillars.

Seeing that buildings straddle many of the boundaries of the enclaves, the placement of boundary pillars was not even possible in this case.

The Convention then proceeded to delimit the enclave boundaries in terms of both the Belgian and Dutch national grids:

d) As it is impossible to describe the boundaries of the enclaves on a topographic or geographic basis, each turning point of the boundaries is identified by a serial number.

For each enclave, the perimeter is described by a list of the successive serial numbers of the turning points along its perimeter.

Each numbered turning point corresponds to a single set of coordinates. The area and perimeter of each enclave is also given.

[...]

4. Description of the Enclaves

A. Generalities

[...]

The perimeter points of the enclaves are determined in the coordinate systems of Belgium (Lambert 1950) and of the Netherlands (Rijksdriehoeksnet, triangulation network of the kingdom).

For the coordinates of the perimeter points, refer to the list of coordinates which forms part of the Minute (pp.16-38 inclusive).

After this preamble, the Convention listed the serial numbers of the turning points of the boundary, in order around the perimeter of each enclave in turn. The listing for Belgian enclave H3 is given below.

B. The Enclaves of Baarle-Hertog.

[...]

ENCLAVE H3.

Local name: De Rethsche Akkers

Area: 34a 28ca

Perimeter: 0.295km

The perimeter is delimited by the points: 5145 - 5146 - 5147 - 5148 - 5145.

Plans: Annex No.4, p57 and Annex No. 5, p84.

After this listing of turning point serial numbers for each of the 30 enclaves, the final 22 pages of the Convention listed the Belgian and Dutch coordinate pairs for each of the 959 turning points. The X and Y coordinates correspond to eastings and northings respectively. In this listing the points were listed in numerical order of serial number, from 1008 up to 98120, rather than enclave by enclave. Note that the coordinates of each point are in metres, but with centimetre precision. The serial numbers are also not consecutive: there are many gaps in the sequence of serial numbers.
5. List of coordinates for the perimeter points of the enclaves.

Point Belgian Coordinates Dutch Coordinates

 X Y X Y

1008 188198.81 237287.15 122390.54 383978.75
1009 188204.49 237228.74 122395.38 383920.22
1010 188225.48 237229.50 122416.41 383920.76
[...]
98117 189790.89 236330.28 123968.95 382999.98
98120 189597.92 236408.41 123777.15 383080.84


Thus about 800 years after they were created, the 30 enclaves, having a total area of 249.5525 hectares and combined perimeters totalling 35.207 km, were formally delimited by 959 turning points connected by great circle line segments, for an average of one turning point every 36.71 metres.

MAPPING THE BOUNDARY IN EXCEL

To map this data in Excel is a simple exercise, although the data entry is obviously tedious. The 959 serial numbers and Belgian set of national coordinates listed in the Convention were entered row-wise into the spreadsheet. The Belgian coordinates were chosen because they were listed immediately adjacent to the serial numbers themselves, and this adjacency reduced the likelihood of data entry errors.

A further column was then added to the spreadsheet, coding each turning point according to the enclave it bounded. The spreadsheet was then sorted by this coded column, to group the turning points for each enclave, and then each enclave's entries were manually sorted into perimetrical order as given in the Convention, as the numbering was not always in numerical order nor consecutive (Table 1.).

The points in the two coordinate columns are then easily plotted using Excel's XY (scatter) graph type, with straight connecting lines between points (Fig. 2.). By repeating the first coordinate pair of each enclave at the end of the list for that enclave, the straight lines linking the plotted points will form a closed curve linking back to the first point of that enclave. To ensure the enclaves are each plotted as discrete subgraphs, rather than all connected together, a blank row should be left between each enclave listing. In Excel 2003 from the 'Tools' menu, choose 'Options', and in the resulting pop-up window, click the 'Chart' tab, and select the radio button for 'Plot empty cells as: Not plotted (leave gaps)'. In Excel 2007, blank cells signal breaks in the graph by default. If someone has changed this default, click the chart, which highlights the 'Chart Tools'--'Design' tab on the toolbar; then in the 'Data' group, click 'Select Data', click the 'Hidden and Empty Cells' button, and then select the 'Gaps' radio button.

[FIGURE 2 OMITTED]

Because the coordinates are in metres, a kilometre grid can easily be added to the map by setting the major gridlines on each axis to 1000-unit intervals. The turning points, and the linework of the boundary and the grid, can also all be formatted for style and colour as needed.

The individual turning points can also be labelled with their serial numbers, but given the complexity of this particular map, this is only useful when the graph's axes are rescaled to show only one or two of the enclaves (Fig. 3.).

[FIGURE 3 OMITTED]

The map can be copied and pasted into other documents, such as Word, or printed directly from Excel. By pasting it into Word first, the whole map can be stretched as necessary to ensure even scales on both axes. It is thus probably better to label the map in Word, rather than in Excel, adding the title and axis labels as text boxes after stretching the map as needed.

AREA AND PERIMETER CALCULATIONS

The perimeter and area of each enclave can also be calculated, and checked against the figures given in the 1995 Convention (this also provides a check on the accuracy of data entry).

Each enclave's perimeter is the sum of the lengths of the individual segments connecting the points around the enclave. This is easily calculated in the worksheet by adding another column each cell of which calculates the Pythagorean distance between the XY coordinate pair on its own row, and that on the row immediately below using the formula:

=SQRT(([X.sub.i]-[X.sub.i+1])^2+([Y.sub.i]-[Y.sub.i+1])^2)

Each enclave is obviously a polygon, so its area is calculated as the sum of the areas of a series of trapezia constructed by dropping vertical lines from each vertex (i.e. turning point) to the x-axis (Fig. 4.). In this example, using the Belgian national grid, the axis is over 230 km southwards, so the trapezia are necessarily very tall and very thin. The area of each trapezium is calculated from the length of its base multiplied by its mean height:

=([X.sub.i]-[X.sub.i+1])*([Y.sub.i]+[Y.sub.i+1])/2

By progressively moving around the perimeter in sequence, some of the trapezia will have positive areas and some will have negative areas; as a consequence, the resulting sum of the area of the trapezia may itself be negative or positive. This is simply an artefact of the arbitrary choice of proceeding clockwise or anticlockwise around the enclave when constructing the trapezia. The negative trapezia areas will partially cancel with the positive areas when summed, removing from the calculations the area between the southern boundary of the enclave and the x-axis. By taking the absolute value of the sum of the areas of the individual trapezia using the formula:

[FIGURE 4 OMITTED]

=ABS(SUM(FirstTrapeziumArea:LastTrapeziumArea)

a positive, and correct, area for the enclave as a whole is obtained (Table 2). The perimeter and area of enclave H3 calculated here agree with those given in the 1995 Convention of 0.295 km, and 34 ares 28 centares (i.e. 0.3428 ha or 3428 [m.sup.2]) respectively.

The mapping process in Excel also provided a visual check of the data. The map highlighted several typographic errors in the published coordinate listing used for this case study, such as point 1446 in enclave H8, whose x-coordinate was listed as 189187.83, but should have been 189787.83. By using Pythagoras' Theorem on the Dutch coordinates (assuming they were correct!) to obtain correct segment lengths, and then using Pythagoras in reverse on the Belgian coordinates, the erroneous Belgian coordinate could be corrected.

However, while the calculated perimeters of all 30 enclaves agreed with those given in the Convention, the calculated areas of four Belgian enclaves and one Dutch counter-enclave did not agree. Careful checking eliminated transcription errors as the source of these discrepancies. In three of the six cases (H18, H19 & N1), the difference in calculated area was less than 1 [m.sup.2] and so the discrepancies may simply be due to rounding errors in the calculations used by the Boundary Commission. Two other cases (H1 & H17) in which the errors were between 2.5 and 2.8 mm appear more difficult to explain, although they represent only 0.0002% and 0.002% errors respectively. The sixth case (H21) had an error of almost 27 [m.sup.2] or 0.22%. No response was received to enquiries addressed to the Belgian and Dutch provincial surveying authorities regarding these errors. If the coordinate data as printed in the Moniteur Belge is in error, this could be identified by careful crosschecking with the data in the original survey plans. Alternatively, the errors could be detected and corrected by repeating the mapping, and perimeter and area calculations, using the Dutch coordinates, or simply by using Pythagoras' Theorem on both sets of coordinates as printed to locate and correct errors in both. Given the amount of data entry needed this has not been attempted here.

Given a set of coordinate data on a Cartesian plane, Excel and other spreadsheets offer a simple yet powerful means of mapping the data, checking for data errors, and performing geometric calculations to find perimeters and areas. The graph formatting functionalities, while limited compared with most GIS systems, do provide simple means to make the maps relatively visually pleasing. Nevertheless the ubiquity, ease of use and compatibility of Excel with other common programmes such as Microsoft's Word and Powerpoint are big advantages for low-end users, who may not have the finances or training for, or access to, higher-end GIS software.

Acknowledgement

This report originated in work funded by a Lady Davis Postdoctoral Fellowship at the Hebrew University of Jerusalem.

REFERENCES

Moniteur Belge/Belgisch StAATSBLAD. 1996. "Convention fixant les limites entre le Royaume de

Belgique et le Royaume des Pays-Bas, signee a Maestricht le 8 aout 1843. Proces-verbal de delimitation de la frontiere des enclaves de la commune de Baarle-Duc, situees sur le territoire de la commune de Baarle-Nassau et des enclaves de la commune de Baarle-Nassau, situees sur le territoire de la commune de Baarle-Duc, signe a Baarle le 31 octobre 1995", Mercredi 26 Juin [Wed. 26 June], 166/122: 1756517594. In parallel Dutch and French columns.

Whyte, B. 2002. "Bordering on the Ridiculous? A comparison of the world's two most complex boundaries: The Belgo-Dutch enclaves at Baarle and the Indo-Bangladeshi enclaves at Cooch Behar", The Globe, 53: 43-61.

--. 2004. 'En Territoire Belge et a Quarante Centimetres de la Frontiere', an historical and documentary study of the Belgian and Dutch enclaves of Baarle-Hertog and Baarle-Nassau, Research Paper 19, School of Anthropology, Geography and Environmental Studies, University of Melbourne, Melbourne.

--. 2011. "On the Use of Excel (and other Spreadsheets) for Mapping", Cartographica, 46(1): 41-50.

Brendan Whyte (1)

(1) Brendan Whyte is Assistant Curator of Maps at the National Library of Australia. Email: bwhyte@nla.gov.au
Table 1. Excel extract showing layout of data for Belgian enclave H3.

Enclave Turning Turning BELGIAN COORDS
serial point Point (Lambert 1950)
 serial Number
 X Y

 (m) (m)

H3 1 5145 189712.19 236803.41
H3 2 5146 189705.28 236831.40
H3 3 5147 189821.43 236857.46
H3 4 5148 189828.08 236829.39
H3 1 5145 189712.19 236803.41

Table 2. Excel extract showing area and perimeter calculations
for Belgian enclave H3.

Turning Turning BELGIAN COORDS inter-point enclave
Point Point (Lambert 1950) distances perimeter
serial Number
 X Y (m) (m)

 (m) (m)

1 5145 189712.19 236803.41 28.830
2 5146 189705.28 236831.40 119.038
3 5147 189821.43 236857.46 28.847
4 5148 189828.08 236829.39 118.766
1 5145 189712.19 236803.41 295.48

Turning trapezia enclave
Point areas area
serial (sq. m) (sq. m)

1 1,636,408.2686
2 -27,509,480.5445
3 -1,575,008.7762
4 27,444,652.5960
1 3428
COPYRIGHT 2011 Australian and New Zealand Map Society, Inc.
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2011 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Author:Whyte, Brendan
Publication:The Globe
Date:Jul 1, 2011
Words:2809
Previous Article:James Cook--SNLR.
Next Article:Phillip Lionel Barton: Map Librarian, Alexander Turnbull Library, Wellington.
Topics:

Terms of use | Privacy policy | Copyright © 2021 Farlex, Inc. | Feedback | For webmasters