Calculating linestring length in metres

Follow
Avatar
Andrew

Hello

I have a spatial table containing linestrings- and would like to have a chart in Immerse (say a bar chart) which shows the length of each geometry in metres.

the ST_TRANSFORM function in Immerse doesn't seem to know my coordinate system- do I need to load this somewhere ??

I can calculate the length in Postgres, and include as a static column when it gets imported to the omnisci database- but I'm guessing it can be done in omnisci

image|690x366

thanks

Andrew

0

Comments

9 comments

Sort by Date Votes
  • Avatar
    Candido Dessanti

    Hi,

    Yes, it's possible using the st_transform; if the SRID is missing, you can set it with the st_srid function.

    omnisql> create table test_linestring2(id integer, line_string geometry(linestring);
omnisql> insert into test_linestring2 values(1, 'LINESTRING(0 0,1 1,1 2)');
omnisql> select st_length(st_transform(line_string,900913)) as length_in_meter 
from test_linestring2;
ST_Transform: unexpected input SRID, unable to transform

    The error is because, at table creation, there is no SRID for the geometry column (I guess the SQLImporter created a table inferring the datatypes)

    omnisql> show create  table test_linestring2;
Result
CREATE TABLE test_linestring2 (
  id INTEGER,
  line_string GEOMETRY(LINESTRING) ENCODING NONE);

    Anyway, there isn't any problem because you can set the SRID for the geometry in the query. 

    omnisql> select st_length(st_transform(ST_SetSRID(line_string,4326),900913)) as length_in_meter from test_linestring2;
length_in_meter
268792.59590872

    CReating the table specifying the SRID it will be a better solution.

    omnisql> create table test_linestring(id integer, line_string geometry(linestring,4326));
omnisql> insert into test_linestring values(1, 'LINESTRING(0 0,1 1,1 2)');
omnisql> select st_length(st_transform(ST_SetSRID(line_string,4326),900913)) as length_in_meter from test_linestring;
length_in_meter 
268792.5879772492

    The DDL of the table is this one

    omnisql> show create table test_linestring ;
Result
CREATE TABLE test_linestring (
  id INTEGER,
  line_string GEOMETRY(LINESTRING, 4326) ENCODING COMPRESSED(32));

    The line_string column is compressed (the default of 4326), using half of the memory of an uncompressed column with a negligible loss in precision.

    Let me know if it helped.

    Candido

    0
    Comment actions Permalink
  • Avatar
    Andrew

    ok thanks Candido, that makes sense- I will give it a try

    Just to clarify- I'm assuming it is best to create an omnisci view with the above query, and then use the view as the source for a chart or table in Immerse ?? (rather than a 'custom SQL measure' in Immerse, with the table as data source)

    0
    Comment actions Permalink
  • Avatar
    Candido Dessanti

    Hi,

    Well, I think it's better to use a custom measure or dimension because you can also apply it to other tables, but you can use it this way if needed/preferred.

    I often used views joining tables together or doing something that wasn't viable doing with immerse, but lately, we are filling a lot of gaps so that using Views won't be needed anymore (or at least will be reduced by a lot)

    Candido

    0
    Comment actions Permalink
  • Avatar
    Andrew

    thanks Candido

    I've played around with the query in omnisci, but it does not behave as I'd expect (or how the same data & query behaves in PostGIS) The geometry seems to be correctly set to SRID4326, but transforming to 28354 and the st_length function produce an incorrect result

    I've attached a sample file with a single record tbl_test.geojson.txt|attachment (838 Bytes)

    thanks

    image|429x499

    0
    Comment actions Permalink
  • Avatar
    Candido Dessanti

    You need to transform to 900913 to get the result in meters, like in the previous example.

    st_length(st_transform(line_string,900913))

    Candido

    0
    Comment actions Permalink
  • Avatar
    Andrew

    thanks Candido, but transforming to that coordinate system introduces a significant error (~20%)- so in my case, several kilometres For example, in the sample above- the length should be 12,142m, whereas it is calculated as 14,914m

    I'll forget it for now, and calculate lengths outside of omnisci

    Andrew

    0
    Comment actions Permalink
  • Avatar
    Candido Dessanti

    Hi,

    calculating the length in degree has a similar error?

    0
    Comment actions Permalink
  • Avatar
    Andrew

    Hi Candido

    I'm not sure- I did some testing in Postgres, by roundtripping data as follows: a. original data in 28354- export to geojson b. import & transform to 4326 (decimal degrees)- export to geojson c. import & transform back to 28354 and the distances between a. & c. tallied

    I'm no expert in this- but believe that the EPSG people got real upset and refused to give 900913 an official code as it was inaccurate & not a proper projection GeoGarage blog: Advisory notice on "Web Mercator"

    For now, I've worked around the problem by calculating distance outside omnisci, and importing it as a static value

    Andrew

    0
    Comment actions Permalink
  • Avatar
    Candido Dessanti

    Hi,

    dug a little on SRIDs and the right one to use with geometries heavily depends on the geographic location of points, so the 28354 is accurate for Australia, but it's inadequate for Italy, where you need to use the 3003.

    the best way would be to cast everything in geography, but this would slow down the calculation

    0
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post