Merging Tables and Dropping Geometry

Maintenance, Reinstatement and Special Designation can have different geometry (point, line or area) or no geometry. In a non-spatial database this is not a problem and they can easily be merged into one. A spatial database will not allow mixed geometry types. It is suggested to create a new table, dropping the geometry. Link to geometry can then be preserved if required.

What You Will Need

Data

• RAMI Specification

• Maintenance

• Reinstatement

• Special Designation

Software

• Translation software is required to convert data from GML into chosen format. FME workbenches are available on GitHub to help with this.

• Data storage – once translated the data will need to be stored in a format compatible with your systems, this may be on your file system as a shape file, geopackage or tab file or in a database.

• GIS or web application to manipulate and display your results.

Steps to Success: Merging Tables and Dropping Geometry

Create table statement with UNION ALL:

CREATE TABLE highways_nov.maintenance AS
SELECT id, maintenanceresponsibility, partialreference, highwayauthorityname, highwayauthorityid, maintenanceauthorityname, maintenanceauthorityid, referencetostreetusrn, locationdescription FROM highways_nov.maintenance_line
UNION ALL
SELECT id, maintenanceresponsibility, partialreference, highwayauthorityname, highwayauthorityid, maintenanceauthorityname, maintenanceauthorityid, referencetostreetusrn, locationdescription FROM highways_nov.maintenance_area
UNION ALL
SELECT id, maintenanceresponsibility, partialreference, highwayauthorityname, highwayauthorityid, maintenanceauthorityname, maintenanceauthorityid, referencetostreetusrn, locationdescription FROM highways_nov.maintenance_point
UNION ALL
SELECT id, maintenanceresponsibility, partialreference, highwayauthorityname, highwayauthorityid, maintenanceauthorityname, maintenanceauthorityid, referencetostreetusrn, locationdescription FROM highways_nov.maintenance_nogeom