Category: Geographical Information Systems

QGIS and PostGIS : Identifying direction of a vector

If using the dijkstra function with direction turned on it is important to identify the order in which the nodes of a vector line have been digitised. This is called the direction, dijkstra can use this with a reverse_cost attribute to handicap wrong movement along lines to such an extent that the correct path can be calculated around things like roundabouts.

Here is an example of the roundabout in Straiton in Edinburgh just North of the A720 bypass. While some of the lines have a correct anti clockwise orienation clearly some have been incorrectly digitised.

First we can see this by displaying the network in QGIS but using the styling to arrow the direction.

The function that can be used to reverse such inaccuracies if you can’t resort to buying a correct dataset try ST_REVERSE

013 Postgres command line : psql : Using ST_Within function to build junction tables to compare 2 separate polygon tables

First off let us create a new database to hold our examples in.

CREATE DATABASE stwithindb;

Now add the postgis extension.

Lets create two tables one called fields and one called plots

CREATE TABLE
t00001fields
(
pkid serial primary key,
fieldname varchar(50),
geom geometry(polygon,27700)
)
;
CREATE TABLE
t00002Plots
(
pkid serial primary key,
plotname varchar(50),
geom geometry(polygon,27700)
)
;

Now lets go to QGIS connect to the PostGIS instance add the tables and create some test data manually.

Here I have added fields in green with bold number labels and plots in brown with smaller number labelling. The numbers represent the pkid fields.

Now here I can quickly run a query to identify the plots that are in fields

SELECT t00002plots.pkid
FROM
t00002plots,
t00001fields
WHERE 
ST_WITHIN(PUBLIC.T00002PLOTS.GEOM, PUBLIC.T00001FIELDS.GEOM);

And it correctly identifies that plot 1 is within the fields layer.

But what would be great in an application is to have some kind of junction table that individual master records could display their children on. For this we need a junction table that links between the field and plots table showing the pkids from each.

SELECT t00002plots.pkid as Plotspkid,t00001fields.pkid as Fieldspkid
FROM
t00002plots,
t00001fields
WHERE 
ST_WITHIN(PUBLIC.T00002PLOTS.GEOM, PUBLIC.T00001FIELDS.GEOM);

Now I will move plot 2 into field 3 and rerun the above.

The layer now looks like

and running the former query we get.

Now its possible to either create a junction table to hold this information..

eg

CREATE TABLE t00010fieldplotjunction AS 
SELECT t00002plots.pkid as Plotspkid,t00001fields.pkid as Fieldspkid
FROM
t00002plots,
t00001fields
WHERE 
ST_WITHIN(PUBLIC.T00002PLOTS.GEOM, PUBLIC.T00001FIELDS.GEOM);

or we can create a view that will constantly calculate this everytime it is seen

CREATE VIEW v001FieldPlotJunction AS
SELECT t00002plots.pkid as Plotspkid,t00001fields.pkid as Fieldspkid
FROM
t00002plots,
t00001fields
WHERE 
ST_WITHIN(PUBLIC.T00002PLOTS.GEOM, PUBLIC.T00001FIELDS.GEOM);

Now if I add a few more plots and fields and then pull up the view we shall see that everything has been adjusted

and running the view we now get

In some circumstances this calculation may be expensive so we may wish to run and create a junction table overnight other times we may be happy to do it fully dynamically. Of course in a front end you could query and filter such that only one record was compared against the fields plot at anytime. Very useful nonetheless.

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

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

Creation of SITE History from Planning Application Polygons using QGIS

In planning it is important to know the planning history on a site. The status and likelihood of approved permission will often relate to previous permissions. Many council planning systems do not specifically relate planning applications to each other and there may be situations where you would like to create such links. This is essentially an excercise in using spatial analysis to create the junction table to hold what are many to many relationships.

If your datasets are in any way large you will need to set aside a computer so that it can perform the calculations. When I first tried this the process took a weekend with queries running overnight.

Start by obtaining as many years of planning application polygons as you can. Here I use polygon files in shape format.

