[pgrouting-users] Issues with pgrouting workshop code in postgis2.11/pgrouting 2.0
Emmanuel Adegboye
eaadegboye at googlemail.com
Thu Jan 9 01:48:22 PST 2014
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/ce2ffbb4/attachment-0001.html>
More information about the Pgrouting-users
mailing list