View allAll Photos Tagged postgres
The 10,000 biggest buildings in Paris, sorted by area. I used OSM data and selected all buildings within an 8km radius of the Louvre. Wrote a python script and used postgres/postgis to place the buildings.
The scan order is right-to-left, top-to-bottom, so the top-rightmost building is biggest.
The colouring is based on area - each band of colours represents one of 5 jenks breaks in the area data.
I can't claim to have invented this - I remember seeing a similar poster a few years back, but I can't find a link.
I quite like the way it looks a bit like ancient script, or the Rosetta stone.
This visualisation was produced in QGIS 2.12.1, with a little help from postgres/postgis (could also be done in QGIS only).
Areas of each country's circle are in proportion to population.
How it was done
First of all, reprojected the Natural Earth data to epsg:3410 (a cylindrical equal area projection in meters). This is important; doing it in epsg:4326 makes countries appear bigger the further they are from the equator (areal distortion). Because degrees.
Next, used postgis to find the centroids and buffer them, so that the circle area is in direct proportion to population. Brought these in as CSV delimited files after exporting from pgAdminIII. The query sorted images in descending order of size, so smaller countries appear on top of larger countries.
Unfortunately, this projection keeps areas in scale, but distorts shapes. Some countries start to look like eggs. Oh noes!
Got round this by scaling the map down to a small size (a few square kilometers) and centred on null island, which removed the shape distortion, but kept the equal-area. Turned off OTF projection.
Flag images came from CIA World Factbook, and were used as raster Image fills. Used the FIPS code from the Natural Earth data to build the filepath to the CIA factbook images like this
'/tmp/factbook/flags/' || "fips" || '-lgflag.gif'
Because you can only scale the image width at present (I used bounds_width($geometry) in map units) I had to convert each flag into a square format using ImageMagick. Most flags are wider than they are tall, this avoids tiling the flag in each circle, at the expense of corner detail.
Apologies in advance to Norway - not sure what happened there. Some manual tweaking may be needed :D
I've used Postgres/PostGIS and QGIS to create a strip map of the recently opened Borders Railway, this shows the section between Edinburgh Waverley station and Newcraighall.
This map straightens out the railway line - shown as the thick white line in the middle.
Points on surrounding roads have been moved so that their vertical position is based on their perpendicular distance from the railway line.
This means that 'up' can be North, North-East, East or even South-East, depending on the direction of the train ;-)
It's surprisingly difficult to tell whether a point is to the left or right of a long line which meanders :)
Blog post on how this was done.
Grid was created using this plugin (now available in the plugins repo)
Using data copyright OpenStreetMap contributors.
Using QGIS Nødebo and PostGRES / PostGIS, and using 2011 Census data.
The Blue area contains the same population as Scotland (5.3 million).
The Pink area has the same population as Scotland, Wales and Northern Ireland combined (just over 10 million).
The population of Scotland would reach about half-way to the M25
In case you're wondering, the reason why the pink area is much larger than double that of blue, it's because as you go out, population density decreases. This means Scotland's area appears smaller in relation to the combined area than a simple 1:2 ratio would suggest.
The combined population of Scotland, Wales and Northern Ireland would extend just past the M25.
How it was done : Based on cumulative population of output areas, which were sorted by ascending distance from Westminster based on centroids.
QGIS features used : print composer, buffer/dissolve, draw effects/drop shadow, QuickMapServices plugin.
See also this map comparing some smaller european countries with Edinburgh
Dividing the world into areas with a quarter of the global population each.
Four maps, based on Longitude, Latitude, and (great-circle) distance from Shaghai and London.
Based on SEDAC gridded world population v4.
More info on how this was done.
using QGIS 2.16, postgres/postgis.
tip: use lighten render mode in composer if the background is black, that way you can slide the maps closer to each other without the empty areas obscuring things (or use nodata properly, i suppose... :)
when you stand on the coastline of the UK, which country is due west or east? grey dots are points where the answer is the UK.
qgis / postgres / postgis / python.
A bit of experimental cartography using Qgis, python and Postgres/PostGIS. Inspired by this generative artwork by Matthew Conroy.
Took the building outlines of Edinburgh (using the QGIS radius select tool), and re-located them at random in a square 12 km across. If you look closely you'll see that no two buildings overlap or touch.
I started with the biggest buildings first, to make sure I didn't run out of space at the end.
Used ordnance survey data outlines, thanks to Alasdair Rae for making available a scotland-only shapefile. That saved me a lot of time.
Colorised by area using $area, 5 bands, jenks breaks. Used ColorBrewer GnBu5 gradient.
comparing the population of four European countries - Vatican City, Monaco, Andorra and Iceland - with Edinburgh.
Done in QGIS 2.16 Nodebo.
Brought the census data into postrgres and ran a postgis query. This selected output areas by increasing distance from Haymarket Station, and calculated a cumulative population based on centroid to centroid distance. A single rule-based layer gave the bands.
Used UK Census data 2011 and OpenStreetMap as background, with the QuickMapServices plugin.
using QGIS 3.2 on Mac, postgres/postgis. Using the Great Britain dataset from Geofabrik
This dataset includes the Channel Islands (cropped off) and Isle of Man (included), so strictly speaking it's Great Britain and the Isle of Man.
Roads in red are called '- road'. Roads in yellow are 'streets'. Generally speaking, streets are shorter and appear in the oldest parts of towns, and roads are longer and join towns together.
The big curled street is 'Ermine Street', a roman road.
It reminds me our work on indexing arrays in PostgreSQL (elephant is the official logo of PostgreSQL), where I and Teodor used RD-Tree (Russian Doll Tree) data structure to store signatures and fast search.
Yoga people may identify one variant of Vrikshasana.
What do you think about mouse ?
Anastasia is a PostgreSQL developer. I made this picture in Vienna after session day of PGGConf-2015.
using QGIS, Python and Postgres/PostGIS.
Inspired by this question on GIS Stack Exchange about world building. Using QGIS / Python / PostGRES / PostGIS to create a random world.
Starting with a voronoi lattice, used a flood-fill algorithm to make each city spread outwards into uncontested neighbouring areas.
experiment with a pseudo-cartogram. Done in QGIS 3.0.1 on Mac, using Postgres/PostGIS to do the heavy lifting. Technically this is a map (which you can see by zooming in), just a distorted one ;-)
Took nodes from OSM building and road centroids (about 1M points).
Sorted their x and y coords independently, then distributed each axis' values linearly across a unit square.
This means that a point at (0.5,0.7) has 50% of the nodes to its west, and 70% of nodes to its south. (0,0) is lower left corner, (1,1) is top right corner.
This has the effect that the central belt is stretched as the vast majority of the data is there. It tends to squash areas nearer the edges (e.g. the Western Isles, Shetland). These are flattened and/or shrunk. e.g. the tiny white blob at the top right is Shetland :)
It also uses up more space in areas with more data.
Detail and shape is fairly well preserved locally, especially in contiguous areas with lots of data (e.g. Glasgow and Edinburgh) but distortion is worse in sparse areas.
The least distorted area - Edinburgh - has the most detail. It also distorts areas at the same range of latitudes/longitudes (this is also the reason for the various "streaks" across the map)
My son Sergey visited my office to discuss optimization of PostgreSQL uisng machine learning. He is going Google Deepmind for internship this summer.
Buildings in Iceland, data copyright OpenStreetMap and contributors.
Points coordinates are ranked independently in the x and y axes and plotted within a square, using the rank as the coordinate in each axis.
Using QGIS, Postgres/PostGIS.
Similar analysis of Scotland here
I've posted one of these before but this one is for our master database.
The initial drop is us starting to hit pgbouncer on the server, rather than via lots of pgbouncers on the client, and switching to transaction pooling.
It got a bit messy as it turns out our server_lifetime and server_idle_timeout were tuned way down; to seconds rather than minutes, so a lot of connection churn was occuring. The pleasant flat bit at the end is our current state.
PGCon 2011 Developer's meeting group -- Ottawa, Canada.
Back row, from left to right: Robert Haas, Selena Deckelmann, Marko Kreen, KaiGai Kohei, Stephen Frost, Magnus Hagander , Robert Treat, Tom Lane, Heikki Linnakangas, Mark Wong, Josh Berkus, Kevin Grittner, Dimitri Fontaine, Koichi Suzuki, Andrew Dunstan, Fujii Masao, Jeff Davis, Greg Smith, Tatsuo Ishii, Dave Page, Simon Riggs.
Front row: Greg Stark, David Wheeler, David Fetter, Bruce Momjian, Teodor Sigaev.
They came us to made a clip with me talking about visit of Steve Wozniak to Moscow University. Our company organized this visit.
using QGIS, Postgres/PostGIS to outline continuous conurbations using postcode density.
Shows all postcodes which can be reached by a series of 500m steps, starting from Westminster. Yellow / Red bands show how the algorithm works.
Done using a Python script and Postgis queries.
Could also do this in QGIS by buffering all the postcodes by 500m and dissolving the results, although this would take ages (1.5 million points+) and wouldn't give the stripes
Used data copyright OpenStreetMap contributors, postcode data from OS Crown Copyright and Database Rights 2015.