Convert your address data into geographic coordinates using Spatial Studio

August 11, 2022 | 11 minute read
Karin Patenge
Senior Principal Product Manager, Oracle Spatial and Graph
Text Size 100%:

You probably have address data stored in your Oracle Database, but now you want to visualize addresses on a map and do some spatial analysis or mining. You can´t do so by using the address information as is. What you need are geocoded addresses, meaning geographic coordinates for your locations.

To get started with geocoding, have a look at your address data. Make sure you understand how your addresses are specified and where you might have problems with the data quality.

To receive the best possible results while geocoding, we recommend having separate columns for the following address-type attributes:

  • House number
  • Street name
  • City/Settlement
  • Postal code
  • State/Province

Our demo data set is a subset of publicly available and free address data is provided by Statistics Canada. Click here to download it. The complete data set you can download via this URL.

Let´s load the demo data into an Oracle Database using Spatial Studio.

Step 1: Create a data set

Start Spatial Studio and connect using your credentials. Click on the “Datasets” icon in the navigation pane on the left, then “Create Dataset”.

Create dataset


Drag and drop the previously downloaded file (address_data_ca_subset.csv) into the “Create Dataset” dialog. Confirm with “Create”.

In “Create dataset from a CSV file” enter values for the following parameters or confirm the defaults:

  • Upload to connection
  • Table name
  • Dataset name

Ensure the column names are valid and you have the correct data type assigned.


Create dataset from a csv file


If everything looks correct, click “Submit” and wait for the process to finish. You should then find the newly created data set.




Step 2: Fix address data issues

You see a warning when looking at the icon left from the data set. This means that there are issues to be fixed first. Those issues could be:

  • The definition of a unique key is missing.
  • The geographic coordinates are missing.
  • A spatial index is missing.

To identify what is missing from your data, click on the icon. You will get the following information:


Key Column Issue


Let´s fix the issues one by one. Start defining the key column by clicking “Go to Dataset Columns”.


Dataset Properties


The column ID can possibly serve as a key column. Select it and check whether the values in this column are unique by clicking on “Validate key”.


Key column


The key is valid. Therefore go ahead by clicking on “Apply”.

Note: If there is no suitable column in the data set that can serve as a unique key, you can select to create one by clicking on “Create Key Column”.


Enter column name


For this option, click “Ok” and “Apply”.

Since the warning icon is still attached to the data set, we will work on the next issue, the missing coordinates. Remember, to derive the geographic coordinates from the address data, we need the geocoder to start working.

Click on the icon again and then select “Geocode Addresses”.


Geocode Addresses Issue


Now the dialog opens where you can map the different columns in your data set to a geo-type that the geocoder requires.

Based on the column names, some columns have already been mapped to the best matching geo-type. It does not always have to be correct. Feel free to change the mapping to your needs if necessary.


Geocode Addresses Dialog


After you have mapped the columns, you also need to select the country. This is necessary because every country has its way of specifying address data. Selecting the State/Province is only recommended if the addresses can be fully assigned to one value. It is not the case for our demo data set.

Optionally, you can create two additional columns with the default names LONGITUDE and LATITUDE.

Now you are ready to start the geocoder. Click “Apply”.

It is time to take a break and get a coffee or tea since it takes a couple of minutes to geocode the entire data set.


Geocoding Progress


You can follow the progress of the geocoding process. The geocoder fetches the data batch by batch, calculates the coordinates for each batch, and writes the results back into the database. The batch size you can specify in the general settings of Spatial Studio. For now, it uses the default or whatever value you have set before.

If the status changes to “DONE” you can “Close” the dialog.

When finished, you can see that the warning icon is no longer there. Instead, you have a new icon that represents a location. This is your confirmation that the data set now has coordinates assigned and is ready to be visualized on a map.


Geocoded Dataset


Let´s sidestep a bit and have a look at our database table using SQL Developer. We can see there that we have three new columns with data populated:



Address Data via SQL Developer


Step 3: Verify geocoding results

It’s common that not all addresses in a data set can be geocoded. Reasons include missing or incorrect data, affecting the quality of your data. Therefore, checking the geocoding results textually and visually is a good practice

