queryprocs.sql.in

-- queryprocs.sql.in --
--
--	Procedures to query the OpenStreetMap database for roads that
--      should have markers placed on them on a map
--
-- This file has been placed in the Public Domain in Common Law countries,
-- and is licenced under Creative Commons Zero in all jurisdictions.
--
-----------------------------------------------------------------------------

-----------
-- USAGE --
-----------

-- This file is designed to be given to PostgreSQL as a script after
-- replacing the string, @PREFIX@, with the prefix under which OSM
-- data have been imported, e.g., 'planet_osm'.  A suitable Unix
-- command to accomplish this might be:
--
--      sed s/@PREFIX@/planet_osm/g <queryprocs.sql.in | psql -d gis


--------------------
-- INITIALIZATION --
--------------------

-- Clean up any trash from a previous run

DROP FUNCTION IF EXISTS @PREFIX@_query_shields_roads_motorway(GEOMETRY, INT)
     CASCADE
\g

DROP FUNCTION IF EXISTS @PREFIX@_query_shields_roads_major(GEOMETRY, INT)
     CASCADE
\g

DROP FUNCTION IF EXISTS @PREFIX@_query_shields_roads_all(GEOMETRY, INT)
     CASCADE
\g

DROP FUNCTION IF EXISTS @PREFIX@_query_shields_line_all(GEOMETRY, INT)
     CASCADE
\g

DROP FUNCTION IF EXISTS @PREFIX@_analyze_markers(@PREFIX@_road_segment[], INT)
     CASCADE
\g

DROP FUNCTION IF EXISTS @PREFIX@_make_repeat_key(@PREFIX@_gid_label[])
     CASCADE
\g

DROP FUNCTION IF EXISTS @PREFIX@_gids_to_files(INT[])  -- former function
     CASCADE
\g


DROP FUNCTION IF EXISTS @PREFIX@_gids_to_files(@PREFIX@_gid_label[])
     CASCADE
\g

DROP TYPE IF EXISTS @PREFIX@_file_label CASCADE
\g

DROP TYPE IF EXISTS @PREFIX@_gid_label CASCADE
\g

DROP TYPE IF EXISTS @PREFIX@_road_segment CASCADE
\g



-----------
-- TYPES --
-----------

-- Type: @PREFIX@_road_segment
--
--    A 'road_segment' is a piece of a road. An array of road segments
--    will be constructed to represent the network of numbered routes
--    that are to be rendered, and passed into @PREFIX@_analyze_markers
--    to produce the data set that will drive the renderer.

CREATE TYPE
@PREFIX@_road_segment AS(

    osm_id BIGINT,		-- The OSM ID of the segment

    way GEOMETRY,		-- The geometry of the segment

    "ref" TEXT,                 -- The 'ref' tag on the segment

    highway TEXT		-- The 'highway' tag on the segment
)
\g

-- Type: @PREFIX@_gid_label
--
--	A 'gid_label' is an ordered pair of a graphic file ID and
--      a textual label to attach to it.

CREATE TYPE
@PREFIX@_gid_label AS (

    gid INT,			-- Graphics file ID

    label TEXT			-- Textual label
)
\g

-- Type: @PREFIX_file_label
--
--	A 'file_label' is an ordered pair of a graphic file name and
--	a textual label to attach to it

CREATE TYPE
@PREFIX@_file_label AS (

    filename TEXT,		-- Graphics file name

    label TEXT			-- Textual label
)
\g

---------------
-- FUNCTIONS --
---------------

-- Function: @PREFIX@_gids_to_files
--
--	Converts a table with graphics IDs and labels into a table
--      with filenames and labels
--
-- Parameters:
--	gids - Array of graphics ID/label pairs
--
-- Results:
--	Returns an array with graphics IDs replaced with filenames
--
-- Since each graphics ID is a primary key in the 'osm_shield_graphics'
-- table, this function need only join the array to the table,
-- select the filenames and aggregate the result.

CREATE OR REPLACE FUNCTION
@PREFIX@_gids_to_files(gid_labels @PREFIX@_gid_label[])
RETURNS @PREFIX@_file_label[]
LANGUAGE plpgsql
AS $@PREFIX@_gids_to_files$
BEGIN
  RETURN (
    SELECT ARRAY_AGG((g.filename, (t.glabel).label)::@PREFIX@_file_label)
    FROM (SELECT UNNEST(gid_labels) AS glabel) AS t
	  JOIN osm_shield_graphics AS g
	  ON g.id = (t.glabel).gid
  );
