Identifying UK local authorities in geo map reports

[Nigel_Pain]

Using VA 7.5

Having just got geo mapping enabled using the SAS OpenStreetMap service I'm experimenting with creating reports using it. However, I'm not sure how to identify geographies. I have some sample data for Scottish local authorities with 9-character ID codes (along the lines of "S12000001"). I also have a shape file containing the ID codes, X and Y values, and names so I can merge this with the data. I think the ID codes are probably ISO codes without the "GB-" prefix, so I've also tried adding that on to them. But when I use this as a geography, either with or without the "GB-" prefix, I don't see anything plotted. For the context I've tried using the various ISO codes and SAS Map ID values without success. I also tried using the names as Subdivision Names and this was more successful, but it didn't identify all local authorities successfully and omitted the City of Edinburgh, Perth and Kinross, Argyll and Bute, and Dumfries and Galloway.

Can anyone suggest how to define a reliable geography?

[FalkoSchulz]

Hi!

SAS Visual Analytics ships with boundary data for country and first administrative level (e.g. states or sub-divisions). You can find out expected mapping values by visiting https://support.sas.com/rnd/datavisualization/vageo/85/va85lookupvalues.html .

Now, Scottish local authorities are administrative level 2 I believe (correct me here of course) - and as such would require custom boundary data to be imported. You can follow guides such as https://blogs.sas.com/content/sgf/2019/03/27/creating-custom-region-maps-with-visual-analytics/ or of course check out related VA documentation how to do that. If you have an Esri shapefiles - you could also use the provided %shpimprt macro to import the data into SAS. Often boundary data is provided free of charge and many government agencies around the world provide data as part of their open-data strategy.

Once you have imported the boundary data and defined your geographic item based on a custom geographic provider - the mapping should work.

Hope this helps. Falko

[Nigel_Pain]

Thanks @FalkoSchulz that's all quite helpful. Unfortunately, the blog entry seems to be fairly specific to VA 8.* (ie. on Viya) whilst we have VA 7.5 in SAS 9.4M7. There doesn't seem to be such a range of options available for creating a geography item, such as no option to define custom polygon shapes, only custom coordinates:

The other thing I'm finding is that I can't work out how to use the shape data as well as a separate data set for the business data. I'm sure I'm being stupid somewhere!

[FalkoSchulz]

Hey @Nigel_Pain - sorry I was somehow under the impression you would use current production version with VA on Viya. Well, in VA 7.x - things are a little more complicated unfortunately. Especially geographical support for custom polygons and centroids along with better Esri integration is something which got massively improved in VA 8.x.

However - we do have instructions on how to do this in VA 7.x as well. Please check out https://go.documentation.sas.com/?docsetId=vaag&docsetTarget=n09tvhnyrosr8mn1nmw7u3akrb1e.htm&docset.... There is a bit of SAS code involved so hopefully not too difficult. Essentially you will update the underlying lookup tables VA uses and merges in your custom polygon data. If all goes well - the 'Country or Region' drop down list will have your new entry populated.

In regards to your question around separating business from shape data. After the steps above you will have the shape data in a different table (the internal VA lookup tables). Once done - only an ID column ('GB-XXXXXX') is required in your business data. That is the very same column you will convert to a geographic item in VA. Once it's a geo column - you can use it in any geo visualization and combine with other business values to color regions or size bubbles.

Obviously let me know how you go.

Thanks, Falko

[Nigel_Pain]

Many thanks for this @FalkoSchulz. We don't have Viya, so having just upgraded to 9.4M7 in October we have the most recent non-Viya version of VA.

This all looks helpful so I'll see how it goes. I'm applying hotfixes across the servers this Friday so will get the opportunity to restart services then. In the meantime I'm running the import program (love a bit of code, me!). Seems to be taking a long time to run PROC REDUCE but maybe I'm just impatient!

I will let you know how it goes, and attribute credit where it's due.

