Solved: I'm not sure why, but it works fine if I specify
--config SHAPE_ENCODING "ISO-8859-1" in my ogr2ogr command. For whatever reason, this allowed ogr2ogr to go from the original TigerLine shapefile, as-is, directly into a MySQL database. My final ogr2ogr instruction, in full:
ogr2ogr --config SHAPE_ENCODING "ISO-8859-1" -f "MySql" MySql:"usa_basemap,host=127.0.0.1,user=myUser,password=myPass,port=3306" -lco engine=MYISAM "C:/path/to/data/tl_2012_us_county.shp" -nln county -nlt "geometry" -s_srs EPSG:4269 -t_srs EPSG:3857
Background: I was finally able to use ogr2ogr to import the TigerLine 2012 USA Counties shapefile into MySQL, but only after using QGIS to save the shapefile with a new encoding value (UTF-8).
I'm wondering: What can I do differently, right inside the commandline with ogr2ogr, to avoid having to involved QGIS in the workflow? (Nothing against QGIS, it's just that I want to be able to automate this..)
Here's what I did before involving QGIS:
1) Changed the MySQL max_allowed_packet size as is warned might be necessary on the OGR MySQL driver page.
In MySQL Workbench, I set the new parameter value like this:
SET GLOBAL max_allowed_packet=1073741824;
2) Next, I tried to import it into MySQL with ogr2ogr like this:
ogr2ogr -f "MySql" MySql:"basemap,host=127.0.0.1,user=myUser,password=myPass,port=3306" -lco engine=MYISAM "C:/path/to/data/tl_2012_us_county.shp" -nln county -nlt "geometry" -s_srs EPSG:4269 -t_srs EPSG:3857
But that bombs with an error that spills outside of the buffer, it ends as shown below, note the appearance of the special chars, which are lost.
[....hugely....truncated....console....output....] 322122,-7366573.8710948359 2062754.2910927597))',1) , '72', '045', '01804502', ' 72045', 'Comer�o', 'Comer�o Municipio', '13', 'H1', 'G4020', '490', '41980', 'A' , 73557129, 319735, '+18.2250402', '-066.2202984')
It's worth noting that after hitting this brick wall and Googling around, I tried the tips suggested by Mateusz Łoskot in this discussion; to restate the salient portion:
*Windows Command Prompt can work with UTF-8 characters if you change codepage to UTF-8:
0) Open new prompt (cmd.exe)
1) Change font to Lucida Concole
3) chcp 65001*
But this didn't overcome the obstacle.
Finally, wondering if QGIS could help, I used QGIS to export the shapefile, specifying UTF-8 as the character encoding, then the ogr2ogr command I provided above worked. Bingo. Now I have "Comerío Municipio" in my database..
..but what gives?
So my question: Is QGIS doing something I can add to my ogr2ogr instruction that automatically resolves the special characters issues I encountered so I can automate this task in the future and not have to manually involve QGIS?