drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Yash Sharma <yash...@gmail.com>
Subject Re: Querying GeoJSON
Date Sun, 10 Aug 2014 05:45:22 GMT
Hi Luca,
the 'features' is a list inside a single json record so is referenced by
Index.

Having all the features as separate json records will let you query the
json for all locations.
Something like:

{ "type": "Feature",
>         "geometry": {"type": "Point", "coordinates": [-75.343, 39.984]},
>         "properties": {
>           "name": "Location A",
>           "category": "Store"
>         }
> }
> { "type": "Feature",
>         "geometry": {"type": "Point", "coordinates": [-75.833, 39.284]},
>         "properties": {
>           "name": "Location B",
>           "category": "House"
>         }
> }
> { "type": "Feature",
>         "geometry": {"type": "Point", "coordinates": [ -75.534, 39.123]},
>         "properties": {
>           "name": "Location C",
>           "category": "Office"
>         }
> }
>
> 0: jdbc:drill:zk=local> select gg.properties.name from
> dfs.`/home/yash/Drill/geo_features.json` gg;
> +------------+
> |   EXPR$0   |
> +------------+
> | Location A |
> | Location B |
> | Location C |
> +------------+



Else if you have all the 3 locations in the list you will have to query the
list against indexes to fetch all the data. Like:

0: jdbc:drill:zk=local> select gg.features[0].properties.name loc_a,
> gg.features[1].properties.name loc_b, gg.features[2].properties.name
> loc_c from dfs.`/home/yash/Drill/geo.json` gg;
> +------------+------------+------------+
> |   loc_a    |   loc_b    |   loc_c    |
> +------------+------------+------------+
> | Location A | Location B | Location C |
> | Location A | Location B | Location C |
> | Location A | Location B | Location C |
> +------------+------------+------------+



Attaching json files for your reference.
Yash






On Sun, Aug 10, 2014 at 10:21 AM, Luca Morandini <lmorandini@ieee.org>
wrote:

> I put Drill to the test on GeoJSON data (GeoJSON is, as the name implies,
> a way to serialize geo-spatial data in JSON, see an example at the end of
> this post), but found an issue.
>
> The trouble is that geometry features are held in a vector, which is where
> all the selection should happen, but how I have to figure out how to do it.
>
> I tried this statement:
> select t.features.properties.name from dfs.`/opt/drill/sample-data/geo2.json`
> t;
>
> but...
> Query failed: Failure while running fragment. org.apache.drill.exec.vector.complex.RepeatedMapVector
> cannot be cast to org.apache.drill.exec.vector.complex.MapVector
> [4098f044-ebba-4ca4-8bec-c8b2760dd2ec]
>
> Error: exception while executing query: Failure while trying to get next
> result batch. (state=,code=0)
>
> While, specifying the vector index works as expected:
> select t.features[0].properties.name from dfs.`/opt/drill/sample-data/geo2.json`
> t;
>
> Help appreciated,
>
> Luca Morandini
> Data Architect - AURIN project
> Melbourne eResearch Group
> Department of Computing and Information Systems
> University of Melbourne
> Tel. +61 03 903 58 380
> Skype: lmorandini
>
> {
>     "type": "FeatureCollection",
>     "features": [
>       { "type": "Feature",
>         "geometry": {"type": "Point", "coordinates": [-75.343, 39.984]},
>         "properties": {
>           "name": "Location A",
>           "category": "Store"
>         }
>       },
>       { "type": "Feature",
>         "geometry": {"type": "Point", "coordinates": [-75.833, 39.284]},
>         "properties": {
>           "name": "Location B",
>           "category": "House"
>         }
>       },
>       { "type": "Feature",
>         "geometry": {"type": "Point", "coordinates": [ -75.534, 39.123]},
>         "properties": {
>           "name": "Location C",
>           "category": "Office"
>         }
>       }
>     ]
>   }
>
>

Mime
View raw message