-- Carthorse Production Functions Export
-- Generated on: 2025-08-02T03:55:23.088Z
-- Database: trail_master_db
-- 
-- This file contains all functions from the production PostGIS database.
-- Use this file for backup and version control purposes.
--

CREATE OR REPLACE FUNCTION public.__st_countagg_transfn(agg agg_count, rast raster, nband integer DEFAULT 1, exclude_nodata_value boolean DEFAULT true, sample_percent double precision DEFAULT 1)
 RETURNS agg_count
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE
AS $function$
	DECLARE
		_count bigint;
		rtn_agg agg_count;
	BEGIN
		IF agg IS NULL THEN
			rtn_agg.count := 0;
			IF nband < 1 THEN
				RAISE EXCEPTION 'Band index must be greater than zero (1-based)';
			ELSE
				rtn_agg.nband := nband;
			END IF;
			IF exclude_nodata_value IS FALSE THEN
				rtn_agg.exclude_nodata_value := FALSE;
			ELSE
				rtn_agg.exclude_nodata_value := TRUE;
			END IF;
			IF sample_percent < 0. OR sample_percent > 1. THEN
				RAISE EXCEPTION 'Sample percent must be between zero and one';
			ELSE
				rtn_agg.sample_percent := sample_percent;
			END IF;
		ELSE
			rtn_agg := agg;
		END IF;
		IF rast IS NOT NULL THEN
			IF rtn_agg.exclude_nodata_value IS FALSE THEN
				SELECT width * height INTO _count FROM public.ST_Metadata(rast);
			ELSE
				SELECT count INTO _count FROM public._ST_summarystats(
					rast,
				 	rtn_agg.nband, rtn_agg.exclude_nodata_value,
					rtn_agg.sample_percent
				);
			END IF;
		END IF;
		rtn_agg.count := rtn_agg.count + _count;
		RETURN rtn_agg;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._add_overview_constraint(ovschema name, ovtable name, ovcolumn name, refschema name, reftable name, refcolumn name, factor integer)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	DECLARE
		fqtn text;
		cn name;
		sql text;
	BEGIN
		fqtn := '';
		IF length($1) > 0 THEN
			fqtn := quote_ident($1) || '.';
		END IF;
		fqtn := fqtn || quote_ident($2);
		cn := 'enforce_overview_' || $3;
		sql := 'ALTER TABLE ' || fqtn
			|| ' ADD CONSTRAINT ' || quote_ident(cn)
			|| ' CHECK ( public._overview_constraint(' || quote_ident($3)
			|| ',' || $7
			|| ',' || quote_literal($4)
			|| ',' || quote_literal($5)
			|| ',' || quote_literal($6)
			|| '))';
		RETURN  public._add_raster_constraint(cn, sql);
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._add_raster_constraint(cn name, sql text)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	BEGIN
		BEGIN
			EXECUTE sql;
		EXCEPTION
			WHEN duplicate_object THEN
				RAISE NOTICE 'The constraint "%" already exists.  To replace the existing constraint, delete the constraint and call ApplyRasterConstraints again', cn;
			WHEN OTHERS THEN
				RAISE NOTICE 'Unable to add constraint: %', cn;
				RAISE NOTICE 'SQL used for failed constraint: %', sql;
				RAISE NOTICE 'Returned error message: % (%)', SQLERRM, SQLSTATE;
				RETURN FALSE;
		END;
		RETURN TRUE;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._add_raster_constraint_alignment(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	DECLARE
		fqtn text;
		cn name;
		sql text;
		attr text;
	BEGIN
		fqtn := '';
		IF length($1) > 0 THEN
			fqtn := quote_ident($1) || '.';
		END IF;
		fqtn := fqtn || quote_ident($2);
		cn := 'enforce_same_alignment_' || $3;
		sql := 'SELECT public.st_makeemptyraster(1, 1, upperleftx, upperlefty, scalex, scaley, skewx, skewy, srid) FROM public.st_metadata((SELECT '
			|| quote_ident($3)
			|| ' FROM '
			|| fqtn
			|| ' WHERE '
			|| quote_ident($3)
			|| ' IS NOT NULL LIMIT 1))';
		BEGIN
			EXECUTE sql INTO attr;
		EXCEPTION WHEN OTHERS THEN
			RAISE NOTICE 'Unable to get the alignment of a sample raster: % (%)',
        SQLERRM, SQLSTATE;
			RETURN FALSE;
		END;
		sql := 'ALTER TABLE ' || fqtn ||
			' ADD CONSTRAINT ' || quote_ident(cn) ||
			' CHECK (public.st_samealignment(' || quote_ident($3) || ', ''' || attr || '''::raster))';
		RETURN  public._add_raster_constraint(cn, sql);
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._add_raster_constraint_blocksize(rastschema name, rasttable name, rastcolumn name, axis text)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	DECLARE
		fqtn text;
		cn name;
		sql text;
		attrset integer[];
		attr integer;
	BEGIN
		IF lower($4) != 'width' AND lower($4) != 'height' THEN
			RAISE EXCEPTION 'axis must be either "width" or "height"';
			RETURN FALSE;
		END IF;
		fqtn := '';
		IF length($1) > 0 THEN
			fqtn := quote_ident($1) || '.';
		END IF;
		fqtn := fqtn || quote_ident($2);
		cn := 'enforce_' || $4 || '_' || $3;
		sql := 'SELECT public.st_' || $4 || '('
			|| quote_ident($3)
			|| ') FROM ' || fqtn
			|| ' GROUP BY 1 ORDER BY count(*) DESC';
		BEGIN
			attrset := ARRAY[]::integer[];
			FOR attr IN EXECUTE sql LOOP
				attrset := attrset || attr;
			END LOOP;
		EXCEPTION WHEN OTHERS THEN
			RAISE NOTICE 'Unable to get the % of a sample raster: % (%)',
        $4, SQLERRM, SQLSTATE;
			RETURN FALSE;
		END;
		sql := 'ALTER TABLE ' || fqtn
			|| ' ADD CONSTRAINT ' || quote_ident(cn)
			|| ' CHECK (st_' || $4 || '('
			|| quote_ident($3)
			|| ') IN (' || array_to_string(attrset, ',') || '))';
		RETURN  public._add_raster_constraint(cn, sql);
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._add_raster_constraint_coverage_tile(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	DECLARE
		fqtn text;
		cn name;
		sql text;
		_scalex double precision;
		_scaley double precision;
		_skewx double precision;
		_skewy double precision;
		_tilewidth integer;
		_tileheight integer;
		_alignment boolean;
		_covextent public.geometry;
		_covrast public.raster;
	BEGIN
		fqtn := '';
		IF length($1) > 0 THEN
			fqtn := quote_ident($1) || '.';
		END IF;
		fqtn := fqtn || quote_ident($2);
		cn := 'enforce_coverage_tile_' || $3;
		BEGIN
			sql := 'WITH foo AS (SELECT public.ST_Metadata(' || quote_ident($3) || ') AS meta, public.ST_ConvexHull(' || quote_ident($3) || ') AS hull FROM ' || fqtn || ') SELECT max((meta).scalex), max((meta).scaley), max((meta).skewx), max((meta).skewy), max((meta).width), max((meta).height), public.ST_Union(hull) FROM foo';
			EXECUTE sql INTO _scalex, _scaley, _skewx, _skewy, _tilewidth, _tileheight, _covextent;
		EXCEPTION WHEN OTHERS THEN
			RAISE DEBUG 'Unable to get coverage metadata for %.%: % (%)',
        fqtn, quote_ident($3), SQLERRM, SQLSTATE;
		END;
		BEGIN
			_covrast := public.ST_AsRaster(_covextent, _scalex, _scaley, '8BUI', 1, 0, NULL, NULL, _skewx, _skewy);
			IF _covrast IS NULL THEN
				RAISE NOTICE 'Unable to create coverage raster: ST_AsRaster returned NULL.';
				RETURN FALSE;
			END IF;
			_covrast := ST_MakeEmptyRaster(_covrast);
		EXCEPTION WHEN OTHERS THEN
			RAISE NOTICE 'Unable to create coverage raster. Cannot add coverage tile constraint: % (%)',
        SQLERRM, SQLSTATE;
			RETURN FALSE;
		END;
		sql := 'ALTER TABLE ' || fqtn ||
			' ADD CONSTRAINT ' || quote_ident(cn) ||
			' CHECK (public.st_iscoveragetile(' || quote_ident($3) || ', ''' || _covrast || '''::raster, ' || _tilewidth || ', ' || _tileheight || '))';
		RETURN  public._add_raster_constraint(cn, sql);
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._add_raster_constraint_extent(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT COST 9000
AS $function$
	DECLARE
		fqtn text;
		cn name;
		sql text;
		attr text; srid integer;
	BEGIN
		fqtn := '';
		IF length(rastschema) > 0 THEN
			fqtn := quote_ident(rastschema) || '.';
		END IF;
		fqtn := fqtn || quote_ident(rasttable);
		sql := 'SELECT public.ST_SRID('
			|| quote_ident(rastcolumn)
			|| ') FROM '
			|| fqtn
			|| ' WHERE '
			|| quote_ident(rastcolumn)
			|| ' IS NOT NULL LIMIT 1;';
                EXECUTE sql INTO srid;
    IF srid IS NULL THEN
      RETURN false;
    END IF;
		cn := 'enforce_max_extent_' || rastcolumn;
		sql := 'SELECT public.st_ashexewkb( public.st_setsrid( public.st_extent( public.st_envelope('
			|| quote_ident(rastcolumn)
			|| ')), ' || srid || ')) FROM '
			|| fqtn;
		EXECUTE sql INTO attr;
		sql := 'ALTER TABLE ' || fqtn
			|| ' ADD CONSTRAINT ' || quote_ident(cn)
			|| ' CHECK ( public.st_envelope('
			|| quote_ident(rastcolumn)
			|| ') @ ''' || attr || '''::geometry) NOT VALID';
		RETURN  public._add_raster_constraint(cn, sql);
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._add_raster_constraint_nodata_values(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	DECLARE
		fqtn text;
		cn name;
		sql text;
		attr numeric[];
		max int;
	BEGIN
		fqtn := '';
		IF length($1) > 0 THEN
			fqtn := quote_ident($1) || '.';
		END IF;
		fqtn := fqtn || quote_ident($2);
		cn := 'enforce_nodata_values_' || $3;
		sql := 'SELECT public._raster_constraint_nodata_values(' || quote_ident($3)
			|| ') FROM ' || fqtn
			|| ' WHERE '
			|| quote_ident($3)
			|| ' IS NOT NULL LIMIT 1;';
		BEGIN
			EXECUTE sql INTO attr;
		EXCEPTION WHEN OTHERS THEN
			RAISE NOTICE 'Unable to get the nodata values of a sample raster: % (%)',
        SQLERRM, SQLSTATE;
			RETURN FALSE;
		END;
		max := array_length(attr, 1);
		IF max < 1 OR max IS NULL THEN
			RAISE NOTICE 'Unable to get the nodata values of a sample raster (max < 1 or null)';
			RETURN FALSE;
		END IF;
		sql := 'ALTER TABLE ' || fqtn
			|| ' ADD CONSTRAINT ' || quote_ident(cn)
			|| ' CHECK (public._raster_constraint_nodata_values(' || quote_ident($3)
			|| ')::numeric[] = ''{';
		FOR x in 1..max LOOP
			IF attr[x] IS NULL THEN
				sql := sql || 'NULL';
			ELSE
				sql := sql || attr[x];
			END IF;
			IF x < max THEN
				sql := sql || ',';
			END IF;
		END LOOP;
		sql := sql || '}''::numeric[])';
		RETURN  public._add_raster_constraint(cn, sql);
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._add_raster_constraint_num_bands(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	DECLARE
		fqtn text;
		cn name;
		sql text;
		attr int;
	BEGIN
		fqtn := '';
		IF length($1) > 0 THEN
			fqtn := quote_ident($1) || '.';
		END IF;
		fqtn := fqtn || quote_ident($2);
		cn := 'enforce_num_bands_' || $3;
		sql := 'SELECT public.st_numbands(' || quote_ident($3)
			|| ') FROM '
			|| fqtn
			|| ' WHERE '
			|| quote_ident($3)
			|| ' IS NOT NULL LIMIT 1;';
		BEGIN
			EXECUTE sql INTO attr;
		EXCEPTION WHEN OTHERS THEN
			RAISE NOTICE 'Unable to get the number of bands of a sample raster: % (%)',
        SQLERRM, SQLSTATE;
			RETURN FALSE;
		END;
		sql := 'ALTER TABLE ' || fqtn
			|| ' ADD CONSTRAINT ' || quote_ident(cn)
			|| ' CHECK (public.st_numbands(' || quote_ident($3)
			|| ') = ' || attr
			|| ')';
		RETURN  public._add_raster_constraint(cn, sql);
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._add_raster_constraint_out_db(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	DECLARE
		fqtn text;
		cn name;
		sql text;
		attr boolean[];
		max int;
	BEGIN
		fqtn := '';
		IF length($1) > 0 THEN
			fqtn := quote_ident($1) || '.';
		END IF;
		fqtn := fqtn || quote_ident($2);
		cn := 'enforce_out_db_' || $3;
		sql := 'SELECT public._raster_constraint_out_db(' || quote_ident($3)
			|| ') FROM ' || fqtn
			|| ' WHERE '
			|| quote_ident($3)
			|| ' IS NOT NULL LIMIT 1;';
		BEGIN
			EXECUTE sql INTO attr;
		EXCEPTION WHEN OTHERS THEN
			RAISE NOTICE 'Unable to get the out-of-database bands of a sample raster: % (%)',
        SQLERRM, SQLSTATE;
			RETURN FALSE;
		END;
		max := array_length(attr, 1);
		IF max < 1 OR max IS NULL THEN
			RAISE NOTICE 'Unable to get the out-of-database bands of a sample raster (max < 1 or null)';
			RETURN FALSE;
		END IF;
		sql := 'ALTER TABLE ' || fqtn
			|| ' ADD CONSTRAINT ' || quote_ident(cn)
			|| ' CHECK ( public._raster_constraint_out_db(' || quote_ident($3)
			|| ') = ''{';
		FOR x in 1..max LOOP
			IF attr[x] IS FALSE THEN
				sql := sql || 'FALSE';
			ELSE
				sql := sql || 'TRUE';
			END IF;
			IF x < max THEN
				sql := sql || ',';
			END IF;
		END LOOP;
		sql := sql || '}''::boolean[])';
		RETURN  public._add_raster_constraint(cn, sql);
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._add_raster_constraint_pixel_types(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	DECLARE
		fqtn text;
		cn name;
		sql text;
		attr text[];
		max int;
	BEGIN
		fqtn := '';
		IF length($1) > 0 THEN
			fqtn := quote_ident($1) || '.';
		END IF;
		fqtn := fqtn || quote_ident($2);
		cn := 'enforce_pixel_types_' || $3;
		sql := 'SELECT public._raster_constraint_pixel_types(' || quote_ident($3)
			|| ') FROM ' || fqtn
			|| ' WHERE '
			|| quote_ident($3)
			|| ' IS NOT NULL LIMIT 1;';
		BEGIN
			EXECUTE sql INTO attr;
		EXCEPTION WHEN OTHERS THEN
			RAISE NOTICE 'Unable to get the pixel types of a sample raster: % (%)',
        SQLERRM, SQLSTATE;
			RETURN FALSE;
		END;
		max := array_length(attr, 1);
		IF max < 1 OR max IS NULL THEN
			RAISE NOTICE 'Unable to get the pixel types of a sample raster (max < 1 or null)';
			RETURN FALSE;
		END IF;
		sql := 'ALTER TABLE ' || fqtn
			|| ' ADD CONSTRAINT ' || quote_ident(cn)
			|| ' CHECK (public._raster_constraint_pixel_types(' || quote_ident($3)
			|| ') = ''{';
		FOR x in 1..max LOOP
			sql := sql || '"' || attr[x] || '"';
			IF x < max THEN
				sql := sql || ',';
			END IF;
		END LOOP;
		sql := sql || '}''::text[])';
		RETURN  public._add_raster_constraint(cn, sql);
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._add_raster_constraint_scale(rastschema name, rasttable name, rastcolumn name, axis character)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	DECLARE
		fqtn text;
		cn name;
		sql text;
		attr double precision;
	BEGIN
		IF lower($4) != 'x' AND lower($4) != 'y' THEN
			RAISE EXCEPTION 'axis must be either "x" or "y"';
			RETURN FALSE;
		END IF;
		fqtn := '';
		IF length($1) > 0 THEN
			fqtn := quote_ident($1) || '.';
		END IF;
		fqtn := fqtn || quote_ident($2);
		cn := 'enforce_scale' || $4 || '_' || $3;
		sql := 'SELECT st_scale' || $4 || '('
			|| quote_ident($3)
			|| ') FROM '
			|| fqtn
			|| ' WHERE '
			|| quote_ident($3)
			|| ' IS NOT NULL LIMIT 1;';
		BEGIN
			EXECUTE sql INTO attr;
		EXCEPTION WHEN OTHERS THEN
			RAISE NOTICE 'Unable to get the %-scale of a sample raster: % (%)',
        upper($4), SQLERRM, SQLSTATE;
			RETURN FALSE;
		END;
		sql := 'ALTER TABLE ' || fqtn
			|| ' ADD CONSTRAINT ' || quote_ident(cn)
			|| ' CHECK (round(public.st_scale' || $4 || '('
			|| quote_ident($3)
			|| ')::numeric, 10) = round(' || text(attr) || '::numeric, 10))';
		RETURN  public._add_raster_constraint(cn, sql);
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._add_raster_constraint_spatially_unique(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	DECLARE
		fqtn text;
		cn name;
		sql text;
		attr text;
		meta record;
	BEGIN
		fqtn := '';
		IF length($1) > 0 THEN
			fqtn := quote_ident($1) || '.';
		END IF;
		fqtn := fqtn || quote_ident($2);
		cn := 'enforce_spatially_unique_' || quote_ident($2) || '_'|| $3;
		sql := 'ALTER TABLE ' || fqtn ||
			' ADD CONSTRAINT ' || quote_ident(cn) ||
			' EXCLUDE ((' || quote_ident($3) || '::geometry) WITH =)';
		RETURN  public._add_raster_constraint(cn, sql);
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._add_raster_constraint_srid(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	DECLARE
		fqtn text;
		cn name;
		sql text;
		attr int;
	BEGIN
		fqtn := '';
		IF length($1) > 0 THEN
			fqtn := quote_ident($1) || '.';
		END IF;
		fqtn := fqtn || quote_ident($2);
		cn := 'enforce_srid_' || $3;
		sql := 'SELECT public.st_srid('
			|| quote_ident($3)
			|| ') FROM ' || fqtn
			|| ' WHERE '
			|| quote_ident($3)
			|| ' IS NOT NULL LIMIT 1;';
		BEGIN
			EXECUTE sql INTO attr;
		EXCEPTION WHEN OTHERS THEN
			RAISE NOTICE 'Unable to get the SRID of a sample raster: % (%)',
        SQLERRM, SQLSTATE;
			RETURN FALSE;
		END;
		sql := 'ALTER TABLE ' || fqtn
			|| ' ADD CONSTRAINT ' || quote_ident(cn)
			|| ' CHECK (public.st_srid('
			|| quote_ident($3)
			|| ') = ' || attr || ')';
		RETURN  public._add_raster_constraint(cn, sql);
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._drop_overview_constraint(ovschema name, ovtable name, ovcolumn name)
 RETURNS boolean
 LANGUAGE sql
 STRICT
AS $function$ SELECT  public._drop_raster_constraint($1, $2, 'enforce_overview_' || $3) $function$
;

CREATE OR REPLACE FUNCTION public._drop_raster_constraint(rastschema name, rasttable name, cn name)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	DECLARE
		fqtn text;
	BEGIN
		fqtn := '';
		IF length($1) > 0 THEN
			fqtn := quote_ident($1) || '.';
		END IF;
		fqtn := fqtn || quote_ident($2);
		BEGIN
			EXECUTE 'ALTER TABLE '
				|| fqtn
				|| ' DROP CONSTRAINT '
				|| quote_ident(cn);
			RETURN TRUE;
		EXCEPTION
			WHEN undefined_object THEN
				RAISE NOTICE 'The constraint "%" does not exist.  Skipping', cn;
			WHEN OTHERS THEN
				RAISE NOTICE 'Unable to drop constraint "%": % (%)',
          cn, SQLERRM, SQLSTATE;
				RETURN FALSE;
		END;
		RETURN TRUE;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._drop_raster_constraint_alignment(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE sql
 STRICT
AS $function$ SELECT  public._drop_raster_constraint($1, $2, 'enforce_same_alignment_' || $3) $function$
;

CREATE OR REPLACE FUNCTION public._drop_raster_constraint_blocksize(rastschema name, rasttable name, rastcolumn name, axis text)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	BEGIN
		IF lower($4) != 'width' AND lower($4) != 'height' THEN
			RAISE EXCEPTION 'axis must be either "width" or "height"';
			RETURN FALSE;
		END IF;
		RETURN  public._drop_raster_constraint($1, $2, 'enforce_' || $4 || '_' || $3);
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._drop_raster_constraint_coverage_tile(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE sql
 STRICT
AS $function$ SELECT  public._drop_raster_constraint($1, $2, 'enforce_coverage_tile_' || $3) $function$
;

CREATE OR REPLACE FUNCTION public._drop_raster_constraint_extent(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE sql
 STRICT
AS $function$ SELECT  public._drop_raster_constraint($1, $2, 'enforce_max_extent_' || $3) $function$
;

CREATE OR REPLACE FUNCTION public._drop_raster_constraint_nodata_values(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE sql
 STRICT
AS $function$ SELECT  public._drop_raster_constraint($1, $2, 'enforce_nodata_values_' || $3) $function$
;

CREATE OR REPLACE FUNCTION public._drop_raster_constraint_num_bands(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE sql
 STRICT
AS $function$ SELECT  public._drop_raster_constraint($1, $2, 'enforce_num_bands_' || $3) $function$
;

CREATE OR REPLACE FUNCTION public._drop_raster_constraint_out_db(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE sql
 STRICT
AS $function$ SELECT  public._drop_raster_constraint($1, $2, 'enforce_out_db_' || $3) $function$
;

CREATE OR REPLACE FUNCTION public._drop_raster_constraint_pixel_types(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE sql
 STRICT
AS $function$ SELECT  public._drop_raster_constraint($1, $2, 'enforce_pixel_types_' || $3) $function$
;

CREATE OR REPLACE FUNCTION public._drop_raster_constraint_regular_blocking(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE sql
 STRICT
AS $function$ SELECT public._drop_raster_constraint($1, $2, 'enforce_regular_blocking_' || $3) $function$
;

CREATE OR REPLACE FUNCTION public._drop_raster_constraint_scale(rastschema name, rasttable name, rastcolumn name, axis character)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	BEGIN
		IF lower($4) != 'x' AND lower($4) != 'y' THEN
			RAISE EXCEPTION 'axis must be either "x" or "y"';
			RETURN FALSE;
		END IF;
		RETURN  public._drop_raster_constraint($1, $2, 'enforce_scale' || $4 || '_' || $3);
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._drop_raster_constraint_spatially_unique(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	DECLARE
		cn text;
	BEGIN
		SELECT
			s.conname INTO cn
		FROM pg_class c, pg_namespace n, pg_attribute a
		, (SELECT connamespace, conname, conrelid, conkey, conindid, contype, conexclop, pg_get_constraintdef(oid) As consrc
			FROM pg_constraint) AS s
		, pg_index idx, pg_operator op
		WHERE n.nspname = $1
			AND c.relname = $2
			AND a.attname = $3
			AND a.attrelid = c.oid
			AND s.connamespace = n.oid
			AND s.conrelid = c.oid
			AND s.contype = 'x'
			AND 0::smallint = ANY (s.conkey)
			AND idx.indexrelid = s.conindid
			AND pg_get_indexdef(idx.indexrelid, 1, true) LIKE '(' || quote_ident($3) || '::geometry)'
			AND s.conexclop[1] = op.oid
			AND op.oprname = '=';
		RETURN  public._drop_raster_constraint($1, $2, cn);
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._drop_raster_constraint_srid(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE sql
 STRICT
AS $function$ SELECT  public._drop_raster_constraint($1, $2, 'enforce_srid_' || $3) $function$
;

CREATE OR REPLACE FUNCTION public._overview_constraint(ov raster, factor integer, refschema name, reftable name, refcolumn name)
 RETURNS boolean
 LANGUAGE sql
 STABLE
AS $function$ SELECT COALESCE((SELECT TRUE FROM public.raster_columns WHERE r_table_catalog = current_database() AND r_table_schema = $3 AND r_table_name = $4 AND r_raster_column = $5), FALSE) $function$
;

CREATE OR REPLACE FUNCTION public._overview_constraint_info(ovschema name, ovtable name, ovcolumn name, OUT refschema name, OUT reftable name, OUT refcolumn name, OUT factor integer)
 RETURNS record
 LANGUAGE sql
 STABLE STRICT
AS $function$
	SELECT
		split_part(split_part(s.consrc, '''::name', 1), '''', 2)::name,
		split_part(split_part(s.consrc, '''::name', 2), '''', 2)::name,
		split_part(split_part(s.consrc, '''::name', 3), '''', 2)::name,
		trim(both from split_part(s.consrc, ',', 2))::integer
	FROM pg_class c, pg_namespace n, pg_attribute a
		, (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
		    FROM pg_constraint) AS s
	WHERE n.nspname = $1
		AND c.relname = $2
		AND a.attname = $3
		AND a.attrelid = c.oid
		AND s.connamespace = n.oid
		AND s.conrelid = c.oid
		AND a.attnum = ANY (s.conkey)
		AND s.consrc LIKE '%_overview_constraint(%' LIMIT 1
	$function$
;

CREATE OR REPLACE FUNCTION public._pgr_alphashape(text, alpha double precision DEFAULT 0, OUT seq1 bigint, OUT textgeom text)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_alphashape$function$
;

-- Function: _pgr_alphashape
-- Comment: pgrouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_array_reverse(anyarray)
 RETURNS anyarray
 LANGUAGE sql
 IMMUTABLE STRICT
AS $function$
SELECT ARRAY(
    SELECT $1[i]
    FROM generate_subscripts($1,1) AS s(i)
    ORDER BY i DESC
);
$function$
;

-- Function: _pgr_array_reverse
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_articulationpoints(edges_sql text, OUT seq integer, OUT node bigint)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_articulationpoints$function$
;

-- Function: _pgr_articulationpoints
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_astar(edges_sql text, combinations_sql text, directed boolean DEFAULT true, heuristic integer DEFAULT 5, factor double precision DEFAULT 1.0, epsilon double precision DEFAULT 1.0, only_cost boolean DEFAULT false, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_astar$function$
;

-- Function: _pgr_astar
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_astar(edges_sql text, start_vids anyarray, end_vids anyarray, directed boolean DEFAULT true, heuristic integer DEFAULT 5, factor double precision DEFAULT 1.0, epsilon double precision DEFAULT 1.0, only_cost boolean DEFAULT false, normal boolean DEFAULT true, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_astar$function$
;

-- Function: _pgr_astar
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_bdastar(text, anyarray, anyarray, directed boolean DEFAULT true, heuristic integer DEFAULT 5, factor double precision DEFAULT 1.0, epsilon double precision DEFAULT 1.0, only_cost boolean DEFAULT false, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_bdastar$function$
;

-- Function: _pgr_bdastar
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_bdastar(text, text, directed boolean DEFAULT true, heuristic integer DEFAULT 5, factor double precision DEFAULT 1.0, epsilon double precision DEFAULT 1.0, only_cost boolean DEFAULT false, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_bdastar$function$
;

-- Function: _pgr_bdastar
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_bddijkstra(text, anyarray, anyarray, directed boolean, only_cost boolean DEFAULT false, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_bddijkstra$function$
;

-- Function: _pgr_bddijkstra
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_bddijkstra(text, text, directed boolean, only_cost boolean DEFAULT false, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_bddijkstra$function$
;

-- Function: _pgr_bddijkstra
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_bellmanford(edges_sql text, from_vids anyarray, to_vids anyarray, directed boolean, only_cost boolean, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_bellmanford$function$
;

-- Function: _pgr_bellmanford
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_bellmanford(edges_sql text, combinations_sql text, directed boolean, only_cost boolean, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_bellmanford$function$
;

-- Function: _pgr_bellmanford
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_betweennesscentrality(edges_sql text, directed boolean, OUT vid bigint, OUT centrality double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_betweennesscentrality$function$
;

-- Function: _pgr_betweennesscentrality
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_biconnectedcomponents(edges_sql text, OUT seq bigint, OUT component bigint, OUT edge bigint)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_biconnectedcomponents$function$
;

-- Function: _pgr_biconnectedcomponents
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_binarybreadthfirstsearch(edges_sql text, from_vids anyarray, to_vids anyarray, directed boolean DEFAULT true, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_binarybreadthfirstsearch$function$
;

-- Function: _pgr_binarybreadthfirstsearch
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_binarybreadthfirstsearch(edges_sql text, combinations_sql text, directed boolean DEFAULT true, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_binarybreadthfirstsearch$function$
;

-- Function: _pgr_binarybreadthfirstsearch
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_bipartite(edges_sql text, OUT node bigint, OUT color bigint)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_bipartite$function$
;

-- Function: _pgr_bipartite
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_boost_version()
 RETURNS text
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_boost_version$function$
;

-- Function: _pgr_boost_version
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_breadthfirstsearch(edges_sql text, from_vids anyarray, max_depth bigint, directed boolean, OUT seq bigint, OUT depth bigint, OUT start_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_breadthfirstsearch$function$
;

-- Function: _pgr_breadthfirstsearch
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_bridges(edges_sql text, OUT seq integer, OUT edge bigint)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_bridges$function$
;

-- Function: _pgr_bridges
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_build_type()
 RETURNS text
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_build_type$function$
;

-- Function: _pgr_build_type
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_checkcolumn(text, text, text, is_optional boolean DEFAULT false, dryrun boolean DEFAULT false)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
  has_column BOOLEAN := TRUE;
  rec RECORD;
  sqlhint TEXT;
BEGIN
  BEGIN
    EXECUTE format('SELECT %1$s FROM ( %2$s ) AS __a__ limit 1', $2, $1);
    EXCEPTION WHEN OTHERS THEN
      BEGIN
      IF NOT is_optional THEN
        RAISE EXCEPTION '%', SQLERRM USING HINT = $1, ERRCODE = SQLSTATE;
      ELSE
        has_column := FALSE;
      END IF;
      END;
  END;
  BEGIN
    EXECUTE format('SELECT pg_typeof(%1$s) FROM ( %2$s ) AS __a__ limit 1', $2, $1)
    INTO rec;
    EXCEPTION WHEN OTHERS THEN
      has_column := FALSE;
  END;
  IF dryrun THEN
    RETURN has_column;
  END IF;
  IF NOT is_optional AND NOT has_column THEN
    RAISE EXCEPTION 'Missing column'
    USING HINT = format('Column "%1$s" missing in: %2$s', $2, $1);
  END IF;
  IF has_column THEN
    CASE $3
    WHEN 'ANY-INTEGER' THEN
      IF  rec.pg_typeof NOT IN ('smallint','integer','bigint') THEN
        RAISE EXCEPTION 'Expected type of column "%" is ANY-INTEGER', $2
        USING HINT = 'Query: ' || $1;
      END IF;
    WHEN 'ANY-INTEGER[]' THEN
      IF  rec.pg_typeof NOT IN ('smallint[]','integer[]','bigint[]') THEN
        RAISE EXCEPTION 'Expected type of column "%" is ANY-INTEGER-ARRAY', $2
        USING HINT = 'Query: ' || $1;
      END IF;
    WHEN 'ANY-NUMERICAL' THEN
      IF  rec.pg_typeof NOT IN ('smallint','integer','bigint','real','float','numeric') THEN
        RAISE EXCEPTION 'Expected type of column "%s" is ANY-NUMERICAL', $2
        USING HINT = 'Query: ' || $1;
      END IF;
    ELSE
      IF rec.pg_typeof::TEXT != $3 THEN
        RAISE EXCEPTION 'Expected type of column "%" is %', $2, $3
        USING HINT = 'Query: ' || $1;
      END IF;
    END CASE;
  END IF;
  RETURN has_column;
END;
$function$
;

-- Function: _pgr_checkcolumn
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_checkquery(text)
 RETURNS text
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
  main_sql TEXT;
BEGIN
  IF $1 !~ '[[:space:]]' THEN
    EXECUTE format($$
      SELECT regexp_replace(regexp_replace(statement, %1$L,'','i'),';$','')
      FROM pg_prepared_statements WHERE name = %2$L$$,
      '.*' || $1 || '\s*as', $1)
    INTO main_sql;
    IF main_sql IS NULL THEN
      RAISE EXCEPTION 'prepared statement "%" does not exist', $1;
    END IF;
  ELSE
    main_sql := $1;
  END IF;
  BEGIN
    EXECUTE format('SELECT * FROM ( %1$s ) AS __a__ limit 1', main_sql);
    EXCEPTION WHEN OTHERS THEN
      RAISE EXCEPTION '%', SQLERRM USING HINT = $1, ERRCODE = SQLSTATE;
  END;
  RETURN main_sql;
END;
$function$
;

-- Function: _pgr_checkquery
-- Comment: pgrouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_checkverttab(vertname text, columnsarr text[], reporterrs integer DEFAULT 1, fnname text DEFAULT '_pgr_checkVertTab'::text, OUT sname text, OUT vname text)
 RETURNS record
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
    cname text;
    colname text;
    naming record;
    debuglevel text;
    err  boolean;
    msgKind int;
BEGIN
    msgKind = 0; -- debug_
    execute 'show client_min_messages' into debuglevel;
    perform _pgr_msg(msgKind, fnName, 'Checking table ' || vertname || ' exists');
       select * from _pgr_getTableName(vertname, 0, fnName) into naming;
       sname=naming.sname;
       vname=naming.tname;
       err = sname is NULL or vname is NULL;
    perform _pgr_onError( err, 2, fnName,
          'Vertex Table: ' || vertname || ' not found',
          'Please create ' || vertname || ' using  _pgr_createTopology()',
          'Vertex Table: ' || vertname || ' found');
    perform _pgr_msg(msgKind, fnName, 'Checking columns of ' || vertname);
      FOREACH cname IN ARRAY columnsArr
      loop
         select _pgr_getcolumnName(vertname, cname, 0, fnName) into colname;
         if colname is null then
           perform _pgr_msg(msgKind, fnName, 'Adding column ' || cname || ' in ' || vertname);
           set client_min_messages  to warning;
                execute 'ALTER TABLE '||_pgr_quote_ident(vertname)||' ADD COLUMN '||cname|| ' integer';
           execute 'set client_min_messages  to '|| debuglevel;
           perform _pgr_msg(msgKind, fnName);
         end if;
      end loop;
    perform _pgr_msg(msgKind, fnName, 'Finished checking columns of ' || vertname);
    perform _pgr_createIndex(vertname , 'id' , 'btree', reportErrs, fnName);
 END
$function$
;

-- Function: _pgr_checkverttab
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_chinesepostman(edges_sql text, only_cost boolean, OUT seq integer, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_chinesepostman$function$
;

-- Function: _pgr_chinesepostman
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_compilation_date()
 RETURNS text
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_compilation_date$function$
;

-- Function: _pgr_compilation_date
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_compiler_version()
 RETURNS text
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_compiler_version$function$
;

-- Function: _pgr_compiler_version
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_connectedcomponents(edges_sql text, OUT seq bigint, OUT component bigint, OUT node bigint)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_connectedcomponents$function$
;

-- Function: _pgr_connectedcomponents
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_contraction(edges_sql text, contraction_order bigint[], max_cycles integer DEFAULT 1, forbidden_vertices bigint[] DEFAULT ARRAY[]::bigint[], directed boolean DEFAULT true, OUT type text, OUT id bigint, OUT contracted_vertices bigint[], OUT source bigint, OUT target bigint, OUT cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_contraction$function$
;

-- Function: _pgr_contraction
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_contractionhierarchies(edges_sql text, forbidden_vertices bigint[], directed boolean, OUT type text, OUT id bigint, OUT contracted_vertices bigint[], OUT source bigint, OUT target bigint, OUT cost double precision, OUT metric bigint, OUT vertex_order bigint)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_contractionhierarchies$function$
;

-- Function: _pgr_contractionhierarchies
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_createindex(sname text, tname text, colname text, indext text, reporterrs integer DEFAULT 1, fnname text DEFAULT '_pgr_createIndex'::text)
 RETURNS void
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
    debuglevel text;
    naming record;
    tabname text;
    query text;
    msgKind int;
BEGIN
  msgKind = 0; -- debug_
  execute 'show client_min_messages' into debuglevel;
  tabname=_pgr_quote_ident(sname||'.'||tname);
  perform _pgr_msg(msgKind, fnName, 'Checking ' || colname || ' column in ' || tabname || ' is indexed');
    IF (_pgr_isColumnIndexed(sname,tname,colname, 0, fnName)) then
       perform _pgr_msg(msgKind, fnName);
    else
      if indext = 'gist' then
        query = 'create  index '||_pgr_quote_ident(tname||'_'||colname||'_idx')||'
                         on '||tabname||' using gist('||quote_ident(colname)||')';
      else
        query = 'create  index '||_pgr_quote_ident(tname||'_'||colname||'_idx')||'
                         on '||tabname||' using btree('||quote_ident(colname)||')';
      end if;
      perform _pgr_msg(msgKind, fnName, 'Adding index ' || tabname || '_' ||  colname || '_idx');
      perform _pgr_msg(msgKind, fnName, ' Using ' ||  query);
      set client_min_messages  to warning;
      BEGIN
        execute query;
        EXCEPTION WHEN others THEN
          perform _pgr_onError( true, reportErrs, fnName,
            'Could not create index on:' || colname, SQLERRM);
      END;
      execute 'set client_min_messages  to '|| debuglevel;
      perform _pgr_msg(msgKind, fnName);
    END IF;
END;
$function$
;

-- Function: _pgr_createindex
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_createindex(tabname text, colname text, indext text, reporterrs integer DEFAULT 1, fnname text DEFAULT '_pgr_createIndex'::text)
 RETURNS void
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
    naming record;
    sname text;
    tname text;
BEGIN
    select * from _pgr_getTableName(tabname, 2, fnName)  into naming;
    sname=naming.sname;
    tname=naming.tname;
    execute _pgr_createIndex(sname, tname, colname, indext, reportErrs, fnName);
END;
$function$
;

-- Function: _pgr_createindex
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_cuthillmckeeordering(text, OUT seq bigint, OUT node bigint)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_cuthillmckeeordering$function$
;

-- Function: _pgr_cuthillmckeeordering
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_dagshortestpath(text, text, directed boolean DEFAULT true, only_cost boolean DEFAULT false, OUT seq integer, OUT path_seq integer, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_dagshortestpath$function$
;

-- Function: _pgr_dagshortestpath
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_dagshortestpath(text, anyarray, anyarray, directed boolean DEFAULT true, only_cost boolean DEFAULT false, OUT seq integer, OUT path_seq integer, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_dagshortestpath$function$
;

-- Function: _pgr_dagshortestpath
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_depthfirstsearch(edges_sql text, root_vids anyarray, directed boolean, max_depth bigint, OUT seq bigint, OUT depth bigint, OUT start_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_depthfirstsearch$function$
;

-- Function: _pgr_depthfirstsearch
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_dijkstra(edges_sql text, combinations_sql text, directed boolean, only_cost boolean, n_goals bigint, global boolean, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_dijkstra$function$
;

-- Function: _pgr_dijkstra
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_dijkstra(edges_sql text, combinations_sql text, directed boolean DEFAULT true, only_cost boolean DEFAULT false, normal boolean DEFAULT true, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_dijkstra$function$
;

-- Function: _pgr_dijkstra
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_dijkstra(edges_sql text, start_vids anyarray, end_vids anyarray, directed boolean, only_cost boolean, normal boolean, n_goals bigint, global boolean, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_dijkstra$function$
;

-- Function: _pgr_dijkstra
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_dijkstra(edges_sql text, start_vids anyarray, end_vids anyarray, directed boolean DEFAULT true, only_cost boolean DEFAULT false, normal boolean DEFAULT true, n_goals bigint DEFAULT 0, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_dijkstra$function$
;

-- Function: _pgr_dijkstra
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_dijkstranear(text, anyarray, bigint, bigint, directed boolean DEFAULT true, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE sql
AS $function$
    SELECT seq, path_seq, start_vid, node, edge, cost, agg_cost
    FROM _pgr_dijkstra(_pgr_get_statement($1), $2::BIGINT[], ARRAY[$3]::BIGINT[], $5, false, false, $4);
$function$
;

-- Function: _pgr_dijkstranear
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_dijkstranear(text, bigint, anyarray, bigint, directed boolean DEFAULT true, OUT seq integer, OUT path_seq integer, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE sql
AS $function$
    SELECT seq, path_seq, end_vid, node, edge, cost, agg_cost
    FROM _pgr_dijkstra(_pgr_get_statement($1), ARRAY[$2]::BIGINT[], $3::BIGINT[], $5, false, true, $4);
$function$
;

-- Function: _pgr_dijkstranear
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_dijkstranear(text, anyarray, anyarray, bigint, directed boolean DEFAULT true, OUT seq integer, OUT path_seq integer, OUT end_vid bigint, OUT start_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE sql
AS $function$
    SELECT seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost
    FROM _pgr_dijkstra(_pgr_get_statement($1), ARRAY[$2]::BIGINT[], ARRAY[$3]::BIGINT[], $5, false, false, $4);
$function$
;

-- Function: _pgr_dijkstranear
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_dijkstravia(edges_sql text, via_vids anyarray, directed boolean, strict boolean, u_turn_on_edge boolean, OUT seq integer, OUT path_id integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision, OUT route_agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_dijkstravia$function$
;

-- Function: _pgr_dijkstravia
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_drivingdistance(edges_sql text, start_vids anyarray, distance double precision, directed boolean DEFAULT true, equicost boolean DEFAULT false, OUT seq integer, OUT from_v bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_drivingdistance$function$
;

-- Function: _pgr_drivingdistance
-- Comment: pgRouting internal function deprecated on v3.6.0
-- 
CREATE OR REPLACE FUNCTION public._pgr_drivingdistancev4(text, anyarray, double precision, boolean, boolean, OUT seq bigint, OUT depth bigint, OUT start_vid bigint, OUT pred bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_drivingdistancev4$function$
;

-- Function: _pgr_drivingdistancev4
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_edgecoloring(edges_sql text, OUT edge_id bigint, OUT color_id bigint)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_edgecoloring$function$
;

-- Function: _pgr_edgecoloring
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_edgedisjointpaths(text, text, directed boolean, OUT seq integer, OUT path_id integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_edgedisjointpaths$function$
;

-- Function: _pgr_edgedisjointpaths
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_edgedisjointpaths(text, anyarray, anyarray, directed boolean, OUT seq integer, OUT path_id integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_edgedisjointpaths$function$
;

-- Function: _pgr_edgedisjointpaths
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_edwardmoore(edges_sql text, from_vids anyarray, to_vids anyarray, directed boolean DEFAULT true, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_edwardmoore$function$
;

-- Function: _pgr_edwardmoore
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_edwardmoore(edges_sql text, combinations_sql text, directed boolean DEFAULT true, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_edwardmoore$function$
;

-- Function: _pgr_edwardmoore
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_endpoint(g geometry)
 RETURNS geometry
 LANGUAGE sql
 IMMUTABLE
AS $function$
SELECT CASE WHEN geometryType($1) ~ '^MULTI' THEN ST_EndPoint(st_geometryN($1,1))
ELSE ST_EndPoint($1)
END;
$function$
;

-- Function: _pgr_endpoint
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_floydwarshall(edges_sql text, directed boolean, OUT start_vid bigint, OUT end_vid bigint, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_floydwarshall$function$
;

-- Function: _pgr_floydwarshall
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_get_statement(o_sql text)
 RETURNS text
 LANGUAGE plpgsql
 STABLE STRICT
AS $function$
DECLARE
sql TEXT;
BEGIN
    EXECUTE 'SELECT statement FROM pg_prepared_statements WHERE name ='  || quote_literal(o_sql) || ' limit 1 ' INTO sql;
    IF (sql IS NULL) THEN
      RETURN   o_sql;
    ELSE
      RETURN  regexp_replace(regexp_replace(regexp_replace(sql, '\s(as)\s', '___foo___', 'i'), '^.*___foo___', '','i'), ';$', '');
    END IF;
END
$function$
;

-- Function: _pgr_get_statement
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_getcolumnname(sname text, tname text, col text, reporterrs integer DEFAULT 1, fnname text DEFAULT '_pgr_getColumnName'::text)
 RETURNS text
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
    cname text;
    naming record;
    err boolean;
BEGIN
    execute 'SELECT column_name FROM information_schema.columns
          WHERE table_name='||quote_literal(tname)||' and table_schema='||quote_literal(sname)||' and column_name='||quote_literal(col) into cname;
    IF cname is null  THEN
    execute 'SELECT column_name FROM information_schema.columns
          WHERE table_name='||quote_literal(tname)||' and table_schema='||quote_literal(sname)||' and column_name='||quote_literal(lower(col))  into cname;
    END if;
    err = cname is null;
    perform _pgr_onError(err, reportErrs, fnName,  'Column '|| col ||' not found', ' Check your column name','Column '|| col || ' found');
    RETURN cname;
END;
$function$
;

-- Function: _pgr_getcolumnname
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_getcolumnname(tab text, col text, reporterrs integer DEFAULT 1, fnname text DEFAULT '_pgr_getColumnName'::text)
 RETURNS text
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
    sname text;
    tname text;
    cname text;
    naming record;
    err boolean;
BEGIN
    select a.sname, a.tname into naming from _pgr_getTableName(tab,reportErrs, fnName) AS a;
    sname=naming.sname;
    tname=naming.tname;
    select _pgr_getColumnName into cname from _pgr_getColumnName(sname,tname,col,reportErrs, fnName);
    RETURN cname;
END;
$function$
;

-- Function: _pgr_getcolumnname
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_getcolumntype(sname text, tname text, cname text, reporterrs integer DEFAULT 0, fnname text DEFAULT '_pgr_getColumnType'::text)
 RETURNS text
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
    ctype text;
    naming record;
    err boolean;
BEGIN
    EXECUTE 'select data_type  from information_schema.columns '
            || 'where table_name = '||quote_literal(tname)
                 || ' and table_schema=' || quote_literal(sname)
                 || ' and column_name='||quote_literal(cname)
       into ctype;
    err = ctype is null;
    perform _pgr_onError(err, reportErrs, fnName,
            'Type of Column '|| cname ||' not found',
            'Check your column name',
            'OK: Type of Column '|| cname || ' is ' || ctype);
    RETURN ctype;
END;
$function$
;

-- Function: _pgr_getcolumntype
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_getcolumntype(tab text, col text, reporterrs integer DEFAULT 0, fnname text DEFAULT '_pgr_getColumnType'::text)
 RETURNS text
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
    sname text;
    tname text;
    cname text;
    ctype text;
    naming record;
    err boolean;
BEGIN
    select * into naming from _pgr_getTableName(tab,reportErrs, fnName) ;
    sname=naming.sname;
    tname=naming.tname;
    select _pgr_getColumnName into cname from _pgr_getColumnName(tab,col,reportErrs, fnName) ;
    select _pgr_getColumnType into ctype from _pgr_getColumnType(sname,tname,cname,reportErrs, fnName);
    RETURN ctype;
END;
$function$
;

-- Function: _pgr_getcolumntype
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_gettablename(tab text, reporterrs integer DEFAULT 0, fnname text DEFAULT '_pgr_getTableName'::text, OUT sname text, OUT tname text)
 RETURNS record
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
        naming record;
        i integer;
        query text;
        sn text; -- schema name
        tn text; -- table name
        ttype text; --table type for future use
        err boolean;
        debuglevel text;
        var_types text[] = ARRAY['BASE TABLE', 'VIEW'];
BEGIN
    execute 'show client_min_messages' into debuglevel;
    perform _pgr_msg( 0, fnName, 'Checking table ' || tab || ' exists');
    i := strpos(tab,'.');
    IF (i <> 0) THEN
        sn := split_part(tab, '.',1);
        tn := split_part(tab, '.',2);
    ELSE
        sn := current_schema;
        tn := tab;
    END IF;
   SELECT schema_name INTO sname
   FROM information_schema.schemata WHERE schema_name = sn;
    IF sname IS NOT NULL THEN -- found schema (as is)
       SELECT table_name, table_type INTO tname, ttype
       FROM information_schema.tables
       WHERE
                table_type = ANY(var_types) and
                table_schema = sname and
                table_name = tn ;
        IF tname is NULL THEN
            SELECT table_name, table_type INTO tname, ttype
            FROM information_schema.tables
            WHERE
                table_type  = ANY(var_types) and
                table_schema = sname and
                table_name = lower(tn) ORDER BY table_name;
        END IF;
    END IF;
    IF sname is NULL or tname is NULL THEN --schema not found or table not found
        SELECT schema_name INTO sname
        FROM information_schema.schemata
        WHERE schema_name = lower(sn) ;
        IF sname IS NOT NULL THEN -- found schema (with lower caps)
            SELECT table_name, table_type INTO tname, ttype
            FROM information_schema.tables
            WHERE
                table_type  =  ANY(var_types) and
                table_schema = sname and
                table_name= tn ;
           IF tname IS NULL THEN
                SELECT table_name, table_type INTO tname, ttype
                FROM information_schema.tables
                WHERE
                    table_type  =  ANY(var_types) and
                    table_schema = sname and
                    table_name= lower(tn) ;
           END IF;
        END IF;
    END IF;
   err = (sname IS NULL OR tname IS NULL);
   perform _pgr_onError(err, reportErrs, fnName, 'Table ' || tab ||' not found',' Check your table name', 'Table '|| tab || ' found');
END;
$function$
;

-- Function: _pgr_gettablename
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_git_hash()
 RETURNS text
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_git_hash$function$
;

-- Function: _pgr_git_hash
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_hawickcircuits(text, OUT seq integer, OUT path_id integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_hawickcircuits$function$
;

-- Function: _pgr_hawickcircuits
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_iscolumnindexed(tab text, col text, reporterrs integer DEFAULT 1, fnname text DEFAULT '_pgr_isColumnIndexed'::text)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
    naming record;
    rec record;
    sname text;
    tname text;
    cname text;
    pkey text;
    value boolean;
BEGIN
    SELECT a.sname, a.tname into naming FROM _pgr_getTableName(tab, 0, fnName) AS a;
    sname=naming.sname;
    tname=naming.tname;
    IF sname IS NULL OR tname IS NULL THEN
        RETURN FALSE;
    END IF;
    SELECT _pgr_getColumnName into cname from _pgr_getColumnName(sname, tname, col, 0, fnName) ;
    IF cname IS NULL THEN
        RETURN FALSE;
    END IF;
    select _pgr_isColumnIndexed into value  from _pgr_isColumnIndexed(sname, tname, cname, reportErrs, fnName);
    return value;
END
$function$
;

-- Function: _pgr_iscolumnindexed
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_iscolumnindexed(sname text, tname text, cname text, reporterrs integer DEFAULT 1, fnname text DEFAULT '_pgr_isColumnIndexed'::text)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
    naming record;
    rec record;
    pkey text;
BEGIN
    SELECT
          pg_attribute.attname into pkey
          FROM pg_index, pg_class, pg_attribute
          WHERE
                  pg_class.oid = _pgr_quote_ident(sname||'.'||tname)::regclass AND
                  indrelid = pg_class.oid AND
                  pg_attribute.attrelid = pg_class.oid AND
                  pg_attribute.attnum = any(pg_index.indkey)
                  AND indisprimary;
    IF pkey=cname then
          RETURN TRUE;
    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 = cname
  ORDER BY a.index_name,
           a.index_num;
  RETURN FOUND;
  EXCEPTION WHEN OTHERS THEN
    perform _pgr_onError( true, reportErrs, fnName,
    'Error when checking for the postgres system attributes', SQLERR);
    RETURN FALSE;
END;
$function$
;

-- Function: _pgr_iscolumnindexed
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_iscolumnintable(tab text, col text)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
    cname text;
BEGIN
    select _pgr_getColumnName from _pgr_getColumnName(tab,col,0, '_pgr_isColumnInTable') into cname;
    return cname is not null;
END;
$function$
;

-- Function: _pgr_iscolumnintable
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_isplanar(text)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_isplanar$function$
;

-- Function: _pgr_isplanar
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_johnson(edges_sql text, directed boolean, OUT start_vid bigint, OUT end_vid bigint, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_johnson$function$
;

-- Function: _pgr_johnson
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_kruskal(text, anyarray, fn_suffix text, max_depth bigint, distance double precision, OUT seq bigint, OUT depth bigint, OUT start_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_kruskal$function$
;

-- Function: _pgr_kruskal
-- Comment: pgRouting internal function deprecated on v3.7.0
-- 
CREATE OR REPLACE FUNCTION public._pgr_kruskalv4(text, anyarray, text, bigint, double precision, OUT seq bigint, OUT depth bigint, OUT start_vid bigint, OUT pred bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_kruskalv4$function$
;

-- Function: _pgr_kruskalv4
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_ksp(edges_sql text, start_vid bigint, end_vid bigint, k integer, directed boolean, heap_paths boolean, OUT seq integer, OUT path_id integer, OUT path_seq integer, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_ksp$function$
;

-- Function: _pgr_ksp
-- Comment: pgRouting internal function deprecated on v3.6.0
-- 
CREATE OR REPLACE FUNCTION public._pgr_ksp(text, text, integer, boolean, boolean, OUT seq integer, OUT path_id integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_ksp$function$
;

-- Function: _pgr_ksp
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_ksp(text, anyarray, anyarray, integer, boolean, boolean, boolean, OUT seq integer, OUT path_id integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_ksp$function$
;

-- Function: _pgr_ksp
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_lengauertarjandominatortree(edges_sql text, root_vid bigint, OUT seq integer, OUT vid bigint, OUT idom bigint)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_lengauertarjandominatortree$function$
;

-- Function: _pgr_lengauertarjandominatortree
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_lib_version()
 RETURNS text
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_lib_version$function$
;

-- Function: _pgr_lib_version
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_linegraph(text, directed boolean, OUT seq integer, OUT source bigint, OUT target bigint, OUT cost double precision, OUT reverse_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_linegraph$function$
;

-- Function: _pgr_linegraph
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_linegraphfull(text, OUT seq integer, OUT source bigint, OUT target bigint, OUT cost double precision, OUT edge bigint)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_linegraphfull$function$
;

-- Function: _pgr_linegraphfull
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_makeconnected(text, OUT seq bigint, OUT start_vid bigint, OUT end_vid bigint)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_makeconnected$function$
;

-- Function: _pgr_makeconnected
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_maxcardinalitymatch(edges_sql text, directed boolean, OUT seq integer, OUT edge bigint, OUT source bigint, OUT target bigint)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_maxcardinalitymatch$function$
;

-- Function: _pgr_maxcardinalitymatch
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_maxflow(edges_sql text, combinations_sql text, algorithm integer DEFAULT 1, only_flow boolean DEFAULT false, OUT seq integer, OUT edge_id bigint, OUT source bigint, OUT target bigint, OUT flow bigint, OUT residual_capacity bigint)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_maxflow$function$
;

-- Function: _pgr_maxflow
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_maxflow(edges_sql text, sources anyarray, targets anyarray, algorithm integer DEFAULT 1, only_flow boolean DEFAULT false, OUT seq integer, OUT edge_id bigint, OUT source bigint, OUT target bigint, OUT flow bigint, OUT residual_capacity bigint)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_maxflow$function$
;

-- Function: _pgr_maxflow
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_maxflowmincost(edges_sql text, combinations_sql text, only_cost boolean DEFAULT false, OUT seq integer, OUT edge bigint, OUT source bigint, OUT target bigint, OUT flow bigint, OUT residual_capacity bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_maxflowmincost$function$
;

-- Function: _pgr_maxflowmincost
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_maxflowmincost(edges_sql text, sources anyarray, targets anyarray, only_cost boolean DEFAULT false, OUT seq integer, OUT edge bigint, OUT source bigint, OUT target bigint, OUT flow bigint, OUT residual_capacity bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_maxflowmincost$function$
;

-- Function: _pgr_maxflowmincost
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_msg(msgkind integer, fnname text, msg text DEFAULT '---->OK'::text)
 RETURNS void
 LANGUAGE plpgsql
 STRICT
AS $function$
BEGIN
  if msgKind = 0 then
       raise debug '----> PGR DEBUG in %: %',fnName,msg;
  else
       raise notice '----> PGR NOTICE in %: %',fnName,msg;
  end if;
END;
$function$
;

-- Function: _pgr_msg
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_onerror(errcond boolean, reporterrs integer, fnname text, msgerr text, hinto text DEFAULT 'No hint'::text, msgok text DEFAULT 'OK'::text)
 RETURNS void
 LANGUAGE plpgsql
 STRICT
AS $function$
BEGIN
  if errCond=true then
     if reportErrs=0 then
       raise debug '----> PGR DEBUG in %: %',fnName,msgerr USING HINT = '  ---->'|| hinto;
     else
       if reportErrs = 2 then
         raise notice '----> PGR ERROR in %: %',fnName,msgerr USING HINT = '  ---->'|| hinto;
         raise raise_exception;
       else
         raise notice '----> PGR NOTICE in %: %',fnName,msgerr USING HINT = '  ---->'|| hinto;
       end if;
     end if;
  else
       raise debug 'PGR ----> %: %',fnName,msgok;
  end if;
END;
$function$
;

-- Function: _pgr_onerror
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_operating_system()
 RETURNS text
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_operating_system$function$
;

-- Function: _pgr_operating_system
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_parameter_check(fn text, sql text, big boolean DEFAULT false)
 RETURNS boolean
 LANGUAGE plpgsql
 COST 1
AS $function$
  DECLARE
  rec record;
  rec1 record;
  has_rcost boolean;
  safesql text;
  BEGIN
    IF (big) THEN
       RAISE EXCEPTION 'This function is for old style functions';
    END IF;
    BEGIN
      safesql =  'select * from ('||sql||' ) AS __a__ limit 1';
      execute safesql into rec;
      EXCEPTION
        WHEN OTHERS THEN
            RAISE EXCEPTION 'Could not execute query please verify syntax of: '
              USING HINT = sql;
    END;
    IF fn IN ('dijkstra','astar') THEN
        BEGIN
          execute 'select id,source,target,cost  from ('||safesql||') as __b__' into rec;
          EXCEPTION
            WHEN OTHERS THEN
                RAISE EXCEPTION 'An expected column was not found in the query'
                  USING ERRCODE = 'XX000',
                   HINT = 'Please veryfy the column names: id, source, target, cost';
        END;
        execute 'select pg_typeof(id)::text as id_type, pg_typeof(source)::text as source_type, pg_typeof(target)::text as target_type, pg_typeof(cost)::text as cost_type'
            || ' from ('||safesql||') AS __b__ ' into rec;
        IF NOT(   (rec.id_type in ('integer'::text))
              AND (rec.source_type in ('integer'::text))
              AND (rec.target_type in ('integer'::text))
              AND (rec.cost_type = 'double precision'::text)) THEN
            RAISE EXCEPTION 'Error, columns ''source'', ''target'' must be of type int4, ''cost'' must be of type float8'
            USING ERRCODE = 'XX000';
        END IF;
    END IF;
    IF fn IN ('astar') THEN
        BEGIN
          execute 'select x1,y1,x2,y2  from ('||safesql||') as __b__' into rec;
          EXCEPTION
            WHEN OTHERS THEN
                RAISE EXCEPTION 'An expected column was not found in the query'
                  USING ERRCODE = 'XX000',
                   HINT = 'Please veryfy the column names: x1,y1, x2,y2';
        END;
        execute 'select pg_typeof(x1)::text as x1_type, pg_typeof(y1)::text as y1_type, pg_typeof(x2)::text as x2_type, pg_typeof(y2)::text as y2_type'
            || ' from ('||safesql||') AS __b__ ' into rec;
        IF NOT(   (rec.x1_type = 'double precision'::text)
              AND (rec.y1_type = 'double precision'::text)
              AND (rec.x2_type = 'double precision'::text)
              AND (rec.y2_type = 'double precision'::text)) THEN
            RAISE EXCEPTION 'Columns: x1, y1, x2, y2 must be of type float8'
            USING ERRCODE = 'XX000';
        END IF;
    END IF;
    IF fn IN ('johnson') THEN
        BEGIN
          execute 'select source,target,cost  from ('||safesql||') as __b__' into rec;
          EXCEPTION
            WHEN OTHERS THEN
                RAISE EXCEPTION 'An expected column was not found in the query'
                  USING HINT = 'Please veryfy the column names: id, source, target, cost',
                         ERRCODE = 'XX000';
        END;
        execute 'select pg_typeof(source)::text as source_type, pg_typeof(target)::text as target_type, pg_typeof(cost)::text as cost_type'
            || ' from ('||safesql||') AS __b__ ' into rec;
        IF NOT(   (rec.source_type in ('integer'::text))
              AND (rec.target_type in ('integer'::text))
              AND (rec.cost_type = 'double precision'::text)) THEN
            RAISE EXCEPTION 'Support for source,target columns only of type: integer. Support for Cost: double precision'
            USING ERRCODE = 'XX000';
        END IF;
    END IF;
    has_rcost := false;
    IF fn IN ('johnson','dijkstra','astar') THEN
      BEGIN
        execute 'select reverse_cost, pg_typeof(reverse_cost)::text as rev_type  from ('||safesql||' ) AS __b__ limit 1 ' into rec1;
        has_rcost := true;
        EXCEPTION
          WHEN OTHERS THEN
            has_rcost = false;
            return has_rcost;
      END;
      if (has_rcost) then
        IF (big) then
           IF  not (rec1.rev_type in ('bigint'::text, 'integer'::text, 'smallint'::text, 'double precision'::text, 'real'::text)) then
             RAISE EXCEPTION 'Illegar type in optional parameter reverse_cost.'
             USING ERRCODE = 'XX000';
           END IF;
        ELSE -- Version 2.0.0 is more restrictive
           IF (rec1.rev_type != 'double precision') then
             RAISE EXCEPTION 'Illegal type in optional parameter reverse_cost, must be of type float8'
             USING ERRCODE = 'XX000';
           END IF;
        END IF;
      end if;
      return true;
    END IF;
    return true;
  END
  $function$
;

-- Function: _pgr_parameter_check
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_pgsql_version()
 RETURNS text
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_pgsql_version$function$
;

-- Function: _pgr_pgsql_version
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_pickdeliver(text, text, text, factor double precision DEFAULT 1, max_cycles integer DEFAULT 10, initial_sol integer DEFAULT 4, OUT seq integer, OUT vehicle_seq integer, OUT vehicle_id bigint, OUT stop_seq integer, OUT stop_type integer, OUT stop_id bigint, OUT order_id bigint, OUT cargo double precision, OUT travel_time double precision, OUT arrival_time double precision, OUT wait_time double precision, OUT service_time double precision, OUT departure_time double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_pickdeliver$function$
;

-- Function: _pgr_pickdeliver
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_pickdelivereuclidean(text, text, factor double precision DEFAULT 1, max_cycles integer DEFAULT 10, initial_sol integer DEFAULT 4, OUT seq integer, OUT vehicle_seq integer, OUT vehicle_id bigint, OUT stop_seq integer, OUT stop_type integer, OUT order_id bigint, OUT cargo double precision, OUT travel_time double precision, OUT arrival_time double precision, OUT wait_time double precision, OUT service_time double precision, OUT departure_time double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_pickdelivereuclidean$function$
;

-- Function: _pgr_pickdelivereuclidean
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_pointtoid(point geometry, tolerance double precision, vertname text, srid integer)
 RETURNS bigint
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
    rec record;
    pid bigint;
BEGIN
    EXECUTE 'SELECT ST_Distance(
        the_geom,
        ST_GeomFromText(ST_AsText('
                || quote_literal(point::text)
                || '),'
            || srid ||')) AS d, id, the_geom
    FROM '||_pgr_quote_ident(vertname)||'
    WHERE ST_DWithin(
        the_geom,
        ST_GeomFromText(
            ST_AsText(' || quote_literal(point::text) ||'),
            ' || srid || '),' || tolerance||')
    ORDER BY d
    LIMIT 1' INTO rec ;
    IF rec.id IS NOT NULL THEN
        pid := rec.id;
    ELSE
        execute 'INSERT INTO '||_pgr_quote_ident(vertname)||' (the_geom) VALUES ('||quote_literal(point::text)||')';
        pid := lastval();
END IF;
RETURN pid;
END;
$function$
;

-- Function: _pgr_pointtoid
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_prim(text, anyarray, order_by text, max_depth bigint, distance double precision, OUT seq bigint, OUT depth bigint, OUT start_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_prim$function$
;

-- Function: _pgr_prim
-- Comment: pgRouting internal function deprecated on v3.7.0
-- 
CREATE OR REPLACE FUNCTION public._pgr_primv4(text, anyarray, text, bigint, double precision, OUT seq bigint, OUT depth bigint, OUT start_vid bigint, OUT pred bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_primv4$function$
;

-- Function: _pgr_primv4
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_quote_ident(idname text)
 RETURNS text
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
declare
    t text[];
    pgver text;
begin
    pgver := regexp_replace(version(), E'^PostgreSQL ([^ ]+)[ ,].*$', E'\\1');
    if _pgr_versionless(pgver, '9.2') then
        select into t array_agg(quote_ident(term)) from
            (select nullif(unnest, '') as term
               from unnest(string_to_array(idname, '.'))) as foo;
    else
        select into t array_agg(quote_ident(term)) from
            (select unnest(string_to_array(idname, '.', '')) as term) as foo;
    end if;
    return array_to_string(t, '.');
end;
$function$
;

-- Function: _pgr_quote_ident
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_sequentialvertexcoloring(edges_sql text, OUT vertex_id bigint, OUT color_id bigint)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_sequentialvertexcoloring$function$
;

-- Function: _pgr_sequentialvertexcoloring
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_startpoint(g geometry)
 RETURNS geometry
 LANGUAGE sql
 IMMUTABLE
AS $function$
SELECT CASE WHEN geometryType($1) ~ '^MULTI' THEN ST_StartPoint(ST_geometryN($1,1))
ELSE ST_StartPoint($1)
END;
$function$
;

-- Function: _pgr_startpoint
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_stoerwagner(edges_sql text, OUT seq integer, OUT edge bigint, OUT cost double precision, OUT mincut double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_stoerwagner$function$
;

-- Function: _pgr_stoerwagner
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_strongcomponents(edges_sql text, OUT seq bigint, OUT component bigint, OUT node bigint)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_strongcomponents$function$
;

-- Function: _pgr_strongcomponents
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_topologicalsort(edges_sql text, OUT seq integer, OUT sorted_v bigint)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_topologicalsort$function$
;

-- Function: _pgr_topologicalsort
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_transitiveclosure(edges_sql text, OUT seq integer, OUT vid bigint, OUT target_array bigint[])
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_transitiveclosure$function$
;

-- Function: _pgr_transitiveclosure
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_trsp(text, text, anyarray, bigint, directed boolean DEFAULT true, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE sql
 STRICT
AS $function$
    SELECT a.seq, a.path_seq, a.start_vid, a.node, a.edge, a.cost, a.agg_cost
        FROM _trsp(
            _pgr_get_statement($1),
            _pgr_get_statement($2),
            $3::bigint[],
            ARRAY[$4]::BIGINT[],
            $5) AS a;
$function$
;

-- Function: _pgr_trsp
-- Comment: pgRouting internal function deprecated on v3.4.0
-- 
CREATE OR REPLACE FUNCTION public._pgr_trsp(sql text, source_eid integer, source_pos double precision, target_eid integer, target_pos double precision, directed boolean, has_reverse_cost boolean, turn_restrict_sql text DEFAULT NULL::text, OUT seq integer, OUT id1 integer, OUT id2 integer, OUT cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE
AS '$libdir/libpgrouting-3.8', $function$_pgr_trsp$function$
;

-- Function: _pgr_trsp
-- Comment: pgRouting internal function deprecated on v3.4.0
-- 
CREATE OR REPLACE FUNCTION public._pgr_trsp(text, text, bigint, bigint, directed boolean DEFAULT true, OUT seq integer, OUT path_seq integer, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE sql
 STRICT
AS $function$
    SELECT a.seq, a.path_seq, a.node, a.edge, a.cost, a.agg_cost
        FROM _trsp(
            _pgr_get_statement($1),
            _pgr_get_statement($2),
            ARRAY[$3]::BIGINT[],
            ARRAY[$4]::BIGINT[],
            directed) AS a;
$function$
;

-- Function: _pgr_trsp
-- Comment: pgRouting internal function deprecated on v3.4.0
-- 
CREATE OR REPLACE FUNCTION public._pgr_trsp(text, text, anyarray, anyarray, directed boolean DEFAULT true, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE sql
 STRICT
AS $function$
    SELECT a.seq, a.path_seq, a.start_vid, a.end_vid, a.node, a.edge, a.cost, a.agg_cost
        FROM _trsp(
            _pgr_get_statement($1),
            _pgr_get_statement($2),
            $3::bigint[],
            $4::bigint[],
            $5) AS a;
$function$
;

-- Function: _pgr_trsp
-- Comment: pgRouting internal function deprecated on v3.4.0
-- 
CREATE OR REPLACE FUNCTION public._pgr_trsp(text, text, bigint, anyarray, directed boolean DEFAULT true, OUT seq integer, OUT path_seq integer, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE sql
 STRICT
AS $function$
    SELECT a.seq, a.path_seq, a.end_vid, a.node, a.edge, a.cost, a.agg_cost
        FROM _trsp(
            _pgr_get_statement($1),
            _pgr_get_statement($2),
            ARRAY[$3]::BIGINT[],
            $4::bigint[],
            directed) AS a;
$function$
;

-- Function: _pgr_trsp
-- Comment: pgRouting internal function deprecated on v3.4.0
-- 
CREATE OR REPLACE FUNCTION public._pgr_trsp_withpoints(text, text, text, text, directed boolean, driving_side character, details boolean, OUT seq integer, OUT path_seq integer, OUT departure bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_trsp_withpoints$function$
;

-- Function: _pgr_trsp_withpoints
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_trsp_withpoints(text, text, text, anyarray, anyarray, directed boolean, driving_side character, details boolean, OUT seq integer, OUT path_seq integer, OUT departure bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
AS '$libdir/libpgrouting-3.8', $function$_pgr_trsp_withpoints$function$
;

-- Function: _pgr_trsp_withpoints
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_trspv4(text, text, anyarray, anyarray, boolean, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
AS '$libdir/libpgrouting-3.8', $function$_pgr_trspv4$function$
;

-- Function: _pgr_trspv4
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_trspv4(text, text, text, boolean, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
AS '$libdir/libpgrouting-3.8', $function$_pgr_trspv4$function$
;

-- Function: _pgr_trspv4
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_trspvia(text, text, anyarray, boolean, boolean, boolean, OUT seq integer, OUT path_id integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision, OUT route_agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_trspvia$function$
;

-- Function: _pgr_trspvia
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_trspvia_withpoints(text, text, text, anyarray, boolean, boolean, boolean, character, boolean, OUT seq integer, OUT path_id integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision, OUT route_agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_trspvia_withpoints$function$
;

-- Function: _pgr_trspvia_withpoints
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_trspviavertices(sql text, vids integer[], directed boolean, has_rcost boolean, turn_restrict_sql text DEFAULT NULL::text, OUT seq integer, OUT id1 integer, OUT id2 integer, OUT id3 integer, OUT cost double precision)
 RETURNS SETOF record
 LANGUAGE plpgsql
 STABLE
AS $function$
declare
    i integer;
    rr RECORD;
    lrr RECORD;
    lrra boolean := false;
    seq1 integer := 0;
    seq2 integer := 0;
    restrictions_query TEXT;
begin
    IF (turn_restrict_sql IS NULL) THEN
        RAISE EXCEPTION 'Restrictions Missing';
    END IF;
    restrictions_query = $$
    WITH old_restrictions AS ( $$ ||
        $5 || $$
    )
    SELECT ROW_NUMBER() OVER() AS id,
    _pgr_array_reverse(array_prepend(target_id, string_to_array(via_path, ',')::INTEGER[])) AS path,
    to_cost AS cost
    FROM old_restrictions;
    $$;
    for i in 1 .. array_length(vids, 1)-1 loop
        seq2 := seq2 + 1;
        for rr in select a.seq, seq2 as id1, a.node::INTEGER as id2, a.edge::INTEGER as id3, a.cost
                    from _pgr_trsp(sql, restrictions_query, vids[i], vids[i+1], directed) as a loop
            if rr.id3 = -1 then
                lrr := rr;
                lrra := true;
            else
                seq1 := seq1 + 1;
                rr.seq := seq1;
                seq := rr.seq;
                id1 := rr.id1;
                id2 := rr.id2;
                id3 := rr.id3;
                cost := rr.cost;
                return next;
            end if;
        end loop;
    end loop;
    if lrra then
        seq1 := seq1 + 1;
        lrr.seq := seq1;
        seq := lrr.seq;
        id1 := lrr.id1;
        id2 := lrr.id2;
        id3 := lrr.id3;
        cost := lrr.cost;
        return next;
    end if;
    return;
end;
$function$
;

-- Function: _pgr_trspviavertices
-- Comment: pgRouting internal function deprecated on v3.4.0
-- 
CREATE OR REPLACE FUNCTION public._pgr_tsp(matrix_row_sql text, start_id bigint DEFAULT 0, end_id bigint DEFAULT 0, max_processing_time double precision DEFAULT 'Infinity'::double precision, tries_per_temperature integer DEFAULT 500, max_changes_per_temperature integer DEFAULT 60, max_consecutive_non_changes integer DEFAULT 100, initial_temperature double precision DEFAULT 100, final_temperature double precision DEFAULT 0.1, cooling_factor double precision DEFAULT 0.9, randomize boolean DEFAULT true, OUT seq integer, OUT node bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_tsp$function$
;

-- Function: _pgr_tsp
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_tspeuclidean(coordinates_sql text, start_id bigint DEFAULT 0, end_id bigint DEFAULT 0, max_processing_time double precision DEFAULT 'Infinity'::double precision, tries_per_temperature integer DEFAULT 500, max_changes_per_temperature integer DEFAULT 60, max_consecutive_non_changes integer DEFAULT 100, initial_temperature double precision DEFAULT 100, final_temperature double precision DEFAULT 0.1, cooling_factor double precision DEFAULT 0.9, randomize boolean DEFAULT true, OUT seq integer, OUT node bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_tspeuclidean$function$
;

-- Function: _pgr_tspeuclidean
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_turnrestrictedpath(text, text, bigint, bigint, integer, directed boolean, heap_paths boolean, stop_on_first boolean, strict boolean, OUT seq integer, OUT path_id integer, OUT path_seq integer, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_turnrestrictedpath$function$
;

-- Function: _pgr_turnrestrictedpath
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_versionless(v1 text, v2 text)
 RETURNS boolean
 LANGUAGE plpgsql
 COST 1
AS $function$
declare
    v1a text[];
    v2a text[];
    nv1 integer;
    nv2 integer;
    ne1 integer;
    ne2 integer;
begin
    v1a := regexp_matches(v1, E'^(\\d+)(?:[\\.](\\d+))?(?:[\\.](\\d+))?[-+\\.]?(.*)$');
    v2a := regexp_matches(v2, E'^(\\d+)(?:[\\.](\\d+))?(?:[\\.](\\d+))?[-+\\.]?(.*)$');
    ne1 := case when v1a[4] is null or v1a[4]='' then 5
                when v1a[4] ilike 'rc%' then 4
                when v1a[4] ilike 'beta%' then 3
                when v1a[4] ilike 'alpha%' then 2
                when v1a[4] ilike 'dev%' then 1
                else 0 end;
    ne2 := case when v2a[4] is null or v2a[4]='' then 5
                when v2a[4] ilike 'rc%' then 4
                when v2a[4] ilike 'beta%' then 3
                when v2a[4] ilike 'alpha%' then 2
                when v2a[4] ilike 'dev%' then 1
                else 0 end;
    nv1 := v1a[1]::integer * 10000 +
           coalesce(v1a[2], '0')::integer * 1000 +
           coalesce(v1a[3], '0')::integer *  100 + ne1;
    nv2 := v2a[1]::integer * 10000 +
           coalesce(v2a[2], '0')::integer * 1000 +
           coalesce(v2a[3], '0')::integer *  100 + ne2;
    return nv1 < nv2;
end;
$function$
;

-- Function: _pgr_versionless
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_vrponedepot(text, text, text, integer, OUT seq integer, OUT vehicle_seq integer, OUT vehicle_id bigint, OUT stop_seq integer, OUT stop_type integer, OUT stop_id bigint, OUT order_id bigint, OUT cargo double precision, OUT travel_time double precision, OUT arrival_time double precision, OUT wait_time double precision, OUT service_time double precision, OUT departure_time double precision)
 RETURNS SETOF record
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
orders_sql TEXT;
trucks_sql TEXT;
matrix_sql TEXT;
final_sql TEXT;
BEGIN
    orders_sql = $$WITH
    vrp_orders AS ($$ || $1 || $$ ),
    pickups AS (
        SELECT id, x AS p_x, y AS p_y, open_time AS p_open, close_time AS p_close, service_time AS p_service
        FROM vrp_orders
        WHERE id = $$ || $4 || $$
    )
    SELECT vrp_orders.id AS id, order_unit AS demand, pickups.id AS p_node_id, p_x, p_y, p_open, p_close, p_service,
    vrp_orders.id AS d_node_id, x AS d_x, y AS d_y, open_time AS d_open, close_time AS d_close, service_time AS d_service
    FROM vrp_orders, pickups
    WHERE vrp_orders.id != $$ || $4;
    trucks_sql = $$ WITH
    vrp_orders AS ($$ || $1 || $$ ),
    vrp_vehicles AS ($$ || $2 || $$ ),
    starts AS (
        SELECT id AS start_node_id, x AS start_x, y AS start_y, open_time AS start_open, close_time AS start_close, service_time AS start_service
        FROM vrp_orders
        WHERE id = $$ || $4 || $$
    )
    SELECT vehicle_id AS id, capacity, starts.* FROM vrp_vehicles, starts;
    $$;
    final_sql = '
    SELECT seq, vehicle_seq, vehicle_id, stop_seq, stop_type, stop_id, order_id, cargo, travel_time, arrival_time,
           wait_time, service_time, departure_time
    FROM _pgr_pickDeliver(
            $$' || orders_sql || '$$,
            $$' || trucks_sql || '$$,
            $$' || $3 || '$$,
            max_cycles := 3,
            initial_sol := 7 ); ';
    RAISE DEBUG '%', orders_sql;
    RAISE DEBUG '%', trucks_sql;
    RAISE DEBUG '%', $3;
    RAISE DEBUG '%', final_sql;
    RETURN QUERY EXECUTE final_sql;
END;
$function$
;

-- Function: _pgr_vrponedepot
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_withpoints(edges_sql text, points_sql text, start_pids anyarray, end_pids anyarray, directed boolean, driving_side character, details boolean, only_cost boolean DEFAULT false, normal boolean DEFAULT true, OUT seq integer, OUT path_seq integer, OUT start_pid bigint, OUT end_pid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
AS '$libdir/libpgrouting-3.8', $function$_pgr_withpoints$function$
;

-- Function: _pgr_withpoints
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_withpoints(edges_sql text, points_sql text, combinations_sql text, directed boolean, driving_side character, details boolean, only_cost boolean DEFAULT false, OUT seq integer, OUT path_seq integer, OUT start_pid bigint, OUT end_pid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_withpoints$function$
;

-- Function: _pgr_withpoints
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_withpointsdd(edges_sql text, points_sql text, start_pid anyarray, distance double precision, directed boolean DEFAULT true, driving_side character DEFAULT 'b'::bpchar, details boolean DEFAULT false, equicost boolean DEFAULT false, OUT seq integer, OUT start_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_withpointsdd$function$
;

-- Function: _pgr_withpointsdd
-- Comment: pgRouting internal function deprecated on v3.6.0
-- 
CREATE OR REPLACE FUNCTION public._pgr_withpointsddv4(text, text, anyarray, double precision, character, boolean, boolean, boolean, OUT seq bigint, OUT depth bigint, OUT start_vid bigint, OUT pred bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_withpointsddv4$function$
;

-- Function: _pgr_withpointsddv4
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_withpointsksp(text, text, text, integer, character, boolean, boolean, boolean, OUT seq integer, OUT path_id integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_withpointsksp$function$
;

-- Function: _pgr_withpointsksp
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_withpointsksp(text, text, anyarray, anyarray, integer, character, boolean, boolean, boolean, boolean, OUT seq integer, OUT path_id integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_withpointsksp$function$
;

-- Function: _pgr_withpointsksp
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._pgr_withpointsksp(edges_sql text, points_sql text, start_pid bigint, end_pid bigint, k integer, directed boolean, heap_paths boolean, driving_side character, details boolean, OUT seq integer, OUT path_id integer, OUT path_seq integer, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STABLE STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_withpointsksp$function$
;

-- Function: _pgr_withpointsksp
-- Comment: pgRouting internal function deprecated on v3.6.0
-- 
CREATE OR REPLACE FUNCTION public._pgr_withpointsvia(sql text, via_edges bigint[], fraction double precision[], directed boolean DEFAULT true, OUT seq integer, OUT path_id integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision, OUT route_agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE plpgsql
 STRICT
AS $function$
  DECLARE
  has_rcost boolean;
  sql_new_vertices text := ' ';
  sql_on_vertex text;
  v_union text := ' ';
  dummyrec record;
  rec1 record;
  via_vertices int[];
  sql_safe text;
  new_edges text;
  BEGIN
     BEGIN
        sql_safe = 'SELECT id, source, target, cost, reverse_cost FROM ('|| sql || ') AS __a';
        EXECUTE 'select reverse_cost, pg_typeof(reverse_cost)::text as rev_type  from ('||sql_safe||' ) AS __b__ limit 1 ' INTO rec1;
        has_rcost := true;
        EXCEPTION
          WHEN OTHERS THEN
            has_rcost = false;
     END;
      IF array_length(via_edges, 1) != array_length(fraction, 1) then
        RAISE EXCEPTION 'The length of via_edges is different of length of via_edges';
      END IF;
      FOR i IN 1 .. array_length(via_edges, 1)
      LOOP
          IF fraction[i] = 0 THEN
              sql_on_vertex := 'SELECT source FROM ('|| sql || ') __a where id = ' || via_edges[i];
              EXECUTE sql_on_vertex into dummyrec;
              via_vertices[i] = dummyrec.source;
          ELSE IF fraction[i] = 1 THEN
              sql_on_vertex := 'SELECT target FROM ('|| sql || ') __a where id = ' || via_edges[i];
              EXECUTE sql_on_vertex into dummyrec;
              via_vertices[i] = dummyrec.target;
          ELSE
              via_vertices[i] = -i;
              IF has_rcost THEN
                   sql_new_vertices = sql_new_vertices || v_union ||
                          '(SELECT id, source, ' ||  -i || ' AS target, cost * ' || fraction[i] || ' AS cost,
                              reverse_cost * (1 - ' || fraction[i] || ')  AS reverse_cost
                          FROM (SELECT * FROM (' || sql || ') __b' || i || ' WHERE id = ' || via_edges[i] || ') __a' || i ||')
                             UNION
                          (SELECT id, ' ||  -i || ' AS source, target, cost * (1 -' || fraction[i] || ') AS cost,
                              reverse_cost *  ' || fraction[i] || '  AS reverse_cost
                          FROM (SELECT * FROM (' || sql || ') __b' || i || ' where id = ' || via_edges[i] || ') __a' || i ||')';
                      v_union = ' UNION ';
               ELSE
                   sql_new_vertices = sql_new_vertices || v_union ||
                          '(SELECT id, source, ' ||  -i || ' AS target, cost * ' || fraction[i] || ' AS cost
                          FROM (SELECT * FROM (' || sql || ') __b' || i || ' WHERE id = ' || via_edges[i] || ') __a' || i ||')
                             UNION
                          (SELECT id, ' ||  -i || ' AS source, target, cost * (1 -' || fraction[i] || ') AS cost
                          FROM (SELECT * FROM (' || sql || ') __b' || i || ' WHERE id = ' || via_edges[i] || ') __a' || i ||')';
                      v_union = ' UNION ';
               END IF;
          END IF;
          END IF;
     END LOOP;
     IF sql_new_vertices = ' ' THEN
         new_edges := sql;
     ELSE
         IF has_rcost THEN
            new_edges:= 'WITH
                   orig AS ( ' || sql || '),
                   original AS (SELECT id, source, target, cost, reverse_cost FROM orig),
                   the_union AS ( ' || sql_new_vertices || '),
                   first_part AS ( SELECT * FROM (SELECT id, target AS source,  lead(target) OVER w  AS target,
                         lead(cost) OVER w  - cost AS cost,
                         lead(cost) OVER w  - cost AS reverse_cost
                      FROM  the_union  WHERE source > 0 AND cost > 0
                      WINDOW w AS (PARTITION BY id  ORDER BY cost ASC) ) as n2
                      WHERE target IS NOT NULL),
                   second_part AS ( SELECT * FROM (SELECT id, lead(source) OVER w  AS source, source as target,
                         reverse_cost - lead(reverse_cost) OVER w  AS cost,
                         reverse_cost - lead(reverse_cost) OVER w  AS reverse_cost
                      FROM  the_union  WHERE target > 0 and reverse_cost > 0
                      WINDOW w AS (PARTITION BY id  ORDER BY reverse_cost ASC) ) as n2
                      WHERE source IS NOT NULL),
                   more_union AS ( SELECT * from (
                       (SELECT * FROM original)
                             UNION
                       (SELECT * FROM the_union)
                             UNION
                       (SELECT * FROM first_part)
                             UNION
                       (SELECT * FROM second_part) ) _union )
                  SELECT *  FROM more_union';
         ELSE
            new_edges:= 'WITH
                   orig AS ( ' || sql || '),
                   original AS (SELECT id, source, target, cost FROM orig),
                   the_union AS ( ' || sql_new_vertices || '),
                   first_part AS ( SELECT * FROM (SELECT id, target AS source,  lead(target) OVER w  AS target,
                         lead(cost) OVER w  - cost AS cost
                      FROM  the_union  WHERE source > 0 AND cost > 0
                      WINDOW w AS (PARTITION BY id  ORDER BY cost ASC) ) as n2
                      WHERE target IS NOT NULL ),
                   more_union AS ( SELECT * from (
                       (SELECT * FROM original)
                             UNION
                       (SELECT * FROM the_union)
                             UNION
                       (SELECT * FROM first_part) ) _union )
                  SELECT *  FROM more_union';
          END IF;
      END IF;
     sql_new_vertices := sql_new_vertices || v_union || ' (' || sql || ')';
     RETURN query SELECT *
         FROM pgr_dijkstraVia(new_edges, via_vertices, directed, has_rcost);
  END
  $function$
;

-- Function: _pgr_withpointsvia
-- Comment: pgRouting internal function deprecated on v3.4.0
-- 
CREATE OR REPLACE FUNCTION public._pgr_withpointsvia(text, text, anyarray, boolean, boolean, boolean, character, boolean, OUT seq integer, OUT path_id integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision, OUT route_agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
 STRICT
AS '$libdir/libpgrouting-3.8', $function$_pgr_withpointsvia$function$
;

-- Function: _pgr_withpointsvia
-- Comment: pgRouting internal function
-- 
CREATE OR REPLACE FUNCTION public._postgis_deprecate(oldname text, newname text, version text)
 RETURNS void
 LANGUAGE plpgsql
 IMMUTABLE STRICT COST 250
AS $function$
DECLARE
  curver_text text;
BEGIN
	curver_text := '3.5.3';
	IF pg_catalog.split_part(curver_text,'.',1)::int > pg_catalog.split_part(version,'.',1)::int OR
	   ( pg_catalog.split_part(curver_text,'.',1) = pg_catalog.split_part(version,'.',1) AND
		 pg_catalog.split_part(curver_text,'.',2) != split_part(version,'.',2) )
	THEN
	  RAISE WARNING '% signature was deprecated in %. Please use %', oldname, version, newname;
	ELSE
	  RAISE DEBUG '% signature was deprecated in %. Please use %', oldname, version, newname;
	END IF;
END;
$function$
;

CREATE OR REPLACE FUNCTION public._postgis_index_extent(tbl regclass, col text)
 RETURNS box2d
 LANGUAGE c
 STABLE STRICT
AS '$libdir/postgis-3', $function$_postgis_gserialized_index_extent$function$
;

CREATE OR REPLACE FUNCTION public._postgis_join_selectivity(regclass, text, regclass, text, text DEFAULT '2'::text)
 RETURNS double precision
 LANGUAGE c
 PARALLEL SAFE STRICT
AS '$libdir/postgis-3', $function$_postgis_gserialized_joinsel$function$
;

CREATE OR REPLACE FUNCTION public._postgis_pgsql_version()
 RETURNS text
 LANGUAGE sql
 STABLE
AS $function$
	SELECT CASE WHEN pg_catalog.split_part(s,'.',1)::integer > 9 THEN pg_catalog.split_part(s,'.',1) || '0'
	ELSE pg_catalog.split_part(s,'.', 1) || pg_catalog.split_part(s,'.', 2) END AS v
	FROM pg_catalog.substring(version(), E'PostgreSQL ([0-9\\.]+)') AS s;
$function$
;

CREATE OR REPLACE FUNCTION public._postgis_scripts_pgsql_version()
 RETURNS text
 LANGUAGE sql
 IMMUTABLE
AS $function$SELECT '140'::text AS version$function$
;

CREATE OR REPLACE FUNCTION public._postgis_selectivity(tbl regclass, att_name text, geom geometry, mode text DEFAULT '2'::text)
 RETURNS double precision
 LANGUAGE c
 PARALLEL SAFE STRICT
AS '$libdir/postgis-3', $function$_postgis_gserialized_sel$function$
;

CREATE OR REPLACE FUNCTION public._postgis_stats(tbl regclass, att_name text, text DEFAULT '2'::text)
 RETURNS text
 LANGUAGE c
 PARALLEL SAFE STRICT
AS '$libdir/postgis-3', $function$_postgis_gserialized_stats$function$
;

CREATE OR REPLACE FUNCTION public._raster_constraint_info_alignment(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE sql
 STABLE STRICT
AS $function$
	SELECT
		TRUE
	FROM pg_class c, pg_namespace n, pg_attribute a
		, (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
			FROM pg_constraint) AS s
	WHERE n.nspname = $1
		AND c.relname = $2
		AND a.attname = $3
		AND a.attrelid = c.oid
		AND s.connamespace = n.oid
		AND s.conrelid = c.oid
		AND a.attnum = ANY (s.conkey)
		AND s.consrc LIKE '%st_samealignment(%';
	$function$
;

CREATE OR REPLACE FUNCTION public._raster_constraint_info_blocksize(rastschema name, rasttable name, rastcolumn name, axis text)
 RETURNS integer
 LANGUAGE sql
 STABLE STRICT
AS $function$
	SELECT
		CASE
			WHEN strpos(s.consrc, 'ANY (ARRAY[') > 0 THEN
				split_part((substring(s.consrc FROM E'ARRAY\\[(.*?){1}\\]')), ',', 1)::integer
			ELSE
				regexp_replace(
					split_part(s.consrc, '= ', 2),
					E'[\\(\\)]', '', 'g'
				)::integer
			END
	FROM pg_class c, pg_namespace n, pg_attribute a
		, (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
			FROM pg_constraint) AS s
	WHERE n.nspname = $1
		AND c.relname = $2
		AND a.attname = $3
		AND a.attrelid = c.oid
		AND s.connamespace = n.oid
		AND s.conrelid = c.oid
		AND a.attnum = ANY (s.conkey)
		AND s.consrc LIKE '%st_' || $4 || '(%= %';
	$function$
;

CREATE OR REPLACE FUNCTION public._raster_constraint_info_coverage_tile(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE sql
 STABLE STRICT
AS $function$
	SELECT
		TRUE
	FROM pg_class c, pg_namespace n, pg_attribute a
			, (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
			FROM pg_constraint) AS s
	WHERE n.nspname = $1
		AND c.relname = $2
		AND a.attname = $3
		AND a.attrelid = c.oid
		AND s.connamespace = n.oid
		AND s.conrelid = c.oid
		AND a.attnum = ANY (s.conkey)
		AND s.consrc LIKE '%st_iscoveragetile(%';
	$function$
;

CREATE OR REPLACE FUNCTION public._raster_constraint_info_extent(rastschema name, rasttable name, rastcolumn name)
 RETURNS geometry
 LANGUAGE sql
 STABLE STRICT
AS $function$
	SELECT
		trim(both '''' from split_part(trim(split_part(s.consrc, ' @ ', 2)), '::', 1))::geometry
	FROM pg_class c, pg_namespace n, pg_attribute a
		, (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
			FROM pg_constraint) AS s
	WHERE n.nspname = $1
		AND c.relname = $2
		AND a.attname = $3
		AND a.attrelid = c.oid
		AND s.connamespace = n.oid
		AND s.conrelid = c.oid
		AND a.attnum = ANY (s.conkey)
		AND s.consrc LIKE '%st_envelope(% @ %';
	$function$
;

CREATE OR REPLACE FUNCTION public._raster_constraint_info_index(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE sql
 STABLE STRICT
AS $function$
		SELECT
			TRUE
		FROM pg_catalog.pg_class c
		JOIN pg_catalog.pg_index i
			ON i.indexrelid = c.oid
		JOIN pg_catalog.pg_class c2
			ON i.indrelid = c2.oid
		JOIN pg_catalog.pg_namespace n
			ON n.oid = c.relnamespace
		JOIN pg_am am
			ON c.relam = am.oid
		JOIN pg_attribute att
			ON att.attrelid = c2.oid
				AND pg_catalog.format_type(att.atttypid, att.atttypmod) = 'raster'
		WHERE c.relkind IN ('i')
			AND n.nspname = $1
			AND c2.relname = $2
			AND att.attname = $3
			AND am.amname = 'gist'
			AND strpos(pg_catalog.pg_get_expr(i.indexprs, i.indrelid), att.attname) > 0;
	$function$
;

CREATE OR REPLACE FUNCTION public._raster_constraint_info_nodata_values(rastschema name, rasttable name, rastcolumn name)
 RETURNS double precision[]
 LANGUAGE sql
 STABLE STRICT
AS $function$
	SELECT
		trim(both '''' from
			split_part(
				regexp_replace(
					split_part(s.consrc, ' = ', 2),
					E'[\\(\\)]', '', 'g'
				),
				'::', 1
			)
		)::double precision[]
	FROM pg_class c, pg_namespace n, pg_attribute a
		, (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
			FROM pg_constraint) AS s
	WHERE n.nspname = $1
		AND c.relname = $2
		AND a.attname = $3
		AND a.attrelid = c.oid
		AND s.connamespace = n.oid
		AND s.conrelid = c.oid
		AND a.attnum = ANY (s.conkey)
		AND s.consrc LIKE '%_raster_constraint_nodata_values(%';
	$function$
;

CREATE OR REPLACE FUNCTION public._raster_constraint_info_num_bands(rastschema name, rasttable name, rastcolumn name)
 RETURNS integer
 LANGUAGE sql
 STABLE STRICT
AS $function$
	SELECT
		regexp_replace(
			split_part(s.consrc, ' = ', 2),
			E'[\\(\\)]', '', 'g'
		)::integer
	FROM pg_class c, pg_namespace n, pg_attribute a
		, (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
			FROM pg_constraint) AS s
	WHERE n.nspname = $1
		AND c.relname = $2
		AND a.attname = $3
		AND a.attrelid = c.oid
		AND s.connamespace = n.oid
		AND s.conrelid = c.oid
		AND a.attnum = ANY (s.conkey)
		AND s.consrc LIKE '%st_numbands(%';
	$function$
;

CREATE OR REPLACE FUNCTION public._raster_constraint_info_out_db(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean[]
 LANGUAGE sql
 STABLE STRICT
AS $function$
	SELECT
		trim(
			both '''' from split_part(
				regexp_replace(
					split_part(s.consrc, ' = ', 2),
					E'[\\(\\)]', '', 'g'
				),
				'::', 1
			)
		)::boolean[]
	FROM pg_class c, pg_namespace n, pg_attribute a
			, (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
			FROM pg_constraint) AS s
	WHERE n.nspname = $1
		AND c.relname = $2
		AND a.attname = $3
		AND a.attrelid = c.oid
		AND s.connamespace = n.oid
		AND s.conrelid = c.oid
		AND a.attnum = ANY (s.conkey)
		AND s.consrc LIKE '%_raster_constraint_out_db(%';
	$function$
;

CREATE OR REPLACE FUNCTION public._raster_constraint_info_pixel_types(rastschema name, rasttable name, rastcolumn name)
 RETURNS text[]
 LANGUAGE sql
 STABLE STRICT
AS $function$
	SELECT
		trim(
			both '''' from split_part(
				regexp_replace(
					split_part(s.consrc, ' = ', 2),
					E'[\\(\\)]', '', 'g'
				),
				'::', 1
			)
		)::text[]
	FROM pg_class c, pg_namespace n, pg_attribute a
		, (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
			FROM pg_constraint) AS s
	WHERE n.nspname = $1
		AND c.relname = $2
		AND a.attname = $3
		AND a.attrelid = c.oid
		AND s.connamespace = n.oid
		AND s.conrelid = c.oid
		AND a.attnum = ANY (s.conkey)
		AND s.consrc LIKE '%_raster_constraint_pixel_types(%';
	$function$
;

CREATE OR REPLACE FUNCTION public._raster_constraint_info_regular_blocking(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE plpgsql
 STABLE STRICT
AS $function$
	DECLARE
		covtile boolean;
		spunique boolean;
	BEGIN
		covtile := COALESCE( public._raster_constraint_info_coverage_tile($1, $2, $3), FALSE);
		spunique := COALESCE( public._raster_constraint_info_spatially_unique($1, $2, $3), FALSE);
		RETURN (covtile AND spunique);
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._raster_constraint_info_scale(rastschema name, rasttable name, rastcolumn name, axis character)
 RETURNS double precision
 LANGUAGE sql
 STABLE STRICT
AS $function$
	WITH c AS (SELECT
		regexp_replace(
			replace(
				split_part(
					split_part(s.consrc, ' = ', 2),
					'::', 1
				),
				'round(', ''
			),
			E'[ ''''\\(\\)]', '', 'g'
		)::text AS val
	FROM pg_class c, pg_namespace n, pg_attribute a
		, (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
		    FROM pg_constraint) AS s
	WHERE n.nspname = $1
		AND c.relname = $2
		AND a.attname = $3
		AND a.attrelid = c.oid
		AND s.connamespace = n.oid
		AND s.conrelid = c.oid
		AND a.attnum = ANY (s.conkey)
		AND s.consrc LIKE '%st_scale' || $4 || '(% = %')
   SELECT CASE WHEN split_part(c.val,',', 2) > ''
        THEN round( split_part(c.val, ',',1)::numeric, split_part(c.val,',',2)::integer )::float8
        ELSE c.val::float8 END
        FROM c;
	$function$
