Immerse pointmap could not show taxi data (Shanghai, China)


#1

I try MapD with the docker (mapd-docker-gpu 3.6.1) version, and interested in the taxi trip data of NewYork.

The NewYork trip data work no problem on Immerse. When I use Shanghai taxi data, there are problem occured.

In the mapd_server.INFO:

I0515 08:20:44.203508 2949 MapDHandler.cpp:1875] render_vega :A18cwiTLPhfxV8KL59Cgb6NVM4A3Qs2K:widget_id:3:compression_level:3:vega_json:{“width”:640,“height”:431,“data”:[{“name”:“pointmap”,“sql”:“SELECT conv_4326_900913_x(lon) as x, conv_4326_900913_y(lat) as y, taxi_sh_3.rowid FROM taxi_sh_3 WHERE ((lon >= -112.500000000001 AND lon <= 112.50000000000102) AND (lat >= -40.44079785631133 AND lat <= 72.50415165350412)) LIMIT 2000000”}],“scales”:[{“name”:“x”,“type”:“linear”,“domain”:[-12523442.71252511,12523442.712525114],“range”:“width”},{“name”:“y”,“type”:“linear”,“domain”:[-4930206.108707672,11937305.794780156],“range”:“height”},{“name”:“pointmap_fillColor”,“type”:“linear”,“domain”:[0,0.125,0.25,0.375,0.5,0.625,0.75,0.875,1],“range”:[“rgba(17,95,154,0.475)”,“rgba(25,132,197,0.5471153846153846)”,“rgba(34,167,240,0.6192307692307691)”,“rgba(72,181,196,0.6913461538461538)”,“rgba(118,198,143,0.7634615384615384)”,“rgba(166,215,91,0.835576923076923)”,“rgba(201,229,47,0.85)”,“rgba(208,238,17,0.85)”,“rgba(208,244,0,0.85)”],“accumulator”:“density”,“minDensityCnt”:"-2ndStdDev",“maxDensityCnt”:“2ndStdDev”,“clamp”:true}],“marks”:[{“type”:“points”,“from”:{“data”:“pointmap”},“properties”:{“x”:{“scale”:“x”,“field”:“x”},“y”:{“scale”:“y”,“field”:“y”},“fillColor”:{“scale”:“pointmap_fillColor”,“value”:0},“size”:1}}]}:nonce:9
I0515 08:20:44.203814 73 QueryRenderManager.cpp:578] Active render session [userId: A18cwiTLPhfxV8KL59Cgb6NVM4A3Qs2K, widgetId: 3]
I0515 08:20:44.203902 73 QueryRenderManager.cpp:738] QueryRenderManager - purging 3 idle connections.
I0515 08:20:44.211714 73 Calcite.cpp:277] User mapd catalog mapd sql ‘SELECT conv_4326_900913_x(lon) as x, conv_4326_900913_y(lat) as y, taxi_sh_3.rowid FROM taxi_sh_3 WHERE ((lon >= -112.500000000001 AND lon <= 112.50000000000102) AND (lat >= -40.44079785631133 AND lat <= 72.50415165350412)) LIMIT 2000000’
E0515 08:20:44.228842 2949 MapDHandler.cpp:1882] Exception: Exception occurred: org.apache.calcite.runtime.CalciteContextException: From line 1, column 92 to line 1, column 98: Column ‘rowid’ not found in table ‘taxi_sh_3’

From this log, Immerse try to limit the spatial range with lon(-112.5,112.5) and lat (-40.44,72.5), Shanghai is not in this range, so the query result return none.

How can I change the spatial limitation or disable the limitation?


#2

Hi @wangliangxu,

I think you might have better luck starting with a fresh dashboard. Have you tried building a new pointmap with your data in Immerse? Here I uploaded some taxi data from New Zealand and I’m building a pointmap with longitude and latitude from my data file:

Let me know if this works out for you or if you’re still having trouble.


#3

Thanks @b23kelly.
I have tried the NewYork taxi trip data, which is ok in Immerse.


