links.sql

create or replace function get_motorway_links(bbox geometry)
returns table (id bigint, highway text, geom geometry)
language sql as
$$
with
-- roads that are shown at current zoom-level
roads as (select osm_id as id, way as geom, highway 
	from planet_line 
	where way && $1 and highway in ('motorway', 'trunk')),

-- road types not shown at the intended zoom-level (which may be connected to links)
offroad as (select osm_id as id, way as geom
	from planet_line 
	where way && $1 and highway in ('primary', 'secondary', 'tertiary', 'service')),

-- possible links to show
all_links as (select osm_id as id, way as geom, highway 
	from planet_line l
	where way && $1 and highway in ('motorway_link', 'trunk_link')),

-- links that connect to offroads
ignore as (select distinct on (id) l.id as id 
	from all_links as l, offroad r
	where r.geom && l.geom and 
		(st_intersects(st_startpoint(l.geom), r.geom) or 
		 st_intersects(st_endpoint(l.geom), r.geom))),
		
-- remove the links that connect to offroads
links as (select l.* from all_links as l
	left join ignore on (ignore.id = l.id)
	where ignore.id is null),

-- find links connected with a road at their startpoint 
startp as (select distinct on(id) l.id as id
	from links l, roads r
	where r.geom && l.geom and 
		st_intersects(st_startpoint(l.geom), r.geom)),
	
endp as (select distinct on(id) l.id as id
	from links l, roads r
	where r.geom && l.geom and 
		st_intersects(st_endpoint(l.geom), r.geom)),
		
-- build connection table for links (assuming default one-way orientation of lines)
graph as (select way1.id, way2.id link 
	from links way1, links way2
	where way1 <> way2 and 
		(ST_Intersects(ST_StartPoint(way1.geom), ST_EndPoint(way2.geom)) or
		 ST_Intersects(ST_EndPoint(way1.geom), ST_StartPoint(way2.geom)))
),

-- create paths of links
paths as (with recursive search_graph(id, link, depth, path, cycle) as (
	-- start from startp, init path
	select g.id, g.link, 1, array[g.id], false
		from graph g, startp c  
		where c.id = g.id
	union all
	-- append links to path
	select g.id, g.link, sg.depth + 1, path || g.id, g.id = any(path)
		from graph g, search_graph sg
		where (g.id = sg.link)  
			and not g.id = any(path)
			and depth < 10 -- just in case
	)
	select * from search_graph
),
-- extract paths which end at endpoints
matching_links as (select unnest(path) id 
	--from paths, endp
	--where path[array_upper(path, 1)] = endp.id
	from paths
	join endp on(paths.id = endp.id)
	union 
	select startp.id from endp, startp where endp.id = startp.id 
)
select distinct on(l.id) l.id, l.highway, geom
 	from matching_links m
 	join links l on (m.id = l.id) 
$$;

CREATE OR REPLACE FUNCTION ttbx(
    tilex bigint,
    tiley bigint,
    tilez integer,
    size integer DEFAULT 256,
    pixel integer DEFAULT 0) -- expand by pixels
  RETURNS geometry AS
$BODY$
DECLARE
scaleFactor double precision = 20037508.342789244;
xmin double precision;
xmax double precision;
ymin double precision;
ymax double precision;
center double precision;
BEGIN
	tileX := tileX * size;
	tileY := tileY * size;
	center := (size << tileZ) >> 1;
		
	ymin := ((center - (tileY + size + pixel)) / center) * scaleFactor;
	ymax := ((center - (tileY - pixel)) / center) * scaleFactor;
	xmin := (((tileX - pixel) - center) / center) * scaleFactor;
	xmax := (((tileX + size + pixel) - center) / center) * scaleFactor;

	RETURN ST_MakeEnvelope(xmin, ymin, xmax, ymax, 3785);		
END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE;





select * from get_motorway_links(ttbx(2646/2,6362/2,13, 256, 256))