mobile and gis dev notes

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

I only tried this using point objects.

1. Download and install MyODBC3.5.1

2. Create database mapinfotest

3. Create the mapinfo_mapcatalog table as follows:

CREATE TABLE `mapinfo_mapcatalog` (
`SpatialType` float NOT NULL default '0',
`TableName` char(32) NOT NULL default '',
`OwnerName` char(32) NOT NULL default '',
`SpatialColumn` char(32) NOT NULL default '',
`DB_X_LL` float NOT NULL default '0',
`DB_Y_LL` float NOT NULL default '0',
`DB_X_UR` float NOT NULL default '0',
`DB_Y_UR` float NOT NULL default '0',
`CoordinateSystem` char(254) NOT NULL,
`Symbol` char(254) NOT NULL default '',
`XColumnName` char(32) NOT NULL default '',
`YColumnName` char(32) NOT NULL default '',
PRIMARY KEY (`TableName`,`OwnerName`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


4. create a sample table with two (2) decimal columns for the latitude and longitude.

CREATE TABLE `poi_orig2` (
`poiid` decimal(10,0) unsigned NOT NULL default '0',
`id` char(10) NOT NULL default '',
`py_name` char(255) character set utf8 default NULL,
`latitude` decimal(20,8) default '0.00000000',
`longitude` decimal(20,8) default '0.00000000',
PRIMARY KEY (`poiid`,`id`)
)


5. Open Mapinfo 8
6. File -> Open DBMS Connection

7. Table > Maintenance > Make DBMS Table Mappable

Fill up the necessary values for the x,y and the index column.

You need to choose an index column so Mapinfo could update the record/s in MySQL, normally it would be an ID. Once successful you should be able to see the success window…

8. Open the table

Click on the upper right corner to open a dbms connection. After selecting the datasource (mine is system) then it would give you a list of tables to choose from.