;

CREATE OR REPLACE FUNCTION public._raster_constraint_info_spatially_unique(rastschema name, rasttable name, rastcolumn name)
 RETURNS boolean
 LANGUAGE sql
 STABLE STRICT
AS $function$
	SELECT
		TRUE
	FROM pg_class c, pg_namespace n, pg_attribute a
		, (SELECT connamespace, conrelid, conindid, conkey, contype, conexclop, pg_get_constraintdef(oid) As consrc
			FROM pg_constraint) AS s
		, pg_index idx, pg_operator op
	WHERE n.nspname = $1
		AND c.relname = $2
		AND a.attname = $3
		AND a.attrelid = c.oid
		AND s.connamespace = n.oid
		AND s.conrelid = c.oid
		AND s.contype = 'x'
		AND 0::smallint = ANY (s.conkey)
		AND idx.indexrelid = s.conindid
		AND pg_get_indexdef(idx.indexrelid, 1, true) LIKE '(' || quote_ident($3) || '::geometry)'
		AND s.conexclop[1] = op.oid
		AND op.oprname = '=';
	$function$
;

CREATE OR REPLACE FUNCTION public._raster_constraint_info_srid(rastschema name, rasttable name, rastcolumn name)
 RETURNS integer
 LANGUAGE sql
 STABLE STRICT
