[pgrouting-users] ArcGIS service area with pgRouting

[Ian]

Skipped content of type multipart/alternative-------------- next part --------------
CREATE TYPE networkcalcrs AS
(
  gid integer,
  the_geom geometry,
  source integer,
  target integer
);

CREATE OR REPLACE FUNCTION _jmsnetworkcalc(double precision, double precision, double precision)
  RETURNS SETOF networkcalcrs AS
$BODY$
  DECLARE
    clickLon ALIAS FOR $1;
    clickLat ALIAS FOR $2;
    distance ALIAS FOR $3;

    sourceID integer;
    returnNetwork networkCalcRS;
  BEGIN
    CREATE TEMP TABLE path
    (
      gid integer NOT NULL,
      the_geom geometry NOT NULL,
      vertex_id integer NOT NULL
    );

    CREATE TEMP TABLE network
    (
      gid integer NOT NULL,
      the_geom geometry NOT NULL,
      source integer NOT NULL, 
      target integer NOT NULL
    );

    SELECT INTO sourceID v.id
    FROM (SELECT st_transform(ST_SetSRID(ST_Point(clickLon, clickLat), 900913), 4269) As the_geom) As b, vertices_tmp as v
    ORDER BY ST_Distance(v.the_geom, b.the_geom)
    LIMIT 1;

    INSERT INTO path(gid, the_geom, vertex_id)
    SELECT rd.gid, rd.the_geom, dd.vertex_id
    FROM driving_distance('SELECT gid AS id, source, target, lengthfeet::double precision AS cost 
		           FROM cinciroad', sourceID, distance, false, false) dd, cinciroad rd
    WHERE rd.gid = dd.edge_id;
    
    INSERT INTO network(gid, the_geom, source, target)
    SELECT rd.gid, rd.the_geom, rd.source, rd.target
    FROM path firstPath
    CROSS JOIN path secondPath
    INNER JOIN cinciroad rd
    ON firstPath.vertex_id = rd.source
    AND secondPath.vertex_id = rd.target;

    FOR returnNetwork IN SELECT * FROM network LOOP
        RETURN NEXT returnNetwork;
    END LOOP;
  END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION _jmsnetworkcalc(double precision, double precision, double precision) OWNER TO postgres;

SELECT gid, the_geom, source, target FROM _jmsnetworkcalc(X, Y, distance);