pgr_analyzegraph considering schemas also modified pgr_iscolumnintable pgr_iscolumnindexed

modified_pgr_analyzegraph

/*
Modification of 
   pgr_iscolumnintable(tab text, col text)
   pgr_iscolumnindexed(tab text, col text)
   pgr_analyzegraph(edge_tab text, geom_col text, tol double precision)
that handles schemas
*/   

CREATE OR REPLACE FUNCTION pgr_isColumnInTableV2(tab text, col text)
/*
This is a modification of pgr_iscolumnintable(tab text, col text)

Considers that the input table is in a scheme
Examples:
    pgr_iscolumnintableV2('s09.streets','source')    -> schema='s09'     
    pgr_iscolumnintableV2('s10.streets','source')    -> schema='s10' 
    pgr_iscolumnintableV2('streets','source')        -> schema='public'  <-- because public is the current schema
*/
  RETURNS boolean AS
$BODY$
DECLARE
    cname text;
    tname text;
    sname text;
    i integer;
BEGIN

    execute 'select strpos('||quote_literal(tab)||','||quote_literal('.')||')' into i;
    if (i!=0) then 
	execute 'select substr('||quote_literal(tab)||',1,strpos('||quote_literal(tab)||','||quote_literal('.')||')-1)' into sname;
	execute 'select substr('||quote_literal(tab)||',strpos('||quote_literal(tab)||','||quote_literal('.')||')+1),length('||quote_literal(tab)||')' into tname;
    else 
        execute 'select current_schema' into sname;
        tname =tab;
    end if;    
	
    
    
    SELECT column_name INTO cname
        FROM information_schema.columns 
        WHERE table_name=tname and table_schema=sname and column_name=col;

    IF FOUND THEN
        RETURN true;
    ELSE
        RETURN false;
    END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE STRICT;


CREATE OR REPLACE FUNCTION public.pgr_iscolumnindexedV2(tab text, col text)
/*
This is a modification of pgr_iscolumnindexed(tab text, col text)

Considers that the input table is in a scheme
Examples:
    pgr_iscolumnindexedV2('s09.streets','source')    -> schema='s09'     
    pgr_iscolumnindexedV2('s10.streets','source')    -> schema='s10' 
    pgr_iscolumnindexedV2('streets','source')        -> schema='public'
*/
  RETURNS boolean AS
$BODY$
DECLARE
    rec record;
    sname text;
    tname text;
    i integer;
BEGIN

    execute 'select strpos('||quote_literal(tab)||','||quote_literal('.')||')' into i;
    if (i!=0) then 
	execute 'select substr('||quote_literal(tab)||',1,strpos('||quote_literal(tab)||','||quote_literal('.')||')-1)' into sname;
	execute 'select substr('||quote_literal(tab)||',strpos('||quote_literal(tab)||','||quote_literal('.')||')+1),length('||quote_literal(tab)||')' into tname;
    else 
        execute 'select current_schema' into sname;
        tname =tab;
    end if;   

    IF NOT pgr_isColumnInTableV2(tab, col) THEN
        RETURN false;
    END IF;

    SELECT a.index_name, 
           b.attname,
           b.attnum,
           a.indisunique,
           a.indisprimary
      INTO rec
      FROM ( SELECT a.indrelid,
                    a.indisunique,
                    a.indisprimary, 
                    c.relname index_name, 
                    unnest(a.indkey) index_num 
               FROM pg_index a,
                    pg_class b, 
                    pg_class c,
                    pg_namespace d  
              WHERE b.relname=tname
                AND b.relnamespace=d.oid
                AND d.nspname=sname 
                AND b.oid=a.indrelid 
                AND a.indexrelid=c.oid 
           ) a, 
           pg_attribute b 
     WHERE a.indrelid = b.attrelid 
       AND a.index_num = b.attnum 
       AND b.attname = col
  ORDER BY a.index_name, 
           a.index_num;

    IF FOUND THEN
        RETURN true;
    ELSE
        RETURN false;
    END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE STRICT;


CREATE OR REPLACE FUNCTION public.pgr_analyzegraphV2(edge_tab text, geom_col text, tol double precision)
/*
This is a modification of pgr_analyzegraph(edge_tab text, geom_col text, tol double precision)

makes more checks:
   checks table edge_tab exists in the schema 
   checks source and target columns exist in edge_tab
   checks that source and target are completely populated i.e. do not have NULL values
   checks table edge_tabVertices exist in the appropiate schema
       if not, it creates it and populates it
   checks 'cnt','chk' columns exist in  edge_tabVertices 
       if not, it creates them
   checks if 'id' column of edge_tabVertices is indexed 
       if not, it creates the index
   checks if 'source','target',geom_col columns of edge_tab are indexed 
       if not, it creates their index     
   populates cnt in edge_tabVertices  <--- changed the way it was processed, because on large tables took to long.
					   For sure I am wrong doing this, but it gave me the same result as the original.
   populates chk                      <--- added a notice for big tables, because it takes time
           (edge_tab text, geom_col text, tol double precision)
*/

  RETURNS character varying AS
