Loading Mapinfo table to PostGis
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
To import the roads.sql into the database…
$ psql -U postgres -d cybersoftbj < roads.sql
$ ogr2ogr --help Usage: ogr2ogr [--help-general] [-skipfailures] [-append] [-update] [-select field_list] [-where restricted_where] [-sql <sql statement>] [-spat xmin ymin xmax ymax] [-preserve_fid] [-fid FID] [-a_srs srs_def] [-t_srs srs_def] [-s_srs srs_def] [-f format_name] [-overwrite] [[-dsco NAME=VALUE] ...] dst_datasource_name src_datasource_name [-lco NAME=VALUE] [-nln name] [-nlt type] [layer [layer ...]] -f format_name: output file format name, possible values are: -f "ESRI Shapefile" -f "MapInfo File" -f "TIGER" -f "S57" -f "DGN" -f "Memory" -f "CSV" -f "GML" -f "KML" -f "Interlis 1" -f "Interlis 2" -f "SQLite" -f "ODBC" -f "PostgreSQL" -f "MySQL" -append: Append to existing layer instead of creating new if it exists -overwrite: delete the output layer and recreate it empty -update: Open existing output datasource in update mode -select field_list: Comma-delimited list of fields from input layer to copy to the new layer (defaults to all) -where restricted_where: Attribute query (like SQL WHERE) -sql statement: Execute given SQL statement and save result. -skipfailures: skip features or layers that fail to convert -spat xmin ymin xmax ymax: spatial query extents -dsco NAME=VALUE: Dataset creation option (format specific) -lco NAME=VALUE: Layer creation option (format specific) -nln name: Assign an alternate name to the new layer -nlt type: Force a geometry type for new layer. One of NONE, GEOMETRY, POINT, LINESTRING, POLYGON, GEOMETRYCOLLECTION, MULTIPOINT, MULTILINE, MULTIPOLYGON, or MULTILINESTRING. Add "25D" for 3D layers. Default is type of source layer. -a_srs srs_def: Assign an output SRS -t_srs srs_def: Reproject/transform to this SRS on output -s_srs srs_def: Override source SRS Srs_def can be a full WKT definition (hard to escape properly), or a well known definition (ie. EPSG:4326) or a file with a WKT definition.Converting postgres to Mapinfo. You need to specify the SPATIAL REFERENCE SYSTEM (SRS).
$ ogr2ogr -f “Mapinfo File” busline_buffer10m.tab PG:”host=localhost user=postgres dbname=cybersoftbj” -sql “select * from busline_buffer1″ -a_srs WGS84 -nln busline_buffered10m -nlt MULTIPOLYGON