The polygon file or shape file should be in one file so if you need to merge the shape files you have together. I did this and the file I ended up with was

AllPlanningApplications.shp

Next – Delete all attribute fields EXCEPT the planning application number.

Next – Create a centroids file from AllPlanningApplications.shp I called mine
AllPlanningApplicationsCentroids.shp

The next series of iterations are about getting a unique set of polygons with which we can go forward and generate a set of SITEPKIDS that can be attached to the child records.

Step – Using AllPlanningApplications.shp ADD an additional field called area and populate it using QGIS $area calculation – save this file.

Step – this is where it becomes interesting – in most authorities there are a vast number of planning application boundaries that overlap. Performing a dissolve at this point would result in a large congealed set of polygons that could not clearly identify unique sites. Thus buffering the polygons down we can start to identify unique sites. This is particularly important where boundaries are completely contiguous to each other.

sites the buffering command is used within the geometry tools to try to separate adjacent overlapping and contiguous polygons.

Step ‐ Create two files from the AllPlanningApplications.shp one for polygons less than 4500 metres squared and one for more than or equal to 4500 metres squared. This is to allow for two differing buffering processing to be performed on each.

AllSmallLessthan4500PlanningApplications.shp

AllLargeGreaterthanequal4500PlanningApplications.shp

Now the 4500 is an empirical figure that was subjectively chosen there may be a better figure feel free to investigate.

The following 2 steps also introduce empirical figures for the buffering that can be altered as appropriate.

Step ‐ Take the file AllSmallLessthan4500PlanningApplications.shp and create a buffer polygon file of this with

boundaries of less than 2m lets call it

AllSmallLessthan4500PlanningApplicationsBufferMinus2.shp

Step ‐ Take the file AllLargeGreatethanequal4500PlanningApplications.shp and create a buffer polygon file with

boundaries of less than 20m lets call it

AllLargeGreaterthanequal4500PlanningApplicationsMinus20.shp

THIS NEXT STEP WILL TAKE SEVERAL HOURS IT MAY BE BEST TO DO EACH ONE OVERNIGHT

Step ‐ Perform dissolves on both of these new files ensuring that dissolve all is used names could be something like

Vector / Geoprocessing Tools / Dissolve /

Set input layer alternatively to the two above files and set Dissolve field to dissolve all.

Suggested file Names are

MultipartDissolvedPolygonLessthan4500PlanningApplicationsBufferMinus2.shp

MultipartDissolvedPolygonAllLargeGreaterthanequal4500PlanningApplicationsMinus20.shp

Step You should now have two shape files of a large multipart polygon you want to perform the multipart to single part operation now

Vector / Geometry Tools / Multipart to Single Part

Processing involved with this is typically quick and suggested names for these files are

DistinctPolygonsAllSmallLessthan4500PlanningApplicationsMinus2.shp

DistinctPolygonsAllLargeGreatethanEqual4500PlanningApplicationsMinus20.shp

Add area column and identify the largest polygon on the small files

Add area column and identify the smallest polygon are on the large files you may want to remember this.

Step ‐ perform merge on these two files to get

Vector / Data Management Tools / Merge

CombinedSmallandLargeDistinctPolygonsPlanningApplicationswithbuffering.shp

ONGOING investigation ‐ would Difference be better than dissolve on this and should the above files be put together before

Step ‐ perform dissolve

Vector / GeoprocessingTools / Dissolve

ensure that ‐‐Dissolve all‐‐ is selected

DissolvedCombinedSmallandLargeDistinctPolygonsPlanningApplicationswithbuffering.shp

Step now you want to split mutlipart to single

DistinctPolygonsAllPlanningApplications.shp

Step add field called SitePKID and populate it using $rownum command.

Step

Vector / Data Management Tools / Join Attributes by Location

Set Target Vector Layer as

AllPlanningApplicationsCentroids.shp

Set Join Vector Layer as

DistinctPolygonsAllPlanningApplications.shp

Ensure that Keep all records (including non‐matching target records are kept)

Output Shapefile suggestions

AllPlanningApplicationsCentroidswithSitePKID.shp

