By Rupert
Posts tagged mapinfo
Exporting from Postgres to Mapinfo
Aug 27th
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…
OGR Quick Reference
Jul 10th
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"
Mapinfo Utility for gdal_translate.
Jun 21st
I made a small perl utility to automate the gcp’s from Mapinfo Raster TABS to gdal_translate command line. Currently your tabfile would have:
!table !version 300 !charset WindowsLatin1 Definition Table File "beijing_6th_1.jpg" Type "RASTER" (116.522865,40.016316) (347,184) Label "Pt 1", (116.681215,40.015286) (7729,243) Label "Pt 2", (116.679777,39.777904) (7666,14674) Label "Pt 3", (116.523827,39.779108) (397,14606) Label "Pt 4" CoordSys Earth Projection 1, 104 Units "degree" RasterStyle 4 1 RasterStyle 7 1677695
How to use
gdal_mapinfo
1. ls *.TAB > init.sh
2. vi init.sh to reflect the ff:
perl gdal_mapinfo.pl Beijing_6th_1.TAB >> final.bat perl gdal_mapinfo.pl Beijing_6th_10.TAB >> final.bat perl gdal_mapinfo.pl Beijing_6th_11.TAB >> final.bat perl gdal_mapinfo.pl Beijing_6th_12.TAB >> final.bat perl gdal_mapinfo.pl Beijing_6th_13.TAB >> final.bat perl gdal_mapinfo.pl Beijing_6th_2.TAB >> final.bat perl gdal_mapinfo.pl Beijing_6th_3.TAB >> final.bat perl gdal_mapinfo.pl Beijing_6th_4.TAB >> final.bat perl gdal_mapinfo.pl Beijing_6th_5.TAB >> final.bat perl gdal_mapinfo.pl Beijing_6th_6.TAB >> final.bat perl gdal_mapinfo.pl Beijing_6th_7.TAB >> final.bat perl gdal_mapinfo.pl Beijing_6th_8.TAB >> final.bat perl gdal_mapinfo.pl Beijing_6th_9.TAB >> final.bat
3. The resulting final.bat should have the ff:
gdal_translate -gcp 347 184 116.522865 40.016316 -gcp 7729 243 116.681215 40.015286 -gcp 7666 14674 116.679777 39.777904 -gcp 397 14606 116.523827 39.779108 -of GTiff Beijing_6th_1.jpg I:\\satimages\translated\Beijing_6th_1_translated.tif
gdalwarp -s_srs epsg:4326 -t_srs epsg:4326 I:\\satimages\translated\Beijing_6th_1_translated.tif
I:\\satimages\warped\Beijing_6th_1.tif
Processing Mapinfo Raster JPEG Images using GDAL
Jun 14th
I have a couple of sat images (raw jpegs) from Google that I want to use with Openlayers/Mapserver. The raw jpegs were registered using Mapinfo via GCP (Ground Control Points).
Mapinfo Raster JPEG Images example:
rupert@rupert-winxp /e/home/map/beijing/new/satimages$ ll
-rw-r–r– 1 rupert None 358 Jan 30 03:23 2NE1.TAB
-rw-r–r– 1 rupert None 7.1M Jan 30 02:38 2NE1.jpg
-rw-r–r– 1 rupert None 356 Feb 1 18:56 2NE2a.TAB
-rw-r–r– 1 rupert None 3.8M Feb 1 08:57 2NE2a.jpg
You cannot fully reference 2NE1.TAB as a Mapserver Layer. I tried to use 2NE1.jpg, but the problem its not georeferenced.
rupert@rupert-winxp /e/home/map/beijing/new/satimages $ gdalinfo 2NE1.jpg Driver: JPEG/JPEG JFIF Size is 8650, 6744 Coordinate System is `' Corner Coordinates: Upper Left ( 0.0, 0.0) Lower Left ( 0.0, 6744.0) Upper Right ( 8650.0, 0.0) Lower Right ( 8650.0, 6744.0) Center ( 4325.0, 3372.0) Band 1 Block=8650x1 Type=Byte, ColorInterp=Red Band 2 Block=8650x1 Type=Byte, ColorInterp=Green Band 3 Block=8650x1 Type=Byte, ColorInterp=Blue
The georeference coordinates of 2NE1.jpg, just like an ESRI World File, is found in 2NE1.TAB…
rupert@rupert-winxp /e/home/map/beijing/new/satimages $ cat 2NE1.TAB !table !version 300 !charset WindowsLatin1 Definition Table File "2ne1.jpg" Type "RASTER" (116.38575,39.906105) (349,6619) Label "Pt 1", (116.390072,39.93201) (1160,317) Label "Pt 2", (116.42786,39.932296) (8210,253) Label "Pt 3", (116.4295878,39.90722318) (8522,6358) Label "Pt 4" CoordSys Earth Projection 1, 0 Units "degree"
I found hurting myself in trying to create an ESRI world file from the current MAPINFO Raster TABS. So, I decided to go for GeoTIFF since its native in Mapserver. Using GDAL utilities my only problem is how to put a coordinate system and reference to the raster.
On windows, you could use Frank’s FWTools. For Linux, compile GDAL by source with python would be extremely helpful later on. For installation of GDAL on Linux, we can use Mapserver’s Verbose Installation in Linux Guide.
GDAL – the saviour!.
GDAL utilities is extremely helpful in reprojection, scaling, image mosaics, etc. For now, we will use gdal_translate and gdal_warp. Please RTFM the utilities.
1. Using gdal_translate to specify the gcp’s registered in Mapinfo.
gdal_translate -gcp pixel line easting northing
Add the indicated ground control point to the output dataset. This option may be provided multiple times to provide a set of GCPs.
$ gdal_translate -gcp 349 6619 116.38575 39.906105 -gcp 1160 317 116.390072 39.93201 -gcp 8210 253 116.42786 39.932296 -gcp 8522 6358 116.4295878 39.90722318 -of GTiff 2NE1.jpg 2NE1translated.tif
Input file size is 8650, 6744
0...10...20...30...40...50...60...70...80...90...100 - done.
Note: even if we specify the gcp’s, gdal_translate would not specify the corner coordinates of the tiff.
rupert@rupert-winxp /e/home/map/beijing/new/satimages
$ gdalinfo 2NE1translated.tif
Driver: GTiff/GeoTIFF
Size is 8650, 6744
Coordinate System is `'
GCP Projection =
GCP[ 0]: Id=1, Info=
(349,6619) -> (116.38575,39.906105,0)
GCP[ 1]: Id=2, Info=
(1160,317) -> (116.390072,39.93201,0)
GCP[ 2]: Id=3, Info=
(8210,253) -> (116.42786,39.932296,0)
GCP[ 3]: Id=4, Info=
(8522,6358) -> (116.4295878,39.90722318,0)
Corner Coordinates:
Upper Left ( 0.0, 0.0)
Lower Left ( 0.0, 6744.0)
Upper Right ( 8650.0, 0.0)
Lower Right ( 8650.0, 6744.0)
Center ( 4325.0, 3372.0)
Band 1 Block=8650x1 Type=Byte, ColorInterp=Red
Band 2 Block=8650x1 Type=Byte, ColorInterp=Green
Band 3 Block=8650x1 Type=Byte, ColorInterp=Blue2. Use gdalwarp to reproject using the gcp and specify the coordinates.
The gdalwarp utility is an image mosaicing, reprojection and warping utility. The program can reproject to any supported projection, and can also apply GCPs stored with the image if the image is “raw” with control information.
$ gdalwarp -s_srs epsg:4326 -t_srs epsg:4326 2NE1translated.tif warped.tif
Creating output file that is 9422P x 5631L.
Processing input file 2NE1translated.tif.
:0...10...20...30...40...5050...60...70...80...90...
Let’s check after gdalwarp using gdalinfo…
$ gdalinfo warped.tif
Driver: GTiff/GeoTIFF
Size is 9422, 5631
Coordinate System is:
GEOGCS["WGS 84",
DATUM["WGS_1984",
SPHEROID["WGS 84",6378137,298.2572235630016,
AUTHORITY["EPSG","7030"]],
AUTHORITY["EPSG","6326"]],
PRIMEM["Greenwich",0],
UNIT["degree",0.0174532925199433],
AUTHORITY["EPSG","4326"]]
Origin = (116.383841930499160,39.933342296341991)
Pixel Size = (0.000004927579869,-0.000004927579869)
Metadata:
AREA_OR_POINT=Area
Corner Coordinates:
Upper Left ( 116.3838419, 39.9333423) (116d23'1.83"E, 39d56'0.03"N)
Lower Left ( 116.3838419, 39.9055951) (116d23'1.83"E, 39d54'20.14"N)
Upper Right ( 116.4302696, 39.9333423) (116d25'48.97"E, 39d56'0.03"N)
Lower Right ( 116.4302696, 39.9055951) (116d25'48.97"E, 39d54'20.14"N)
Center ( 116.4070558, 39.9194687) (116d24'25.40"E, 39d55'10.09"N)
Band 1 Block=9422x1 Type=Byte, ColorInterp=Red
Band 2 Block=9422x1 Type=Byte, ColorInterp=Green
Band 3 Block=9422x1 Type=Byte, ColorInterp=BlueSweet. Now, all we need to do is display the raster images in Mapserver/OpenLayers.
Specifying a raster image in Mapserver
LAYER
NAME “2NE1″
DATA “satimages/2NE1.tif”
TYPE RASTER
STATUS DEFAULT
ENDLAYER
NAME “2NE2″
DATA “satimages/2NE2a.tif”
TYPE RASTER
STATUS DEFAULT
END
Here is the end result…
.
Lessons learned, I tried to specify coordinate extents using gdal_translate -a_ullr ulx uly lrx lry. Specifying the coordinates was subjective by just looking at the cursor location of the registered image in Mapinfo. It is still best to use the GCP’s. Simply put, we need to be accurate in specifying corner coordinates in raster images to project them accurately.
.
Preparing routing data for pgRouting
Apr 10th
1. It is important that you already have postgres, postgis, pgRouting installed in your machine.
A. The schema. Below is the sample schema that is a derivative of the kanagawa sample data from pgRouting. Take note of the source and target nodes, as well as the length and the node coordinates (x1,y1; x2,y2) of the line.
cybersoftbj=# \dt List of relations Schema | Name | Type | Owner --------+------------------+-------+---------- public | geometry_columns | TABLE | postgres public | roads | TABLE | postgres public | spatial_ref_sys | TABLE | postgres (3 rows)
cybersoftbj=# \d roads TABLE "public.roads" COLUMN | Type | Modifiers ------------+------------------------+----------------------------------------------------- gid | integer | NOT NULL DEFAULT NEXTVAL('roads_gid_seq'::regclass) rd_id | bigint yutu_id | integer block_id | bigint heirarchy | character varying(5) cn_name | character varying(75) py_name | character varying(100) source | bigint target | bigint x1 | numeric y1 | numeric x2 | numeric y2 | numeric costlength | numeric the_geom | geometry
A. Extracting the coordinates of the line segments from Mapinfo.
1. I have to format the data structure as follows…
…here is the roads table after weeding out some unnecessary columns…

… adding the source,target,x1,y1,x2,y2,costlength…

2. Using ObjectGeography. Download the MapBasic Reference
ObjectGeography( object, attribute )
ObjectGeography( object, “1″ ) <– gives you the beginning x coord of the point

3. Export the tab file to a shape file for ArchMap.

Comments