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

Much like Google Maps changing the projection to a proper globe on zoom out. I noticed Google made a very slight change to their browser application which is a nice demonstration of how Google are constantly trying to improve their interfaces to make them more intuitive. At some point they altered the menu setup for creating web pages in application mode – I had written a post about the former method (see my post here) For those unfamiliar with this it is a really nice way of displaying web pages in what appears an application like window (ie no url bar or tabs). Comparing the two you can see that they have simply changed the menu from Add to desktop to Create Shortcut. Its a nice demonstration of the small improvements that are continually being made to the browser.

I had noticed that all my icons that I had formally created using the former application mode had stopped working.
Didn’t think much of it at the time but I think that must have been the date when Chrome changed over their code.

It should be noted that simply dragging the web page onto the desktop from the chrome browser window still gives the old windows shortcut what we want here is the Google shortcut. So here is how I do it.

Then select the following

Select create shortcut and a new dialog should appear

Name it and there you go

Now you get a nice icon on your desktop related to the site and what’s more when you open it up there is no address bar and within the taskbar you get the correct icon for the website see below example.

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 the UK along with eastings and northings as an example – This lists each postcode in the UK along with further administrative categories. As it is open data I list the links so you can get the same data if you want to follow along.

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 process to request the download including filling out your name you should be sent an email to download the data. This will consist 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
' contains the csv files
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.

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 As DAO.Recordset
Dim folderspec

Set rs = CurrentDb.OpenRecordset("tblFiles")

folderspec = "C:\Users\Mark\Documents\CodePoint\Data\CSV"

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files

For Each f1 In fc
rs.AddNew
rs.Fields("FileName") = f1.Name
rs.Update
Next

Set rs = Nothing

MsgBox "Finished"

End Function

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 (Not web specific). Here I set to 1 second
Public Function Await1()
Dim time1 As Variant
Dim time2 As Variant

time1 = Now
time2 = Now + TimeValue("0:00:01")
Do Until time1 >= time2
DoEvents
time1 = Now()
Loop

End Function

Split a string of text into a 1 dimensional array with splitting occurring at returns.
Dim strArray() as String
Dim strtoParse as String
Dim intCount as Long

'here I taken an imaginary recordset and set the string to parse from the text value in the recordset
strtoParse = rs!Text
strArray = Split(strtoParse, vbNewLine)

'a simple loop that will count the number of values in the array (1 dimensional)
For intcount = LBound(strArray) To UBound(strArray)
MsgBox Trim(strArray(intCount))
Next

Find a string in an array note the complete string needs to be found not a part within one of the array parts.
Private Function WhereInArray(vArrayName As Variant, vStringtoFind As String) As Variant
Dim i As Long
For i = LBound(vArrayName) To UBound(vArrayName)
If vArrayName(i) = vStringtoFind Then
WhereInArray = i
Exit Function
End If
Next i
'if you get here, vStringtoFind was not in the array. Set to null
WhereInArray = Null
End Function

Show whats in which Array cell – where n is a number equal to the value of a cell
So eg strArray(60) would show the value in 60th cell note arrays start from 0

MsgBox strArray(n)

Copy the HTML of a web page to a variable
my_StrVariable = ie.Document.body.innerHTML

Filter out much of the HTML formatting and only put inner text into a variable
my_StrVariable = ie.Document.body.innerText

Replace with spaces with less spaces till 2 and 3 spaces are replaced by 1 space
Function ThreeTwo(ByVal parmString As String) As String
Dim strTemp As String
strTemp = parmString

Do Until InStr(strTemp, " ") = 0
strTemp = Replace(strTemp, " ", " ")
Loop

Do Until InStr(strTemp, " ") = 0
strTemp = Replace(strTemp, " ", " ")
Loop
ThreeTwo = strTemp
End Function

Click on a button if all you know is its class name
Dim e
Set e = ie.Document.getElementsByClassName("button primary")(0)
e.Click

Identify sites and how often they are visited
labpix.online/rounduptheusualsuspects.org
Look at any site
http://labpix.online

Take a look at the robots.txt of a site
https://www.theregister.co.uk/robots.txt

https://www.ebay.co.uk/robots.txt

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 how to connect to PostGres.

To follow along you will either need PostGres installed on your local computer or alternatively all required connection parameters to a database in the cloud or on your lan.
PostGres Download

You will also need to Download and install a PostGreSQL ODBC driver – these are available at the following(March 2018).

PostGres ODBC Drivers

Scroll down the list – here I went to the bottom and obtained x64 version – MSI are downloaded (Microsoft Windows Installer files) – Install and then move to the next step.

Next create a blank database and right click in the white area to reveal a menu – select link tables.

You should now be presented with the Select Data Source dialog. Here I hit New…