END
$@PREFIX@_gids_to_files$
\g

-- Function: @PREFIX@_make_repeat_key --
--
--	Makes a repeat key from an array of graphic-id/label pairs
--
-- Parameters:
--	glabels - Array of gid/label pairs
--
-- Results:
--	Returns a string that may serve as a repeat key

CREATE OR REPLACE FUNCTION
@PREFIX@_make_repeat_key(glabels @PREFIX@_gid_label[])
RETURNS TEXT
LANGUAGE plpgsql
AS
$@PREFIX@_make_repeat_key$
BEGIN
   RETURN (
       WITH q1 AS (
           SELECT x.glabel
	   FROM (SELECT UNNEST(glabels) AS glabel) AS x
       )
       SELECT ARRAY_TO_STRING(ARRAY_AGG(
           (q1.glabel).gid || '~' || (q1.glabel).label), '|')
       FROM q1
   );
END
$@PREFIX@_make_repeat_key$
\g

-- Function: @PREFIX@_analyze_markers --
--
--	Analyzes a set of road segments, and returns a set of ways, together
--      with the data needed to support shield rendering.
--
-- Parameters:
--	segs -- Array of '@PREFIX@_road_segment' objects that represent
--              roads that might be candidates for labelling with markers.
--      size_wanted -- The desired height of the graphic elements in pixels.
--                     Because of banners, odd shield proportions, and similar
--                     issues, the returned graphic may not be precisely
--                     the nominal size, but should harmonize with other
--                     graphics of the same nominal size.
--
-- Results:
--	Returns a table giving ways that actually should be labeled with
--      markers. Each way has a repeat key that informs Mapnik what marker
--      set it belongs to (to eliminate markers that are too close together)
--      and up to eight occurrences of a file name containing the marker
--      graphic and a string (often empty) to layer atop the graphic.
--
-- The ways passed in are ordinarily not in one-to-one correspondence
-- with the ways returned. Sets of ways that share the same set of markers
-- are coalesced if possible, so the returned linestrings are typically
-- longer than the ones passed as parameters.
--
-- Example:
--
--   Ordinarily, this stored procedure, or one that wraps it, will
--   be used as input to some rendering layer with a given style.
--   The set of input ways is derived from a query on one of the OSM
--   line tables, so the 'table' parameter in the layer's datasource
--   will look something like:
--
--      (SELECT * from @PREFIX@_analyze_markers(
--          SELECT ARRAY_AGG(ROW(rd.osm_id, rd.way,
--                               rd."ref", rd.highway)::@PREFIX@_road_segment)
--          FROM @PREFIX@_osm_roads
--          WHERE ST_Intersects(!bbox!, way) ))

CREATE OR REPLACE FUNCTION
@PREFIX@_analyze_markers(segs @PREFIX@_road_segment[],
                         size_wanted INT)
