# 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 prefixed with 100km grid letters in their filename — 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.

A screenshot of the left hand side menu showing merged shapefiles loaded into PostgreSQL correctly — 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; ```sql 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; ```sql 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; ```sql 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. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.os.uk/os-downloads/products/maps-and-imagery-portfolio/os-openmap-local/os-openmap-local-getting-started-guide/using-shapefiles-in-postgis.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.