¶Ô±ÈÐÂÎļþ |
| | |
| | | ----------------------------------------------------------------------------------------------------------------------------- |
| | | CREATE EXTENSION PostGIS; |
| | | CREATE EXTENSION pgRouting; |
| | | create extension postgis_raster; |
| | | create extension postgis_topology; |
| | | CREATE EXTENSION fuzzystrmatch; |
| | | CREATE EXTENSION postgis_tiger_geocoder; |
| | | CREATE EXTENSION address_standardizer; |
| | | ----------------------------------------------------------------------------------------------------------------------------- |
| | | --å é¤å·²åå¨ç彿° |
| | | DROP FUNCTION pgr_fromAtoB(tbl varchar,startx float, starty float,endx float,endy float); |
| | | |
| | | --DROP FUNCTION pgr_fromAtoB(varchar, double precision, double precision |
| | | -- double precision, double precision); |
| | | --åºäºä»»æä¸¤ç¹ä¹é´çæçè·¯å¾åæ |
| | | CREATE OR REPLACE FUNCTION pgr_fromAtoB( |
| | | IN tbl varchar,--æ°æ®åºè¡¨å |
| | | IN x1 double precision,--èµ·ç¹xåæ |
| | | IN y1 double precision,--èµ·ç¹yåæ |
| | | IN x2 double precision,--ç»ç¹xåæ |
| | | IN y2 double precision,--ç»ç¹yåæ |
| | | OUT seq integer,--éè·¯åºå· |
| | | OUT gid integer, |
| | | OUT name text,--éè·¯å |
| | | OUT heading double precision, |
| | | OUT cost double precision,--æ¶è |
| | | OUT geom geometry--éè·¯å ä½éå |
| | | ) |
| | | RETURNS SETOF record AS |
| | | $BODY$ |
| | | DECLARE |
| | | sql text; |
| | | rec record; |
| | | source integer; |
| | | target integer; |
| | | point integer; |
| | | |
| | | BEGIN |
| | | -- æ¥è¯¢è·ç¦»åºåç¹æè¿çéè·¯èç¹ |
| | | EXECUTE 'SELECT id::integer FROM '|| quote_ident(tbl) ||'_vertices_pgr |
| | | ORDER BY the_geom <-> ST_GeometryFromText(''POINT(' |
| | | || x1 || ' ' || y1 || ')'',4326) LIMIT 1' INTO rec; |
| | | source := rec.id; |
| | | |
| | | -- æ¥è¯¢è·ç¦»ç®çå°æè¿çéè·¯èç¹ |
| | | EXECUTE 'SELECT id::integer FROM '|| quote_ident(tbl) ||'_vertices_pgr |
| | | ORDER BY the_geom <-> ST_GeometryFromText(''POINT(' |
| | | || x2 || ' ' || y2 || ')'',4326) LIMIT 1' INTO rec; |
| | | target := rec.id; |
| | | |
| | | -- æçè·¯å¾æ¥è¯¢ |
| | | seq := 0; |
| | | sql := 'SELECT gid, geom, node as name, cost, source, target, |
| | | ST_Reverse(geom) AS flip_geom FROM ' || |
| | | 'pgr_dijkstra(''SELECT gid as id, |
| | | source::integer,target::integer,' |
| | | || 'length::float AS cost FROM ' |
| | | || quote_ident(tbl) || ''', ' |
| | | || source || ', ' || target |
| | | || ' ,false) as di, ' |
| | | || quote_ident(tbl) || ' WHERE di.edge = gid ORDER BY seq'; |
| | | |
| | | |
| | | -- Remember start point |
| | | point := source; |
| | | |
| | | FOR rec IN EXECUTE sql |
| | | LOOP |
| | | -- Flip geometry (if required) |
| | | IF ( point != rec.source ) THEN |
| | | rec.geom := rec.flip_geom; |
| | | point := rec.source; |
| | | ELSE |
| | | point := rec.target; |
| | | END IF; |
| | | |
| | | -- Calculate heading (simplified) |
| | | EXECUTE 'SELECT degrees( ST_Azimuth( |
| | | ST_StartPoint(''' || rec.geom::text || '''), |
| | | ST_EndPoint(''' || rec.geom::text || ''') ) )' |
| | | INTO heading; |
| | | |
| | | -- Return record |
| | | seq := seq + 1; |
| | | gid := rec.gid; |
| | | name := rec.name; |
| | | cost := rec.cost; |
| | | geom := rec.geom; |
| | | RETURN NEXT; |
| | | END LOOP; |
| | | RETURN; |
| | | END; |
| | | $BODY$ |
| | | LANGUAGE 'plpgsql' VOLATILE STRICT; |
| | | ----------------------------------------------------------------------------------------------------------------------------- |
| | | --å é¤å·²åå¨ç彿° |
| | | DROP FUNCTION pgr_fromAtoB(tbl varchar, startx float, starty float, endx float, endy float); |
| | | |
| | | --DROP FUNCTION pgr_fromAtoB(varchar, double precision, double precision double precision, double precision); |
| | | --åºäºä»»æä¸¤ç¹ä¹é´çæçè·¯å¾åæ |
| | | CREATE OR REPLACE FUNCTION pgr_fromAtoB( |
| | | IN tbl varchar,--æ°æ®åºè¡¨å |
| | | IN x1 double precision,--èµ·ç¹xåæ |
| | | IN y1 double precision,--èµ·ç¹yåæ |
| | | IN x2 double precision,--ç»ç¹xåæ |
| | | IN y2 double precision,--ç»ç¹yåæ |
| | | OUT seq integer,--éè·¯åºå· |
| | | OUT gid integer, |
| | | OUT name text,--éè·¯å |
| | | OUT heading double precision, |
| | | OUT cost double precision,--æ¶è |
| | | OUT geom geometry--éè·¯å ä½éå |
| | | ) RETURNS SETOF record AS $BODY$ |
| | | DECLARE |
| | | sql text; |
| | | rec record; |
| | | source integer; |
| | | target integer; |
| | | point integer; |
| | | BEGIN |
| | | -- æ¥è¯¢è·ç¦»åºåç¹æè¿çéè·¯èç¹ |
| | | EXECUTE 'SELECT id::integer FROM ' || quote_ident(tbl) |
| | | || '_vertices_pgr ORDER BY the_geom <-> ST_GeometryFromText(''POINT(' |
| | | || x1 || ' ' || y1 || ')'',4326) LIMIT 1' INTO rec; |
| | | source := rec.id; |
| | | |
| | | -- æ¥è¯¢è·ç¦»ç®çå°æè¿çéè·¯èç¹ |
| | | EXECUTE 'SELECT id::integer FROM ' || quote_ident(tbl) |
| | | || '_vertices_pgr ORDER BY the_geom <-> ST_GeometryFromText(''POINT(' |
| | | || x2 || ' ' || y2 || ')'',4326) LIMIT 1' INTO rec; |
| | | target := rec.id; |
| | | |
| | | -- æçè·¯å¾æ¥è¯¢ |
| | | seq := 0; |
| | | sql := 'SELECT gid, geom, node as name, cost, source, target, ST_Reverse(geom) AS flip_geom FROM ' |
| | | || 'pgr_dijkstra(''SELECT gid as id, source::integer,target::integer,' |
| | | || 'length::float AS cost FROM ' |
| | | || quote_ident(tbl) || ''', ' |
| | | || source || ', ' || target |
| | | || ' ,false) as di, ' |
| | | || quote_ident(tbl) || ' WHERE di.edge = gid ORDER BY seq'; |
| | | |
| | | -- Remember start point |
| | | point := source; |
| | | FOR rec IN EXECUTE sql LOOP |
| | | -- Flip geometry (if required) |
| | | IF ( point != rec.source ) THEN |
| | | rec.geom := rec.flip_geom; |
| | | point := rec.source; |
| | | ELSE |
| | | point := rec.target; |
| | | END IF; |
| | | |
| | | -- Calculate heading (simplified) |
| | | EXECUTE 'SELECT degrees( ST_Azimuth(ST_StartPoint(''' |
| | | || rec.geom::text || '''),ST_EndPoint(''' || rec.geom::text || ''') ) )' INTO heading; |
| | | |
| | | -- Return record |
| | | seq := seq + 1; |
| | | gid := rec.gid; |
| | | name := rec.name; |
| | | cost := rec.cost; |
| | | geom := rec.geom; |
| | | RETURN NEXT; |
| | | END LOOP; |
| | | RETURN; |
| | | END; |
| | | $BODY$ LANGUAGE 'plpgsql' VOLATILE STRICT; |
| | | ----------------------------------------------------------------------------------------------------------------------------- |
| | | select pgr_fromAtoB('public.lrdl', 116.78999, 39.9468, 116.80458, 39.94758); |
| | | |
| | | -- pgr_kdijkstraPath,pgr_dijkstra |
| | | select pgr_shortestpath('public.lrdl', 116.78999, 39.9468, 116.80458, 39.94758); |
| | | select * from public.lrdl limit 10; |
| | | |
| | | ----------------------------------------------------------------------------------------------------------------------------- |