Обращался за не безвозмездной помощью gis-lab.info/forum/viewtopic.php?f=6&t=13811 но, видимо, придется разбираться самостоятельно.
Необходимо расчитывать длину пути между двумя координатами по дорожному графу.
Устновлено:
postgres=# \dx
- List of installed extensions
Name | Version | Schema | Description
------------------+---------+------------+---------------------------------------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 2.0.1 | public | PostGIS geometry, geography, and raster spatial types and functions
postgis_topology | 2.0.1 | topology | PostGIS topology spatial types and functions
shp2pgsql highway-line.shp ways > ways.sql
\i ways.sql
- Table "public.ways"
Column | Type | Modifiers
----------+---------------------------+----------------------------------------------------
gid | integer | not null default nextval('ways_gid_seq'::regclass)
osm_id | double precision |
name | character varying(79) |
ref | character varying(59) |
highway | character varying(14) |
oneway | character varying(9) |
bridge | character varying(12) |
tunnel | character varying(3) |
maxspeed | character varying(12) |
lanes | character varying(17) |
geom | geometry(MultiLineString) |
source | integer |
target | integer |
Indexes:
"ways_pkey" PRIMARY KEY, btree (gid)
"source_idx" btree (source)
"target_idx" btree (target)
ALTER TABLE ways ADD COLUMN source integer;
ALTER TABLE ways ADD COLUMN target integer;
SELECT assign_vertex_id('ways', 0.00001, 'geom', 'gid');
пришлось заменить routing_topology.sql
Спойлер
CREATE OR REPLACE FUNCTION point_to_id(p geometry, tolerance double precision)
RETURNS BIGINT.
AS.
$$.
DECLARE
_r record;.
_id bigint;.
_srid integer;
BEGIN
BEGIN...
_srid := Find_SRID(user::text, 'vertices_tmp', 'the_geom');
EXCEPTION
WHEN SQLSTATE 'P0001' THEN
_srid := Find_SRID('public', 'vertices_tmp', 'the_geom');
END;
SELECT
ST_Distance(the_geom, p) AS d, id, the_geom
INTO _r FROM vertices_tmp WHERE
ST_DWithin(the_geom, p, tolerance)
ORDER BY d LIMIT 1; IF FOUND THEN
_id:= _r.id;
ELSE
INSERT INTO vertices_tmp(the_geom) VALUES (p); _id:=lastval();
END IF;
RETURN _id;
END; $$ LANGUAGE 'plpgsql' VOLATILE STRICT;
CREATE OR REPLACE FUNCTION assign_vertex_id(geom_table varchar, tolerance double precision, geo_cname varchar, gid_cname varchar)
RETURNS VARCHAR AS
$$
DECLARE
_r record;
source_id int;
target_id int;
srid integer;
BEGIN
BEGIN
DROP TABLE vertices_tmp;
EXCEPTION.
WHEN UNDEFINED_TABLE THEN
END;
EXECUTE 'CREATE TABLE vertices_tmp (id serial)';
BEGIN...
srid := Find_SRID(user::text, quote_ident(geom_table), quote_ident(geo_cname));
EXCEPTION
WHEN SQLSTATE 'P0001' THEN
srid := Find_SRID('public', quote_ident(geom_table), quote_ident(geo_cname));
END;
EXECUTE 'SELECT addGeometryColumn(''vertices_tmp'', ''the_geom'', '||srid||', ''POINT'', 2)';
CREATE INDEX vertices_tmp_idx ON vertices_tmp USING GIST (the_geom);
FOR _r IN EXECUTE 'SELECT ' || quote_ident(gid_cname) || ' AS id,'
|| ' ST_StartPoint(ST_GeometryN('|| quote_ident(geo_cname) ||', 1)) AS source,'
|| ' ST_EndPoint(ST_GeometryN('|| quote_ident(geo_cname) ||', 1)) as target'
|| ' FROM ' || quote_ident(geom_table) || ' WHERE ' || quote_ident(geo_cname) || ' IS NOT NULL '
LOOP
source_id := point_to_id(_r.source, tolerance);
target_id := point_to_id(_r.target, tolerance);
EXECUTE 'update ' || quote_ident(geom_table) ||.
' SET source = ' || source_id ||.
', target = ' || target_id ||.
' WHERE ' || quote_ident(gid_cname) || ' = ' || _r.id;
END LOOP;
RETURN 'OK';
END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT;
RETURNS BIGINT.
AS.
$$.
DECLARE
_r record;.
_id bigint;.
_srid integer;
BEGIN
BEGIN...
_srid := Find_SRID(user::text, 'vertices_tmp', 'the_geom');
EXCEPTION
WHEN SQLSTATE 'P0001' THEN
_srid := Find_SRID('public', 'vertices_tmp', 'the_geom');
END;
SELECT
ST_Distance(the_geom, p) AS d, id, the_geom
INTO _r FROM vertices_tmp WHERE
ST_DWithin(the_geom, p, tolerance)
ORDER BY d LIMIT 1; IF FOUND THEN
_id:= _r.id;
ELSE
INSERT INTO vertices_tmp(the_geom) VALUES (p); _id:=lastval();
END IF;
RETURN _id;
END; $$ LANGUAGE 'plpgsql' VOLATILE STRICT;
CREATE OR REPLACE FUNCTION assign_vertex_id(geom_table varchar, tolerance double precision, geo_cname varchar, gid_cname varchar)
RETURNS VARCHAR AS
$$
DECLARE
_r record;
source_id int;
target_id int;
srid integer;
BEGIN
BEGIN
DROP TABLE vertices_tmp;
EXCEPTION.
WHEN UNDEFINED_TABLE THEN
END;
EXECUTE 'CREATE TABLE vertices_tmp (id serial)';
BEGIN...
srid := Find_SRID(user::text, quote_ident(geom_table), quote_ident(geo_cname));
EXCEPTION
WHEN SQLSTATE 'P0001' THEN
srid := Find_SRID('public', quote_ident(geom_table), quote_ident(geo_cname));
END;
EXECUTE 'SELECT addGeometryColumn(''vertices_tmp'', ''the_geom'', '||srid||', ''POINT'', 2)';
CREATE INDEX vertices_tmp_idx ON vertices_tmp USING GIST (the_geom);
FOR _r IN EXECUTE 'SELECT ' || quote_ident(gid_cname) || ' AS id,'
|| ' ST_StartPoint(ST_GeometryN('|| quote_ident(geo_cname) ||', 1)) AS source,'
|| ' ST_EndPoint(ST_GeometryN('|| quote_ident(geo_cname) ||', 1)) as target'
|| ' FROM ' || quote_ident(geom_table) || ' WHERE ' || quote_ident(geo_cname) || ' IS NOT NULL '
LOOP
source_id := point_to_id(_r.source, tolerance);
target_id := point_to_id(_r.target, tolerance);
EXECUTE 'update ' || quote_ident(geom_table) ||.
' SET source = ' || source_id ||.
', target = ' || target_id ||.
' WHERE ' || quote_ident(gid_cname) || ' = ' || _r.id;
END LOOP;
RETURN 'OK';
END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT;
- postgres=# \d
List of relations
Schema | Name | Type | Owner
----------+---------------------+----------+----------
public | geography_columns | view | postgres
public | geometry_columns | view | postgres
public | raster_columns | view | postgres
public | raster_overviews | view | postgres
public | spatial_ref_sys | table | postgres
public | vertices_tmp | table | postgres
public | vertices_tmp_id_seq | sequence | postgres
public | ways | table | postgres
public | ways_gid_seq | sequence | postgres
topology | layer | table | postgres
topology | topology | table | postgres
topology | topology_id_seq | sequence | postgres
SELECT * FROM shortest_path_astar('SELECT gid AS id, source::int4,
target::int4, length::double precision AS cost, x1, y1, x2, y2
FROM ways',3, 7, false, false);
НО этот запрос ссылается на поле length которого нет в таблице ways.
Подскажите пожалуйста, откуда можно взять это поле? Или я все делаю не так?
ps: qgis при создании слоя из highway-line.shp нормально строит маршруты между двумя координатами и считает длину пути.