Category Archives: Postgres

009 Postgres Command Line : psql : Altering the stated language – lets try Python

Given that on creation of a function it is a requirement to state the procedural language of calculation what happens if we try using Python? Just so happens the calculation part of Python for Fahrenheit to Celsius syntatically is identical … Continue reading

Posted in All, PostGIS, Postgres, psql | Comments Off on 009 Postgres Command Line : psql : Altering the stated language – lets try Python

008 Postgres Command Line : psql : Another function example

As per 007 lets try and create our own function to calculate centigrade from fahrenheit and call it calcf CREATE FUNCTION calcf(integer) RETURNS integer AS ‘SELECT ($1 – 32) * 5/9’ LANGUAGE SQL RETURNS NULL ON NULL INPUT; Next lets … Continue reading

Posted in All, PostGIS, Postgres, psql | Comments Off on 008 Postgres Command Line : psql : Another function example

007 Postgres Command Line : psql : Create a function that returns a single value

As per the nature of recursion a function is a variable is a function. Previously in 005 and 006 we wrote functions that returned subsets of queries they were effectively dynamic queries where I entered a parameter that was used … Continue reading

Posted in All, PostGIS, Postgres, psql | Comments Off on 007 Postgres Command Line : psql : Create a function that returns a single value

006 Postgres Command Line : psql : Identify definition of a Function

In 005 when last we left our intrepid explorers we were wondering if having defined an inline table that contains the definition of the selection purely in the user defined function how do we see what that selection is as … Continue reading

Posted in All, PostGIS, Postgres, psql | Comments Off on 006 Postgres Command Line : psql : Identify definition of a Function

005 Postgres Command Line : psql : Create a User Defined Function and Identify its creation and then Drop it.

Like all platforms it is possible to create bespoke functions in Postgres For the following I assume; 1. Postgres 9.5 is installed with the server running (syntax should be the same for other versions) 2. A database called exampledb has … Continue reading

Posted in All, PostGIS, Postgres, psql | Comments Off on 005 Postgres Command Line : psql : Create a User Defined Function and Identify its creation and then Drop it.

004 Postgres Command Line : psql : Create a spatially enabled table

For this you will need to have a version of Postgres Database engine installed and running and you will need to have created a database which has the PostGis extension installed. Open psql Login to the database you wish to … Continue reading

Posted in All, GIS, PostGIS, Postgres, psql | Comments Off on 004 Postgres Command Line : psql : Create a spatially enabled table

003 Postgres Command Line : psql : List all databases in a Postgres Instance

This can be used by most roles either within a database or within the postgres instance. \l or \list Here I show that I am logged in with the general username.

Posted in All, PostGIS, Postgres, psql | Comments Off on 003 Postgres Command Line : psql : List all databases in a Postgres Instance

002 Postgres Command Line : psql : Enabling extensions (PostGIS) to a database

So you have an existing database in PostGres that you wish to add the PostGIS extension to. You will need to be logged in with a username that has superuser privileges. Here let me do this using the postgres default … Continue reading

Posted in All, GIS, PostGIS, Postgres, psql | Comments Off on 002 Postgres Command Line : psql : Enabling extensions (PostGIS) to a database

001 Postgres Command Line : psql : Getting Started with Postgres

I am just getting into PostGres and here are some rough notes for my reference. Assuming you have a postgres admin account you want to sign in first of all and create a database To find the command line go … Continue reading

Posted in All, GIS, PostGIS, Postgres, psql | Comments Off on 001 Postgres Command Line : psql : Getting Started with Postgres

000 Postgres Command Line : psql : Useful RESOURCES Link to Github Cheat Sheet AND live on the web SQL excercises

Some useful Resources PSQL Cheat Sheet PostGres Excercise website – pretty awesome Postgres Excercises

Posted in All, Postgres, psql | Comments Off on 000 Postgres Command Line : psql : Useful RESOURCES Link to Github Cheat Sheet AND live on the web SQL excercises

MS Access Front End – Linked to PostGreSQL back end – a simple walk through using Access 2003

As I have indicated before MS Access makes a brilliant ETL tool. Important in this is being able to connect to different databases. I have set out how to connect to MySQL and SQLAzure before – the following sets out … Continue reading

Posted in All, MS Access, PostGIS, Postgres | Comments Off on MS Access Front End – Linked to PostGreSQL back end – a simple walk through using Access 2003

QGIS – Import shape file into PostGIS Table

The following uses QGIS 2.14.2 Essen and PostGres 9.5 A number of local authorities have released information through the UK’s data government site. The following example uses a shape file obtained from Lichfield District Council – At 2nd of October … Continue reading

Posted in All, Geographical Information Systems, GIS, PostGIS, Postgres, QGIS General | Comments Off on QGIS – Import shape file into PostGIS Table

Connecting to PostgreSQL 9.3 from QGIS 2.8.1 – local host

First ensure that you have both Postgres and QGIS installed on your machine. In order for you to be able to connect to Postgres from QGIS on local host you must ensure 2 things. Firstly that the PostGIS plugin has … Continue reading

Posted in All, Geographical Information Systems, GIS, PostGIS, Postgres, QGIS General | Comments Off on Connecting to PostgreSQL 9.3 from QGIS 2.8.1 – local host