PostgreSQL , PostGIS , PgRouting , Google Maps y Openlayers

Just a Demo Test using PostGIS , PgRouting , Google Maps y Openlayers, there is the sql querys. SELECT UpdateGeometrySRID('public', 'cali_ruteo', 'the_geom', 4326); ALTER TABLE cali_ruteo ADD COLUMN length double precision; ALTER TABLE cali_ruteo ADD COLUMN cost double precision; ALTER TABLE cali_ruteo ADD COLUMN reverse_cost double precision; ALTER TABLE cali_ruteo ADD COLUMN to_cost int4; ALTER TABLE cali_ruteo ADD COLUMN rule text; ALTER TABLE cali_ruteo ADD column source int4; ALTER TABLE cali_ruteo ADD column target int4; ALTER TABLE cali_ruteo ADD column oneway text; ALTER TABLE cali_ruteo ADD column id serial; ALTER TABLE cali_ruteo ADD column source_id int4; ALTER TABLE cali_ruteo ADD column target_id int4; SELECT assign_vertex_id('cali_ruteo',0.0001,'the_geom','gid'); UPDATE cali_ruteo SET length = length(the_geom); UPDATE cali_ruteo SET source=source_id , target=target_id UPDATE cali_ruteo SET cost=length(the_geom) UPDATE cali_ruteo SET reverse_cost=length(the_geom) UPDATE cali_ruteo SET reverse_cost=1000 WHERE oneway in ('YES'); CREATE INDEX source_idx ON cali_ruteo(source); CREATE INDEX target_idx ON cali_ruteo(target); CREATE INDEX geom_idx ON cali_ruteo USING GIST(the_geom GIST_GEOMETRY_OPS); DROP TABLE vertices_tmp; DELETE FROM geometry_columns WHERE f_table_name='vertices_tmp'; -------SQL TEST----------------------- -- # Shortest Path Dijkstra -- well known shortest path algorithm with exact result -- # Shortest Path A* -- shortest path algorithm with heuristics -- # Shortest Path Shooting Star -- shortest path algorithm for real road networks (cares about restrictions) with heuristics SELECT gid as id, source, target, cost, reverse_cost,x1, y1,x2, y2, rule, to_cost FROM cali_ruteo order by id; -- Calculamos el camino optimo entre 20 y 8 SELECT * FROM shortest_path_shooting_star('SELECT gid as id, source, target, cost, reverse_cost,x1, y1,x2, y2, rule, to_cost FROM cali_ruteo order by id',20,8,true,true); --Shortest Path Dijkstra SELECT rt.gid, AsText(rt.the_geom) AS wkt, length(rt.the_geom) AS length, cali_ruteo.id FROM cali_ruteo, (SELECT gid, the_geom FROM dijkstra_sp_delta('cali_ruteo',20, 8, 3000)) as rt WHERE cali_ruteo.gid=rt.gid; --// Shortest Path A* SELECT rt.gid, AsText(rt.the_geom) AS wkt, length(rt.the_geom) AS length, cali_ruteo.id FROM cali_ruteo, (SELECT gid, the_geom FROM astar_sp_delta('cali_ruteo',20, 8, 3000)) as rt WHERE cali_ruteo.gid=rt.gid; ---//Shooting-Star core function SELECT * FROM shortest_path_shooting_star(' SELECT gid as id, source::integer, target::integer, length::double precision as cost, x1, y1, x2, y2, rule, to_cost FROM cali_ruteo', 20, 8, false, false); ---//Wrapper function with bounding box SELECT gid, AsText(the_geom) AS the_geom FROM shootingstar_sp('cali_ruteo', 20, 8, 5000, 'length',true,true); --// Shortest Path Shooting* SELECT rt.gid, AsText(rt.the_geom) AS wkt, length(rt.the_geom) AS length, cali_ruteo.id FROM cali_ruteo, (SELECT gid, the_geom FROM shootingstar_sp('cali_ruteo',20,29000,3000, 'length', false, false)) as rt WHERE cali_ruteo.gid=rt.gid; --// Shortest Path SELECT * FROM shortest_path('SELECT gid AS id, source::int4, target::int4, length::double precision AS cost,length::double precision AS reverse_cost FROM cali_ruteo', 3, 7, true, true); --// Driving Distance Calculation SELECT * FROM driving_distance('SELECT gid AS id,source,target, length::double precision AS cost FROM cali_ruteo',10549,0.01,false,false); --// Traveling Sales Person (TSP) SELECT * FROM tsp('SELECT distinct source AS source_id, x1::double precision AS x, y1::double precision AS y FROM cali_ruteo WHERE source IN (20,56,3,5,23)', '20,56,3,5,23', 23); select ST_Extent(the_geom) from cali_ruteo