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”.
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.
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:
Let´s fix the issues one by one. Start defining the key column by clicking “Go to Dataset Columns”.
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”.
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”.
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”.
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.
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.
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.
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:
◉ LONGITUDE of type NUMBER
◉ LATITUDE of type NUMBER
◉ GC_GEOMETRY of type SDO_GEOMETRY
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.
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.
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:
◉ STREET => TUNNEY’S PASTURE DRIVEWAY
◉ HOUSENO => 150
◉ POSTAL_CODE => K1A 0T6
◉ CITY => ONTARIO
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.
Click on the “Actions” icon “Submit row to geocoder” and click on “Refresh” afterward. Now you should see a new value for LONGITUDE too.
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”.
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).
Caption
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.
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”.
Now, zoom in on the green circle in the east of 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.
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.
Source: oracle.com
Posts
0 comments:
Post a Comment