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…
Note: You need the postgresql-8.2-504.jdbc3.jar.
Technote From Adobe
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"
Here is a post in buffering points and combining them using GeomUnion. Works in GEOS3.0.0, so note that your POSTGIS installation in windows contains a lower version of GEOS.
DROP FUNCTION combineGeometry();
CREATE FUNCTION combineGeometry() RETURNS Void AS'
DECLARE
--define datatypes here
updateCount integer DEFAULT 0;
geom_record RECORD;
geom_current RECORD;
geom_final RECORD;
mytotal integer DEFAULT 0;
BEGIN
--get the total
SELECT count(*) as mycount FROM busstopv1_buffer_20_temp INTO mytotal;
-- 1,2,3,4,5
FOR geom_record IN SELECT * FROM busstopv1_buffer_20 LOOP
--get the current geom of the record
SELECT *
FROM busstopv1_buffer_20_temp b
WHERE b.gid = geom_record.gid INTO geom_current;
RAISE INFO ''RECORD GID: %'', geom_record.gid;
RAISE INFO ''===========TOTAL COUNT: %=============='', mytotal;
--find the intersection of the current geom with other spatial entities
--and loop through that. For each loop, update the geom.
IF geom_current IS NULL THEN
CONTINUE;
END IF;
FOR geom_final IN
SELECT GeomUnion(b0.the_geom, geom_current.the_geom ) AS geom_current_union, b0.gid
FROM busstopv1_buffer_20_temp b0
WHERE b0.gid <> geom_current.gid
AND Intersects(b0.the_geom, geom_current.the_geom) = ''t''
ORDER BY gid ASC LOOP
--geom_current.gid = geom_record.gid
UPDATE busstopv1_buffer_20_temp
SET the_geom = GeomUnion( the_geom, geom_final.geom_current_union )
WHERE gid = geom_current.gid;
RAISE INFO ''UPDATED GID:%'', geom_current.gid;
DELETE FROM busstopv1_buffer_20_temp WHERE gid = geom_final.gid;
RAISE INFO ''DELETED GID:%'', geom_final.gid;
mytotal = mytotal - 1;
END LOOP;
END LOOP;
END;'
LANGUAGE plpgsql;
SELECT combineGeometry();
Next step.. trying out CGAL.
Here is a sample script in using buffer in Postgis. I buffered the line by 10 and 20 meters. Take note that I have to transform the geometry to the corresponding EPSG, so I could specify “meters”.
DROP TABLE busline_buffer1;
DELETE FROM geometry_columns WHERE f_table_name = 'busline_buffer1';
CREATE TABLE busline_buffer1( gid serial, CONSTRAINT pk_buffer1 PRIMARY KEY(gid));
SELECT AddGeometryColumn('public', 'busline_buffer1', 'the_geom', 4326, 'POLYGON',2);
// This would display what the output of the geometry IS...
SELECT AsText( transform( ST_BUFFER( transform(v1.the_geom, 32650), 10 ), 4326 ) ) FROM buslinev1 v1;
INSERT INTO busline_buffer1(the_geom) SELECT transform( ST_BUFFER( transform(v1.the_geom, 32650), 10 ), 4326 ) FROM buslinev1 v1;
SELECT gid, AsText(the_geom) FROM busline_buffer1;
========================================
DROP TABLE busline_buffer2;
DELETE FROM geometry_columns WHERE f_table_name = 'busline_buffer2';
CREATE TABLE busline_buffer2( gid serial, CONSTRAINT pk_buffer2 PRIMARY KEY(gid));
SELECT AddGeometryColumn('public', 'busline_buffer2', 'the_geom', 4326, 'POLYGON',2);
SELECT AsText( transform( ST_BUFFER( transform(v1.the_geom, 32650), 20 ), 4326 ) ) FROM buslinev1 v1;
INSERT INTO busline_buffer2(the_geom) SELECT transform( ST_BUFFER( transform(v1.the_geom, 32650), 20 ), 4326 ) FROM buslinev1 v1;
SELECT gid, AsText(the_geom) FROM busline_buffer2;
