pgRouting 1.02 with OpenLayers 2.6

Do the similar steps in pgRouting 1.01 with OpenLayers 2.5 on Ubuntu 7.10 , only change OpenLayers 2.5 to OpenLayers 2.6, change lines according to OpenLayers 2.5 and change following line:

OpenLayers.loadURL("./routing", OpenLayers.Util.getParameterString(result), null, displayRoute);
-->
OpenLayers.loadURL("./routing", result, null, displayRoute);

Note:
- If you use Windows, you can read this page of David: http://www.davidgis.fr/documentation/pgrouting-1.02/

- With OpenLayers 2.7, you can replace OpenLayers.loadURL to OpenLayers.Request.GET.
Ex:
OpenLayers.loadURL("./routing", result, null, displayRoute);
-->
var request = OpenLayers.Request.GET({
url: "./routing",
params: result,
headers: {
"Content-Type": "text/plain"
},
callback: displayRoute
});

pgRouting 1.01 with OpenLayers 2.5 on Ubuntu 7.10

Step 1. Preparation
1. Download and view information of pgRouting on http://pgrouting.postlbs.org/.
Download OpenLayers 2.5 on http://openlayers.org/.
2. Create a project, such as "routing", and add the library of OpenLayers into project (include directories of "lib", "img", "theme") at some directory, such as "openlayers".

Step 2. Install pgRouting and prepare routing table
1. Do step by step on http://pgrouting.postlbs.org/wiki/1.x/InstallationUbuntu710.
Note:
- Delete the line "svn checkout http://pgrouting.postlbs.org/svn/pgrouting/tags/release-1.0 pgrouting".
- If errors happen with functions "createdb", "createlang" and "psql", append "-h localhost" after those functions.

2. Prepare routing table for Dijkstra, A-Star or Shooting-Star.
Do step by step on http://pgrouting.postlbs.org/wiki/WorkshopFOSS4G2007 at part "PostgreSQL / pgRouting" with your own data. Ignore "Load routing data" if you don't use it.

Step 3. OpenLayers routing map
Do step by step on http://pgrouting.postlbs.org/wiki/WorkshopFOSS4G2007 at part "OpenLayers routing map".

Note:
With OpenLayers 2.5, we need to edit following contents:
* Part "Create a Vector layer and draw a polygon on it"
......
var result_style = OpenLayers.Util.applyDefaults({
strokeWidth: 3,
strokeColor: "#ff0000",
fillOpacity: 0
}, OpenLayers.Feature.Vector.style['default']);
......
var geometry = parser.read(wkt);
var feature = new OpenLayers.Feature.Vector(geometry);
......

---->
......
var result_style = OpenLayers.Util.extend({}, OpenLayers.Feature.Vector.style['default']);
result_style.strokeWidth = 3;
result_style.strokeColor = "#ff0000";
result_style.fillOpacity = 0;
......
var feature = parser.read(wkt);
......

* Part "'set start/end point' tool"
......
var start_style = OpenLayers.Util.applyDefaults({
externalGraphic: "start.png",
graphicWidth: 18,
graphicHeight: 26,
graphicYOffset: -26,
graphicOpacity: 1
}, OpenLayers.Feature.Vector.style['default']);

var stop_style = OpenLayers.Util.applyDefaults({
externalGraphic: "stop.png",
graphicWidth: 18,
graphicHeight: 26,
graphicYOffset: -26,
graphicOpacity: 1
}, OpenLayers.Feature.Vector.style['default']);
......

---->
......
var start_style = OpenLayers.Util.extend({}, OpenLayers.Feature.Vector.style['default']);
start_style.externalGraphic = "start.png";
start_style.graphicWidth = 18;
start_style.graphicHeight = 26;
start_style.graphicYOffset = -26;
start_style.graphicOpacity = 1;

var stop_style = OpenLayers.Util.extend({}, OpenLayers.Feature.Vector.style['default']);
stop_style.externalGraphic = "stop.png";
stop_style.graphicWidth = 18;
stop_style.graphicHeight = 26;
stop_style.graphicYOffset = -26;
stop_style.graphicOpacity = 1;
......

* Part "Show the routing result on the map"
......
var g = parser.read(edges[i].getElementsByTagName('wkt')[0].textContent);
features.push(new OpenLayers.Feature.Vector(g));
......

---->
......
var g = parser.read(edges[i].getElementsByTagName('wkt')[0].textContent);
features.push(g);
......

Step 4. Routing PHP script with XML output
Do step by step on http://pgrouting.postlbs.org/wiki/WorkshopFOSS4G2007 at part "Routing PHP script with XML output".
Note: If you use Java, you can read my classes in file mapgis.zip attached below.

Demo: mapgis.zip (without database).

Can not export data in Oracle 10g

Error: "RemoteOperationException: ERROR: wrong password for user".
Resolve:

1. Go to "Control Panel" -> "Admin Tools" -> "Local Security Policy."
2. Within "Local Policies", go to user "Right Assignment."
3. Add the user to "Logon as a Batch Job."

http://weblogs.asp.net/wallym/archive/2005/01/03/345818.aspx

Import Shapefile into Oracle 10g

