# 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:

```sql
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
```