Right-click on the data set. Select “Prepare” and then “Geocode Addresses”. By choosing the tab “Status”, you can find detailed information about how the geocoder has worked its magic. The result is also stored in the metadata repository of Spatial Studio and can be accessed directly via SQL. You can find the SQL statement in “Query all unmatched rows”.

Now, we want to have a closer look at the results.


Status Geocoded Addresses


You get the following information:

  • Geocoded: YES
  • Number of rows (in the data set): 13062
  • Matched (full matches): 12920
  • Ambiguous (matches): 0
  • Partial (matches): 142
  • Unknown: 0
  • Number of failed rows: 0

This is an excellent result. Is there room for improvement? Possibly.

Let´s switch to the tab “Partial Match”. Here we can see those addresses which could not be entirely mapped with the reference data the geocoder relies on.


Check Geocoded Addresses


You can now manually edit the data. Let us do this for one of the addresses. Replace the following values for one of the addresses:

  • HOUSENO => 150
  • POSTAL_CODE => K1A 0T6

Hold on, why should you store ONTARIO for CITY? There is no city with that name Canada. Relax. The geocoder has built-in features to also work if data is missing, wrong, or just misspelled. Hence let´s give it a try.


Update Address Data


Click on the “Actions” icon “Submit row to geocoder” and click on “Refresh” afterward. Now you should see a new value for LONGITUDE too.


Address Data Re-Geocoded


You cannot see that the values for LATITUDE and GC_GEOMETRY have also changed. But we will check it later and find out where this address is located.

Step 4: Update statistics

It is always a good practice for new data sets to have statistics in place, which also contain information about the bounding box around, a rectangle that defines the outer boundaries of your data set (also called layer in relation to maps). The bounding box focuses and centers the map when appropriately displayed.

Return to the “Datasets” menu and right-click on the data set. Select “Update statistics” and confirm with “OK”.


Update Statistics


Step 5: Plot your addresses on a map

Again, right-click on your data set and select “Create project”. This creates a new project with a map as a central component. Drag and drop your data set from the “Dataset” tree on the left onto the map. Voilà, here is your map with the geocoded addresses (look for green circle icons).


Start a New Project


Zoom in on the green circle(s) placed in the west of Canada. The more you zoom in, the more of your addresses are displayed.

Please note that you can change the style of the position icons. You can find descriptions of how to do so in the Spatial Studio documentation.


Addresses in West Canada


Let’s find the address we have changed in step 3. Click on “…” right from the data set in the “Layers List” and select “Zoom to layer”.


Zoom to layer


Now, zoom in on the green circle in the east of Canada.


Addresses in East Canada


The tiny green circle on the underlying base map reveals the position of the address.  You can see it is on Tunney´s Pasture Driveway in Ottawa. So, this looks good, even though we have previously specified an incorrect value for the city name.

If you want to be as accurate as possible, you can again correct the address since you know that the city’s name is Ottawa. Go back to the data set and its geocoding results, edit the address, and re-submit it to the geocoder.


Corrected Address Data


You get a full match now, and the edited address is removed from the list of partial matches.

Go back to your project and refresh the map layer. Not surprisingly, the geocoder was already right on its first attempt since the icon is still in the same place.


Well done. You have geocoded address data for free using Spatial Studio and created a map without writing a single line of code. I hope you enjoyed trying out Spatial Studio on your own.

Here are some resources to learn more about and get started with Oracle Spatial Studio:

Karin Patenge

Senior Principal Product Manager, Oracle Spatial and Graph

Karin Patenge is a Product Manager for Oracle's Spatial and Graph Technologies. She works closely with customers, partners, and tech communities in mostly Europe, and the Middle East. She engages with developers, solution architects, data engineers & analysts in order to bring added value to their projects by integrating spatial and graph capabilities into solution architectures. Karin has a master's degree in Computer Science and has followed the UNIGIS postgraduate study program. She is based in Berlin, Germany, and collaborates closely with Oracle´s Spatial and Graph development teams.

Previous Post

Why Oracle Database runs best on Oracle Linux

Julie Wong | 2 min read

Next Post

Recent New Graph Features in Autonomous Database

Rahul Tasker | 3 min read