If there are centroids without Site PKIDs put them to the end and give them consecutive unique row numbers. The attribute file associated with AllPlanningApplicationsCentroidswithSitePKID.shp should now be a child table of the shape file DistinctPolygonsAllPlanningApplications.shp perform checks here to see if all centroids within a polygon defined by the distinct polygons have the same SitePKID and that it is matched by the SitePKID of the Parent shape file.

You should be able to do a join on the this file to get the PKID back into the very original file.

AllPlanningApplications.shp

Finally perform a dissolve on the corrected AllPlanningApplications.shp file but this time dissolve on the field

SitePKID

You can call this

DistinctCorrectedPolygonsAllPlanningApplications.shp

QED!!!!

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 2016 this was available for download from the following link

Lichfield Planning Applications

Open up QGIS and add Lichfield’s planning application shape file
qgisessen2142

Now scan along the top menu and go to Database

Select the sub menu DB Manager and then DB Manager

dbmanager

The following windows dialog should appear

dbmanagerdialog

Expand the area on the left named PostGIS – any PostGIS instances that you have created should be visisble here. Note you will have to have the PostGIS server running. Then highlight the actual instance that would like to import information into.

In this case I use the instance LocalPostGres

dbmanagerdialog

Choose the third icon from the left.
dbmanagerimportlayerfile

It should be noted that the window on the right may or may not show the correct connection to the database on the right.

importdialog

Name the table you wish to create and then hit OK – additional parameters are available.
There will be a delay before a confirmation of successful import happens – try to not issue commands during this time – once confirmation has been received go back into the PostGIS option and add the layer.

QGIS 2.8.1 Getting Shape Files into SQL Server 2008 Express R2

For digital mapping the shp extension is the equivalent of csv files – A significant amount of information is still held in shape files and even if it is not, nearly every GIS package can export to shape format. It’s therefore pretty vital that you understand how to get this format into your backends.

Turns out QGIS 2.8.1 comes with a very handy excecutable called ogr2ogr.exe
On my computer this was downloaded with my installation of QGIS 2.8.1 and placed in the the following directory

C:\Program Files\QGIS Wien\bin

It looks like this executable has been a part of the the download for sometime so if you are on a different version of QGIS I suspect the file will be on your machine but in a directory other that QGIS Wien – whatever your version is.

If in doubt a simple search on ogr2ogr should give you the location.

From the command prompt you need to either navigate to the location of ogr2ogr.exe or place the full path into the instruction. In the following I have navigated to the appropriate directory using change directory command at the prompt. I then input the following.

ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:server=MARK-LENOVO\SQLEXPRESS;database=Geodatabase;trusted_connection=yes" "C:\Users\Mark\Documents\shp\polygon\n_america.shp"

On return it will start to import the information creating a new table in your SQL Server instance within the database listed in your parameter string. It looks like it just names the table the same as the shape file, I suspect if that name already exists as a tablename in SQL Server that table will be overwritten with the new shape file. Also note that the import process can take a fair bit of time for large files so be patient. I tested it initially with a small import which it did relatively quickly, I then went and hit it with 500 thousand records and it took a little over 2 hours. Still good to know that it can cope.

Once you have imported the information into SQL you should perform some form of spatial indexing on the table.
I have noted that layers that have spatial indexing are drawn differently than non spatial indexed layers. Layers with spatial indexes are drawn in more rapidly all over the district much like a spray from a can. Non spatial indexed layers appear on screen slower as if painted from one side to the other.

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 been installed on your laptop AND secondly that you have included the postgis extension in each database that you wish to connect to. Without enabling the extension in the database you won’t be able to connect OR import shape files. Installation of PostGIS is often a default during the install of postgres but you can check whether this was completed correctly by using the Application Stack Builder, a small program that is installed with later versions of postgres.

I navigated to this on the win 8.1 machine I was using by using search.

Opening application stack builder you will be presented with the following.

ApplicationStackBuilder