AS $function$
	SELECT
		regexp_replace(
			split_part(s.consrc, ' = ', 2),
			E'[\\(\\)]', '', 'g'
		)::integer
	FROM pg_class c, pg_namespace n, pg_attribute a
		, (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
		    FROM pg_constraint) AS s
	WHERE n.nspname = $1
		AND c.relname = $2
		AND a.attname = $3
		AND a.attrelid = c.oid
		AND s.connamespace = n.oid
		AND s.conrelid = c.oid
		AND a.attnum = ANY (s.conkey)
		AND s.consrc LIKE '%st_srid(% = %';
	$function$
;

CREATE OR REPLACE FUNCTION public._raster_constraint_nodata_values(rast raster)
 RETURNS numeric[]
 LANGUAGE sql
 IMMUTABLE PARALLEL SAFE STRICT
AS $function$ SELECT pg_catalog.array_agg(pg_catalog.round(nodatavalue::numeric, 10))::numeric[] FROM public.ST_BandMetaData($1, ARRAY[]::int[]); $function$
;

CREATE OR REPLACE FUNCTION public._raster_constraint_out_db(rast raster)
 RETURNS boolean[]
 LANGUAGE sql
 IMMUTABLE PARALLEL SAFE STRICT
AS $function$ SELECT pg_catalog.array_agg(isoutdb)::boolean[] FROM public.ST_BandMetaData($1, ARRAY[]::int[]); $function$
;

CREATE OR REPLACE FUNCTION public._raster_constraint_pixel_types(rast raster)
 RETURNS text[]
 LANGUAGE sql
 STABLE STRICT
AS $function$ SELECT pg_catalog.array_agg(pixeltype)::text[] FROM  public.ST_BandMetaData($1, ARRAY[]::int[]); $function$
;

CREATE OR REPLACE FUNCTION public._st_3ddfullywithin(geom1 geometry, geom2 geometry, double precision)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
AS '$libdir/postgis-3', $function$LWGEOM_dfullywithin3d$function$
;

CREATE OR REPLACE FUNCTION public._st_3ddwithin(geom1 geometry, geom2 geometry, double precision)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
AS '$libdir/postgis-3', $function$LWGEOM_dwithin3d$function$
;

CREATE OR REPLACE FUNCTION public._st_3dintersects(geom1 geometry, geom2 geometry)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
AS '$libdir/postgis-3', $function$ST_3DIntersects$function$
;

CREATE OR REPLACE FUNCTION public._st_asgml(integer, geometry, integer, integer, text, text)
 RETURNS text
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE COST 250
AS '$libdir/postgis-3', $function$LWGEOM_asGML$function$
;

CREATE OR REPLACE FUNCTION public._st_aspect4ma(value double precision[], pos integer[], VARIADIC userargs text[] DEFAULT NULL::text[])
 RETURNS double precision
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE
AS $function$
	DECLARE
		x integer;
		y integer;
		z integer;
		_width double precision;
		_height double precision;
		_units text;
		dz_dx double precision;
		dz_dy double precision;
		aspect double precision;
		halfpi double precision;
		_value double precision[][][];
		ndims int;
	BEGIN
		ndims := array_ndims(value);
		IF ndims = 2 THEN
			_value := public._ST_convertarray4ma(value);
		ELSEIF ndims != 3 THEN
			RAISE EXCEPTION 'First parameter of function must be a 3-dimension array';
		ELSE
			_value := value;
		END IF;
		IF (
			array_lower(_value, 2) != 1 OR array_upper(_value, 2) != 3 OR
			array_lower(_value, 3) != 1 OR array_upper(_value, 3) != 3
		) THEN
			RAISE EXCEPTION 'First parameter of function must be a 1x3x3 array with each of the lower bounds starting from 1';
		END IF;
		IF array_length(userargs, 1) < 3 THEN
			RAISE EXCEPTION 'At least three elements must be provided for the third parameter';
		END IF;
		IF array_length(_value, 1) > 1 THEN
			RAISE NOTICE 'Only using the values from the first raster';
		END IF;
		z := array_lower(_value, 1);
		_width := userargs[1]::double precision;
		_height := userargs[2]::double precision;
		_units := userargs[3];
		IF _value[z][2][2] IS NULL THEN
			RETURN NULL;
		ELSE
			FOR y IN 1..3 LOOP
				FOR x IN 1..3 LOOP
					IF _value[z][y][x] IS NULL THEN
						_value[z][y][x] = _value[z][2][2];
					END IF;
				END LOOP;
			END LOOP;
		END IF;
		dz_dy := ((_value[z][3][1] + _value[z][3][2] + _value[z][3][2] + _value[z][3][3]) -
			(_value[z][1][1] + _value[z][1][2] + _value[z][1][2] + _value[z][1][3]));
		dz_dx := ((_value[z][1][3] + _value[z][2][3] + _value[z][2][3] + _value[z][3][3]) -
			(_value[z][1][1] + _value[z][2][1] + _value[z][2][1] + _value[z][3][1]));
		IF abs(dz_dx) = 0::double precision AND abs(dz_dy) = 0::double precision THEN
			RETURN -1;
		END IF;
		aspect := atan2(dz_dy, -dz_dx);
		halfpi := pi() / 2.0;
		IF aspect > halfpi THEN
			aspect := (5.0 * halfpi) - aspect;
		ELSE
			aspect := halfpi - aspect;
		END IF;
		IF aspect = 2 * pi() THEN
			aspect := 0.;
		END IF;
		CASE substring(upper(trim(leading from _units)) for 3)
			WHEN 'rad' THEN
				RETURN aspect;
			ELSE
				RETURN degrees(aspect);
		END CASE;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._st_asraster(geom geometry, scalex double precision DEFAULT 0, scaley double precision DEFAULT 0, width integer DEFAULT 0, height integer DEFAULT 0, pixeltype text[] DEFAULT ARRAY['8BUI'::text], value double precision[] DEFAULT ARRAY[(1)::double precision], nodataval double precision[] DEFAULT ARRAY[(0)::double precision], upperleftx double precision DEFAULT NULL::double precision, upperlefty double precision DEFAULT NULL::double precision, gridx double precision DEFAULT NULL::double precision, gridy double precision DEFAULT NULL::double precision, skewx double precision DEFAULT 0, skewy double precision DEFAULT 0, touched boolean DEFAULT false)
 RETURNS raster
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_asRaster$function$
;

