

CREATE TABLE test_normalise AS
SELECT 'PAZA' AS designator, '0103000020E610000001000000600000008A5E46B1DCE0674000000000000051400100000000406940000000000000524000000000004069400000000000005240895E46B1DCE067400000000000005140895E46B1DCE0674001000000008056400000000000606B4001000000008056400000000000606B40EF940ED6FF7F56400000000000606B40DD291DACFF7F56400000000000606B40CBBE2B82FF7F56400000000000606B40B9533A58FF7F56400000000000606B40A8E8482EFF7F56400000000000606B40967D5704FF7F56400000000000606B4072A774B0FE7F56400000000000606B404FD1915CFE7F56400000000000606B402BFBAE08FE7F56400000000000606B40F6B9DA8AFD7F56400000000000606B40C178060DFD7F56400000000000606B4079CC4065FC7F56400000000000606B400F4A9869FB7F56400000000000606B40A4C7EF6DFA7F56400000000000606B40040473F4F87F56400000000000606B4052D50451F77F56400000000000606B407DD0B359F57F56400000000000606B40611F9DBAF27F56400000000000606B400F2DB29DEF7F56400000000000606B40642310AFEB7F56400000000000606B406102B7EEE67F56400000000000606B40E1F3C308E17F56400000000000606B40AFB6627FD97F56400000000000606B40DDB5847CD07F56400000000000606B4013DA722EC57F56400000000000606B402C4D4A41B77F56400000000000606B40CFF753E3A57F56400000000000606B40B72DCA6C907F56400000000000606B40776C04E2757F56400000000000606B4093C6681D557F56400000000000606B405B0D897B2C7F56400000000000606B401B12F758FA7E56400000000000606B40B8239C16BC7E56400000000000606B4027FC523F6F7E56400000000000606B40BD9179E40F7E56400000000000606B40CFDA6D179A7D56400000000000606B40F0332E1C087D56400000000000606B407CB8E4B8537C56400000000000606B40C53D963E747B56400000000000606B408E40BCAE5F7A56400000000000606B407F8CB96B097956400000000000606B40FE65F7E4617756400000000000606B40C9073D9B557556400000000000606B40CDCCCCCCCC7256400000000000606B407A01F6D1A96F56400000000000606B4053616C21C86B56400000000000606B4009A7052FFA6656400000000000606B40F0332E1C086156400000000000606B4085471B47AC5956400000000000606B40752497FF905056400000000000606B408C84B69C4B4556400000000000606B402C6519E2583756400000000000606B402B357BA0152656400000000000606B4055C6BFCFB81056400000000000606B40F988981249F655400000000000606B408C321B6492D555400000000000606B40ADC5A70018AD55400000000000606B402254A9D9037B55400000000000606B4009E1D1C6113D55400000000000606B40A0E5797077F054400000000000606B40FA49B54FC79154400000000000606B4043959A3DD01C54400000000000606B4075EACA67798C53400000000000606B400EE02D90A0DA52400000000000606B4000000000000052400100000000606B4000000000000052400100000000606B400100000000004F400100000000606B401730815B77274E4074BA2C2636626B404CAB21718F254E4080BC57AD4CAC6C40597380608E5A4B40FBB31F29226A6C40CDCCCCCCCC544B409A99999999216C401E03B2D7BB1B4B400100000000006C400100000000004B400100000000206C401E03B2D7BB5B4A400100000000086A40132C0E677E614C400100000000E069400100000000004C4001000000000069400100000000C04A40064CE0D6DD0568403433333333B34940064CE0D6DDED6640B0C91AF51011494034333333332366401D03B2D7BB1B49402EE7525C555D64409A99999999D946400000000000E063404A63B48EAA0A494000000000002065400100000000004B40000000000040654003F1BA7EC1564B406766666666466540EACF7EA488684B4001000000008066400100000000004E409A9999999979674034333333330350401A390B7BDAE0674000000000004050401A390B7BDAE067400000000000C050408A5E46B1DCE067400000000000005140'::geometry AS visual_border
UNION ALL
SELECT 'ULMM' AS designator, '[encoded data, 4610 characters]'::geometry AS visual_border
;


CREATE OR REPLACE FUNCTION test_normalise_coordinate(coord float, abs_max integer)
RETURNS float
AS $BODY$
BEGIN
	IF abs_max = 90 AND coord > abs_max THEN
		-- Value seems to be between 90.000000000000001 and 90.0000000000001
		RAISE NOTICE 'Suspicious latitude: % > %', coord, abs_max;
	END IF;

	RETURN CASE WHEN abs($1) > $2
		THEN ($1 + $2 * sign($1))::numeric % ($2 * 2) - $2 * sign($1)
		/* Workaround:
			CASE WHEN abs($1) < ($2 + 0.0000001)
				THEN $2 * sign($1) -- Just over - likely a floating point error
				ELSE ($1 + $2 * sign($1))::numeric % ($2 * 2) - $2 * sign($1)
			END
		*/
		ELSE $1
	END;
