Splitting data in each record of attribute field into multiple records/points using QGIS?

Question

I have a point data layer with an attribute named FACILITYNAME that combines multiple records, like 'Office 123/Office 154/Office 191'. I want to use the slash to split each cell into multiple records that duplicate the other attribute data from the parent record (e.g. lat/long). I need to do this because I'm trying to join another dataset by FacilityName.

Answer 1

You'll need a database for this, and since PostgreSQL can handle the text portion, and PostGIS can handle the geometry, you should definitely set up this system.

In PostgresSQL, the function regexp_split_to_table() can split your column based on the '/' delimiter.

Here's some SQL that mocks up your data and uses the values in a temp table to break them apart:

select
 --does the splitting of the data.FacilityName column in the subquery
 -- on the '/' delimitter
  regexp_split_to_table(data.FacilityName, '/') as FacilityNameSingle
    , geom
from (

       select
           'Office 123/Office 154/Office 191' :: varchar as FacilityName
         , '010100101001010'                             as geom
     ) as data

And here's the results:

facilitynamesingle  geom
Office 123          010100101001010
Office 154          010100101001010
Office 191          010100101001010

Answer 2

If you prefer Python code, copy-paste the code into Python Editor in QGIS and specify your file path. (To open Editor, click "Python Console > Python Editor" button)

# in_layer = QgsVectorLayer("path/to/shapefile.shp", "in_layer", "ogr")
# or
in_layer = iface.activeLayer()

crs = in_layer.crs().authid()  # get crs
idx = in_layer.fieldNameIndex('FACILITYNAME') # get field index

# create memory layer
out_layer = QgsVectorLayer("Point?crs=" + crs, "out_layer", "memory")
out_dpr = out_layer.dataProvider()

out_layer.startEditing() # start editing

# copy in_layer attribute schema to out_layer
in_fields = in_layer.dataProvider().fields()
out_dpr.addAttributes(in_fields.toList())

out_feat = QgsFeature()  # create empty feature
for in_feat in in_layer.getFeatures():
    attrs = in_feat.attributes()  # get in_feat attributes

    # clone attributes (to change facility name in the loop)
    out_attrs = list(attrs)
    for facility in attrs[idx].split('/'): # split field by '/'       
        out_attrs[idx] = facility
        out_feat.setAttributes(out_attrs) 

        # copy in_feat gemoetry
        out_feat.setGeometry(in_feat.geometry()) 

        # add out_feat to out_layer
        out_dpr.addFeatures([out_feat])    

out_layer.commitChanges()  # save and stop editing

# Add `out_layer` to layer tree
QgsMapLayerRegistry.instance().addMapLayer(out_layer)

Then save as out_layer.