System Administration

One of the main project goals has been sharing the technical methodology of processing OSM data, to be used in similar or different contexts. In this page the technical details in setting up, programming and maintaining the OSM-GB will be presented.

=System Setup= As described in Information Management page, there are 3 servers working together: OSMGB1, OSMGB2 and OSMGB3. OSMGB1 is a Windows 2003 Server and the two others are Ubunto servers. Briefly, OSMGB1 does the data processing, OSMGB2 does the Web Services front end and OSMGB3 holds the data and does the Web Services backbone.

Setting up OSMGB1
In addition to Windows Server 2003, Radius Studio (RS) and Oracle Spatial databases are installed on this server. The two installations are done centrally by 1Spatial and the University and have not been done in the project development phase. We need to install a few other software:
 * soapUI 4.5.0 or higher
 * Putty 0.62 or higher
 * GDAL 19.0 or higher for Windows
 * For development purposes only: SQL Developer 3.1.06 or higher

Setting up OSMGB2
In addition to the server OS (Ubuntu 11.0.4), we need to install:
 * Apache Web Server 2.0
 * GeoServer 2.2.3
 * We have also installed WordPress and MediaWiki for communication purposes.

Setting up OSMGB3
In addition to the server OS (Ubuntu 11.0.4), we need to install:
 * Apache Web Server 2.0 or higher
 * PostGIS 8.4.13 or higher
 * Mapnik. To configure Mapnik, detailed steps shall be done respectively. One of the best documentations found for this can be seen here.
 * Mapnik OGC Server. Configuration details can be found here
 * mod_tile module for Apache Web Server. Configuration details can be found here
 * OSM2PGSQL
 * SHP2PGSQL
 * OSMOSIS

=Initiating the PostGIS Database= The PostGIS database needs to be initiated according to the project requirements. The steps are the initial data uploading and making the necessary queries (Views). For this purpose, a batch script on OSMGB3 server (source code here) will do the job.

=Update Cycle= The update cycle is the process of updating PostGIS database, processing the database according to the rules, reflecting the changes back to PostGIS and logging all the changes. Server OSMGB1 is the process driver and this is done via the "Scheduled Tasks" in Windows' Control Panel. The currently configured tasks done every day are:
 * 1) At 01:00am: Run "C:\Program Files\GDAL\stop_process.bat"
 * 2) At 01:05am: Run "C:\Program Files\GDAL\TerminateRS.bat"
 * 3) At 01:10am: Run "C:\Program Files\GDAL\CloseCMD.bat"
 * 4) At 01:15am: Run "D:\1Spatial\RadiusStudio212\BatchFiles\02 - StartJBossSessionQueue01.bat"
 * 5) At 01:15am: Run "D:\1Spatial\RadiusStudio212\BatchFiles\04 - StartJBossInterfaceQueue.bat"
 * 6) At 01:40am: Run "C:\Program Files\GDAL\lgosmgb3_update.bat"
 * 7) At 03:00am: Run "C:\Program Files\GDAL\process_selected.bat"
 * 8) At 05:00am: Run "C:\Program Files\GDAL\lgosmgb2_update.bat"
 * 9) At 05:02am: Run "C:\Program Files\GDAL\postprocess.bat"

The job done in each step is broken down here:

Steps 1 to 3: Workflow Initializing
Firstly it is needed to stop the RS session before starting it. stop_process.bat does this job by a call to soapUI. For practical reasons, it is found safer to restart the Radius Studio every day before the update workflow. Thus the TerminateRS.bat is a script to send the termination signal to the java processes handling the RS instance, as well as clearing the RS cache and temporary files. CloseCMD.bat will then close the unclosed DOS command windows left by the idle java processes.

Steps 4 and 5: Restarting RS
These two batch files start the Radius Studio (single partition). The first one is handling the session queue and the other handles the web interface queue.

Step 6: Updating OSMGB3 Server
The lgosmgb3_update.bat starts an script called update.sh located at "postgres" user's home directory on OSMGB3.

Step 7: Processing the data on OSMGB1
The process_selected.bat firstly transfers the "selected" records from PostGIS database to Oracle Spatial database. Then starts the session called "All Actions - single partition" using the RS Web Services, via a call to soapUI.

Step 8: Updating OSMGB2 server
There is a little update that must be done on OSMGB2 server, which is the time stamp shown underneath the map in the project home page. lgosmgb2_update.bat does this change by copying the database updating timestamp stored in OSMGB3 to a text file located on OSMGB2.

Step 9: Post Processing
postprocess.bat transfers the processed data from Oracle (on OSMGB1) back to PostGIS (on OSMGB3) and apply the fixes on PostGIS database. The actions taken in this step are:

Preparing corrected dataset on Oracle database for export
echo quit | sqlplus [username]@[dbname]/[password] @draft2main.sql This will run a sql script on the Oracle database, where draft2main.sql is: truncate table planet_osm_point_corrected; truncate table planet_osm_line_corrected; truncate table planet_osm_polygon_corrected; delete from planet_osm_point_corrected; delete from planet_osm_line_corrected; delete from planet_osm_polygon_corrected; insert into planet_osm_point_corrected select * from draft_osm_point_corrected; insert into planet_osm_line_corrected select * from draft_osm_line_corrected; insert into planet_osm_polygon_corrected select * from draft_osm_polygon_corrected; COMMIT;

The above action is transferring the corrected data between the Oracle tables. The RS process has already made its output as draft_osm_*_corrected tables, so by the end of the above, the planet_osm_*_corrected tables are updated according to the draft_osm_*_corrected. The reason for having the draft tables is to to make a seamless update of the main corrected tables. The corrected tables are being used by the Web Services and are not best to be directly changed by long RS processing, while the above codes can make an almost instant update on them.

Removing the corrected data on server 3 before loading the new corrected dataset
C:\"Program Files"\PostgreSQL\9.1\bin\psql -h lgosmgb3 -p 5432 -U postgres -w -d osmgb < truncate_corrected.sql This will remotely run a SQL script on the PostGIS database on OSMGB3 server, where truncate_corrected.sql is: truncate table planet_osm_point_corrected; truncate table planet_osm_line_corrected; truncate table planet_osm_polygon_corrected; COMMIT;

Transferring the corrected data to server 3
ogr2ogr -f "PostgreSQL" "PG:dbname=osmgb host=lgosmgb3 port=5432 user=postgres" "OCI:UON_CGS_DBA/OSMGBcgs24DBA@OSMRSPRO:planet_osm_polygon_corrected,planet_osm_line_corrected,planet_osm_point_corrected" -lco GEOMETRY_NAME=way -lco DIM=2 -progress -append -t_srs EPSG:900913 -skipfailures

This will do the transfer from Oracle to PostGIS using OGR2OGR utility. The SRS will be transformed from EPSG:4326 to EPSG:900913. The planet_osm_*_corrected on the two database will be identical after this action.

Backing up data on server 3 before applying the corrections
C:\"Program Files"\PostgreSQL\9.1\bin\psql -h lgosmgb3 -p 5432 -U postgres -w -d osmgb < backup.sql Please note that the main tables on the PostGIS database are still not touched. Before changing them according to the corrected tables, it is necessary to back them up. The backups are used on the next day during the OSMGB1 server update. Backing up is populating anther 3 tables called planet_osm_*_uncorrected with the original data of the records that are going to be changed in the next action. The above code remotely runs the SQL script backup.sql on OSMGB3 server, where backup.sql is: truncate table planet_osm_line_uncorrected; truncate table planet_osm_polygon_uncorrected; truncate table planet_osm_point_uncorrected; delete from planet_osm_line_uncorrected; delete from planet_osm_polygon_uncorrected; delete from planet_osm_point_uncorrected; INSERT INTO PLANET_OSM_LINE_UNCORRECTED (SELECT planet_osm_line.* FROM planet_osm_line, planet_osm_line_corrected WHERE planet_osm_line.osm_id = planet_osm_line_corrected.osm_id); INSERT INTO PLANET_OSM_POLYGON_UNCORRECTED (SELECT planet_osm_polygon.* FROM planet_osm_polygon, planet_osm_polygon_corrected WHERE planet_osm_polygon.osm_id = planet_osm_polygon_corrected.osm_id); INSERT INTO PLANET_OSM_POINT_UNCORRECTED (SELECT planet_osm_point.* FROM planet_osm_point, planet_osm_point_corrected WHERE planet_osm_point.osm_id = planet_osm_point_corrected.osm_id); COMMIT;

Applying fixes on server 3
C:\"Program Files"\PostgreSQL\9.1\bin\psql -h lgosmgb3 -p 5432 -U postgres -w -d osmgb < osmfix.sql This will actually do the necessary changes (fixes) on the main PostGIS database (and are reflected on the Web Services). The records in planet_osm_* tables will be changed according to the records in planet_osm_*_corrected (having identical osm_id's). The SQL script (osmfix.sql) that is remotely executed on PostGIS database (OSMGB3) is: UPDATE planet_osm_polygon SET bridge=T.bridge, building=T.building, highway=T.highway, junction=T.junction, layer=T.layer, name=T.name, place=T.place, ref=T.ref, tunnel=T.tunnel, way=T.way FROM planet_osm_polygon_corrected T WHERE planet_osm_polygon.osm_id=T.osm_id; UPDATE planet_osm_line SET bridge=T.bridge, building=T.building, highway=T.highway, junction=T.junction, layer=T.layer, name=T.name, oneway=T.oneway, place=T.place, ref=T.ref, tunnel=T.tunnel, way=T.way FROM planet_osm_line_corrected T WHERE planet_osm_line.osm_id=T.osm_id; COMMIT;