Aggregating points: JSON on SQL and loops on infowindows

Aggregating points: JSON on SQL and loops on infowindows

Fantastic bit of code and tutorial about aggregating point data for JSON objects using SQL.

geomaticblog

NOTE: I’ll use CARTO but you can apply all this to any webmapping technology backed by a modern database.

Get all the data

So we start with the typical use case where we have a one to many relationship like this:

Easy peasy, we have a map with many stacked points. From here you can jump to this excellent post by James Milner about dense point maps. My example is not about having thousands of scattered points that at certain zoom levels overlap. Mine is a small set of locations but many points “stacking” on them. In this case you can do two things: aggregate or not. When you aggregate you pay a prize for readability: reducing all your data to those locations and maybe using visual variables to show counts or averages or any other aggregated value and finally try to use the interactivity of your map to…

View original post 522 more words

Advertisements

Fuzzy Lookup: My Plugin Baby

Fuzzy Lookup: My Plugin Baby

So this isn’t strictly a GIS or mapping tool or tip that I thought I’d share, but considering the amount of time and importance of joins, spreadsheets, datasets and databases in the profession, or even outside geospatial work, this little tool called ‘Fuzzy Lookup’ may come in handy.

Often when trying to query, search or join up one table to another, or for whatever purpose, VLOOKUP can do just the job, ‘vertically looking up’ one value, in order to display another. If you are new or unfamiliar to VLOOKUP, Debra Dalgleish has an awesome blog, Contextures to help with this and much more even for experienced users. There is the ‘range_lookup’ operator, FALSE, which will look for a identical match, or TRUE, an approximate match; the latter of these is useful for numbers, less so for text.

fuzzy4

Continue reading “Fuzzy Lookup: My Plugin Baby”

5. Map of the Day: Knock on Wood

5. Map of the Day: Knock on Wood

How would you define a map? A type in a popular search engine beginning with ‘G’, has a familiar definition, “a diagrammatic representation of an area of land or sea showing physical features, cities, roads, etc.”, and indeed the word map derives from the word ‘mappa’ meaning sheet/napkin. However, ironically though ‘map’ isn’t restricted to this two dimensional version we can fold up; as we all know, we live a digital age where you navigate the world around you with your laptop or smartphone too. But what happens if you live somewhere with little to no mobile internet signal and sub-zero temperatures most of the year round. And what are those odd, suspiciously shaped lumps of wood in the picture above?

Those are maps. Greenlandic wood maps to be precise, and rather than a true to scale, visual representation of the area, like a contemporary map would attempt, this provides a tactile, physical representation of the coastline. As one can see from the useful diagram below, this isn’t a crude attempt at carving the island of Greenland/Kalaallit Nunaat, but traces the contours of the same part of the coast of Greenland, up and down the same piece of wood. This is a much more resourceful and useful map, once you understand, which is adapted to the climate of the nomadic, hunter-gatherer culture that once existed, in some places still does exist. So what’s the point in showing this?  Okay, it’s not quite as interactive and different to most things in map blogs, but it’s quite interesting – it shows that long human desire and need in any culture or time to explore, map (used as a verb now), and have geographic knowledge of the world around us, something which didn’t just start with a Belgian mathematician called Gerardus Mercator.

Continue reading “5. Map of the Day: Knock on Wood”

4. Map of the Day: Selden Map of China

4. Map of the Day: Selden Map of China

In a world where digital maps are available within a few clicks and can rely pre-loaded map projection systems to ensure we are mapping in the right location, for me it is always humbling and breathtaking the painstaking efforts and relative accuracy of older maps. One particular example is the Selden map of China (acquired from estate of London lawyer John Selden in 1659), one of the oldest surviving merchant trade maps, and one of the first Chinese maps to reach Europe, roughly dated 1607-1617. In many ways the beauty and craft of this map speaks for itself (see here), but there is more to this map than first thought.

calicut-selden-2-copy

Continue reading “4. Map of the Day: Selden Map of China”

Some great old maps from Canada

I recently came across a copy of an old school book, “Picture Map Geography of Canada and Alaska” by Vernon Quinn, that includes charming woodcut picture maps by Bruno da Osimo, a then noted Italian illustrator, for each of the Canadian provinces (other than Nunavut, which was then part of the Northwest Territories). Originally published in 1944 […]

via Vintage Picture Map Geography of Canada — O’ Canada

3. Map of the Day – Map powered cars (ZAP MAP)

3. Map of the Day – Map powered cars (ZAP MAP)

Whether you drive an electric car, a ‘normal’ car, or don’t drive at all, this web and mobile (Android and iOS) application from ZapMap is well worth taking a look at. Powered by Google Maps API, this mapping tool provides the locations of electric car chargers, providing pop up boxes with the latest status of these charging points, types and directions on how to get there. Although the shading of the vector tiles is a bit gaudy, with an ability to log in, map and comment on charging points, there is potential for up-to-date community empowered mapping to provide latest running statuses and charging locations.

zapmap070217

Continue reading “3. Map of the Day – Map powered cars (ZAP MAP)”

2. Map of the Day: How much does your vote count?

2. Map of the Day: How much does your vote count?

Today’s map of the day touches on the thorny issue of…politics, though I’m not going to get into a political discussion; if you want to, please got the usual channels, i.e. YouTube comments section. This map from the Economist Intelligent Unit’s (EIU) Democracy Index is a simple, clear, but informative map (click here for full interactive version here).

economistdemocracymap2016

Continue reading “2. Map of the Day: How much does your vote count?”