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.
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.
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.
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.
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
There are several methods to import all_open_names.csv in PostGIS:
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.
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:
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.