This brings up the Create New Data Source dialog which lists database drivers scroll down through the list to PostgreSQL Unicode and select

Give your DSN an appropriate name and then go back to link table but this time rather than hitting the new button navigate to where you saved your DSN and select it and press OK.

The ever important parameters – you just need to know these – if you set up PostGres and you can get in through PG Admin selecting properties on the database should reveal the panel that will give you some guidelines. You may wish to double click on the image below so you can get a closer link of how I place my parameters in. I have opened the PGAdmin dialog here and placed it alongside the MS Access database window to show the properties I am transferring across.

Hitting OK should present you with the tables in your database.

And here is a demonstration with the link in place along with the table open and a simple form shown.

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

MS Access Function Collection that can be used to Generate Housing Forecast Figures

Apologies if you are coming here for the first time. This post is a somewhat dense domain specific holding post for some work I did at the weekend to pull together some thoughts.

I was thinking that if I had a list of all sites in the UK I should be able to generate a phasing for each of them based on maybe the area of the site. This would automatically create a general housing land audit for any site that I should put in. I thought I’d try and see if I could write a set of functions that might generate phasing by site. Given that you can get an estimate of the housing boundaries from open street map and that you can get their area it would be possible to get an estimated number of houses per site which could then be used to phase. Going further if you were ever to know planning permission dates you could more accurately use this as a date from which to start phasing.

Ensure you have three tables
T01Sites (PKID, TotalNoHouses, DecisionDate)
T02HousePhasing (PKID, SiteFKID, Year, Completions)
T03 (PKID, TotalNoHouses, DecisionDate, YearofDecision, YearofStart, YearSpread)

T02 is the phasing child table of T01 and T03 is a holding table for a make table that will hold a randomised spread over which you wish to phase the total no of houses. It also randomly predicts when the housing will start on site.

And three queries

Q01 – Make Table Query
SELECT T01Sites.PKID, T01Sites.SiteName, T01Sites.TotalNoHouses, T01Sites.DecisionDate, Year([DecisionDate]) AS YearofDecision, CalculateintYearStartFULLPP([YearofDecision]) AS YearofStart, intYearSpread([TotalNoHouses]) AS YearSpread INTO T03
FROM T01Sites;

Q02 – Select Query
SELECT T03.PKID, T03.SiteName, T03.TotalNoHouses, T03.DecisionDate, T03.YearofStart, T03.YearSpread, Int(T03!TotalNoHouses/T03!YearSpread) AS PerYearSpread, [TotalNoHouses] Mod [YearSpread] AS Remainder
FROM T03
WHERE ((([TotalNoHouses] Mod [YearSpread])=0));

Q03 – Select Query
SELECT T03.PKID, T03.SiteName, T03.TotalNoHouses, T03.DecisionDate, T03.YearofStart, T03.YearSpread, Int(T03!TotalNoHouses/T03!YearSpread) AS PerYearSpread, [TotalNoHouses] Mod [YearSpread] AS Remainder
FROM T03
WHERE ((([TotalNoHouses] Mod [YearSpread])>0));

VBA Function list
The first function randomises the spread in years over which construction might happen on an individual housing site based on the total number of houses on the site.
Public Function intYearSpread(TotalNoHouses As Integer) As Integer

If TotalNoHouses < 2 Then intYearSpread = 1 ElseIf TotalNoHouses = 2 Then intYearSpread = Int((TotalNoHouses) * Rnd) + 1 ElseIf TotalNoHouses > 2 And TotalNoHouses < 9 Then intYearSpread = Int((TotalNoHouses - 2 + 1) * Rnd + 1) ElseIf TotalNoHouses >= 9 And TotalNoHouses <= 40 Then intYearSpread = Int((4) * Rnd + 1) ElseIf TotalNoHouses >= 41 And TotalNoHouses <= 80 Then intYearSpread = Int((8 - 4 + 1) * Rnd + 4) ElseIf TotalNoHouses >= 81 And TotalNoHouses <= 200 Then intYearSpread = Int((8 - 4 + 1) * Rnd + 4) ElseIf TotalNoHouses >= 201 And TotalNoHouses <= 400 Then intYearSpread = Int((10 - 6 + 1) * Rnd + 6) ElseIf TotalNoHouses >= 401 And TotalNoHouses <= 800 Then intYearSpread = Int((12 - 8 + 1) * Rnd + 8) Else intYearSpread = Int((20 - 10 + 1) * Rnd + 10) End If 'MsgBox intYearSpread End Function

The first of the next three functions is used in the query to identify a year from which phasing on site will start. I wrote two further functions with the thought that in the future I could create a switch that would allow alternative site starts depending on whether a site has planning permission and depending on the type of planning permission. For example full planning permission would mean starting within three years of the granting of planning permission whereas outline would push it to between 3 and 6 years. A site with an LDP allocation would start further into the future.

