Weather demo issue: Join never finishes

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?

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?

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.

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.

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.

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 Like

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