RETURNS TABLE(
    way GEOMETRY,		-- Geometry of a resulting linestring
    repeat_key TEXT,		-- Repeat key used to control marker spacing
    picture_1 TEXT,		-- First graphic element
    label_1 TEXT,               -- First label
    picture_2 TEXT,		-- ...
    label_2 TEXT,
    picture_3 TEXT,
    label_3 TEXT,
    picture_4 TEXT,
    label_4 TEXT,
    picture_5 TEXT,
    label_5 TEXT,
    picture_6 TEXT,
    label_6 TEXT,
    picture_7 TEXT,
    label_7 TEXT,
    picture_8 TEXT,		-- Last graphic element
    label_8 TEXT		-- Last label
)
LANGUAGE plpgsql
AS
$@PREFIX@_analyze_markers$
BEGIN
    RETURN QUERY ((

        --------------------------------------------------------------
	--                                                          --
        -- Pathway for places that use route relations to represent --
	-- road routes                                              --
	--                                                          --
        --------------------------------------------------------------

        -- The first common table expression does the following things:
	--   + Unpacks the input array into a set of rows again.
	--   + Finds any route relations in which any input way participates
	--   + Downselects to 'road' routes
	--   + Looks up graphic elements corresponding to the routes
	--   + Packages tuples of (osm_id, way, highway,
	--                         network, ref, graphic_id)
	
        WITH q10 AS (
	    SELECT ln.osm_id AS id,
	           ln.way AS way,
		   ln.highway AS highway,
		   rt.route AS route,
		   rt.network AS network,
		   rt."ref" AS "ref",
		   g.id AS gid
            FROM UNNEST(segs) AS ln
	    JOIN @PREFIX@_shieldway sw
	      ON  sw.wayid = ln.osm_id
	    JOIN @PREFIX@_shieldroute rt
	      ON  rt.relid = sw.relid
	      AND rt.route = 'road'
	    LEFT JOIN osm_shield_graphics g
	      ON  g.route = rt.route
              AND g.network = rt.network
	      AND g."ref" = CASE
	                    WHEN rt."ref" IS NULL THEN ''
			    ELSE rt."ref"
			    END
	      AND g."size" = size_wanted
	    ORDER BY network, "ref"
      ),

      -- The graphic id in the above query may be NULL because the
      -- network is unknown, or there is no graphic for the ref. Replace
      -- the NULL graphic ID with an appropriate generic graphic ID, and
      -- include a text label to fill in the blank box.

      q15 AS (
      	  SELECT q10.id, q10.way, q10.network, q10.ref,
	         ( q10.gid, '' )::@PREFIX@_gid_label AS glabel
	  FROM q10
	  WHERE q10.gid IS NOT NULL
	  UNION ALL
	  SELECT q10.id, q10.way, q10.network, q10.ref, 
	         ( g.id, q10.ref )::@PREFIX@_gid_label AS glabel
	  FROM q10
	  JOIN osm_shield_graphics g
	  ON g.route = q10.route
	  AND g.network = CASE
	                   WHEN q10.highway IN ('motorway', 'trunk',
			                        'primary', 'secondary')
			   THEN 'generic-' || q10.highway
			   ELSE 'generic-other'
			   END
	  AND g.ref = '1x' || LENGTH(q10.ref)
	  WHERE q10.gid IS NULL
      ),

      -- The next common table expression repackages the result of the
      -- first so that all graphic elements for a given way appear packaged
      -- together in a single row. The result is a table of
      -- (osm_id, way, gid_label[])

      q20 AS (
        SELECT q15.id AS xid,             -- OSM ID
	       ST_Union(q15.way) AS way,  -- This union is always of a single
	                                  -- way with itself.
	       ARRAY_AGG(q15.glabel) AS glabels -- Graphic ID's, aggregated
	FROM q15
	GROUP BY xid
      ),

      -- The next common table expression merges the ways corresponding
      -- to each set of route relations being processed. The result has
      -- one row per distinct set, consisting of an ordered pair of
      -- the merged ways and the array of gids/labels. The ST_LineMerge
      -- call serves to coalesce ways that share a common set of routes
      -- into longer linestrings.

      q30 AS (
      	  SELECT ST_LineMerge(ST_Union(q20.way)) AS way,
	         q20.glabels AS glabels
	  FROM q20
	  GROUP BY glabels
      ),

      -- Now that we've produced pairs of (way, gids/labels) we want to
      -- bring in the graphic file names. They have not been carried up
      -- to this point, because the 'GROUP BY' in the step above would
      -- be inordinately slow if carried out over an arbitrary set of TEXT.
      
      q40 AS (
          SELECT q30.way as way,
	  @PREFIX@_make_repeat_key(q30.glabels) AS repeat_key,
	  @PREFIX@_gids_to_files(q30.glabels) AS flabels
	  FROM q30
      )

      -- Finally, we can package the query result.  The set of graphic ID's, 
      -- separated by vertical bars, becomes the repeat key, and the
      -- individual graphic files are in the result table. In this pipeline,
      -- none of the graphics requires labeling.
      SELECT 
      	     q40.way AS way,
	     q40.repeat_key AS repeat_key,
             q40.flabels[1].filename AS picture_1,
	     q40.flabels[1].label    AS label_1,
             q40.flabels[2].filename AS picture_2,
	     q40.flabels[2].label    AS label_2,
             q40.flabels[3].filename AS picture_3,
	     q40.flabels[3].label    AS label_3,
             q40.flabels[4].filename AS picture_4,
	     q40.flabels[4].label    AS label_4,
             q40.flabels[5].filename AS picture_5,
	     q40.flabels[5].label    AS label_5,
             q40.flabels[6].filename AS picture_6,
	     q40.flabels[6].label    AS label_6,
             q40.flabels[7].filename AS picture_7,
	     q40.flabels[7].label    AS label_7,
             q40.flabels[8].filename AS picture_8,
	     q40.flabels[8].label    AS label_8
      FROM q40

      ----------------------------------------------------------------
      --                                                            --
      -- End of the pathway that works with route relations         --
      --                                                            --
      ----------------------------------------------------------------

      ) UNION ALL (

      ----------------------------------------------------------------
      --                                                            --
      -- Pathway for places that represent road routes only with    --
      -- (possibly multiply-valued) 'ref' tags on the relation      --
      --                                                            --
      ----------------------------------------------------------------

      -- From all the input roads, isolate the ones that have 'ref'
      -- tags, are not link roads, and do not participate in route
      -- relations.  Group the ones that have the same sets of refs
      -- and are the same type of highway.  Merge the linstrings
      -- for the grouped sets. Pass on the merged ways, the
      -- highway type, and an array of refs.
      --
      -- TODO: The deprecated ref_1, ref_2, ... may have to be
      --       included here.

      WITH q110 AS (
        SELECT
	  ST_LineMerge(ST_Union(rd.way))                      AS way,
	  STRING_TO_ARRAY(rd."ref", ';')                      AS refs,
	  rd.highway                                          AS highway
	FROM UNNEST(segs) AS rd
	LEFT JOIN @PREFIX@_shieldway sw
	       ON sw.wayid = rd.osm_id
	WHERE rd.highway NOT LIKE '%_link'
	  AND rd."ref"   IS NOT NULL
	  AND sw.wayid   IS NULL
	GROUP BY highway, refs  
      ),

      -- Refs longer than 10 characters won't fit on a rendered shield,
      -- so it's not reasonable to keep them here. Discard them.

      q120 AS (
          SELECT q110.way                     AS way,
		 q110.highway                 AS highway,
		 (SELECT ARRAY_AGG(x)
		  FROM UNNEST(q110.refs) AS x
		  WHERE LENGTH(x) <= 10)      AS refs
          FROM q110
      ),

      -- For each set of merged roads, make a string of refs separated by
      -- newlines. Calculate the width and height, in characters, of a box
      -- big enough to display them

      q130 AS (
          SELECT q120.way                          AS way,
	                             -- The linestrings that get shields
				     
                 q120.highway                      AS highway,
		                     -- The type of highway
				     
		 ARRAY_TO_STRING(q120.refs, E'\n') AS refstack,
		                     -- The references, stacked with newlines

                 (SELECT MAX(LENGTH(x))
		    FROM UNNEST(q120.refs) AS x
		 )                                 AS w,
		                     -- The maximum width of a reference

		 CARDINALITY(q120.refs)            AS h
		                     -- The height of the stack of references
          FROM q120
      ),

      -- Go find the appropriate box to surround the labels

      q140 AS (
          SELECT q130.way                          AS way,
	         g.id                              AS gid,
	         g.filename                        AS picture,
		 q130.refstack                     AS label
	    FROM q130
	    JOIN osm_shield_graphics g
	      ON g.route = 'road'
	     AND g.network = CASE
	                     WHEN q130.highway IN ('motorway', 'primary',
			     	                   'trunk', 'secondary')
			     THEN 'generic-' || q130.highway
			     ELSE 'generic-other'
			     END
	     AND g.ref = q130.h || 'x' || q130.w
      )

      -- Package the result. We put all the stacked refs in a single marker

      SELECT q140.way                      AS way,
             q140.gid || '~' || q140.label AS repeat_key,
	     q140.picture                  AS picture_1,
	     q140.label		           AS label_1,
	     NULL                          AS picture_2,
	     NULL                          AS label_2,
	     NULL                          AS picture_3,
	     NULL                          AS label_3,
	     NULL                          AS picture_4,
	     NULL                          AS label_4,
	     NULL                          AS picture_5,
	     NULL                          AS label_5,
	     NULL                          AS picture_6,
	     NULL                          AS label_6,
	     NULL                          AS picture_7,
	     NULL                          AS label_7,
	     NULL                          AS picture_8,
	     NULL                          AS label_8
      FROM q140
		
     
    ));	    
