Categories
All OSGEO PostGIS QGIS

Copying Rasters in PostGIS

I ran into a process where I wanted to create copies of rasters in PostgreSQL. While seemingly a simple process this took me a bit of work to figure out.

For my workflow I had three rasters, which all have the same size, and I want to load them into the same PostGIS table with three raster geometry columns. I don’t think this will work for different sized rasters since the rid’s will not match.

Three rasters:
raster1
raster2
raster3

Which I want to copy into:
merged_raster

First to create the merged raster table:

CREATE TABLE merged_raster

(

  rid serial NOT NULL,

  raster1 raster,

  raster2 raster,

  raster3 raster

);

Then to add the rid’s. These are the id’s of the tiles that the raster was split into when loading. If your tile size is large enough then you may only have one.

INSERT INTO merged_raster(rid)

(SELECT rid FROM raster1);

Then copying the actual data is straighforward (this assumes the raster column in the raster1 datasets is called rast):

UPDATE merged_raster m

SET raster1 = r.rast

FROM raster1 r

WHERE r.rid = m.rid;



UPDATE merged_raster m

SET raster2 = r.rast

FROM raster2 r

WHERE r.rid = m.rid;



UPDATE merged_raster m

SET raster3 = r.rast

FROM raster3 r

WHERE r.rid = m.rid;

Now I still have an issue that QGIS will not load these layers. It will always load the initial raster column no matter what is chosen.

Categories
All OSGEO

Anaconda install with OSGeo4W

I was coming across some errors when installing Anaconda, Miniconda specifically.

https://conda.io/miniconda.html

I think part of the reason is I have quite a few installs of Python due to OSGeo4W.

My error:

Fatal Python error: Py_Initialize: unable to load the file system codec

ModuleNotFoundError: No module named 'encodings'



Current thread 0x00002554 (most recent call first):

The solution:

Update the activate.bat file that is called when launching from the start menu. For me located in:

C:\ProgramData\Anaconda\Scripts

We need to add in the following to clear out and reset the python environment before launching anaconda:

@SET PYTHONPATH=

@SET PYTHONHOME=C:\ProgramData\Anaconda

@PATH C:\ProgramData\Anaconda;C:\ProgramData\Anaconda\Scripts;%PATH%

So, editing the file from:

@REM Test first character and last character of %1 to see if first character is a "

@REM   but the last character isn't.

@REM This was a bug as described in https://github.com/ContinuumIO/menuinst/issues/60

@REM When Anaconda Prompt has the form

@REM   %windir%\system32\cmd.exe "/K" "C:\Users\builder\Miniconda3\Scripts\activate.bat" "C:\Users\builder\Miniconda3"

@REM Rather than the correct

@REM    %windir%\system32\cmd.exe /K ""C:\Users\builder\Miniconda3\Scripts\activate.bat" "C:\Users\builder\Miniconda3""

@REM this solution taken from https://stackoverflow.com/a/31359867

@set "_args1=%1"

@set _args1_first=%_args1:~0,1%

@set _args1_last=%_args1:~-1%

@set _args1_first=%_args1_first:"=+%

@set _args1_last=%_args1_last:"=+%

@set _args1=





@if "%_args1_first%"=="+" if NOT "%_args1_last%"=="+" (

    @CALL "%~dp0..\Library\bin\conda.bat" activate

    @GOTO :End

)



@CALL "%~dp0..\Library\bin\conda.bat" activate %*



:End

@set _args1_first=

@set _args1_last=

To:

@REM Test first character and last character of %1 to see if first character is a "

@REM   but the last character isn't.

@REM This was a bug as described in https://github.com/ContinuumIO/menuinst/issues/60

@REM When Anaconda Prompt has the form

@REM   %windir%\system32\cmd.exe "/K" "C:\Users\builder\Miniconda3\Scripts\activate.bat" "C:\Users\builder\Miniconda3"

@REM Rather than the correct

@REM    %windir%\system32\cmd.exe /K ""C:\Users\builder\Miniconda3\Scripts\activate.bat" "C:\Users\builder\Miniconda3""

@REM this solution taken from https://stackoverflow.com/a/31359867

@set "_args1=%1"

@set _args1_first=%_args1:~0,1%

@set _args1_last=%_args1:~-1%

@set _args1_first=%_args1_first:"=+%

@set _args1_last=%_args1_last:"=+%

@set _args1=



@SET PYTHONPATH=

