ogr2ogr csv conversion with latlon in one column

Question

I have a csv with "lat,lon" in a single column. I am trying to convert it with ogr2ogr to another format, and cannot figure out how to format the VRT file to accept this format.

I cannot do anything other than change the VRT at the moment due to other limitations. I would love to rewrite the csv, but this is the format that I have to deal with for now.

example for formating:

name, date, value, latlon
a, 20130101, 0, "100.8\,40.2"
b, 20130101, 1, "101.2\,40.4"
c, 20130102, 1, "100.2\,41.2"
d, 20130103, 2, "102.0\,40.7"
e, 20130104, 3, "100.6\,41.0"

The data is not a set number of characters, so splitting at a character number does not work right.

a, 20130101, 0, "10.844\,-40.2745"
b, 20130101, 1, "101.2010\,40.411111"
c, 20130102, 1, "-100.27867\,1.24522"
d, 20130103, 2, "-1.04577\,16.745637"
e, 20130104, 3, "100.617417\,-1.0757"

---

Accepted answer actually does not require the intermediate CSV, it can be done straight from original CSV. The INSTR is what I managed to get to work.

<OGRVRTDataSource>
    <OGRVRTLayer name="result">
        <SrcDataSource>test2.csv</SrcDataSource>
        <SrcSQL dialect="sqlite">SELECT name, date, value, SUBSTR(latlon,2,INSTR(latlon,"\,")-1) AS lat, SUBSTR(latlon,INSTR(latlon,"\,")+2,-1) AS lon FROM test2</SrcSQL>
    </OGRVRTLayer>
</OGRVRTDataSource>

Answer 1

You can solve with two chained VRT files and a bit of OGR SQL. The first VRT (e.g. remapped_csv.vrt) is:

<OGRVRTDataSource>
    <OGRVRTLayer name="remapped_csv">
        <SrcDataSource>test.csv</SrcDataSource>
        <SrcSQL>SELECT *, SUBSTR(latlon,2,5) AS lat, SUBSTR(latlon,9,12) AS lon FROM test</SrcSQL>
    </OGRVRTLayer>
</OGRVRTDataSource>

The second VRT (e.g. result.vrt) is:

<OGRVRTDataSource>
    <OGRVRTLayer name="result">
        <SrcDataSource>remapped_csv.vrt</SrcDataSource>
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>WGS84</LayerSRS>
        <GeometryField encoding="PointFromColumns" x="lon" y="lat"/>
        <SrcSQL>SELECT name, date, value, lat, lon FROM remapped_csv</SrcSQL>
    </OGRVRTLayer>
</OGRVRTDataSource>

Here's the final result:

>ogrinfo result.vrt result
INFO: Open of `result.vrt'
      using driver `VRT' successful.

Layer name: result
Geometry: Point
Feature Count: 5
Extent: (40.200000, 100.200000) - (41.200000, 102.000000)
Layer SRS WKT:
GEOGCS["WGS 84",
    DATUM["WGS_1984",
        SPHEROID["WGS 84",6378137,298.257223563,
            AUTHORITY["EPSG","7030"]],
        TOWGS84[0,0,0,0,0,0,0],
        AUTHORITY["EPSG","6326"]],
    PRIMEM["Greenwich",0,
        AUTHORITY["EPSG","8901"]],
    UNIT["degree",0.0174532925199433,
        AUTHORITY["EPSG","9108"]],
    AUTHORITY["EPSG","4326"]]
name: String (0.0)
date: String (0.0)
value: String (0.0)
lat: String (0.0)
lon: String (0.0)
OGRFeature(result):1
  name (String) = a
  date (String) =  20130101
  value (String) =  0
  lat (String) = 100.8
  lon (String) = 40.2
  POINT (40.2 100.8)

OGRFeature(result):2
  name (String) = b
  date (String) =  20130101
  value (String) =  1
  lat (String) = 101.2
  lon (String) = 40.4
  POINT (40.4 101.2)

OGRFeature(result):3
  name (String) = c
  date (String) =  20130102
  value (String) =  1
  lat (String) = 100.2
  lon (String) = 41.2
  POINT (41.2 100.2)

OGRFeature(result):4
  name (String) = d
  date (String) =  20130103
  value (String) =  2
  lat (String) = 102.0
  lon (String) = 40.7
  POINT (40.7 102.0)

OGRFeature(result):5
  name (String) = e
  date (String) =  20130104
  value (String) =  3
  lat (String) = 100.6
  lon (String) = 41.0
  POINT (41.0 100.6)

---

Improved version of remapped_csv.vrt using the dialect SQLite (SQLite >= 3.7.15, because the instr function was added to the SQLite core functions since this version):

<OGRVRTDataSource>
    <OGRVRTLayer name="remapped_csv">
        <SrcDataSource>test2.csv</SrcDataSource>
        <SrcSQL dialect="sqlite">SELECT *, SUBSTR(latlon,2,INSTR(latlon,"\,")-1) AS lat, SUBSTR(latlon,INSTR(latlon,"\,")+2,-1) AS lon FROM test2</SrcSQL>
    </OGRVRTLayer>
</OGRVRTDataSource>

Not tested (because I have GDAL built with SQLite 3.7.9 at the moment), but it should work.

---

This is a working version of remapped_csv.vrt using the trimming approach:

<OGRVRTDataSource>
    <OGRVRTLayer name="remapped_csv">
        <SrcDataSource>test2.csv</SrcDataSource>
        <SrcSQL dialect="sqlite">SELECT *, rtrim(rtrim(latlon,'"+-.0123456789'),'\,') AS lat, ltrim(ltrim(latlon,' "+-.0123456789'),'\,') AS lon FROM test2</SrcSQL>
    </OGRVRTLayer>
</OGRVRTDataSource>