END
$@PREFIX@_analyze_markers$
\g

-- Function: @PREFIX@_query_shields_line_all --
--
--	Calculate and return the ways on which highway shields
--	should be rendered, considering all highways of whatever
--	prominence within the bounding box.
--
-- Parameters:
--	bbox - Bounding box of the area being rendered
--	size_wanted - Nominal height of graphic elements in pixels
--
-- Results:
--	Returns a table giving ways that actually should be labeled with
--      markers. Each way has a repeat key that informs Mapnik what marker
--      set it belongs to (to eliminate markers that are too close together)
--      and up to eight occurrences of a file name containing the marker
--      graphic and a string (often empty) to layer atop the graphic.

CREATE OR REPLACE FUNCTION
@PREFIX@_query_shields_line_all(bbox GEOMETRY, size_wanted INT)
RETURNS TABLE(
    way GEOMETRY,		-- Geometry of a resulting linestring
    repeat_key TEXT,		-- Repeat key used to control marker spacing
    picture_1 TEXT,		-- First graphic element
    label_1 TEXT,               -- First label
    picture_2 TEXT,		-- ...
    label_2 TEXT,
    picture_3 TEXT,
    label_3 TEXT,
    picture_4 TEXT,
    label_4 TEXT,
    picture_5 TEXT,
    label_5 TEXT,
    picture_6 TEXT,
    label_6 TEXT,
    picture_7 TEXT,
    label_7 TEXT,
    picture_8 TEXT,		-- Last graphic element
    label_8 TEXT		-- Last label
)
LANGUAGE plpgsql
AS $@PREFIX@_query_shields_line_all$
BEGIN
    RETURN QUERY(
        SELECT * FROM @PREFIX@_analyze_markers(
        (

	    ---------------------------------------------------------------
            -- This is the base query that yields the roads that
	    -- might need markers. For this particular use case,
	    -- it returns all highways in the bounding box.
	    
            SELECT ARRAY_AGG(ROW(l.osm_id, l.way,
	                         l."ref", l.highway)::@PREFIX@_road_segment)
	    FROM @PREFIX@_line l
	    WHERE ST_Intersects(l.way, ST_SetSRID(bbox, 3857))
	    AND l.highway IS NOT NULL

            ---------------------------------------------------------------
	    
	 ),
         size_wanted));
