I had a problem when using ogr2ogr and converting a postgres table to a road table. My postgres table containa a utf-8 road name which is all in chinese. The mapinfo road table created by ogr2ogr seems to contain the correct geometry and other fields that is in utf-8. However, all my chinese characters is all messed up. So, I have to export the file and open it to mapinfo.
1. In Postgres, to export to a file..
cybersoftbjv1=# set client_encoding = gbk;
SET
cybersoftbjv1=# \o road.txt;
cybersoftbjv1=# select rd_id, cn_name from roads where cn_name <> '';
cybersoftbjv1=# \q
2. Open the file in vim, and do a “%s/ //g”. This would replace all ” ” to “”.
Note: This is reasonable for chinese since chinese dont have spaces. However english prases and sentences differ.
3. Open the file in mapinfo and replace the other columns using Table -> Update.
If anybody has any other way to specify the client encoding in ogr2ogr that would be perfect…
Here is a list of the most widely used OGR commands I use..
OGR2OGR
1. POSTGRES -> MAPINFO
$ ogr2ogr -f "Mapinfo File" busline_buffer10m.tab PG:"host=localhost user=postgres dbname=cybersoftbj" -sql "select * from table_name" -a_srs WGS84 -nln layer_name -nlt MULTIPOLYGON
2. MAPINFO -> POSTGRES
ogr2ogr -f "PostgreSQL" PG:"host=127.0.0.1 user=rupert dbname=australia password=*****" AUS_ROAD.TAB -nln AUS_ROAD -a_srs EPSG:4269 -t_srs EPSG:3857 -skip-failures
ogr2ogr -f "PostgreSQL" PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" mytabfile.tab -nln newtablename -select columnName
Note: If you have Chinese characters, might as well do MAPINFO -> SHAPE -> POSTGRES
http://222.128.19.19/wordpress/?p=108
3. SHAPE -> POSTGRES
shp2pgsql -W "gbk" -s 4326 lbjrdnt_small_polyline roads > roads.sql
4. POSTGRES -> SHAPE
pgsql2shp -h 127.0.0.1 -u lbs -P tracking -f roads.shp databasename tablename
4. MAPINFO TO ORACLE
ogr2ogr -f OCI OCI:username/password@orcl C:\path_to_tabfile\EMPLOYEES.TAB -nln employees
Note: This assumes you already have Oracle 10g Client installed and “orcl” is defined as an instance in tnsnames.ora. OGR2OGR automatically updates USER_SDO_GEOM_METADATA and creates a spatial index.
5. MAPINFO to MAPINFO but different projection. From EPSG:4326 to EPSG:3857
ogr2ogr -f "MapInfo File" BaseMaps_3857/AUS_CITIES_3857.TAB BaseMaps/AUS_CITIES.TAB -a_srs "EPSG:4326" -t_srs "EPSG:3857"
AFAIK, there are only two ways to load data to PostGIS:
1. Using Insert statements
2. Using Utilities.
Utilities include shp2pgsql which is found in “C:\Program Files\PostgreSQL\8.2\bin\” or “/usr/local/pgsql/bin”. To load Mapinfo table, I have used the OGR utilities from FWTools for Windows. and used gdal1.3.2 (since it contains ogr) for Unix.
OGR2OGR CheatSheet should be a good kickstart for basic understanding. For the impatient..
ogr2ogr -f "PostGreSQL" -nlt LINESTRING -a_srs "EPSG:4326" PG:"host=localhost user=username password=mypassword dbname=mydb mytab.TAB -select columnName
or
ogr2ogr -f "PostgreSQL" PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" mytabfile.tab -nln newtablename -select columnName
Bear in mind that you should select out the columns from your mapfile (mine is chinese) especially if you have a diffent encoding in your column which matches your database (postgres). You might get a “Terminating translation prematurely after failed translation of layer [layername]” error. Since Mapinfo stores the text to ASCII, my workaround is to export the tabfile to a UTF-8 textfile then upload it to PostGres. Hoping the primary ids would match to make the necessary updates…
An alternative to load Chinese text from Mapinfo to PostGIS is the ff:
1. In Mapinfo use the Universal Translator to export the table into a shape file.
2. Once you have the shape file, you can directly use the shp2pgsql.
shp2pgsql -W “gbk” -s 4326 lbjrdnt_small_polyline roads > roads.sql