[postgis-users] dijkstra.sql
Bayu Kurniawan
bayu_kr at yahoo.com
Tue Apr 3 06:44:45 PDT 2007
This is sql file in dijkstra.sql, i'm using lib with the package in
http://cartoweb.org/downloads.html#pgdijkstra
i'm using mapserver ms4w, postgresql 8.2 and postgis 1.2.0-1 in windows system
--
-- Shortest path algorithm for PostgreSQL
--
-- Copyright (c) 2005 Sylvain Pasche
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
--
CREATE TYPE path_result AS (step integer, vertex_id integer, edge_id integer, cost float8);
-----------------------------------------------------------------------
-- Core function for shortest_path computation
-- See README for description
-----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION shortest_path(sql text, source_id integer, target_id integer,
directed boolean, has_reverse_cost boolean)
RETURNS SETOF path_result
AS '$libdir/libdijkstra.dll'
LANGUAGE 'C' IMMUTABLE STRICT;
-----------------------------------------------------------------------
-- Drops the vertices and edges tables related to the given geom_table
-----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION drop_graph_tables(geom_table varchar) RETURNS void AS
$$
DECLARE
vertices_table varchar := quote_ident(geom_table) || '_vertices';
edges_table varchar := quote_ident(geom_table) || '_edges';
BEGIN
BEGIN
EXECUTE 'DROP TABLE ' || vertices_table;
EXCEPTION
WHEN UNDEFINED_TABLE THEN
END;
BEGIN
EXECUTE 'DROP TABLE ' || edges_table;
EXCEPTION
WHEN UNDEFINED_TABLE THEN
END;
RETURN;
END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT;
-----------------------------------------------------------------------
-- This function should not be used directly. Use create_graph_tables instead
--
-- Insert a vertex into the vertices table if not already there, and
-- return the id of the newly inserted or already existing element
-----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION insert_vertex(vertices_table varchar, geom_id anyelement) RETURNS int AS
$$
DECLARE
vertex_id int;
myrec record;
BEGIN
LOOP
FOR myrec IN EXECUTE 'SELECT id FROM ' || quote_ident(vertices_table) ||
' WHERE geom_id = ' || quote_literal(geom_id) LOOP
IF myrec.id IS NOT NULL THEN
RETURN myrec.id;
END IF;
END LOOP;
EXECUTE 'INSERT INTO ' || quote_ident(vertices_table) || ' (geom_id) VALUES (' || quote_literal(geom_id) || ')';
END LOOP;
END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT;
-----------------------------------------------------------------------
-- Create the vertices and edges tables from a table matching the
-- geometry schema described above.
-----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION create_graph_tables(geom_table varchar, column_type varchar)
RETURNS void AS
$$
DECLARE
geom record;
edge_id int;
myrec record;
source_id int;
target_id int;
vertices_table varchar := quote_ident(geom_table) || '_vertices';
edges_table varchar := quote_ident(geom_table) || '_edges';
BEGIN
EXECUTE 'CREATE TABLE ' || vertices_table || ' (id serial, geom_id '
|| quote_ident(column_type) || ' NOT NULL UNIQUE)';
EXECUTE 'CREATE INDEX ' || vertices_table || '_id_idx on ' || vertices_table || ' (id)';
EXECUTE 'CREATE TABLE ' || edges_table || ' (id serial, source int, target int, '
|| 'cost float8, reverse_cost float8, UNIQUE (source, target))';
EXECUTE 'CREATE INDEX ' || edges_table || '_source_target_idx on ' || edges_table || ' (source, target)';
FOR geom IN EXECUTE 'SELECT gid as id, ' || ' source_id AS source, ' ||
' target_id AS target FROM ' || quote_ident(geom_table) LOOP
SELECT INTO source_id insert_vertex(vertices_table, geom.source);
SELECT INTO target_id insert_vertex(vertices_table, geom.target);
edge_id := nextval(edges_table || '_id_seq');
EXECUTE 'INSERT INTO ' || edges_table || ' (id, source, target) VALUES ('
|| edge_id || ', '
|| quote_literal(source_id) || ', '
|| quote_literal(target_id) || ')';
EXECUTE 'UPDATE ' || quote_ident(geom_table) || ' SET edge_id = '
|| edge_id || ' WHERE gid = ' || quote_literal(geom.id);
END LOOP;
RETURN;
END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT;
----- Original Message ----
From: Rob Tester <robtester at gmail.com>
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Sent: Tuesday, April 3, 2007 8:22:01 PM
Subject: RE: [postgis-users] dijkstra.sql
<!--
_filtered {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}
_filtered {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}
_filtered {font-family:Consolas;panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:"Times New Roman", "serif";}
a:link, span.MsoHyperlink
{color:blue;text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;text-decoration:underline;}
pre
{margin:0in;margin-bottom:.0001pt;font-size:10.0pt;font-family:"Courier New";}
span.HTMLPreformattedChar
{font-family:Consolas;}
span.EmailStyle19
{font-family:"Calibri", "sans-serif";color:#1F497D;}
.MsoChpDefault
{font-size:10.0pt;}
_filtered {margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
{}
-->
What version of the lib did you install? Was it built against Postgre
8.1?
I would download the source and rebuild it against 8.2 then try
to install it again.
From:
postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Bayu
Kurniawan
Sent: Monday, April 02, 2007 6:39 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] dijkstra.sql
I have some problems with installation of the pgdikstra. Running
dijkstra.sql gives this error message:
psql:dijkstra.sql:32: ERROR: incompatible library "C:/Program Files/PostgreSQL/
8.2/lib/libdjikstra.dll" : missing magic block
HINT : Extension libraries are required to use the PG_MODULE_MAGIC macro
Any solutions for this?
Send instant messages to your online friends http://uk.messenger.yahoo.com
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Send instant messages to your online friends http://uk.messenger.yahoo.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070403/1d751720/attachment.html>
More information about the postgis-users
mailing list