END
$@PREFIX@_query_shields_line_all$
\g

-- Function: @PREFIX@_query_shields_roads_all --
--
--	Calculate and return the ways on which highway shields
--	should be rendered, considering all highways in '@PREFIX@_roads'
--	within the bounding box.
--
-- Parameters:
--	bbox - Bounding box of the area being rendered
--	size_wanted - Nominal height of graphic elements in pixels
--
-- Results:
--	Returns a table giving ways that actually should be labeled with
--      markers. Each way has a repeat key that informs Mapnik what marker
--      set it belongs to (to eliminate markers that are too close together)
--      and up to eight occurrences of a file name containing the marker
--      graphic and a string (often empty) to layer atop the graphic.

CREATE FUNCTION
@PREFIX@_query_shields_roads_all(bbox GEOMETRY, size_wanted INT)
RETURNS TABLE(
    way GEOMETRY,		-- Geometry of a resulting linestring
    repeat_key TEXT,		-- Repeat key used to control marker spacing
    picture_1 TEXT,		-- First graphic element
    label_1 TEXT,               -- First label
    picture_2 TEXT,		-- ...
    label_2 TEXT,
    picture_3 TEXT,
    label_3 TEXT,
    picture_4 TEXT,
    label_4 TEXT,
    picture_5 TEXT,
    label_5 TEXT,
    picture_6 TEXT,
    label_6 TEXT,
    picture_7 TEXT,
    label_7 TEXT,
    picture_8 TEXT,		-- Last graphic element
    label_8 TEXT		-- Last label
)
LANGUAGE plpgsql
AS $@PREFIX@_query_shields_roads_all$
BEGIN
    RETURN QUERY(
        SELECT * FROM @PREFIX@_analyze_markers(
        (

	    ---------------------------------------------------------------
            -- This is the base query that yields the roads that
	    -- might need markers. For this particular use case,
	    -- it returns all roads in the bounding box.
	    
            SELECT ARRAY_AGG(ROW(l.osm_id, l.way,
	                         l."ref", l.highway)::@PREFIX@_road_segment)
	    FROM @PREFIX@_roads l
	    WHERE ST_Intersects(l.way, ST_SetSRID(bbox, 3857))
	    AND l.highway IS NOT NULL

            ---------------------------------------------------------------
	    
	 ),
         size_wanted));
END
$@PREFIX@_query_shields_roads_all$
\g

