3

I inherited a .gpkg (no CSV or shapefile) in which the attribute table has a single column containing the relevant non-spatial data. The contents (string) are set up with a <table><ul><il>CONTENTS</li> divider which results in it presenting in bullet points when you hover your mouse over.enter image description here

I need to have each category (current displayed as a bullet points) in its own column. I would also like to remove the bullet point formatting entirely.

enter image description here

As the contents of the column is longer than 255 characters, I cannot export it as a CSV to manipulate then bring back into QGIS. In the field calculator I tried to use '''string_to_array''' with no luck. Because the number of characters in each category is inconsistent '''substr''' is clunky to divide the contents.

Each row looks like:

<ul><li>ship_Vessel_SubType: Cargo_Bulk</li><li>track_direction: 173 degrees</li><li>track_first_passage: None</li><li>track_initial_destination: VANCOUVER</li><li>track_initial_status: At Anchor</li><li>track_start_datetime: 2014-01-04 00:09:05</li><li>track_end_datetime: 2014-01-04 00:48:56</li><li>ship_mmsi: 271000666</li><li>ship_Flag_State: Turkey</li><li>ship_Gross_Tons: 28798.91845</li><li>ship_Deadweight_Tons: 47498.76457</li><li>ship_Length: 190</li><li>track_length_nmi: 7.1</li><li>track_speed_kts: 10.7008343652</li><li>track_n: 3</li></ul>

(it automatically formats to bullet points).

Ideally, ship_Vessel_SubType, track_direction, track_frist_passage, track_initial_destination, etc. are each their own columns headings with no bullet formatting.

As I didn't set the data up, I don't have it in a format where I can reimport it to qgis.

How can I work with an attribute table formatted in this manner?

1
  • 1
    Have you manually created the new columns/fields? If not, that's probably the best place to start. Then the question becomes a lot simpler - basically, "how do I calculate the value for the 'track_n' column from this text in the 'description' column?". The answer is still not trivial, of course. Jul 22, 2022 at 3:32

1 Answer 1

2

Use the field calculator and regular expressions (or regex). Create a new field "track_direction" as integer, use the calculator, as formula use

to_int(regexp_substr( "description" , 'track_direction: (\\d+) degrees'))

This will extract a substring from the column "description" using the regular expression 'track_direction: (\\d+) degrees'. The double backslash is quoting necessary for QGIS, not part of the regex.

The regex says "find the string 'track_direction: ', then find and capture one or more digits, then find the string ' degrees'. Return the captured characters".

Then repeat that for all your fields. As this is a one-off conversion doing this manually for each of the 15 field is OK.

Note that you have to change the data type for some of your fields and then also remove the to_int call, or replace with to_real or others.

See https://dev.to/sandricoprovo/an-intro-to-regex-s-in-under-5-minutes-6lh for a regex introduction.

See https://regex101.com/ for a online regex tester, which is very handy to use. https://regex101.com/r/zXGqqp/1 is a simple start with some data added.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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