[Nigel_Pain]

Well I managed to get the shape file data into a new MAPSCSTM library, with a bit of editing of the va74polygons.sas program. It was changing the ID variable into a shortened version so I had to force it to keep the full code. Here are the settings from the beginning of the program:

%let REGION_LABEL=Scottish Local Authorities; 
%let REGION_PREFIX=S1; 
%let REGION_ISO=827; 
%let REGION_DATASET=MAPSCSTM.SCOTTISH_LA1; 
%let REGION_SHP_FILE=*****************************; 
%let REGION_SHP_ID_COL=code; 
%let REGION_SHP_NAME_COL=name; 
%let REGION_SHP_DENSITY = 5; 

As a result of that, I then was able to choose "Scottish Local Authorities" as the geography type in Report Builder:

But still it isn't actually mapping the data onto the regions:

I can't see where this is going wrong. Here's some extracts of the various data sets involved:

                                  MAPDATA - Business Data                                

                   Obs    code            NAME                     band1

                     1    S1-                                        1
                     2    S1-S12000005    Clackmannanshire           3
                     3    S1-S12000006    Dumfries and Galloway      1
                     4    S1-S12000008    East Ayrshire              2
                     5    S1-S12000010    East Lothian               3
                     6    S1-S12000011    East Renfrewshire          4
                     7    S1-S12000013    Na h-Eileanan an Iar       1
                     8    S1-S12000014    Falkirk                    1
                     9    S1-S12000017    Highland                   2
                    10    S1-S12000018    Inverclyde                 2
                    11    S1-S12000019    Midlothian                 4
                    12    S1-S12000020    Moray                      4
                    13    S1-S12000021    North Ayrshire             1
                    14    S1-S12000023    Orkney Islands             1
                    15    S1-S12000026    Scottish Borders           1
                    16    S1-S12000027    Shetland Islands           2
                    17    S1-S12000028    South Ayrshire             3
                    18    S1-S12000029    South Lanarkshire          3
                    19    S1-S12000030    Stirling                   2
                    20    S1-S12000033    Aberdeen City              1
                    21    S1-S12000034    Aberdeenshire              2
                    22    S1-S12000035    Argyll and Bute            4
                    23    S1-S12000036    City of Edinburgh          3
                    24    S1-S12000038    Renfrewshire               4
                    25    S1-S12000039    West Dunbartonshire        1
                    26    S1-S12000040    West Lothian               1
                    27    S1-S12000041    Angus                      1
                    28    S1-S12000042    Dundee City                4
                    29    S1-S12000045    East Dunbartonshire        4
                    30    S1-S12000047    Fife                       3
                    31    S1-S12000048    Perth and Kinross          3
                    32    S1-S12000049    Glasgow City               3
                    33    S1-S12000050    North Lanarkshire          4

                                   MAPSCSTM.SCOTTISH_LA1  (obs=10)                               

      Obs  ISO  ISOALPHA2       ID            IDNAME       DENSITY      X          Y

        1  827     S1      S1-S12000005  Clackmannanshire     0     298892.30  694533.90
        2  827     S1      S1-S12000005  Clackmannanshire     4     298883.50  694488.50
        3  827     S1      S1-S12000005  Clackmannanshire     4     298890.70  694481.90
        4  827     S1      S1-S12000005  Clackmannanshire     1     298886.40  694361.70
        5  827     S1      S1-S12000005  Clackmannanshire     4     298781.20  694369.40
        6  827     S1      S1-S12000005  Clackmannanshire     4     298721.30  694365.00
        7  827     S1      S1-S12000005  Clackmannanshire     2     298713.80  694371.40
        8  827     S1      S1-S12000005  Clackmannanshire     4     298606.00  694346.90
        9  827     S1      S1-S12000005  Clackmannanshire     3     298508.30  694312.50
       10  827     S1      S1-S12000005  Clackmannanshire     2     298498.20  694319.10

                                     VALIB.CENTLOOKUP    (obs=10)  

                        Obs           mapname           ID

                       3477    MAPSCSTM.SCOTTISH_LA1    S1
                       3478    MAPSCSTM.SCOTTISH_LA1    S1-S12000005
                       3479    MAPSCSTM.SCOTTISH_LA1    S1-S12000006
                       3480    MAPSCSTM.SCOTTISH_LA1    S1-S12000008
                       3481    MAPSCSTM.SCOTTISH_LA1    S1-S12000010
                       3482    MAPSCSTM.SCOTTISH_LA1    S1-S12000011
                       3483    MAPSCSTM.SCOTTISH_LA1    S1-S12000013
                       3484    MAPSCSTM.SCOTTISH_LA1    S1-S12000014
                       3485    MAPSCSTM.SCOTTISH_LA1    S1-S12000017
                       3486    MAPSCSTM.SCOTTISH_LA1    S1-S12000018

                                     VALIB.ATTRLOOKUP (obs=10)

  Obs IDLABEL                       ID              IDNAME

 3477 Scottish Local Authorities    S1              Scottish Local Authorities
 3478 Aberdeen City                 S1-S12000033    Aberdeen City
 3479 Aberdeenshire                 S1-S12000034    Aberdeenshire
 3480 Angus                         S1-S12000041    Angus
 3481 Argyll and Bute               S1-S12000035    Argyll and Bute
 3482 City of Edinburgh             S1-S12000036    City of Edinburgh
 3483 Clackmannanshire              S1-S12000005    Clackmannanshire
 3484 Dumfries and Galloway         S1-S12000006    Dumfries and Galloway
 3485 Dundee City                   S1-S12000042    Dundee City
 3486 East Ayrshire                 S1-S12000008    East Ayrshire

  Obs          ID1NAME           ID2NAME ISO ISONAME

 3477                                    827 Scottish Local Authorities
 3478 Scottish Local Authorities         827 Scottish Local Authorities
 3479 Scottish Local Authorities         827 Scottish Local Authorities
 3480 Scottish Local Authorities         827 Scottish Local Authorities
 3481 Scottish Local Authorities         827 Scottish Local Authorities
 3482 Scottish Local Authorities         827 Scottish Local Authorities
 3483 Scottish Local Authorities         827 Scottish Local Authorities
 3484 Scottish Local Authorities         827 Scottish Local Authorities
 3485 Scottish Local Authorities         827 Scottish Local Authorities
 3486 Scottish Local Authorities         827 Scottish Local Authorities

  Obs key                                                ID1   ID2   ID3   ID3NAME   level

 3477 Scottish Local Authorities                                                       0
 3478 Aberdeen City|Scottish Local Authorities           S1                            1
 3479 Aberdeenshire|Scottish Local Authorities           S1                            1
 3480 Angus|Scottish Local Authorities                   S1                            1
 3481 Argyll and Bute|Scottish Local Authorities         S1                            1
 3482 City of Edinburgh|Scottish Local Authorities       S1                            1
 3483 Clackmannanshire|Scottish Local Authorities        S1                            1
 3484 Dumfries and Galloway|Scottish Local Authorities   S1                            1
 3485 Dundee City|Scottish Local Authorities             S1                            1
 3486 East Ayrshire|Scottish Local Authorities           S1                            1

