1

I would expect each segment length (ST_Length) in this query to be exactly or close to 403 meters. Why do these distances vary?

SELECT n,
       ST_SRID(ST_LineSubstring(ST_LineSubstring(the_geom, 403.00*n/length,
         LEAST(403.00*(n+1)/length, 1.0)), 0.0, 1 )::GEOGRAPHY ),
       ST_LineSubstring(the_geom, 403.00*n/length,
         LEAST(403.00*(n+1)/length, 1.0)) AS the_geom,
       ST_Length(ST_LineSubstring(ST_LineSubstring(the_geom, 403.00*n/length,
         LEAST(403.00*(n+1)/length, 1.0)), 0.0, 1 )::GEOGRAPHY )
FROM (
  SELECT ST_Segmentize(ST_LineMerge('MULTILINESTRING((-81.2190071919865 34.1247822557825,-81.2155187121759 34.1240070380468,-81.212934653057 34.1231026173552,-81.2110612101958 34.1223273996195,
                                                     -81.2091231658566 34.121099971538,-81.2078311362971 34.1202601523243,-81.2053762801341 34.1192265286768,-81.2019524018015 34.1174176872935,
                                                     -81.1980763131231 34.1151566355644,-81.1940710214888 34.1129601853133,-81.1920683756716 34.1116681557539,-81.1912285564579 34.1092779010689,
                                                     -81.1897427224646 34.1046265946548,-81.1883214899491 34.1008797089323,-81.1872232648236 34.0970036202539,-81.181473733284 34.113735403049,
                                                     -81.1851560175285 34.1222627981415,-81.1903887372443 34.1343432745225,-81.1871586633456 34.1415140385775))')::geography, 403.00)::geometry AS the_geom,
         ST_Length(geom::geography) AS length    
  FROM public."ticket_subdivide_lineTesting"
) AS t
CROSS JOIN generate_series(0,10000) AS n
WHERE n*403.00/length < 1 --limit 1
  • the length is computed using the geom field from public."ticket_subdivide_lineTesting but the operations occurs on the manually create the_geom field, which is likely of another length – JGH Jul 11 '18 at 16:08
  • this?: ST_Length(ST_LineSubstring(ST_LineSubstring(the_geom, 403.00*n/length, LEAST(403.00*(n+1)/length, 1.0)), 0.0, 1 )::GEOGRAPHY ) – Matt Jul 11 '18 at 16:13
  • no, ST_Length(geom::geography) AS length (5 lines from the bottom) – JGH Jul 11 '18 at 16:15
  • I don't follow you on that. 'length' is the total length of the line and is just used in the st_linesubstring function. It should be the same across the board, correct? – Matt Jul 11 '18 at 16:30
  • yes, but in the code you are not computing the manually specified line length but of a geometry out of a table that is not used anywhere else. (likely a typo) – JGH Jul 11 '18 at 18:13
0

This behavior is caused by st_linesubstring, not by st_segmentize.

You are computing the line substring using the data in lat-long 4326. The computed fractions in st_linesubstring are applied using the line length in the projection unit, so in degrees. For example, should you cut the original line in 10, each substring will be 1/10 * length in degrees.

Since a degree corresponds to a different length in meter in the X or Y axis, and the length of a degree of longitude depends on the latitude, this length is meaningless.

Then, you convert these substring to geography in order to get the segment length in meters. The calculation takes into account the latitude/longitude of each vertex and returns the true distance, which is now different for each segment.

To overcome this, you could first project your data to a projection in meters, and that is suitable to your region to reduce distortion (UTM for example).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.