Create Line Geometry similarly to ST_Point



  • Avatar
    Candido Dessanti

    Hi Fredrick,

    We have an experimental parameter called enable-interoperability that make usable the sqlite functions on projections query, so at some extent its possible to cast numbers into dictionary encoded none strings, but it unlikely you will be able to build an WKT and use it in omnisql to directly insert linestrings.

    I hope those limitations will be removed soon

    Comment actions Permalink
  • Avatar
    Candido Dessanti

    Anyway, If you haven't any other tools or for whatever reason, you want to use just the omnisql tool, you can do the job using the parameter I talked to you about before.

    Start the database enabling --enable-interoperability; this will enable some text function on simple projection query.

    so assuming you have two tables that will call linestring_source and linestring_target

    CREATE TABLE linestring_source (
      lon0 FLOAT,
      lat0 FLOAT,
      lon1 FLOAT,
      lat1 FLOAT,
      wkt TEXT ENCODING DICT(32));
    CREATE TABLE linestring_target (

    you can write a query that, with some string concatenations, will build a WKT

    omnisql> select 'LINESTRING('||lon0||' '||lat0||','||lon1||' '||lat1||')' as wkt from linestring_source;
    LINESTRING(42.2000007629395 75.1999969482422,43.2000007629395 75.1999969482422)
    1 rows returned.

    then copy to an external file, and copy that file to the target table

    omnisql> copy (select 'LINESTRING('||lon0||' '||lat0||','||lon1||' '||lat1||')' from linestring_source) to '/tmp/ls.csv' with (header='false');
    omnisql> copy linestring_target from '/tmp/ls.csv' with(header='false');
    Loaded: 1 recs, Rejected: 0 recs in 0.022000 secs

    now you have the table with a linestring geometry, populated with the linestring.

    I did some benchmarks, using a quad-core processor with the NYC Taxi dataset, and create a table with roughly 14 million records, took 36051+52975 to unload and load (36051+11162 using a target temporary table) so the TP is floating around 157000 and 296000 records per second; the performance isn't so bad, but everything it's a little tricky.

    Let me know if this helps

    Regards, Candido

    Comment actions Permalink
  • Avatar

    Hi Candido,

    thank you so much. I will try this out right away. Yes I was aware that this was going to become a bit hacky but this is very impressive.

    I might also try to solve this as a python UDF at some point which might make it slightly less "tricky".

    Best Frederic

    Comment actions Permalink

Please sign in to leave a comment.