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.
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.
Ask YC: Best Linux distro to deploy as a Server
"... i will like to know the best Linux Distro to use as my server ..."
The best Linux distro is probably a *BSD variant*.
Your OS choice for servers differs from desktops. Security, stability and available expertise is more important than ability to configure. The self compiled Linux distributions available for your desktop could expose your server to attack. You can read more here for reasons why ~ ftp://rtfm.mit.edu/pub/usenet/news.answers/unix-faq/faq/part6 [0]
think of BSD as an older, more conservative
and stable OS than the "open-all-doors" Linux ...
You can use Linux, but it takes a bit more work to secure than BSD. For the simple reason Linux is younger, more open and therefore less secure than BSD variants (unless hardened with patches like SE) ~ networking.ringofsaturn.com/Unix/bsd.php OBSD and FreeBSD would be my choice. FreeBSD was also the choice of Viaweb. I suppose *"Robert Morris"* knows a thing or to about securing systems ~ www.paulgraham.com/vwfaq.html [2] BSD OS's are safe, secure and have access to the source, support lots of hardware with caveats. Here is a quick choice guide ~ www.serverwatch.com/tutorials/article.phpr/10825_3393051_... [3]
Of the Linux servers I'd stick with Debian variants which includes Ubuntu. Debian has their own way of doing things but it is stable, well understood. Maybe SE-Linux is worth a look ~ www.nsa.gov/selinux/ [4] and en.wikipedia.org/wiki/Selinux [5]
*"... i will like to know the best Linux Distro to use as my server ..."*
That also depends on what hardware you are using. Is it hosted, your own?? Check your hardware lists before you choose your OS.
some time later ...
"... However, the original poster wanted info specifically about linux, and while a BSD UNIX is similar, there are enough differences that productivity ..."
The poster also mentioned inexperience then requests a less secure server OS than what is possible. Productivity may indeed suffer. Would it be a better idea would be to get an experienced sys-admin to look over the server and secure it?
"... I think it is a bit misleading to say that a distro like debian "opens all doors", that is not the case ..."
Of all the distro's debian is probably the better of the bunch to work with. Debian can be secured. But the strength of Linux and debian, its openness is also its weakness. Default installed distributions still install more applications than you need. This can leave the potential of an unwanted application running to be exploited. Debian before Ubuntu was also the hardest to configure hardware.
"... One must acknowledge however that this was a serious security problem on debian's side and in contrast OpenBSD has had _two_ exploits in _ten_ years. Those are some pretty sterling security credentials for OpenBSD ..."
When it comes to security I'd take OBSD over any linux distro for the simple reason the source has been audited. It is secure by default (You have to actively install what you require). You will not be let down core applications with holes. Having said that I balk at upgrades.
"... The FreeBSD threading model does not work well with MySQL for example and you can see database problems there. I suspect Postgres on FreeBSD is more common and works rather well so you can go that route instead. ..."
Or dispense with RDBM's altogether?
some time later ...
"... That has X disabled and no open ports by default ..."*
A big plus. The downside is the CLI will now be your best friend. For those weened on GUIs, all that finding, typing and configuration can be a time sink. Also LTS runs on 64 bit systems and has commercial support (Long Term Support) ~ www.ubuntu.com/support/paid
Reference
[0] MIT, Unix FAQ "most commonly ask Q's on comp.unix.questions and comp.unix.shell
ftp://rtfm.mit.edu/pub/usenet/news.answers/unix-faq/faq/part6
[1] Darwin/Mac OS X: The Fifth BSD, "article describing the main 5 variants of BSD"
networking.ringofsaturn.com/Unix/bsd.php
next >>>