Public Function CalculateintYearStartFULLPP(intDecisionDateYear As Integer) As Integer

CalculateintYearStartFULLPP = intDecisionDateYear + (Int((3 - 1 + 1) * Rnd + 1))

End Function

'Not used at present
Public Function CalculateintYearStartPPPP(intDecisionDateYear As Integer) As Integer

CalculateintYearStartPPPP = intDecisionDateYear + (Int((6 - 3 + 1) * Rnd + 3))

End Function

'Not used at present
Public Function CalculateintYearStartLDP(intDecisionDateYear As Integer) As Integer

CalculateintYearStartLDP = intDecisionDateYear + (Int((20 - 8 + 1) * Rnd + 8))

MsgBox CalculateintYearStartLDP

End Function

Function to create phasing IF housing IS perfectly divisible by Year Spread
GRH is an acronym for Generate Randomised Housing
Public Function GRHZero() As Variant

Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsPhasing As DAO.Recordset
Dim intrsSourcePKID As Integer
Dim intrsSourceYearofStart As Integer
Dim intYearSpread As Integer
Dim intPerYearSpread As Integer
Dim i As Integer

Set db = CurrentDb()
Set rsSource = db.OpenRecordset("Q02")
Set rsPhasing = db.OpenRecordset("T02HousePhasing")

If Not (rsSource.EOF And rsSource.BOF) Then
'There are no records if End of File and Beginning of File are both true

rsSource.MoveFirst

Do Until rsSource.EOF = True

intrsSourcePKID = rsSource!PKID
intrsSourceYearofStart = rsSource!YearofStart
intYearSpread = rsSource!YearSpread
intPerYearSpread = rsSource!PerYearSpread

For i = 1 To intYearSpread

With rsPhasing
rsPhasing.AddNew
rsPhasing!SiteFKID = intrsSourcePKID
rsPhasing!Year = intrsSourceYearofStart
rsPhasing!Completions = intPerYearSpread
rsPhasing.Update
intrsSourceYearofStart = intrsSourceYearofStart + 1
End With

Next i

rsSource.MoveNext

Loop
Else
MsgBox "No Records"
Exit Function
End If

rsPhasing.Close
rsSource.Close

Set rsPhasing = Nothing
Set rsSource = Nothing

Set db = Nothing

End Function

Function to create phasing IF housing IS NOT perfectly divisible by Year Spread and a remainder is put on end
Public Function GRHRemainder() As Variant

Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsPhasing As DAO.Recordset
Dim intrsSourcePKID As Integer
Dim intrsSourceYearofStart As Integer
Dim intYearSpread As Integer
Dim intPerYearSpread As Integer
Dim intRemainder As Integer
Dim i As Integer

Set db = CurrentDb()
Set rsSource = db.OpenRecordset("Q03")
Set rsPhasing = db.OpenRecordset("T02HousePhasing")

If Not (rsSource.EOF And rsSource.BOF) Then
'There are no records if End of File and Beginning of File are both true

rsSource.MoveFirst

Do Until rsSource.EOF = True

intrsSourcePKID = rsSource!PKID
intrsSourceYearofStart = rsSource!YearofStart
intYearSpread = rsSource!YearSpread
intPerYearSpread = rsSource!PerYearSpread
intRemainder = rsSource!Remainder

For i = 1 To intYearSpread

With rsPhasing
rsPhasing.AddNew
rsPhasing!SiteFKID = intrsSourcePKID
rsPhasing!Year = intrsSourceYearofStart
rsPhasing!Completions = intPerYearSpread
rsPhasing.Update
intrsSourceYearofStart = intrsSourceYearofStart + 1
End With

Next i

With rsPhasing
rsPhasing.AddNew
rsPhasing!SiteFKID = intrsSourcePKID
rsPhasing!Year = intrsSourceYearofStart
rsPhasing!Completions = intRemainder
rsPhasing.Update
intrsSourceYearofStart = intrsSourceYearofStart + 1
End With

rsSource.MoveNext

Loop
Else
MsgBox "No Records"
Exit Function
End If

rsPhasing.Close
rsSource.Close

Set rsPhasing = Nothing
Set rsSource = Nothing

Set db = Nothing

End Function

And the Script to run both the above functions
Public Function GeneratePhasingRecords()

Call GRHZero
Call GRHRemainder

MsgBox "Finished"

End Function

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access Function Collection that can be used to Generate Housing Forecast Figures

AHK – Useful AutoHotKeyScripts for Outlook specifically

Another set of very useful scripts mainly dealing with creating hotkeys for common Outlook tasks.