CREATE OR REPLACE FUNCTION public._st_asx3d(integer, geometry, integer, integer, text)
 RETURNS text
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE COST 250
AS '$libdir/postgis-3', $function$LWGEOM_asX3D$function$
;

CREATE OR REPLACE FUNCTION public._st_bestsrid(geography, geography)
 RETURNS integer
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 50
AS '$libdir/postgis-3', $function$geography_bestsrid$function$
;

CREATE OR REPLACE FUNCTION public._st_bestsrid(geography)
 RETURNS integer
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 50
AS '$libdir/postgis-3', $function$geography_bestsrid$function$
;

CREATE OR REPLACE FUNCTION public._st_clip(rast raster, nband integer[], geom geometry, nodataval double precision[] DEFAULT NULL::double precision[], crop boolean DEFAULT true, touched boolean DEFAULT false)
 RETURNS raster
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_clip$function$
;

CREATE OR REPLACE FUNCTION public._st_colormap(rast raster, nband integer, colormap text, method text DEFAULT 'INTERPOLATE'::text)
 RETURNS raster
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/postgis_raster-3', $function$RASTER_colorMap$function$
;

CREATE OR REPLACE FUNCTION public._st_contains(rast1 raster, nband1 integer, rast2 raster, nband2 integer)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE STRICT COST 1000
AS '$libdir/postgis_raster-3', $function$RASTER_contains$function$
;

CREATE OR REPLACE FUNCTION public._st_contains(geom1 geometry, geom2 geometry)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
AS '$libdir/postgis-3', $function$contains$function$
;

CREATE OR REPLACE FUNCTION public._st_containsproperly(geom1 geometry, geom2 geometry)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
AS '$libdir/postgis-3', $function$containsproperly$function$
;

CREATE OR REPLACE FUNCTION public._st_containsproperly(rast1 raster, nband1 integer, rast2 raster, nband2 integer)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 1000
AS '$libdir/postgis_raster-3', $function$RASTER_containsProperly$function$
;

CREATE OR REPLACE FUNCTION public._st_convertarray4ma(value double precision[])
 RETURNS double precision[]
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE STRICT
AS $function$
	DECLARE
		_value double precision[][][];
		x int;
		y int;
	BEGIN
		IF array_ndims(value) != 2 THEN
			RAISE EXCEPTION 'Function parameter must be a 2-dimension array';
		END IF;
		_value := array_fill(NULL::double precision, ARRAY[1, array_length(value, 1), array_length(value, 2)]::int[], ARRAY[1, array_lower(value, 1), array_lower(value, 2)]::int[]);
		FOR y IN array_lower(value, 1)..array_upper(value, 1) LOOP
			FOR x IN array_lower(value, 2)..array_upper(value, 2) LOOP
				_value[1][y][x] = value[y][x];
			END LOOP;
		END LOOP;
		RETURN _value;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._st_count(rast raster, nband integer DEFAULT 1, exclude_nodata_value boolean DEFAULT true, sample_percent double precision DEFAULT 1)
 RETURNS bigint
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE STRICT
AS $function$
	DECLARE
		rtn bigint;
	BEGIN
		IF exclude_nodata_value IS FALSE THEN
			SELECT width * height INTO rtn FROM public.ST_Metadata(rast);
		ELSE
			SELECT count INTO rtn FROM public._ST_summarystats($1, $2, $3, $4);
		END IF;
		RETURN rtn;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._st_countagg_finalfn(agg agg_count)
 RETURNS bigint
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE
AS $function$
	BEGIN
		IF agg IS NULL THEN
			RAISE EXCEPTION 'Cannot count coverage';
		END IF;
		RETURN agg.count;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._st_countagg_transfn(agg agg_count, rast raster, exclude_nodata_value boolean)
 RETURNS agg_count
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE
AS $function$
	DECLARE
		rtn_agg agg_count;
	BEGIN
		rtn_agg :=  public.__ST_countagg_transfn(
			agg,
			rast,
			1, exclude_nodata_value,
			1
		);
		RETURN rtn_agg;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._st_countagg_transfn(agg agg_count, rast raster, nband integer, exclude_nodata_value boolean)
 RETURNS agg_count
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE
AS $function$
	DECLARE
		rtn_agg agg_count;
	BEGIN
		rtn_agg :=  public.__ST_countagg_transfn(
			agg,
			rast,
			nband, exclude_nodata_value,
			1
		);
		RETURN rtn_agg;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._st_countagg_transfn(agg agg_count, rast raster, nband integer, exclude_nodata_value boolean, sample_percent double precision)
 RETURNS agg_count
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE
AS $function$
	DECLARE
		rtn_agg agg_count;
	BEGIN
		rtn_agg :=  public.__st_countagg_transfn(
			agg,
			rast,
			nband, exclude_nodata_value,
			sample_percent
		);
		RETURN rtn_agg;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._st_coveredby(geom1 geometry, geom2 geometry)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
AS '$libdir/postgis-3', $function$coveredby$function$
;

CREATE OR REPLACE FUNCTION public._st_coveredby(geog1 geography, geog2 geography)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
AS '$libdir/postgis-3', $function$geography_coveredby$function$
;

CREATE OR REPLACE FUNCTION public._st_coveredby(rast1 raster, nband1 integer, rast2 raster, nband2 integer)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 1000
AS '$libdir/postgis_raster-3', $function$RASTER_coveredby$function$
;

CREATE OR REPLACE FUNCTION public._st_covers(geom1 geometry, geom2 geometry)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
AS '$libdir/postgis-3', $function$covers$function$
;

CREATE OR REPLACE FUNCTION public._st_covers(geog1 geography, geog2 geography)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
AS '$libdir/postgis-3', $function$geography_covers$function$
;

CREATE OR REPLACE FUNCTION public._st_covers(rast1 raster, nband1 integer, rast2 raster, nband2 integer)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 1000
AS '$libdir/postgis_raster-3', $function$RASTER_covers$function$
;

CREATE OR REPLACE FUNCTION public._st_crosses(geom1 geometry, geom2 geometry)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
AS '$libdir/postgis-3', $function$crosses$function$
;

CREATE OR REPLACE FUNCTION public._st_dfullywithin(rast1 raster, nband1 integer, rast2 raster, nband2 integer, distance double precision)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 1000
AS '$libdir/postgis_raster-3', $function$RASTER_dfullywithin$function$
;

CREATE OR REPLACE FUNCTION public._st_dfullywithin(geom1 geometry, geom2 geometry, double precision)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
AS '$libdir/postgis-3', $function$LWGEOM_dfullywithin$function$
;

CREATE OR REPLACE FUNCTION public._st_distancetree(geography, geography, double precision, boolean)
 RETURNS double precision
 LANGUAGE c
 IMMUTABLE STRICT COST 5000
AS '$libdir/postgis-3', $function$geography_distance_tree$function$
;

CREATE OR REPLACE FUNCTION public._st_distancetree(geography, geography)
 RETURNS double precision
 LANGUAGE sql
 IMMUTABLE STRICT
AS $function$SELECT public._ST_DistanceTree($1, $2, 0.0, true)$function$
;

CREATE OR REPLACE FUNCTION public._st_distanceuncached(geography, geography, double precision, boolean)
 RETURNS double precision
 LANGUAGE c
 IMMUTABLE STRICT COST 5000
AS '$libdir/postgis-3', $function$geography_distance_uncached$function$
;

CREATE OR REPLACE FUNCTION public._st_distanceuncached(geography, geography, boolean)
 RETURNS double precision
 LANGUAGE sql
 IMMUTABLE STRICT
AS $function$SELECT public._ST_DistanceUnCached($1, $2, 0.0, $3)$function$
;

CREATE OR REPLACE FUNCTION public._st_distanceuncached(geography, geography)
 RETURNS double precision
 LANGUAGE sql
 IMMUTABLE STRICT
AS $function$SELECT public._ST_DistanceUnCached($1, $2, 0.0, true)$function$
;

CREATE OR REPLACE FUNCTION public._st_dwithin(rast1 raster, nband1 integer, rast2 raster, nband2 integer, distance double precision)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 1000
AS '$libdir/postgis_raster-3', $function$RASTER_dwithin$function$
;

CREATE OR REPLACE FUNCTION public._st_dwithin(geog1 geography, geog2 geography, tolerance double precision, use_spheroid boolean DEFAULT true)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
AS '$libdir/postgis-3', $function$geography_dwithin$function$
;

CREATE OR REPLACE FUNCTION public._st_dwithin(geom1 geometry, geom2 geometry, double precision)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
AS '$libdir/postgis-3', $function$LWGEOM_dwithin$function$
;

CREATE OR REPLACE FUNCTION public._st_dwithinuncached(geography, geography, double precision, boolean)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE STRICT COST 5000
AS '$libdir/postgis-3', $function$geography_dwithin_uncached$function$
;

CREATE OR REPLACE FUNCTION public._st_dwithinuncached(geography, geography, double precision)
 RETURNS boolean
 LANGUAGE sql
 IMMUTABLE
AS $function$SELECT $1 OPERATOR(public.&&) public._ST_Expand($2,$3) AND $2 OPERATOR(public.&&) public._ST_Expand($1,$3) AND public._ST_DWithinUnCached($1, $2, $3, true)$function$
;

CREATE OR REPLACE FUNCTION public._st_equals(geom1 geometry, geom2 geometry)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
AS '$libdir/postgis-3', $function$ST_Equals$function$
;

CREATE OR REPLACE FUNCTION public._st_expand(geography, double precision)
 RETURNS geography
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 50
AS '$libdir/postgis-3', $function$geography_expand$function$
;

CREATE OR REPLACE FUNCTION public._st_gdalwarp(rast raster, algorithm text DEFAULT 'NearestNeighbour'::text, maxerr double precision DEFAULT 0.125, srid integer DEFAULT NULL::integer, scalex double precision DEFAULT 0, scaley double precision DEFAULT 0, gridx double precision DEFAULT NULL::double precision, gridy double precision DEFAULT NULL::double precision, skewx double precision DEFAULT 0, skewy double precision DEFAULT 0, width integer DEFAULT NULL::integer, height integer DEFAULT NULL::integer)
 RETURNS raster
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_GDALWarp$function$
;

CREATE OR REPLACE FUNCTION public._st_geomfromgml(text, integer)
 RETURNS geometry
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE COST 250
AS '$libdir/postgis-3', $function$geom_from_gml$function$
;

CREATE OR REPLACE FUNCTION public._st_grayscale4ma(value double precision[], pos integer[], VARIADIC userargs text[] DEFAULT NULL::text[])
 RETURNS double precision
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE
AS $function$
	DECLARE
		ndims integer;
		_value double precision[][][];
		red double precision;
		green double precision;
		blue double precision;
		gray double precision;
	BEGIN
		ndims := array_ndims(value);
		IF ndims = 2 THEN
			_value := public._ST_convertarray4ma(value);
		ELSEIF ndims != 3 THEN
			RAISE EXCEPTION 'First parameter of function must be a 3-dimension array';
		ELSE
			_value := value;
		END IF;
		red := _value[1][1][1];
		green := _value[2][1][1];
		blue := _value[3][1][1];
		gray = round(0.2989 * red + 0.5870 * green + 0.1140 * blue);
		RETURN gray;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._st_hillshade4ma(value double precision[], pos integer[], VARIADIC userargs text[] DEFAULT NULL::text[])
 RETURNS double precision
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE
AS $function$
	DECLARE
		_pixwidth double precision;
		_pixheight double precision;
		_width double precision;
		_height double precision;
		_azimuth double precision;
		_altitude double precision;
		_bright double precision;
		_scale double precision;
		dz_dx double precision;
		dz_dy double precision;
		azimuth double precision;
		zenith double precision;
		slope double precision;
		aspect double precision;
		shade double precision;
		_value double precision[][][];
		ndims int;
		z int;
	BEGIN
		ndims := array_ndims(value);
		IF ndims = 2 THEN
			_value := public._ST_convertarray4ma(value);
		ELSEIF ndims != 3 THEN
			RAISE EXCEPTION 'First parameter of function must be a 3-dimension array';
		ELSE
			_value := value;
		END IF;
		IF (
			array_lower(_value, 2) != 1 OR array_upper(_value, 2) != 3 OR
			array_lower(_value, 3) != 1 OR array_upper(_value, 3) != 3
		) THEN
			RAISE EXCEPTION 'First parameter of function must be a 1x3x3 array with each of the lower bounds starting from 1';
		END IF;
		IF array_length(userargs, 1) < 8 THEN
			RAISE EXCEPTION 'At least eight elements must be provided for the third parameter';
		END IF;
		IF array_length(_value, 1) > 1 THEN
			RAISE NOTICE 'Only using the values from the first raster';
		END IF;
		z := array_lower(_value, 1);
		_pixwidth := userargs[1]::double precision;
		_pixheight := userargs[2]::double precision;
		_width := userargs[3]::double precision;
		_height := userargs[4]::double precision;
		_azimuth := userargs[5]::double precision;
		_altitude := userargs[6]::double precision;
		_bright := userargs[7]::double precision;
		_scale := userargs[8]::double precision;
		IF (pos[1][1] = 1 OR pos[1][2] = 1) OR (pos[1][1] = _width OR pos[1][2] = _height) THEN
			RETURN NULL;
		END IF;
		IF _azimuth < 0. THEN
			RAISE NOTICE 'Clamping provided azimuth value % to 0', _azimuth;
			_azimuth := 0.;
		ELSEIF _azimuth >= 360. THEN
			RAISE NOTICE 'Converting provided azimuth value % to be between 0 and 360', _azimuth;
			_azimuth := _azimuth - (360. * floor(_azimuth / 360.));
		END IF;
		azimuth := 360. - _azimuth + 90.;
		IF azimuth >= 360. THEN
			azimuth := azimuth - 360.;
		END IF;
		azimuth := radians(azimuth);
		IF _altitude < 0. THEN
			RAISE NOTICE 'Clamping provided altitude value % to 0', _altitude;
			_altitude := 0.;
		ELSEIF _altitude > 90. THEN
			RAISE NOTICE 'Clamping provided altitude value % to 90', _altitude;
			_altitude := 90.;
		END IF;
		zenith := radians(90. - _altitude);
		IF _bright < 0. THEN
			RAISE NOTICE 'Clamping provided bright value % to 0', _bright;
			_bright := 0.;
		ELSEIF _bright > 255. THEN
			RAISE NOTICE 'Clamping provided bright value % to 255', _bright;
			_bright := 255.;
		END IF;
		dz_dy := ((_value[z][3][1] + _value[z][3][2] + _value[z][3][2] + _value[z][3][3]) -
			(_value[z][1][1] + _value[z][1][2] + _value[z][1][2] + _value[z][1][3])) / (8 * _pixheight);
		dz_dx := ((_value[z][1][3] + _value[z][2][3] + _value[z][2][3] + _value[z][3][3]) -
			(_value[z][1][1] + _value[z][2][1] + _value[z][2][1] + _value[z][3][1])) / (8 * _pixwidth);
		slope := atan(sqrt(dz_dx * dz_dx + dz_dy * dz_dy) / _scale);
		IF dz_dx != 0. THEN
			aspect := atan2(dz_dy, -dz_dx);
			IF aspect < 0. THEN
				aspect := aspect + (2.0 * pi());
			END IF;
		ELSE
			IF dz_dy > 0. THEN
				aspect := pi() / 2.;
			ELSEIF dz_dy < 0. THEN
				aspect := (2. * pi()) - (pi() / 2.);
			ELSE
				aspect := pi();
			END IF;
		END IF;
		shade := _bright * ((cos(zenith) * cos(slope)) + (sin(zenith) * sin(slope) * cos(azimuth - aspect)));
		IF shade < 0. THEN
			shade := 0;
		END IF;
		RETURN shade;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._st_histogram(rast raster, nband integer DEFAULT 1, exclude_nodata_value boolean DEFAULT true, sample_percent double precision DEFAULT 1, bins integer DEFAULT 0, width double precision[] DEFAULT NULL::double precision[], "right" boolean DEFAULT false, min double precision DEFAULT NULL::double precision, max double precision DEFAULT NULL::double precision, OUT min double precision, OUT max double precision, OUT count bigint, OUT percent double precision)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_histogram$function$
;

CREATE OR REPLACE FUNCTION public._st_intersects(geom geometry, rast raster, nband integer DEFAULT NULL::integer)
 RETURNS boolean
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE COST 1000
AS $function$
	DECLARE
		hasnodata boolean := TRUE;
		_geom public.geometry;
	BEGIN
		IF public.ST_SRID(rast) != public.ST_SRID(geom) THEN
			RAISE EXCEPTION 'Raster and geometry do not have the same SRID';
		END IF;
		_geom := public.ST_ConvexHull(rast);
		IF nband IS NOT NULL THEN
			SELECT CASE WHEN bmd.nodatavalue IS NULL THEN FALSE ELSE NULL END INTO hasnodata FROM public.ST_BandMetaData(rast, nband) AS bmd;
		END IF;
		IF public.ST_Intersects(geom, _geom) IS NOT TRUE THEN
			RETURN FALSE;
		ELSEIF nband IS NULL OR hasnodata IS FALSE THEN
			RETURN TRUE;
		END IF;
		SELECT public.ST_Buffer(public.ST_Collect(t.geom), 0) INTO _geom FROM public.ST_PixelAsPolygons(rast, nband) AS t;
		RETURN public.ST_Intersects(geom, _geom);
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._st_intersects(rast1 raster, nband1 integer, rast2 raster, nband2 integer)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 1000
AS '$libdir/postgis_raster-3', $function$RASTER_intersects$function$
;

CREATE OR REPLACE FUNCTION public._st_intersects(geom1 geometry, geom2 geometry)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
AS '$libdir/postgis-3', $function$ST_Intersects$function$
;

CREATE OR REPLACE FUNCTION public._st_linecrossingdirection(line1 geometry, line2 geometry)
 RETURNS integer
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
AS '$libdir/postgis-3', $function$ST_LineCrossingDirection$function$
;

CREATE OR REPLACE FUNCTION public._st_longestline(geom1 geometry, geom2 geometry)
 RETURNS geometry
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 250
AS '$libdir/postgis-3', $function$LWGEOM_longestline2d$function$
;

CREATE OR REPLACE FUNCTION public._st_mapalgebra(rastbandargset rastbandarg[], expression text, pixeltype text DEFAULT NULL::text, extenttype text DEFAULT 'INTERSECTION'::text, nodata1expr text DEFAULT NULL::text, nodata2expr text DEFAULT NULL::text, nodatanodataval double precision DEFAULT NULL::double precision)
 RETURNS raster
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_nMapAlgebraExpr$function$
;

CREATE OR REPLACE FUNCTION public._st_mapalgebra(rastbandargset rastbandarg[], callbackfunc regprocedure, pixeltype text DEFAULT NULL::text, distancex integer DEFAULT 0, distancey integer DEFAULT 0, extenttype text DEFAULT 'INTERSECTION'::text, customextent raster DEFAULT NULL::raster, mask double precision[] DEFAULT NULL::double precision[], weighted boolean DEFAULT NULL::boolean, VARIADIC userargs text[] DEFAULT NULL::text[])
 RETURNS raster
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_nMapAlgebra$function$
;

CREATE OR REPLACE FUNCTION public._st_maxdistance(geom1 geometry, geom2 geometry)
 RETURNS double precision
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 250
AS '$libdir/postgis-3', $function$LWGEOM_maxdistance2d_linestring$function$
;

CREATE OR REPLACE FUNCTION public._st_neighborhood(rast raster, band integer, columnx integer, rowy integer, distancex integer, distancey integer, exclude_nodata_value boolean DEFAULT true)
 RETURNS double precision[]
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/postgis_raster-3', $function$RASTER_neighborhood$function$
;

CREATE OR REPLACE FUNCTION public._st_orderingequals(geom1 geometry, geom2 geometry)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
AS '$libdir/postgis-3', $function$LWGEOM_same$function$
;

CREATE OR REPLACE FUNCTION public._st_overlaps(rast1 raster, nband1 integer, rast2 raster, nband2 integer)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE STRICT COST 1000
AS '$libdir/postgis_raster-3', $function$RASTER_overlaps$function$
;

CREATE OR REPLACE FUNCTION public._st_overlaps(geom1 geometry, geom2 geometry)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
AS '$libdir/postgis-3', $function$overlaps$function$
;

CREATE OR REPLACE FUNCTION public._st_pixelascentroids(rast raster, band integer DEFAULT 1, columnx integer DEFAULT NULL::integer, rowy integer DEFAULT NULL::integer, exclude_nodata_value boolean DEFAULT true)
 RETURNS TABLE(geom geometry, val double precision, x integer, y integer)
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_getPixelCentroids$function$
;

CREATE OR REPLACE FUNCTION public._st_pixelaspolygons(rast raster, band integer DEFAULT 1, columnx integer DEFAULT NULL::integer, rowy integer DEFAULT NULL::integer, exclude_nodata_value boolean DEFAULT true)
 RETURNS TABLE(geom geometry, val double precision, x integer, y integer)
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_getPixelPolygons$function$
;

CREATE OR REPLACE FUNCTION public._st_pointoutside(geography)
 RETURNS geography
 LANGUAGE c
 IMMUTABLE STRICT
AS '$libdir/postgis-3', $function$geography_point_outside$function$
;

CREATE OR REPLACE FUNCTION public._st_quantile(rast raster, nband integer DEFAULT 1, exclude_nodata_value boolean DEFAULT true, sample_percent double precision DEFAULT 1, quantiles double precision[] DEFAULT NULL::double precision[], OUT quantile double precision, OUT value double precision)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_quantile$function$
;

CREATE OR REPLACE FUNCTION public._st_rastertoworldcoord(rast raster, columnx integer DEFAULT NULL::integer, rowy integer DEFAULT NULL::integer, OUT longitude double precision, OUT latitude double precision)
 RETURNS record
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_rasterToWorldCoord$function$
;

CREATE OR REPLACE FUNCTION public._st_reclass(rast raster, VARIADIC reclassargset reclassarg[])
 RETURNS raster
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/postgis_raster-3', $function$RASTER_reclass$function$
;

CREATE OR REPLACE FUNCTION public._st_roughness4ma(value double precision[], pos integer[], VARIADIC userargs text[] DEFAULT NULL::text[])
 RETURNS double precision
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE
AS $function$
	DECLARE
		x integer;
		y integer;
		z integer;
		minimum double precision;
		maximum double precision;
		_value double precision[][][];
		ndims int;
	BEGIN
		ndims := array_ndims(value);
		IF ndims = 2 THEN
			_value := public._ST_convertarray4ma(value);
		ELSEIF ndims != 3 THEN
			RAISE EXCEPTION 'First parameter of function must be a 3-dimension array';
		ELSE
			_value := value;
		END IF;
		IF array_length(_value, 1) > 1 THEN
			RAISE NOTICE 'Only using the values from the first raster';
		END IF;
		z := array_lower(_value, 1);
		IF (
			array_lower(_value, 2) != 1 OR array_upper(_value, 2) != 3 OR
			array_lower(_value, 3) != 1 OR array_upper(_value, 3) != 3
		) THEN
			RAISE EXCEPTION 'First parameter of function must be a 1x3x3 array with each of the lower bounds starting from 1';
		END IF;
		IF _value[z][2][2] IS NULL THEN
			RETURN NULL;
		ELSE
			FOR y IN 1..3 LOOP
				FOR x IN 1..3 LOOP
					IF _value[z][y][x] IS NULL THEN
						_value[z][y][x] = _value[z][2][2];
					END IF;
				END LOOP;
			END LOOP;
		END IF;
		minimum := _value[z][1][1];
		maximum := _value[z][1][1];
		FOR Y IN 1..3 LOOP
		    FOR X IN 1..3 LOOP
		    	 IF _value[z][y][x] < minimum THEN
			    minimum := _value[z][y][x];
			 ELSIF _value[z][y][x] > maximum THEN
			    maximum := _value[z][y][x];
			 END IF;
		    END LOOP;
		END LOOP;
		RETURN maximum - minimum;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._st_samealignment_finalfn(agg agg_samealignment)
 RETURNS boolean
 LANGUAGE sql
 IMMUTABLE PARALLEL SAFE STRICT
AS $function$ SELECT $1.aligned $function$
;

CREATE OR REPLACE FUNCTION public._st_samealignment_transfn(agg agg_samealignment, rast raster)
 RETURNS agg_samealignment
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE
AS $function$
	DECLARE
		m record;
		aligned boolean;
	BEGIN
		IF agg IS NULL THEN
			agg.refraster := NULL;
			agg.aligned := NULL;
		END IF;
		IF rast IS NULL THEN
			agg.aligned := NULL;
		ELSE
			IF agg.refraster IS NULL THEN
				m := public.ST_Metadata(rast);
				agg.refraster := public.ST_MakeEmptyRaster(1, 1, m.upperleftx, m.upperlefty, m.scalex, m.scaley, m.skewx, m.skewy, m.srid);
				agg.aligned := TRUE;
			ELSIF agg.aligned IS TRUE THEN
				agg.aligned := public.ST_SameAlignment(agg.refraster, rast);
			END IF;
		END IF;
		RETURN agg;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._st_setvalues(rast raster, nband integer, x integer, y integer, newvalueset double precision[], noset boolean[] DEFAULT NULL::boolean[], hasnosetvalue boolean DEFAULT false, nosetvalue double precision DEFAULT NULL::double precision, keepnodata boolean DEFAULT false)
 RETURNS raster
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_setPixelValuesArray$function$
;

CREATE OR REPLACE FUNCTION public._st_slope4ma(value double precision[], pos integer[], VARIADIC userargs text[] DEFAULT NULL::text[])
 RETURNS double precision
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE
AS $function$
	DECLARE
		x integer;
		y integer;
		z integer;
		_pixwidth double precision;
		_pixheight double precision;
		_width double precision;
		_height double precision;
		_units text;
		_scale double precision;
		dz_dx double precision;
		dz_dy double precision;
		slope double precision;
		_value double precision[][][];
		ndims int;
	BEGIN
		ndims := array_ndims(value);
		IF ndims = 2 THEN
			_value := public._ST_convertarray4ma(value);
		ELSEIF ndims != 3 THEN
			RAISE EXCEPTION 'First parameter of function must be a 3-dimension array';
		ELSE
			_value := value;
		END IF;
		IF array_length(_value, 1) > 1 THEN
			RAISE NOTICE 'Only using the values from the first raster';
		END IF;
		z := array_lower(_value, 1);
		IF (
			array_lower(_value, 2) != 1 OR array_upper(_value, 2) != 3 OR
			array_lower(_value, 3) != 1 OR array_upper(_value, 3) != 3
		) THEN
			RAISE EXCEPTION 'First parameter of function must be a 1x3x3 array with each of the lower bounds starting from 1';
		END IF;
		IF array_length(userargs, 1) < 6 THEN
			RAISE EXCEPTION 'At least six elements must be provided for the third parameter';
		END IF;
		_pixwidth := userargs[1]::double precision;
		_pixheight := userargs[2]::double precision;
		_width := userargs[3]::double precision;
		_height := userargs[4]::double precision;
		_units := userargs[5];
		_scale := userargs[6]::double precision;
		IF _value[z][2][2] IS NULL THEN
			RETURN NULL;
		ELSE
			FOR y IN 1..3 LOOP
				FOR x IN 1..3 LOOP
					IF _value[z][y][x] IS NULL THEN
						_value[z][y][x] = _value[z][2][2];
					END IF;
				END LOOP;
			END LOOP;
		END IF;
		dz_dy := ((_value[z][3][1] + _value[z][3][2] + _value[z][3][2] + _value[z][3][3]) -
			(_value[z][1][1] + _value[z][1][2] + _value[z][1][2] + _value[z][1][3])) / _pixheight;
		dz_dx := ((_value[z][1][3] + _value[z][2][3] + _value[z][2][3] + _value[z][3][3]) -
			(_value[z][1][1] + _value[z][2][1] + _value[z][2][1] + _value[z][3][1])) / _pixwidth;
		slope := sqrt(dz_dx * dz_dx + dz_dy * dz_dy) / (8 * _scale);
		CASE substring(upper(trim(leading from _units)) for 3)
			WHEN 'PER' THEN
				slope := 100.0 * slope;
			WHEN 'rad' THEN
				slope := atan(slope);
			ELSE
				slope := degrees(atan(slope));
		END CASE;
		RETURN slope;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._st_sortablehash(geom geometry)
 RETURNS bigint
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 50
AS '$libdir/postgis-3', $function$_ST_SortableHash$function$
;

CREATE OR REPLACE FUNCTION public._st_summarystats(rast raster, nband integer DEFAULT 1, exclude_nodata_value boolean DEFAULT true, sample_percent double precision DEFAULT 1)
 RETURNS summarystats
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_summaryStats$function$
;

CREATE OR REPLACE FUNCTION public._st_summarystats_finalfn(internal)
 RETURNS summarystats
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_summaryStats_finalfn$function$
;

CREATE OR REPLACE FUNCTION public._st_summarystats_transfn(internal, raster, integer, boolean)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_summaryStats_transfn$function$
;

CREATE OR REPLACE FUNCTION public._st_summarystats_transfn(internal, raster, boolean, double precision)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_summaryStats_transfn$function$
;

CREATE OR REPLACE FUNCTION public._st_summarystats_transfn(internal, raster, integer, boolean, double precision)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_summaryStats_transfn$function$
;

CREATE OR REPLACE FUNCTION public._st_tile(rast raster, width integer, height integer, nband integer[] DEFAULT NULL::integer[], padwithnodata boolean DEFAULT false, nodataval double precision DEFAULT NULL::double precision)
 RETURNS SETOF raster
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_tile$function$
;

CREATE OR REPLACE FUNCTION public._st_touches(geom1 geometry, geom2 geometry)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
AS '$libdir/postgis-3', $function$touches$function$
;

CREATE OR REPLACE FUNCTION public._st_touches(rast1 raster, nband1 integer, rast2 raster, nband2 integer)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 1000
AS '$libdir/postgis_raster-3', $function$RASTER_touches$function$
;

CREATE OR REPLACE FUNCTION public._st_tpi4ma(value double precision[], pos integer[], VARIADIC userargs text[] DEFAULT NULL::text[])
 RETURNS double precision
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE
AS $function$
	DECLARE
		x integer;
		y integer;
		z integer;
		Z1 double precision;
		Z2 double precision;
		Z3 double precision;
		Z4 double precision;
		Z5 double precision;
		Z6 double precision;
		Z7 double precision;
		Z8 double precision;
		Z9 double precision;
		tpi double precision;
		mean double precision;
		_value double precision[][][];
		ndims int;
	BEGIN
		ndims := array_ndims(value);
		IF ndims = 2 THEN
			_value := public._ST_convertarray4ma(value);
		ELSEIF ndims != 3 THEN
			RAISE EXCEPTION 'First parameter of function must be a 3-dimension array';
		ELSE
			_value := value;
		END IF;
		IF array_length(_value, 1) > 1 THEN
			RAISE NOTICE 'Only using the values from the first raster';
		END IF;
		z := array_lower(_value, 1);
		IF (
			array_lower(_value, 2) != 1 OR array_upper(_value, 2) != 3 OR
			array_lower(_value, 3) != 1 OR array_upper(_value, 3) != 3
		) THEN
			RAISE EXCEPTION 'First parameter of function must be a 1x3x3 array with each of the lower bounds starting from 1';
		END IF;
		IF _value[z][2][2] IS NULL THEN
			RETURN NULL;
		ELSE
			FOR y IN 1..3 LOOP
				FOR x IN 1..3 LOOP
					IF _value[z][y][x] IS NULL THEN
						_value[z][y][x] = _value[z][2][2];
					END IF;
				END LOOP;
			END LOOP;
		END IF;
		Z1 := _value[z][1][1];
		Z2 := _value[z][2][1];
		Z3 := _value[z][3][1];
		Z4 := _value[z][1][2];
		Z5 := _value[z][2][2];
		Z6 := _value[z][3][2];
		Z7 := _value[z][1][3];
		Z8 := _value[z][2][3];
		Z9 := _value[z][3][3];
		mean := (Z1 + Z2 + Z3 + Z4 + Z6 + Z7 + Z8 + Z9)/8;
		tpi := Z5-mean;
		return tpi;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._st_tri4ma(value double precision[], pos integer[], VARIADIC userargs text[] DEFAULT NULL::text[])
 RETURNS double precision
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE
AS $function$
	DECLARE
		x integer;
		y integer;
		z integer;
		Z1 double precision;
		Z2 double precision;
		Z3 double precision;
		Z4 double precision;
		Z5 double precision;
		Z6 double precision;
		Z7 double precision;
		Z8 double precision;
		Z9 double precision;
		tri double precision;
		_value double precision[][][];
		ndims int;
	BEGIN
		ndims := array_ndims(value);
		IF ndims = 2 THEN
			_value := public._ST_convertarray4ma(value);
		ELSEIF ndims != 3 THEN
			RAISE EXCEPTION 'First parameter of function must be a 3-dimension array';
		ELSE
			_value := value;
		END IF;
		IF array_length(_value, 1) > 1 THEN
			RAISE NOTICE 'Only using the values from the first raster';
		END IF;
		z := array_lower(_value, 1);
		IF (
			array_lower(_value, 2) != 1 OR array_upper(_value, 2) != 3 OR
			array_lower(_value, 3) != 1 OR array_upper(_value, 3) != 3
		) THEN
			RAISE EXCEPTION 'First parameter of function must be a 1x3x3 array with each of the lower bounds starting from 1';
		END IF;
		IF _value[z][2][2] IS NULL THEN
			RETURN NULL;
		ELSE
			FOR y IN 1..3 LOOP
				FOR x IN 1..3 LOOP
					IF _value[z][y][x] IS NULL THEN
						_value[z][y][x] = _value[z][2][2];
					END IF;
				END LOOP;
			END LOOP;
		END IF;
		Z1 := _value[z][1][1];
		Z2 := _value[z][2][1];
		Z3 := _value[z][3][1];
		Z4 := _value[z][1][2];
		Z5 := _value[z][2][2];
		Z6 := _value[z][3][2];
		Z7 := _value[z][1][3];
		Z8 := _value[z][2][3];
		Z9 := _value[z][3][3];
		tri := ( abs(Z1 - Z5 ) + abs( Z2 - Z5 ) + abs( Z3 - Z5 ) + abs( Z4 - Z5 ) + abs( Z6 - Z5 ) + abs( Z7 - Z5 ) + abs( Z8 - Z5 ) + abs ( Z9 - Z5 )) / 8;
		return tri;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._st_union_finalfn(internal)
 RETURNS raster
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_union_finalfn$function$
;

