NoRoadBlockMtl

For the moment very basic structure:

• readworks.php: read the data source from montreal website and insert all the road blocks. For the moment, it does a stupid insert, and DB has few limitation, so if script is executed twice, points will be inserted twice...

• index.php: displays a google maps + form to select the route/path + send path points to page postme.php To use the form : 1. Select enter a start and end point and click "Tracer trajet" => this will print the path

  2. Enter a distance (it's in kilometers) and click on "Valider trajet" to send the path to the postme.php page

• postme.php: read the form variable and queries the DB to retrieve and prints points with the entered distance of the selected path

• config.php: contains the configuration to connect to the PostgreSQL DB (config.php.template should be renamed as config.php and values should be filled)

Setup of the DB

The code works with the folliwing DB structure :

Definition of the table (SQL)

CREATE TABLE "points_chantier" ( "id" serial NOT NULL PRIMARY KEY, "name" varchar(500) NOT NULL, "where" text NOT NULL, "raw_description" text NOT NULL );

Creation of the PostGIS parameters

SELECT AddGeometryColumn('points_chantier', 'point', 4326, 'LINESTRING', 2); ALTER TABLE "points_chantier" ALTER "point" SET NOT NULL; CREATE INDEX "points_chantier_point_id" ON "points_chantier" USING GIST ( "point" GIST_GEOMETRY_OPS );

Note: initially my DB was created with a 'POINT' as the fourth parameter of the AddGeometryColumn. However the city started using LINESTRING to mark roadworks. In my DB I just removed the POINT constrains. But here, I set LINESTRING as the type of geometry.

Creation of the user routes tables

CREATE TABLE user_routes ( id serial NOT NULL, userid integer NOT NULL, "name" character varying(500) NOT NULL, )

SELECT AddGeometryColumn('user_routes', 'route_geom', 4326, 'LINESTRING', 2); ALTER TABLE "user_routes" ALTER "route_geom" SET NOT NULL; CREATE INDEX "user_routes_geom" ON "user_routes" USING GIST ( "route_geom" GIST_GEOMETRY_OPS );

Support of json extention : in order to support .json extention being interpreted by PHP, a special config might be needed for Apache. Add the following line in apache2.conf : AddType application/x-httpd-php .json

Added to that, the PHP include path should be set to search in all the useful directory. Could be done in apache2.conf also For the moment it's as follow but should be improved:

<Directory /var/www/NoRoadBlockMtl> php_value include_path ".:/usr/share/php:/usr/share/pear:/var/www/NoRoadBlockMtl/inc:/var/www/NoRoadBlockMtl/api:/var/www/NoRoadBlockMtl/cron"

Cron set up

In order to automatically send notification mail, a cron can be set up : 0 php -q /var/www/NoRoadBlockMtl/cron/readworks.php