
WITH 
RECURSIVE t(n) AS (
    VALUES (0)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 99999
),
user_set_constants AS (
    SELECT 
    	ST_GeogFromText('SRID=4326; LINESTRING(-4.0 49.8, -4.0 59.0)') as latitude_line_broken,
    	ST_GeogFromText('SRID=4326; LINESTRING(-4.0 59.0, -4.0 49.8)') as latitude_line_ok,

        -- The edge distance of 1km works for the UK, but smaller sizes won't work.
    	1000.0 as tile_edge_metres
),
constant AS (
    SELECT
		ST_NPoints(
			ST_AsEWKT(
            	ST_Segmentize(
            		(SELECT latitude_line_ok FROM user_set_constants),
	    			(SELECT tile_edge_metres FROM user_set_constants)
				)
            )
        ) as latitide_ok_line_n_max,
		ST_NPoints(
			ST_AsEWKT(
            	ST_Segmentize(
            		(SELECT latitude_line_broken FROM user_set_constants),
	    			(SELECT tile_edge_metres FROM user_set_constants)
				)
            )
        ) as latitide_broken_line_n_max,
        ST_AsEWKT(
            ST_Segmentize(
                (SELECT latitude_line_ok FROM user_set_constants),
                (SELECT tile_edge_metres FROM user_set_constants)
            )
        ) as segmentized_latitude_ok_line,
        ST_AsEWKT(
            ST_Segmentize(
                (SELECT latitude_line_broken FROM user_set_constants),
                (SELECT tile_edge_metres FROM user_set_constants)
            )
        ) as segmentized_latitude_broken_line
),
latitude_ok AS (
	SELECT 
		ST_Y( -- Y == latitude
	        ST_PointN(
				(SELECT segmentized_latitude_ok_line FROM constant),
	    		t.n + 1
	        )
	    ) as top,
		ST_Y( -- Y == latitude
	        ST_PointN(
				(SELECT segmentized_latitude_ok_line FROM constant),
	    		t.n + 2
	        )
	    ) as bottom
  FROM t WHERE n+1 < (SELECT latitide_ok_line_n_max FROM constant)),
latitude_broken AS (
	SELECT 
		ST_Y( -- Y == latitude
	        ST_PointN(
				(SELECT segmentized_latitude_broken_line FROM constant),
	    		t.n + 1
	        )
	    ) as top,
		ST_Y( -- Y == latitude
	        ST_PointN(
				(SELECT segmentized_latitude_broken_line FROM constant),
	    		t.n + 2
	        )
	    ) as bottom
  FROM t WHERE n+1 < (SELECT latitide_broken_line_n_max FROM constant)),
lat_lines_ok As (
	SELECT 
	   	 ST_SetSRID(
    		ST_MakeLine(
            	ARRAY[
	    		 	ST_MakePoint(4.0, latitude_ok.top),
		    	   	ST_MakePoint(4.0, latitude_ok.bottom)
                ] 
             ),
             4326 -- SRID for WGS84
         ) as l
 FROM latitude_ok),
lat_lines_broken As (
	SELECT 
	   	 ST_SetSRID(
    		ST_MakeLine(
            	ARRAY[
	    		 	ST_MakePoint(4.0, latitude_broken.top),
		    	   	ST_MakePoint(4.0, latitude_broken.bottom)
                ] 
             ),
             4326 -- SRID for WGS84
         ) as l
 FROM latitude_broken)
SELECT 
	min(ST_Length(lat_lines_ok.l)) as min_ok, max(ST_Length(lat_lines_ok.l)) as max_ok,
	min(ST_Length(lat_lines_broken.l)) as min_broken, max(ST_Length(lat_lines_broken.l)) as max_broken,
    max(constant.latitide_ok_line_n_max) as segment_count_ok,
    max(constant.latitide_broken_line_n_max) as segment_count_broken
    FROM lat_lines_broken, lat_lines_ok, constant;
