We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


load geography table from table of lat/long using ST_GeographyFromText — Vertica Forum

load geography table from table of lat/long using ST_GeographyFromText

Hello,

 

I have a Vertica table with millions of positions (positiondate timestamp, latitude numeric, longitude numeric) and I want to load these into another Vertica table with a geography type column.

 

Most of the examples I have found use an insert with values as below (this works fine):

insert into table(geog) values(ST_GeographyFromText('POINT(-85 15)');

 

I want to insert by selecting from the positions table similar to below (this does not work: could not find function defintion):

insert into positions(geog) select ST_GeographyFromText('POINT('||cast(longitude as varchar(10)) ||' '|| cast(latitude as varchar(10))||')') from positions;

 

I am stumped. Any ideas or tips? I am used to casting as geography type in SQL Server, is there something similar in Vertica?

 

Thanks in advance for any ideas!

Comments

  • Hi jdobbins,

     

    Have you installed HP Vertica Place? You didn't mention it in your post so I just want to make sure you have that installed before we get too far ahead of ourseleves. You'll need to have the matching server version. For example: HP Vertica 7.1.1 and HP Vertica Place 7.1.1. You won't get access to the GEOGRAPHY or GEOMETRY type until you have the HP Vertica Place package installed.

     

    Here's a link to the install guide if you don't know where to find it: http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/Place/InstallingPlaceOverview.htm

     

    I think you are really close to the answer. Your use case is very similar to a blog post I wrote recently. Give our GitHub example a look (https://github.com/vertica/Place/blob/master/HP_Vertica_Place_Example_Git.sql).

     

    I think this line in particular might get you on the right track:

    COPY usr_data(usr_id, date_time, x filler long varchar, y filler long varchar, g as ST_GeomFromText('POINT(' || x || ' ' || y || ')')) from LOCAL 'place_output.csv' delimiter ',' enclosed by '';

     

    Doing the load like this will allow you to avoid loading the data once and then minipulating it. This method performs the data minipulation on load.

     

    Give this a try and then let me know if you have any other issues. I'm happy to help you get through this.

     

     

    -Casey

  • Hi Casey,

     

    Thanks for the quick response. I will give your method a try (I am about to be out of the office for a week). I do have Place installed and I can access the functions. 

     

    I have been loading Vertica using Kettle (a job loads data from Oracle into Vertica). I had hoped there would be a way to do table to table loading/conversion, but maybe I can figure out how to automate this using Kettle.

     

    Thanks again,

     

    -Jimmy

     

     

     

     

  • Hi Jimmy,

     

    I needed a little more time to think about this one, but I've got a solution for you:

     

    CREATE TABLE tmp AS SELECT posistiondate, ST_GeographyFromText('POINT('||long||' '||lat||')') from foo_place;

     

    This will take your table and create a new table with a column for timestamps and a column for GEOGRAPHY data-- combining your data from your long and lat columns.

     

    Hopefully you see this before you head out of town, or you see it when you get back.

     

     

    -Casey

  • Hi Casey,

     

    I am in for a little bit today before I am out tomorrow and Wednesday. Thanks for the suggested SQL, I am still getting the 2858 error, "could not find function definition." To make things simple, I have everything in the public schema (which is where the Vertica Place functions are stored). I think everything is installed correctly since I can call the ST_GeographyFromText function without error (as follows):

     

    SELECT public.ST_GeographyFromText('POINT(-85.2312345 33.2645612)' USING PARAMETERS ignore_errors='y');

     

    In the positions table I have latitude and longitude stored as numeric(15,6). In the following query, I cast them to varchar(15) in the following script that returns the same "could not find function definition" 2858 error:

     

    create table tmp as 
    select positiondate, public.ST_GeographyFromText('POINT('||cast(longitude as varchar(15))||' '||cast(latitude as varchar(15))||')' USING PARAMETERS ignore_errors='y') as geogfield
    from public.positions;

     

     

    I am somewhat new to Vertica. If I need to do this through a text file load (I am going to try that now), is there a suggested way or best practice for automating that? These positions come from Oracle and arrive every ten minutes. I have been very happy using Kettle to load the data hourly thus far. 

     

    Thanks!

     

    -Jimmy

  • Hi Jimmy,

     

    You don't need to cast the long and lat column. Since the columns are already a numeric column type (int, numeric, etc.) you don't need to cast them. I recreated your workflow using a slightly modified version of your query. When I performed these queries I was able to load my test data:

    dbadmin=> \d foo_place
    List of Fields by Tables
    Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
    --------+-----------+---------------+----------------+------+---------+----------+-------------+-------------
    public | foo_place | posistiondate | timestamp | 8 | | f | f |
    public | foo_place | lat | numeric(37,15) | 16 | | f | f |
    public | foo_place | long | numeric(37,15) | 16 | | f | f |
    (3 rows)

    -- run the create table statement
    dbadmin=> create table tmp_2 as select posistiondate, public.ST_GeographyFromText('POINT('||long||' '||lat||')' USING PARAMETERS ignore_errors='y') as geogfield from foo_place;
    CREATE TABLE

    -- view the tmp_2 table
    dbadmin=> select st_astext(geogfield) from tmp_2;
    st_astext
    ---------------------
    POINT (55.98 45.32)
    POINT (54.12 78.65)
    POINT (33.23 67.45)
    (3 rows)

     

    Try running the following query on your data:

    CREATE TABLE tmp AS SELECT positiondate, public.ST_GeographyFromText('POINT('||longitude||' '||latitude||')' USING PARAMETERS ignore_errors='y') AS geogfield FROM public.positions;

    The only thing I changed in the query was the casting. 

     

    Let me know if that works.

     

     

    -Casey

     

  • Casey,

     

    That did it! Thank you so much for your help! As a side note, the insert statement below will work as follows for anyone else who might be running into this (I also reinstalled Vertica Place and I think I might not have installed it properly on all nodes...that probably contributed to the errors I was getting):

     

    insert into tmp2(positiondate, geogfield)
    SELECT positiondate, public.ST_GeographyFromText('POINT('||longitude||' '||latitude||')' USING PARAMETERS ignore_errors='y') AS geogfield
    FROM public.positions;

     

    Thanks again!

     

    -Jimmy

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file