$BODY$
DECLARE
    points record;
    seg record;
    ecnt integer;
    verticesTable text;
    schemas text;
    tableName text;
    flag boolean;
    query text;
    i integer;
    tot integer;
BEGIN

    execute 'select strpos('||quote_literal(edge_tab)||','||quote_literal('.')||')' into i;
    if (i!=0) then 
	execute 'select substr('||quote_literal(edge_tab)||',1,strpos('||quote_literal(edge_tab)||','||quote_literal('.')||')-1)' into schemas;
	execute 'select substr('||quote_literal(edge_tab)||',strpos('||quote_literal(edge_tab)||','||quote_literal('.')||')+1),length('||quote_literal(edge_tab)||')' into tableName;
    else 
        execute 'select current_schema' into schemas;
        tableName =edge_tab;
    end if;   

    BEGIN 
       raise notice 'checking table % exists in schema % ',tableName ,schemas;
       EXECUTE 'select count(*) from information_schema.tables where 
		table_type='||quote_literal('BASE TABLE')||' and 
		table_schema='||quote_literal(schemas)||' and 
		table_name='||quote_literal(tableName) INTO ecnt;
       IF ecnt=1 then
           raise notice  '  ------>OK'; 
       ELSE
           raise exception  '  ------>Table % DOES NOT exists in schema % ',tableName ,schemas;  
       END IF;
    END;       

    BEGIN 
       raise notice 'checking source and target columns in  %.% ',schemas,tableName;
       query='select pgr_isColumnInTableV2('||quote_literal(edge_tab)||','||quote_literal('source')||') 
                  and  pgr_isColumnInTableV2('||quote_literal(edge_tab)||','||quote_literal('target')||')';
 
       execute query  into flag;           
       IF flag then
          BEGIN 
		
                raise notice  '  ------>OK'; 
		raise notice 'checking that source and target are completely populated i.e. do not have NULL values';
		query= 'select count(*) from '||edge_tab||' where source is NULL or target is NULL' ;
		execute query  into ecnt;
		IF ecnt=0 then
			raise notice  '  ------>OK'; 
		ELSE
			raise exception  'source or target are not completely populated createTopology is needed prior calling this function';
		END IF;
         END;    
       ELSE 
          raise exception  '  ------> source and target do not exist in %',edge_tab;
       END IF;
     END;

  

    
     verticesTable = tableName||'vertices';
     BEGIN 
       raise notice 'checking table % exists in schema % ',verticesTable ,schemas;
       EXECUTE 'select count(*) from information_schema.tables where 
		table_type='||quote_literal('BASE TABLE')||' and 
		table_schema='||quote_literal(schemas)||' and 
		table_name='||quote_literal(verticesTable) INTO ecnt;
       IF ecnt=1 then
	   raise notice  '  ------>OK';
       ELSE
           raise notice '   --->table % DOES NOT exists in schema % ',verticesTable ,schemas; 
           raise notice '   --->creating table % in schema % ',verticesTable ,schemas;
	   execute 'with
		lines as ((select distinct source as id, st_startpoint(st_linemerge('||geom_col||')) as the_geom from '||edge_tab||') 
			union (select distinct target as id,st_endpoint(st_linemerge('||geom_col||')) as the_geom from '||edge_tab||') )
		,numberedLines as (select row_number() OVER (ORDER BY id) AS i,* from lines )
		,maxid as (select id,max(i) as maxi from numberedLines group by id)
		select id,the_geom into '||schemas||'.'||verticesTable||' from numberedLines join maxid using(id) where i=maxi order by id';
        END IF;
    END;               

    verticesTable=schemas||'.'||verticesTable;

    BEGIN
        RAISE NOTICE 'Cheking for "cnt" column in %',verticesTable;
        if (pgr_iscolumnintableV2(verticesTable,'cnt')) then
		RAISE NOTICE '  ------>OK';
		execute 'UPDATE '||verticesTable||' SET cnt=NULL';
	else 	
		RAISE NOTICE '  ------>Adding "cnt" column in %',verticesTable;
		execute 'ALTER TABLE '||verticesTable||' ADD COLUMN cnt integer';
	END IF;
    END;


    BEGIN
        RAISE NOTICE 'Cheking for "chk" column in %',verticesTable;
        if (pgr_iscolumnintableV2(verticesTable,'chk')) then
		RAISE NOTICE '  ------>OK';
		execute 'UPDATE '||verticesTable||' SET chk=NULL';
	else 	
		RAISE NOTICE '  ------>Adding "chk" column in %',verticesTable;
		execute 'ALTER TABLE '||verticesTable||' ADD COLUMN chk integer';
	END IF;
    END;

    BEGIN
      RAISE NOTICE 'Cheking "id" column in % is indexed',verticesTable;
      if (pgr_iscolumnindexedV2(verticesTable,'id')) then 
	RAISE NOTICE '  ------>OK';
      else 
        RAISE NOTICE ' ------> Adding unique index "%vertices_id_idx".',tableName;
        execute 'create unique index '||tableName||'vertices_id_idx on '||verticesTable||' using btree(id)';
      END IF;
    END;


    BEGIN
      RAISE NOTICE 'Cheking "source" column in % is indexed',edge_tab;
      if (pgr_iscolumnindexedV2(edge_tab,'source')) then 
	RAISE NOTICE '  ------>OK';
      else 
        RAISE NOTICE ' ------> Adding unique index "%_source_idx".',edge_tab;
        execute 'create  index '||tableName||'_source_idx on '||edge_tab||' using btree(source)';
      END IF;
    END;


    BEGIN
      RAISE NOTICE 'Cheking "target" column in % is indexed',edge_tab;
      if (pgr_iscolumnindexedV2(edge_tab,'target')) then 
	RAISE NOTICE '  ------>OK';
      else 
        RAISE NOTICE ' ------> Adding unique index "%_target_idx".',edge_tab;
        execute 'create  index '||tableName||'_target_idx on '||edge_tab||' using btree(target)';
      END IF;
    END;


    BEGIN
      RAISE NOTICE 'Cheking "%" column in % is indexed',geom_col,edge_tab;
      if (pgr_iscolumnindexedV2(edge_tab,geom_col)) then 
	RAISE NOTICE '  ------>OK';
      else 
        RAISE NOTICE ' ------> Adding unique index "%_%_gidx".',edge_tab,geom_col;
        execute 'CREATE INDEX '
            || quote_ident(edge_tab || '_' || geom_col || '_gidx' )
            || ' ON ' || pgr_quote_ident(edge_tab)
            || ' USING gist (' || quote_ident(geom_col) || ')';
      END IF;
    END;


    RAISE NOTICE 'Populating %.cnt',verticesTable;
    execute 'with countingsource as (select a.source as id,count(*) as cnts from '||edge_tab||' a  group by a.source) 
     ,countingtarget as (select a.target as id,count(*) as cntt from '||edge_tab||' a  group by a.target) 
     ,totalcount as (select id,case when cnts is null and cntt is null then 0
		    when cnts is null then cntt 
                    when cntt is null then cnts
                    else cnts+cntt end as totcnt from ('||verticesTable||' as a left join countingsource as t using(id) ) left join countingtarget using(id))
     update '||verticesTable||' as a set cnt=totcnt from totalcount as b where a.id=b.id';
     

    RAISE NOTICE 'Analyzing graph for gaps and zlev errors.';
    i=0;
    execute 'SELECT count(*) FROM '||verticesTable||' WHERE cnt = 1' into tot;
    FOR points IN execute 'SELECT * FROM '||verticesTable||' WHERE cnt = 1 ORDER BY id ' LOOP
        i=i+1;
        if ((i % 1000)=0 or i=1) then raise notice '----->Analysis done to % out of %',i,tot; END IF;
        FOR seg IN EXECUTE 'SELECT * FROM ' || pgr_quote_ident(edge_tab) || ' a
                WHERE ST_DWithin(a.' || quote_ident(geom_col) || ', $1, $2)'
                USING points.the_geom, tol
            LOOP
                IF points.id NOT IN (seg.source::bigint, seg.target::bigint) THEN
                    execute 'UPDATE '||verticesTable||' SET chk=1 WHERE id='||points.id;
                END IF;
        END LOOP;
    END LOOP;

    query ='SELECT count(*)  FROM '||verticesTable||' WHERE chk=1';
    execute query  INTO ecnt;

    RAISE NOTICE 'Found % potential problems at pgr_iscolumnintableV2 ''%''', ecnt,query;

    RETURN 'OK';
END;
$BODY$
  LANGUAGE plpgsql VOLATILE STRICT;