Note that if you are on a laptop you may have pre-mappings for the function keys in which case you will need to quite possibly go for alternatives.

F8::
Run C:\Program Files (x86)\Microsoft Office\Office14\OUTLOOK.EXE /c ipm.note
return

F9:: ;Inbox
keystroke = ^1
parameters =
Gosub open
Return

F10:: ;Calender
keystroke = ^2
parameters = "outlook:calendar"
Gosub open
Return

F11:: ;Contacts
keystroke = ^3
parameters = outlook:contacts
Gosub open
Return

F12::
PROCESS, EXIST, OUTLOOK.EXE
PID := ERRORLEVEL
IF ERRORLEVEL <> 0
{
LOOP,
{
WINSHOW, AHK_CLASS rctrl_renwnd32
WINACTIVATE, AHK_CLASS rctrl_renwnd32
WINWAITACTIVE, AHK_PID %PID% AHK_CLASS rctrl_renwnd32
WINGETACTIVETITLE, TITLE
WINCLOSE, %TITLE%
WINWAIT,, Are you sure you want to permanently delete all the items and subfolders in the "Deleted Items" folder?,3
CONTROLSEND, , {ENTER}, AHK_CLASS #32770, Are you sure you want to permanently delete all the items and subfolders in the "Deleted Items" folder?
IF A_INDEX > 30
PROCESS, CLOSE, OUTLOOK.EXE

PROCESS, EXIST, OUTLOOK.EXE
IF ERRORLEVEL = 0
BREAK
}
}
return

DetectHiddenWindows, On
Process, Exist, outlook.exe
If !ErrorLevel
Run outlook.exe
Return

open:
Process, Exist, outlook.exe
If (ErrorLevel != 0)
{
WinActivate ahk_class rctrl_renwnd32
WinWaitActive ahk_class rctrl_renwnd32
Send %keystroke%
}
else
Run outlook.exe %parameters%
Return

Posted in All, AutoHotKey, E-mail, Personal Development, Productivity | Comments Off on AHK – Useful AutoHotKeyScripts for Outlook specifically

AHK – Useful AutoHotKeyScripts

F2::
; Close all windows (open/minimized, browsers) but not pwr off
WinGet, id, list,,, Program Manager
Loop, %id%
{
this_id := id%A_Index%
WinActivate, ahk_id %this_id%
WinGetClass, this_class, ahk_id %this_id%
WinGetTitle, this_title, ahk_id %this_id%
If(This_class != "Shell_traywnd") && (This_class != "Button") ; If class is not Shell_traywnd and not Button
WinClose, ahk_id %this_id% ;This is what it should be ;MsgBox, This ahk_id %this_id% ; Easier to test ;)
}
Return

If you don’t know or can’t find the executable for the program you wish to AHK to you can place a link on the desktop or somewhere else and trigger the link using a mapped key as follows;

F3::
;Open QGIS
path = "C:\Users\brooks.mark\Desktop\QGIS Desktop 2.14.8.lnk"
;MsgBox, %path%

Run, %path%
Return

Open Chrome using CTRL Z – (^ is the sign for CTRL)

^z::
Run, C:\Program Files (x86)\Google\Chrome\Application\chrome.exe
Return

Here I have a key to open a work Uniform program – line send types into the username field usernamevariable – alter to your actual value
F6::
path = "C:\Users\brooks.mark\Desktop\Uniform LIVE.lnk"
;MsgBox, %path%
Run, %path%

Sleep, 5000

ControlFocus, Edit4,Uniform Spatial - LIVE Database
Send, usernamevariable

Return

Posted in All, AutoHotKey, Chrome, Productivity | Comments Off on AHK – Useful AutoHotKeyScripts

Navigate to Website directly from MS Access using Internet Explorer

One of the major suppliers of planning software to the United Kingdom is a company called Idox Group plc. They produce probably the most popular back office software that runs all aspects of administering planning permission. As such their public access web pages usually have the same fundamental structure. What if rather than holding information about a planning application you would like to create a command button that would take the user directly to the planning application details.

Unfortunately the url bears no relation to the planning application reference so it is necessary to go to a search page enter the planning application number and then trigger search which if you have a completely accurate planning reference will take you to the individual record. Here’s a function for City of Edinburgh Council complete with relevant application number. Previously I had achieved this using AHK but here is an elegant solution using VBA code. As ever there are multiple ways to achieve the same thing.

Public Function GotoWebPage()

Dim ie As Object
Dim strapplication As String

strapplication = "18/00488/TCO"
Set ie = CreateObject("Internetexplorer.application")

ie.Visible = True

ie.navigate "https://citydev-portal.edinburgh.gov.uk/idoxpa-web/search.do?action=simple&searchType=Application"