The shanghai taxi data does not work in the same method:


#4

Oh no, I’m sorry. Could you possibly send me a sample of the data you’re working with? I’d like to see if I can replicate your issue.


#5

Thanks.

This is head output of the data:
id,warning,empty,light,bridge,rectime,gpstime,lon,lat,speed,direction,satellite
18834,0,0,0,0,0,2015-04-01 14:25:27,2015-04-01 14:25:21,121.441895,31.206928,0.0,228.0,7
27320,0,1,3,0,0,2015-04-01 19:38:18,2015-04-01 19:38:12,121.562235,31.249510,0.0,249.0,10
16926,0,1,5,0,0,2015-04-01 02:55:08,2015-04-01 02:55:02,121.666902,31.282538,28.4,15.0,11
26337,0,0,0,0,0,2015-04-01 23:23:38,2015-04-01 23:23:32,121.424717,31.235583,0.0,135.0,10
23807,0,1,5,0,0,2015-04-01 04:49:49,2015-04-01 04:49:43,121.478988,31.341545,0.0,294.0,6
23390,0,1,1,0,0,2015-04-01 01:12:08,2015-04-01 01:12:00,121.388335,31.263890,42.2,1.0,3
10827,0,1,1,0,0,2015-04-01 17:37:56,2015-04-01 17:33:07,121.333718,31.173365,0.0,307.0,9
20043,0,0,0,0,0,2015-04-01 02:28:59,2015-04-01 02:28:53,121.357767,31.385620,0.0,171.0,8
00049,0,1,1,0,0,2015-04-01 00:24:06,2015-04-01 00:24:01,121.303755,31.247965,0.0,10.0,8

and the SQL DDL I used is:

drop table if exists taxi_shanghai;
create table taxi_shanghai (
id integer,
warning smallint,
empty smallint,
light smallint,
bridge smallint,
braking smallint,
rectime timestamp encoding fixed(32),
gpstime timestamp encoding fixed(32),
lon real,
lat real,
speed real,
direction real,
satellite smallint
) with (fragment_size = 2000000);


#6

I tried to import your data sample, and it looks like a column header name might have been errantly dropped - there is one less column header than there are columns of data, and the lon field is being assigned to the timestamp column before it (screen shot)39%20AM


#7

Sorry, the head output lost the braking field (in the first line), you could use this without the header:

18834,0,0,0,0,0,2015-04-01 14:25:27,2015-04-01 14:25:21,121.441895,31.206928,0.0,228.0,7
27320,0,1,3,0,0,2015-04-01 19:38:18,2015-04-01 19:38:12,121.562235,31.249510,0.0,249.0,10
16926,0,1,5,0,0,2015-04-01 02:55:08,2015-04-01 02:55:02,121.666902,31.282538,28.4,15.0,11
26337,0,0,0,0,0,2015-04-01 23:23:38,2015-04-01 23:23:32,121.424717,31.235583,0.0,135.0,10
23807,0,1,5,0,0,2015-04-01 04:49:49,2015-04-01 04:49:43,121.478988,31.341545,0.0,294.0,6
23390,0,1,1,0,0,2015-04-01 01:12:08,2015-04-01 01:12:00,121.388335,31.263890,42.2,1.0,3
10827,0,1,1,0,0,2015-04-01 17:37:56,2015-04-01 17:33:07,121.333718,31.173365,0.0,307.0,9
20043,0,0,0,0,0,2015-04-01 02:28:59,2015-04-01 02:28:53,121.357767,31.385620,0.0,171.0,8
00049,0,1,1,0,0,2015-04-01 00:24:06,2015-04-01 00:24:01,121.303755,31.247965,0.0,10.0,8

In my table, the lon, lat is correct:


#8

I was able to create this pointmap in a new Immerse dashboard using the data you sent:

Your data seems okay. Can you describe the method you’re using to get that error in more detail?


#9

Thanks @b23kelly
With your comfirmation, I recheck my steps, find out that in the pointmap, if I add dimension field first, it is no problem of display the map of Shanghai taxi.