Open Source – Round Up the Usual Suspects

014 Postgres command line : psql : Create SQL function referring to a table or column that does not yet exist

I was trying to write a script that would allow me to measure distances to schools and my original script gradually built up tables that were subsequently deleted. Worked fine in one big sql script but when I tried to convert this into a function so that it could be more easily stored with the database I kept on getting error messages stating that it was not possible to create sql that referred to objects that did not exist. Postgres validates functions and will at default prevent creation of functions containing SQL that refers to objects not yet in existence.

Postgres does not however save dependencies for code in the function body. So although once the function is created the tables and views can be dropped (and the function still exists) in default you need a set of tables in place with default settings before the function can be created. One workaround would be to create dummy tables and views in advance and later drop them but this if often clunky and awkward. Luckily this validation can be turned off.

BEGIN;
SET LOCAL check_function_bodies TO FALSE;
CREATE or REPLACE FUNCTION examplefunction() Returns void AS $$
  CREATE TABLE t001 (pkid serial primary key, field1 varchar(20));
$$ LANGUAGE sql;
COMMIT;

Documentation says

check_function_bodies (boolean)

This parameter is normally on. When set to off, it disables validation of the function body string during CREATE FUNCTION. Disabling validation avoids side effects of the validation process and avoids false positives due to problems such as forward references. Set this parameter to off before loading functions on behalf of other users; pg_dump does so automatically.

see here
Documentation

Totally invaluable when you write scripts like I do.

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 the UK along with eastings and northings as an example – This lists each postcode in the UK along with further administrative categories. Apologies to anyone from outside of the UK that may not be able to access these files I hope the demonstration is still useful. For those wishing to try please follow the links.

After download you will see the problem each postcode is in a separate CSV.

Ordnance Survey Open Data Code Point UK Postcodes

After a short administration excercise to request a data download involving filling out your name you should be sent an email link to initiate a data download. The download consists of a zip file of two directories one named DOC one named DATA the DATA directory contains a subdirectory called CSV which at May 2018 for my download consisted of 120 csv files.

Opening a single file ( in this case Edinburgh EH ) we see

I’ve already figured this out here , but there are 10 fields here (some are blank in my example)

Here I create a table called T01CodePointCombined with 10 fields marked
F1 through to F10
Note if you don’t create the table this function is so powerful it will do it for you

I then create a module and ensure that all the CSV files I wish to import are in a single directory here “C:\Users\Mark\Documents\CodePoint\Data\CSV\”

Public Function ImportAllFiles()

        Dim strPathFile As String, strFile As String, strPath As String
        Dim strTable As String
        Dim blnHasFieldNames As Boolean

        ' Change this next line to True if the first row in csv file
        ' has field names
        blnHasFieldNames = False

        ' Replace C:\Users\Mark\Documents\CodePoint\Data\CSV\ with the real path to the folder that
        ' Now place the location of where the csvs are within the brackets below
        strPath = ""

        ' Replace tablename with the real name of the table into which
        ' the data are to be imported
        strTable = "T01CodePointCombined"

        strFile = Dir(strPath & "*.csv")
        Do While Len(strFile) > 0
              strPathFile = strPath & strFile

              DoCmd.TransferText _
                TransferType:=acImportDelim, _
                TableName:=strTable, _
                filename:=strPathFile, _
                HasFieldNames:=blnHasFieldNames

        ' Uncomment out the next code step if you want to delete the
        ' csv file after it's been imported
        '       Kill strPathFile

              strFile = Dir()
        Loop

        MsgBox "Finished"

End Function

Points to note make sure all csv are closed when you run it. That’s about it takes less than 5 minutes to move all the records from those 120 files into a single table within an MS Access Database.
After import if it’s gone correctly you should have in the region of 1.7 million records in T01CodePointCombined.

nuBuilderPro – Import csv into a table of your application MySQL database (Its very easy)

I don’t know about you but for me its pretty rare to start an application without any information. At the very least there may be lookup tables or you have information collected in a spreadsheet. Thus when I came to nuBuilderPro one of the first things I researched was how to get information into a table. nuBuilderPro uses a vanilla version of mySQL in the background so this is what we will be working with. We will be attempting to import a csv file. You will need a clean organised csv file.

First create the tables that you require information to go into. Ensure that you have exactly the same table structure as the csv file that you wish to import. Therefore either adjust the table or the csv appropriately. Failure to have the same structure will halt the import.

