6

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. I have been pulling my hair out all day, and have yet to figure out a viable solution.

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"

Any ideas would be greatly appreciated.

edit for clarification: 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"

EDIT: 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>
  • Consider that the SQL query using the trimming approach (last edit in my answer) should work with GDAL >= 1.10 (with SQLite and SpatiaLite support) without the need to have a more recent SQLite support and the availability of the instr function. – Antonio Falciano Dec 6 '13 at 17:31
6

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)

EDIT

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.

LAST EDIT

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>
  • Looks great, but what if I do not know the exact length of the lat/lon? So one goes to 3 decimal places and another goes to 5? – eseglem Dec 5 '13 at 20:51
  • Also there could be a negative or anywhere up to three digits before the decimal. "-1.12341234\,80.1234" vs "134.1186\,-6.89467" – eseglem Dec 5 '13 at 21:12
  • 1
    After your updates, it seems that the only way to solve your problem is to write a Python script with a robust regex management of latlon string. Consider also this question: gis.stackexchange.com/questions/77897/… Good luck! – Antonio Falciano Dec 5 '13 at 21:32
  • 1
    And what happens if the string doesn't contain valid coordinates? ;) Although, I've just updated the answer considering your updates. It should work in theory, but I don't have the right configuration at the moment in order to test it. – Antonio Falciano Dec 6 '13 at 9:54
  • 1
    I found another solution without using regex in the last update. Hope this helps. – Antonio Falciano Dec 6 '13 at 11:01

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.