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 22:20, Emmanuel Adegboye <[email protected]
<mailto:[email protected]>> wrote:
Hello Daniel.
Thanks for your response.
I will take a look at the workshop again but I got stuck at some
point trying to write wrapper functions. I will post questions if I
get stuck, your answers would be invaluable.
Regards,
Emmanuel Adegboye
On 4 January 2014 04:38, Daniel Kastl <[email protected]
<mailto:[email protected]>> wrote:
Hi Emmanuel,
The workshop code you're posting is not the current one anymore
and won't work anymore with pgRouting 2.0.
If you want to use the "old" PHP function, then you need to
modify it:
* Write your own plpgsql wrapper function. You can take a look
at the examples in the new workshop:
http://workshop.pgrouting.org/chapters/wrapper.html
* Change the function name, arguments, etc. according to your
custom function in the PHP script.
Daniel
On Sat, Jan 4, 2014 at 6:43 AM, Emmanuel Adegboye
<[email protected] <mailto:[email protected]>>
wrote:
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;";
break;
case 'SPS' : // Shortest Path Shooting*
$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 shootingstar_sp(
'".TABLE."',
".$startEdge['gid'].",
".$endEdge['gid'].",
3000, 'length', false, false)
) as rt
WHERE ".TABLE.".gid=rt.gid;";
break;
} // close switch
// Database connection and query
$dbcon= pg_connect("dbname=".PG_DB." host=".PG_HOST."
user=".PG_USER." password=".PG_PASSWD);
$query= pg_query($dbcon,$sql);
// Return route as XML
$xml= '<?xml version="1.0" encoding="UTF-8"
standalone="yes"?>'."\n";
$xml .= "<route>\n";
// Add edges to XML file
while($edge=pg_fetch_assoc($query)) {
$pathlength += $edge['length'];
$xml .= "\t<edge id='".++$counter."'>\n";
$xml .= "\t\t<id>".$edge['id']."</id>\n";
$xml .= "\t\t<wkt>".$edge['wkt']."</wkt>\n";
$xml .=
"\t\t<length>".round(($pathlength/1000),3)."</length>\n";
$xml .= "\t</edge>\n";
}
$xml .= "</route>\n";
// Close database connection
pg_close($dbcon);
// Return routing result
header('Content-type: text/xml',true);
echo $xml;
?>|
Thanks,
_______________________________________________
postgis-users mailing list
[email protected]
<mailto:[email protected]>
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
--
Georepublic UG & Georepublic Japan
eMail: [email protected]
<mailto:[email protected]>
Web: http://georepublic.de <http://georepublic.de/>
_______________________________________________
postgis-users mailing list
[email protected] <mailto:[email protected]>
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