CREATE OR REPLACE FUNCTION public._st_union_transfn(internal, raster, integer)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_union_transfn$function$
;

CREATE OR REPLACE FUNCTION public._st_union_transfn(internal, raster, integer, text)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_union_transfn$function$
;

CREATE OR REPLACE FUNCTION public._st_union_transfn(internal, raster, text)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_union_transfn$function$
;

CREATE OR REPLACE FUNCTION public._st_union_transfn(internal, raster, unionarg[])
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_union_transfn$function$
;

CREATE OR REPLACE FUNCTION public._st_union_transfn(internal, raster)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_union_transfn$function$
;

CREATE OR REPLACE FUNCTION public._st_valuecount(rast raster, nband integer DEFAULT 1, exclude_nodata_value boolean DEFAULT true, searchvalues double precision[] DEFAULT NULL::double precision[], roundto double precision DEFAULT 0, OUT value double precision, OUT count integer, OUT percent double precision)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_valueCount$function$
;

CREATE OR REPLACE FUNCTION public._st_valuecount(rastertable text, rastercolumn text, nband integer DEFAULT 1, exclude_nodata_value boolean DEFAULT true, searchvalues double precision[] DEFAULT NULL::double precision[], roundto double precision DEFAULT 0, OUT value double precision, OUT count integer, OUT percent double precision)
 RETURNS SETOF record
 LANGUAGE c
 STABLE
AS '$libdir/postgis_raster-3', $function$RASTER_valueCountCoverage$function$
;

CREATE OR REPLACE FUNCTION public._st_voronoi(g1 geometry, clip geometry DEFAULT NULL::geometry, tolerance double precision DEFAULT 0.0, return_polygons boolean DEFAULT true)
 RETURNS geometry
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE COST 5000
AS '$libdir/postgis-3', $function$ST_Voronoi$function$
;

CREATE OR REPLACE FUNCTION public._st_within(geom1 geometry, geom2 geometry)
 RETURNS boolean
 LANGUAGE sql
 IMMUTABLE PARALLEL SAFE
AS $function$SELECT public._ST_Contains($2,$1)$function$
;

CREATE OR REPLACE FUNCTION public._st_within(rast1 raster, nband1 integer, rast2 raster, nband2 integer)
 RETURNS boolean
 LANGUAGE sql
 IMMUTABLE PARALLEL SAFE COST 1000
AS $function$ SELECT public._st_contains($3, $4, $1, $2) $function$
;

CREATE OR REPLACE FUNCTION public._st_worldtorastercoord(rast raster, longitude double precision DEFAULT NULL::double precision, latitude double precision DEFAULT NULL::double precision, OUT columnx integer, OUT rowy integer)
 RETURNS record
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/postgis_raster-3', $function$RASTER_worldToRasterCoord$function$
;