-- Function: @PREFIX@_query_shields_roads_major --
--
--	Calculate and return the ways on which highway shields
--	should be rendered, considering major highways in '@PREFIX@_roads'
--	within the bounding box.
--
-- Parameters:
--	bbox - Bounding box of the area being rendered
--	size_wanted - Nominal height of graphic elements in pixels
--
-- Results:
--	Returns a table giving ways that actually should be labeled with
--      markers. Each way has a repeat key that informs Mapnik what marker
--      set it belongs to (to eliminate markers that are too close together)
--      and up to eight occurrences of a file name containing the marker
--      graphic and a string (often empty) to layer atop the graphic.
--
-- A highway is considered to be 'major' if it is a motorway, trunk
-- or primary way.

CREATE FUNCTION
@PREFIX@_query_shields_roads_major(bbox GEOMETRY, size_wanted INT)
RETURNS TABLE(
    way GEOMETRY,		-- Geometry of a resulting linestring
    repeat_key TEXT,		-- Repeat key used to control marker spacing
    picture_1 TEXT,		-- First graphic element
    label_1 TEXT,               -- First label
    picture_2 TEXT,		-- ...
    label_2 TEXT,
    picture_3 TEXT,
    label_3 TEXT,
    picture_4 TEXT,
    label_4 TEXT,
    picture_5 TEXT,
    label_5 TEXT,
    picture_6 TEXT,
    label_6 TEXT,
    picture_7 TEXT,
    label_7 TEXT,
    picture_8 TEXT,		-- Last graphic element
    label_8 TEXT		-- Last label
)
LANGUAGE plpgsql
AS $@PREFIX@_query_shields_roads_major$
BEGIN
    RETURN QUERY(
        SELECT * FROM @PREFIX@_analyze_markers(
        (

	    ---------------------------------------------------------------
            -- This is the base query that yields the roads that
	    -- might need markers. For this particular use case,
	    -- it returns all motorways, trunks and primary highways
	    -- in the bounding box.
	    
            SELECT ARRAY_AGG(ROW(l.osm_id, l.way,
	                         l."ref", l.highway)::@PREFIX@_road_segment)
	    FROM @PREFIX@_roads l
	    WHERE ST_Intersects(l.way, ST_SetSRID(bbox, 3857))
	    AND l.highway IN ('motorway', 'trunk', 'primary')

            ---------------------------------------------------------------
	    
	 ),
         size_wanted));
END
$@PREFIX@_query_shields_roads_major$
\g

-- Function: @PREFIX@_query_shields_roads_motorway --
--
--	Calculate and return the ways on which highway shields
--	should be rendered, considering only motorways in '@PREFIX@_roads'
--	within the bounding box.
--
-- Parameters:
--	bbox - Bounding box of the area being rendered
--	size_wanted - Nominal height of graphic elements in pixels
--
-- Results:
--	Returns a table giving ways that actually should be labeled with
--      markers. Each way has a repeat key that informs Mapnik what marker
--      set it belongs to (to eliminate markers that are too close together)
--      and up to eight occurrences of a file name containing the marker
--      graphic and a string (often empty) to layer atop the graphic.

CREATE FUNCTION
@PREFIX@_query_shields_roads_motorway(bbox GEOMETRY, size_wanted INT)
RETURNS TABLE(
    way GEOMETRY,		-- Geometry of a resulting linestring
    repeat_key TEXT,		-- Repeat key used to control marker spacing
    picture_1 TEXT,		-- First graphic element
    label_1 TEXT,               -- First label
    picture_2 TEXT,		-- ...
    label_2 TEXT,
    picture_3 TEXT,
    label_3 TEXT,
    picture_4 TEXT,
    label_4 TEXT,
    picture_5 TEXT,
    label_5 TEXT,
    picture_6 TEXT,
    label_6 TEXT,
    picture_7 TEXT,
    label_7 TEXT,
    picture_8 TEXT,		-- Last graphic element
    label_8 TEXT		-- Last label
)
LANGUAGE plpgsql
AS $@PREFIX@_query_shields_roads_motorway$
BEGIN
    RETURN QUERY(
        SELECT * FROM @PREFIX@_analyze_markers(
        (

	    ---------------------------------------------------------------
            -- This is the base query that yields the roads that
	    -- might need markers. For this particular use case,
	    -- it returns all motorways in the bounding box.
	    
            SELECT ARRAY_AGG(ROW(l.osm_id, l.way,
	                         l."ref", l.highway)::@PREFIX@_road_segment)
	    FROM @PREFIX@_roads l
	    WHERE ST_Intersects(l.way, ST_SetSRID(bbox, 3857))
	    AND l.highway = 'motorway'

            ---------------------------------------------------------------
	    
	 ),
         size_wanted));
END
$@PREFIX@_query_shields_roads_motorway$
\g