How to separate one column with latitude and longitude into two columns. Lat/long are not separated by anything

Question

I'm a GIS novice. I have a CSV file that contains the latitude and longitude data for a small region in the northeast U.S. The data appear in one column appearing as one uninterrupted string of text in each field. I need to separate this data into two columns.

Each field is formatted like this, with all fields in this column having the same number of digits:

   xy4285021070865890

Two other issues: each field begins with "xy," and the longitude (or "y") should have a minus sign in front of it because the location for this map is within the western hemisphere.

How can I separate this column into two columns so that 1) the "xy" disappears and 2) the longitude includes a minus sign? OR will QGIS3 be able to automatically read and correctly interpret this column because of placement of "xy" before each number?

I've searched answers to this "one-column to two-column" issue, but all the questions referred to lat-long figures separated by a comma in their respective fields. There were some references to use of the field calculator - but I don't know how to use that, either.

Answer 1

You will need to learn how to use field calculator. See this section of the manual to start.

Then, your field is a string (text) field from which you need to extract the relevant substrings for the x coordinate and y coordinate and convert them into numbers.

The expression substr(string,startpos,length) will let you extract length characters starting at startpos from string. You will then need to convert that to a number, using the to_real(...) function. For instance, if the attribute with the xy strings is called XYString, then the following would return the number represented by the 8 numbers after the y, so 42850210 for the example you gave.

to_real(substr("XYString",3,8))

Note you will have to figure out what coordinate system the numbers are in, and how many digits for x and y, and you may need to do further post processing e.g. if there is an implied decimal point.

As advanced operations, which I'm afraid may not make sense to you right now but may be useful to you or others later, you might first split the string into the x and y, then recombine into a geometry (point) in WKT format, and have QGIS interpret it that way. For instance, as something you'll need to modify to fit your purposes, something like:

'POINT(-' || substr("XYString",3,9) || ' ' || substr("XYString",12,7) || ')'

would repack your strange xy string as POINT(-428502107 0865890) which could then be interpreted by QGIS as a WKT geometry. But you'll need to figure out what the coordinate system (CRS) is and probably adjust significant digits. In that example, you'll see I snuck in a - to make the x coordinate negative as you requested.