While ie.busy
DoEvents
Wend

ie.Document.getElementbyID("simpleSearchString").Value = strapplication

ie.Document.Forms(0).submit

End Function

And here’s one for City of London Council
Public Function GotoWebPageCityLondon()

Dim ie As Object
Dim strapplication As String

strapplication = "18/00152/FULEIA"
Set ie = CreateObject("Internetexplorer.application")

ie.Visible = True

ie.navigate "http://www.planning2.cityoflondon.gov.uk/online-applications/search.do?action=simple&searchType=Application"

While ie.busy
DoEvents
Wend

ie.Document.getElementbyID("simpleSearchString").Value = strapplication

ie.Document.Forms(0).submit

End Function

Further useful reading
How to Navigate IE using VBA

Posted in MS Access, VBA Code MS Access | Comments Off on Navigate to Website directly from MS Access using Internet Explorer

Add Open Street Map to Background QGIS Project and then Digitise against imported Raster

The following is a workflow that can be used to get a raster basemap of anything into QGIS which you then reference to Open Street Map Layers ready for digitising against. This will be useful for approximate digitising of masterplans and approsimate digitisation of housing completions.

Firstly ensure you have dowloaded QGIS and added the following two plugins
OpenLayers Plugin

Georeferencer GDAL
Plugin

Opening QGIS now lets add the the Open Street Map Raster

From a blank project selection of Open Street Map should give you the following result

Now zoom to the approximate location where you wish to have a unique basemap. You will be referencing points on this map to points on your imported raster so you should zoom into a location to the extent that you can identify common locations between the two maps.

Identify the basemap you wish to have in your particular QGIS map here I choose freely available masterplan from Calderwood development in West Lothian from planning application 0524/P/09

Within the menus navigate to
Raster / Georeferencer /

You should be presented with the following window.

Hit the add raster button in the top left

Select the basemap you wish to add to your project and ensure that the coordinate system that you choose is OSGB 1936 / British National Grid

Next you want to add reference points to the basemap that will allow for you to put the basemap against it – This is done using the button marked

Next hit the settings button

You should now be presented with the Transformation parameters windows dialog as follows.
The dialog will remember old parameters if not ensure that you have the same selections (with your own selection of output raster location) as mine.

Now hit the play button the raster will be added to your map and the georeferencer will be reduced and moved to the bottom left of the corner where you will be open it and reduce it in size if you wish. You can now go in and alter the transparency so that it is possible to see both Open Street Map and your newly added raster

You should now be presented with something like the following – if there are red dots on the screen this is because you have not closed georeferencer down – simply open the window up again and hit file close.

Posted in All, QGIS General | Comments Off on Add Open Street Map to Background QGIS Project and then Digitise against imported Raster

Links to GIS information for test system design

If you are trying to design software that includes a Geographical element it is easier if you are working with data that makes some kind of sense.

The following are a list of sites where you can get good and consistent information on Local Authority Geographical Datasets within Scotland and in London. There has been an improvement in the quality and extent of information available but open data still remains patchy. Fortunately some datasets are available. The interesting thing about this data is that although it is rich it is largely unstructured and without relationships. Fortunately if there are geographical attributes then these can be used to spatialiy analyse the information and create relationships from which you can start to construct better systems.

I understand why the data is patchy. To really publish well it is a necessity to get your systems working well so that the export (and publication) of data can be at least semi-automated. Without this it is simply too onerous for staff to repeatedly perform Extraction Transformation and Load procedures on ever larger numbers of datasets. Taking a step back however therein may lie the benefit. The quicker they can learn to cleanly optimise and export and hopefully automate these procedures the more likely they are to have their systems working properly and importantly the more investigation and experimentation they can put into linking their datasets. The skills to link these datasets constantly to a web data portal being similar to the skills required to link between systems.

It might be expected therefore that better availability of open data is reflective of better internal systems.

Here is the information that I was able to identify through Google Searches at February 2018.

Aberdeenshire Open Data

Angus Council Open Data Portal

Argyll and Bute Open Data Portal

Dundee City Open Data Portal

Edinburgh Open Data Portal

Moray Council Open Data Portal

North Ayrshire Open Data Portal

North Lanarkshire Open Data Portal

Perth and Kinross Open Data Portal

South Ayrshire Open Data Portal

and scheduled ancient monument information can be obtained here.

Historic Environment Scotland

Here is London

London

Posted in All, Geographical Information Systems, GIS | Comments Off on Links to GIS information for test system design

MS Access 2010 – Simple Function to loop through a list and Print to file an individual PDF

Had to move to MS Access 2010 to do this as no facility for direct to PDF print in MS Access 2003

