PostGIS – Calculate LineString Length from WGS84 After Transforming from OSGB 36

coordinate systempostgisqgiswgs84

I am having problems getting lengths from multilinestrings after transforming data from EPSG:27700 – OSGB 1936 / British National Grid to EPSG:4326/ WGS 84.

I'm doing this conversion to import a roads shapefile into a PostGIS database that uses WGS84 as default. However, when I try to calulate lengths, I'm having weird readings.

Firstly I thought about some incorrect transformation, but after doing some additional tests in an empty database importing the layer without transforming it (OSGB 1936 reference), I´m still puzzled:

The following query returns contradictory results

SELECT  
    ST_Length(geom) AS length_OSGB
    ,ST_Length_Spheroid(ST_Transform(geom,4326),'SPHEROID["WGS 84",6378137,298.257223563]') As length_WGS84    
FROM osgb36_data
WHERE id = 108

this yields 338 meters for the OSGB36 length and 482 meters for the WGS84 length. The right one is 338 meters.

I've done the importation to the database using QGIS and DBmanager.

Also, I had to guess the initial projectios, as the .prj was missing, but projecting the result over google maps results in a perfect match.

Any hint is welcome

EDIT:

This is the WKT for the element in the example, as suggested:

    SRID=27700;MULTILINESTRING((
 423216.279 574665.249 0,
 423206.315 574708.077 44.649,
 423158.458 574896.911 242.525,
 423132.406 574993.061 344))

Well, that's weird, seems like they were using the z coordinate to register the accumulated distance for each point in the linestring… That could explain the issue with the wrong distances

Best Answer

The length functions work differently with 3D linestring geometries:

  • ST_Length - returns 2D distances for geometry types, and oddly 3D distances for geography types (but not in this question)
  • ST_Length_Spheroid - returns 3D distances for geometry types

Your example is in 3D, so it will calculate the 3D length with ST_Length_Spheroid and the 2D length with ST_Length on a geometry.

However, if you always want 2D lengths, you can force the geometry to a 2D linestring using either ST_Force2D or ST_Force_2D function (the name change in PostGIS 2.1).

SELECT ST_Length(geom) AS length_27700,
  ST_Length_Spheroid(ST_Transform(ST_Force2d(geom), 4326), spheroid) AS length_spheroid_2d,
  ST_Length_Spheroid(ST_Transform(geom, 4326), spheroid) AS length_spheroid_3d
FROM (
  SELECT 'SRID=27700;MULTILINESTRING((
      423216.279 574665.249 0,
      423206.315 574708.077 44.649,
      423158.458 574896.911 242.525,
      423132.406 574993.061 344))'::geometry AS geom,
    'SPHEROID["WGS 84",6378137,298.257223563]'::spheroid
) AS f;

  length_27700   | length_spheroid_2d | length_spheroid_3d
-----------------+--------------------+--------------------
 338.39264086563 |   338.515774984236 |   482.622196178051
Related Question