mobile and gis dev notes

There is no way I could accomplish better data manipulation between MySQL and PostGres without Navicat. Just to be clear, I do not work for Navicat. These tools were able to help me export data out of MySQL and import it to PostGres. It is just one of those products that really deserve credit.

Take note though that you can not see the “messages” displayed by the “raise command” in PL/PGSQL when using Navicat 8 for PostGres. I have to switch back to using pgAdmin III or my standard command line.

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…

Im trying out ways to create a topology of edges and vertices for a road network. Currently, there are three ways I know of:

1. Using ArcGIS build coverage line.
– This includes the use of ArcGIS. Exporting the feature into tics, arcs and nodes, then afterwards assembling them all together. Its functional but have not fully tested the quality of the road topology. Also, assembling them back together through the spatial objects comparison will take some time. It would be better *If* ArcGIS could create the nodes wrt to the base table.

2. Using PostGres, postlbs functions.
– Use of SELECT ASSIGN_VERTEX_ID(‘table_name’, double_precision_distance. So far this bails on me on my first test on my win-xp laptop. We can test this on Linux if the response is the same.

3. Using Mapinfo Basic Scripts provided by J.
– Haven’t gone indepth with these for now, but looking forward to it.

4. Using Grass
– As documented in pgrouting.postlbs.org site’s Topology Creation

Note: You need the postgresql-8.2-504.jdbc3.jar.

Technote From Adobe

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.