Capture Location Data & Geographic Coordinates from a Wikipedia Table in a Spreadsheet

Many modern teachers tell their students not to rely on Wikipedia as a source of information. But it’s difficult to ignore one of the Internet’s top sites, peer-reviewed all day long by the connected world. The free online encyclopedia is often the only place some information is aggregated. With crowd-sourced data about anything and everything, Wikipedia’s attraction is obvious. And that’s not to mention its lists and data tables that make it easy to copy and paste information into spreadsheets like Excel for further data analysis.

That said, there are still limitations to data gathering on Wikipedia, especially if locations are involved. While many Wikipedia tables contain the exact latitude and longitude coordinates of each location, other times, that information is missing. There are plenty of reasons to need that specific information. One example is if you’re dealing with locations that formerly existed. Exact coordinates of where these places once stood would be necessary as they may no longer exist on a map.

While you can certainly click on most Wikipedia pages to see a location’s coordinates in the upper right-hand corner, you wouldn’t want to have to do that for each item in a 135-location table. There’s an easier way to obtain coordinates from Wikipedia locations using the MediaWiki API result—and we’ll show you how in this post.

Copy All Wikipedia Table Data to a Spreadsheet

Step one of capturing location data from a Wikipedia table in a spreadsheet is getting the data from a Wikipedia table into a spreadsheet. While there are multiple ways of doing so, including just regular copy-paste, to get gather what we need to use the MediaWiki API, we’ll need to use a tool like Table Capture.

  1. Add the Table Capture Google Chrome extension
  2. IMPORTANT: Click the extension in the upper right-hand corner of your browser and opt for options
  3. On the page that pops up, ensure Extract link URLs from table cells is checked
  4. Now, navigate the Wikipedia page with a table you wish to pull data from
  5. Click the extension
  6. Select your desired table
  7. Click the icon that represents the action you want to take (i.e. Copy table data to the clipboard, Export table to Google Sheets, etc.)
  8. Paste to your spreadsheet if necessary

Now that you have the Wikipedia table data in your spreadsheet, we’ll need to isolate captured links from table in your spreadsheet so that we can use the MediaWiki API.

Isolate Captured Links & Queries in Your Spreadsheet

The data we captured from the Wikipedia table in the previous section contains Wikipedia page links, which we’ll need in order to obtain exact geographic coordinates. However, these URLs are buried within the data, not useful until we can isolate them into one column.