CREATE OR REPLACE FUNCTION public._trsp(text, text, anyarray, anyarray, directed boolean DEFAULT true, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE c
AS '$libdir/libpgrouting-3.8', $function$_trsp$function$
;

-- Function: _trsp
-- Comment: pgRouting internal function deprecated on v3.4.0
-- 
CREATE OR REPLACE FUNCTION public._updaterastersrid(schema_name name, table_name name, column_name name, new_srid integer)
 RETURNS boolean
 LANGUAGE plpgsql
AS $function$
	DECLARE
		fqtn text;
		schema name;
		sql text;
		srid integer;
		ct boolean;
	BEGIN
		schema := NULL;
		IF length($1) > 0 THEN
			sql := 'SELECT nspname FROM pg_namespace '
				|| 'WHERE nspname = ' || quote_literal($1)
				|| 'LIMIT 1';
			EXECUTE sql INTO schema;
			IF schema IS NULL THEN
				RAISE EXCEPTION 'The value provided for schema is invalid';
				RETURN FALSE;
			END IF;
		END IF;
		IF schema IS NULL THEN
			sql := 'SELECT n.nspname AS schemaname '
				|| 'FROM pg_catalog.pg_class c '
				|| 'JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace '
				|| 'WHERE c.relkind = ' || quote_literal('r')
				|| ' AND n.nspname NOT IN (' || quote_literal('pg_catalog')
				|| ', ' || quote_literal('pg_toast')
				|| ') AND pg_catalog.pg_table_is_visible(c.oid)'
				|| ' AND c.relname = ' || quote_literal($2);
			EXECUTE sql INTO schema;
			IF schema IS NULL THEN
				RAISE EXCEPTION 'The table % does not occur in the search_path', quote_literal($2);
				RETURN FALSE;
			END IF;
		END IF;
		IF new_srid < 0 THEN
			srid :=  public.ST_SRID('POINT EMPTY'::public.geometry);
			RAISE NOTICE 'SRID % converted to the officially unknown SRID %', new_srid, srid;
		ELSE
			srid := new_srid;
		END IF;
		ct := public._raster_constraint_info_coverage_tile(schema, $2, $3);
		IF ct IS TRUE THEN
			PERFORM  public._drop_raster_constraint_coverage_tile(schema, $2, $3);
		END IF;
		PERFORM  public.DropRasterConstraints(schema, $2, $3, 'extent', 'alignment', 'srid');
		fqtn := '';
		IF length($1) > 0 THEN
			fqtn := quote_ident($1) || '.';
		END IF;
		fqtn := fqtn || quote_ident($2);
		sql := 'UPDATE ' || fqtn ||
			' SET ' || quote_ident($3) ||
			' =  public.ST_SetSRID(' || quote_ident($3) ||
			'::public.raster, ' || srid || ')';
		RAISE NOTICE 'sql = %', sql;
		EXECUTE sql;
		PERFORM  public.AddRasterConstraints(schema, $2, $3, 'srid', 'extent', 'alignment');
		IF ct IS TRUE THEN
			PERFORM  public._add_raster_constraint_coverage_tile(schema, $2, $3);
		END IF;
		RETURN TRUE;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public._v4trsp(text, text, text, directed boolean DEFAULT true, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE sql
 STRICT
AS $function$
  SELECT seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost
  FROM _pgr_trspv4( $1, $2, $3, $4);
$function$
;

-- Function: _v4trsp
-- Comment: pgRouting internal function deprecated on v3.6.0
-- 
CREATE OR REPLACE FUNCTION public._v4trsp(text, text, anyarray, anyarray, directed boolean DEFAULT true, OUT seq integer, OUT path_seq integer, OUT start_vid bigint, OUT end_vid bigint, OUT node bigint, OUT edge bigint, OUT cost double precision, OUT agg_cost double precision)
 RETURNS SETOF record
 LANGUAGE sql
 STRICT
AS $function$
  SELECT seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost
  FROM _pgr_trspv4( $1, $2, $3, $4, $5);
$function$
;

-- Function: _v4trsp
-- Comment: pgRouting internal function deprecated on v3.6.0
-- 
CREATE OR REPLACE FUNCTION public.addgeometrycolumn(table_name character varying, column_name character varying, new_srid integer, new_type character varying, new_dim integer, use_typmod boolean DEFAULT true)
 RETURNS text
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
	ret  text;
BEGIN
	SELECT public.AddGeometryColumn('','',$1,$2,$3,$4,$5, $6) into ret;
	RETURN ret;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.addgeometrycolumn(schema_name character varying, table_name character varying, column_name character varying, new_srid integer, new_type character varying, new_dim integer, use_typmod boolean DEFAULT true)
 RETURNS text
 LANGUAGE plpgsql
 STABLE STRICT
AS $function$
DECLARE
	ret  text;
BEGIN
	SELECT public.AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7) into ret;
	RETURN ret;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.addgeometrycolumn(catalog_name character varying, schema_name character varying, table_name character varying, column_name character varying, new_srid_in integer, new_type character varying, new_dim integer, use_typmod boolean DEFAULT true)
 RETURNS text
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
	rec RECORD;
	sr varchar;
	real_schema name;
	sql text;
	new_srid integer;
BEGIN
	IF (postgis_type_name(new_type,new_dim) IS NULL )
	THEN
		RAISE EXCEPTION 'Invalid type name "%(%)" - valid ones are:
	POINT, MULTIPOINT,
	LINESTRING, MULTILINESTRING,
	POLYGON, MULTIPOLYGON,
	CIRCULARSTRING, COMPOUNDCURVE, MULTICURVE,
	CURVEPOLYGON, MULTISURFACE,
	GEOMETRY, GEOMETRYCOLLECTION,
	POINTM, MULTIPOINTM,
	LINESTRINGM, MULTILINESTRINGM,
	POLYGONM, MULTIPOLYGONM,
	CIRCULARSTRINGM, COMPOUNDCURVEM, MULTICURVEM
	CURVEPOLYGONM, MULTISURFACEM, TRIANGLE, TRIANGLEM,
	POLYHEDRALSURFACE, POLYHEDRALSURFACEM, TIN, TINM
	or GEOMETRYCOLLECTIONM', new_type, new_dim;
		RETURN 'fail';
	END IF;
	IF ( (new_dim >4) OR (new_dim <2) ) THEN
		RAISE EXCEPTION 'invalid dimension';
		RETURN 'fail';
	END IF;
	IF ( (new_type LIKE '%M') AND (new_dim!=3) ) THEN
		RAISE EXCEPTION 'TypeM needs 3 dimensions';
		RETURN 'fail';
	END IF;
	IF ( new_srid_in > 0 ) THEN
		IF new_srid_in > 998999 THEN
			RAISE EXCEPTION 'AddGeometryColumn() - SRID must be <= %', 998999;
		END IF;
		new_srid := new_srid_in;
		SELECT SRID INTO sr FROM public.spatial_ref_sys WHERE SRID = new_srid;
		IF NOT FOUND THEN
			RAISE EXCEPTION 'AddGeometryColumn() - invalid SRID';
			RETURN 'fail';
		END IF;
	ELSE
		new_srid := public.ST_SRID('POINT EMPTY'::public.geometry);
		IF ( new_srid_in != new_srid ) THEN
			RAISE NOTICE 'SRID value % converted to the officially unknown SRID value %', new_srid_in, new_srid;
		END IF;
	END IF;
	IF ( schema_name IS NOT NULL AND schema_name != '' ) THEN
		sql := 'SELECT nspname FROM pg_namespace ' ||
			'WHERE text(nspname) = ' || quote_literal(schema_name) ||
			'LIMIT 1';
		RAISE DEBUG '%', sql;
		EXECUTE sql INTO real_schema;
		IF ( real_schema IS NULL ) THEN
			RAISE EXCEPTION 'Schema % is not a valid schemaname', quote_literal(schema_name);
			RETURN 'fail';
		END IF;
	END IF;
	IF ( real_schema IS NULL ) THEN
		RAISE DEBUG 'Detecting schema';
		sql := 'SELECT n.nspname AS schemaname ' ||
			'FROM pg_catalog.pg_class c ' ||
			  'JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace ' ||
			'WHERE c.relkind = ' || quote_literal('r') ||
			' AND n.nspname NOT IN (' || quote_literal('pg_catalog') || ', ' || quote_literal('pg_toast') || ')' ||
			' AND pg_catalog.pg_table_is_visible(c.oid)' ||
			' AND c.relname = ' || quote_literal(table_name);
		RAISE DEBUG '%', sql;
		EXECUTE sql INTO real_schema;
		IF ( real_schema IS NULL ) THEN
			RAISE EXCEPTION 'Table % does not occur in the search_path', quote_literal(table_name);
			RETURN 'fail';
		END IF;
	END IF;
	IF use_typmod THEN
		 sql := 'ALTER TABLE ' ||
			quote_ident(real_schema) || '.' || quote_ident(table_name)
			|| ' ADD COLUMN ' || quote_ident(column_name) ||
			' geometry(' || public.postgis_type_name(new_type, new_dim) || ', ' || new_srid::text || ')';
		RAISE DEBUG '%', sql;
	ELSE
		sql := 'ALTER TABLE ' ||
			quote_ident(real_schema) || '.' || quote_ident(table_name)
			|| ' ADD COLUMN ' || quote_ident(column_name) ||
			' geometry ';
		RAISE DEBUG '%', sql;
	END IF;
	EXECUTE sql;
	IF NOT use_typmod THEN
		sql := 'ALTER TABLE ' ||
			quote_ident(real_schema) || '.' || quote_ident(table_name)
			|| ' ADD CONSTRAINT '
			|| quote_ident('enforce_srid_' || column_name)
			|| ' CHECK (st_srid(' || quote_ident(column_name) ||
			') = ' || new_srid::text || ')' ;
		RAISE DEBUG '%', sql;
		EXECUTE sql;
		sql := 'ALTER TABLE ' ||
			quote_ident(real_schema) || '.' || quote_ident(table_name)
			|| ' ADD CONSTRAINT '
			|| quote_ident('enforce_dims_' || column_name)
			|| ' CHECK (st_ndims(' || quote_ident(column_name) ||
			') = ' || new_dim::text || ')' ;
		RAISE DEBUG '%', sql;
		EXECUTE sql;
		IF ( NOT (new_type = 'GEOMETRY')) THEN
			sql := 'ALTER TABLE ' ||
				quote_ident(real_schema) || '.' || quote_ident(table_name) || ' ADD CONSTRAINT ' ||
				quote_ident('enforce_geotype_' || column_name) ||
				' CHECK (GeometryType(' ||
				quote_ident(column_name) || ')=' ||
				quote_literal(new_type) || ' OR (' ||
				quote_ident(column_name) || ') is null)';
			RAISE DEBUG '%', sql;
			EXECUTE sql;
		END IF;
	END IF;
	RETURN
		real_schema || '.' ||
		table_name || '.' || column_name ||
		' SRID:' || new_srid::text ||
		' TYPE:' || new_type ||
		' DIMS:' || new_dim::text || ' ';
END;
$function$
;

CREATE OR REPLACE FUNCTION public.addoverviewconstraints(ovschema name, ovtable name, ovcolumn name, refschema name, reftable name, refcolumn name, ovfactor integer)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	DECLARE
		x int;
		s name;
		t name;
		oschema name;
		rschema name;
		sql text;
		rtn boolean;
	BEGIN
		FOR x IN 1..2 LOOP
			s := '';
			IF x = 1 THEN
				s := $1;
				t := $2;
			ELSE
				s := $4;
				t := $5;
			END IF;
			IF length(s) > 0 THEN
				sql := 'SELECT nspname FROM pg_namespace '
					|| 'WHERE nspname = ' || quote_literal(s)
					|| 'LIMIT 1';
				EXECUTE sql INTO s;
				IF s IS NULL THEN
					RAISE EXCEPTION 'The value % is not a valid schema', quote_literal(s);
					RETURN FALSE;
				END IF;
			END IF;
			IF length(s) < 1 THEN
				sql := 'SELECT n.nspname AS schemaname '
					|| 'FROM pg_catalog.pg_class c '
					|| 'JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace '
					|| 'WHERE c.relkind = ' || quote_literal('r')
					|| ' AND n.nspname NOT IN (' || quote_literal('pg_catalog')
					|| ', ' || quote_literal('pg_toast')
					|| ') AND pg_catalog.pg_table_is_visible(c.oid)'
					|| ' AND c.relname = ' || quote_literal(t);
				EXECUTE sql INTO s;
				IF s IS NULL THEN
					RAISE EXCEPTION 'The table % does not occur in the search_path', quote_literal(t);
					RETURN FALSE;
				END IF;
			END IF;
			IF x = 1 THEN
				oschema := s;
			ELSE
				rschema := s;
			END IF;
		END LOOP;
		rtn :=  public._add_overview_constraint(oschema, $2, $3, rschema, $5, $6, $7);
		IF rtn IS FALSE THEN
			RAISE EXCEPTION 'Unable to add the overview constraint.  Is the schema name, table name or column name incorrect?';
			RETURN FALSE;
		END IF;
		RETURN TRUE;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public.addoverviewconstraints(ovtable name, ovcolumn name, reftable name, refcolumn name, ovfactor integer)
 RETURNS boolean
 LANGUAGE sql
 STRICT
AS $function$ SELECT  public.AddOverviewConstraints('', $1, $2, '', $3, $4, $5) $function$
;

CREATE OR REPLACE FUNCTION public.addrasterconstraints(rastschema name, rasttable name, rastcolumn name, VARIADIC constraints text[])
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	DECLARE
		max int;
		cnt int;
		sql text;
		schema name;
		x int;
		kw text;
		rtn boolean;
	BEGIN
		cnt := 0;
		max := array_length(constraints, 1);
		IF max < 1 THEN
			RAISE NOTICE 'No constraints indicated to be added.  Doing nothing';
			RETURN TRUE;
		END IF;
		schema := NULL;
		IF length($1) > 0 THEN
			sql := 'SELECT nspname FROM pg_namespace '
				|| 'WHERE nspname = ' || quote_literal($1)
				|| 'LIMIT 1';
			EXECUTE sql INTO schema;
			IF schema IS NULL THEN
				RAISE EXCEPTION 'The value provided for schema is invalid';
				RETURN FALSE;
			END IF;
		END IF;
		IF schema IS NULL THEN
			sql := 'SELECT n.nspname AS schemaname '
				|| 'FROM pg_catalog.pg_class c '
				|| 'JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace '
				|| 'WHERE c.relkind = ' || quote_literal('r')
				|| ' AND n.nspname NOT IN (' || quote_literal('pg_catalog')
				|| ', ' || quote_literal('pg_toast')
				|| ') AND pg_catalog.pg_table_is_visible(c.oid)'
				|| ' AND c.relname = ' || quote_literal($2);
			EXECUTE sql INTO schema;
			IF schema IS NULL THEN
				RAISE EXCEPTION 'The table % does not occur in the search_path', quote_literal($2);
				RETURN FALSE;
			END IF;
		END IF;
		<<kwloop>>
		FOR x in 1..max LOOP
			kw := trim(both from lower(constraints[x]));
			BEGIN
				CASE
					WHEN kw = 'srid' THEN
						RAISE NOTICE 'Adding SRID constraint';
						rtn :=  public._add_raster_constraint_srid(schema, $2, $3);
					WHEN kw IN ('scale_x', 'scalex') THEN
						RAISE NOTICE 'Adding scale-X constraint';
						rtn :=  public._add_raster_constraint_scale(schema, $2, $3, 'x');
					WHEN kw IN ('scale_y', 'scaley') THEN
						RAISE NOTICE 'Adding scale-Y constraint';
						rtn :=  public._add_raster_constraint_scale(schema, $2, $3, 'y');
					WHEN kw = 'scale' THEN
						RAISE NOTICE 'Adding scale-X constraint';
						rtn :=  public._add_raster_constraint_scale(schema, $2, $3, 'x');
						RAISE NOTICE 'Adding scale-Y constraint';
						rtn :=  public._add_raster_constraint_scale(schema, $2, $3, 'y');
					WHEN kw IN ('blocksize_x', 'blocksizex', 'width') THEN
						RAISE NOTICE 'Adding blocksize-X constraint';
						rtn :=  public._add_raster_constraint_blocksize(schema, $2, $3, 'width');
					WHEN kw IN ('blocksize_y', 'blocksizey', 'height') THEN
						RAISE NOTICE 'Adding blocksize-Y constraint';
						rtn :=  public._add_raster_constraint_blocksize(schema, $2, $3, 'height');
					WHEN kw = 'blocksize' THEN
						RAISE NOTICE 'Adding blocksize-X constraint';
						rtn :=  public._add_raster_constraint_blocksize(schema, $2, $3, 'width');
						RAISE NOTICE 'Adding blocksize-Y constraint';
						rtn :=  public._add_raster_constraint_blocksize(schema, $2, $3, 'height');
					WHEN kw IN ('same_alignment', 'samealignment', 'alignment') THEN
						RAISE NOTICE 'Adding alignment constraint';
						rtn :=  public._add_raster_constraint_alignment(schema, $2, $3);
					WHEN kw IN ('regular_blocking', 'regularblocking') THEN
						RAISE NOTICE 'Adding coverage tile constraint required for regular blocking';
						rtn :=  public._add_raster_constraint_coverage_tile(schema, $2, $3);
						IF rtn IS NOT FALSE THEN
							RAISE NOTICE 'Adding spatially unique constraint required for regular blocking';
							rtn :=  public._add_raster_constraint_spatially_unique(schema, $2, $3);
						END IF;
					WHEN kw IN ('num_bands', 'numbands') THEN
						RAISE NOTICE 'Adding number of bands constraint';
						rtn :=  public._add_raster_constraint_num_bands(schema, $2, $3);
					WHEN kw IN ('pixel_types', 'pixeltypes') THEN
						RAISE NOTICE 'Adding pixel type constraint';
						rtn :=  public._add_raster_constraint_pixel_types(schema, $2, $3);
					WHEN kw IN ('nodata_values', 'nodatavalues', 'nodata') THEN
						RAISE NOTICE 'Adding nodata value constraint';
						rtn :=  public._add_raster_constraint_nodata_values(schema, $2, $3);
					WHEN kw IN ('out_db', 'outdb') THEN
						RAISE NOTICE 'Adding out-of-database constraint';
						rtn :=  public._add_raster_constraint_out_db(schema, $2, $3);
					WHEN kw = 'extent' THEN
						RAISE NOTICE 'Adding maximum extent constraint';
						rtn :=  public._add_raster_constraint_extent(schema, $2, $3);
					ELSE
						RAISE NOTICE 'Unknown constraint: %.  Skipping', quote_literal(constraints[x]);
						CONTINUE kwloop;
				END CASE;
			END;
			IF rtn IS FALSE THEN
				cnt := cnt + 1;
				RAISE WARNING 'Unable to add constraint: %.  Skipping', quote_literal(constraints[x]);
			END IF;
		END LOOP kwloop;
		IF cnt = max THEN
			RAISE EXCEPTION 'None of the constraints specified could be added.  Is the schema name, table name or column name incorrect?';
			RETURN FALSE;
		END IF;
		RETURN TRUE;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public.addrasterconstraints(rasttable name, rastcolumn name, VARIADIC constraints text[])
 RETURNS boolean
 LANGUAGE sql
 STRICT
AS $function$ SELECT public.AddRasterConstraints('', $1, $2, VARIADIC $3) $function$
;

CREATE OR REPLACE FUNCTION public.addrasterconstraints(rasttable name, rastcolumn name, srid boolean DEFAULT true, scale_x boolean DEFAULT true, scale_y boolean DEFAULT true, blocksize_x boolean DEFAULT true, blocksize_y boolean DEFAULT true, same_alignment boolean DEFAULT true, regular_blocking boolean DEFAULT false, num_bands boolean DEFAULT true, pixel_types boolean DEFAULT true, nodata_values boolean DEFAULT true, out_db boolean DEFAULT true, extent boolean DEFAULT true)
 RETURNS boolean
 LANGUAGE sql
 STRICT
AS $function$ SELECT public.AddRasterConstraints('', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) $function$
;

CREATE OR REPLACE FUNCTION public.addrasterconstraints(rastschema name, rasttable name, rastcolumn name, srid boolean DEFAULT true, scale_x boolean DEFAULT true, scale_y boolean DEFAULT true, blocksize_x boolean DEFAULT true, blocksize_y boolean DEFAULT true, same_alignment boolean DEFAULT true, regular_blocking boolean DEFAULT false, num_bands boolean DEFAULT true, pixel_types boolean DEFAULT true, nodata_values boolean DEFAULT true, out_db boolean DEFAULT true, extent boolean DEFAULT true)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	DECLARE
		constraints text[];
	BEGIN
		IF srid IS TRUE THEN
			constraints := constraints || 'srid'::text;
		END IF;
		IF scale_x IS TRUE THEN
			constraints := constraints || 'scale_x'::text;
		END IF;
		IF scale_y IS TRUE THEN
			constraints := constraints || 'scale_y'::text;
		END IF;
		IF blocksize_x IS TRUE THEN
			constraints := constraints || 'blocksize_x'::text;
		END IF;
		IF blocksize_y IS TRUE THEN
			constraints := constraints || 'blocksize_y'::text;
		END IF;
		IF same_alignment IS TRUE THEN
			constraints := constraints || 'same_alignment'::text;
		END IF;
		IF regular_blocking IS TRUE THEN
			constraints := constraints || 'regular_blocking'::text;
		END IF;
		IF num_bands IS TRUE THEN
			constraints := constraints || 'num_bands'::text;
		END IF;
		IF pixel_types IS TRUE THEN
			constraints := constraints || 'pixel_types'::text;
		END IF;
		IF nodata_values IS TRUE THEN
			constraints := constraints || 'nodata_values'::text;
		END IF;
		IF out_db IS TRUE THEN
			constraints := constraints || 'out_db'::text;
		END IF;
		IF extent IS TRUE THEN
			constraints := constraints || 'extent'::text;
		END IF;
		RETURN public.AddRasterConstraints($1, $2, $3, VARIADIC constraints);
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public.auto_calculate_bbox()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF NEW.geometry IS NOT NULL AND 
     (NEW.bbox_min_lng IS NULL OR NEW.bbox_max_lng IS NULL OR 
      NEW.bbox_min_lat IS NULL OR NEW.bbox_max_lat IS NULL) THEN
    NEW.bbox_min_lng := ST_XMin(NEW.geometry);
    NEW.bbox_max_lng := ST_XMax(NEW.geometry);
    NEW.bbox_min_lat := ST_YMin(NEW.geometry);
    NEW.bbox_max_lat := ST_YMax(NEW.geometry);
  END IF;
  RETURN NEW;
END;
$function$
;

-- Function: auto_calculate_bbox
-- Comment: Automatically calculates bounding box from geometry
-- 
CREATE OR REPLACE FUNCTION public.auto_calculate_length()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF NEW.geometry IS NOT NULL AND (NEW.length_km IS NULL OR NEW.length_km <= 0) THEN
    NEW.length_km := ST_Length(NEW.geometry, true) / 1000.0; -- Convert meters to kilometers
  END IF;
  RETURN NEW;
END;
$function$
;

-- Function: auto_calculate_length
-- Comment: Automatically calculates trail length from geometry
-- 
CREATE OR REPLACE FUNCTION public.box(box3d)
 RETURNS box
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 50
AS '$libdir/postgis-3', $function$BOX3D_to_BOX$function$
;

CREATE OR REPLACE FUNCTION public.box(geometry)
 RETURNS box
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 50
AS '$libdir/postgis-3', $function$LWGEOM_to_BOX$function$
;

CREATE OR REPLACE FUNCTION public.box2d(geometry)
 RETURNS box2d
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 50
AS '$libdir/postgis-3', $function$LWGEOM_to_BOX2D$function$
;

CREATE OR REPLACE FUNCTION public.box2d(box3d)
 RETURNS box2d
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 50
AS '$libdir/postgis-3', $function$BOX3D_to_BOX2D$function$
;

CREATE OR REPLACE FUNCTION public.box2d_in(cstring)
 RETURNS box2d
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/postgis-3', $function$BOX2D_in$function$
;

CREATE OR REPLACE FUNCTION public.box2d_out(box2d)
 RETURNS cstring
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/postgis-3', $function$BOX2D_out$function$
;

CREATE OR REPLACE FUNCTION public.box2df_in(cstring)
 RETURNS box2df
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/postgis-3', $function$box2df_in$function$
;

CREATE OR REPLACE FUNCTION public.box2df_out(box2df)
 RETURNS cstring
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/postgis-3', $function$box2df_out$function$
;

CREATE OR REPLACE FUNCTION public.box3d(raster)
 RETURNS box3d
 LANGUAGE sql
 IMMUTABLE PARALLEL SAFE STRICT
AS $function$select box3d( public.ST_convexhull($1))$function$
;

CREATE OR REPLACE FUNCTION public.box3d(box2d)
 RETURNS box3d
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 50
AS '$libdir/postgis-3', $function$BOX2D_to_BOX3D$function$
;

CREATE OR REPLACE FUNCTION public.box3d(geometry)
 RETURNS box3d
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 50
AS '$libdir/postgis-3', $function$LWGEOM_to_BOX3D$function$
;

CREATE OR REPLACE FUNCTION public.box3d_in(cstring)
 RETURNS box3d
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/postgis-3', $function$BOX3D_in$function$
;

CREATE OR REPLACE FUNCTION public.box3d_out(box3d)
 RETURNS cstring
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/postgis-3', $function$BOX3D_out$function$
;

CREATE OR REPLACE FUNCTION public.box3dtobox(box3d)
 RETURNS box
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 50
AS '$libdir/postgis-3', $function$BOX3D_to_BOX$function$
;

CREATE OR REPLACE FUNCTION public.build_routing_nodes_with_trail_ids(staging_schema text, trails_table text, intersection_tolerance_meters double precision DEFAULT 2.0)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
    node_count integer;
BEGIN
    -- Clear existing routing nodes
    EXECUTE format('DELETE FROM %I.routing_nodes', staging_schema);
    
    -- Insert routing nodes using optimized PostGIS spatial functions with trail_ids
    EXECUTE format('
        INSERT INTO %I.routing_nodes (node_uuid, lat, lng, elevation, node_type, connected_trails, trail_ids)
        WITH trail_endpoints AS (
            -- Extract start and end points of all trails using PostGIS functions
            -- Preserve 3D geometry by removing ST_Force2D()
            SELECT 
                ST_StartPoint(geometry) as start_point,
                ST_EndPoint(geometry) as end_point,
                app_uuid,
                name
            FROM %I.%I
            WHERE geometry IS NOT NULL AND ST_IsValid(geometry)
        ),
        intersection_points AS (
            -- Use the enhanced intersection detection function
            SELECT 
                intersection_point,
                intersection_point_3d,
                connected_trail_ids,
                connected_trail_names,
                node_type,
                distance_meters
            FROM detect_trail_intersections(''%I'', ''%I'', GREATEST($1, 0.001))
            WHERE array_length(connected_trail_ids, 1) > 1  -- Only true intersections
        ),
        all_nodes AS (
            -- Combine intersection points and trail endpoints
            SELECT 
                intersection_point as point,
                intersection_point_3d as point_3d,
                unnest(connected_trail_names) as connected_trail,
                connected_trail_ids as trail_ids,
                ''intersection'' as node_type
            FROM intersection_points
            
            UNION ALL
            
            -- Trail start points
            SELECT 
                start_point as point,
                ST_Force3D(start_point) as point_3d,
                name as connected_trail,
                ARRAY[app_uuid] as trail_ids,
                ''endpoint'' as node_type
            FROM trail_endpoints
            
            UNION ALL
            
            -- Trail end points
            SELECT 
                end_point as point,
                ST_Force3D(end_point) as point_3d,
                name as connected_trail,
                ARRAY[app_uuid] as trail_ids,
                ''endpoint'' as node_type
            FROM trail_endpoints
        ),
        grouped_nodes AS (
            -- Group nearby nodes to avoid duplicates using spatial clustering
            SELECT 
                ST_X(point) as lng,
                ST_Y(point) as lat,
                COALESCE(ST_Z(point_3d), 0) as elevation,
                array_agg(DISTINCT connected_trail) as all_connected_trails,
                array_agg(DISTINCT unnest(trail_ids)) as all_trail_ids,
                CASE 
                    WHEN array_length(array_agg(DISTINCT connected_trail), 1) > 1 THEN ''intersection''
                    ELSE ''endpoint''
                END as node_type,
                point,
                point_3d
            FROM all_nodes
            GROUP BY point, point_3d
        ),
        final_nodes AS (
            -- Remove duplicate nodes within tolerance distance
            SELECT DISTINCT ON (ST_SnapToGrid(point, GREATEST($1, 0.001)/1000))
                lng,
                lat,
                elevation,
                all_connected_trails,
                all_trail_ids,
                node_type
            FROM grouped_nodes
            ORDER BY ST_SnapToGrid(point, GREATEST($1, 0.001)/1000), array_length(all_connected_trails, 1) DESC
        )
        SELECT 
            gen_random_uuid()::text as node_uuid,
            lat,
            lng,
            elevation,
            node_type,
            array_to_string(all_connected_trails, '','') as connected_trails,
            all_trail_ids as trail_ids
        FROM final_nodes
        WHERE array_length(all_connected_trails, 1) > 0
    ', staging_schema, staging_schema, trails_table, staging_schema, trails_table)
    USING intersection_tolerance_meters;
    
    -- Get the count of inserted nodes
    EXECUTE format('SELECT COUNT(*) FROM %I.routing_nodes', staging_schema) INTO node_count;
    
    RETURN node_count;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.bytea(geography)
 RETURNS bytea
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/postgis-3', $function$LWGEOM_to_bytea$function$
;

CREATE OR REPLACE FUNCTION public.bytea(raster)
 RETURNS bytea
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/postgis_raster-3', $function$RASTER_to_bytea$function$
;

CREATE OR REPLACE FUNCTION public.bytea(geometry)
 RETURNS bytea
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 50
AS '$libdir/postgis-3', $function$LWGEOM_to_bytea$function$
;

CREATE OR REPLACE FUNCTION public.calculate_route_connectivity_score(trail_count integer, route_distance_km real)
 RETURNS real
 LANGUAGE plpgsql
AS $function$
DECLARE
    connectivity_score REAL;
BEGIN
    IF trail_count IS NULL OR route_distance_km IS NULL OR route_distance_km <= 0 THEN
        RETURN NULL;
    END IF;
    connectivity_score := trail_count::REAL / route_distance_km;
    connectivity_score := LEAST(1.0, connectivity_score / 5.0);
    RETURN connectivity_score;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.calculate_route_cost(steepness_m_per_km double precision, distance_km double precision)
 RETURNS double precision
 LANGUAGE plpgsql
AS $function$
DECLARE
    weights json;
BEGIN
    weights := get_cost_weights();
    RETURN (steepness_m_per_km * (weights ->> 'steepness_weight')::float) + 
           (distance_km * (weights ->> 'distance_weight')::float);
END;
$function$
;

CREATE OR REPLACE FUNCTION public.calculate_route_difficulty(elevation_gain_rate real)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF elevation_gain_rate IS NULL THEN
        RETURN NULL;
    END IF;
    IF elevation_gain_rate < 50 THEN
        RETURN 'easy';
    ELSIF elevation_gain_rate < 100 THEN
        RETURN 'moderate';
    ELSIF elevation_gain_rate < 150 THEN
        RETURN 'hard';
    ELSE
        RETURN 'expert';
    END IF;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.calculate_route_elevation_stats(route_edges_json jsonb)
 RETURNS TABLE(min_elevation real, max_elevation real, avg_elevation real)
 LANGUAGE plpgsql
AS $function$
DECLARE
    edge_record RECORD;
    min_elev REAL := 9999;
    max_elev REAL := -9999;
    total_elev REAL := 0;
    edge_count INTEGER := 0;
BEGIN
    FOR edge_record IN 
        SELECT 
            (edge->>'min_elevation')::REAL as min_elev,
            (edge->>'max_elevation')::REAL as max_elev,
            (edge->>'avg_elevation')::REAL as avg_elev
        FROM jsonb_array_elements(route_edges_json) as edge
        WHERE edge->>'min_elevation' IS NOT NULL
          AND edge->>'max_elevation' IS NOT NULL
          AND edge->>'avg_elevation' IS NOT NULL
    LOOP
        IF edge_record.min_elev < min_elev THEN
            min_elev := edge_record.min_elev;
        END IF;
        IF edge_record.max_elev > max_elev THEN
            max_elev := edge_record.max_elev;
        END IF;
        total_elev := total_elev + edge_record.avg_elev;
        edge_count := edge_count + 1;
    END LOOP;
    IF edge_count = 0 THEN
        RETURN QUERY SELECT NULL::REAL, NULL::REAL, NULL::REAL;
        RETURN;
    END IF;
    RETURN QUERY SELECT 
        CASE WHEN min_elev = 9999 THEN NULL ELSE min_elev END,
        CASE WHEN max_elev = -9999 THEN NULL ELSE max_elev END,
        total_elev / edge_count;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.calculate_route_estimated_time(distance_km real, elevation_gain_rate real)
 RETURNS real
 LANGUAGE plpgsql
AS $function$
DECLARE
    base_speed_kmh REAL := 4.0; -- Base hiking speed on flat terrain
    elevation_factor REAL;
    estimated_hours REAL;
BEGIN
    IF distance_km IS NULL OR distance_km <= 0 THEN
        RETURN NULL;
    END IF;
    IF elevation_gain_rate IS NULL OR elevation_gain_rate < 50 THEN
        elevation_factor := 1.0; -- No penalty for easy terrain
    ELSIF elevation_gain_rate < 100 THEN
        elevation_factor := 0.8; -- 20% slower for moderate terrain
    ELSIF elevation_gain_rate < 150 THEN
        elevation_factor := 0.6; -- 40% slower for hard terrain
    ELSE
        elevation_factor := 0.4; -- 60% slower for expert terrain
    END IF;
    estimated_hours := distance_km / (base_speed_kmh * elevation_factor);
    RETURN GREATEST(0.5, LEAST(24.0, estimated_hours));
END;
$function$
;

CREATE OR REPLACE FUNCTION public.calculate_route_gain_rate(route_distance_km real, route_elevation_gain real)
 RETURNS real
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF route_distance_km IS NULL OR route_distance_km <= 0 THEN
        RETURN NULL;
    END IF;
    IF route_elevation_gain IS NULL THEN
        RETURN NULL;
    END IF;
    RETURN route_elevation_gain / route_distance_km;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.calculate_route_parametric_metrics(route_distance_km real, route_elevation_gain real, route_trail_count integer, route_edges_json jsonb)
 RETURNS TABLE(elevation_gain_rate real, difficulty text, estimated_time_hours real, connectivity_score real, min_elevation real, max_elevation real, avg_elevation real)
 LANGUAGE plpgsql
AS $function$
DECLARE
    gain_rate REAL;
    route_difficulty TEXT;
    estimated_time REAL;
    connectivity REAL;
    elevation_stats RECORD;
BEGIN
    gain_rate := calculate_route_gain_rate(route_distance_km, route_elevation_gain);
    route_difficulty := calculate_route_difficulty(gain_rate);
    estimated_time := calculate_route_estimated_time(route_distance_km, gain_rate);
    connectivity := calculate_route_connectivity_score(route_trail_count, route_distance_km);
    SELECT * INTO elevation_stats FROM calculate_route_elevation_stats(route_edges_json);
    RETURN QUERY SELECT 
        gain_rate,
        route_difficulty,
        estimated_time,
        connectivity,
        elevation_stats.min_elevation,
        elevation_stats.max_elevation,
        elevation_stats.avg_elevation;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.calculate_route_similarity_score(actual_distance_km double precision, target_distance_km double precision, actual_elevation_gain double precision, target_elevation_gain double precision)
 RETURNS double precision
 LANGUAGE plpgsql
AS $function$
BEGIN
    -- Simple similarity score based on how close we are to target
    RETURN GREATEST(0, 1 - ABS(actual_distance_km - target_distance_km) / target_distance_km);
END;
$function$
;

CREATE OR REPLACE FUNCTION public.calculate_trail_stats()
 RETURNS TABLE(total_trails bigint, total_length_km double precision, avg_elevation_gain double precision, regions_count bigint)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT 
        COUNT(*) as total_trails,
        COALESCE(SUM(length_km), 0) as total_length_km,
        COALESCE(AVG(elevation_gain), 0) as avg_elevation_gain,
        COUNT(DISTINCT region) as regions_count
    FROM trails;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.check_database_integrity()
 RETURNS TABLE(check_name text, status text, count bigint, details text)
 LANGUAGE plpgsql
AS $function$
BEGIN
  RETURN QUERY
  SELECT 
    'Incomplete Trails'::TEXT,
    CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' END::TEXT,
    COUNT(*),
    'Trails missing required data'::TEXT
  FROM incomplete_trails;
  RETURN QUERY
  SELECT 
    '2D Geometries'::TEXT,
    CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'WARN' END::TEXT,
    COUNT(*),
    'Trails with 2D geometry (should be 3D)'::TEXT
  FROM trails_with_2d_geometry;
  RETURN QUERY
  SELECT 
    'Invalid Geometries'::TEXT,
    CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' END::TEXT,
    COUNT(*),
    'Trails with invalid geometry'::TEXT
  FROM invalid_geometries;
  RETURN QUERY
  SELECT 
    'Inconsistent Elevation'::TEXT,
    CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' END::TEXT,
    COUNT(*),
    'Trails with inconsistent elevation data'::TEXT
  FROM inconsistent_elevation_data;
  RETURN QUERY
  SELECT 
    'Orphaned Routing Edges'::TEXT,
    CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' END::TEXT,
    COUNT(*),
    'Routing edges referencing non-existent trails'::TEXT
  FROM routing_edges re
  WHERE NOT EXISTS (SELECT 1 FROM trails t WHERE t.app_uuid = re.trail_id);
END;
$function$
;

-- Function: check_database_integrity
-- Comment: Comprehensive database integrity check
-- 
CREATE OR REPLACE FUNCTION public.cleanup_orphaned_nodes(staging_schema text)
 RETURNS TABLE(success boolean, message text, cleaned_nodes integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    orphaned_nodes_count integer := 0;
    total_nodes_before integer := 0;
    total_nodes_after integer := 0;
BEGIN
    -- Get count before cleanup
    EXECUTE format('SELECT COUNT(*) FROM %I.routing_nodes', staging_schema) INTO total_nodes_before;

    -- Remove orphaned nodes (nodes not connected to any trails)
    -- These are nodes that were created but don't actually connect any trail segments
    EXECUTE format($f$
        DELETE FROM %I.routing_nodes n
        WHERE NOT EXISTS (
            SELECT 1 FROM %I.trails t
            WHERE ST_DWithin(
                ST_SetSRID(ST_MakePoint(n.lng, n.lat), 4326),
                t.geometry,
                0.001
            )
        )
    $f$, staging_schema, staging_schema);

    GET DIAGNOSTICS orphaned_nodes_count = ROW_COUNT;

    -- Get count after cleanup
    EXECUTE format('SELECT COUNT(*) FROM %I.routing_nodes', staging_schema) INTO total_nodes_after;

    -- Return results
    RETURN QUERY SELECT
        true as success,
        format('Cleaned up %s orphaned nodes (before: %s, after: %s)', 
               orphaned_nodes_count, total_nodes_before, total_nodes_after) as message,
        orphaned_nodes_count as cleaned_nodes;

    RAISE NOTICE 'Cleaned up % orphaned nodes', orphaned_nodes_count;

EXCEPTION WHEN OTHERS THEN
    -- Return error information
    RETURN QUERY SELECT
        false,
        format('Error during orphaned nodes cleanup: %s', SQLERRM) as message,
        0 as cleaned_nodes;

    RAISE NOTICE 'Error during orphaned nodes cleanup: %', SQLERRM;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.cleanup_routing_graph(staging_schema text)
 RETURNS TABLE(orphaned_nodes integer, orphaned_edges integer, message text)
 LANGUAGE plpgsql
AS $function$ DECLARE orphaned_nodes_count integer := 0; orphaned_edges_count integer := 0; BEGIN EXECUTE format($f$ DELETE FROM %I.routing_nodes WHERE id NOT IN (SELECT DISTINCT source FROM %I.routing_edges UNION SELECT DISTINCT target FROM %I.routing_edges) $f$, staging_schema, staging_schema, staging_schema, staging_schema); GET DIAGNOSTICS orphaned_nodes_count = ROW_COUNT; EXECUTE format($f$ DELETE FROM %I.routing_edges WHERE source NOT IN (SELECT id FROM %I.routing_nodes) OR target NOT IN (SELECT id FROM %I.routing_nodes) $f$, staging_schema, staging_schema, staging_schema, staging_schema); GET DIAGNOSTICS orphaned_edges_count = ROW_COUNT; RETURN QUERY SELECT orphaned_nodes_count, orphaned_edges_count, format('Cleaned up %s orphaned nodes and %s orphaned edges', orphaned_nodes_count, orphaned_edges_count) as message; END; $function$
;

CREATE OR REPLACE FUNCTION public.contains_2d(box2df, box2df)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/postgis-3', $function$gserialized_contains_box2df_box2df_2d$function$
;

CREATE OR REPLACE FUNCTION public.contains_2d(geometry, box2df)
 RETURNS boolean
 LANGUAGE sql
 IMMUTABLE PARALLEL SAFE STRICT COST 1
AS $function$SELECT $2 OPERATOR(public.@) $1;$function$
;

CREATE OR REPLACE FUNCTION public.contains_2d(box2df, geometry)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/postgis-3', $function$gserialized_contains_box2df_geom_2d$function$
;

CREATE OR REPLACE FUNCTION public.copy_and_split_trails_to_staging_native(staging_schema text, source_table text, region_filter text, bbox_min_lng real DEFAULT NULL::real, bbox_min_lat real DEFAULT NULL::real, bbox_max_lng real DEFAULT NULL::real, bbox_max_lat real DEFAULT NULL::real, trail_limit integer DEFAULT NULL::integer, tolerance_meters real DEFAULT 1.0)
 RETURNS TABLE(original_count integer, split_count integer, intersection_count integer, success boolean, message text)
 LANGUAGE plpgsql
AS $function$
DECLARE
    original_count_var integer := 0;
    split_count_var integer := 0;
    intersection_count_var integer := 0;
    source_query text;
    limit_clause text := '';
BEGIN
    -- Clear existing data
    EXECUTE format('DELETE FROM %I.trails', staging_schema);
    EXECUTE format('DELETE FROM %I.intersection_points', staging_schema);

    -- Build source query with filters
    source_query := format('SELECT * FROM %I WHERE region = %L', source_table, region_filter);

    -- Add bbox filter if provided
    IF bbox_min_lng IS NOT NULL AND bbox_min_lat IS NOT NULL AND bbox_max_lng IS NOT NULL AND bbox_max_lat IS NOT NULL THEN
        source_query := source_query || format(' AND ST_Intersects(geometry, ST_MakeEnvelope(%s, %s, %s, %s, 4326))', bbox_min_lng, bbox_min_lat, bbox_max_lng, bbox_max_lat);
    END IF;

    -- Add limit
    IF trail_limit IS NOT NULL THEN
        limit_clause := format(' LIMIT %s', trail_limit);
    END IF;

    source_query := source_query || limit_clause;

    -- Step 1: Copy and split trails using native PostGIS ST_Split
    EXECUTE format($f$
        INSERT INTO %I.trails (
            app_uuid, osm_id, name, region, trail_type, surface, difficulty, source_tags,
            bbox_min_lng, bbox_max_lng, bbox_min_lat, bbox_max_lat,
            length_km, elevation_gain, elevation_loss, max_elevation, min_elevation, avg_elevation, source,
            geometry, created_at, updated_at
        )
        WITH trail_intersections AS (
            SELECT DISTINCT
                t1.app_uuid as trail1_uuid,
                t2.app_uuid as trail2_uuid,
                ST_Intersection(t1.geometry, t2.geometry) as intersection_point
            FROM (%s) t1
            JOIN (%s) t2 ON t1.id < t2.id
            WHERE ST_Intersects(t1.geometry, t2.geometry)
              AND ST_GeometryType(ST_Intersection(t1.geometry, t2.geometry)) IN ('ST_Point', 'ST_MultiPoint')
              AND ST_Length(t1.geometry::geography) > 5
              AND ST_Length(t2.geometry::geography) > 5
        ),
        all_trails AS (
            SELECT
                id, app_uuid, osm_id, name, region, trail_type, surface, difficulty, source_tags,
                bbox_min_lng, bbox_max_lng, bbox_min_lat, bbox_max_lat,
                length_km, elevation_gain, elevation_loss, max_elevation, min_elevation, avg_elevation,
                source, created_at, updated_at, geometry
            FROM (%s) t WHERE t.geometry IS NOT NULL AND ST_IsValid(t.geometry)
        ),
        trails_with_intersections AS (
            SELECT
                at.id, at.app_uuid, at.osm_id, at.name, at.region, at.trail_type, at.surface, at.difficulty, at.source_tags,
                at.bbox_min_lng, at.bbox_max_lng, at.bbox_min_lat, at.bbox_max_lat,
                at.length_km, at.elevation_gain, at.elevation_loss, at.max_elevation, at.min_elevation, at.avg_elevation,
                at.source, at.created_at, at.updated_at, at.geometry,
                (ST_Dump(ST_Split(at.geometry, ti.intersection_point))).geom as split_geometry,
                (ST_Dump(ST_Split(at.geometry, ti.intersection_point))).path[1] as segment_order
            FROM all_trails at
            JOIN trail_intersections ti ON at.app_uuid IN (ti.trail1_uuid, ti.trail2_uuid)
        ),
        trails_without_intersections AS (
            SELECT
                at.id, at.app_uuid, at.osm_id, at.name, at.region, at.trail_type, at.surface, at.difficulty, at.source_tags,
                at.bbox_min_lng, at.bbox_max_lng, at.bbox_min_lat, at.bbox_max_lat,
                at.length_km, at.elevation_gain, at.elevation_loss, at.max_elevation, at.min_elevation, at.avg_elevation,
                at.source, at.created_at, at.updated_at, at.geometry,
                at.geometry as split_geometry,
                1 as segment_order
            FROM all_trails at
            WHERE at.app_uuid NOT IN (
                SELECT DISTINCT trail1_uuid FROM trail_intersections
                UNION
                SELECT DISTINCT trail2_uuid FROM trail_intersections
            )
        ),
        processed_trails AS (
            SELECT * FROM trails_with_intersections
            UNION ALL
            SELECT * FROM trails_without_intersections
        )
        SELECT
            gen_random_uuid() as app_uuid,
            osm_id,
            name,
            region,
            trail_type,
            surface,
            difficulty,
            source_tags,
            ST_XMin(split_geometry) as bbox_min_lng,
            ST_XMax(split_geometry) as bbox_max_lng,
            ST_YMin(split_geometry) as bbox_min_lat,
            ST_YMax(split_geometry) as bbox_max_lat,
            ST_Length(split_geometry::geography) / 1000.0 as length_km,
            elevation_gain,
            elevation_loss,
            max_elevation,
            min_elevation,
            avg_elevation,
            source,
            split_geometry as geometry,
            NOW() as created_at,
            NOW() as updated_at
        FROM processed_trails pt
        WHERE ST_IsValid(pt.split_geometry)
          AND pt.app_uuid IS NOT NULL
    $f$, staging_schema, source_query, source_query, source_query);

    GET DIAGNOSTICS split_count_var = ROW_COUNT;

    -- Get original count from source query
    EXECUTE format('SELECT COUNT(*) FROM (%s) t WHERE t.geometry IS NOT NULL AND ST_IsValid(t.geometry)', source_query) INTO original_count_var;

    -- Step 2: Detect intersections between split trail segments
    PERFORM detect_trail_intersections(staging_schema, 'trails', tolerance_meters);

    -- Get intersection count
    EXECUTE format('SELECT COUNT(*) FROM %I.intersection_points', staging_schema) INTO intersection_count_var;

    -- Clear routing data in staging schema since it needs to be regenerated from split trails
    EXECUTE format('DELETE FROM %I.routing_nodes', staging_schema);
    EXECUTE format('DELETE FROM %I.routing_edges', staging_schema);

    -- Create optimized spatial indexes
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_trails_geometry ON %I.trails USING GIST(geometry)', staging_schema);
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_trails_app_uuid ON %I.trails(app_uuid)', staging_schema);
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_trails_name ON %I.trails(name)', staging_schema);
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_intersection_points ON %I.intersection_points USING GIST(point)', staging_schema);

    -- Return results
    RETURN QUERY SELECT
        original_count_var,
        split_count_var,
        intersection_count_var,
        true as success,
        format('Successfully copied and split %s trails into %s segments with %s intersections',
               original_count_var, split_count_var, intersection_count_var) as message;

    RAISE NOTICE 'Native PostgreSQL copy and split: % original trails -> % split segments with % intersections',
        original_count_var, split_count_var, intersection_count_var;

EXCEPTION WHEN OTHERS THEN
    -- Return error information
    RETURN QUERY SELECT
        0, 0, 0, false,
        format('Error during copy and split: %s', SQLERRM) as message;

    RAISE NOTICE 'Error during native PostgreSQL copy and split: %', SQLERRM;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.copy_and_split_trails_to_staging_native_v12(staging_schema text, region_filter text, bbox_min_lng real DEFAULT NULL::real, bbox_min_lat real DEFAULT NULL::real, bbox_max_lng real DEFAULT NULL::real, bbox_max_lat real DEFAULT NULL::real, trail_limit integer DEFAULT NULL::integer, tolerance_meters real DEFAULT 2.0)
 RETURNS TABLE(original_count integer, split_count integer, intersection_count integer, success boolean, message text)
 LANGUAGE plpgsql
AS $function$
DECLARE
    original_count_var integer := 0;
    split_count_var integer := 0;
    intersection_count_var integer := 0;
    source_query text;
    limit_clause text := '';
    source_table text := 'trails';  -- Hardcode the source table
    full_sql text;
BEGIN
    -- Debug: Log the parameters we received
    RAISE NOTICE 'Function received parameters: staging_schema=%, region_filter=%', staging_schema, region_filter;
    RAISE NOTICE 'DEBUG: staging_schema=% staging_schema type=%', staging_schema, pg_typeof(staging_schema);
    RAISE NOTICE 'FUNCTION IS BEING CALLED - V12 VERSION';
    
    -- Clear existing data
    EXECUTE format('DELETE FROM %I.trails', staging_schema);
    EXECUTE format('DELETE FROM %I.intersection_points', staging_schema);
    
    -- Build source query with filters
    source_query := format('SELECT * FROM %I WHERE region = %L', source_table, region_filter);
    
    -- Add bbox filter if provided
    IF bbox_min_lng IS NOT NULL AND bbox_min_lat IS NOT NULL AND bbox_max_lng IS NOT NULL AND bbox_max_lat IS NOT NULL THEN
        source_query := source_query || format(' AND ST_Intersects(geometry, ST_MakeEnvelope(%s, %s, %s, %s, 4326))', bbox_min_lng, bbox_min_lat, bbox_max_lng, bbox_max_lat);
    END IF;
    
    -- Add limit
    IF trail_limit IS NOT NULL THEN
        limit_clause := format(' LIMIT %s', trail_limit);
    END IF;
    
    source_query := source_query || limit_clause;
    
    -- Debug: Log the source_query
    RAISE NOTICE 'Source query: %', source_query;
    
    -- Build the full SQL dynamically to avoid parameter passing issues
    full_sql := format($f$
        INSERT INTO %I.trails (
            app_uuid, osm_id, name, region, trail_type, surface, difficulty, source_tags,
            bbox_min_lng, bbox_max_lng, bbox_min_lat, bbox_max_lat,
            length_km, elevation_gain, elevation_loss, max_elevation, min_elevation, avg_elevation, source,
            geometry, created_at, updated_at
        )
        WITH trail_intersections AS (
            -- Find all intersection points between trails using 3D coordinates
            SELECT DISTINCT
                t1.app_uuid as trail1_uuid,
                t2.app_uuid as trail2_uuid,
                ST_Intersection(t1.geometry, t2.geometry) as intersection_point
            FROM (%s) t1
            JOIN (%s) t2 ON t1.id < t2.id
            WHERE ST_Intersects(t1.geometry, t2.geometry)
              AND ST_GeometryType(ST_Intersection(t1.geometry, t2.geometry)) IN ('ST_Point', 'ST_MultiPoint')
              AND ST_Length(t1.geometry::geography) > 5
              AND ST_Length(t2.geometry::geography) > 5
        ),
        all_trails AS (
            -- Get all source trails
            SELECT 
                id, app_uuid, osm_id, name, region, trail_type, surface, difficulty, source_tags,
                bbox_min_lng, bbox_max_lng, bbox_min_lat, bbox_max_lat,
                length_km, elevation_gain, elevation_loss, max_elevation, min_elevation, avg_elevation,
                source, created_at, updated_at, geometry
            FROM (%s) t WHERE t.geometry IS NOT NULL AND ST_IsValid(t.geometry)
        ),
        trails_with_intersections AS (
            -- Get trails that have intersections and split them
            SELECT 
                at.id, at.app_uuid, at.osm_id, at.name, at.region, at.trail_type, at.surface, at.difficulty, at.source_tags,
                at.bbox_min_lng, at.bbox_max_lng, at.bbox_min_lat, at.bbox_max_lat,
                at.length_km, at.elevation_gain, at.elevation_loss, at.max_elevation, at.min_elevation, at.avg_elevation,
                at.source, at.created_at, at.updated_at, at.geometry,
                (ST_Dump(ST_Split(at.geometry, ti.intersection_point))).geom as split_geometry,
                (ST_Dump(ST_Split(at.geometry, ti.intersection_point))).path[1] as segment_order
            FROM all_trails at
            JOIN trail_intersections ti ON at.app_uuid IN (ti.trail1_uuid, ti.trail2_uuid)
        ),
        trails_without_intersections AS (
            -- Get trails that don't have intersections (keep original geometry)
            SELECT 
                at.id, at.app_uuid, at.osm_id, at.name, at.region, at.trail_type, at.surface, at.difficulty, at.source_tags,
                at.bbox_min_lng, at.bbox_max_lng, at.bbox_min_lat, at.bbox_max_lat,
                at.length_km, at.elevation_gain, at.elevation_loss, at.max_elevation, at.min_elevation, at.avg_elevation,
                at.source, at.created_at, at.updated_at, at.geometry,
                at.geometry as split_geometry,
                1 as segment_order
            FROM all_trails at
            WHERE at.app_uuid NOT IN (
                SELECT DISTINCT trail1_uuid FROM trail_intersections
                UNION
                SELECT DISTINCT trail2_uuid FROM trail_intersections
            )
        ),
        processed_trails AS (
            -- Combine both sets
            SELECT * FROM trails_with_intersections
            UNION ALL
            SELECT * FROM trails_without_intersections
        )
        SELECT 
            gen_random_uuid() as app_uuid,  -- Generate new UUID for ALL segments
            osm_id,
            name,
            region,
            trail_type,
            surface,
            difficulty,
            source_tags,
            ST_XMin(split_geometry) as bbox_min_lng,
            ST_XMax(split_geometry) as bbox_max_lng,
            ST_YMin(split_geometry) as bbox_min_lat,
            ST_YMax(split_geometry) as bbox_max_lat,
            ST_Length(split_geometry::geography) / 1000.0 as length_km,
            elevation_gain,
            elevation_loss,
            max_elevation,
            min_elevation,
            avg_elevation,
            source,
            split_geometry as geometry,
            NOW() as created_at,
            NOW() as updated_at
        FROM processed_trails pt
        WHERE ST_IsValid(pt.split_geometry)  -- Only include valid geometries
          AND pt.app_uuid IS NOT NULL    -- Ensure app_uuid is not null
    $f$, staging_schema, source_query, source_query, source_query);
    
    -- Debug: Log the full SQL
    RAISE NOTICE 'Full SQL: %', full_sql;
    
    -- Execute the SQL
    EXECUTE full_sql;
    
    GET DIAGNOSTICS split_count_var = ROW_COUNT;
    
    -- Get original count from source query
    EXECUTE format('SELECT COUNT(*) FROM (%s) t WHERE t.geometry IS NOT NULL AND ST_IsValid(t.geometry)', source_query) INTO original_count_var;
    
    -- Step 2: Detect intersections between split trail segments
    EXECUTE format($f$
        INSERT INTO %I.intersection_points (point, point_3d, connected_trail_ids, connected_trail_names, node_type, distance_meters)
        SELECT DISTINCT
            ST_Force2D(intersection_point) as point,
            ST_Force3D(intersection_point) as point_3d,
            ARRAY[t1.app_uuid, t2.app_uuid] as connected_trail_ids,
            ARRAY[t1.name, t2.name] as connected_trail_names,
            'intersection' as node_type,
            $1 as distance_meters
        FROM (
            SELECT 
                (ST_Dump(ST_Intersection(ST_Force2D(t1.geometry), ST_Force2D(t2.geometry)))).geom as intersection_point,
                t1.app_uuid as t1_uuid,
                t2.app_uuid as t2_uuid
            FROM %I.trails t1
            JOIN %I.trails t2 ON t1.id < t2.id
            WHERE ST_Intersects(t1.geometry, t2.geometry)
              AND ST_GeometryType(ST_Intersection(ST_Force2D(t1.geometry), ST_Force2D(t2.geometry))) IN ('ST_Point', 'ST_MultiPoint')
              AND ST_Length(t1.geometry::geography) > 5
              AND ST_Length(t2.geometry::geography) > 5
        ) AS intersections
    $f$, staging_schema, tolerance_meters, staging_schema, staging_schema);

    GET DIAGNOSTICS intersection_count_var = ROW_COUNT;

    -- Clear routing data in staging schema since it needs to be regenerated from split trails
    EXECUTE format('DELETE FROM %I.routing_nodes', staging_schema);
    EXECUTE format('DELETE FROM %I.routing_edges', staging_schema);

    -- Create optimized spatial indexes
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_trails_geometry ON %I.trails USING GIST(geometry)', staging_schema);
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_trails_app_uuid ON %I.trails(app_uuid)', staging_schema);
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_trails_name ON %I.trails(name)', staging_schema);
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_intersection_points ON %I.intersection_points USING GIST(point)', staging_schema);

    -- Return results
    RETURN QUERY SELECT 
        original_count_var,
        split_count_var,
        intersection_count_var,
        true as success,
        format('Successfully copied and split %s trails into %s segments with %s intersections (v12)',
               original_count_var, split_count_var, intersection_count_var) as message;

    RAISE NOTICE 'Native PostgreSQL copy and split: % original trails -> % split segments with % intersections',
        original_count_var, split_count_var, intersection_count_var;

EXCEPTION WHEN OTHERS THEN
    -- Return error information
    RETURN QUERY SELECT 
        0, 0, 0, false, 
        format('Error during copy and split (v12): %s', SQLERRM) as message;

    RAISE NOTICE 'Error during native PostgreSQL copy and split: %', SQLERRM;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.copy_and_split_trails_to_staging_native_v13(staging_schema text, region_filter text, bbox_min_lng real DEFAULT NULL::real, bbox_min_lat real DEFAULT NULL::real, bbox_max_lng real DEFAULT NULL::real, bbox_max_lat real DEFAULT NULL::real, trail_limit integer DEFAULT NULL::integer, tolerance_meters real DEFAULT 2.0)
 RETURNS TABLE(original_count integer, split_count integer, intersection_count integer, success boolean, message text)
 LANGUAGE plpgsql
AS $function$
DECLARE
    original_count_var integer := 0;
    split_count_var integer := 0;
    intersection_count_var integer := 0;
    source_query text;
    limit_clause text := '';
    source_table text := 'trails';  -- Hardcode the source table
    full_sql text;
BEGIN
    -- Debug: Log the parameters we received
    RAISE NOTICE 'Function received parameters: staging_schema=%, region_filter=%', staging_schema, region_filter;
    RAISE NOTICE 'DEBUG: staging_schema=% staging_schema type=%', staging_schema, pg_typeof(staging_schema);
    RAISE NOTICE 'FUNCTION IS BEING CALLED - V13 VERSION';
    
    -- Clear existing data
    EXECUTE format('DELETE FROM %I.trails', staging_schema);
    EXECUTE format('DELETE FROM %I.intersection_points', staging_schema);
    
    -- Build source query with filters
    source_query := format('SELECT * FROM %I WHERE region = %L', source_table, region_filter);
    
    -- Add bbox filter if provided
    IF bbox_min_lng IS NOT NULL AND bbox_min_lat IS NOT NULL AND bbox_max_lng IS NOT NULL AND bbox_max_lat IS NOT NULL THEN
        source_query := source_query || format(' AND ST_Intersects(geometry, ST_MakeEnvelope(%s, %s, %s, %s, 4326))', bbox_min_lng, bbox_min_lat, bbox_max_lng, bbox_max_lat);
    END IF;
    
    -- Add limit
    IF trail_limit IS NOT NULL THEN
        limit_clause := format(' LIMIT %s', trail_limit);
    END IF;
    
    source_query := source_query || limit_clause;
    
    -- Debug: Log the source_query
    RAISE NOTICE 'Source query: %', source_query;
    
    -- Build the full SQL dynamically to avoid parameter passing issues
    full_sql := format($f$
        INSERT INTO %I.trails (
            app_uuid, osm_id, name, region, trail_type, surface, difficulty, source_tags,
            bbox_min_lng, bbox_max_lng, bbox_min_lat, bbox_max_lat,
            length_km, elevation_gain, elevation_loss, max_elevation, min_elevation, avg_elevation, source,
            geometry, created_at, updated_at
        )
        WITH trail_intersections AS (
            -- Find all intersection points between trails using 3D coordinates
            SELECT DISTINCT
                t1.app_uuid as trail1_uuid,
                t2.app_uuid as trail2_uuid,
                ST_Intersection(t1.geometry, t2.geometry) as intersection_point
            FROM (%s) t1
            JOIN (%s) t2 ON t1.id < t2.id
            WHERE ST_Intersects(t1.geometry, t2.geometry)
              AND ST_GeometryType(ST_Intersection(t1.geometry, t2.geometry)) IN ('ST_Point', 'ST_MultiPoint')
              AND ST_Length(t1.geometry::geography) > 5
              AND ST_Length(t2.geometry::geography) > 5
        ),
        all_trails AS (
            -- Get all source trails
            SELECT 
                id, app_uuid, osm_id, name, region, trail_type, surface, difficulty, source_tags,
                bbox_min_lng, bbox_max_lng, bbox_min_lat, bbox_max_lat,
                length_km, elevation_gain, elevation_loss, max_elevation, min_elevation, avg_elevation,
                source, created_at, updated_at, geometry
            FROM (%s) t WHERE t.geometry IS NOT NULL AND ST_IsValid(t.geometry)
        ),
        trails_with_intersections AS (
            -- Get trails that have intersections and split them
            SELECT 
                at.id, at.app_uuid, at.osm_id, at.name, at.region, at.trail_type, at.surface, at.difficulty, at.source_tags,
                at.bbox_min_lng, at.bbox_max_lng, at.bbox_min_lat, at.bbox_max_lat,
                at.length_km, at.elevation_gain, at.elevation_loss, at.max_elevation, at.min_elevation, at.avg_elevation,
                at.source, at.created_at, at.updated_at, at.geometry,
                (ST_Dump(ST_Split(at.geometry, ti.intersection_point))).geom as split_geometry,
                (ST_Dump(ST_Split(at.geometry, ti.intersection_point))).path[1] as segment_order
            FROM all_trails at
            JOIN trail_intersections ti ON at.app_uuid IN (ti.trail1_uuid, ti.trail2_uuid)
        ),
        trails_without_intersections AS (
            -- Get trails that don't have intersections (keep original geometry)
            SELECT 
                at.id, at.app_uuid, at.osm_id, at.name, at.region, at.trail_type, at.surface, at.difficulty, at.source_tags,
                at.bbox_min_lng, at.bbox_max_lng, at.bbox_min_lat, at.bbox_max_lat,
                at.length_km, at.elevation_gain, at.elevation_loss, at.max_elevation, at.min_elevation, at.avg_elevation,
                at.source, at.created_at, at.updated_at, at.geometry,
                at.geometry as split_geometry,
                1 as segment_order
            FROM all_trails at
            WHERE at.app_uuid NOT IN (
                SELECT DISTINCT trail1_uuid FROM trail_intersections
                UNION
                SELECT DISTINCT trail2_uuid FROM trail_intersections
            )
        ),
        processed_trails AS (
            -- Combine both sets
            SELECT * FROM trails_with_intersections
            UNION ALL
            SELECT * FROM trails_without_intersections
        )
        SELECT 
            gen_random_uuid() as app_uuid,  -- Generate new UUID for ALL segments
            osm_id,
            name,
            region,
            trail_type,
            surface,
            difficulty,
            source_tags,
            ST_XMin(split_geometry) as bbox_min_lng,
            ST_XMax(split_geometry) as bbox_max_lng,
            ST_YMin(split_geometry) as bbox_min_lat,
            ST_YMax(split_geometry) as bbox_max_lat,
            ST_Length(split_geometry::geography) / 1000.0 as length_km,
            elevation_gain,
            elevation_loss,
            max_elevation,
            min_elevation,
            avg_elevation,
            source,
            split_geometry as geometry,
            NOW() as created_at,
            NOW() as updated_at
        FROM processed_trails pt
        WHERE ST_IsValid(pt.split_geometry)  -- Only include valid geometries
          AND pt.app_uuid IS NOT NULL    -- Ensure app_uuid is not null
    $f$, staging_schema, source_query, source_query, source_query);
    
    -- Debug: Log the full SQL
    RAISE NOTICE 'Full SQL: %', full_sql;
    
    -- Execute the SQL
    EXECUTE full_sql;
    
    GET DIAGNOSTICS split_count_var = ROW_COUNT;
    
    -- Get original count from source query
    EXECUTE format('SELECT COUNT(*) FROM (%s) t WHERE t.geometry IS NOT NULL AND ST_IsValid(t.geometry)', source_query) INTO original_count_var;
    
    -- Step 2: Detect intersections between split trail segments
    EXECUTE format($f$
        INSERT INTO %I.intersection_points (point, point_3d, connected_trail_ids, connected_trail_names, node_type, distance_meters)
        SELECT DISTINCT
            ST_Force2D(intersection_point) as point,
            ST_Force3D(intersection_point) as point_3d,
            ARRAY[t1.app_uuid, t2.app_uuid] as connected_trail_ids,
            ARRAY[t1.name, t2.name] as connected_trail_names,
            'intersection' as node_type,
            $1 as distance_meters
        FROM (
            SELECT 
                (ST_Dump(ST_Intersection(ST_Force2D(t1.geometry), ST_Force2D(t2.geometry)))).geom as intersection_point,
                t1.app_uuid as t1_uuid,
                t2.app_uuid as t2_uuid
            FROM %I.trails t1
            JOIN %I.trails t2 ON t1.id < t2.id
            WHERE ST_Intersects(t1.geometry, t2.geometry)
              AND ST_GeometryType(ST_Intersection(ST_Force2D(t1.geometry), ST_Force2D(t2.geometry))) IN ('ST_Point', 'ST_MultiPoint')
              AND ST_Length(t1.geometry::geography) > 5
              AND ST_Length(t2.geometry::geography) > 5
        ) AS intersections
    $f$, staging_schema, tolerance_meters, staging_schema, staging_schema);

    GET DIAGNOSTICS intersection_count_var = ROW_COUNT;

    -- Clear routing data in staging schema since it needs to be regenerated from split trails
    EXECUTE format('DELETE FROM %I.routing_nodes', staging_schema);
    EXECUTE format('DELETE FROM %I.routing_edges', staging_schema);

    -- Create optimized spatial indexes
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_trails_geometry ON %I.trails USING GIST(geometry)', staging_schema);
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_trails_app_uuid ON %I.trails(app_uuid)', staging_schema);
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_trails_name ON %I.trails(name)', staging_schema);
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_intersection_points ON %I.intersection_points USING GIST(point)', staging_schema);

    -- Return results
    RETURN QUERY SELECT 
        original_count_var,
        split_count_var,
        intersection_count_var,
        true as success,
        format('Successfully copied and split %s trails into %s segments with %s intersections (v13)',
               original_count_var, split_count_var, intersection_count_var) as message;

    RAISE NOTICE 'Native PostgreSQL copy and split: % original trails -> % split segments with % intersections',
        original_count_var, split_count_var, intersection_count_var;

EXCEPTION WHEN OTHERS THEN
    -- Return error information
    RETURN QUERY SELECT 
        0, 0, 0, false, 
        format('Error during copy and split (v13): %s', SQLERRM) as message;

    RAISE NOTICE 'Error during native PostgreSQL copy and split: %', SQLERRM;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.copy_and_split_trails_to_staging_native_v14(staging_schema text, region_filter text, bbox_min_lng real DEFAULT NULL::real, bbox_min_lat real DEFAULT NULL::real, bbox_max_lng real DEFAULT NULL::real, bbox_max_lat real DEFAULT NULL::real, trail_limit integer DEFAULT NULL::integer, tolerance_meters real DEFAULT 2.0)
 RETURNS TABLE(original_count integer, split_count integer, intersection_count integer, success boolean, message text)
 LANGUAGE plpgsql
AS $function$
DECLARE
    original_count_var integer := 0;
    split_count_var integer := 0;
    intersection_count_var integer := 0;
    source_query text;
    limit_clause text := '';
    source_table text := 'trails';  -- Hardcode the source table
    full_sql text;
BEGIN
    -- Debug: Log the parameters we received
    RAISE NOTICE 'Function received parameters: staging_schema=%, region_filter=%', staging_schema, region_filter;
    RAISE NOTICE 'DEBUG: staging_schema=% staging_schema type=%', staging_schema, pg_typeof(staging_schema);
    RAISE NOTICE 'FUNCTION IS BEING CALLED - V14 VERSION';
    RAISE NOTICE 'DEBUG: source_table=%', source_table;
    
    -- Clear existing data
    EXECUTE format('DELETE FROM %I.trails', staging_schema);
    EXECUTE format('DELETE FROM %I.intersection_points', staging_schema);
    
    -- Build source query with filters - explicitly use hardcoded table name
    source_query := format('SELECT * FROM trails WHERE region = %L', region_filter);
    
    -- Add bbox filter if provided
    IF bbox_min_lng IS NOT NULL AND bbox_min_lat IS NOT NULL AND bbox_max_lng IS NOT NULL AND bbox_max_lat IS NOT NULL THEN
        source_query := source_query || format(' AND ST_Intersects(geometry, ST_MakeEnvelope(%s, %s, %s, %s, 4326))', bbox_min_lng, bbox_min_lat, bbox_max_lng, bbox_max_lat);
    END IF;
    
    -- Add limit
    IF trail_limit IS NOT NULL THEN
        limit_clause := format(' LIMIT %s', trail_limit);
    END IF;
    
    source_query := source_query || limit_clause;
    
    -- Debug: Log the source_query
    RAISE NOTICE 'Source query: %', source_query;
    
    -- Build the full SQL dynamically to avoid parameter passing issues
    full_sql := format($f$
        INSERT INTO %I.trails (
            app_uuid, osm_id, name, region, trail_type, surface, difficulty, source_tags,
            bbox_min_lng, bbox_max_lng, bbox_min_lat, bbox_max_lat,
            length_km, elevation_gain, elevation_loss, max_elevation, min_elevation, avg_elevation, source,
            geometry, created_at, updated_at
        )
        WITH trail_intersections AS (
            -- Find all intersection points between trails using 3D coordinates
            SELECT DISTINCT
                t1.app_uuid as trail1_uuid,
                t2.app_uuid as trail2_uuid,
                ST_Intersection(t1.geometry, t2.geometry) as intersection_point
            FROM (%s) t1
            JOIN (%s) t2 ON t1.id < t2.id
            WHERE ST_Intersects(t1.geometry, t2.geometry)
              AND ST_GeometryType(ST_Intersection(t1.geometry, t2.geometry)) IN ('ST_Point', 'ST_MultiPoint')
              AND ST_Length(t1.geometry::geography) > 5
              AND ST_Length(t2.geometry::geography) > 5
        ),
        all_trails AS (
            -- Get all source trails
            SELECT 
                id, app_uuid, osm_id, name, region, trail_type, surface, difficulty, source_tags,
                bbox_min_lng, bbox_max_lng, bbox_min_lat, bbox_max_lat,
                length_km, elevation_gain, elevation_loss, max_elevation, min_elevation, avg_elevation,
                source, created_at, updated_at, geometry
            FROM (%s) t WHERE t.geometry IS NOT NULL AND ST_IsValid(t.geometry)
        ),
        trails_with_intersections AS (
            -- Get trails that have intersections and split them
            SELECT 
                at.id, at.app_uuid, at.osm_id, at.name, at.region, at.trail_type, at.surface, at.difficulty, at.source_tags,
                at.bbox_min_lng, at.bbox_max_lng, at.bbox_min_lat, at.bbox_max_lat,
                at.length_km, at.elevation_gain, at.elevation_loss, at.max_elevation, at.min_elevation, at.avg_elevation,
                at.source, at.created_at, at.updated_at, at.geometry,
                (ST_Dump(ST_Split(at.geometry, ti.intersection_point))).geom as split_geometry,
                (ST_Dump(ST_Split(at.geometry, ti.intersection_point))).path[1] as segment_order
            FROM all_trails at
            JOIN trail_intersections ti ON at.app_uuid IN (ti.trail1_uuid, ti.trail2_uuid)
        ),
        trails_without_intersections AS (
            -- Get trails that don't have intersections (keep original geometry)
            SELECT 
                at.id, at.app_uuid, at.osm_id, at.name, at.region, at.trail_type, at.surface, at.difficulty, at.source_tags,
                at.bbox_min_lng, at.bbox_max_lng, at.bbox_min_lat, at.bbox_max_lat,
                at.length_km, at.elevation_gain, at.elevation_loss, at.max_elevation, at.min_elevation, at.avg_elevation,
                at.source, at.created_at, at.updated_at, at.geometry,
                at.geometry as split_geometry,
                1 as segment_order
            FROM all_trails at
            WHERE at.app_uuid NOT IN (
                SELECT DISTINCT trail1_uuid FROM trail_intersections
                UNION
                SELECT DISTINCT trail2_uuid FROM trail_intersections
            )
        ),
        processed_trails AS (
            -- Combine both sets
            SELECT * FROM trails_with_intersections
            UNION ALL
            SELECT * FROM trails_without_intersections
        )
        SELECT 
            gen_random_uuid() as app_uuid,  -- Generate new UUID for ALL segments
            osm_id,
            name,
            region,
            trail_type,
            surface,
            difficulty,
            source_tags,
            ST_XMin(split_geometry) as bbox_min_lng,
            ST_XMax(split_geometry) as bbox_max_lng,
            ST_YMin(split_geometry) as bbox_min_lat,
            ST_YMax(split_geometry) as bbox_max_lat,
            ST_Length(split_geometry::geography) / 1000.0 as length_km,
            elevation_gain,
            elevation_loss,
            max_elevation,
            min_elevation,
            avg_elevation,
            source,
            split_geometry as geometry,
            NOW() as created_at,
            NOW() as updated_at
        FROM processed_trails pt
        WHERE ST_IsValid(pt.split_geometry)  -- Only include valid geometries
          AND pt.app_uuid IS NOT NULL    -- Ensure app_uuid is not null
    $f$, staging_schema, source_query, source_query, source_query);
    
    -- Debug: Log the full SQL
    RAISE NOTICE 'Full SQL: %', full_sql;
    
    -- Execute the SQL
    EXECUTE full_sql;
    
    GET DIAGNOSTICS split_count_var = ROW_COUNT;
    
    -- Get original count from source query
    EXECUTE format('SELECT COUNT(*) FROM (%s) t WHERE t.geometry IS NOT NULL AND ST_IsValid(t.geometry)', source_query) INTO original_count_var;
    
    -- Step 2: Detect intersections between split trail segments
    EXECUTE format($f$
        INSERT INTO %I.intersection_points (point, point_3d, connected_trail_ids, connected_trail_names, node_type, distance_meters)
        SELECT DISTINCT
            ST_Force2D(intersection_point) as point,
            ST_Force3D(intersection_point) as point_3d,
            ARRAY[t1.app_uuid, t2.app_uuid] as connected_trail_ids,
            ARRAY[t1.name, t2.name] as connected_trail_names,
            'intersection' as node_type,
            $1 as distance_meters
        FROM (
            SELECT 
                (ST_Dump(ST_Intersection(ST_Force2D(t1.geometry), ST_Force2D(t2.geometry)))).geom as intersection_point,
                t1.app_uuid as t1_uuid,
                t2.app_uuid as t2_uuid
            FROM %I.trails t1
            JOIN %I.trails t2 ON t1.id < t2.id
            WHERE ST_Intersects(t1.geometry, t2.geometry)
              AND ST_GeometryType(ST_Intersection(ST_Force2D(t1.geometry), ST_Force2D(t2.geometry))) IN ('ST_Point', 'ST_MultiPoint')
              AND ST_Length(t1.geometry::geography) > 5
              AND ST_Length(t2.geometry::geography) > 5
        ) AS intersections
    $f$, staging_schema, tolerance_meters, staging_schema, staging_schema);

    GET DIAGNOSTICS intersection_count_var = ROW_COUNT;

    -- Clear routing data in staging schema since it needs to be regenerated from split trails
    EXECUTE format('DELETE FROM %I.routing_nodes', staging_schema);
    EXECUTE format('DELETE FROM %I.routing_edges', staging_schema);

    -- Create optimized spatial indexes
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_trails_geometry ON %I.trails USING GIST(geometry)', staging_schema);
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_trails_app_uuid ON %I.trails(app_uuid)', staging_schema);
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_trails_name ON %I.trails(name)', staging_schema);
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_intersection_points ON %I.intersection_points USING GIST(point)', staging_schema);

    -- Return results
    RETURN QUERY SELECT 
        original_count_var,
        split_count_var,
        intersection_count_var,
        true as success,
        format('Successfully copied and split %s trails into %s segments with %s intersections (v14)',
               original_count_var, split_count_var, intersection_count_var) as message;

    RAISE NOTICE 'Native PostgreSQL copy and split: % original trails -> % split segments with % intersections',
        original_count_var, split_count_var, intersection_count_var;

EXCEPTION WHEN OTHERS THEN
    -- Return error information
    RETURN QUERY SELECT 
        0, 0, 0, false, 
        format('Error during copy and split (v14): %s', SQLERRM) as message;

    RAISE NOTICE 'Error during native PostgreSQL copy and split: %', SQLERRM;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.copy_and_split_trails_to_staging_native_v15(staging_schema text, source_table text, region_filter text, bbox_min_lng numeric, bbox_min_lat numeric, bbox_max_lng numeric, bbox_max_lat numeric, trail_limit integer, tolerance_meters numeric)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
    source_query text;
    full_sql text;
    result_count integer := 0;
BEGIN
    RAISE NOTICE 'DEBUG V15: Function called with parameters:';
    RAISE NOTICE '  staging_schema: %', staging_schema;
    RAISE NOTICE '  source_table: %', source_table;
    RAISE NOTICE '  region_filter: %', region_filter;
    RAISE NOTICE '  bbox_min_lng: %', bbox_min_lng;
    RAISE NOTICE '  bbox_min_lat: %', bbox_min_lat;
    RAISE NOTICE '  bbox_max_lng: %', bbox_max_lng;
    RAISE NOTICE '  bbox_max_lat: %', bbox_max_lat;
    RAISE NOTICE '  trail_limit: %', trail_limit;
    RAISE NOTICE '  tolerance_meters: %', tolerance_meters;
    
    -- Build source query using format() with %I for table name
    source_query := format('SELECT * FROM %I WHERE region = %L', source_table, region_filter);
    
    RAISE NOTICE 'DEBUG V15: source_query: %', source_query;
    
    -- Build full SQL using format() to handle nulls properly
    full_sql := format($f$
        WITH source_trails AS (
            %s
        ),
        split_trails AS (
            SELECT 
                gen_random_uuid() as app_uuid,
                osm_id,
                name,
                region,
                ST_GeometryType(geometry) as geometry_type,
                geometry,
                elevation,
                length_meters,
                created_at,
                updated_at
            FROM source_trails
            WHERE ST_Intersects(geometry, ST_MakeEnvelope(%s, %s, %s, %s, 4326))
            LIMIT %s
        ),
        intersection_splits AS (
            SELECT 
                t.app_uuid,
                t.osm_id,
                t.name,
                t.region,
                t.geometry_type,
                CASE 
                    WHEN ST_Intersects(t.geometry, i.geometry) THEN 
                        ST_Split(t.geometry, i.geometry)
                    ELSE 
                        t.geometry
                END as split_geometry,
                t.elevation,
                t.length_meters,
                t.created_at,
                t.updated_at
            FROM split_trails t
            CROSS JOIN (
                SELECT geometry 
                FROM %I 
                WHERE region = %L
                AND ST_Intersects(geometry, ST_MakeEnvelope(%s, %s, %s, %s, 4326))
                AND ST_DWithin(geometry, ST_MakeEnvelope(%s, %s, %s, %s, 4326), %s)
            ) i
        )
        INSERT INTO %I.trails (
            app_uuid, osm_id, name, region, geometry_type, geometry, elevation, length_meters, created_at, updated_at
        )
        SELECT 
            gen_random_uuid() as app_uuid,
            osm_id,
            name,
            region,
            geometry_type,
            split_geometry as geometry,
            elevation,
            length_meters,
            created_at,
            updated_at
        FROM intersection_splits
        WHERE ST_GeometryType(split_geometry) = 'LINESTRING'
        AND ST_Length(split_geometry) > 0;
    $f$, 
        source_query,
        bbox_min_lng, bbox_min_lat, bbox_max_lng, bbox_max_lat, trail_limit,
        source_table, region_filter,
        bbox_min_lng, bbox_min_lat, bbox_max_lng, bbox_max_lat,
        bbox_min_lng, bbox_min_lat, bbox_max_lng, bbox_max_lat, tolerance_meters,
        staging_schema
    );
    
    RAISE NOTICE 'DEBUG V15: About to execute full_sql';
    RAISE NOTICE 'DEBUG V15: full_sql: %', full_sql;
    
    EXECUTE full_sql;
    
    GET DIAGNOSTICS result_count = ROW_COUNT;
    
    RAISE NOTICE 'DEBUG V15: Inserted % rows', result_count;
    
    RETURN result_count;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.copy_and_split_trails_to_staging_native_v15(staging_schema text, region_filter text, bbox_min_lng numeric, bbox_min_lat numeric, bbox_max_lng numeric, bbox_max_lat numeric, trail_limit integer, tolerance_meters numeric)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
    source_query text;
    full_sql text;
    result_count integer := 0;
BEGIN
    RAISE NOTICE 'DEBUG V15: Function called with parameters:';
    RAISE NOTICE '  staging_schema: %', staging_schema;
    RAISE NOTICE '  region_filter: %', region_filter;
    RAISE NOTICE '  bbox_min_lng: %', bbox_min_lng;
    RAISE NOTICE '  bbox_min_lat: %', bbox_min_lat;
    RAISE NOTICE '  bbox_max_lng: %', bbox_max_lng;
    RAISE NOTICE '  bbox_max_lat: %', bbox_max_lat;
    RAISE NOTICE '  trail_limit: %', trail_limit;
    RAISE NOTICE '  tolerance_meters: %', tolerance_meters;
    
    -- Build source query using format() to handle nulls properly
    source_query := format('SELECT * FROM trails WHERE region = %L', region_filter);
    
    RAISE NOTICE 'DEBUG V15: source_query: %', source_query;
    
    -- Build full SQL using format() to handle nulls properly
    full_sql := format($f$
        WITH source_trails AS (
            %s
        ),
        split_trails AS (
            SELECT 
                gen_random_uuid() as app_uuid,
                osm_id,
                name,
                region,
                ST_GeometryType(geometry) as geometry_type,
                geometry,
                elevation,
                length_meters,
                created_at,
                updated_at
            FROM source_trails
            WHERE ST_Intersects(geometry, ST_MakeEnvelope(%s, %s, %s, %s, 4326))
            LIMIT %s
        ),
        intersection_splits AS (
            SELECT 
                t.app_uuid,
                t.osm_id,
                t.name,
                t.region,
                t.geometry_type,
                CASE 
                    WHEN ST_Intersects(t.geometry, i.geometry) THEN 
                        ST_Split(t.geometry, i.geometry)
                    ELSE 
                        t.geometry
                END as split_geometry,
                t.elevation,
                t.length_meters,
                t.created_at,
                t.updated_at
            FROM split_trails t
            CROSS JOIN (
                SELECT geometry 
                FROM trails 
                WHERE region = %L
                AND ST_Intersects(geometry, ST_MakeEnvelope(%s, %s, %s, %s, 4326))
                AND ST_DWithin(geometry, ST_MakeEnvelope(%s, %s, %s, %s, 4326), %s)
            ) i
        )
        INSERT INTO %I.trails (
            app_uuid, osm_id, name, region, geometry_type, geometry, elevation, length_meters, created_at, updated_at
        )
        SELECT 
            gen_random_uuid() as app_uuid,
            osm_id,
            name,
            region,
            geometry_type,
            split_geometry as geometry,
            elevation,
            length_meters,
            created_at,
            updated_at
        FROM intersection_splits
        WHERE ST_GeometryType(split_geometry) = 'LINESTRING'
        AND ST_Length(split_geometry) > 0;
    $f$, 
        source_query,
        bbox_min_lng, bbox_min_lat, bbox_max_lng, bbox_max_lat, trail_limit,
        region_filter,
        bbox_min_lng, bbox_min_lat, bbox_max_lng, bbox_max_lat,
        bbox_min_lng, bbox_min_lat, bbox_max_lng, bbox_max_lat, tolerance_meters,
        staging_schema
    );
    
    RAISE NOTICE 'DEBUG V15: About to execute full_sql';
    RAISE NOTICE 'DEBUG V15: full_sql: %', full_sql;
    
    EXECUTE full_sql;
    
    GET DIAGNOSTICS result_count = ROW_COUNT;
    
    RAISE NOTICE 'DEBUG V15: Inserted % rows', result_count;
    
    RETURN result_count;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.copy_and_split_trails_to_staging_native_v16(staging_schema text, source_table text, region_filter text, bbox_min_lng numeric DEFAULT NULL::numeric, bbox_min_lat numeric DEFAULT NULL::numeric, bbox_max_lng numeric DEFAULT NULL::numeric, bbox_max_lat numeric DEFAULT NULL::numeric, trail_limit integer DEFAULT NULL::integer, tolerance_meters numeric DEFAULT 1.0)
 RETURNS TABLE(original_count integer, split_count integer, intersection_count integer, success boolean, message text)
 LANGUAGE plpgsql
AS $function$
DECLARE
    original_count_var integer := 0;
    split_count_var integer := 0;
    intersection_count_var integer := 0;
    source_query text;
    limit_clause text := '';
BEGIN
    RAISE NOTICE 'DEBUG V16: Function called with parameters:';
    RAISE NOTICE '  staging_schema: %', staging_schema;
    RAISE NOTICE '  source_table: %', source_table;
    RAISE NOTICE '  region_filter: %', region_filter;
    RAISE NOTICE '  bbox_min_lng: %', bbox_min_lng;
    RAISE NOTICE '  bbox_min_lat: %', bbox_min_lat;
    RAISE NOTICE '  bbox_max_lng: %', bbox_max_lng;
    RAISE NOTICE '  bbox_max_lat: %', bbox_max_lat;
    RAISE NOTICE '  trail_limit: %', trail_limit;
    RAISE NOTICE '  tolerance_meters: %', tolerance_meters;

    -- Clear existing data
    EXECUTE format('DELETE FROM %I.trails', staging_schema);
    EXECUTE format('DELETE FROM %I.intersection_points', staging_schema);

    -- Build source query with filters
    source_query := format('SELECT * FROM %I WHERE region = %L', source_table, region_filter);

    -- Add bbox filter if provided
    IF bbox_min_lng IS NOT NULL AND bbox_min_lat IS NOT NULL AND bbox_max_lng IS NOT NULL AND bbox_max_lat IS NOT NULL THEN
        source_query := source_query || format(' AND ST_Intersects(geometry, ST_MakeEnvelope(%s, %s, %s, %s, 4326))', bbox_min_lng, bbox_min_lat, bbox_max_lng, bbox_max_lat);
    END IF;

    -- Add limit
    IF trail_limit IS NOT NULL THEN
        limit_clause := format(' LIMIT %s', trail_limit);
    END IF;

    source_query := source_query || limit_clause;

    RAISE NOTICE 'DEBUG V16: source_query: %', source_query;

    -- Step 1: Copy and split trails using native PostGIS ST_Split
    EXECUTE format($f$
        INSERT INTO %I.trails (
            app_uuid, osm_id, name, region, trail_type, surface, difficulty, source_tags,
            bbox_min_lng, bbox_max_lng, bbox_min_lat, bbox_max_lat,
            length_km, elevation_gain, elevation_loss, max_elevation, min_elevation, avg_elevation, source,
            geometry, created_at, updated_at
        )
        WITH trail_intersections AS (
            SELECT DISTINCT
                t1.app_uuid as trail1_uuid,
                t2.app_uuid as trail2_uuid,
                ST_Intersection(t1.geometry, t2.geometry) as intersection_point
            FROM (%s) t1
            JOIN (%s) t2 ON t1.id < t2.id
            WHERE ST_Intersects(t1.geometry, t2.geometry)
              AND ST_GeometryType(ST_Intersection(t1.geometry, t2.geometry)) IN ('ST_Point', 'ST_MultiPoint')
              AND ST_Length(t1.geometry::geography) > 5
              AND ST_Length(t2.geometry::geography) > 5
        ),
        all_trails AS (
            SELECT
                id, app_uuid, osm_id, name, region, trail_type, surface, difficulty, source_tags,
                bbox_min_lng, bbox_max_lng, bbox_min_lat, bbox_max_lat,
                length_km, elevation_gain, elevation_loss, max_elevation, min_elevation, avg_elevation,
                source, created_at, updated_at, geometry
            FROM (%s) t WHERE t.geometry IS NOT NULL AND ST_IsValid(t.geometry)
        ),
        trails_with_intersections AS (
            SELECT
                at.id, at.app_uuid, at.osm_id, at.name, at.region, at.trail_type, at.surface, at.difficulty, at.source_tags,
                at.bbox_min_lng, at.bbox_max_lng, at.bbox_min_lat, at.bbox_max_lat,
                at.length_km, at.elevation_gain, at.elevation_loss, at.max_elevation, at.min_elevation, at.avg_elevation,
                at.source, at.created_at, at.updated_at, at.geometry,
                (ST_Dump(ST_Split(at.geometry, ti.intersection_point))).geom as split_geometry,
                (ST_Dump(ST_Split(at.geometry, ti.intersection_point))).path[1] as segment_order
            FROM all_trails at
            JOIN trail_intersections ti ON at.app_uuid IN (ti.trail1_uuid, ti.trail2_uuid)
        ),
        trails_without_intersections AS (
            SELECT
                at.id, at.app_uuid, at.osm_id, at.name, at.region, at.trail_type, at.surface, at.difficulty, at.source_tags,
                at.bbox_min_lng, at.bbox_max_lng, at.bbox_min_lat, at.bbox_max_lat,
                at.length_km, at.elevation_gain, at.elevation_loss, at.max_elevation, at.min_elevation, at.avg_elevation,
                at.source, at.created_at, at.updated_at, at.geometry,
                at.geometry as split_geometry,
                1 as segment_order
            FROM all_trails at
            WHERE at.app_uuid NOT IN (
                SELECT DISTINCT trail1_uuid FROM trail_intersections
                UNION
                SELECT DISTINCT trail2_uuid FROM trail_intersections
            )
        ),
        processed_trails AS (
            SELECT * FROM trails_with_intersections
            UNION ALL
            SELECT * FROM trails_without_intersections
        )
        SELECT
            gen_random_uuid() as app_uuid,
            osm_id,
            name,
            region,
            trail_type,
            surface,
            difficulty,
            source_tags,
            ST_XMin(split_geometry) as bbox_min_lng,
            ST_XMax(split_geometry) as bbox_max_lng,
            ST_YMin(split_geometry) as bbox_min_lat,
            ST_YMax(split_geometry) as bbox_max_lat,
            ST_Length(split_geometry::geography) / 1000.0 as length_km,
            elevation_gain,
            elevation_loss,
            max_elevation,
            min_elevation,
            avg_elevation,
            source,
            split_geometry as geometry,
            NOW() as created_at,
            NOW() as updated_at
        FROM processed_trails pt
        WHERE ST_IsValid(pt.split_geometry)
          AND pt.app_uuid IS NOT NULL
    $f$, staging_schema, source_query, source_query, source_query);

    GET DIAGNOSTICS split_count_var = ROW_COUNT;

    -- Get original count from source query
    EXECUTE format('SELECT COUNT(*) FROM (%s) t WHERE t.geometry IS NOT NULL AND ST_IsValid(t.geometry)', source_query) INTO original_count_var;

    -- Step 2: Detect intersections between split trail segments
    PERFORM detect_trail_intersections(staging_schema, 'trails', tolerance_meters);

    -- Get intersection count
    EXECUTE format('SELECT COUNT(*) FROM %I.intersection_points', staging_schema) INTO intersection_count_var;

    -- Clear routing data in staging schema since it needs to be regenerated from split trails
    EXECUTE format('DELETE FROM %I.routing_nodes', staging_schema);
    EXECUTE format('DELETE FROM %I.routing_edges', staging_schema);

    -- Create optimized spatial indexes
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_trails_geometry ON %I.trails USING GIST(geometry)', staging_schema);
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_trails_app_uuid ON %I.trails(app_uuid)', staging_schema);
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_trails_name ON %I.trails(name)', staging_schema);
    EXECUTE format('CREATE INDEX IF NOT EXISTS idx_intersection_points ON %I.intersection_points USING GIST(point)', staging_schema);

    -- Return results
    RETURN QUERY SELECT
        original_count_var,
        split_count_var,
        intersection_count_var,
        true as success,
        format('Successfully copied and split %s trails into %s segments with %s intersections',
               original_count_var, split_count_var, intersection_count_var) as message;

    RAISE NOTICE 'Native PostgreSQL copy and split: % original trails -> % split segments with % intersections',
        original_count_var, split_count_var, intersection_count_var;

EXCEPTION WHEN OTHERS THEN
    -- Return error information
    RETURN QUERY SELECT
        0, 0, 0, false,
        format('Error during copy and split: %s', SQLERRM) as message;

    RAISE NOTICE 'Error during native PostgreSQL copy and split: %', SQLERRM;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.detect_trail_intersections(staging_schema text, tolerance_meters real DEFAULT 1.0)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    intersection_count integer := 0;
BEGIN
    EXECUTE format('DELETE FROM %I.intersection_points', staging_schema);
    EXECUTE format($f$
        INSERT INTO %I.intersection_points (point, point_3d, connected_trail_ids, connected_trail_names, node_type, distance_meters)
        SELECT DISTINCT
            ST_Force2D(intersection_point) as point,
            ST_Force3D(intersection_point) as point_3d,
            ARRAY[t1.app_uuid, t2.app_uuid] as connected_trail_ids,
            ARRAY[t1.name, t2.name] as connected_trail_names,
            'intersection' as node_type,
            $1 as distance_meters
        FROM (
            SELECT 
                (ST_Dump(ST_Intersection(ST_Force2D(t1.geometry), ST_Force2D(t2.geometry)))).geom as intersection_point,
                t1.app_uuid as t1_uuid,
                t2.app_uuid as t2_uuid
            FROM %I.trails t1
            JOIN %I.trails t2 ON t1.id < t2.id
            WHERE ST_Intersects(t1.geometry, t2.geometry)
              AND ST_GeometryType(ST_Intersection(ST_Force2D(t1.geometry), ST_Force2D(t2.geometry))) IN ('ST_Point', 'ST_MultiPoint')
              AND ST_Length(t1.geometry::geography) > 5  -- Reduced from 10 to 5 meters
              AND ST_Length(t2.geometry::geography) > 5  -- Reduced from 10 to 5 meters
        ) intersections
        JOIN %I.trails t1 ON t1.app_uuid = intersections.t1_uuid
        JOIN %I.trails t2 ON t2.app_uuid = intersections.t2_uuid
        WHERE ST_Length(intersection_point::geography) = 0  -- Point intersections only
    $f$, staging_schema, staging_schema, staging_schema, staging_schema, staging_schema) USING tolerance_meters;
    GET DIAGNOSTICS intersection_count = ROW_COUNT;
    RAISE NOTICE 'Detected % intersection points', intersection_count;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.detect_trail_intersections(trails_schema text, trails_table text, intersection_tolerance_meters double precision DEFAULT 2.0)
 RETURNS TABLE(intersection_point geometry, intersection_point_3d geometry, connected_trail_ids integer[], connected_trail_names text[], node_type text, distance_meters double precision)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY EXECUTE format('
        WITH noded_trails AS (
            SELECT id, name, (ST_Dump(ST_Node(geometry))).geom as noded_geom
            FROM %I.%I
            WHERE geometry IS NOT NULL AND ST_IsValid(geometry)
        ),
        true_intersections AS (
            SELECT 
                ST_Intersection(t1.noded_geom, t2.noded_geom) as intersection_point,
                ST_Force3D(ST_Intersection(t1.noded_geom, t2.noded_geom)) as intersection_point_3d,
                ARRAY[t1.id, t2.id] as connected_trail_ids,
                ARRAY[t1.name, t2.name] as connected_trail_names,
                ''intersection'' as node_type,
                ST_Distance(t1.noded_geom::geography, t2.noded_geom::geography) as distance_meters
            FROM noded_trails t1
            JOIN noded_trails t2 ON t1.id < t2.id
            WHERE ST_Intersects(t1.noded_geom, t2.noded_geom)
            AND ST_GeometryType(ST_Intersection(t1.noded_geom, t2.noded_geom)) = ''ST_Point''
        )
        SELECT 
            intersection_point,
            intersection_point_3d,
            connected_trail_ids,
            connected_trail_names,
            node_type,
            distance_meters
        FROM true_intersections
        WHERE distance_meters <= $1
    ', trails_schema, trails_table) USING intersection_tolerance_meters;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.difference(text, text)
 RETURNS integer
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/fuzzystrmatch', $function$difference$function$
;

CREATE OR REPLACE FUNCTION public.dmetaphone(text)
 RETURNS text
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/fuzzystrmatch', $function$dmetaphone$function$
;

CREATE OR REPLACE FUNCTION public.dmetaphone_alt(text)
 RETURNS text
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/fuzzystrmatch', $function$dmetaphone_alt$function$
;

CREATE OR REPLACE FUNCTION public.dropgeometrycolumn(catalog_name character varying, schema_name character varying, table_name character varying, column_name character varying)
 RETURNS text
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
	myrec RECORD;
	okay boolean;
	real_schema name;
BEGIN
	IF ( schema_name != '' ) THEN
		okay = false;
		FOR myrec IN SELECT nspname FROM pg_namespace WHERE text(nspname) = schema_name LOOP
			okay := true;
		END LOOP;
		IF ( okay <>  true ) THEN
			RAISE NOTICE 'Invalid schema name - using current_schema()';
			SELECT current_schema() into real_schema;
		ELSE
			real_schema = schema_name;
		END IF;
	ELSE
		SELECT current_schema() into real_schema;
	END IF;
	okay = false;
	FOR myrec IN SELECT * from public.geometry_columns where f_table_schema = text(real_schema) and f_table_name = table_name and f_geometry_column = column_name LOOP
		okay := true;
	END LOOP;
	IF (okay <> true) THEN
		RAISE EXCEPTION 'column not found in geometry_columns table';
		RETURN false;
	END IF;
	EXECUTE 'ALTER TABLE ' || quote_ident(real_schema) || '.' ||
		quote_ident(table_name) || ' DROP COLUMN ' ||
		quote_ident(column_name);
	RETURN real_schema || '.' || table_name || '.' || column_name ||' effectively removed.';
END;
$function$
;

CREATE OR REPLACE FUNCTION public.dropgeometrycolumn(schema_name character varying, table_name character varying, column_name character varying)
 RETURNS text
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
	ret text;
BEGIN
	SELECT public.DropGeometryColumn('',$1,$2,$3) into ret;
	RETURN ret;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.dropgeometrycolumn(table_name character varying, column_name character varying)
 RETURNS text
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
	ret text;
BEGIN
	SELECT public.DropGeometryColumn('','',$1,$2) into ret;
	RETURN ret;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.dropgeometrytable(schema_name character varying, table_name character varying)
 RETURNS text
 LANGUAGE sql
 STRICT
AS $function$ SELECT public.DropGeometryTable('',$1,$2) $function$
;

CREATE OR REPLACE FUNCTION public.dropgeometrytable(catalog_name character varying, schema_name character varying, table_name character varying)
 RETURNS text
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
	real_schema name;
BEGIN
	IF ( schema_name = '' ) THEN
		SELECT current_schema() into real_schema;
	ELSE
		real_schema = schema_name;
	END IF;
	EXECUTE 'DROP TABLE IF EXISTS '
		|| quote_ident(real_schema) || '.' ||
		quote_ident(table_name) || ' RESTRICT';
	RETURN
		real_schema || '.' ||
		table_name ||' dropped.';
END;
$function$
;

CREATE OR REPLACE FUNCTION public.dropgeometrytable(table_name character varying)
 RETURNS text
 LANGUAGE sql
 STRICT
AS $function$ SELECT public.DropGeometryTable('','',$1) $function$
;

CREATE OR REPLACE FUNCTION public.dropoverviewconstraints(ovschema name, ovtable name, ovcolumn name)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	DECLARE
		schema name;
		sql text;
		rtn boolean;
	BEGIN
		schema := NULL;
		IF length($1) > 0 THEN
			sql := 'SELECT nspname FROM pg_namespace '
				|| 'WHERE nspname = ' || quote_literal($1)
				|| 'LIMIT 1';
			EXECUTE sql INTO schema;
			IF schema IS NULL THEN
				RAISE EXCEPTION 'The value provided for schema is invalid';
				RETURN FALSE;
			END IF;
		END IF;
		IF schema IS NULL THEN
			sql := 'SELECT n.nspname AS schemaname '
				|| 'FROM pg_catalog.pg_class c '
				|| 'JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace '
				|| 'WHERE c.relkind = ' || quote_literal('r')
				|| ' AND n.nspname NOT IN (' || quote_literal('pg_catalog')
				|| ', ' || quote_literal('pg_toast')
				|| ') AND pg_catalog.pg_table_is_visible(c.oid)'
				|| ' AND c.relname = ' || quote_literal($2);
			EXECUTE sql INTO schema;
			IF schema IS NULL THEN
				RAISE EXCEPTION 'The table % does not occur in the search_path', quote_literal($2);
				RETURN FALSE;
			END IF;
		END IF;
		rtn :=  public._drop_overview_constraint(schema, $2, $3);
		IF rtn IS FALSE THEN
			RAISE EXCEPTION 'Unable to drop the overview constraint .  Is the schema name, table name or column name incorrect?';
			RETURN FALSE;
		END IF;
		RETURN TRUE;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public.dropoverviewconstraints(ovtable name, ovcolumn name)
 RETURNS boolean
 LANGUAGE sql
 STRICT
AS $function$ SELECT  public.DropOverviewConstraints('', $1, $2) $function$
;

CREATE OR REPLACE FUNCTION public.droprasterconstraints(rastschema name, rasttable name, rastcolumn name, srid boolean DEFAULT true, scale_x boolean DEFAULT true, scale_y boolean DEFAULT true, blocksize_x boolean DEFAULT true, blocksize_y boolean DEFAULT true, same_alignment boolean DEFAULT true, regular_blocking boolean DEFAULT true, num_bands boolean DEFAULT true, pixel_types boolean DEFAULT true, nodata_values boolean DEFAULT true, out_db boolean DEFAULT true, extent boolean DEFAULT true)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	DECLARE
		constraints text[];
	BEGIN
		IF srid IS TRUE THEN
			constraints := constraints || 'srid'::text;
		END IF;
		IF scale_x IS TRUE THEN
			constraints := constraints || 'scale_x'::text;
		END IF;
		IF scale_y IS TRUE THEN
			constraints := constraints || 'scale_y'::text;
		END IF;
		IF blocksize_x IS TRUE THEN
			constraints := constraints || 'blocksize_x'::text;
		END IF;
		IF blocksize_y IS TRUE THEN
			constraints := constraints || 'blocksize_y'::text;
		END IF;
		IF same_alignment IS TRUE THEN
			constraints := constraints || 'same_alignment'::text;
		END IF;
		IF regular_blocking IS TRUE THEN
			constraints := constraints || 'regular_blocking'::text;
		END IF;
		IF num_bands IS TRUE THEN
			constraints := constraints || 'num_bands'::text;
		END IF;
		IF pixel_types IS TRUE THEN
			constraints := constraints || 'pixel_types'::text;
		END IF;
		IF nodata_values IS TRUE THEN
			constraints := constraints || 'nodata_values'::text;
		END IF;
		IF out_db IS TRUE THEN
			constraints := constraints || 'out_db'::text;
		END IF;
		IF extent IS TRUE THEN
			constraints := constraints || 'extent'::text;
		END IF;
		RETURN DropRasterConstraints($1, $2, $3, VARIADIC constraints);
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public.droprasterconstraints(rasttable name, rastcolumn name, srid boolean DEFAULT true, scale_x boolean DEFAULT true, scale_y boolean DEFAULT true, blocksize_x boolean DEFAULT true, blocksize_y boolean DEFAULT true, same_alignment boolean DEFAULT true, regular_blocking boolean DEFAULT true, num_bands boolean DEFAULT true, pixel_types boolean DEFAULT true, nodata_values boolean DEFAULT true, out_db boolean DEFAULT true, extent boolean DEFAULT true)
 RETURNS boolean
 LANGUAGE sql
 STRICT
AS $function$ SELECT public.DropRasterConstraints('', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) $function$
;

CREATE OR REPLACE FUNCTION public.droprasterconstraints(rasttable name, rastcolumn name, VARIADIC constraints text[])
 RETURNS boolean
 LANGUAGE sql
 STRICT
AS $function$ SELECT  public.DropRasterConstraints('', $1, $2, VARIADIC $3) $function$
;

CREATE OR REPLACE FUNCTION public.droprasterconstraints(rastschema name, rasttable name, rastcolumn name, VARIADIC constraints text[])
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$
	DECLARE
		max int;
		x int;
		schema name;
		sql text;
		kw text;
		rtn boolean;
		cnt int;
	BEGIN
		cnt := 0;
		max := array_length(constraints, 1);
		IF max < 1 THEN
			RAISE NOTICE 'No constraints indicated to be dropped.  Doing nothing';
			RETURN TRUE;
		END IF;
		schema := NULL;
		IF length($1) > 0 THEN
			sql := 'SELECT nspname FROM pg_namespace '
				|| 'WHERE nspname = ' || quote_literal($1)
				|| 'LIMIT 1';
			EXECUTE sql INTO schema;
			IF schema IS NULL THEN
				RAISE EXCEPTION 'The value provided for schema is invalid';
				RETURN FALSE;
			END IF;
		END IF;
		IF schema IS NULL THEN
			sql := 'SELECT n.nspname AS schemaname '
				|| 'FROM pg_catalog.pg_class c '
				|| 'JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace '
				|| 'WHERE c.relkind = ' || quote_literal('r')
				|| ' AND n.nspname NOT IN (' || quote_literal('pg_catalog')
				|| ', ' || quote_literal('pg_toast')
				|| ') AND pg_catalog.pg_table_is_visible(c.oid)'
				|| ' AND c.relname = ' || quote_literal($2);
			EXECUTE sql INTO schema;
			IF schema IS NULL THEN
				RAISE EXCEPTION 'The table % does not occur in the search_path', quote_literal($2);
				RETURN FALSE;
			END IF;
		END IF;
		<<kwloop>>
		FOR x in 1..max LOOP
			kw := trim(both from lower(constraints[x]));
			BEGIN
				CASE
					WHEN kw = 'srid' THEN
						RAISE NOTICE 'Dropping SRID constraint';
						rtn :=  public._drop_raster_constraint_srid(schema, $2, $3);
					WHEN kw IN ('scale_x', 'scalex') THEN
						RAISE NOTICE 'Dropping scale-X constraint';
						rtn :=  public._drop_raster_constraint_scale(schema, $2, $3, 'x');
					WHEN kw IN ('scale_y', 'scaley') THEN
						RAISE NOTICE 'Dropping scale-Y constraint';
						rtn :=  public._drop_raster_constraint_scale(schema, $2, $3, 'y');
					WHEN kw = 'scale' THEN
						RAISE NOTICE 'Dropping scale-X constraint';
						rtn :=  public._drop_raster_constraint_scale(schema, $2, $3, 'x');
						RAISE NOTICE 'Dropping scale-Y constraint';
						rtn :=  public._drop_raster_constraint_scale(schema, $2, $3, 'y');
					WHEN kw IN ('blocksize_x', 'blocksizex', 'width') THEN
						RAISE NOTICE 'Dropping blocksize-X constraint';
						rtn :=  public._drop_raster_constraint_blocksize(schema, $2, $3, 'width');
					WHEN kw IN ('blocksize_y', 'blocksizey', 'height') THEN
						RAISE NOTICE 'Dropping blocksize-Y constraint';
						rtn :=  public._drop_raster_constraint_blocksize(schema, $2, $3, 'height');
					WHEN kw = 'blocksize' THEN
						RAISE NOTICE 'Dropping blocksize-X constraint';
						rtn :=  public._drop_raster_constraint_blocksize(schema, $2, $3, 'width');
						RAISE NOTICE 'Dropping blocksize-Y constraint';
						rtn :=  public._drop_raster_constraint_blocksize(schema, $2, $3, 'height');
					WHEN kw IN ('same_alignment', 'samealignment', 'alignment') THEN
						RAISE NOTICE 'Dropping alignment constraint';
						rtn :=  public._drop_raster_constraint_alignment(schema, $2, $3);
					WHEN kw IN ('regular_blocking', 'regularblocking') THEN
						rtn :=  public._drop_raster_constraint_regular_blocking(schema, $2, $3);
						RAISE NOTICE 'Dropping coverage tile constraint required for regular blocking';
						rtn :=  public._drop_raster_constraint_coverage_tile(schema, $2, $3);
						IF rtn IS NOT FALSE THEN
							RAISE NOTICE 'Dropping spatially unique constraint required for regular blocking';
							rtn :=  public._drop_raster_constraint_spatially_unique(schema, $2, $3);
						END IF;
					WHEN kw IN ('num_bands', 'numbands') THEN
						RAISE NOTICE 'Dropping number of bands constraint';
						rtn :=  public._drop_raster_constraint_num_bands(schema, $2, $3);
					WHEN kw IN ('pixel_types', 'pixeltypes') THEN
						RAISE NOTICE 'Dropping pixel type constraint';
						rtn :=  public._drop_raster_constraint_pixel_types(schema, $2, $3);
					WHEN kw IN ('nodata_values', 'nodatavalues', 'nodata') THEN
						RAISE NOTICE 'Dropping nodata value constraint';
						rtn :=  public._drop_raster_constraint_nodata_values(schema, $2, $3);
					WHEN kw IN ('out_db', 'outdb') THEN
						RAISE NOTICE 'Dropping out-of-database constraint';
						rtn :=  public._drop_raster_constraint_out_db(schema, $2, $3);
					WHEN kw = 'extent' THEN
						RAISE NOTICE 'Dropping maximum extent constraint';
						rtn :=  public._drop_raster_constraint_extent(schema, $2, $3);
					ELSE
						RAISE NOTICE 'Unknown constraint: %.  Skipping', quote_literal(constraints[x]);
						CONTINUE kwloop;
				END CASE;
			END;
			IF rtn IS FALSE THEN
				cnt := cnt + 1;
				RAISE WARNING 'Unable to drop constraint: %.  Skipping', quote_literal(constraints[x]);
			END IF;
		END LOOP kwloop;
		IF cnt = max THEN
			RAISE EXCEPTION 'None of the constraints specified could be dropped.  Is the schema name, table name or column name incorrect?';
			RETURN FALSE;
		END IF;
		RETURN TRUE;
	END;
	$function$
;

CREATE OR REPLACE FUNCTION public.equals(geom1 geometry, geom2 geometry)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT COST 250
AS '$libdir/postgis-3', $function$ST_Equals$function$
;

CREATE OR REPLACE FUNCTION public.find_out_and_back_spatial(staging_schema text, target_distance_km double precision, target_elevation_gain double precision, tolerance_percent double precision DEFAULT 30.0)
 RETURNS TABLE(route_id text, start_node integer, end_node integer, total_distance_km double precision, total_elevation_gain double precision, route_path integer[], route_edges integer[], route_shape text, trail_count integer, similarity_score double precision)
 LANGUAGE plpgsql
AS $function$
DECLARE
    min_distance float;
    max_distance float;
    min_elevation float;
    max_elevation float;
BEGIN
    min_distance := target_distance_km * (1 - tolerance_percent / 100.0);
    max_distance := target_distance_km * (1 + tolerance_percent / 100.0);
    min_elevation := target_elevation_gain * (1 - tolerance_percent / 100.0);
    max_elevation := target_elevation_gain * (1 + tolerance_percent / 100.0);
    RETURN QUERY EXECUTE format($f$
        WITH out_and_back AS (
            SELECT 
                e1.id as edge1_id,
                e1.source as start_node,
                e1.target as mid_node,
                e2.id as edge2_id,
                e2.target as end_node,
                e1.distance_km + e2.distance_km as total_distance,
                COALESCE(e1.elevation_gain, 0) + COALESCE(e2.elevation_gain, 0) as total_elevation,
                ARRAY[e1.source, e1.target, e2.target] as path,
                ARRAY[e1.id, e2.id] as edges,
                ARRAY[e1.trail_name, e2.trail_name] as trail_names
            FROM %I.routing_edges e1
            JOIN %I.routing_edges e2 ON e1.target = e2.source
            WHERE e1.source = e2.target  -- Forms a loop back to start
              AND e1.distance_km + e2.distance_km BETWEEN $1 AND $2
              AND COALESCE(e1.elevation_gain, 0) + COALESCE(e2.elevation_gain, 0) BETWEEN $3 AND $4
        ),
        valid_routes AS (
            SELECT 
                gen_random_uuid()::text as route_id,
                start_node,
                end_node,
                total_distance as total_distance_km,
                total_elevation as total_elevation_gain,
                path,
                edges,
                'out-and-back' as route_shape,
                array_length(array_agg(DISTINCT trail_names), 1) as trail_count,
                calculate_route_similarity_score(
                    total_distance, $5,
                    total_elevation, $6
                ) as similarity_score
            FROM out_and_back
            GROUP BY start_node, end_node, total_distance, total_elevation, path, edges
        )
        SELECT * FROM valid_routes
        WHERE similarity_score >= get_min_route_score()
        ORDER BY similarity_score DESC
        LIMIT get_max_routes_per_bin()
    $f$, staging_schema, staging_schema)
    USING min_distance, max_distance, min_elevation, max_elevation,
          target_distance_km, target_elevation_gain;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.find_routes_for_criteria(staging_schema text, target_distance_km double precision, target_elevation_gain double precision, desired_route_shape text DEFAULT NULL::text, max_routes integer DEFAULT 10)
 RETURNS TABLE(route_id text, total_distance_km double precision, total_elevation_gain double precision, route_shape text, trail_count integer, similarity_score double precision, route_path integer[], route_edges integer[])
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY EXECUTE format($f$
        SELECT 
            route_id,
            total_distance_km,
            total_elevation_gain,
            route_shape,
            trail_count,
            similarity_score,
            route_path,
            route_edges
        FROM find_routes_recursive($1, $2, $3, 20.0, 8)
        WHERE ($4 IS NULL OR route_shape = $4)
        ORDER BY similarity_score DESC
        LIMIT $5
    $f$, staging_schema)
    USING target_distance_km, target_elevation_gain, desired_route_shape, max_routes;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.find_routes_for_criteria_configurable(staging_schema text, target_distance_km double precision, target_elevation_gain double precision, desired_route_shape text DEFAULT NULL::text, max_routes integer DEFAULT NULL::integer)
 RETURNS TABLE(route_id text, total_distance_km double precision, total_elevation_gain double precision, route_shape text, trail_count integer, similarity_score double precision, route_path integer[], route_edges integer[])
 LANGUAGE plpgsql
AS $function$
DECLARE
    config_max_routes integer;
BEGIN
    IF max_routes IS NULL THEN
        config_max_routes := get_max_routes_per_bin();
    ELSE
        config_max_routes := max_routes;
    END IF;
    RETURN QUERY EXECUTE format($f$
        SELECT 
            route_id,
            total_distance_km,
            total_elevation_gain,
            route_shape,
            trail_count,
            similarity_score,
            route_path,
            route_edges
        FROM find_routes_recursive_configurable($1, $2, $3, 20.0, 8)
        WHERE ($4 IS NULL OR route_shape = $4)
        ORDER BY similarity_score DESC
        LIMIT $5
    $f$, staging_schema)
    USING staging_schema, target_distance_km, target_elevation_gain, desired_route_shape, config_max_routes;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.find_routes_recursive(staging_schema text, target_distance_km double precision, target_elevation_gain double precision, tolerance_percent double precision DEFAULT 20.0, max_depth integer DEFAULT 8)
 RETURNS TABLE(route_id text, start_node integer, end_node integer, total_distance_km double precision, total_elevation_gain double precision, route_path integer[], route_edges integer[], route_shape text, trail_count integer, similarity_score double precision)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY EXECUTE format($f$
        WITH RECURSIVE route_search AS (
            SELECT 
                id as start_node,
                id as current_node,
                id as end_node,
                ARRAY[id] as path,
                ARRAY[]::integer[] as edges,
                0.0::double precision as total_distance,
                0.0::double precision as total_elevation_gain,
                0 as depth,
                ARRAY[]::text[] as trail_names
            FROM %I.routing_nodes
            WHERE node_type IN ('intersection', 'endpoint')
            UNION ALL
            SELECT 
                rs.start_node,
                e.target as current_node,
                e.target as end_node,
                rs.path || e.target,
                rs.edges || e.id,
                (rs.total_distance + e.distance_km)::double precision,
                (rs.total_elevation_gain + COALESCE(e.elevation_gain, 0))::double precision,
                rs.depth + 1,
                rs.trail_names || e.trail_name
            FROM route_search rs
            JOIN %I.routing_edges e ON rs.current_node = e.source
            WHERE rs.depth < $1  -- Limit depth to prevent infinite loops
              AND e.target != ALL(rs.path)  -- Avoid cycles
              AND rs.total_distance < $2 * (1 + $3 / 100.0)  -- Distance tolerance
              AND rs.total_elevation_gain < $4 * (1 + $3 / 100.0)  -- Elevation tolerance
        ),
        valid_routes AS (
            SELECT 
                gen_random_uuid()::text as route_id,
                start_node,
                end_node,
                total_distance as total_distance_km,
                total_elevation_gain,
                path as route_path,
                edges as route_edges,
                CASE 
                    WHEN start_node = end_node THEN 'loop'
                    WHEN array_length(path, 1) = 2 THEN 'out-and-back'
                    WHEN array_length(path, 1) > 2 AND start_node = end_node THEN 'loop'
                    ELSE 'point-to-point'
                END as route_shape,
                array_length(array_agg(DISTINCT trail_names), 1) as trail_count,
                calculate_route_similarity_score(total_distance, $2, total_elevation_gain, $4) as similarity_score
            FROM route_search
            WHERE total_distance >= $2 * (1 - $3 / 100.0)  -- Minimum distance
              AND total_distance <= $2 * (1 + $3 / 100.0)  -- Maximum distance
              AND total_elevation_gain >= $4 * (1 - $3 / 100.0)  -- Minimum elevation
              AND total_elevation_gain <= $4 * (1 + $3 / 100.0)  -- Maximum elevation
              AND array_length(path, 1) >= 2  -- At least 2 nodes
            GROUP BY start_node, end_node, total_distance, total_elevation_gain, route_path, route_edges
        )
        SELECT 
            route_id,
            start_node,
            end_node,
            total_distance_km,
            total_elevation_gain,
            route_path,
            route_edges,
            route_shape,
            trail_count,
            similarity_score
        FROM valid_routes
        ORDER BY similarity_score DESC, total_distance_km
        LIMIT get_max_routes_per_bin()  -- Limit results
    $f$, staging_schema, staging_schema)
    USING max_depth, target_distance_km, tolerance_percent, target_elevation_gain;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.find_routes_recursive_configurable(staging_schema text, target_distance_km double precision, target_elevation_gain double precision, tolerance_percent double precision DEFAULT NULL::double precision, max_depth integer DEFAULT 8)
 RETURNS TABLE(route_id text, start_node integer, end_node integer, total_distance_km double precision, total_elevation_gain double precision, route_path integer[], route_edges integer[], route_shape text, trail_count integer, similarity_score double precision)
 LANGUAGE plpgsql
AS $function$
DECLARE
    config_tolerance float;
    distance_limits json;
    elevation_limits json;
BEGIN
    -- Get configurable values
    IF tolerance_percent IS NULL THEN
        config_tolerance := 20.0;  -- Default from config
    ELSE
        config_tolerance := tolerance_percent;
    END IF;
    
    distance_limits := get_route_distance_limits();
    elevation_limits := get_elevation_gain_limits();
    
    RETURN QUERY EXECUTE format($f$
        WITH RECURSIVE route_search AS (
            -- Start with all intersection nodes as potential starting points
            SELECT 
                id as start_node,
                id as current_node,
                id as end_node,
                ARRAY[id] as path,
                ARRAY[]::integer[] as edges,
                0.0::float as total_distance_km,
                0.0::float as total_elevation_gain,
                0 as depth,
                ARRAY[]::text[] as trail_names
            FROM %I.routing_nodes
            
            UNION ALL
            
            -- Recursively explore connected nodes
            SELECT 
                rs.start_node,
                e.target as current_node,
                e.target as end_node,
                rs.path || e.target,
                rs.edges || e.id,
                rs.total_distance_km + e.length_km,
                rs.total_elevation_gain + COALESCE(e.elevation_gain, 0),
                rs.depth + 1,
                rs.trail_names || e.trail_name
            FROM route_search rs
            JOIN %I.routing_edges e ON rs.current_node = e.source
            WHERE rs.depth < $1  -- Limit depth to prevent infinite loops
              AND e.target != ALL(rs.path)  -- Avoid cycles
              AND rs.total_distance_km < $2 * (1 + $3 / 100.0)  -- Distance tolerance
              AND rs.total_elevation_gain < $4 * (1 + $3 / 100.0)  -- Elevation tolerance
        ),
        valid_routes AS (
            -- Filter to routes that meet our criteria
            SELECT 
                gen_random_uuid()::text as route_id,
                start_node,
                end_node,
                total_distance_km,
                total_elevation_gain,
                path,
                edges,
                -- Classify route shape
                CASE 
                    WHEN start_node = end_node THEN 'loop'
                    WHEN array_length(path, 1) = 2 THEN 'out-and-back'
                    WHEN array_length(path, 1) > 2 AND start_node = end_node THEN 'loop'
                    ELSE 'point-to-point'
                END as route_shape,
                -- Count unique trails
                array_length(array_agg(DISTINCT trail_names), 1) as trail_count,
                -- Calculate similarity score
                calculate_route_similarity_score(
                    total_distance_km, $2,
                    total_elevation_gain, $4
                ) as similarity_score
            FROM route_search
            WHERE total_distance_km >= $2 * (1 - $3 / 100.0)  -- Minimum distance
              AND total_elevation_gain >= $4 * (1 - $3 / 100.0)  -- Minimum elevation
            GROUP BY start_node, end_node, total_distance_km, total_elevation_gain, path, edges
        )
        SELECT * FROM valid_routes
        WHERE similarity_score >= get_min_route_score()
        ORDER BY similarity_score DESC
        LIMIT get_max_routes_per_bin()
    $f$, staging_schema, staging_schema)
    USING max_depth, target_distance_km, config_tolerance, target_elevation_gain;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.find_routes_spatial(staging_schema text, target_distance_km double precision, target_elevation_gain double precision, tolerance_percent double precision DEFAULT 20.0, max_depth integer DEFAULT 6)
 RETURNS TABLE(route_id text, start_node integer, end_node integer, total_distance_km double precision, total_elevation_gain double precision, route_path integer[], route_edges integer[], route_shape text, trail_count integer, similarity_score double precision)
 LANGUAGE plpgsql
AS $function$
DECLARE
    min_distance float;
    max_distance float;
    min_elevation float;
    max_elevation float;
    route_detail record;
BEGIN
    min_distance := target_distance_km * (1 - tolerance_percent / 100.0);
    max_distance := target_distance_km * (1 + tolerance_percent / 100.0);
    min_elevation := target_elevation_gain * (1 - tolerance_percent / 100.0);
    max_elevation := target_elevation_gain * (1 + tolerance_percent / 100.0);
    RETURN QUERY EXECUTE format($f$
        WITH RECURSIVE route_search AS (
            SELECT 
                e.id as edge_id,
                e.source as start_node,
                e.target as current_node,
                e.source as end_node,
                ARRAY[e.source, e.target] as path,
                ARRAY[e.id] as edges,
                e.distance_km as total_distance_km,
                COALESCE(e.elevation_gain, 0) as total_elevation_gain,
                1 as depth,
                ARRAY[e.trail_name] as trail_names
            FROM %I.routing_edges e
            WHERE e.distance_km <= $1  -- Start with edges that fit our target
            UNION ALL
            SELECT 
                rs.edge_id,
                rs.start_node,
                e.target as current_node,
                e.target as end_node,
                rs.path || e.target,
                rs.edges || e.id,
                rs.total_distance_km + e.distance_km,
                rs.total_elevation_gain + COALESCE(e.elevation_gain, 0),
                rs.depth + 1,
                rs.trail_names || e.trail_name
            FROM route_search rs
            JOIN %I.routing_edges e ON rs.current_node = e.source
            WHERE rs.depth < $2  -- Limit depth
              AND e.target != ALL(rs.path)  -- Avoid cycles
              AND rs.total_distance_km + e.distance_km <= $3  -- Distance tolerance
              AND rs.total_elevation_gain + COALESCE(e.elevation_gain, 0) <= $4  -- Elevation tolerance
        ),
        valid_routes AS (
            SELECT 
                gen_random_uuid()::text as route_id,
                start_node,
                end_node,
                total_distance_km,
                total_elevation_gain,
                path,
                edges,
                CASE 
                    WHEN start_node = end_node THEN 'loop'
                    WHEN array_length(path, 1) = 2 THEN 'out-and-back'
                    WHEN array_length(path, 1) > 2 AND start_node = end_node THEN 'loop'
                    ELSE 'point-to-point'
                END as route_shape,
                array_length(array_agg(DISTINCT trail_names), 1) as trail_count,
                calculate_route_similarity_score(
                    total_distance_km, $5,
                    total_elevation_gain, $6
                ) as similarity_score
            FROM route_search
            WHERE total_distance_km >= $7  -- Minimum distance
              AND total_elevation_gain >= $8  -- Minimum elevation
              AND array_length(path, 1) >= 2  -- At least 2 nodes
            GROUP BY start_node, end_node, total_distance_km, total_elevation_gain, path, edges
        )
        SELECT * FROM valid_routes
        WHERE similarity_score >= get_min_route_score()
        ORDER BY similarity_score DESC
        LIMIT get_max_routes_per_bin()
    $f$, staging_schema, staging_schema)
    USING max_distance, max_depth, max_distance, max_elevation, 
          target_distance_km, target_elevation_gain, min_distance, min_elevation;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.find_routes_with_cost_configurable(staging_schema text, target_distance_km double precision, target_elevation_gain double precision, max_cost double precision DEFAULT NULL::double precision)
 RETURNS TABLE(route_id text, total_distance_km double precision, total_elevation_gain double precision, route_cost double precision, steepness_m_per_km double precision, similarity_score double precision, route_shape text)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY EXECUTE format($f$
        WITH route_costs AS (
            SELECT 
                r.route_id,
                r.total_distance_km,
                r.total_elevation_gain,
                r.route_shape,
                r.similarity_score,
                CASE 
                    WHEN r.total_distance_km > 0 THEN r.total_elevation_gain / r.total_distance_km
                    ELSE 0
                END as steepness_m_per_km,
                calculate_route_cost(
                    CASE 
                        WHEN r.total_distance_km > 0 THEN r.total_elevation_gain / r.total_distance_km
                        ELSE 0
                    END,
                    r.total_distance_km
                ) as route_cost
            FROM find_routes_recursive_configurable($1, $2::float, $3::float, 20.0, 8) r
        )
        SELECT 
            route_id,
            total_distance_km,
            total_elevation_gain,
            route_cost,
            steepness_m_per_km,
            similarity_score,
            route_shape
        FROM route_costs
        WHERE ($4 IS NULL OR route_cost <= $4)
        ORDER BY route_cost ASC, similarity_score DESC
        LIMIT get_max_routes_per_bin()
    $f$, staging_schema)
    USING target_distance_km, target_elevation_gain, max_cost;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.find_simple_loops_spatial(staging_schema text, target_distance_km double precision, target_elevation_gain double precision, tolerance_percent double precision DEFAULT 30.0)
 RETURNS TABLE(route_id text, start_node integer, end_node integer, total_distance_km double precision, total_elevation_gain double precision, route_path integer[], route_edges integer[], route_shape text, trail_count integer, similarity_score double precision)
 LANGUAGE plpgsql
AS $function$
DECLARE
    min_distance float;
    max_distance float;
    min_elevation float;
    max_elevation float;
BEGIN
    min_distance := target_distance_km * (1 - tolerance_percent / 100.0);
    max_distance := target_distance_km * (1 + tolerance_percent / 100.0);
    min_elevation := target_elevation_gain * (1 - tolerance_percent / 100.0);
    max_elevation := target_elevation_gain * (1 + tolerance_percent / 100.0);
    RETURN QUERY EXECUTE format($f$
        WITH potential_loops AS (
            SELECT 
                e1.id as edge1_id,
                e1.source as start_node,
                e1.target as mid_node,
                e2.id as edge2_id,
                e2.target as end_node,
                e1.distance_km + e2.distance_km as total_distance,
                COALESCE(e1.elevation_gain, 0) + COALESCE(e2.elevation_gain, 0) as total_elevation,
                ARRAY[e1.source, e1.target, e2.target] as path,
                ARRAY[e1.id, e2.id] as edges,
                ARRAY[e1.trail_name, e2.trail_name] as trail_names
            FROM %I.routing_edges e1
            JOIN %I.routing_edges e2 ON e1.target = e2.source
            WHERE e1.source != e2.target  -- Not a self-loop
              AND e1.distance_km + e2.distance_km BETWEEN $1 AND $2
              AND COALESCE(e1.elevation_gain, 0) + COALESCE(e2.elevation_gain, 0) BETWEEN $3 AND $4
        ),
        valid_loops AS (
            SELECT 
                gen_random_uuid()::text as route_id,
                start_node,
                end_node,
                total_distance as total_distance_km,
                total_elevation as total_elevation_gain,
                path,
                edges,
                'loop' as route_shape,
                array_length(array_agg(DISTINCT trail_names), 1) as trail_count,
                calculate_route_similarity_score(
                    total_distance, $5,
                    total_elevation, $6
                ) as similarity_score
            FROM potential_loops
            GROUP BY start_node, end_node, total_distance, total_elevation, path, edges
        )
        SELECT * FROM valid_loops
        WHERE similarity_score >= get_min_route_score()
        ORDER BY similarity_score DESC
        LIMIT get_max_routes_per_bin()
    $f$, staging_schema, staging_schema)
    USING min_distance, max_distance, min_elevation, max_elevation,
          target_distance_km, target_elevation_gain;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.find_simple_routes_with_logging(staging_schema text, target_distance_km double precision, target_elevation_gain double precision, tolerance_percent double precision DEFAULT 30.0)
 RETURNS TABLE(route_id text, start_node integer, end_node integer, total_distance_km double precision, total_elevation_gain double precision, route_path integer[], route_edges integer[], route_shape text, trail_count integer, similarity_score double precision)
 LANGUAGE plpgsql
AS $function$
DECLARE
    min_distance float;
    max_distance float;
    min_elevation float;
    max_elevation float;
    edge_count integer;
    route_count integer;
BEGIN
    min_distance := target_distance_km * (1 - tolerance_percent / 100.0);
    max_distance := target_distance_km * (1 + tolerance_percent / 100.0);
    min_elevation := target_elevation_gain * (1 - tolerance_percent / 100.0);
    max_elevation := target_elevation_gain * (1 + tolerance_percent / 100.0);
    RAISE NOTICE 'Searching for routes: distance %.1f-%.1f km, elevation %.0f-%.0f m', 
        min_distance, max_distance, min_elevation, max_elevation;
    EXECUTE format('SELECT COUNT(*) FROM %I.routing_edges WHERE distance_km BETWEEN %s AND %s', 
        staging_schema, min_distance, max_distance) INTO edge_count;
    RAISE NOTICE 'Found % edges in distance range', edge_count;
    RETURN QUERY EXECUTE format($f$
        WITH simple_routes AS (
            SELECT 
                gen_random_uuid()::text as route_id,
                e1.source as start_node,
                e2.target as end_node,
                (e1.distance_km + e2.distance_km)::double precision as total_distance_km,
                (COALESCE(e1.elevation_gain, 0) + COALESCE(e2.elevation_gain, 0))::double precision as total_elevation_gain,
                ARRAY[e1.source, e1.target, e2.target] as route_path,
                ARRAY[e1.id, e2.id] as route_edges,
                CASE 
                    WHEN e1.source = e2.target THEN 'loop'
                    ELSE 'out-and-back'
                END as route_shape,
                2 as trail_count,
                calculate_route_similarity_score(
                    e1.distance_km + e2.distance_km, $1,
                    COALESCE(e1.elevation_gain, 0) + COALESCE(e2.elevation_gain, 0), $2
                ) as similarity_score
            FROM %I.routing_edges e1
            JOIN %I.routing_edges e2 ON e1.target = e2.source
            WHERE e1.distance_km + e2.distance_km BETWEEN $3 AND $4
              AND COALESCE(e1.elevation_gain, 0) + COALESCE(e2.elevation_gain, 0) BETWEEN $5 AND $6
              AND e1.source != e2.target  -- Avoid self-loops
        ),
        valid_routes AS (
            SELECT * FROM simple_routes
            WHERE similarity_score >= get_min_route_score()
            ORDER BY similarity_score DESC
            LIMIT get_max_routes_per_bin()
        )
        SELECT * FROM valid_routes
    $f$, staging_schema, staging_schema)
    USING target_distance_km, target_elevation_gain, min_distance, max_distance, min_elevation, max_elevation;
    GET DIAGNOSTICS route_count = ROW_COUNT;
    RAISE NOTICE 'Generated % routes', route_count;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.find_srid(character varying, character varying, character varying)
 RETURNS integer
 LANGUAGE plpgsql
 STABLE PARALLEL SAFE STRICT
AS $function$
DECLARE
	schem varchar =  $1;
	tabl varchar = $2;
	sr int4;
BEGIN
	IF ( schem = '' and strpos(tabl,'.') > 0 ) THEN
	 schem = substr(tabl,1,strpos(tabl,'.')-1);
	 tabl = substr(tabl,length(schem)+2);
	END IF;
	select SRID into sr from public.geometry_columns where (f_table_schema = schem or schem = '') and f_table_name = tabl and f_geometry_column = $3;
	IF NOT FOUND THEN
	   RAISE EXCEPTION 'find_srid() - could not find the corresponding SRID - is the geometry registered in the GEOMETRY_COLUMNS table?  Is there an uppercase/lowercase mismatch?';
	END IF;
	return sr;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.generate_app_uuid()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF NEW.app_uuid IS NULL OR NEW.app_uuid = '' THEN
        NEW.app_uuid := gen_random_uuid();
    END IF;
    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.generate_route_name(route_edges integer[], route_shape text)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
DECLARE
  trail_names text[];
  unique_trail_names text[];
  route_name text;
BEGIN
  -- Extract unique trail names from route edges
  SELECT array_agg(DISTINCT trail_name ORDER BY trail_name) INTO trail_names
  FROM routing_edges 
  WHERE id = ANY(route_edges);
  
  -- Remove duplicates while preserving order
  SELECT array_agg(DISTINCT name ORDER BY name) INTO unique_trail_names
  FROM unnest(trail_names) AS name;
  
  -- Apply naming convention based on number of unique trails
  IF array_length(unique_trail_names, 1) = 1 THEN
    -- Single trail: use trail name directly
    route_name := unique_trail_names[1];
  ELSIF array_length(unique_trail_names, 1) = 2 THEN
    -- Two trails: {First Trail}/{Second Trail} Route
    route_name := unique_trail_names[1] || '/' || unique_trail_names[2] || ' Route';
  ELSE
    -- More than 2 trails: {First Trail}/{Last Trail} Route
    route_name := unique_trail_names[1] || '/' || unique_trail_names[array_length(unique_trail_names, 1)] || ' Route';
  END IF;
  
  -- Add route shape suffix if not already present
  IF route_name NOT LIKE '%' || route_shape || '%' THEN
    route_name := route_name || ' ' || route_shape;
  END IF;
  
  RETURN route_name;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.generate_route_recommendations(staging_schema text)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN generate_route_recommendations_configurable(staging_schema, 'boulder');
END;
$function$
;

CREATE OR REPLACE FUNCTION public.generate_route_recommendations_adaptive(staging_schema text, region_name text DEFAULT 'boulder'::text, min_routes_per_pattern integer DEFAULT 10, max_tolerance_percent integer DEFAULT 50)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
    route_count integer := 0;
    pattern record;
    total_routes integer := 0;
    current_tolerance float;
    routes_found integer;
    max_iterations integer := 5; -- Prevent infinite loops
    iteration integer;
BEGIN
    EXECUTE format('
