Weather demo issue: Join never finishes

Follow

timbenz

• January 13, 2022 21:57

Trying to mimic the "Is Snow Coming" demo, but having an issue. Have successfuly downloaded the data, and set up the station list table, but the join of the two tables never finishes. I have tried to see if it's the size of the weather records table (600m), and limited it to just 2-3 years of data, but the join still never finishes. Any idea why this might be?

0

• 

  Kyle Pennell

  • January 14, 2022 20:19

  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
• 

  Kyle Pennell

  • January 14, 2022 17:35

  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
• 

  timbenz

  • January 14, 2022 18:55

  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
• 

  Kyle Pennell

  • January 14, 2022 19:11

  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
• 

  timbenz

  • January 14, 2022 20:05

  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
• 

  Candido Dessanti

  • January 17, 2022 14:14

  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.