Weather demo issue: Join never finishes

Comments

6 comments

  • Avatar
    Kyle Pennell

    Hi Tim - Do you have the IDs on both encoded as a string encoded dict? Could you try limiting it to just 10000 rows and see if it runs then? What version of OmniSci are you on?

    0
    Comment actions Permalink
  • Avatar
    timbenz

    Thanks. On checking, only the df_snow (weather data) table has ID as a string encoded dict, not the weather station table. The latter was created by importing a CSV, so I'm not sure how to change the data type in Omnsci. Am using version 5.5.1 on a Mac.

    0
    Comment actions Permalink
  • Avatar
    Kyle Pennell

    Hey Tim - You'd have to reimport that and make sure the join keys are the same data type on both. That's the only way they'll be able to be joined.

    0
    Comment actions Permalink
  • Avatar
    timbenz

    Ah, thanks for that. Missed that the two ID fields were different str types. Fixed and join works now.

    One more issue. The imported CSV has the location lat/long as type float, so when it joins to df_snow it leaves them that way, rather than creating location/point fields. Is that as intended? It's not clear to me why it's not seeing location data as location, or how to fix that in CSV import.

    0
    Comment actions Permalink
  • Avatar
    Kyle Pennell

    Hey Tim - You could have the lat lng held separately as floats and renders the points that way. Or could you combine them into something like a WKT geometry. Either one will work for an OmniSci map.

    1
    Comment actions Permalink
  • Avatar
    Candido Dessanti

    Hi @timbenz ,

    Let's say you loaded a table with lon/lat, and you want a point datatype; you can re-create the table using a point datatype instead lon/lat fields and then re-reload the table with the files you previously used.

    let's say we have a table like that REATE TABLE yellow_tripdata ( vendorid TEXT ENCODING DICT(8), tpep_pickup_datetime TIMESTAMP(0) ENCODING FIXED(32), tpep_dropoff_datetime TIMESTAMP(0) ENCODING FIXED(32), passenger_count SMALLINT, trip_distance FLOAT, pickup_longitude DOUBLE, pickup_latitude DOUBLE, ratecodeid TEXT ENCODING DICT(8), store_and_fwd_flag TEXT ENCODING DICT(8), dropoff_longitude DOUBLE, dropoff_latitude DOUBLE, payment_type TEXT ENCODING DICT(8), fare_amount FLOAT, extra DECIMAL(14,2), mta_tax DECIMAL(14,2), tip_amount DECIMAL(14,2), tolls_amount DECIMAL(14,2), improvement_surcharge DECIMAL(14,2), total_amount DECIMAL(14,2));

    just create another table yellow_tripdata_p

    CREATE TABLE yellow_tripdata_p ( vendorid TEXT ENCODING DICT(8), tpep_pickup_datetime TIMESTAMP(0) ENCODING FIXED(32), tpep_dropoff_datetime TIMESTAMP(0) ENCODING FIXED(32), passenger_count SMALLINT, trip_distance FLOAT, pickup_point GEOMETRY(POINT, 4326) NOT NULL ENCODING COMPRESSED(32), ratecodeid TEXT ENCODING DICT(8), store_and_fwd_flag TEXT ENCODING DICT(8), dropoff_point GEOMETRY(POINT, 4326) NOT NULL ENCODING COMPRESSED(32), payment_type TEXT ENCODING DICT(8), fare_amount FLOAT, extra DECIMAL(14,2), mta_tax DECIMAL(14,2), tip_amount DECIMAL(14,2), tolls_amount DECIMAL(14,2), improvement_surcharge DECIMAL(14,2), total_amount DECIMAL(14,2))

    and load your data with copy command from SQL editor (I am assuming you have source files on your desktop).

    copy yellow_pripdata_p from '/your_path/your_csv_file.csv'.

    Unluckly you have to run the command from SQL Editor, not from the import data interface

    0
    Comment actions Permalink

Please sign in to leave a comment.