Length of trail segments

[kylebarron]

It would be cool to have lengths of trail between trail junctions. Something like:

(From naturalatlas.com) Caltopo has it too.

It looks like it's relatively easy to get the length of the LineString in PostGIS: https://gis.stackexchange.com/a/170828

The hard part would be trying to split trails at vertices. I would expect that if you ran ST_Length on the existing path data, it would include the length of the full original OSM way, which would be longer than the sections between trail junctions, though I should test this.

[kylebarron]

I tried using ST_Length(geom::geography), and got the error:

Error: Postgis Plugin: ERROR:  Only lon/lat coordinate systems are supported in geography.
in executeQuery Full sql was: 'SELECT ST_AsBinary("geometry") AS geom,"bicycle","brunnel","class","foot","horse","indoor","layer","level","mtb_scale","oneway","ramp","service","subclass","surface","trail_length" FROM (SELECT geometry, class, subclass, oneway, ramp, brunnel, service, layer, level, indoor, bicycle, foot, horse, mtb_scale, surface, trail_length FROM layer_transportation(ST_SetSRID('BOX3D(-7934927.906284147 5635396.347352906,-7924838.218550504 5645486.035086549)'::box3d, 900913), z(136495))) AS t'

    at Error (native)

I think this is because the existing srid is 900913, not WGS84

openmaptiles/layers/transportation/transportation.yaml

Lines 138 to 141 in d325605

	datasource:
	geometry_field: geometry
	srid: 900913
	query: (SELECT geometry, class, subclass, oneway, ramp, brunnel, service, layer, level, indoor, bicycle, foot, horse, mtb_scale, surface FROM layer_transportation(!bbox!, z(!scale_denominator!))) AS t

So instead, I'm testing out first reprojecting to wgs84, then casting to geometry:

CASE WHEN highway IN ('pedestrian', 'path', 'footway', 'cycleway', 'steps', 'bridleway', 'corridor')
    THEN round(ST_Length(ST_Transform(geometry, 4326)::geography))::int
    ELSE NULL::int
END AS "trail_length",