Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
    
You should mention which QGIS/GDAL version you are using. QGIS 1.7.3 did the encoding correct, 1.8.0 is buggy, and 1.9.0 has the chance to do it right again. –  Andre Joost Dec 15 '12 at 20:24
    
That's fair---I'm using Q 1.8. However, QGIS was the solution, not the problem, and I was hoping to accomplish whatever Q did directly in ogr2ogr. But @steko's answer down there looks promising. –  elrobis Dec 15 '12 at 22:10
add comment

2 Answers 2

up vote 2 down vote accepted

Quoting from the GDAL documentation for the Esri Shapefile driver:

An attempt is made to read the LDID/codepage setting from the .dbf file and use it to translate string fields to UTF-8 on read, and back when writing. LDID "87 / 0x57" is treated as ISO8859_1 which may not be appropriate. The SHAPE_ENCODING configuration option may be used to override the encoding interpretation of the shapefile with any encoding supported by CPLRecode or to "" to avoid any recoding. (Recoding support is new for GDAL/OGR 1.9.0)

Depending on which GDAL/OGR version you are using, ogr2ogr may be trying to translate your data to UTF-8 or not doing nothing at all.

So you would do either:

ogr2ogr --config SHAPE_ENCODING "UTF-8" -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

or

ogr2ogr --config SHAPE_ENCODING "" -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

Finally, check that your MySQL database is using UTF-8 and not Latin-1.

share|improve this answer
1  
Ok man, you led me to the right place so you get the green check mark. Although--somewhat counter-intuitively--the config option that ultimately worked was this: --config SHAPE_ENCODING "ISO-8859-1" Note that it doesn't have an = between the key and the value, also that it didn't work with UTF-8, but with ISO-8859-1. But thanks a ton for your help, I'll post the complete solution in the question. Maybe someday, someone will take an interest and describe why I needed to force-feed the ISO-8859-1 encoding. :) –  elrobis Dec 15 '12 at 23:23
1  
@elrobis Feel free to read the metadata that came with that shapefile =) I'm sure whoever made it will appreciate that. It's the .shp.xml in the zip. Have a close look at the very top, what does encoding say? That's the encoding of that shapefile. You need to import to a db with that encoding, otherwise as seen in the gdal docs, it will try to force it to UTF-8 which will lose special chars that UTF-8 encoding doesn't handle. –  SaultDon Nov 17 '13 at 18:15
1  
Some shapefile providers are really generous and include a .cpj file which has, in plain text, the shapefile encoding that the attributes are stored in. You can always open that in a text editor to see it. See gis.stackexchange.com/a/3663/1297 –  SaultDon Nov 17 '13 at 18:17
    
@steko Your first option is what's causing the error, the second option could fix it by not translating from the source encoding of "ISO-8859-1" to "UTF-8". GDAL would try to find it automatically and use the existing encoding, but to be safe, the OP has specified the exact encoding needed. –  SaultDon Nov 17 '13 at 18:23
    
@SaultDon, thanks for the extra insight here. Just for clarification, does the "encoding" in the XML header truly describe the shapefile itself, or is that just a coincidence? Or said differently, can I count on that? If nothing else it'd always be worth a try! This was definitely an odd one to crack, and I'm glad to have some more ammo for the future. –  elrobis Nov 18 '13 at 15:55
show 1 more comment

shape is not insrted in mysql properly and error came that 'shape does not have a default value' only shape field not inserted remaining are inserted..plz help me Priyanka

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.