@SET PYTHONHOME=C:\ProgramData\Anaconda

@PATH C:\ProgramData\Anaconda;C:\ProgramData\Anaconda\Scripts;%PATH%



@if "%_args1_first%"=="+" if NOT "%_args1_last%"=="+" (

    @CALL "%~dp0..\Library\bin\conda.bat" activate

    @GOTO :End

)



@CALL "%~dp0..\Library\bin\conda.bat" activate %*



:End

@set _args1_first=

@set _args1_last=

Updating the paths as required.

This just clears out the python and windows environmental variables before launching, similar to what OSGeo4W does.

Categories
All Ireland

Ireland and Dublin Street Orientations

Based on the work by:

Geoff Boeing: Comparing City Street Orientations

Rixx: Street Orientations

The graphs show the percentage of streets that run in a certain orientation. So for a grid based city like Chicago, there will be a heavy bias in north/south and east/west streets. Bearing in mind north and south will be the same (unless there are one-way streets, which only count in the direction they run in).

But for older cities that formed naturally, without modern city planning, the streets should be more varied.

Ireland:

Largest populated places by population. Based on the Ordnance Survey Ireland urban areas. As it is OSI data, Northern Ireland is not included.

Dublin Postcodes:

Some areas are clearly impacted by large motorways running through them.

And for non-Dubliners, a map of the postal district boundaries:

I updated the script by Rixx, so that it would take a ShapeFile as an input with a few caveats (it must be WGS84, it must have an attribute that has the are name and it must be called settl_name).

Check out the script at: GitHub

Categories
All Animations Maps

Stanley Cup Champions Since 1915

Stanley Cup Champions Since 1915:

Average Location of Stanley Cup Champions Since 1915:

The average locations were created using a window function in PostgreSQL. We can utilise the geography type to take into account the curvature of the earth and make the calculation on a spheroid.

So for the average location of the last five years:

select id,
ST_Centroid(st_collect(geom)
over (ORDER BY id ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)::geography,
True)::geometry as five_year_cent
from nhl.winner_1915
Categories
All OpenStreetMap OSGEO QGIS

QGIS OpenStreetMap Scales

Save as a text file ending in .xml like qgis_scales.xml

These are the scales OpenStreetMap tiles are rendered in for 96 dpi, so the map will look sharp on most monitors. These are the scales for the zoom levels.

The xml file can then be loaded into the project from:

Project> Project Properties…> General> Project scales

<qgsScales version="1.0">
    <scale value="1:554678932"/>
    <scale value="1:277339466"/>
    <scale value="1:138669733"/>
    <scale value="1:69334866"/>
    <scale value="1:34667433"/>
    <scale value="1:17333716"/>
    <scale value="1:8666858"/>
    <scale value="1:4333429"/>
    <scale value="1:2166714"/>
    <scale value="1:1083357"/>
    <scale value="1:541678"/>
    <scale value="1:270839"/>
    <scale value="1:135419"/>
    <scale value="1:67709"/>
    <scale value="1:33854"/>
    <scale value="1:16927"/>
    <scale value="1:8463"/>
    <scale value="1:4231"/>
    <scale value="1:2115"/>
</qgsScales>

Example:

1,000,000 (QGIS default):

1,083,357 (OSM wiki):

1,155,584 (From: 3liz):

Scales from:
OSM wiki

Zoom scales.

Categories
All Animations Ireland

GAA Winners 1887 to 2017 GIFs

Some of the outputs from my Data Driven Cartography workshop at the 2nd OSGeo Ireland conference.

Gaelic Football:

Final frame:
GAA football

Hurling:

Final frame:
GAA hurling

Categories
All Ireland OpenStreetMap OSGEO PostGIS QGIS Tutorials

Creating OpenStreetMap History Visualisations

I created a couple of OSM visualisations for my talk at the OSGeo Ireland conference.

See: History of OpenStreetMap in Ireland

These are pretty easy to make, but take a fair bit of time. I did mine for Ireland, but should work with any part of the world.

Required software:

  • PostgreSQL with PostGIS
  • Python
  • QGIS
  • osmium-tools

This is the trickiest part, installing osmium-tools: here.

Data:

An OSM full history export. The best source for these is GEOFABRIK.

For Ireland:

http://download.geofabrik.de/europe/ireland-and-northern-ireland.html

Due to GDPR, you will have to log in with an OSM id to download the full history extracts. User ID’s are personal data.

Process:

