3

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.

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
1

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.

  • Thank you! I tried this and it created a new layer but without any data. I don't yet know enough python to troubleshoot. But I think this would probably work. – Tan May 2 '18 at 0:48
  • Ok then. I editted the code. Copy-paste the code and select the layer (so it's active layer now) then run the script. – Kadir Şahbaz May 2 '18 at 0:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.