Expand the spatial extensions tree to identify if you already have the PostGIS plugin installed – if not – select as appropriate the plugin and you will be prompted to install. You will need an internet connection for this. Above you can see that my plugin was already installed.

Next you need to add the PostGIS extension to each Postgres database you wish to link to from QGIS this is done through PG Admin.

This is something that both myself and a colleague got caught out by and it took me an hour of searching to find how to fix it.

Below I have a database called GISData which I have just created. You will note there is only one object within the expanded extensions tree. You will not be able to connect to a database that does not include PostGIS in its list of extensions
CreateExtension

Hi-light the database you want to spatially enable then go to Tools – Query Tool( Ctrl + E will do the same). In the above picture I’ve slightly jumped the gun. To add the extension to the database type.

CREATE EXTENSION postgis

Run the query by selecting the green right arrow
There will be a short delay and then upon refresh of the connection postgis should appear in the list of extensions.

CreateExtensionCreated

You can now close the Postgres administrator and return to QGIS where you should be able to setup the connection to the database.

Parameters should be similar to below and it is useful to test the connection prior to saving.

SettingupthePostGISconnection

Enabling Geospatial integration in applications.

Despite the fact that spatially enabled databases have been around pretty much everywhere for quite sometime there’s still a heck a lot of enterprise applications out there that are not using the feature even though their backends support it.

SQL server has had the facility since 2008 , Oracle has it as well although it is with the expensive Oracle 11g Enterprise edition.

So if its available why are so many applications not using it?

Well one of the reasons is that many of the applications which would benefit from introduction are central to organisations and were in existence long before the feature was available in backend databases. So why not introduce it as an update? Well the problem is a geospatial attribute is a form of primary key more accurate than the often completely arbitrary primary keys that most tables will take as their reference. Adding it is likely to require not just the addition of a geospatial attribute which will be a defacto primary but potentially adding a full table not as a child but as a parent to the previous parent records – the former parent records requiring the addition of foreign keys that relate to their parents.

As most of you know altering primary keys in tables is pretty much equivalent to transplant surgery for a database.
Totally wiping a primary key and starting with a different primary key that needs to then be captured for itself and related back to its children is if anything several orders of magnitude worse than that..

Doesn’t sound good does it.

This is another case where if you have paying clients or you have a purchased product that isn’t going to happen until there is an outside force from a competitor. But the benefits are legion. As it stands most information in geographical systems is flat files that has to be updated directly within either the web gis or a gis desktop. These programs are really terrible making an application fully geospatial by design allows the UI you to display the geographical information in the GIS – web or desktop and related information in forms which often have vastly improved searching / linking to other systems drop down boxes well pretty much everything.

Leave plenty of time for it but would be proper automation. Too many GIS systems are mirrored copies of a database that periodically have to be updated. This is not the long term optimum.

Introduction to Basic Printing QGIS 2.2

Creating maps that you can pass on to others is often a central and regular requirement if not in paper format then in a digital format that can be e-mailed or printed out. Here’s a quick reference for myself as much as anything else.

