Calculating linestring length in metres
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
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.
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)
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)
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 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
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
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
Please sign in to leave a comment.