This page provides a step by step guide to using GO Loader to load OpenStreetMap data into a relational database. It is recommended to have created a separate OpenStreetMap project and split your data into appropriate file sizes.
- GO Loader v1.7 onwards will load OpenStreetMap data - you can download this version from the Customer Portal.
- Use the OpenStreetMap Project Template to create your OpenStreetMap project.
- (Optionally) Split your OpenStreetMap data file
- Create an OpenStreetMap database user.
- We recommend for sample OpenStreetMap loads to leave the <Use Default> option for all the tablespaces.
- For larger loads of one or more countries, to create and specify individual tablespaces to hold data, and the individual index types separately.
- In addition to this, for the 5 tables that the OSM Tool populates, we recommend that additional tablespaces are created to store data, and the individual index types separately.
This is explained in more detail throughout this workflow guide.
Working with OpenStreetMap
Step 1. Open the OpenStreetMap Project:
Review the Project Description so you understand what the project contains.
OpenStreetMap Plugins have been applied to this project and should not be removed.
The following tabs have already been set up as standard in the GO Loader standard project so can be ignored by the user.
#1 - Project Management
#2 - GML Schema
#3 - GML Filter
#4 - Translation Policy
#5 - Translations
#6 - Tables **Though you can edit this to include additional OpenStreetMap Attributes as required - see OSM attributes
#8 - Spatial Reference System
Image displaying the Project Management Tab in GO Loader.
Step 2. Edit your OpenStreetMap Attributes
This allows you to identify the following objects, classified as:
You may want to style specific OpenStreetMap attributes in your GIS, which extends the attributes included in the project template supplied with GO Loader.
To do this you need to include the tag_name as a new column in your OpenStreetMap project, so the OpenStreetMap tool knows what attributes to pull out from the data.
Each OpenStreetMap 'tag' comes as a tag_key with a tag_value e.g.
You can open your .osm file in an xml reader like Altova XML Spy to reivew the tags used.
There are websites that monitor and report on the most popular OpenStreetMap tags used in the data, or are proposed:
You can use these to review the tags you might like in your OpenStreetMap project - to then display in your GIS product.
For example, lets suppose you want to add the tag 'historic' to your project. This will pull out all historic tag values and add them to a column in one of the above tables, based on the geometry type (point, line or polygon) that the tag applies to.
1. Go to the Tables tab.
2. Select the relevant table you want to add a new attribute to - There are 4 tables you may need to add attribute columns to: (see also OpenStreetMap tables)
Editing your OpenStreetMap project attributes
In this case the historic tag has been used for point, lines and polygons according to the tag_watch website:
tag_watch results for 'historic' tag
Lets start with Points:
- Select the OSM_NODE_POINT table
- Click to define a new column - this will add a new column at the bottom of the table's column list
Add a New column in GO Loader
- Enter a column name - type the tag_key (historic in this example).
- Save your project
- Add this attribute to any other relevant tables (in this case, 'historic' applies to all three geometries, so all 4 tables)
- Repeat this process with additional attributes as required.
Once you are satisfied with the attributes in your project, then you can continue to connect to your database ...
Step 3. Loader Configuration - Connect to your database:
Image displaying the Loader Configuration Tab in GO Loader - Oracle Database.
Image displaying the Loader Configuration Tab in GO Loader - PostgreSQL database.
Step 4. Loader Configuration - Tablespace Naming Configuration:
When loading small sample OpenStreetMap data, leave the <Use Default> option for all of the tablespaces.
We recommend that if you are loading one or more national datasets then we recommend using tablespaces to hold data, and the individual index types separately, though you may choose to load the indices into one tablespace (INDX).
- Specify the Data and Index tablespace names in the Tablespace Naming Policy sub-tab of the Loader Confirguration Tab, an example is shown in the image below.
- This policy will be applied to all tables from tab #9 (Tablespace & Partition).
Image displaying the Loader Configuration, Tablespace Naming Policy sub-tab in GO Loader.
Step 5. Spatial Reference System (Preset):
The Default spatial reference 4326 is used for Oracle, PostgreSQL and SQL server 2008 so you do not need to edit tab #8.
Image displaying the Spatial Reference Tab in GO Loader.
Step 6. Tablespace Configuration:
Apply the Tablespace Naming Configuration you generated in tab 7's sub tab, at the database level.
- Click at the Database level and then 'Apply the Tablespace Naming Policy to All' icon
- Click + to expand the database content to check the tablespace policy was applied as required.
Image displaying the Tablespace & Partition Tab in GO Loader.
For the 5 tables that the OSM Tool populates:
We recommend that additional tablespaces are created to store data, and the individual index types separately, though again, you may wish to keep the indices together in one tablespace.
Define this using the Tablespace dropdown.
Step 7. Checkpoint:
- Review the checklist and save your project once you have your configuration set up.
Image displaying theCheckpoint Tab in GO Loader.
Step 8. Create Tables:
Read about the OpenStreetMap tables that are created, making note of the ones to visualise in a GIS.
- Click on the Create All Tables button to create the list of tables displayed in the top window.
- GO Loader will display a dialog window to confirm table creation. Click OK once it has completed.
Image displaying the Create Tables Tab in GO Loader.
Step 9. File Loading:
- Select your OpenStreetMap file(s) to load using the Select File button.
- Click Load Files and review the Load Progress window.
Image displaying the Load Files Tab in GO Loader.
Step 10. Create Non-Spatial Indices:
You now need to create all of the NON-SPATIAL indices.
- From tab # 13: Create Indices use the Selection Shortcuts dropdown (bottom right hand corner)
- Choose Primary Key Indices - the IPK will be highlighted.
- Next, choose the Foreign Key Indices - the IFK will be highlighted as well.
- Click Create Selected Indices button.
Select the Spatial Indices, then click on Invert Selection - this will select all indices EXCEPT for the spatial index.
Image displaying the Create Indices Tab in GO Loader.
- GO Loader will display a dialog window to confirm Index creation. Click OK once it has completed.
Now you have loaded the data into a relational database, created non-spatial indices, you can now create spatial geometry using the OpenStreetMap Tool.