I would have expected the business data to be mapped onto the Scottish Local Authorities but it hasn't been. If I can get this working there are various other Scottish geographies which I will be able to add in. 

Thanks.

[FalkoSchulz]

Nice work. Getting thru the code examples is quite a bit of effort so good to see you actually have the new provider showing up in the UI.
Looking at the data - it seems the registration went ok. I noticed the X/Y values in table SCOTTISH_LA1 (and assuming the resulting entries in the CENTLOOKUP table) are not using projection WGS84 (or EPSG:4326). Note, that VA 7.x does not support any other coordinate space (support for custom projection have been added to VA 8.x) and as such you will need to convert to EPSG:4326 before. SAS ships method proc gproject (doc: https://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=grmapref&docsetTarget=...) and conversion is usually pretty straight forward assuming you know the projection your data is in.
So it may just be that the registration went all well - but the latitude/longitude values aren't in the range the system expects it. WGS84 has a range from -90..90 (lat) and -180..180 (lng).
Hope this helps. Falko

[Nigel_Pain]

Well, I'm getting really confused now trying to convert the coordinates. I can't work out what units they are in the shape file and I've tried different PROJ.4 conversions without success.

Looking at the MAPSGFK.VAEUROPE1  data set, which appears to be what VA is using by default, I see that the lat and long variables are standard WGS84 values but the X and Y variables have ranges of roughly -0.49 to 0.5 (X) and -0.4 to 0.3 (Y).

[FalkoSchulz]

Well, typically you can determine the coordinate space by looking at your original Esri shape files. One of the files has the extension .PRJ and you can just open using a text editor. Then use information to find related proj.4 or EPSG code via https://epsg.io or https://spatialreference.org. Once done use a command such as (here an example converting Canada's UTM projection) :

proc gproject latlon
project=proj4 from="+proj=utm +datum=NAD27 +units=m +no_defs +zone=11" to="EPSG:4326" data=myorigshp out=myprjshp;
id REGION_ID_COL;
run;

..to convert values from your original projection into WGS. Note, that both from/to parameters support both the proj.4 syntax or EPSG codes.

Now, if you don't have the original shape file anymore or it doesn't have a .prj file - things get a little more complicated ;_) and it may involve a bit try and error approach. Looking at your first example line above - you stated that one coordinate pair for Clackmannanshire is:

