Using shapefiles in PostGIS

It is possible to load multiple 100km² grid tiles of data into the same schema in PostgreSQL. As the shapefiles have the 100km grid letters as a prefix in the filename, these files will go into separate tables in the schema. It will then be possible to view data across tile edges using QGIS or other GI applications which support PostGIS.

Multiple grid tiles loaded into the main data window

The screenshot above shows data from the SU grid tile (styled) and the buildings and roads from the TQ grid tile (un-styled). However, it should be noted that duplicate features will exist across the tile edges as the data is supplied as ‘hairy tiles’ as previously indicated.

Removing duplicate features in PostGIS

As stated above, if using multiple tiles of data in PostGIS, loading them as described, some features will replicated across tile edges loaded in different tables of the same features, e.g. in SU_Buildings and TQ_Buildings. If the data is being used for contextual purposes only, this should not be an issue for the user. However, if the data is being used for any kind of analysis involving counts of features, these duplicates will need to be removed to avoid providing spurious results.

It is possible to remove these features using SQL commands in PostgreSQL itself.

Using SQL Commands

Firstly, create a merged file containing the area required using the merge shapefile feature in QGIS documented earlier. In this example, the roads from TQ and SU will be merged. Once created, these merged shapefiles can be loaded into PostgreSQL using the shapefile loader plugin as described above.

Merged shapefiles loaded into PostgreSQL correctly

Check to see that the merged file has been loaded. This table will contain duplicate features across the tile edges. Using the SQL window in PostgreSQL, a count of the features within the file can be determined using the following command;

SELECT COUNT(id)
FROM openmap.os_openmap_su_tq_roads
;

In the case above the command is querying the table os_openmap_su_tq_roads in the schema openmap used previously in this guide. The count returned will be as follows in this example;

SQL Query output showing 491 349 entries returned

Using the following command, a new table called os_openmap_su_tq_roads_dissolved will be created in the same schema osopenmap;

CREATE TABLE openmap.os_openmap_su_tq_roads_dissolved AS
SELECT id, distname, roadnumber, classifica, featcode, ST_UNION(geom) AS geom FROM openmap.os_openmap_su_tq_roads
GROUP BY 1,2,3,4,5
;

Finally the following command will provide a count of the features in the newly created dissolved table;

SELECT COUNT(id)
FROM openmap.os_openmap_su_tq_roads_dissolved
;

The result of this query is as follows;

SQL Query output showing 491 150 entries returned

The user can see, from running this query that the number of features in the newly created table is less that in the original merged table. This indicates that the duplicate features along the tile edges have been removed. It will now be possible to load the dissolved table into QGIS and carry out the required analysis.

Using a graphical method in QGIS

An alternative way to do what has been described above would be to merge the required shapefiles together and de-duplicate using QGIS as described earlier in this document. The user will then have a set of de-duplicated shapefiles which can then be loaded into PostgreSQL/PostGIS and displayed in QGIS using the methods described previously.