I am trying to use choropleth map to represent the SLA status of a few countries (in Europe). I need to represent the country color based on whether that country's SLA percentage.
I am using rangemap to distinguish them.
... | stats count(MET) as Total sum(MET) as Met by Country | eval SLA_perc=Met*100/Total | rangemap field=SLA_perc "SLA Met <85%"=0-85, "SLA Met 85-90%"=85-90,"SLA Met >90%"=90-100 | table Country, range | geom geo_countries featureIdField="Country"
But I can't seem to achieve 2 things that are part of the requirements
1. I can't change the color bins to use red, amber and green to represent the 3 ranges. Tried using divergent and sequential but none of them gives you much flexibility to decide on the range values (SLA values tend to skew towards 80-100% range for all records) and assign colors accordingly. Can't edit the color values of 'Categorical' as well which would have been simpler.
2. I can't find a way to only retain the country label that's required and remove the rest of the labels. I played around with the geo_countries.kml and and removed all other country details except those I want and created a new geo-spatial lookup. But that didn't help. I don't know what values to edit in the KML file to achieve this.
Anyone been through this kind of issues with the maps?
Let me start with how to achieve point 2). This answer is long but hopefully is easy to grasp so buckle up. Please follow as below:
<Placemark>
xml element tag so remove all the Placemark tags for countries which you do not need.If I want to remove "Aruba" as a country then I should be removing all the following lines from my countries.kml file. (Basically everything in between <Placemark></Placemark>
tags. Use an xml editor which will be easy 🙂
<Placemark>
<name>Aruba</name>
<Style><LineStyle><color>ff0000ff</color></LineStyle><PolyStyle><fill>0</fill></PolyStyle></Style>
<ExtendedData><SchemaData schemaUrl="#countries">
<SimpleData name="ISO2">AW</SimpleData>
<SimpleData name="ISO3">ABW</SimpleData>
<SimpleData name="REGION_WB">Latin America & Caribbean</SimpleData>
<SimpleData name="REGION_UN">Americas</SimpleData>
<SimpleData name="SUBREGION">Caribbean</SimpleData>
<SimpleData name="CONTINENT">North America</SimpleData>
</SchemaData></ExtendedData>
<Polygon><outerBoundaryIs><LinearRing><coordinates>-69.996937628999916,12.577582098000036 -69.924672003999945,12.519232489000046 -69.880197719999842,12.453558661000045 -69.888091600999928,12.417669989000046 -69.930531378999888,12.425970770000035 -69.945139126999919,12.44037506700009 -69.924672003999945,12.447211005000014 -70.058094855999883,12.537176825000088 -70.048736131999931,12.583726304000024 -70.061105923999975,12.625392971000068 -70.048736131999931,12.632147528000104 -69.996937628999916,12.577582098000036</coordinates></LinearRing></outerBoundaryIs></Polygon>
</Placemark>
Once all the required countries are deleted save your "countries.kml" file.
Come outside to the parent directory and again zip back the "geo_countries_new" folder as now this folder has the file countries.kml you had been editing and has the countries you require.
Once zipped "geo_countries_new" folder as "geo_countries_new.zip", rename the "geo_countries_new.zip" file to "geo_countries_new.kmz"
Now go to Settings> Lookups » Lookup table files » Add new
Upload this new "geo_countries_new.kmz" and choose "geo_countries_new.kmz" as "Destination filename". Ensure the name is same to avoid confusions and avoid improper results.
Now go to Settings > Lookups » Lookup definitions » Add new
Create a lookup definition called "geo_countries_new" referring your "geo_countries_new.kmz" file. Ensure proper permissions.
Once done, you can now use this "geo_countries_new" in your command as below:
yourBaseQuery
| geom geo_countries_new featureIdField="Country"
For point 1) the best I think we can do is (unless someone else posts something better) you can choose Color Mode as Divergent and bins as 3. Choose the colors to Red and Green accordingly (to your max and Min) wherever you want the colors to reflect. Since the neutral is always white so u might not be able to choose amber and best I think we can do is Red, White and Green. If you choose your neutral value to be a value in between "85-90" then your graphs will now show up in Red and Green accordingly but all expected ambers will be white.
Hope this fulfills your requirement 1) and 2) and if you are still reading this solution then let me know if it worked and if its worth an upvote. 🙂
I just followed the steps you have given but same results. It is still showing all the country labels. Maybe its referencing the default geo_countries.kmz lookup somewhere. I might give a try removing this default lookup so that it only has my new lookup to refer. I will let you know if that made a difference.
geom geo_countries_new featureIdField="Country"
My new lookup is geo_countries_new so use that in your geom command like I had.
Yes I used a different name for the kmz file with the countries.kml (did not rename this one) having only the country details required.
| geom <inserted the custom kmz file lookup definition name> featureIdField="Country"
Do you recommend a different name for countries.kml?
No, I didn't use different name for kml file. The new names were used only in .kmz file, lookup table name and lookup definition name. Basically I replicated everything the way default kmz, table and lookup was setup but with _new everywhere. So this is what I did:
geo_countries.kmz -----> geo_countries_new.kmz (local file name)
geo_countries.kmz -----> geo_countries_new.kmz (file name in lookup table upload)
geo_countries -----> geo_countries_new (in the lookup table definition)
use of geo_countries_new in my geom command.
makes complete sense to me too. Don't know why it didn't work for me. If I open the kmz file in Google Earth, it clearly gives me only the included countries marked.
But anyway, its comforting to know that it works for someone. I will tweak things around till it works.
just curious, when u created the lookup definition did u ensure to keep its type as external_type=geo
Thought of mentioning so.
I apologize if the solution is not working as that's the only way I know to get the countries data manipulated. Let's see if someone comes with a better solution.
Thanks for detailed answer.
For point one, I got the right solution from this answer. Yeah, it was much simpler than I thought it would be.
For the second point, this is what I tried (mentioned in my question) but I will try following your step by step instruction and see if I get a different result.