Skip to content

Instantly share code, notes, and snippets.

@dch0ph
Last active April 3, 2024 15:02
Show Gist options
  • Save dch0ph/5d152262ac69798d0f321885af86a5e0 to your computer and use it in GitHub Desktop.
Save dch0ph/5d152262ac69798d0f321885af86a5e0 to your computer and use it in GitHub Desktop.
Proof of principle for extending rendering of access tags
/* Adapted from https://github.com/imagico/osm-carto-alternative-colors/tree/591c861112b4e5d44badd108f4cd1409146bca0b/sql/roads.sql */
/* Numerical scale for 'severity' of access restriction, including NULL if null input */
CREATE OR REPLACE FUNCTION carto_restriction_severity(highway_type text, accesstag text)
RETURNS integer
LANGUAGE SQL
IMMUTABLE PARALLEL SAFE
AS $$
SELECT
CASE
WHEN accesstag IN ('yes', 'designated', 'permissive', 'customers') THEN 0
WHEN accesstag IN ('destination', 'delivery') THEN
CASE WHEN highway_type = 'type_footway' THEN 0 ELSE 1 END
WHEN accesstag IN ('no', 'private') THEN 2
ELSE NULL::INTEGER
END
END
$$;
/* Classify highways into categories. Default is NULL in which case simple access tag will be used */
CREATE OR REPLACE FUNCTION carto_access_type(highway text)
RETURNS text
LANGUAGE SQL
IMMUTABLE PARALLEL SAFE
AS $$
SELECT
CASE
WHEN highway IN ('motorway', 'motorway_link', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary',
'secondary_link', 'tertiary', 'tertiary_link', 'residential', 'unclassified', 'service',
'road') THEN 'type_motorvehicle'
WHEN highway = 'pedestrian' THEN 'type_pedestrian'
WHEN highway IN ('footway', 'steps') THEN 'type_footway'
ELSE NULL
END
END
$$;
/* Return int_access value which will be used to determine access marking. */
/* Only a restricted number of types can be returned, with NULL corresponding to no access restriction */
CREATE OR REPLACE FUNCTION carto_highway_int_access_internal (highway text, "access" text, foot text, tags hstore, OUT int_access text)
language plpgsql
immutable
AS $$
DECLARE
highway_type text DEFAULT NULL;
specific_severity INTEGER DEFAULT NULL;
overall_severity INTEGER DEFAULT NULL;
BEGIN
SELECT INTO highway_type carto_access_type(highway);
IF highway_type = 'type_motorvehicle' THEN
SELECT INTO specific_severity MAX(severity) FROM (VALUES (carto_restriction_severity(highway_type, tags->'motor_vehicle')),
(carto_restriction_severity(highway_type, tags->'motorcar')), (carto_restriction_severity(highway_type, tags->'vehicle'))) AS tmp(severity);
ELSIF highway_type = 'type_footway' THEN
SELECT INTO specific_severity carto_restriction_severity(highway_type, foot);
ELSIF highway_type = 'type_pedestrian' THEN
SELECT INTO specific_severity 2;
END IF;
SELECT INTO overall_severity
CASE WHEN specific_severity IS NOT NULL THEN specific_severity ELSE carto_restriction_severity(highway_type, "access") END;
SELECT INTO int_access
CASE overall_severity
WHEN 0 THEN NULL
WHEN NULL THEN NULL
WHEN 1 THEN 'destination'
WHEN 2 THEN 'no'
END;
END;
$$;
CREATE OR REPLACE FUNCTION carto_highway_access_modifier (highway text, tags hstore, OUT access_modifier text)
language plpgsql
immutable
AS $$
DECLARE
highway_type text DEFAULT NULL;
BEGIN
SELECT INTO highway_type carto_access_type(highway);
SELECT INTO access_modifier
CASE WHEN highway_type = 'type_motorvehicle' THEN
CASE
WHEN (tags->'psv' IN ('yes', 'designated')) OR (tags->'bus' IN ('yes', 'designated')) THEN 'psv'
WHEN tags->'hgv' IN ('yes', 'designated') THEN 'hgv'
ELSE NULL
END
ELSE NULL
END;
END;
$$;
ALTER TABLE planet_osm_line DROP COLUMN IF EXISTS int_access;
ALTER TABLE planet_osm_line
ADD int_access text GENERATED ALWAYS AS (CASE WHEN highway IS NOT NULL THEN carto_highway_int_access_internal(highway, "access", foot, tags) ELSE NULL END) STORED;
table: &tunnels_sql |-
(SELECT
way,
(CASE WHEN feature IN ('highway_motorway_link', 'highway_trunk_link', 'highway_primary_link', 'highway_secondary_link', 'highway_tertiary_link') THEN substr(feature, 0, length(feature)-4) ELSE feature END) AS feature,
[...]
int_access AS access,
access_modifier,
[...]
FROM ( -- subselect that contains both roads and rail
SELECT
way,
'highway_' || highway AS feature, --only motorway to tertiary links are accepted later on
[...]
int_access,
CASE WHEN int_access IS NULL THEN NULL::text ELSE carto_highway_access_modifier (highway, tags) END AS access_modifier,
[...]
// Taken from osm-carto-alternative-colors
@access-marking-psv: lighten(@transportation-icon, 28%);
@access-marking-hgv: #888;
[...]
[access = 'no'] {
[feature = 'highway_service'][service = 'INT-normal'] {
[zoom >= 15] {
access/line-color: @access-marking;
[access_modifier = 'psv'] { access/line-color: @access-marking-psv; }
[access_modifier = 'hgv'] { access/line-color: @access-marking-hgv; }
access/line-join: round;
access/line-cap: round;
[...]
}
}
}
@dch0ph
Copy link
Author

dch0ph commented Mar 30, 2024

Notes

  • As noted, the code is strongly inspired by imagico's demonstration, but with an emphasis on a framework for development / discussion rather than trying to render access restrictions on all highway types.
  • Creates new column, int_access, in planet_osm_line, which crunches various access tags on highways to an overall result with a restricted set of values.
  • This column is created as a stored GENERATED column, since it is almost certainly more efficient to calculate this value once per way rather than in every roads query. But this is not essential.
  • The processing of access tags involves sequential steps, and so it is cleaner and easier to maintain to use some PLPGSQL functions rather than pure inline SQL.
  • The highway type is first classified into highway_type, depending on which access tags are being considered. Currently three types considered: obvious vehicle road ways (considering vehicle access tags), foot ways (considering foot) and highway=pedestrian (with implicit restricted vehicle access).
  • Only the overall access tag is considered if the highway does not fit into these categories, i.e. defaults to existing behaviour.
  • Each tag to be considered is reduced into a numerical severity ranking: 0 represents "no access marking", 2 represents "marking for no access", 1 represents an "intermediate" (typically access=destination). Footways only support "no" vs. "yes".
  • In the case of roads, for example, multiple tags can be used for access. Here motorcar , motor_vehicle and vehicle are considered for road types, since this covers most cases where a road might be not be open to vehicles. Rather than prioritising specific tags, "approximately equivalent" tags are combined by finding the most severe restriction.
  • The "overall severity" is then converted to a simple NULL, destination or no scale, stored as int_access, which is fully compatible with the existing usage of access in the MSS. Currently, int_access is simply presented as access to the MSS, but it would be better to modify the MSS to int_access to clearly delineate the calculated column from the original.
  • As in imagico's demonstration, the road access marking is tinted to indicate where a restricted road is available to designated traffic.
  • This information is contained in access_modifier, which can be easily generated on the fly - the carto_highway_access_modifier function is only called for the relatively small number of ways with access restrictions.
  • For demonstration purposes, only two tintings are considered: blue for roads open to busses and/or public service vehicles, and (for fun), a dark grey for HGV only routes (e.g. HGV lanes in fuel stations).
  • As for the overall access restriction, "almost equivalent" tags are combined e.g. psv and bus.
  • The problematic highway=path could be dissolved into cycleway, bridleway, footway, depending on access tags in a Lua transform. Effectively highway=path and bicycle=yes is a cycleway, and it simplifies the MML/MMS/XML considerably to get rid of it early. For the moment, however, we can stay with the current scheme of accepting highway=path + int_access=yes as equivalent to highway=cycleway .

Issues

  • The use of a GENERATED column would be a novelty, and sits between crunching the tags in Lua on import (which makes changes difficult) and using in-line SQL (which risks being extremely slow). The rendering can be changed without a database re-import by editing and re-running access.sql, but is a bit more cumbersome / unfamiliar than re-running carto and changing the XML file. GENERATED columns appeared in version 12 of PostgresSQL.
  • Dissolving highway=path in Lua would database reload. In which case, it would make sense to move foot and bicycle columns into hstore, since these have been "processed" into int_access. This would neaten the code. But this perhaps best done separately.
  • The use of access_modifier seems to generate a lot of additional mapnik XML. At least part of this is simply the number of the combinations needed of road type times road width times access colours (3 here). It will also be linked to the number of road layers, i.e. an efficient solution for multiple tintings will probably require rationalisation of the road rendering.

@dch0ph
Copy link
Author

dch0ph commented Apr 1, 2024

Examples

Above is a current Carto rendering. Below new access marking in a Carto-derived personal style (ignore the non-access related differences):

PSV access
Bus only road on bridge. access=no, bicycle=yes, bus=yes, foot=yes.

image

image

The presence of bus=yes means the access=no restriction is rendered in pale blue.

Honouring motor_vehicle

Residential highway tagged with motor_vehicle=destination.

image

image

Honouring foot

North-south footway tagged with highway=footway, foot=private
image
Screenshot 2024-04-01 120915

More tagging normalisation

Cycleway tagged as highway=path, foot=yes, bicycle=yes

image

image

Carto currently only accepts bicycle=designated.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment