Click any image to view a larger version




Last Updated 11 May 2022                                                Difficulty level :   Moderate

Section Links (this page):     Obtain API Key     Create a Simple Map     Annotating Your Maps     Related items/apps     Useful links


This is the first in a series of articles about annotating downloaded maps in Access

This article focuses on the use of Google maps with Access.

However, the approach can easily be adapted to use other online mapping services such as Bing, Open Street Maps etc

A significant part of the content in this article was presented to the online Access DevCon conference on Thur 28 Apr 2022.
The full program for this event is available at: Access DevCon Vienna (donkarl.com)
There were a total of 135 participants from 18 countries

The session was used to demonstrate ways of ‘annotating’ downloaded static Google Maps to personalise these for yourself or your clients.

The generated maps can be displayed using a web browser control (standard or ActiveX) or as a downloaded image.



1.   Obtain an API Key                                                                                                                   Return To Top

The first thing you need to do is get an API key to use the Google Mapping APIs you wish to employ.

To obtain your own free API key, go to: https://cloud.google.com/maps-platform/

The API key will be a long string starting with 'AIza' such as: AIzaSyAHXXMSyKgS00M314zzHr0gSg0upAX-q1hy

NOTE:
The above key is only given as an example. It isn’t currently valid and will not work
If you do try to use it, you will see this message:
    The Google Maps Platform server rejected your request. The provided API key is invalid.

IMPORTANT:
Google now requires billing information to be provided when obtaining/using API keys.
The number of free downloads available per month is restricted though the limit is currently very generous.

The current pricing model is based on $200 USD free usage each month (28,500 free map loads per month).
Each downloaded map contributes towards that map loads total.
Usage above the free usage limit for each month will be charged to your credit/debit card as supplied when obtaining the API key.
However, for individual or small/medium business use, this is likely to work out free in practice

NOTE:
Despite regular usage, I have only been charged once in over 5 years of using Google Map APIs.
That charge was because I downloaded geolocation data for a huge number of UK postcodes in a short period.
I knew I would hit the limit and the charges were exactly as I had predicted.

Depending on your requirements, you will need to enable several map APIs.

The main APIs needed are likely to be:
    - Static Maps
    - Places
    - Geocoding
    - Geolocation

However, for certain types of maps, you may also wish to add other APIs such as:
    - Directions
    - Distance Matrix
    - Maps Elevation
    - Maps Embed
    - Maps Javascript     (NOTE: this no longer works with the MS web browser control currently based on Internet Explorer)
    - Roads
    - Street View
    - URL Shortener

When setting up the APIs to use with your app(s), you can choose to do either of the following:
    - Use the same API key for all APIs used with your project – simpler to code but more likely to hit free usage limit
    - Use a different API key for each API – slightly more complex to code but less likely to hit usage limit



2.   Creating a simple map                                                                                                             Return To Top

To create a map centred on a specified location, the location can be based on any of the following:
    - Zip code / Postcode e.g. SE1 7PB
    - Co-ordinates e.g. 51.5027667,-0.1215859 (lat/long)
    - Full/partial address e.g. London Eye ; 6 Hill Street, Clevedon, Somerset

You must also specify:
    - Size in pixels (horizontal/vertical) e.g. 800x400; 640x400; 480x320

    - Zoom (range 1-20) where 1 = whole earth & 20 = rooftop level

        Zoom = 1
Zoom1
        Zoom = 12
Zoom12
        Zoom = 17
Zoom17
        Zoom = 20
Zoom20



    - Map type (roadmap/terrain/satellite/hybrid)

        Roadmap
roadmap
        Terrain
terrain
        Satellite
satellite
        Hybrid     (roadmap & satellite combined)
hybrid



    - Your Google Maps API key     (see above)



This information is then combined to create the Google Static Maps URL needed to generate the map.

For example, this URL creates the map shown below:
https://maps.googleapis.com/maps/api/staticmap?center=SE1+7PB&zoom=15&size=640x480&maptype=hybrid&key=YourGoogleAPIKey

image1

The first section https://maps.googleapis.com/maps/api/staticmap? is an instruction to use the Google Static Maps API
It is followed by a question mark to separate it from the rest of the URL

The remainder of the URL states that the map is centred on the London Eye which has a UK postcode = SE1 7PB, the zoom value = 15, the map size is 640 x 480 pixels and the map type is hybrid (satellite + roadmap combined). Each part is separated by the '&' operator



3.   Annotating your maps by adding map layers                                                                         Return To Top

Once you have generated standard Google static maps, the next step is to personalise the map for your clients
To do this, additional items (map layers) can optionally be added including the use of:

a)   Marker points e.g. to mark client locations or nearby places

image2
URL:
https://maps.googleapis.com/maps/api/staticmap?center=51.51866,-0.111234&zoom=16&size=800x400
&maptype=hybrid&markers=color:red|size:normal|label:X|51.51866,-0.111234&key=YourAPIKey

The latter part of the URL is an instruction that a hybrid map is used and a red marker of normal size with label X should be placed at the map centre


b)   Lines joining 2 or more points e.g. to indicate routes between locations

image3
URL:
https://www.google.co.uk/maps/dir/BS25 5NB/BS22 8XX

This uses the Google route planning service to set a route between two UK postcodes BS25 5NB & BS22 8XX


c)   Circles around a location e.g. to show places of a specified type within a specified distance

image4
URL:
https://maps.googleapis.com/maps/api/place/nearbysearch/json?center=51.50282,-0.119252&zoom=15&size=800x400&maptype=roadmap
&path=fillcolor:0x00FF0033|weight:3|color:red|enc:w_kyh?hhv__?l____?yf___?f`___?sf___?ba___?cf___?xa___?me___?lb___?qd___?~b___?oc___
?lc___?kb___?vc___?_a___?zc___?u____?zc___?t____?vc___?~`___?lc___?jb___?~b___?nc___?nb___?pd___?xa___?le___?`a___?bf___?f`___
?rf___?l____?xf___?m____?zf___?g`___?pf___?aa___?bf___?ya___?le___?ob___?rd___?_c___?nc___?mc___?hb___?wc___?`a___
?{c___?t____?{c___?u____?wc___?aa___?mc___?ib___?_c___?oc___?mb___?qd___?ya___?oe___?ca___?cf___?g`___?qf___?m____?{f___
?&border='0'&key= YourAPIKey

This uses the Google Places API to generate markers for up to 20 places of interest within a specified radius of the map centre.
Each place is given a unique label. Nearby places data is downloaded as a JSON file and stored in a 'temporary' table for retrieval.
The boundary circle is generated as a series of short red lines of weight (thickness) = 3.
To reduce the URL length, the points used for the circle are encoded


d)   Boundary shapes e.g. to show postcode or regional boundaries

image5
URL:
https://maps.googleapis.com/maps/api/staticmap?center=51.4539,-2.60214&zoom=14&size=800x400&maptype=roadmap
&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:qfayh?zczn_?m____?i____?oa___?{d___?ke___?gb___?qf___?{c___?gb___?kc___?jb___
?yb___?_c___?{c___?q`___?id___?ob___?f____?ea___?gc___?qb___?l____?oa___?cc___?i`___?`a___?gb___?`a___?ob___?|d___?ec___?oc___
?r____?yg___?h`___?sc___?lc___?|a___?dd___?{____?b`___?lc___?pc___?{`___?hb___?ta___?dd___?la___?~a___?uo___?vb___?wd___?sb___
?ub___?`b___?ui___?v`___?{a___?fg___?e`___?`d___?_d___?be___?o`___?ta___?zg___?`a___?tn___?ub___?hi___?|b___?l`___?nf___?gk___
?ha___?}e___?va___?{h___?~d___?sn___?|e___?wg___?ne___?mh___?bi___?af___?ta___?zy___?tf___?~h___?td___?|g___?r`___?xj___?h`___
?vd___?|`___?vk___?kb___?tj___?m`___?hm___?hb___?jl___?yc___?nm___?g____?vt___?i`___?rl___?u`___?hk___?i____?fk___?bb___?~e___
?k`___?xn___?`b___?bo___?g`___?|f___?dd___?tq___?sa___?bz___?{c___?zr___?qm___?_t___?|a___?el___?rb___?ye___?o`___?_q___?m`___
?}k___?_b___?yg___?m`___?ul___?a`___?of___?{a___?{q___?oe___?bg___?ah___?qd___?_d___?d____?ec___?gh___?qf___?yd___?s`___
?s____?o`___?`____?wc___?mk___?t`___?ih___?x`___?gf___?gb___?oe___?gc___?gd___?a`___?zb___?e`___?}f___?o____?oc___?
&border='0'&markers=color:red%7Clabel:9%7C51.4574,-2.57892&markers=color:red%7Clabel:6%7C51.4497,-2.59184
&markers=color:red%7Clabel:5%7C51.4531,-2.60196&markers=color:red%7Clabel:4%7C51.4517,-2.59588
&markers=color:red%7Clabel:3%7C51.459,-2.58925&markers=color:red%7Clabel:2%7C51.4563,-2.59358
&markers=color:red%7Clabel:1%7C51.4532,-2.59329&markers=color:blue%7Csize:normal%7Clabel:X%7C51.4539,-2.59214&key=YourAPIKey

The latter part of the URL is an instruction to mark the boundary of the postcode district BS1 with a blue line and a pale yellow fill.
Seven red markers of normal size with labels numbered 1,2,3,4,5,6 & 9 denote the centre of each postocde sector BS1 1, BS1 2 etc

The boundary path data was obtained from a different source. It consists of a large number of coordinates for each section of the boundary.
A sample of the boundary path points is used for the map and these are then encoded to reduce the total length of the URL.
However, using only a sample of the boundary data results in less precision in the boundary path displayed.


e)   Feature styles e.g. show local & main roads in selected colours

image6
URL:
https://maps.googleapis.com/maps/api/staticmap?center=53.96672,-1.072703&zoom=16&size=800x400&maptype=terrain
&style=feature:road|color:blue|visibility:simplified&style=feature:road.local|color:gray
&style=feature:road|element:labels.text.fill|invert_lightness:false&key=YourAPIKey

The latter part of the URL is an instruction to mark the main roads in blue and the local roads in grey.
Other features such as parks and government buildings can be marked in a similar way.

In each of the examples above, all 'annotations' are added as additional map layers using code as part of the URL used to generate the map.

Several layers can be combined to include additional information.

However, adding more features to the map increases the length of the URL.
Eventually you will hit the URL character limit using the current web browser control which is still based on the antiquated Internet Explorer 7 browser.

NOTE:
1. The limit is 2048 characters for the standard web browser control or 2083 characters using the ActiveX web browser.
2. A new web browser control based on Chromium Edge is currently under development and this is likely to have a much larger URL character limit.
3. Much longer URLs can be used if the generated map is saved as an image and displayed using an image control.
    Doing this bypasses the restrictions caused by the existing web browser controls
4. However, the maximum URL length will still be limited by the maximum length determined by Google. From wide testing, this appears to be around 10,000 characters

The session at DevCon demonstrated how to manage issues caused by the 2048/2083 character URL limit in the web browser controls.

Further details will be given in the remaining articles in this series including managing the total number of characters by:
a)   Encoding the co-ordinates
b)   Using a sample of the data
c)   Using a downloaded static map image

For example, the following is a hybrid map of Aberdeen city centre with zoom = 15.
It has an orange marker labelled A together with vertical and horizontal red lines centred at postcode AB10 1BA.
The map shows the postcode sector AB10 1 with a blue boundary line and a yellow fill.
Main roads are marked in blue and local roads in brown

image7
URL:
https://maps.googleapis.com/maps/api/staticmap?center=57.14885,-2.098458&zoom=15&size=800x400&maptype=hybrid
&markers=color:yellow|size:normal|label:A|57.14885,-2.098458&path=color:red|weight:5|52.14885,-2.098458|57.14885,-2.098458|62.14885,-2.098458
&path=color:red|weight:5|57.14885,-12.09846|57.14885,-2.098458|57.14885,7.901542&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:ejy{i?zmxk_
?zb___?ga___?la___?aa___?b`___?kg___?b`___?aa___?nd___?zc___?de___?ra___?~a___?bb___?v`___?lf___?ba___?|c___?va___?jj___?bb___
?zc___?d`___?hi___?fa___?vi___?z`___?vf___?v`___?fc___?jb___?|e___?n`___?ng___?ha___?|e___?ta___?fh___?q____?xb___?xb___?vd___
?lb___?~g___?n____?np___?aa___?vj___?ja___?ro___?ac___?nl___?ra___?jl___?ud___?rf___?ef___?oh___?cb___?ao___?uc___?fg___?a`___
?}h___?tc___?yr___?g____?gg___?fd___?ie___?y`___?wf___?{b___?~h___?kc___?}i___?mc___?co___?we___?we___?e`___?sj___?b`___?ch___
?}a___?ce___?xc___?_e___?ea___?ms___?ef___?yl___?qb___?al___?pe___?o____?ga___?wj___?sc___?ld___?_d___?ea___?ia___?`c___?}b___
?yh___?&border='0'&style=feature:road|color:blue|visibility:simplified&style=feature:road.local|color:brown
&style=feature:road|element:labels.text.fill|invert_lightness:false&key=YourAPIKeyHere

Even with all of this detail, the total number of characters in the above URL is only just over 1200

However, by comparison the map below is of part of the Shetland Isles off the north coast of Scotland and has a much longer URL of around 4300 characters:

The map includes a green X marker at the map centre surrounded by an octagonal shape with a yellow boundary and yellow fill.
That is also surrounded by a larger circle with a red line and green fill.
Also just visible in the bottom left corner is a blue line which is a small part of the postcode area boundary path.
As the map shows an area with low population density, the postcode area is very large and the long boundary path needs a lot of characters even though both sampling and encoding of the data has been used.

image8
URL:
https://maps.googleapis.com/maps/api/staticmap?center=60.79741,-0.819162&zoom=14&size=800x400&maptype=roadmap
&markers=color:green|size:normal|label:X|60.79741,-0.819162&path=color:orange|weight:5|fillcolor:0xFFFF0044
|60.8024052429199,-0.824162011146545|60.8024052429199,-0.814162011146545|60.7999052429199,-0.809162011146545
|60.7949052429199,-0.809162011146545|60.7924052429199,-0.814162011146545|60.7924052429199,-0.824162011146545
|60.7949052429199,-0.829162011146545|60.7999052429199,-0.829162011146545|60.8024052429199,-0.824162011146545
&path=fillcolor:0x00FF0033|weight:3|color:red|enc:_gcrj?v~~c_?z____?_s___?pa___?kr___?bc___?gq___?rd___?mo___?|e___?em___?~f___
?oj___?zg___?og___?lh___?ed___?vh___?w`___?xh___?v`___?lh___?dd___?zg___?ng___?~f___?nj___?|e___?fm___?rd___?lo___?bc___
?dq___?pa___?jr___?z____?~r___?{____?~r___?qa___?jr___?cc___?fq___?sd___?lo___?}e___?dm___?_g___?nj___?{g___?ng___?mh___
?dd___?yh___?v`___?wh___?u`___?mh___?gd___?{g___?mg___?_g___?qj___?}e___?em___?sd___?mo___?cc___?eq___?qa___?mr___?{____
?_s___?&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:yp`nj?fp{k_?_____?_____?
&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:irgoj?xkgi_?}v___?`cc__?|v___?acc__?
&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:wr}mj?~odf_?txn__?zgg__?rrp__?rih__
?tr`__?zhr__?qqb`_?osx__?pp___?{gj__?&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:unzmj?fzzf_?ljj__?rom__?ikl__?g~h__
?z_a__?kpc__?&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:md`pj?zm|f_?hkk`_?pcb__?vnc__?d|h__?~fh__?jax__?qui__?lge__
?kwa__?tu_`_?i}j__?_|x__?ot___?cjs__?sdb__?e}o__?orn__?qp`__?xw___?pql__?_~k__?|vf__?owc__?hiw__?izc__?qqb`_?w}r__?evi__?rdl__
?c{e__?~ek__?lg`__?tdl__?hsb__?wjj__?gsy__?~h`__?d|o__?&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|
enc:&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|
enc:gd}pj?zshf_?svq__?ymv__?znl__?abn__?`_i__?~jd__?vrp__?`_g__?wlh__?vtv__?i}k__?`o`__?&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|
enc:qosqj?he|d_?gjp__?owt__?l}i__?}~d__?|un__?twz__?cih__?g____?&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|
enc:&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|
enc:&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:u|fjj?|xai_?_____?_____?&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|
enc:&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|
enc:&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:iclnj?x_xh_?_____?_____?&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|
enc:gponj?rhrh_?_____?_____?&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|
enc:&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:}reoj?zz`h_?_____?_____?&border='0'
&path=color:blue|weight:3|fillcolor:0xFFFF0033|
enc:&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|
enc:gdhoj?jsog_?_____?_____?&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|
enc:k~joj?jdjg_?_____?_____?&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|
enc:uq{mj?vukg_?_____?_____?&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:mmanj?xxig_?_____?_____?&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|
enc:_pjmj?p_ig_?_____?_____?&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|enc:&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|
enc:cn}mj?v|fg_?_____?_____?&border='0'&path=color:blue|weight:3|fillcolor:0xFFFF0033|
enc:uw`nj?`ggg_?_____?_____?&border='0'
&style=feature:road|color:blue|visibility:simplified&style=feature:road.local|color:brown&style=feature:road|element:labels.text.fill|
invert_lightness:false&key= YourAPIKeyHere

This map cannot be generated using either a standard or an ActiveX web browser control in Access as it exceeds the 2083 character limit
However, it can be generated as a downloaded static map image



4.   Related items / example apps                                                                                               Return To Top

The following apps use various geolocation features to generate maps. All are available elsewhere on this website:

a)   Get Geolocation from Photos   (FREE)

b)   Get Current Geolocation   (FREE)

c)   UK Postcode Address Lookup   (FREE)

d)   UK Postal Address Finder   (PAID)



5.   Useful links                                                                                                                               Return To Top

The following links will help you get started on creating your own maps in Access:

a)   Google Static Maps API Developers Guides
      https://developers.google.com/maps/documentation/maps-static/start
      https://developers.google.com/maps/documentation/maps-static/styling

b)   Google Nearby Places API Developers Guide
      https://developers.google.com/maps/documentation/places/web-service/search-nearby

c)   IE browser URL character limit
      Maximum URL length is 2,083 characters in Internet Explorer (microsoft.com)
      What is the maximum length of a URL in different browsers? - Stack Overflow
      How long of a URL can Internet Explorer 9 take? - Stack Overflow

d)   Fix Google maps display issues in Access web browser control
      https://www.isladogs.co.uk/google-maps-fix/



The above article was the first in a series of articles planned for this website.
The second article in this series will be used to explain how the URLs can be built up in code

The third article will explain some of the VBA functions used including:
a) How to generate a circle of specified radius
b) How to encode co-ordinates to shorten the URL length required

An example app will also be supplied



Colin Riddington           Mendip Data Systems                 Last Updated 11 May 2022



Return to Access Articles Page




Return to Top