Clear(colSecondCollection);ForAll(colFirstCollection.features,Collect(colSecondCollection,features.Value));Clear(colThirdCollection);ForAll(colSecondCollection,Collect(colThirdCollection,Value.geometry))
This will give you the main collection colThirdCollection which will contain all the coordinates
Now, if you only need the first 2 coordinates then use
First(colThirdCollection).coordinates.Value - First coordinates
Last(FirstN(colThirdCollection,2)).coordinates.Value - Second coordinates
And to fetch individual values for the coordinates
Example : coordinates: [
-121.7506667,
46.8501667,
1.436
]
First(First(colThirdCollection).coordinates.Value).Value = -121.7506667
Last(FirstN(First(colThirdCollection).coordinates.Value,2)).Value = 46.8501667
Last(FirstN(First(colThirdCollection).coordinates.Value,3)).Value = 1.436
Hope this helps
Hi @RandyHayes and @RezaDorrani ,
Thank you both for the thoughtful replies. This was the easiest for me to implement:
ForAll(
Ungroup(colEarthquakeResults.features, "features"),
Collect(colEarthquakeLatLongs,
{long: First(geometry.coordinates.Value).Value, lat: Last(FirstN(geometry.coordinates.Value,2)).Value}
)
)It results in:
@RandyHayes, you were right! It is not easy to implement an incremental ID column in this case! Here's what I tried so far:
ClearCollect(colEarthquakeLatLongs,
AddColumns(
ForAll(
Ungroup(colEarthquakeResults.features, "features"),
{long:First(geometry.coordinates.Value).Value, lat:Last(FirstN(geometry.coordinates.Value,2)).Value}
),
"id",
If(
1 = 1,
CountRows(colEarthquakeLatLongs) + 1
)
)
);ClearCollect(colEarthquakeLatLongs,
ForAll(
Ungroup(colEarthquakeResults.features, "features"),
{id: Text(CountRows(colEarthquakeLatLongs)) + 1, long: First(geometry.coordinates.Value).Value, lat:Last(FirstN(geometry.coordinates.Value,2)).Value}
)
)ForAll(
Ungroup(colEarthquakeResults.features, "features"),
Collect(colEarthquakeLatLongs,
{id: Value(CountRows(Ungroup(colEarthquakeResults.features, "features"))) + 1, long: First(geometry.coordinates.Value).Value, lat: Last(FirstN(geometry.coordinates.Value,2)).Value}
)
)ClearCollect(colEarthquakeLatLongs,
AddColumns(
ForAll(
Ungroup(colEarthquakeResults.features, "features"),
{long:First(geometry.coordinates.Value).Value, lat:Last(FirstN(geometry.coordinates.Value,2)).Value}
),
"id",
If(
!IsEmpty(First(First(First(colEarthquakeResults.features).features.geometry).geometry.coordinates)),
Text(Value(varCounter) + 1)
)
)
)
I bet @mr-dang knows how to do this! 🙂
Hi @RandyHayes ,
There is no way of knowing exactly how many results will be returned.
- In this blog post, I found a fun way to create a large Numbers Table.
- Without a way to programatically create a Value Table based on CountRows or the like, we'll always run into this issue. Bummer!
Ended up going with this MONSTER!:
Set(varNumTable, [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]);
Clear(colNumbersTable);
Clear(colRecordToCount);
ClearCollect(colNumTemp, varNumTable);
ForAll(colNumTemp,
ForAll(colNumTemp,
ForAll(colNumTemp,
Collect(colRecordToCount,
{
record: 1
}
);
Collect(colNumbersTable,
{
number: CountRows(colRecordToCount)
}
)
)
)
);
ClearCollect(
colEarthquakeResults,
earthquakeAPI.GETearthquakes(
{
format: "geojson",
starttime: Today() - 1,
endtime: Today(),
latitude: Location.Latitude,
longitude: Location.Longitude,
maxradiuskm: 1000,
orderby: "magnitude-asc"
}
)
);
ClearCollect(colEarthquakeLatLongs,
ForAll(colNumbersTable,
If(number <= CountRows(Ungroup(colEarthquakeResults.features, "features")),
{
id: number,
long: First(
Last(
FirstN(
Ungroup(colEarthquakeResults.features, "features"),
number
)
).geometry.coordinates
).Value,
lat: Last(
FirstN(
Last(
FirstN(
Ungroup(colEarthquakeResults.features, "features"),
number
)
).geometry.coordinates,2
)
).Value
}
)
)
)...all to get an ID column in this Collection!:
@RandyHayesand @RezaDorrani , thanks for the Sunday help! Have a great week!
Thanks for the help here @RezaDorrani @RandyHayes.
One thing I'll add. When I want to understand the structure a little bit more and figure out when I need to use First() Last() etc., I use a JSON viewer:
This one is Code Beautify which is one of the prettier viewers. The collapsing helps to understand things.
@RandyHayes, hep meh! I'm missing the pattern here... Looking to do a similar thing, but this time I have a different schema. Instead of earthquakes, I want to take the results of a Fuzzy Search and plot pins on a map.
Heres the schema:
{
"summary": {
"query": "pub",
"queryType": "NON_NEAR",
"queryTime": 39,
"numResults": 10,
"offset": 0,
"totalResults": 30,
"fuzzyLevel": 1,
"geoBias": {
"lat": 47.56328695,
"lon": -122.37870965
}
},
"results": [
{
"type": "POI",
"id": "US/POI/p0/8718662",
"score": 2.575,
"dist": 128.26041102796466,
"info": "search:ta:840539001007346-US",
"poi": {
"name": "West Seattle Brewing Company",
"phone": "+(1)-(206)-7086627",
"categorySet": [
{
"id": 9376007
}
],
"categories": [
"café/pub",
"microbrewery/beer garden"
],
"classifications": [
{
"code": "CAFE_PUB",
"names": [
{
"nameLocale": "en-US",
"name": "microbrewery/beer garden"
},
{
"nameLocale": "en-US",
"name": "café/pub"
}
]
}
]
},
"address": {
"streetNumber": "4415",
"streetName": "Fauntleroy Way SW",
"municipalitySubdivision": "Seattle, Genesee, Fairmount Park",
"municipality": "Seattle",
"countrySecondarySubdivision": "King",
"countryTertiarySubdivision": "Seattle",
"countrySubdivision": "WA",
"postalCode": "98126",
"extendedPostalCode": "981262631",
"countryCode": "US",
"country": "United States",
"countryCodeISO3": "USA",
"freeformAddress": "4415 Fauntleroy Way SW, Seattle, WA 98126",
"localName": "Seattle",
"countrySubdivisionName": "Washington"
},
"position": {
"lat": 47.56428,
"lon": -122.37784
},
"viewport": {
"topLeftPoint": {
"lat": 47.56518,
"lon": -122.37917
},
"btmRightPoint": {
"lat": 47.56338,
"lon": -122.37651
}
},
"entryPoints": [
{
"type": "main",
"position": {
"lat": 47.56412,
"lon": -122.37767
}
}
]
}
]
}
Problem: I can only get the first set of Lat / Longs from the Fuzzy Search results
Tried so far:
/*====================================================================
Create a numbers table for ForAll looping purposes
====================================================================*/
Set(varNumTable, [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]);
Clear(colNumbersTable);
Clear(colRecordToCount);
ClearCollect(colNumTemp, varNumTable);
ForAll(colNumTemp,
ForAll(colNumTemp,
ForAll(colNumTemp,
Collect(colRecordToCount,
{
record: 1
}
);
Collect(colNumbersTable,
{
number: CountRows(colRecordToCount)
}
)
)
)
);
/*====================================================================
Collect all nearby "pubs" within a 2mile (~3200 meter) radius
====================================================================*/
ClearCollect(
colNearbySpots,
azureMapTimezone.GETSearchFuzzy(1.0,1,3,
{
query: "pub",
lon: Location.Longitude,
lat: Location.Latitude,
radius: 3200
}
)
);
/*====================================================================
Using the numbers table, create a Collection of all nearby spot Lat/Longs returned from
the previous API call.
Thank god for the PowerApps forums!: https://powerusers.microsoft.com/t5/General-Discussion/Complex-Collection-of-a-Collection/m-p/267273#M78095
====================================================================*/
ClearCollect(colNearbySpotLatLongs,
ForAll(colNumbersTable,
If(number <= CountRows(Ungroup(colNearbySpots.results, "results")),
{
id:number,
long: First(First(colNearbySpots.results).results.position).position.lon,
lat: First(First(colNearbySpots.results).results.position).position.lat
}
)
)
);
/*====================================================================
Using the table of nearby spot Lat/Longs and the numbers table, call the Azure Maps API
to get a map image of these spots with pins, at each zoom level!
====================================================================*/
Clear(colNearbySpotMaps);
ForAll(
Filter(colNumbersTable, number >= 10 && number <= 14),
AddColumns(
Collect(colNearbySpotMaps,
azureMapTimezone.GetMapImageWithPins(
1.0,
600,
600,
{
zoom: number,
center: Concatenate(Text(Location.Longitude), ",", Text(Location.Latitude)),
pins: Concatenate("default||", Concat(colNearbySpotLatLongs, lat & " " & lat, "|")),
path: Concatenate("ra3200", "||", Text(Location.Longitude), " ", Text(Location.Latitude))
}
)
) ,
"id",
number
)
);
ClearCollect(colNearbySpotMapsWithIDs,
ForAll(colNumbersTable,
If(number <= CountRows(colNearbySpots),
{
id:number,
mapImage: colNearbySpotMaps
}
)
)
)
I also tried First(Ungroup(colNearbySpots.results, "results").position).position.lon (/lat).
I end up with a lat/long list that only shows the first Result copied a bunch:
...and colNearbySpotMapsWithID is nested instead of a flat table:
I know this is a long post 🙂 . Hopefully the patterns that emerge for unfolding JSON will help others too.
Thank you!
Helpful resources
Welcome to the User Group Public Preview
Check out new user group experience and if you are a leader please create your group
