[pgrouting-users] Issues with pgrouting workshop code in postgis2.11/pgrouting 2.0
Emmanuel Adegboye
eaadegboye at googlemail.com
Thu Jan 9 01:53:02 PST 2014
By the way, I followed Sittichai's code but I still got the same errors I
got earlier.
On 9 January 2014 10:48, Emmanuel Adegboye <eaadegboye at googlemail.com>wrote:
> Hello.
>
> I'm now working with the 2013 workshop now and I have my tables structured
> as follows:
>
> routing=# \d
> List of relations
> Schema | Name | Type | Owner
> --------+-------------------------------+----------+----------
> public | eastlegon | table | postgres
> public | eastlegon_gid_seq | sequence | postgres
> public | eastlegon_vertices_pgr | table | postgres
> public | eastlegon_vertices_pgr_id_seq | sequence | postgres
> public | eastlegonboundary | table | postgres
> public | eastlegonboundary_gid_seq | sequence | postgres
> public | geography_columns | view | postgres
> public | geometry_columns | view | postgres
> public | raster_columns | view | postgres
> public | raster_overviews | view | postgres
> public | route | table | postgres
> public | spatial_ref_sys | table | postgres
> (12 rows)
>
>
>
> routing=# \d eastlegon
> Table "public.eastlegon"
> Column | Type |
> Modifiers
>
>
> --------------+---------------------------+-------------------------------------
> --------------------
> gid | integer | not null default
> nextval('eastlegon_
> gid_seq'::regclass)
> length | double precision |
> class_id | integer | not null
> postcode | character varying(254) |
> name | character varying(100) |
> the_geom | geometry(LineString,4326) |
> source | integer |
> target | integer |
> cost_len | double precision |
> cost_time | double precision |
> rcost_len | double precision |
> rcost_time | double precision |
> x1 | double precision |
> y1 | double precision |
> x2 | double precision |
> y2 | double precision |
> to_cost | double precision |
> rule | text |
> isolated | integer |
> reverse_cost | double precision |
> Indexes:
> "eastlegon_pkey" PRIMARY KEY, btree (gid)
> "eastlegon_source_idx" btree (source)
> "eastlegon_target_idx" btree (target)
> "eastlegon_the_geom_gidx" gist (the_geom)
> "source_idx" btree (source)
> "target_idx" btree (target)
>
>
> My aim is simply to follow the workshop and display my routes using at
> least dijkstra algorithm in openlayers 2 (or 3 if it's not too
> complicated). I try installing the wrappers in the workshop but I get
> syntax errors.
>
> I'm sure my routing is properly set up since I can display routes using
> the pgRouting layer plugin for QGIS. I also have the layer stored properly
> in Geoserver. I can already display the layers as a WMS layer using
> openlayers. My only challenge is to display the route dynamically in a web
> browser.
>
> I need pointers in the right direction. My skills are still basic though,
> but I'm willing to learn.
>
> Thanks a lot for your help.
>
> Emmanuel
>
>
> On 4 January 2014 05:59, Stephen Woodbridge <woodbri at swoodbridge.com>wrote:
>
>> Emmanuel,
>>
>> I wrote the legacy.sql more as an example to document how things changed,
>> but it was never tested as we did not plan on supporting it. The idea
>> behind it was to provide and an example to help people convert their
>> existing applications to use the new functions.
>>
>> Part of the problem with the legacy code is that it had not test suite
>> and the code was very buggy. It also was haphazard because of its evolution
>> and random things being added that people thought might be nice to have.
>>
>> The best thing to do is use the new workshop and if you have questions or
>> get stuck to ask the list and we will try to help you sort them out.
>>
>> Best regards,
>> -Steve
>>
>>
>> On 1/3/2014 11:28 PM, Emmanuel Adegboye wrote:
>>
>>> Regina.
>>>
>>> It was from the foss4g 2007 workshop. The most recent worshops include
>>> wrapper functions which I am not familiar with so I also get stuck at
>>> some point.
>>>
>>> What I am trying to do is to create a simple application that allows
>>> users to set a start and end point and calculate the route using any one
>>> of the major algorithms like the workshop describes.
>>>
>>> So far, the user interface (based on openlayers 2.10) works well and
>>> displays both points. I get an error in firebug when I click the
>>> 'calculate route' button and the php script is called, and this was
>>> after I installed legacy.sql. I get "ERROR: type "geoms" does not exist"
>>> when installing pgrouting.sql.
>>>
>>> Here's the error from firebug:
>>>
>>> |<br />
>>> ||<b>Warning</b>: pg_query(): Query failed: ERROR: parse error -
>>> invalid geometry
>>> ||HINT: "POINT( )" <-- parse error at position 9 within
>>> geometry in <b>C:\ms4w\Apache\htdocs||\hgt\routing.php</b> on line
>>> <b>42</b><br />
>>> ||<br />
>>> ||<b>Warning</b>: pg_fetch_result() expects parameter 1 to be resource,
>>> boolean given in <b>C:\ms4w\Apache||\htdocs\hgt\routing.php</b> on line
>>> <b>44</b><br />
>>> ||<br />
>>> ||<b>Warning</b>: pg_fetch_result() expects parameter 1 to be resource,
>>> boolean given in <b>C:\ms4w\Apache||\htdocs\hgt\routing.php</b> on line
>>> <b>45</b><br />
>>> ||<br />
>>> ||<b>Warning</b>: pg_fetch_result() expects parameter 1 to be resource,
>>> boolean given in <b>C:\ms4w\Apache||\htdocs\hgt\routing.php</b> on line
>>> <b>46</b><br />
>>> ||<br />
>>> ||<b>Warning</b>: pg_fetch_result() expects parameter 1 to be resource,
>>> boolean given in <b>C:\ms4w\Apache||\htdocs\hgt\routing.php</b> on line
>>> <b>47</b><br />
>>> ||<br />
>>> ||<b>Warning</b>: pg_query(): Query failed: ERROR: parse error -
>>> invalid geometry
>>> ||HINT: "POINT( )" <-- parse error at position 9 within
>>> geometry in <b>C:\ms4w\Apache\htdocs||\hgt\routing.php</b> on line
>>> <b>42</b><br />
>>> ||<br />
>>> ||<b>Warning</b>: pg_fetch_result() expects parameter 1 to be resource,
>>> boolean given in <b>C:\ms4w\Apache||\htdocs\hgt\routing.php</b> on line
>>> <b>44</b><br />
>>> ||<br />
>>> ||<b>Warning</b>: pg_fetch_result() expects parameter 1 to be resource,
>>> boolean given in <b>C:\ms4w\Apache||\htdocs\hgt\routing.php</b> on line
>>> <b>45</b><br />
>>> ||<br />
>>> ||<b>Warning</b>: pg_fetch_result() expects parameter 1 to be resource,
>>> boolean given in <b>C:\ms4w\Apache||\htdocs\hgt\routing.php</b> on line
>>> <b>46</b><br />
>>> ||<br />
>>> ||<b>Warning</b>: pg_fetch_result() expects parameter 1 to be resource,
>>> boolean given in <b>C:\ms4w\Apache||\htdocs\hgt\routing.php</b> on line
>>> <b>47</b><br />
>>> ||<br />
>>> ||<b>Warning</b>: pg_query(): Query failed: in
>>> <b>C:\ms4w\Apache\htdocs\hgt\routing.php</b> on line||<b>108</b><br />
>>> ||<br />
>>> ||<b>Warning</b>: pg_fetch_assoc() expects parameter 1 to be resource,
>>> boolean given in <b>C:\ms4w\Apache||\htdocs\hgt\routing.php</b> on line
>>> <b>115</b><br />
>>> ||<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
>>> ||<route>
>>> ||</route>|
>>>
>>>
>>>
>>> And the html document:
>>>
>>>
>>> <!DOCTYPE html>
>>>
>>> <html>
>>>
>>> <head>
>>>
>>> <title>pgRouting Demo</title>
>>>
>>> <link rel="stylesheet" href="openlayers/theme/default/style.css"
>>> type="text/css">
>>>
>>> <style>
>>>
>>> #map-id {
>>>
>>> width: 100%;
>>>
>>> height: 512px;
>>>
>>> border: 1px solid black;
>>>
>>> }
>>>
>>> </style>
>>>
>>> <script src="openlayers/OpenLayers.js"></script>
>>>
>>> </head>
>>>
>>> <body>
>>>
>>> <div id="map-id"></div>
>>>
>>> <script>
>>>
>>> var SinglePoint = OpenLayers.Class.create();
>>>
>>> SinglePoint.prototype = OpenLayers.Class.inherit(OpenLayers.Handler.Point,
>>> {
>>>
>>> createFeature: function(evt) {
>>>
>>> this.control.layer.removeFeatures(this.control.layer.features);
>>>
>>> OpenLayers.Handler.Point.prototype.createFeature.apply(this, arguments);
>>>
>>> }
>>>
>>> });
>>>
>>> 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 result_style = OpenLayers.Util.applyDefaults({
>>>
>>> strokeWidth: 3,
>>>
>>> strokeColor: "#ff0000",
>>>
>>> fillOpacity: 0
>>>
>>> }, OpenLayers.Feature.Vector.style['default']);
>>>
>>> // global variables
>>>
>>> var map, parser, start, stop, result, controls;
>>>
>>> var format = 'image/png';
>>>
>>> var bounds = new OpenLayers.Bounds(
>>>
>>> -0.178166372987693, 5.62323476776518,
>>>
>>> -0.140817506885068, 5.64840001174401
>>>
>>> );
>>>
>>> var options = {
>>>
>>> controls: [],
>>>
>>> maxExtent: bounds,
>>>
>>> maxResolution: 0.0001458940082134,
>>>
>>> projection: "EPSG:4326",
>>>
>>> units: 'degrees'
>>>
>>> };
>>>
>>> map = new OpenLayers.Map("map-id", options);
>>>
>>> boundary = new OpenLayers.Layer.WMS(
>>>
>>> "East Legon - Boundary",
>>> "http://localhost:8080/geoserver/pgrouting/wms",
>>>
>>> {
>>>
>>> LAYERS: 'pgrouting:eastlegonboundary',
>>>
>>> STYLES: '',
>>>
>>> format: format,
>>>
>>> tiled: true,
>>>
>>> tilesOrigin : map.maxExtent.left + ',' +
>>> map.maxExtent.bottom
>>>
>>> },
>>>
>>> {
>>>
>>> buffer: 0,
>>>
>>> displayOutsideMaxExtent: true,
>>>
>>> isBaseLayer: true,
>>>
>>> yx : {'EPSG:4326' : true}
>>>
>>> }
>>>
>>> );
>>>
>>> eastlegon = new OpenLayers.Layer.WMS(
>>>
>>> "East Legon",
>>> "http://localhost:8080/geoserver/pgrouting/wms",
>>>
>>> {
>>>
>>> LAYERS: 'pgrouting:eastlegon',
>>>
>>> STYLES: '',
>>>
>>> format: format,
>>>
>>> transparent: true,
>>>
>>> tiled: true,
>>>
>>> tilesOrigin : map.maxExtent.left + ',' +
>>> map.maxExtent.bottom
>>>
>>> },
>>>
>>> {
>>>
>>> buffer: 0,
>>>
>>> displayOutsideMaxExtent: true,
>>>
>>> isBaseLayer: false,
>>>
>>> yx : {'EPSG:4326' : true}
>>>
>>> }
>>>
>>> );
>>>
>>> start = new OpenLayers.Layer.Vector("Start Point", {style: start_style});
>>>
>>> stop = new OpenLayers.Layer.Vector("End Point", {style: stop_style});
>>>
>>> result = new OpenLayers.Layer.Vector("Routing Results",
>>>
>>> {style: result_style});
>>>
>>> map.addLayers([boundary, eastlegon, start, stop, result]);
>>>
>>> map.addControl(new OpenLayers.Control.LayerSwitcher());
>>>
>>> map.addControl(new OpenLayers.Control.PanPanel());
>>>
>>> map.addControl(new OpenLayers.Control.ZoomPanel());
>>>
>>> map.addControl(new OpenLayers.Control.ScaleLine());
>>>
>>> map.addControl(new OpenLayers.Control.Navigation());
>>>
>>> map.addControl(new OpenLayers.Control.MousePosition());
>>>
>>> map.zoomToExtent(bounds);
>>>
>>> // controls
>>>
>>> controls = {
>>>
>>> start: new OpenLayers.Control.DrawFeature(start, SinglePoint),
>>>
>>> stop: new OpenLayers.Control.DrawFeature(stop, SinglePoint)
>>>
>>> }
>>>
>>> for (var key in controls) {
>>>
>>> map.addControl(controls[key]);
>>>
>>> }
>>>
>>> function toggleControl(element) {
>>>
>>> for (key in controls) {
>>>
>>> if (element.value == key && element.checked) {
>>>
>>> controls[key].activate();
>>>
>>> } else {
>>>
>>> controls[key].deactivate();
>>>
>>> }
>>>
>>> }
>>>
>>> }
>>>
>>> function compute() {
>>>
>>> var startPoint = start.features[0];
>>>
>>> var stopPoint = stop.features[0];
>>>
>>>
>>> if (startPoint && stopPoint) {
>>>
>>> var result = {
>>>
>>> startpoint: startPoint.geometry.x + ' ' +
>>> startPoint.geometry.y,
>>>
>>> finalpoint: stopPoint.geometry.x + ' ' +
>>> stopPoint.geometry.y,
>>>
>>> method: OpenLayers.Util.getElement('method').value,
>>>
>>> region: "eastlegon",
>>>
>>> srid: "4326"
>>>
>>> };
>>>
>>> OpenLayers.loadURL("./routing.php",
>>>
>>>
>>> OpenLayers.Util.getParameterString(result),
>>>
>>> null,
>>>
>>> displayRoute);
>>>
>>> }
>>>
>>> }
>>>
>>>
>>> function displayRoute(response) {
>>>
>>> if (response && response.responseXML) {
>>>
>>> // erase the previous results
>>>
>>> result.removeFeatures(result.features);
>>>
>>>
>>> // parse the features
>>>
>>> var edges =
>>> response.responseXML.getElementsByTagName('edge');
>>>
>>> var features = [];
>>>
>>> for (var i = 0; i < edges.length; i++) {
>>>
>>> var g =
>>> parser.read(edges[i].getElementsByTagName('wkt')[0].textContent);
>>>
>>> features.push(new OpenLayers.Feature.Vector(g));
>>>
>>> }
>>>
>>> result.addFeatures(features);
>>>
>>> }
>>>
>>> }
>>>
>>> </script>
>>>
>>> <ul>
>>>
>>> <li>
>>>
>>> <input type="radio" name="control" id="noneToggle"
>>>
>>> onclick="toggleControl(this);" checked="checked" />
>>>
>>> <label for="noneToggle">navigate</label>
>>>
>>> </li>
>>>
>>> <li>
>>>
>>> <input type="radio" name="control" value="start" id="startToggle"
>>>
>>> onclick="toggleControl(this);" />
>>>
>>> <label for="startToggle">set start point</label>
>>>
>>> </li>
>>>
>>> <li>
>>>
>>> <input type="radio" name="control" value="stop" id="stopToggle"
>>>
>>> onclick="toggleControl(this);" />
>>>
>>> <label for="stopToggle">set stop point</label>
>>>
>>> </li>
>>>
>>> </ul>
>>>
>>> <select id="method">
>>>
>>> <option value="SPD">Shortest Path Dijkstra - undirected (BBox)</option>
>>>
>>> <option value="SPA">Shortest Path A Star - undirected</option>
>>>
>>> <option value="SPS">Shortest Path Shooting Star</option>
>>>
>>> </select>
>>>
>>> <button onclick="compute()">Calculate Route</button>
>>>
>>> </body>
>>>
>>> </html>
>>>
>>>
>>>
>>> On Jan 4, 2014 3:45 AM, "Paragon Corporation" <lr at pcorp.us
>>> <mailto:lr at pcorp.us>> wrote:
>>>
>>> __
>>>
>>> Emmanuel,
>>> That PHP code looks like it was written for pgRouting 1.0. Which
>>> workshop did you get that from?
>>> Most of the pgRouting 2.0 functions are prefixed with pgr_. There
>>> should be a file in your install called
>>> In C:/Program
>>> Files/PostgreSQL/9.2/share/extensions/pgrouting_legacy.sql which
>>> installs the 1.0 function names. You could try using that though I
>>> haven't personally had a need to.
>>> One other thing I did notice about this code which I think is a bug
>>> is that although PG_PORT is defined, it doesn't seem to be used for
>>> pg_connect, so will not work without modification if you are
>>> runnning postgres on non-default port (5432).
>>> Hope that helps,
>>> Regina
>>> http://www.postgis.us
>>> http://www.bostongis.com
>>> http://www.paragoncorporation.com
>>>
>>> ------------------------------------------------------------
>>> ------------
>>> *From:* pgrouting-users-bounces at lists.osgeo.org
>>> <mailto:pgrouting-users-bounces at lists.osgeo.org>
>>> [mailto:pgrouting-users-bounces at lists.osgeo.org
>>> <mailto:pgrouting-users-bounces at lists.osgeo.org>] *On Behalf Of
>>> *Emmanuel Adegboye
>>> *Sent:* Friday, January 03, 2014 4:42 PM
>>> *To:* pgrouting-users at lists.osgeo.org
>>> <mailto:pgrouting-users at lists.osgeo.org>
>>> *Subject:* [pgrouting-users] Issues with pgrouting workshop code in
>>>
>>> postgis2.11/pgrouting 2.0
>>>
>>> I'm having issues getting the pgrouting workshop to work on
>>> Postgresql 9.2/PostGIS 2.11 and pgrouting 2.0 on windows.
>>>
>>> How can I rewrite the following php/sql code and make it compatible
>>> with my version:
>>>
>>> |<?php
>>>
>>> // Database connection settings
>>> define("PG_DB" , "routing");
>>> define("PG_HOST", "localhost");
>>> define("PG_USER", "postgres");
>>> define("PG_PORT", "5432");
>>> define("PG_PASSWD", "*******");
>>> define("TABLE", "eastlegon");
>>>
>>> $counter= $pathlength= 0;
>>>
>>>
>>> // Retrieve start point
>>> $start= split('',$_REQUEST['startpoint']);
>>> $startPoint= array($start[0], $start[1]);
>>>
>>>
>>> // Retrieve end point
>>> $end= split('',$_REQUEST['finalpoint']);
>>> $endPoint= array($end[0], $end[1]);
>>>
>>>
>>> // Find the nearest edge
>>> $startEdge= findNearestEdge($startPoint);
>>> $endEdge= findNearestEdge($endPoint);
>>>
>>>
>>> // FUNCTION findNearestEdge
>>> function findNearestEdge($lonlat) {
>>>
>>> // Connect to database
>>> $con= pg_connect("dbname=".PG_DB." host=".PG_HOST."
>>> user=".PG_USER." password=".PG_PASSWD);
>>>
>>> $sql= "SELECT gid, source, target, the_geom,
>>>
>>> distance(the_geom, GeometryFromText(
>>> 'POINT(".$lonlat[0]."".$lonlat[1].")', 4326)) AS
>>> dist
>>> FROM ".TABLE."
>>> WHERE the_geom && setsrid(
>>> 'BOX3D(".($lonlat[0]-200)."
>>> ".($lonlat[1]-200).",
>>> ".($lonlat[0]+200)."
>>> ".($lonlat[1]+200).")'::box3d, 4326)
>>> ORDER BY dist LIMIT 1";
>>>
>>> $query= pg_query($con,$sql);
>>>
>>>
>>> $edge['gid'] = pg_fetch_result($query, 0, 0);
>>> $edge['source'] = pg_fetch_result($query, 0, 1);
>>> $edge['target'] = pg_fetch_result($query, 0, 2);
>>> $edge['the_geom'] = pg_fetch_result($query, 0, 3);
>>>
>>> // Close database connection
>>> pg_close($con);
>>>
>>> return $edge;
>>> }
>>>
>>> // Select the routing algorithm
>>> switch($_REQUEST['method']) {
>>>
>>> case 'SPD' : // Shortest Path Dijkstra
>>>
>>> $sql= "SELECT rt.gid, AsText(rt.the_geom) AS wkt,
>>>
>>> length(rt.the_geom) AS length, ".TABLE.".id
>>> FROM ".TABLE.",
>>> (SELECT gid, the_geom
>>> FROM dijkstra_sp_delta(
>>> '".TABLE."',
>>> ".$startEdge['source'].",
>>> ".$endEdge['target'].",
>>> 3000)
>>> ) as rt
>>> WHERE ".TABLE.".gid=rt.gid;";
>>> break;
>>>
>>> case 'SPA' : // Shortest Path A*
>>>
>>> $sql= "SELECT rt.gid, AsText(rt.the_geom) AS wkt,
>>>
>>> length(rt.the_geom) AS length, ".TABLE.".id
>>> FROM ".TABLE.",
>>> (SELECT gid, the_geom
>>> FROM astar_sp_delta(
>>> '".TABLE."',
>>> ".$startEdge['source'].",
>>> ".$endEdge['target'].",
>>> 3000)
>>> ) as rt
>>> WHERE ".TABLE.".gid=rt.gid;";
>>> |
>>>
>>>
>>> _______________________________________________
>>> Pgrouting-users mailing list
>>> Pgrouting-users at lists.osgeo.org <mailto:Pgrouting-users at lists.
>>> osgeo.org>
>>> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>>> ...
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> Pgrouting-users mailing list
>>> Pgrouting-users at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>>>
>>>
>> _______________________________________________
>> Pgrouting-users mailing list
>> Pgrouting-users at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20140109/b35f02f4/attachment-0001.html>
More information about the Pgrouting-users
mailing list