Where the fields in the QUERY-ListofIDtoPrint includes ID / Field02 / Field03 / Field04

Public Function LoopandPrint()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim mypath As String

mypath = "C:\Data\EXPORTdirectory\"

Set db = CurrentDb()
Set rs = db.OpenRecordset("QUERY-listofIDtoPrint")

Do While Not rs.EOF

MyFileName = rs!ID & "-" & rs!Field02 & "-" & rs!Field03 & "-" & rs!Filed04 & ".pdf"

'MsgBox MyFileName

DoCmd.OpenReport "R001TargetReport", acViewPreview, , "[PlanAppID]=" & rs!ID
DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
DoCmd.Close acReport, "R001TargetReport"

rs.MoveNext
Loop

Set rs = Nothing
Set db = Nothing

End Function

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access 2010 – Simple Function to loop through a list and Print to file an individual PDF

SQL SERVER – Create TSQL Trigger to add date to existing record after INSERT or UPDATE

I came across an interesting issue with a web application I have been trying to create. DEFAULTS in SQL Server didn’t seem to be registering when new records were inserted through the web application.

How come? Surely the web application is not bypassing requirements set out by the database.

What I believe is happening is that the web application is explicitly supplying a null value to the dateupdated field and because of this the default value is NOT applied by SQL Server. You are therefore in the strange position of creating a trigger on a column after insert. Normally you would never really do this as that’s specifically what default is for.

Here’s code to create the trigger on the SQL Server that will do just this.

CREATE TRIGGER TR_moddate
On T0001PersonData
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE T0001PersonData SET DATEUPDATED=GETDATE()
WHERE PKID IN (SELECT PKID FROM INSERTED)
END

Some explanation – This updates the field DateUpdated within the table T0001PersonData after an update or an insert. If you have default on a column this may mean that it updates to default then to null then to trigger so you may wish to remove the default on the column. Alternatively you may wish to get the front end to update to the current date. I guess this could have performance issues at scale but my application is pretty small beer so I am happy with this at the moment. I think I prefer to have it at database level as well. It should be noted that INSERTED is actually a ALIAS table created by SQL Server and held in memory that allows for reference to pull out items before they are registered in the database. Leaving out the where clause may have resulted in the application updating the dateupdated column for all records anytime an update or insert was performed. BE WARNED

Posted in All, Database Design, SQL Azure, SQL Server, TSQL scripts | Comments Off on SQL SERVER – Create TSQL Trigger to add date to existing record after INSERT or UPDATE

MS Access – VBA Functions – Create Category Tag Junction Table by comparing a text field against a table of categories

Going forward there are more and more systems that have somewhat un-formated text or memo fields. It can be useful to tag fields. Here’s a collection of 2 functions with a script to pull them together designed to create a junction table.

What’s nice about it is that it could be used in lots of situations as a nightly process to tag manually input notes to help assist users navigate screeds of text.

This code is generalized and would need to be adapted for your specific table and field names

In mine you will need 4 tables
T001TableContainingFieldtobeCatetgorized – as per title it has a field called PKID and a field Called Text which is the memo field against which the SQL compares categories
T002Category – table that contains the categories that are compared against the text field
T003JunctionTable – the junction table that will contain the links between our notes table and the category table.
T004SQL – table to contain update queries – the field storing the strings is SQLstring – RunQueriesFromTable uses the SQLstring query and places the result in T003JunctionTable

The function RunQueriesFromTable is a previous function I wrote

Function CategorizeField()

'Make sure the name of the recordset is unambiguous
'Good practice to reference the actual library

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim SQLUpJunc As String
strQuote = Chr$(34)

Set db = CurrentDb
Set rs1 = db.OpenRecordset("SELECT * FROM T001TableContainingFieldtobeCategorized")
Set rs2 = db.OpenRecordset("T004SQL")
Set rs3 = db.OpenRecordset("T002Category")

'the data source can be a Table Name a query name or an sql string
'it would be possible to change the SQL to set to another set of records
'Check to see if there are any records in the set

If Not (rs3.EOF And rs3.BOF) Then
'there are no records if End of File and beginning of file are both true

rs3.MoveFirst

Do Until rs3.EOF = True

SQLUpJunc = "INSERT INTO T003JunctionTable ( FKIDT001, FKIDT002 ) SELECT T001TableContainingFieldtobeCategorized.PKID, " & rs3!PKID & " AS FKIDT002 FROM T001TableContainingFieldtobeCategorized WHERE (((T001TableContainingFieldtobeCategorized.Text) Like " & strQuote & "*" & rs3!Category & "*" & strQuote & "));"

With rs2
.AddNew
rs2!SQLstring = SQLUpJunc
rs2.Update
End With

