Recently, CartoDB had the the chance to put our new space to work by hosting MaptimeNYC in our New York office. This exciting and informative session was a great opportunity to bring together mappers from across the city in a community-run and critically engaged environment.
Every two weeks in New York (and in other cities across the nation and world) a group of twenty or so mapping aficionados meet to share ideas and build community around mapping, and generate learning experiences around how to create functional, well-designed maps. Maptime is a great place for newbies and experienced mappers alike to come together and build collaboratively. People from all professions and interests are encouraged to bring mapping problems and barriers that are holding them back, and through our growing and active mapping community, participants can break through some of the barriers in constructive ways while making friends. Whether you are new to mapping, getting started with CartoDB, or a long-time GIS professional, Maptime creates a safe space to experiment, make mistakes, and lean on experienced peers. Plus… pizza. (You can’t beat mapping and pizza!)
Each session of Maptime is designed to showcase a skill or technique presented by a fellow mapper, then transition into problem-solving working sessions that tackle everything from CartoDB’s built in APIs, to Javascript, and many other facets of developing and mapping technologies. Our community of mappers here in New York and across the world are creating a strong culture of data visualization that CartoDB and our family of web-based mappers are proud to be embedded in.
Early December’s session featured the second part of a series on SQL using PostGIS, which many have been aching to improve their skills given its robust capabilities in CartoDB.
So we offer you part 2 of our SQL demo:
Built and fine-tuned by the group of fifteen that gathered at CartoDB HQ in NYC last week these handy SQL queries range from common to exotic, and inform a wide range of experienced users. We hope you find them useful!
####Connecting a dots with lines: ST_Makeline
This is one for the n00bies. So you have a bunch of points, whether from a GPS or points you’ve created, and you want to connect them to make a path. Seems simple, but might not be as straightforward as you think.
Make sure you have:
SELECT ST_MakeLine (the_geom ORDER BY _order ASC)
AS the_geom, route
FROM maptimesql_points
GROUP BY route
From here you can create a new table when prompted on the CartoDB Interface.
Lines appear in new table:
Alternately, you can add in webmercator to create the line AND the cartodb_id to make it interactive. Try removing one or both of those to help you understand the function of them:
SELECT ST_MakeLine (the_geom_webmercator, cartodb_id ORDER BY _order ASC)
AS the_geom_webmercator, route
FROM maptimesql_points
GROUP BY route
####Points in a Geographic Area: ST_Contains
Let’s say you want to find all the points inside of a geographic area. The first thing you need is a table with polygons and a table with points. Here, we will use two CartoDB common data sets, Real Time Earthquakes (points) and US Counties (polygons), found at the top of your CartoDB dashboard.
In the end, this will take the counties and highlight the ones that have had earthquakes in the time-period defined by the earthquakes dataset.
Make sure you have:
Use the_geom_webmercator to get the polygons and points merged on one table without having to select ‘create new table’.
SELECT us_counties.the_geom_webmercator,us_counties.cartodb_id, count(quakes.the_geom)
AS total
FROM us_counties JOIN quakes
ON st_contains(us_counties.the_geom,quakes.the_geom)
GROUP BY us_counties.cartodb_id
####10 Closest Points to a Selected Latitude and Longitude: CDB_LatLng
One thing that SQL allows you to do is create compounded functions and compile multiple steps into a more simple statement. Here, we are using CDB_LatLng (latitude, Longitude)) to find the 10 closest points from a selected latlon. The original statement is found below and is unwieldy and difficult for new or intermediate users. CartoDB has created a few new statements to make life easier.
We will now find the ten earthquakes closest to Times Square.
Make sure you:
It should show you the ten closest earthquakes on that day to the point you noted. Use can use any latlon. Find out yours here.
SELECT * FROM quakes
ORDER BY the_geom <->
CDB_LatLng(42.5,-73) LIMIT 10
####Compare Rows of Data, Visualize Difference: lag( )
The lag() enables you to compare to rows of data and pull out the difference of those and display that difference visually. Here, we will use some self-created data to look at the time between stops on a New York tourist’s landmark tour.
What lag() does is to takes the data in a row and compare it to a determined previous row. lag (column, number of rows back). Useful in comparing data and extracting duration. Here’s a link to what’s happening in the background.
Make sure you:
You should see lines running between the landmarks that can now be changed using a chloropleth for lines to show the difference in times between the journey segments.
SELECT cartodb_id,the_geom, description, extract(epoch FROM (time_left - lag(time_left,1) OVER(ORDER BY time_left))) AS duration_in_seconds
FROM landmark_visits
ORDER BY time_left
That wraps it up! We hope you learned a lot. SQL and PostGIS can be powerful tools for visualizing and analyzing data. If you want to learn more, check out our SQL and Post GIS Academy course and come out to Maptime NYC. Come learn new things, help grow our mapping community! We hope you learned a lot and as always:
Happy Mapping
Application development is a key requirement for many organizations that need to provide custom experiences for their users. This can range from a simple interface providin...
NewsThis week, Mapbox announced that they were changing the license of their MapboxGL JS library as part of their latest v2.0 release. The library has gone from an Open Source ...
NewsPlease fill out the below form and we'll be in touch real soon.