Standardize priority expressions

[pnorman]

It's common to use some kind of custom ordering, because alphanumeric sorting is not useful for most OSM data. Some examples are

turning-circle-fill

...
  JOIN (VALUES
    ('tertiary', 1),
    ('unclassified', 2),
    ('residential', 3),
    ('living_street', 4),
    ('service', 5),
    ('track', 6)
  ) AS v (highway, prio)
...
ORDER BY ..., v.prio

stations

...
  CASE railway
    WHEN 'station' THEN 1
    WHEN 'subway_entrance' THEN 3
    ELSE 2
  END
    AS prio
...
ORDER BY prio

various

...
ORDER BY
...
  z_order,
  CASE WHEN substring(feature for 8) = 'railway_' THEN 2 ELSE 1 END,
  CASE WHEN feature IN ('railway_INT-preserved-ssy', 'railway_INT-spur-siding-yard', 'railway_tram-service') THEN 0 ELSE 1 END,
  CASE WHEN access IN ('no', 'private') THEN 0 WHEN access IN ('destination') THEN 1 ELSE 2 END,
  CASE WHEN int_surface IN ('unpaved') THEN 0 ELSE 2 END

aeroways

...
ORDER BY
...
  CASE WHEN aeroway = 'runway' THEN 10 ELSE 0 END

We manage to use

• z_order,
• value statements with priority,
• case statements in ORDER BY,
• case statements in column lists referenced by column name,
• lists that increment by 1, and
• lists that increment by 10.

These all work, but it'd be nice to standardize so I know which way to write.

I am not a particular fan of value statements. Where we have case statements in column lists we don't use that column anywhere, so I'd rather not use those either. Instead, I prefer putting all the ordering logic into the ORDER BY.

There is a weak convention that when you have two types, the more important is > the other, e.g. primary roads > secondary roads. This is what z_order uses, where primary road z_order > a secondary road z_order.

I haven't touched on ASC vs DESC and the reason is which you want depends on if the layer is being used for something that paints over itself or collides. For the former, you want the most important last, for the latter, you want it first

[matthijsmelissen]

I prefer putting all the ordering logic into the ORDER BY.

I agree, that's by far the cleanest. We should rewrite the queries that do not follow that format.

[jeisenbe]

I'm working on this, but I'm a bit baffled by the SQL query in turning-circle-casing.

[jeisenbe]

There is a weak convention that when you have two types, the more important is > the other, e.g. primary roads > secondary roads. This is what z_order uses, where primary road z_order > a secondary road z_order.

So do we want:

          CASE power
            WHEN 'tower' THEN 2
            WHEN 'pole' THEN 1
            ELSE NULL
          END DESC

rather than the current:

          CASE power
            WHEN 'tower' THEN 1
            WHEN 'pole' THEN 2
            ELSE NULL
          END

(With implied "ASC" ascending order)?

[pnorman]

Yes.

If it should be ascending or descending depends on the layer. Generally text layers need most important first and drawing layers need most important last.