ogr2ogr CSV conversion with two points into column names

Question

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)

Answer 1

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

Answer 2

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)

Answer 3

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