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 <[email protected]>
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"
> }
> }
> ]
> }
>
>
geo.json
Description: application/json
geo_features.json
Description: application/json
