Author Archives: Mark

About Mark

Mark Brooks a forty something individual working and living in and around Edinburgh

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

SQL Azure – Group child records by parent in order

Lets say we have two tables A site parent table T001 consisting of two fields PKID Sitename And a planning application child table T002 consisting of three fields PKID PKIDT001 PlanRef We have the following records in the parent table … Continue reading

Posted in All, SQL Azure, SQL Server | Comments Off on SQL Azure – Group child records by parent in order

Lighthouse – Performance statistics for Web Sites

As part of the general move towards the web I continue to investigate and learn about web development. An important aspect for any developer considering how to serve programs to clients and colleagues with as little resistance as possible – … Continue reading

Posted in All, Chrome, Productivity | Comments Off on Lighthouse – Performance statistics for Web Sites

MS Access Function CreateSimpleStrings()

A simple function that will loop through and create strings that add a number to a simple string. This string will then be used to create update queries. The same could be done in Excel or any other spreadsheet option … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access Function CreateSimpleStrings()

Visual Basic Script that can be used to rename a file

Here I am trying to automatically load log files into a MS Access file. Log files are actually txt files which need their extension changed if like me you wish to automate their import into MS Access. The following Visual … Continue reading

Posted in All, VBS Scripts | Comments Off on Visual Basic Script that can be used to rename a file

MS Access Function – Run VBS script from within MS Access

A very simple function that will allow you to run any vbs script from within MS Access Function RunVBS() Shell “wscript c:\users\mark\vbs\RenameFile.vbs”, vbHide End Function

Posted in All, VBA Code MS Access, VBS Scripts | Comments Off on MS Access Function – Run VBS script from within MS Access

Chrome polishes its menu setup – Chrome Shortcuts now run web pages in application mode

PS – UPDATE AT 10 NOVEMBER 2018 – I MAY TAKE THIS POST DOWN AS MY CHROME VERSION ON WINDOWS 10 NO LONGER GIVES ME THE OPEN IN NEW WINDOW TICK AND JUST CREATES A SHORTCUT ON THE DESKTOP NOW. … Continue reading

Posted in All, Chrome | Comments Off on Chrome polishes its menu setup – Chrome Shortcuts now run web pages in application mode

MS Access Function – import all CSV files from a directory with the same structure into a single table

This is a really nice function that can be used to place all data from multiple CSVs (with the same structure) into a single table. Here I use the Ordnance Survey’s excellent Code Point data set that gives postcodes in … Continue reading

Posted in Address Matching, All, Geographical Information Systems, GIS, MS Access, Open Source, VBA Code MS Access | Comments Off on MS Access Function – import all CSV files from a directory with the same structure into a single table

MS Access Function – Scan through a directory and write list of files to a table

Pretty much as the title Ensure you have a table called tblFiles with one field called Filename Here I am finding all files in folderspec = “C:\Users\Mark\Documents\CodePoint\Data\CSV” Alter as appropriate Function ShowFolderList() Dim fs, f, f1, fc, s Dim rs … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access Function – Scan through a directory and write list of files to a table

MS Access – Code Snippets for Navigating using Internet Explorer

Waiting for a web page to load While ie.Busy DoEvents Wend Selecting differing radio buttons Dim ieRadio As Object Set ieRadio = ie.Document.all ieRadio.Item(“datetype”)(1).Checked = True A function that can be used to delay action before an action in code … Continue reading

Posted in All, Data Mining, VBA Code MS Access | Comments Off on MS Access – Code Snippets for Navigating using Internet Explorer

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