Clackmannanshire     0     298892.30  694533.90

Using sites such as http://projfinder.com and entering both values as well as navigate the right hand map to exactly the location of Clackmannanshire shows a potential hit for EPSG:27700. Have you tried that?

In regards to your question and confusion about two different pairs of coordinates in MAPSGFK.VAEUROPE1. Note, that files in MAPSGFK are the original (unchanged) map data as provided by GfK. SAS Visual Analytics references only the LAT/LONG columns in these tables but not the X/Y. The latter represent the coordinates as projected by the Albers projection and aren't used in VA. So when you create your custom shape data set - it doesn't really matter what you populate in X/Y as long as LAT/LONG carry correct WGS84 representation of your values.

Hope this helps! Falko

[Nigel_Pain]

Breakthrough! Thank you so much for all your help @FalkoSchulz . I'd assumed it was the X & Y values so was concentrating on them. But I finally discovered that the original values were OSGB grid references and was able to use the appropriate PROJ.4 settings for them. This allowed me to map data correctly using bubbles or coordinates as the map type:

It's still failing if I use regions though:

[FalkoSchulz]

Nice work @Nigel_Pain - I feel like we are getting close 😎.

Given that data point overlays (bubble/scatter) are working indicates that you have correct entries in the CENTLOOKUP table along with correct X/Y values for the coordinates. Now, for regional overlays the actual polygon data is retrieved by referencing the table as specified in the MAPNAME column in CENTLOOKUP. If that fails (and you can double check any potential errors in the underlying workspace server log file) - it typically means that Visual Analytics can't find the related table (correct name?). In many cases it may just be a missing library definition during runtime - so first I would check whether you have correctly done step #2 in the instructions? In particular whether you have added the library to the appserver_autoexec_usermods.sas file and restarted the object spawner service?

If that library is there and table exists within that library and is correctly referenced in the CENTLOOKUP table - check the underlying workspace server log files and see whether you can see any other messages related to that. If you find something not making sense there - just post it here and I have a look...

Thanks, Falko

[Nigel_Pain]

I really appreciate all the time you're spending on this @FalkoSchulz. If I'm taking up too much I can always raise a track with Tech Support.