rs3.MoveNext
Loop
Else
'MsgBox "No Records available for updating exit sub"
Exit Function
End If
'MsgBox "Looped through the records and updated table of SQL"

rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set rs3 = Nothing
Set db = Nothing

'remember to break an infinite loop press ctrl + break

End Function

Public Function RunQueriesFromTable(SQLSource As String)

DoCmd.SetWarnings False

Dim StartTime As Date
Dim EndTime As Date
Dim rstZ As DAO.Recordset
Dim strSQL As String

StartTime = Now()

Set rstZ = CurrentDb.OpenRecordset(SQLSource)

Do Until rstZ.EOF

strSQL = rstZ!SQLstring
DoCmd.RunSQL strSQL
rstZ.MoveNext

Loop

DoCmd.SetWarnings True

EndTime = Now()

'MsgBox "Finished ALL SQL queries! Process started at " & StartTime & " and finished at " & EndTime

End Function

Public Function CreateJunctionTable()

Call CategorizeField
Call RunQueriesFromTable("T004SQL")

MsgBox "Finished"

End Function

Posted in All, Database Design, VBA Code MS Access | Comments Off on MS Access – VBA Functions – Create Category Tag Junction Table by comparing a text field against a table of categories

MS Access – VBA – Open Form and go to record from unbound List Box

Very clean and simple

Private Sub List0_Click()

Dim stFormName As String
Dim stLinkCriteria As String

stFormName = "Form1"

stLinkCriteria = "[ID]=" & Me.List0
DoCmd.OpenForm stFormName, , , stLinkCriteria

End Sub

Posted in All, Database Design, VBA Code MS Access | Comments Off on MS Access – VBA – Open Form and go to record from unbound List Box

Simple Bat File to open multiple Web Pages in One Browser Window with alternate Tabs

Simple but can be useful

Useful if you are wanting to open multiple tabs in a browser window at the same time

@echo off
start "Simple Search" "https://planning.westlothian.gov.uk/publicaccess/search.do?action=simple&searchType=Application"
start "Google Maps " "https://www.google.co.uk/maps/@55.8625775,-3.6759593,17z"
start "WLC Spade" http://gis.westlothian.gov.uk/wml/spade/"
start "idoxEDRMS Login" "http://cc-dmsapp-01:8080/IDOXSoftware/secure/IG_Main?url="

Posted in All, Configuration, Dev Ops, General Opinion | Comments Off on Simple Bat File to open multiple Web Pages in One Browser Window with alternate Tabs

Pivoting Relationships from Many to Many – One to Many – One to One – and then Key Value – Relationships and Schemas

There are a whole host of applications in which you may wish to record the biological or legal relationships between individuals. Here is a short investigation of some of the subtleties related to the options you have to model this data.

