Issue with Retrieving Lat/Long from Map

["Dela Cruz, Vergel" <Ve...@ap.jll.com>]

Hi,

I have a json file with a map of lat/long values like below.

"coordinates":[[103.61408,1.25228],[103.62203,1.29562]..."

I would like to have two columns for this field. One for Latitude and other for Longitude.
Is there a way to split this column ? I have used the flatten function to get all the coordinate values.

Below is the drill query I used.

SELECT tbl.features[0].properties.`timestamp` as DateTime,
tbl.features[0].properties.taxi_count as TaxiCount,
FLATTEN(tbl.features[0].geometry.coordinates) as Location FROM `apvergeldelacruz`.`default`.`./Processed/Taxi_Avail_2017_03_13_13_54_09_201703130055` tbl

Thanks,
Vergel




This email is for the use of the intended recipient(s) only. If you have received this email in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not keep, use, disclose, copy or distribute this email without the author's prior permission. We have taken precautions to minimize the risk of transmitting software viruses, but we advise you to carry out your own virus checks on any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses. The information contained in this communication may be confidential and may be subject to the attorney-client privilege. If you are the intended recipient and you do not wish to receive similar electronic messages from us in the future then please respond to the sender to this effect.

Re: Issue with Retrieving Lat/Long from Map

[Jinfeng Ni <jn...@apache.org>]

The flatten given you access of the list of locations. You can use the
following to put it into two columns:

SELECT DateTime, TaxCount, Location[0] as Latittude, Locations[1] as Longtitude
FROM (
SELECT tbl.features[0].properties.`timestamp` as DateTime,

tbl.features[0].properties.taxi_count as TaxiCount,

FLATTEN(tbl.features[0].geometry.coordinates) as Location FROM
`apvergeldelacruz`.`default`.`./Processed/Taxi_Avail_2017_03_13_13_54_09_201703130055`
tbl);


Here is what I got for your sample data:

select locations[0] as latitude, locations[1] as longtitude from
(select flatten(tbl.coordinates) locations from dfs.`/tmp/1.json`
tbl);
+------------+-------------+
|  latitude  | longtitude  |
+------------+-------------+
| 103.61408  | 1.25228     |
| 103.62203  | 1.29562     |
+------------+-------------+


On Fri, Mar 17, 2017 at 7:49 AM, Ted Dunning <te...@gmail.com> wrote:
> What result did you get?
>
>
>
> On Fri, Mar 17, 2017 at 12:02 AM, Dela Cruz, Vergel <
> Vergel.Delacruz@ap.jll.com> wrote:
>
>> Hi,
>>
>>
>>
>> I have a json file with a map of lat/long values like below.
>>
>>
>>
>> "coordinates":[[103.61408,1.25228],[103.62203,1.29562]…”
>>
>>
>>
>> I would like to have two columns for this field. One for Latitude and
>> other for Longitude.
>>
>> Is there a way to split this column ? I have used the flatten function to
>> get all the coordinate values.
>>
>>
>>
>> Below is the drill query I used.
>>
>>
>>
>> SELECT tbl.features[0].properties.`timestamp` as DateTime,
>>
>> tbl.features[0].properties.taxi_count as TaxiCount,
>>
>> FLATTEN(tbl.features[0].geometry.coordinates) as Location FROM
>> `apvergeldelacruz`.`default`.`./Processed/Taxi_Avail_2017_03_13_13_54_09_201703130055`
>> tbl
>>
>>
>>
>> Thanks,
>>
>> Vergel
>>
>>
>>
>>
>>
>> This email is for the use of the intended recipient(s) only. If you have
>> received this email in error, please notify the sender immediately and then
>> delete it. If you are not the intended recipient, you must not keep, use,
>> disclose, copy or distribute this email without the author's prior
>> permission. We have taken precautions to minimize the risk of transmitting
>> software viruses, but we advise you to carry out your own virus checks on
>> any attachment to this message. We cannot accept liability for any loss or
>> damage caused by software viruses. The information contained in this
>> communication may be confidential and may be subject to the attorney-client
>> privilege. If you are the intended recipient and you do not wish to receive
>> similar electronic messages from us in the future then please respond to
>> the sender to this effect.
>>

Re: Issue with Retrieving Lat/Long from Map

[Ted Dunning <te...@gmail.com>]

What result did you get?



On Fri, Mar 17, 2017 at 12:02 AM, Dela Cruz, Vergel <
Vergel.Delacruz@ap.jll.com> wrote:

> Hi,
>
>
>
> I have a json file with a map of lat/long values like below.
>
>
>
> "coordinates":[[103.61408,1.25228],[103.62203,1.29562]…”
>
>
>
> I would like to have two columns for this field. One for Latitude and
> other for Longitude.
>
> Is there a way to split this column ? I have used the flatten function to
> get all the coordinate values.
>
>
>
> Below is the drill query I used.
>
>
>
> SELECT tbl.features[0].properties.`timestamp` as DateTime,
>
> tbl.features[0].properties.taxi_count as TaxiCount,
>
> FLATTEN(tbl.features[0].geometry.coordinates) as Location FROM
> `apvergeldelacruz`.`default`.`./Processed/Taxi_Avail_2017_03_13_13_54_09_201703130055`
> tbl
>
>
>
> Thanks,
>
> Vergel
>
>
>
>
>
> This email is for the use of the intended recipient(s) only. If you have
> received this email in error, please notify the sender immediately and then
> delete it. If you are not the intended recipient, you must not keep, use,
> disclose, copy or distribute this email without the author's prior
> permission. We have taken precautions to minimize the risk of transmitting
> software viruses, but we advise you to carry out your own virus checks on
> any attachment to this message. We cannot accept liability for any loss or
> damage caused by software viruses. The information contained in this
> communication may be confidential and may be subject to the attorney-client
> privilege. If you are the intended recipient and you do not wish to receive
> similar electronic messages from us in the future then please respond to
> the sender to this effect.
>