I did do all the configuring of the MAPSCSTM library and added a libname statement for it in the appserver_autoexec_usermods.sas file. I tested this by running the WorkspaceServer.bat file manually and the library was assigned as it should be. I did notice that I'd edited the va74polygon.sas file too much and removed the step where the custom region row was added to ATTRLOOKUP but I fixed that. I also ran this code to ensure that the mapname value in CENTLOOKUP was correct:

data _null_;
set valib.centlookup;
where id="S1";
call symput("mapname",mapname);
run;

data _null_;
set &mapname (obs=10);
put _all_;
run;

But I don't have logging enabled for workspace servers (I think that's the default?) and I haven't yet got to grips with how to do this. So I don't have any logs to look through.

All the best,

Nigel

[FalkoSchulz]

Well, you did all the hard work - I'm just leaving some comments 🙂

Question. I just had another look at your data column description above - in particular table MAPSCSTM.SCOTTISH_LA1. I do not see a column named SEGMENT in there. Can you confirm it's there? At the very minimum you need ID, IDNAME, SEGMENT, X & Y columns. 

In VA - can you also add a filter to reduce data to one region only? It sometimes helps to narrow down an issue. I have seen instances - nothing would render, just because there was one region which had invalid polygon data and confused the system. Worth a try - just looking at one for testing purposes.

Also let's validate whether expected ID variables are passed thru to the server. Can you open the browser developer tools (typically just by pressing F12 in Chrome/Edge). By default that window will be docked next to the browser window - if you undock and open in a new dialog - it will give you more room to see content. In the developer tools open the 'Network' tab and enter 'geoMapService' in the top left 'filter' input field. This will reduce the noise and only show requests to that one service. Every time you open a region map (even when sample data is shown) - a new request is send to retrieve the polygons. Now, if you select a request entry it will show you things like 'Headers' and 'Response' information. In 'Headers' - validate the query string parameters and make sure ID's are passed in as expected. No leading/ending spaces - upper/lowercase etc? Also validate the table name and library information. What is the return HTTP status code of such request (it should be 200) and do you see any response data at all?

Chances are, response data is empty given your description and it will be a little difficult to debug this here further without seeing any log files. If that is the case - we may need to get TechSupport involved so you can share log entries and they can help you enabling logs temporarily for debugging purposes. 

However, it feels like you have everything in place - and it may just turn out to be one minor last tweak required here.

[Nigel_Pain]

Segment does exist, I just didn't include it in the listing.

Well, all looks OK in the request, the form data shows this:

name: MAPSCSTM.SCOTTISH_LA1
location: 
ID: ID
subset: ID in ( 'S1-S12000005' 'S1-S12000006' 'S1-S12000008' 'S1-S12000010' 'S1-S12000011' 'S1-S12000013' 'S1-S12000014' 'S1-S12000017' 'S1-S12000018' 'S1-S12000019' 'S1-S12000020' 'S1-S12000021' 'S1-S12000023' 'S1-S12000026' 'S1-S12000027' 'S1-S12000028' 'S1-S12000029' 'S1-S12000030' 'S1-S12000033' 'S1-S12000034' 'S1-S12000035' 'S1-S12000036' 'S1-S12000038' 'S1-S12000039' 'S1-S12000040' 'S1-S12000041' 'S1-S12000042' 'S1-S12000045' 'S1-S12000047' 'S1-S12000048' 'S1-S12000049' 'S1-S12000050' )
xRes: 2400
yRes: 1800

or if I filter the data that is reflected in the subset line. As you predicted, there's no response. I think the best thing will be for me to raise a track with Tech Support. I've exported the requests to a HAR file so I can attach that. I'll report back in due course.

Once again, thanks for your help Falco. I wouldn't have known where to start!

Nigel

[FalkoSchulz]

Yes, request body looks good. Keep us posted how you go. Curious what's missing here. Thanks, Falko