END
$BODY$ LANGUAGE plpgsql IMMUTABLE COST 2;


CREATE OR REPLACE FUNCTION test_normalise_point(point geometry(Point, 4326))
RETURNS geometry(Point, 4326)
AS $BODY$
DECLARE
	result geometry(Point, 4326);
BEGIN
	result := ST_SetSRID(ST_MakePoint(
		test_normalise_coordinate(ST_X($1), 180),
		test_normalise_coordinate(ST_Y($1), 90)
		), 4326);
	--RAISE DEBUG 'Normalised point % to %', ST_AsText(point), ST_AsText(result);
	RETURN result;
END
$BODY$ LANGUAGE plpgsql IMMUTABLE COST 3;


CREATE OR REPLACE FUNCTION test_normalise_linestring(line geometry(LineString))
RETURNS geometry(LineString)
AS $BODY$
DECLARE
	geog_line geography(LineString);
BEGIN
	BEGIN
		-- Without this cast to geography the "90+" latitude occurs regardless of order, on PostgresSQL 9.1 and 9.2.
		geog_line := line::geography;
	EXCEPTION WHEN SQLSTATE '22023' THEN
		IF NOT SQLERRM LIKE 'Coordinate values are out of range %' THEN
			RAISE EXCEPTION E'Error %: %', SQLSTATE, SQLERRM;
		END IF;
	END;

	RETURN ST_MakeLine(test_normalise_point(geom)) FROM ST_DumpPoints(line);
END
$BODY$ LANGUAGE plpgsql IMMUTABLE;



WITH data AS
(
	SELECT *, ST_ExteriorRing(visual_border) AS exterior_ring
	FROM test_normalise
	WHERE designator IN ('PAZA', 'ULMM')
	-- If ULMM is first and the cast to geography above runs the result is correct:
	--ORDER BY designator DESC
	-- But if ULMM is processed second then the "90+" latitudes occur:
	ORDER BY designator
)
, recalc AS
(
	SELECT designator, test_normalise_linestring(exterior_ring) AS normalised_ring
	FROM data
)
SELECT designator, ST_AsText(normalised_ring) AS normalised_ring
FROM recalc
WHERE designator = 'ULMM'
	-- Check against expected result
	AND ST_AsText(normalised_ring) != 'LINESTRING(43.26667 68.66667,44.15 67.86667,42.5 66.78333,40 66.25,38.5 66,38.44663 66,38.3568 66,38.26697 66,38.17714 66,38.0873 66,37.99747 66,37.90764 66,37.81781 66,37.72798 66,37.63815 66,37.54831 66,37.45848 66,37.36865 66,37.27882 66,37.18899 66,37.09916 66,37.00933 66,36.91949 66,36.82966 66,36.73983 66,36.65 66,35 66.35,29.6167 66.4,29.59417 66.4075,28.92917 69.05194,30.81778 69.79472,31.13333 70,32.08333 70.16667,32.08333 71.5,32.08333 74,32.12539 74,32.21522 74,32.30505 74,32.39489 74,32.48472 74,32.57455 74,32.66438 74,32.75421 74,32.84404 74,32.93388 74,33.02371 74,33.11354 74,33.20337 74,33.2932 74,33.38303 74,33.47286 74,33.5627 74,33.65253 74,33.74236 74,33.83219 74,33.92202 74,34.01185 74,34.10168 74,34.19152 74,34.28135 74,34.37118 74,34.46101 74,34.55084 74,34.64067 74,34.7305 74,34.82034 74,34.91017 74,35 74,35 81,34.96444 81,34.87461 81,34.78478 81,34.69495 81,34.60511 81,34.51528 81,34.42545 81,34.33562 81,34.24579 81,34.15596 81,34.06612 81,33.97629 81,33.88646 81,33.79663 81,33.7068 81,33.61697 81,33.52714 81,33.4373 81,33.34747 81,33.25764 81,33.16781 81,33.07798 81,32.98815 81,32.89831 81,32.80848 81,32.71865 81,32.62882 81,32.53899 81,32.44916 81,32.35933 81,32.26949 81,32.17966 81,32.08983 81,32 81,32 90,-168.97333 90,-168.97333 87.63333,114.75 78,89.5 77.33333,81.83333 75.33333,70.68333 74.5,70.9167 73.6667,70.91249 73.6667,70.82266 73.6667,70.73282 73.6667,70.64299 73.6667,70.55316 73.6667,70.46333 73.6667,70.3735 73.6667,70.28367 73.6667,70.19384 73.6667,70.104 73.6667,70.01417 73.6667,69.92434 73.6667,69.83451 73.6667,69.74468 73.6667,69.65485 73.6667,69.56502 73.6667,69.47518 73.6667,69.38535 73.6667,69.29552 73.6667,69.20569 73.6667,69.11586 73.6667,69.02603 73.6667,68.93619 73.6667,68.84636 73.6667,68.75653 73.6667,68.6667 73.6667,66.2917 71.505,57.6483 71.1683,57.9983 73.9033,48.61667 73,49.5 70.5,43.26667 68.66667)'
;