The method we’re about to show you is quicker, especially if you have hundreds of locations, as with our Former Major League Baseball stadiums. So let’s continue.

  • In your spreadsheet, identify the column that contains both the names of your locations and their corresponding Wikipedia page URLs (if the previous step was done correctly). In our case, that’s the “Stadium” column
  • Next, you’ll need to separate location names from the URLs using Excel’s “Text to Columns” tool (Google Sheets has a similar feature called “Split text to columns”):
    • Navigate to the “Data” tab in Excel and select “Text to Columns…”
    • Opt for Delimited characters seeing as the URLs should be contained within parentheses
    • Click Next and check “Other”
    • Type in an open parenthesis ((), then click Finish
      • Note: If any rows end up split into three columns instead of two (one for location names, one for Wikipedia page URLs), you’ll need to use =CONCATENTATE to combine the two parts of the links
      • First, make these easy to identify by using “Sort & Filter” > “Sort A to Z”
      • Then, in the second cell of a fourth column, type =CONCATENTATE, click the first cell with part of a URL, “(”, followed by a comma and the second URL cell
      • Drag that formula down to all of the similar cells in the column
      • Copy and “Paste Values” into that same column, then select the previous two columns, delete and shift them left
    • You’ll finish up by following the same “Text to Columns” steps above and splitting the entire column by ending parentheses
  • Then, use “Conditional Formatting” in the Home tab to highlight any link cells in which a starting parenthesis remains as part of the link
  • You can either use ‘=CONCATENATE’ or manually add back the closing “)” to all of the highlighted cells
  • Just one more step: we only need the query string or parameter part of the link, so you’ll once again use “Text to Columns,” this time noting “/” in the “Other” space
  • Delete the other split link columns and you’re done!

Now you have the exact links to the exact Wikipedia pages, and can use the MediaWiki API result to more easily grab their exact coordinates.

Use MediaWiki API Result to Get Coordinates

With our data gathered and locations and link queries in their respective columns, we can finally use the MediaWiki API result in order to obtain latitude and longitude coordinates with minimal clicks. Non-developers needn’t worry—this is the easy part for any experience level.

  • In your spreadsheet, copy (Ctrl+C or Cmd+C) your first Wikipedia page query
  • Navigate to MediaWiki API result and paste your query at the end of the URL bar, replacing our Sahlen_Field example
  • Press enter, then copy and paste the resulting “lat” and “long” into your spreadsheet!

They’re even in the perfect format for making a custom Google Map from your data…

Map Your Coordinates & Data

As an optional, final step, you might plot your newly gathered data points on a Google Map. Here’s how:

View MLB Stadiums in a full screen map

  1. Open your spreadsheet
  2. Select (Ctrl+A or Cmd+A) and copy (Ctrl+C or Cmd+C) your data
  3. Open your web browser and head to
  4. Click on the location data box with the example data in it, then paste (Ctrl+V or Cmd+V) your own data
  5. Check to make sure you have the proper location data columns available by clicking “Validate and Set Options”
  6. Select the proper location column from each drop-down
  7. Click “Make Map” and watch as the geocoder performs its process

Get started for free at

Top 5 Mapping Apps of 2024

It’s easier than ever for you to bring location data to life. When you provide a geographic visualization to your website, news story, or sales process, you uncover additional meaning. And just about everything has a useful location context.

Anyone can put some markers on places, thanks to the abundance of mapping apps available. You have many different types of maps now at your fingertips. In some cases, you can change marker colors, overlay heatmaps, and filter by data about each place. However, all these features also add more complications: how do you choose which to use and how do you know if it does what you need? Whether you’re a seasoned cartographer or just starting your mapping journey, we’ve compiled a list of 2024’s top mapping apps to help you find the perfect fit for your needs.

1. BatchGeo: Best for Entirely Customizable Google Maps

View Example Sales Map in a full screen map

BatchGeo sets itself apart as a user-friendly mapping tool that relies on the robust infrastructure of Google Maps, distinguishing it from many competitors in the market. With its user-friendly bulk mapping capabilities, BatchGeo caters to the needs of businesses and individuals alike, providing a convenient way to create interactive maps efficiently.

Ranking #1 in both satisfaction and popularity from G2’s 2023 GIS Software Awards, the platform’s integration with Google Maps offers users a familiar and dependable interface for their mapping endeavors.

Furthermore, BatchGeo’s complimentary plan is a notable advantage, granting access to a range of features that are typically reserved for premium subscriptions.

BatchGeo also enables seamless sharing of maps, allowing users to distribute them publicly or privately. Its diverse functionalities, such as creating heat maps, along with customizable map bases, marker shapes, and colors, enhance its adaptability.

For users seeking even more advanced customization options, BatchGeo offers a paid subscription that unlocks 100,000 markers per map, measurement and routing features, and more for you and a team of 10 users for one low price.

We recommend you start a BatchGeo trial and compare it to one or more of the options below.

2. Mapline: Best for the Basics

Mapline, like the others on our list, lets you mark places on a map. Its in-house map looks quite a bit different than Google’s, though Mapline adds routes and territories, as well as measurement features.

The Geospatial starter plan provides a sampling of the company’s mapping tools. It allows you to create maps, plot locations, and explore Mapline basics. You can only upload up to two datasets with 500 locations each, but you’ll be able to create territory and heat maps and draw a coverage radius.

Beyond its basic maps, Mapline has enterprise products for analytics, dispatching, scheduling, and operations. The pricing and functionality for these products are only available from Mapline Sales.

Nevertheless, the self-serve option is worth a test drive, if only to see how to create and navigate the maps. Some users find Mapline less user-friendly, with its interface a bit confusing compared to the next top mapping app. The paid products may provide a more focused offering, but you’ll want to make sure the underlying map does what you need.

3. Maptive: Best for Complicated Territory Maps

Maptive is a simple and easy-to-use choice for online mapping. It provides the basics we’ve come to expect from web mapping, like creating and sharing maps effortlessly.

If you’re looking for complicated sales territories, you’ll want to try Maptive. It has polygon-drawing features to create territories, map geographic boundaries, and color-code each area. It adds heat maps, demographics data, and distance and drive time calculations.

To get the most out of Maptive’s most complicated features, you’ll likely need a team account, which is at least $5,000 annually for 10 seats. You’ll find a 10-day free trial to see if it works for your needs. Unless you need the complicated territories, this pricing may be a bit on the higher side. On the other hand, if you need GIS-level mapping needs, you might prefer to look at one of the next two options.

4. Maptitude: Best for Offline Use

Maptitude is firstly a desktop application to create maps. There are options to take these maps online, but much of the customization needs to happen on your computer. There are advantages to this approach, such as bringing external geographic data to your maps.

Another advantage of a desktop tool is that Maptitude enables you to create and view maps whether you’re connected to the internet or not. In fact, Maptitude’s examples focus on static visualizations, which you can share as images.

There is a Maptitude free trial to get you started, so you can answer for yourself if it solves your mapping problem. The pricing isn’t as simple as web-based software, with a number of options based on your needs. The entry point for the desktop software is $695 per user for a year of access.

5. ESRI ArcGIS Online: Best for Versatility

As the undisputed industry standard for cartography, we couldn’t wrap up our list of the top apps without mentioning ESRI ArcGIS Online. The well-known software caters to businesses and individuals that need robust spatial analysis and map creation tools. ESRI ArcGIS Online stands out for its unparalleled versatility, offering a comprehensive suite of features used by professional GIS engineers worldwide. From precise geolocation to advanced analytics, it is the primary tool for those who need maximum map customization beyond what’s possible with Google Maps.

ESRI ArcGIS’s downside lies in its steep learning curve, which often demands a large time investment. Additionally, its cost may pose a challenge, especially when compared to lower-cost alternatives that solve the most common mapping problems with web-based maps.

When it comes to mapping apps, there are many options. Mapline, Maptitude, Maptive, and ESRI ArcGIS Online all are excellent choices for specific needs. However, BatchGeo stands out among the most user-friendly and intuitive, thanks to its seamless integration with the familiar Google Maps platform and customization features that go beyond what Google Maps offers, all without the need for coding knowledge.

Give BatchGeo a try now and make Google Maps work for you – with faster geocoding, route optimization, password protection, 10 users at no additional cost, PDF support, and more.

A Map of U.S. State Mottos

What’s your personal motto? By definition, a motto is a phrase intended to formally describe the general motivation or intention of an organization or individual. Ours might be “Make a map from your data,” while the motto of the United States is “In God We Trust.” The U.S.’s is much more official, as it was proclaimed by Congress and signed into law by President Dwight D. Eisenhower in 1956.

The nation as a whole aside, most U.S. states, the District of Columbia, and three territories have their own mottos, which can sometimes be found on state seals, flags, or even quarters.

So, looking at our motto map, let’s figure out the states with more than one, their languages, and the oldest of the bunch.

View U.S. State Mottos in a full screen map

Which States Have More than One Motto?

South Carolina state seal

Every U.S. state has at least one motto, though several states have multiple.

South Carolina is one of the four states with two official mottos: “Dum spiro spero” and “Animis opibusque parati.” Both are in Latin, though they can be translated to:

While I breathe, I hope

Ready in soul and resource

Kentucky also has two state mottos, one in Latin (“Deo gratiam habeamus” or “Let us be grateful to God”) and the other in English (“United we stand, divided we fall”).

The same goes for North Dakota and Vermont. North Dakota’s “Serit ut alteri saeclo prosit,” means “One sows for the benefit of another age” while its second English motto is “Liberty and union, now and forever, one and inseparable.” Vermont’s Latin motto is “Stella quarta decima fulgeat” or “May the fourteenth star shine bright,” followed by “Freedom and Unity.”

Except for Guam and the Northern Mariana Islands, which are, unfortunately, motto-less, all other U.S. states and territories have one motto, though they’re in a variety of languages.

The 9 Languages of U.S. State Mottos

The 54 U.S. states and territories have 58 mottos in nine languages. Here’s the breakdown:

  • Latin: 26 states
  • English: 25
  • Chinook Jargon: 1
  • French: 1
  • Greek: 1
  • Hawaiian: 1
  • Italian: 1
  • Samoan: 1
  • Spanish: 1

Latin is the most-used language for state mottos, used by 26 states and territories, including the previously mentioned South Carolina, Kentucky, North Dakota, and Vermont. Also among those with a Latin motto is the District of Columbia (“Justitia Omnibus” or “Justice for all.”)

Meanwhile, the number of English state mottos isn’t far behind, especially in the Midwest (12 states). This includes Wisconsin’s, which is simply, “Forward.”

That leaves just seven states and territories that use another language for their motto, of which each is only used once. Chinook Jargon is the language of Washington’s state motto, which translates to “By and by” in English. Minnesota, California, Hawaii, and Maryland’s mottos are in French, Greek, Hawaiian, and Italian, respectively. Then there’s the Samoan motto for American Samoa and Spanish for Montana.

You can group the map by “Language” to learn more because we’re moving onto the age of these mottos.

The Oldest State Mottos

For most state mottos on the map, the year indicates the earliest date they were officially used. These range from as recent as 2015 to as old as 1511, so let’s take a closer look at the oldest among these in the table below:

Jurisdiction Motto Year
Puerto Rico Joannes Est Nomen Ejus 1511
Connecticut Qui transtulit sustinet 1662
Rhode Island Hope 1664
Massachusetts Ense petit placidam sub libertate quietem 1775
Virginia Sic semper tyrannis 1776
South Carolina Dum spiro spero 1777
South Carolina Animis opibusque parati 1777
New York Excelsior 1778
Vermont Freedom and Unity 1779
Georgia Wisdom, Justice, Moderation 1798

The earliest use of a current motto is that of Puerto Rico’s “Joannes est nomen ejus”, which was granted to the island by the Spanish back in 1511. State-side, Connecticut has the oldest motto, “Qui transtulit sustinet,” first used in October 1662.

Of course, it’s no surprise that aside from Puerto Rico, the oldest among these are located in the East, both in the North (Connecticut, Rhode Island, Massachusetts, and New York) and South (Virginia, South Carolina, and Georgia).

To learn more about the U.S., we’ve created a flashcard map to help you master each state’s capital, flower, and bird.