1

How to import a data file in which both longitude and latitude are combined in one column? e.g. 52.2299571,21.0099775

  • 6
    How about splitting that column before importing? Excel is more than capable of doing so. – Erik Aug 30 at 9:21
  • @Erik Thank you- this it not an option in this specific situation. – Tomasz Wojciechowski Aug 30 at 11:59
  • 2
    Then you must split it using the field calculator, save the result and load that. Kind of a hassle. – Erik Aug 30 at 12:04
  • What format is your data file? If it is raw text you could add a delimited text layer using the CSV format since you longitudes and latitudes have a comma delimiter. – Dirwin Aug 30 at 14:03
3

When importing a delimited text file into QGIS, there's an option to choose multiple custom delimiters. Choose this option. Select both "Comma" and whatever delimiter is used by the format that your file is in.

enter image description here

If other columns in your data file also have commas, they will be split into separate columns. You can fix this in one of two ways:

  • Use the Field Calculator to combine those columns, or
  • Import the data twice, then link the two layers with a table join (this method requires a unique, shared attribute).
    1. The first time, import it as described above.
    2. The second time, choose only one delimiter (whichever one the file uses between columns). Import it without geometry by choosing the option "No geometry (attribute only table).
2

You could just use left() and right() to create new columns with the Field Calculator.

left(
    "xy",
     (strpos( 
     "xy",
     ',' ) - 1)
)

The above extracts the number before the comma (by extracting all characters up until the index of character ',' minus 1). The below extracts the number after the comma:

right(
    "xy",
     (strpos( 
     "xy",
     ',' ) - 1)
)

You could add those as virtual columns if you don't want to change your original file, and then run "Points layer from table" in processing to create a new point file from this.

2

First of all, simply try the approach that was suggested by @Erik, deploy Excel with the combination of FIND(), LEFT()/RIGHT() and LENGTH(). Afterwards, import into QGIS.

Alternatively, try the following workflow

Tested on QGIS 2.18 and QGIS 3.4

Let's assume there is a .csv-file "points" with wrong coordinates knitted together as x,y, see image below.

csv

Simply drag&drop it into QGIS, so it can appear in the 'Layers Panel', like this

csv_in_qgis

Then proceed with a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...

With the following Query, it is possible to plot locations based on coordinates (even with knitted coordinates) on the map

SELECT p.*,
make_point(
  (CASE
    WHEN p.xy LIKE '%,%' THEN substr(p.xy,0,instr(p.xy, ','))
    ELSE 0
  END),
  (CASE
    WHEN p.xy LIKE '%,%' THEN substr(p.xy,instr(p.xy, ',')+1)
    ELSE 0
  END)
),
  (CASE
    WHEN p.xy LIKE '%,%' THEN substr(p.xy,0,instr(p.xy, ','))
    ELSE 0
  END) AS x,
  (CASE
    WHEN p.xy LIKE '%,%' THEN substr(p.xy,instr(p.xy, ',')+1)
    ELSE 0
  END) AS y
FROM "points" AS p

In case if you possess the exact CRS, you can adjust the above query with a corresponding EPSG code, by means of SetSRID().

SELECT p.*,
setsrid(make_point(
  (CASE
    WHEN p.xy LIKE '%,%' THEN substr(p.xy,0,instr(p.xy,','))
    ELSE 0
  END),
  (CASE
    WHEN p.xy LIKE '%,%' THEN substr(p.xy,instr(p.xy,',')+1)
    ELSE 0
  END)
),31469),
  (CASE
    WHEN p.xy LIKE '%,%' THEN substr(p.xy,0,instr(p.xy,','))
    ELSE 0
  END) AS x,
  (CASE
    WHEN p.xy LIKE '%,%' THEN substr(p.xy,instr(p.xy,',')+1)
    ELSE 0
  END) AS y
FROM "points" AS p

Check the output

result

and the updated attribute table

new_at

P.S. QGIS likes UTF-8


References:

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.