Archive

Posts Tagged ‘mapinfo’

Exporting from Postgres to Mapinfo

August 27th, 2007 rupert No comments

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…

Categories: postgis, postgres Tags: , ,

OGR Quick Reference

July 10th, 2007 rupert No comments

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 &gt; 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"
Categories: GDAL/OGR Tags: , , ,

Mapinfo Utility for gdal_translate.

June 21st, 2007 rupert Comments off

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 &gt;&gt; final.bat
perl gdal_mapinfo.pl Beijing_6th_10.TAB &gt;&gt; final.bat
perl gdal_mapinfo.pl Beijing_6th_11.TAB &gt;&gt; final.bat
perl gdal_mapinfo.pl Beijing_6th_12.TAB &gt;&gt; final.bat
perl gdal_mapinfo.pl Beijing_6th_13.TAB &gt;&gt; final.bat
perl gdal_mapinfo.pl Beijing_6th_2.TAB &gt;&gt; final.bat
perl gdal_mapinfo.pl Beijing_6th_3.TAB &gt;&gt; final.bat
perl gdal_mapinfo.pl Beijing_6th_4.TAB &gt;&gt; final.bat
perl gdal_mapinfo.pl Beijing_6th_5.TAB &gt;&gt; final.bat
perl gdal_mapinfo.pl Beijing_6th_6.TAB &gt;&gt; final.bat
perl gdal_mapinfo.pl Beijing_6th_7.TAB &gt;&gt; final.bat
perl gdal_mapinfo.pl Beijing_6th_8.TAB &gt;&gt; final.bat
perl gdal_mapinfo.pl Beijing_6th_9.TAB &gt;&gt; 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

Categories: GDAL/OGR Tags: , , , ,

Processing Mapinfo Raster JPEG Images using GDAL

June 14th, 2007 rupert Comments off

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) -&gt; (116.38575,39.906105,0)
GCP[  1]: Id=2, Info=
          (1160,317) -&gt; (116.390072,39.93201,0)
GCP[  2]: Id=3, Info=
          (8210,253) -&gt; (116.42786,39.932296,0)
GCP[  3]: Id=4, Info=
          (8522,6358) -&gt; (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=Blue

2. 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=Blue

Sweet. 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
END

LAYER
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

April 10th, 2007 rupert No comments

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.