0

I have a CSV with ":" in all column names. I am trying to convert it with ogr2ogr to another format. I don't need all fields

example for formating:

#0:imgID,1:camSN,2:lat,3:lon,...,57:fields
000000001,201002422,4.8192835254,-75.7043506327,...
000000003,201002422,4.8192743299,-75.7042594643,...
000000004,201002422,4.8192692652,-75.7042137224,...

where I use:

ogrinfo Outfile.csv -dialect SQLite -sql "SELECT substr(1:camSN , 2) as serial FROM Outfile"

I get:

INFO: Open of `Outfile.csv'
      using driver `CSV' successful.
ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(SELECT substr(1:camSN , 2) as serial FROM Outfile):
  near ":camSN": syntax error

Where I use:

ogrinfo Outfile.csv -dialect SQLite -sql "SELECT 1:camSN,2:lat,3:lon FROM Outfile"

I get:

INFO: Open of `Outfile.csv'
      using driver `CSV' successful.
ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(SELECT 1:camSN,2:lat,3:lon FROM Outfile):
  near ":camSN": syntax error

where I use:

ogr2ogr -s_srs EPSG:4326 -t_srs EPSG:4326 -oo X_POSSIBLE_NAMES=lon* -oo Y_POSSIBLE_NAMES=lat* -f "ESRI Shapefile" Informe.shp Outfile.csv -dialect SQLite -sql "SELECT camSN,lat,lon,height,roll FROM Outfile"

I get only Informe.dbf and Informe.prj NOT shapefile (Informe.shp)

  • The leading numeric would make it invalid in my eyes. Why can't you edit the first row to remove the number:? – Vince Mar 17 at 14:04
  • it is generated dynamically, at the moment I do not have control. – 120m4n Mar 17 at 14:58
  • Filtering the first row is a trivial task, and can be done is scores of languages. – Vince Mar 17 at 15:17
  • The substring command would be applied to the column values, not the column header. The hammer I hit all of these nails with it python and (geo)pandas. But e.g., awk, sed could probably get you there too – Paul H Mar 17 at 15:35
  • @120m4n Did you've try to do as in my reply gis.stackexchange.com/a/315860/466? – aborruso Mar 21 at 17:35
2

It is possible to use field names like 1:camSN in the SQL databases but such field names must be handled as "delimited identifiers" and they must be enclosed between double quotation marks in all SQL queries. For example https://en.wikibooks.org/wiki/SQL_Dialects_Reference/Data_structure_definition/Delimited_identifiers gives more details.

So in order to make your query to work you must make ogr2ogr to use query SELECT substr("1:camSN", 2) as serial…. Because the -sql parameter of ogr2ogr must also appear between double quotes the inner double quetes must be escaped. If you run ogr2ogr from Windows command line then backslash is used as the escape characrer and the working command would be as follows:

ogrinfo csvtest.csv -dialect SQLite -sql "SELECT substr(\"1:camSN\", 2) as serial FROM csvtest"
INFO: Open of `csvtest.csv'
      using driver `CSV' successful.

Layer name: SELECT
Geometry: None
Feature Count: 3
Layer SRS WKT:
(unknown)
serial: String (0.0)
OGRFeature(SELECT):0
  serial (String) = 01002422

OGRFeature(SELECT):1
  serial (String) = 01002422

OGRFeature(SELECT):2
  serial (String) = 01002422
0

A recipe (tried)

  • Clean prefix with (a bit too generic maybe but works for your case)

    sed -i "1s/[0-9:]*//g" infile.csv
    

Why cleaning? Because no choice in particular if using database e.g https://stackoverflow.com/questions/15917064/table-or-column-name-cannot-start-with-numeric or you can double quote the column name)

  • Then, filter your columns

    ogr2ogr -f "CSV" intermediate.csv infile.csv -dialect SQLite -sql "SELECT camSN,lat,lon FROM infile"
    

Why? Because I was encountering the same issue as you: mixing Sqlite behaviour with dialect SQlite on the top of CSV driver was a "bad idea"

  • Then, create your shapefile

    ogr2ogr -s_srs "EPSG:4326" -t_srs "EPSG:4326" -oo X_POSSIBLE_NAMES=lon* -oo Y_POSSIBLE_NAMES=lat* -f "ESRI Shapefile" informe.shp intermediate.csv
    

Why? only take the CSV driver behaviour and it works contrary to previous command you provided that was mixing "things" (e.g SQLite syntax and CSV syntax)

0

I have no problem using

ogr2ogr -s_srs EPSG:4326 -t_srs EPSG:4326 output.shp -oo X_POSSIBLE_NAMES="3:lon" -oo Y_POSSIBLE_NAMES="2:lat" input.csv

As input I have used

#0:imgID,1:camSN,2:lat,3:lon
000000001,201002422,4.8192835254,-75.7043506327
000000003,201002422,4.8192743299,-75.7042594643
000000004,201002422,4.8192692652,-75.7042137224

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.