For our latest meetup, we had the pleasure to welcome Till Nagel (@tillnm), Jonas Parnow (@zeto) and Sebastian Meier (@seb_meier).
Jonas Parnow is a freelance visualization expert and part time working for Golden Section Graphics. He introduced some of his latest visualization work and gave us a peak behind the scenes. Here are some of the Tools he talked about:
Gephi, D3, React, ScrapeSimilar
And of course Jonas’s helpful toolkit:
Till Nagel is a guest professor at the University at Burg Giebichenstein. He is specialized in interactive (geo-) data visualization. One focus of his are interactive exhibits, one of his latest works cf - city flows, was exhibited at the Streams and Traces exhibition.
IN addition to the inspiring projects, Sebastian gave quick introduction to Postgresql and PostGIS:
Introduction to PostGIS (& Postgresql)
When you are working with GeoData, Maps and Geovisualization, at some point you will find yourself in the need of using a database, a database that supports spatial features. Either the data set you want to use comes as a Postgres dump or your data is just to complex and big to be handled in a json or csv file.
Postgresql (alternate documentation) and Postgis are handy combination to work with large spatial datasets. Many webservers support it natively and there are also comfortable installers for local setups: Mac http://postgresapp.com/de/, Windows: https://www.postgresql.org/download/windows/. If you want a GUI to access and query your data you can either use pgAdmin or on Mac Postico.
Before we can start you should have gdal and osm2pgsql installed: http://cl.ly/0j0E0N1J3z0z
Before we can import any data, we need to create a new database, to hold the data, you can use pgsql from command-line or use one of the previously highlighted GUIs:
Now we add the spatial capabilities by adding the PostGIS extension:
CREATE EXTENSION postgis;
Okey, we are all set, from here you can create spatial data and query it.
A good way to start exploring Postgres and Postgis is to download a OSM dataset and import it into our postgres server. As we are in Berlin, lets head over to Geofabrik.de and download the Berlin area: https://download.geofabrik.de/europe/germany/berlin-latest.osm.pbf
osm2pgsql -d NAME_OF_DATABASE /DIRECTORY/YOUR_OSM_FILE.pbf
This might take a while....
Now we have some data to work with. Let's take a look at the planet_osm_polygon table for a start. This table contains all polygons. The actual geometry of each item in the table is stored in the column "way". You will notice that the spatial data is stored in a binary format, which means you cannot directly read it (at least most of us cannot, i guess).
So the first command you want to remember is ST_AsText(column_name). This will return the column in a readable format. And if you want to take this data and put it in a web-map, an even more useful command is ST_AsGeoJson(column_name), which will return a well formatted geojson string, that you can use in your web-map.
If you want to insert additional data into your table, the easiest way is using the ST_GeomFromText command, which takes a string and converts it into geometry data, e.g. 'LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932)';
http://postgis.net/docs/manual-1.4/ST_GeomFromText.html
Before we start create a little more complex queries and use spatial functions, another important thing in postgis are projections. As we all know sptial data comes in a variety of projections, postgis natively support a bunch of projections, but if you are in need you can even add custom projections into the "spatial_ref_sys" table. This means every geo-column always has a projection, normally when you create a table manually you would define the projection. Here comes a first complication, you might think great the column knows it projection so i am safe, well... the projection is defined, but postgis simply expects that all data in the column, all data inserted into the column etc. has the projection, if the data has another projection it will still think the projection is set and everything will become messy, so it is really important when importing data etc. you need to convert it into the correct projection. And this job can easily be done by Postgis:
To force a column (independently from the projection set for this column) into a certain projection we can use ST_SetSRID(column_name, SRID_ID). We can then use ST_Transform(column_name, SRID_ID) to change the projection, all transformations are calculated by PostGIS. The great thing about those two function is, that we can also compare data from two tables (or columns) that are present in two different projections and PostGis will take care of the rest.
Lets look at some of those comparison/query features of PostGIS:
Probably the easiest one and most used feature is ST_Distance and ST_DWithin. ST_Distance simply calculates the distance between two features. ST_DWithin does the same and directly checks if distance is smaller than value X and returns true or false. Using this we can get all buildings that are near point X:
SELECT way FROM planet_osm_polygon WHERE building = 'yes'
AND ST_DWithin(way, ST_Transform(ST_SetSRID(ST_GeomFromText('POINT(13.42277 52.49658)'),4326),900913), 100)
Note: the number defining the distance follows a weird logic, depending on the projection, it sometimes is meters, sometimes miles, sometimes something else, so be careful what to expect.
These kind of functions are useful, but you could almost calculate this manually, things that are more interesting are functions that combine two columns. For example ST_Contains, ST_Intersects, ST_Overlaps etc.
Those features allow you to find polygons, lines or points that intersect for example.
Especially for intersects and these kinds of things there are functions that can help, e.g. ST_Buffer, which creates a buffer around a point, a line or a polygon, allowing you to find e.g. points that are in a certain catchment area around your object.
So here is the IXDS building:
SELECT ST_AsGeoJson(ST_Transform(way,4326)) FROM planet_osm_polygon WHERE building = 'yes'
AND ST_DWithin(way, ST_Transform(ST_SetSRID(ST_GeomFromText('POINT(13.42277 52.49658)'),4326),900913), 20)
You can check by copying the code to geojson.io
Lets build a buffer around that building:
SELECT ST_AsGeoJson(ST_Buffer(ST_Transform(way,4326),0.0001)) FROM planet_osm_polygon WHERE building = 'yes'
AND ST_DWithin(way, ST_Transform(ST_SetSRID(ST_GeomFromText('POINT(13.42277 52.49658)'),4326),900913), 20)
Lets now build a query that selects all the buildings nearby...
Get the geometry of the building:
SELECT ST_AsText(ST_Buffer(ST_Transform(way,4326),0.0001)) FROM planet_osm_polygon WHERE building = 'yes'
AND ST_DWithin(way, ST_Transform(ST_SetSRID(ST_GeomFromText('POINT(13.42277 52.49658)'),4326),900913), 20)
SELECT ST_AsGeoJson(ST_Transform(way,4326)) FROM planet_osm_polygon WHERE building = 'yes'
AND ST_Intersects(way, ST_Transform(ST_GeomFromText('POLYGON((13.4222080327773 52.4962812870821,13.4221899276404 52.4962886926653,13.4221736105618 [...] ,13.4222080327773 52.4962812870821))', 4326), 900913))
Normally you would of course simply query the object directly and not copy paste the geometry, this is just to make it simpler.
Beyond this query features, postgis also offers a lot of features for calculations from ST_Distance to ST_Area to ST_Length and much more.
For more introductory content, check out mapbox tutorials for linux, windows and mac:
https://www.mapbox.com/tilemill/docs/guides/osm-bright-mac-quickstart/
https://eggerapps.at/postico/
And the node.js script for connecting to your postgres database:
var Client = require('pg-native'),
http = require('http'),
express = require('express'),
app = express();
var client,
pg_conf = {
database:'maptime',
user:'YOUR_USERNAME',
password:'YOUR_PASSWORD',
port:5432,
host:"localhost",
ssl:false
};
client = new Client("postgres://"+pg_conf.user+":"+pg_conf.password+"@"+pg_conf.host+"/"+pg_conf.database);
client.connectSync();
function locations(req, res){
res.header("Access-Control-Allow-Origin", "*");
var json = [];
var rows = client.querySync("SELECT ST_AsGeoJSON(geom) FROM planet_osm_polygon LIMIT 1");
for(var i in rows){
json.push((JSON.parse(rows[i].st_asgeojson)).coordinates);
}
res.send(JSON.stringify(json));
}
//Setup HTTP Server (to handle external requests / communication)
app.set('port', 10066);
//pages
app.get('/locations', locations);
http.createServer(app).listen(app.get('port'));