Next navigate to the administration panel using your particular variation of the below url. Note that it is important to have the / at the end of the url otherwise you will be taken to the more specific database administration page where you design forms. Don’t worry if this happens you can still get to the php administration page by hitting the databases button. In fact this is an alternative way of getting to the screens that I show here.

https://youracount.nubuilder.net/nuadmin/

Use your username and password to get into the nuadmin index panel

Once you have entered your username and password appropriately you should be at the following address

https://youraccount.nubuilder.net/nuadmin/index.php

Now select the small spanner sign in the top right – this takes you to the php admin section for your whole VPS there are other ways of going into this web page but we will go this way for now.
You should be taken to a section which looks as follows

All databases within your VPS should be listed on the left. Each new application will have a database created for it. Each database holds all the required tables that hold your database and are listed on the left hand side. Click on the database in question and then hit structure. You are interested not just in the database but also the particular table. There is a notification grey line at the top of the page which shows you what database and what table you are in.

Importing a csv is a straightforward process of hitting the import button at the top selecting the csv file and hitting the go button. If the csv file contains column names you may wish to alter the row at which import starts.

Once import has been completed it will indicate how many lines were imported and how long it took. If there are problems you will obtain a message indicating so. I tried to create a simple Russian / English dictionary and it was really very straightforward. It is important that the csv has the right number of columns as per your designed table.

The Gunslinger and the Indian

There’s a fight on your computer over a girl called Planned Obsolecence

I get that you need to update software periodically and I definitely want to see software developers paid an appropriate amount but what happens when no one can think of anymore functions to add? With my older applications I definitely change them less and less as time goes on. What then? Can we keep our ten year old software packages which still have the distilled mathematical knowledge from 2 millenia of scientific knowledge 95% of which we have never used?

How will software houses make their money? Temptation is to either by accident or design discourage or deny companies the ability to buy and maintain software outright for local installations rather requiring them to rent from proprietary servers to steady their reducing income streams? (cough splutter splutter Adobe)

I’m sure it will act as a tempting vacancy for the Open Source community, especially on the desktop.

What does the user do?

Go with the native Indian who is less familiar and sometimes rough around the edges but has a far more sustainable and economical way of life or the mercenary gun slinger who you always kind of half suspect has his own agenda and may leave town quickly.

To the horses boys there’s a fight coming.

QGIS – Free GREAT Digital Mapping Software

A map showing winds over the Atlantic

Looking for a desktop digital mapping package? You really need to check out QGIS it is an absolutely excellent open source geographical information system. At the time of writing the latest version was QGIS 2.4 – the below tips were taken from research into windows version of QGIS 2.2

Full program available here.
Link to www.qgis.org site (English)

Tip : Navigation – Magnification – Plus or Minus mangifier Icons or wheel scroll
Tip : Navigation – Scroll – cursor keys or alternatively the hand icon or hold down the space bar and movement of the mouse when pointer is in the map window.
Tip : Projection – CRS stands for Coordinate Referencing System – lots of different ways of showing what is essentially the surface of a sphere on a flat surface – and more generally referred to as map projection – you will remember from geography. For most UK maps the coordinates are often in Ordnance Survey UK Grid therefore you want the properties of Coordinate Referencing System of the project to be OSGB and you want the coordinate referencing system of the individual layers to be OSGB as well. Once this is done the scaling will be correct and so will the measurement tools.
Tip : View / Panel – allows you to switch on and off menus – very good and very powerful
Tip : Graphical Record selection – Icon in the middle of the toolbar that has a number of differing options – it’s a drop down that allows different things for selection.
Tip : Attribute Record selection – Icon in the middle of the toolbar that allows for table attribute selection. Shows the table and this can be sorted properly.
Tip : Deselect Records – can individually de-select using the keyboard alternatively you can also use the de-select icon in the middle of the top of the screen.
Tip : Browser – brilliant for navigating through the directory and seems a lot quicker than going through the pop up individual menus on the left – for me anyway – additionally you can add an additional browser layer and transfer things between directories. It is an excellent alternative to the file dialogue manager.
Tip : View / Decorations – You can add things like scale bar and copyright to the map window here – very intuitive and nice finishing touch to your projects.
Tip : Labelling – Make scale dependent – highlight the layer you are interested in and right click. Now select the Labels option and within the Size section change the drop down from points to map units.
Tip : Labelling – Threshold the labelling – right click on layer and then go to the Rendering section and select scale based visibilty and adjust accordingly.

Above interpreted from the QGIS manual see:
Link to PDF version of QGIS v2.2 manual