The actual biological parent to biological child relationship is somewhat more subtle than the classic description in books would have you believe (usually these equate the parent child relationship as a one to many relationship.

Strictly speaking biological parent to biological child is a many to many relationship (which in MS Access is a recursion of 2 one to many relationships) but it is a specific version of the many to many relationship. One parent can have many (read infinite) children but one child can only ever have 2 parents – one biological mother linked to one biological father (ignoring latest developments in science). Of course a one to two relationship is still a type of of one to many relationship it is a more specific type of one to many relationship but one to many nonetheless – so to model all relationships you still need two one to many links. If you consider the unique combination of mother and father as a group in itself then you can model all relationships again as only consisting of one group of one to many relationships rather than as two one to many relationships (which we presently have defined as one one to many and one one to two relationship). To do this you would have to make a mother and father a unique pair who can have infinite number of children but a child can ONLY have one mother father pair.

Many to many relationship

To a certain extent this looks like a one to many relationship as there appears to be only two tables (albeit one being aliased). This is correct but to record a dependents relationship with both parents two separate records with separate PKIDs need to be input into the persons junction table actually making it a many to many relationship.

By viewing the parents PKIDs as a group we can convert this many to many relationship back to a one to many relationship by adding a further field into the persons junction table. Now the relationship between a dependent and its mother and father can be recorded in one record in the personsjunctiontable.

It is not initially clear but a one to many relationship where the many is a finite number (ie not infinite – in our example 2) – can be re-modelled as a one to one relationship as follows.

In fact one thing that I learnt when I started reading about relational databases is that relational not only relates to the relationship between tables but the relationship within tables.

Thus we can separate a table of columns back out into a one to many relationship by pivoting out the column names and making them a value within a field themselves linking them to a field and then adding the ability to add a value. This works because the number of columns is finite. I believe this is the thinking behind a key value database.

Looking at the tables resulting from data input you would get

What makes this particularly good is that a lot of attributes can be stored against an individual and if you forget a column name you simply add it to the field name table and it becomes an additional option in the KeyValueData table. This can be particularly good if you are not sure of the set of column names that your users may wish to use. It has the disadvantage that validation will become harder as the values are often a mix of value types and cannot be so easily tied back to a particular list as the source of the field will change according to the value of its relative field name. Here for example I could easily add Mother and Father to the table of field names but a user would simply type these in rather than having them validated as per the first three relationship structures.

Posted in All, General Opinion, MS Access, Normalisation | Comments Off on Pivoting Relationships from Many to Many – One to Many – One to One – and then Key Value – Relationships and Schemas

SQL Azure – SQL to Select Distinct list of Child Records based on a Maximum or Minimum Child Field Value

Following on from the previous post I wanted to know the syntax for SQL to do the same but in SQL Azure.

SELECT * FROM dbo.T02AttendanceGame T1
WHERE Attendance = (
SELECT max(Attendance)
FROM dbo.T02AttendanceGame T2
WHERE T1.FKID=T2.FKID
);

I created the same tables that I created for the MS Access example with the same field names but within a SQL Azure database.

Here’s the same SQL but then creating a view called rather unimaginatively ‘View01’

CREATE VIEW VIEW01 AS SELECT * FROM dbo.T02AttendanceGame T1
WHERE Attendance = (
SELECT max(Attendance)
FROM dbo.T02AttendanceGame T2
WHERE T1.FKID=T2.FKID
);

Posted in All, SQL Azure | Comments Off on SQL Azure – SQL to Select Distinct list of Child Records based on a Maximum or Minimum Child Field Value

MS Access – SQL to Select Distinct list of Child Records based on a Maximum or Minimum Child Field Value

So we have a table of Stadiums and a table of attendances. We would like to create a query that shows an individual child record for each stadium of the highest attendances. Quite often you seek the latest or earliest child record by grouping on the primary key of the child record but what happens if you needing to identify a child not on the latest or earliest but on a value that does not correlate with the order in which the records have been created. In such a case the primary key can no longer act as a proxy for minimum or maximum value of the required field.

To demonstrate the problem and to illustrate it I create two tables

T01Stadium with the fields
PKID
Location

and

T02AttendanceGame with the fields
PKID
FKID
Attendance
GameDate
Date
Weather

and these are the example values I entered.

and here’s the code that shows the maximum attendance in the child records
SELECT *
FROM T02AttendanceGame AS G1 INNER JOIN [SELECT FKID, MAX(Attendance) AS HAttend FROM T02AttendanceGame GROUP BY FKID]. AS G2 ON (G1.Attendance=G2.HAttend) AND (G1.FKID=G2.FKID)
ORDER BY G1.FKID;

and here’s similar code that shows the latest games played at the two stadia
SELECT *
FROM T02AttendanceGame AS G1 INNER JOIN [SELECT FKID, MAX(GameDate) AS LatestDate FROM T02AttendanceGame GROUP BY FKID]. AS G2 ON (G1.GameDate=G2.LatestDate) AND (G1.FKID=G2.FKID)
ORDER BY G1.FKID;

This is yet another very valuable structure with which to reduce the complexity of data for users who are quickly seeking to find key values in a child table when that data is coming in out of synch with the order of data entry. Individuals wishing to use this code will need to enter the MS Access SQL editor and will need to be careful as the graphics designer cannot represent this syntax. I have tested this code with MS Access 2003 as the front end and SQL Azure as the backend and I can confirm that it does work. I have not extensively tested it.

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access – SQL to Select Distinct list of Child Records based on a Maximum or Minimum Child Field Value

Javascript – Nubuilder Specific to save Date to DateUpdated field on Record Change (not subform)

From the admin screen go to Develop / Forms / Form of choice / Custom Code / Javascript

To place focus on the search button

function nuLoadBrowse(){
$('#nuSearchButton').focus();
}

Function to Get System Date
function GetTodayDate() {
var tdate = new Date();
var dd = tdate.getDate(); //yields day
var MM = tdate.getMonth(); //yields month
var twoDigitMonth = ((tdate.getMonth().length+1) === 1)? (tdate.getMonth()+1) : '' + (tdate.getMonth()+1);
var yyyy = tdate.getFullYear(); //yields year
var currdate = dd + "-" + twoDigitMonth + "-" + yyyy;

return currdate;
}

Function to update field called “DateUpdated” on edit- to be used with the GetTodayDate() function
function nuOnSave() {
if (nuFORM.edited == true)
{
$( "#DateUpdated" ).val( GetTodayDate() );
}
return true;

}

Posted in All, Javascript, nuBuilderPro | Comments Off on Javascript – Nubuilder Specific to save Date to DateUpdated field on Record Change (not subform)