To get into the print composer you can create a completely new print composition or alternatively load an existing print composition – Generally the 5th icon in the main menu bar will take you there (should be a white landscape rectangle with a star will give you access to the print composer , demonstrated below;

The 6th icon can be used to get to an existing declared print composition.

PrintComposerDemonstrationEnvironment

Now in the first instance you are going to want to navigate around the map and ensure that the map you wish to produce has the correct extents. In the below image on a 2 screen image I show QGIS v2.2 open on the left screen and the print composer open in the right. To move the composition area around go to the map window within the main program and navigate accordingly. Then within the print composer window hit the command button titled

Set to map canvas extent

This will re-draw your composition with an interpreted boundary defined directly from you map window. You can enforce scale in the item properties. Similarly after changing layers you will need to ensure that you hit the above button again when you want the composition to reflect the layers within the map window.

QGIS – Free GREAT Digital Mapping Software

windglobe 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

VBA Function Collection for converting Eastings and Northings to Latitude and Longitude

Some years back we hired a young lad by the name of Iain Brodie on a temporary contract – The week before I had been at an ESRI conference which had extensively discussed Web Mapping and  a speaker had demonstrated showing points in Google Maps. It was clear to me that the Google Maps url would accept and zoom to coordinates if those coordinates passed to it were Longitude and Latitude. Where I work there are significant numbers of datasets that use old Ordnance Survey UK specific Eastings and Northings coordinate system. Ordnance Survey actually set out the mathematics of conversion to Lat and Long on this page even detailing coded functions albeit in Javascript.

http://www.movable-type.co.uk/scripts/latlong-gridref.html

I specifically wanted to dynamically convert using Visual Basic for applications (specifically from MS Access). When Iain arrived it was clear that he was useful with computers and so I tasked him with finding VBA code from the internet. Between us we managed to get it working and I still regularly use the function set today to give users of applications a map in Google Maps. It really is a very nice quick tool that gives users quick access to maps for – you bet zero cost. My favourite price. We originally had it working with Google Earth but I only use it with Google Maps now.

Function PHId(North1, N0, aFo, PHI0, n, bFo)
PHI1 = ((North1 - N0) / aFo) + PHI0
M = Marc(bFo, n, PHI0, PHI1)
PHI2 = ((North1 - N0 - M) / aFo) + PHI1
Do While Abs(North1 - N0 - M) > 0.00001
PHI2 = ((North1 - N0 - M) / aFo) + PHI1
M = Marc(bFo, n, PHI0, PHI2)
PHI1 = PHI2
Loop
PHId = PHI2
End Function

Function Marc(bFo, n, P1, P2)
Marc = bFo * (((1 + n + ((5 / 4) * (n ^ 2)) + ((5 / 4) * (n ^ 3))) * (P2 - P1)) - (((3 * n) + (3 * (n ^ 2)) + ((21 / 8) * (n ^ 3))) * (Sin(P2 - P1)) * (Cos(P2 + P1))) + ((((15 / 8) * (n ^ 2)) + ((15 / 8) * (n ^ 3))) * (Sin(2 * (P2 - P1))) * (Cos(2 * (P2 + P1)))) - (((35 / 24) * (n ^ 3)) * (Sin(3 * (P2 - P1))) * (Cos(3 * (P2 + P1)))))
End Function

Function lon(East1, North1)
a = 6377563.396
b = 6356256.91
F0 = 0.9996012717
E0 = 400000
N0 = -100000
PHI0 = 0.855211333
LAM0 = -0.034906585
aFo = a * F0
bFo = b * F0
e2 = (aFo ^ 2 - bFo ^ 2) / aFo ^ 2
n = (aFo - bFo) / (aFo + bFo)
InitPHI = PHId(North1, N0, aFo, PHI0, n, bFo)
nuPL = aFo / ((1 - (e2 * (Sin(InitPHI)) ^ 2)) ^ 0.5)
rhoPL = (nuPL * (1 - e2)) / (1 - (e2 * (Sin(InitPHI)) ^ 2))
eta2PL = (nuPL / rhoPL) - 1
M = Marc(bFo, n, PHI0, InitPHI)
Et = East1 - E0
X = ((Cos(InitPHI)) ^ -1) / nuPL
XI = (((Cos(InitPHI)) ^ -1) / (6 * nuPL ^ 3)) * ((nuPL / rhoPL) + (2 * ((Tan(InitPHI)) ^ 2)))
XII = (((Cos(InitPHI)) ^ -1) / (120 * nuPL ^ 5)) * (5 + (28 * ((Tan(InitPHI)) ^ 2)) + (24 * ((Tan(InitPHI)) ^ 4)))
XIIA = (((Cos(InitPHI)) ^ -1) / (5040 * nuPL ^ 7)) * (61 + (662 * ((Tan(InitPHI)) ^ 2)) + (1320 * ((Tan(InitPHI)) ^ 4)) + (720 * ((Tan(InitPHI)) ^ 6)))
lon = (LAM0 + (Et * X) - ((Et ^ 3) * XI) + ((Et ^ 5) * XII) - ((Et ^ 7) * XIIA))
End Function

Function lat(East1, North1)
a = 6377563.396
b = 6356256.91
F0 = 0.9996012717
E0 = 400000
N0 = -100000
PHI0 = 0.855211333
LAM0 = -0.034906585
aFo = a * F0
bFo = b * F0
e2 = (aFo ^ 2 - bFo ^ 2) / aFo ^ 2
n = (aFo - bFo) / (aFo + bFo)
InitPHI = PHId(North1, N0, aFo, PHI0, n, bFo)
nuPL = aFo / ((1 - (e2 * (Sin(InitPHI)) ^ 2)) ^ 0.5)
rhoPL = (nuPL * (1 - e2)) / (1 - (e2 * (Sin(InitPHI)) ^ 2))
eta2PL = (nuPL / rhoPL) - 1
M = Marc(bFo, n, PHI0, InitPHI)
Et = East1 - E0
VII = (Tan(InitPHI)) / (2 * nuPL * rhoPL)
VIII = ((Tan(InitPHI)) / (24 * rhoPL * nuPL ^ 3)) * (5 + (3 * ((Tan(InitPHI)) ^ 2)) + eta2PL - (9 * ((Tan(InitPHI)) ^ 2) * eta2PL))
IX = ((Tan(InitPHI)) / (720 * rhoPL * nuPL ^ 5)) * (61 + (90 * ((Tan(InitPHI)) ^ 2)) + (45 * ((Tan(InitPHI)) ^ 4)))
lat = (InitPHI - ((Et ^ 2) * VII) + ((Et ^ 4) * VIII) - ((Et ^ 6) * IX))
End Function

Function degrees(radians)
degrees = 180 * radians / 3.14159265358979
End Function

Function trunc(value)
If value > 0 Then
trunc = Int(value)
Else
trunc = Int(value + 1)
End If
End Function

And here is the code the onclick function of a button called Command01 and it pulls from a screen that has an eastings and northings field on it and which has a Sitename field.

Dim Llatitude As Double
Dim Llongitude As Double
Dim strSitename As String

Llatitude = degrees(lat([Eastings], [Northings]))
Llongitude = degrees(lon([Eastings], [Northings])) - 0.0015
strSitename = Me.Sitename
    
Dim strlatlong As String
strlatlong = Llatitude & ",+" & Llongitude

‘Here I have two options – the first places a marker on the map – as far as I can tell – the marker is only available within google with the side panel displayed as well. The second shows the map centered on the requested location but without any markers. Choose one

Command01.HyperlinkAddress = "https://maps.google.com/maps?q=" & strlatlong & "+(" & strSitename & ")&z=18&iwloc=near&hl=en&ll=" & strlatlong

Command01.HyperlinkAddress ="https://www.google.com/maps/@" & Llatitude & "," & Llongitude & ",18z?hl=en"

And for Developers wanting to get into more detail here is the url for more information on passing parameters to the google maps url.

Google Map URLs

VB Function for Zooming to location in Google Maps at 23 March 2014

mapGoogle recently slightly altered their URL to link to Google Maps. This code passes eastings and northings from fields into a function that calculates Longitude and Latitude and then passes those calculated coordinates to the google maps url for display

Note you will need to have implemented the functions that calculate lat and long for this to be useful.

Private Sub Command01_Click()

On Error GoTo Err_Command01_Click

Dim Llatitude As Double

Dim Llongitude As Double

Dim strSitename As String

Llatitude = degrees(lat([Eastings], [Northings]))

Llongitude = degrees(lon([Eastings], [Northings])) - 0.0015

strSitename = Me.Sitename

Dim strlatlong As String

strlatlong = Llatitude & ",+" & Llongitude

'From 24 March Google changed their hyperlink address this is the new one

Command01.HyperlinkAddress = "https://maps.google.com/maps?q=" & strlatlong & "+(" & strSitename & ")&z=18&iwloc=near&hl=en&ll=" & strlatlong

Exit_Command01_Click:

Exit Sub

Err_Command01_Click:

MsgBox Err.Description

Resume Exit_Command01_Click

End Sub