Step 1
Download tool Oracle Shapefile Converter at http://www.oracle.com/technology/software/products/spatial/index.html and extract it into any directory.
Open the directory correspond with your operating system (ex: directory shp2sdo_nt for MS Windows), called CONVERT_HOME.
Call the directory that contains your shapefiles is SHAPE_HOME.
Call the directory that installs Oracle 10g is ORACLE_HOME.

Step 2
1. Open console, run the following commands:
> cd $SHAPE_HOME
> set path="$CONVERT_HOME"

2. Run shp2sdo converter:

• on Windows NT

shp2sdo.exe <file_name> <table_name> -g <geom_name> -d -x (<minx>,<maxx>) -y (<miny>,<maxy>) -s <SRS> -t <tolerance> -v

• on Sun Sparc Solaris or Linux

shp2sdo.exe <file_name> <table_name> -g <geom_name> -d -x \(<minx>,<maxx>\) -y \(<miny>,<maxy>\) -s <SRS> -t <tolerance> -v

Ex:
shp2sdo.exe tuyendg spa_tuyendg -g geom -d -x (575977.125,577368.125) -y (1195219.125,1196522.0) -s 32648 -t 0.5 -v

Note: Type shp2sdo.exe -h for help.


Step 3
After running shp2sdo converter, it creates 2 files:

• *.sql : to create oracle table.

• *.ctrl : to store data.

1. Open iSQL*Plus with database that you want to import, execute the content in *.sql (ex: @spa_tuyendg.sql).

2. Open file *.ctrl, insert line " CHARACTERSET UTF8" after line "LOAD DATA" and save it.

3. Open console, use Oracle SQL*Loader by running the following commands:
> set path="$ORACLE_HOME/product/10.1.0/db_1/BIN"
> sqlldr <username>/<password> <table_name>
Ex: sqlldr SYSTEM/oracle spa_tuyendg


Step 4
Open iSQL*Plus, execute the following commands:
CREATE INDEX <index_name> ON <table_name>(<geom_name>) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
EXECUTE SDO_MIGRATE.TO_CURRENT('<table_name>','<geom_name>');

Ex:
CREATE INDEX spa_tuyendg_spatial_idx ON spa_tuyendg(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
EXECUTE SDO_MIGRATE.TO_CURRENT('SPA_TUYENDG','GEOM');


View details in using_shp2sdo.txt of Oracle Shapefile Converter.

Add fonts to Ubuntu

1. Copy all fonts into /usr/share/fonts/truetype.
2. Restart computer or type following command:
sudo fc-cache -fv

http://saylinux.wordpress.com/2007/06/19/cai-them-fonts-cho-ubuntu-than-yeu-p/

Import spatial data (shapefile) into PostgreSQL

1. Open console, execute following commands:
cd $ShapeDir
shp2pgsql -W UTF-8 <filename>.shp <schema>.<tablename> ><filename>.sql

2. Open file that has just created, insert string "WITH OIDS" before character ";" of command CREATE TABLE, and then save that file.

3. Continue to execute the command:
psql -h <hostname> -U <username> -d <databasename> -f <filename>.sql

4. Open pgAdmin3 (or phpPgAdmin), open Query Tool and input following lines:
CREATE INDEX <indexname> ON <tablename> USING gist (<spatial_attribute>);
VACUUM ANALYZE <tablename>

Ex:
shp2pgsql -W UTF-8 hcm_road.shp public.hcm_road > hcm_road.sql

psql -h 127.0.0.1 -U postgres -d hcmcity -f hcm_road.sql

CREATE INDEX hcm_road_the_geom_gist ON hcm_road USING gist (the_geom);

VACUUM ANALYZE hcm_road

* Note: if you want to export data from PostgreSQL to shapefile, using command pgsql2shp.

Install PostgreSQL 8.2 with PostGIS on Ubuntu

On Ubuntu 7.10
Open console, execute following command:
sudo apt-get install postgresql-8.2 postgresql-client-8.2 postgresql-client-common postgresql-common postgresql-server-dev-8.2 postgresql-contrib-8.2 libgeos-dev proj postgis postgresql-8.2-postgis

Note: /usr/lib/postgresql/8.2/lib ---> directory contains file liblwgeom.so


On Ubuntu 7.04
1. Open console, execute following command:
sudo apt-get install postgresql-8.2 postgresql-client-8.2 postgresql-client-common postgresql-common postgresql-server-dev-8.2 postgresql-contrib-8.2 libgeos-dev proj flex postgis

2. Download postgis-1.3.x.tar.gz and decompress.

3. Execute following commands:
cd ${postgis-1.3.x}
./configure

Result:

SUMMARY
-------------------------------------------------------

HOST_OS: linux-gnu

PGSQL: /usr/bin/pg_config
GEOS: /usr/bin/geos-config (with C-API)
(ldflags: -L/usr/lib)
PROJ: prefix=/usr libdir=/usr/lib
ICONV: 1

PORTNAME: linux
PREFIX: /usr
EPREFIX: ${prefix}
DOC: /usr/share/doc/postgresql-doc-8.2/contrib
DATA: ${datarootdir}
MAN: ${datarootdir}/man
BIN: /usr/lib/postgresql/8.2/bin
EXT: /usr/lib/postgresql/8.2/lib (\$$libdir)
-------------------------------------------------------

Note: EXT: /usr/lib/postgresql/8.2/lib ---> directory contains file liblwgeom.so


4. Compile & Install
make
sudo make install