PostGIS Tutorial Data

1 Reply

The OpenGeo (now called Boundless) PostGIS tutorial has long been my recommendation for people just getting started with PostGIS. It provides a broad intro to PostGIS, great for users new to SQL.

Unfortunately they recently removed it from their website. The guide was a fairly old now, so I don’t blame them. But it was a great free resource.

Luckily Archive.org has saved a copy of it. The course was based on open data, so compiling a similar dataset to complete the course was not difficult.

The course can be accessed at:

https://web.archive.org/web/20170913113658/http://workshops.boundlessgeo.com/postgis-intro/welcome.html

And the data can be accesses at (choose the green Clone or download button and Download ZIP):

https://github.com/HeikkiVesanto/postgis_course_data

I would skip the Loading spatial data chapter (very dated at this stage), and just use the ogr2ogr commands in the GitHub readme. Great chance to get started with ogr2ogr as well.

The data is also not 100% the same as the original. So some of the queries will have to be modified. Particularly the neighborhoods are different. So they may have slightly different names/boundaries/capitalization. So keep that in mind when creating your queries, and interpreting the results. But overall the data is pretty similar.

Also, if/when the Archive.org is gone that will likely be the end of the tutorial. So I would not waste any time completing the it.

Copying Rasters in PostGIS

Leave a reply

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
);

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);

1 2	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;

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.

Dublin Monopoly

Leave a reply

Dublin has an official Monopoly board in time for Christmas.

Not to be confused with the Irish Edition of Monopoly. Which consisted of locations in Dublin with the addition of Shannon Airport.

Buy at:

Irish Edition

Dublin version

Full screen map.

Check out: MappingDublin.com

Anaconda install with OSGeo4W

Leave a reply

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):

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

1	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%

@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=

@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=

@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.

Ireland and Dublin Street Orientations

Leave a reply

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

GIS for Thought

GIS, Mapping, and Remote Sensing

PostGIS Tutorial Data

Copying Rasters in PostGIS

Dublin Monopoly

Anaconda install with OSGeo4W

Ireland and Dublin Street Orientations