The workflow is pretty simple. Osmium-tools provides pretty easy API access to the history files, where you can provide a data, and it will extract what OSM was like at that date. We simply need to loop through the desired dates we want to extract, and pipe the results into a workflow that loads the data into PostgreSQL. The final step is simply rendering in QGIS using the time manager plugin.

Python Script:

Github GIST:

https://gist.github.com/HeikkiVesanto/f01ea54cca499a6a144d18cf8909c940

The tables in the database will be:

  • lines
  • multilinestrings
  • multipolygons
  • other_relations
  • points

Each feature will be tagged with the date it is associated with.

Visualisation:

To visualise the data in QGIS we use simply use the excellent time manager plugin, filtering on the load_date field and with a monthly interval.

Result:

Categories
All Animations Ireland Maps OpenStreetMap

History of OpenStreetMap in Ireland

Last week I was invited to give a workshop at the Second Irish OSGeo Conference in Portlaoise. The event was a great success with a attendees from across the OSGeo space, from academics to startups.

I also volunteered to give a talk on OpenStreetMap (OSM) in Ireland. Since we are currently in the process of setting up an official chapter of OSM in Ireland. Check out OpenStreetmaMap.ie if you want to help.

Check out my full talk: here

But some of the visuals are more interesting than the talk as a whole.

To start out, the first edits in Ireland.

The first line:

Located in Banbridge, in Northern Ireland between Newry and Belfast:

First polygon:

Clearly St. Stephens Green, a great park in Dublin:

Then a look at the full history of roads in Ireland on OpenStreetMap:

 

A large part of the history of OpenStreetMap in Ireland is the townlands project. Townlands in Ireland are small divisions of land, often used in addressing for example. Your address could be: Mr. O’Brien, Blue house, Tawny (the townland), Donegal. The postman knows where you are.

There are over 61,000 townlands in Ireland. Traced manually from 650+ out of copyright OSi maps from Trinity College. Check out Townlands.ie for more info.

The project ran from 2012 to 2017 and the progress is clear when charting the history of boundaries in OpenStreetMap in Ireland:

 

And since the conference was held in Portlaoise, here is how it looks over time in OSM:

 

For the script to generate these histories see: Creating OpenStreetMap History Visualisations

Categories
All OSGEO QGIS

QGIS Select Within Plugin 0.4

Runs through each geometry that you want to select from and tests if the centroid or the point of surface central point falls within the selecting geometry. If the central point falls within the selecting polygon, it is usually mostly inside the selecting geometry.

Or selecting based on percentage overlap. By default dissolving the selecting features first.

Useful if you are splitting up a polygon layer based on a polygon coverage layer, as each polygon will only end up in one of the selecting polygon areas. Unlike with an Intersects query which would return the geometry twice if it is on the border one on each side, or a Within query, which would not return the geometry at all. Very useful if your selection and selecting geometries have similar boundaries.

Version 0.4 brings a couple of new features to the Select Within plugin.

Most importantly it now supports mostly within and percentage within selections.

Also introduced is Pole of Inaccessibility within selections. As implemented in the QGIS core with the Polylabel algorithm: https://github.com/mapbox/polylabel

Point within selection examples. Centroid (red), Point on Surface (green), and Pole of Inaccessibility (blue):

Examples:

Centroid within:

Point on Surface within:

Pole of Inaccessibility within (1.0 tolerance):

50% within:

75% within:

Code:
GitHub Code Base

Issues and bug tracker:
GitHub Issues

Original Post:
Centroid Within Selection in QGIS

Categories
All OSGEO QGIS

QGIS Multi Ring Buffer Plugin Version 1

After about 3 years of existing. I am releasing version 1 of the Multi Ring Buffer Plugin.

QGIS plugin repository.

With version 1 comes a few new features:

  • Ability to choose the layer to buffer after launching the plugin
  • Ability to supply buffer distances as comma separated text string
  • Ability to make non-doughnut buffers
  • Doughnut buffer:

    Non-doughnut buffer (regular):

    Works either in a sequential manner by buffering the resulting buffer and differencing the previous buffer, or a central manner by buffering the original feature with different distances. Also by default the buffer dissolves the features before buffering.

    Dissolving makes adjacent features behave in a better manner without overlapping buffers. The QGIS buffer feature has the logic behind this.

    Post:

    Buffering methodology

    Known issues:

    Help Guide

    Code:

    GitHub Code Base

    Issues and bug tracker:

    GitHub Issues