As a part of migrating to Open Source GIS, the Newcastle City Council has commissioned us to create a user friendly gazetteer plugin.

In this post, we will import the OS Open Names and configure the Discovery plugin to use the data.

In the following sections, we are going to set up a PostGIS database and import the OS Open Names, freely available from here.

To use the data with the Discovery plugin, we need to first set up a PostGIS database and load the data inside the geo-databases.

If you have an existing Postgresql/PostGIS server, you can skip the next section.

Postgresql/PostGIS installation and configuration

Installation

For MS Windows users, download and install Postgresql from here (32-bit) or here (64-bit) install the software.

During the installation, select the StackBuilder to install PostGIS, under Spatial Extensions.

If your StackBuilder fails to download PostGIS (in case your proxy server blocks it), you can download and install it manually.

Preparing the database

Now that installation is successful, create a new database as osdata. Make sure you add PostGIS extension to your database. You can do that by simply running the following command in the Query editor:

CREATE EXTENSION postgis;

Create osopennames as a new schema under osdata.

Preparing data

In this example, we are focusing on the OS Open Names comes (for Great Britain only) which comes in a zip file containing several CSV files.

If you’d like to use address data for your part of the world, you can visit the OpenAddress website for the global coverage. Note that the CSV files for this dataset come with the vrt files, so you can skip the vrt creation and directly use ogr2ogr.

Once the zip file extracted, there are 2 folders, one containing the header file (DOC) and the other containing the csv files (DATA).

To be able to import all the csv files in PostGIS, we can merge all the files including the header file.

You can move the header file (OS_Open_Names_Header.csv) from the DOC folder. To ensure, the file will appear first during the merge process you can rename it to 1_OS_Open_Names_Header.csv.

You can use Windows command prompt to merge the files. The following command merges all csv files to all_open_names.csv:

 copy /b \*.csv all_open_names.csv

Loading data in PostGIS

There are several methods to import all_open_names.csv in PostGIS:

1. Adding it as a delimited text layer in QGIS and then load it in PostGIS
2. Importing it in PostGIS as a CSV and then using PostGIS’ geometry to create points
3. Using virtual layer and OGR2OGR library

In the example below, we explore the third option.

To create a virtual vector layer from your csv file, open a text editor, copy and paste the following lines and save it as all_open_names.vrt under DATA folder along with your csv file.

	<OGRVRTDataSource>
    	<OGRVRTLayer name="all_open_names">
        	<SrcDataSource relativeToVRT="1">all_open_names.csv</SrcDataSource>
        	<GeometryType>wkbPoint</GeometryType>
        	<LayerSRS>EPSG:27700</LayerSRS>
        	<GeometryField encoding="PointFromColumns" x="GEOMETRY_X" y="GEOMETRY_y"/>
    	</OGRVRTLayer>
	</OGRVRTDataSource>

In fact, you can use the virtual vector layer to merge all your csv files and skip the previous section!

You can then use ogr2ogr command from the OSGeo4W shell to import it to your PostGIS:

    ogr2ogr -append  -a_srs EPSG:27700 -f "PostgreSQL" PG:"host=127.0.0.1 user=postgres dbname=osdata password=postgres active_schema=osopennames" -nln osnames all_open_names.vrt

Note: if you are using the OpenAdress data make sure you assign the right EPSG (4326) in the above command.

Configuring Discovery plugin

First you need to install the plugin from the QGIS plugin repository.

Once the plugin installed, you should have a new toolbar. Click on from the toolbar to open the configuration:

1. For Connection, select OS Data
2. For Shema, select opennames
3. For Table, select osnames
4. For Search Column, select Name1
5. Select the option to Echo Search Column in Results
6. For Display Columns select the followings:
   1. Name2
   2. DISTRICT_BOROUGH
   3. POSTCODE_DISTRICT
   4. COUNTRY
7. For Geometry Column, select geom (or other columns depending on your QGIS or OGR versions)
8. For BBOX Expresssion, type the following:

CASE
	WHEN "MBR_XMIN" IS NOT NULL
THEN
	MBR_XMIN || ',' ||
	MBR_YMIN || ',' ||
	MBR_XMAX || ',' ||
	MBR_YMAX
END

And you should be able to start using the gazetteer plugin after pressing OK.

For performance enhancement and other tips visit this page.

You may also like...

Mergin Maps, a field data collection app based on QGIS. Mergin Maps makes field work easy with its simple interface and cloud-based sync. Available on Android, iOS and Windows.