9 - LocateXL
Contents
1. Matches ae in deep ae in deep 1 3 3 1 in 34 2 1 3 8 1 WriteLine in Console WriteLine A12xl A12XL in A12xl A12XL a12xl is ran and out in He is The dog ran The sun is out sure used in unsure sure unity used 99 50 05 in 1851 1999 1950 1905 2003 51 03 in 1851 1999 1950 1905 2003 1ABB 3ABB and 5AB in 1ABB 3ABBC 5AB 5AC A quantifier specifies how many instances of the previous element which can be a character a group or a character class must be present in the input string for a match to occur Quantifiers include the language elements listed in the following table Quantifier Description Pattern Chapter 9 LocateXL Ribbon Matches LocateXL Ribbon 9 r Matches the previous element zero Orad 0 19 9 219 9 more times Matches the previous element one or hawt bee in been be in bent more times 9 Matches the previous element zero or raj n ran rain one time n Matches the previous element exactly d 3 043 in 1 043 6 876 543 and n times 210 in 9 876 543 210 n Matches the previous element at leaStm yo 466 29 1930 n times i i i n m Matches the previous element at least d 3 5 166 1 608 n times but no more than m times 49302 in 193024 Matches the previous element zero or more times but as few times as d d 019 9 219 92. LocateXL User s Guide LocateXL Help 2012 PrimeThought Software Solutions Contents Chapter 1 Chapter 2 Chapter 3 Chapter 4 Chapter 5 Chapter 6 Chapter 7 Chapter 8 Chapter 9 N nN OO C8 FP OO ND oO 10 29 30 30 30 31 31 31 32 32 39 39 40 40 41 41 43 44 Introduction Videos and Training LocateXL and SpatialXL Download and installation Quick geocoder Batch geocoder Analysis of results Reverse geocoder LocateXL Ribbon Geocode Data Open Save Save As Settings Custom Data Open Save Save As Geocode Locate All Locate Selected Detailed Locations Zoom on map Geocode or Reverse Geocode Quick Geocoder Task pane Windowed Score Address Columns Structured or not Help About Contents Chapter 10 Chapter 11 Chapter 12 Chapter 13 Chapter 14 Chapter 15 Chapter 16 44 45 45 46 46 47 47 48 Information LocateXL Context Menu SpatialXL Ribbon SpatialXL Context Menu SpatialXL Thematics Base data Technical information Contact and Support Introduction 1 LocateXL is a geocoder that is an add in to Microsoft Excel 2007 or 2010 which works with a mapping add in called SpatialXL LocateXL is made up of the following Quick geocoder Batch geocoder Reverse geocoder SpatialxXL mapping and analytics Geocode reference data is included Currently LocateXL is available for the South African market Limited data exists in A
3. y LPT ee ae F ID v ADDRESSLINEI v ADDRESSLINEZ v ADDRESSLINES V PROVINCE v zPcoDe Structured or not 1 Structured Batch Geocode With a structured geocoder you choose each column to be an exact match 2 Unstructured Batch Geocode With an unstructured batch geocode the data that you are geocoding is not exactly placed per column in the data the suburbs and towns might be mixed up or the towns and provinces might be mixed up Therefore there is not a structured approach but an approach of putting together each row adding the columns together and working through each address working out more possible options for the best match Unstructured geocodes are slower than structured but it is very workable where cleaning data would be very time consuming and tedious to still get a good result More information of how to choose the correct columns are in the Address columns section You can tick Structured Geocoding on or off on the LocateXL ribbon M Use Structured Geocoding Chapter 9 LocateXL Ribbon 43 44 LocateXL Ribbon 9 Help About SJ Pe Pk T EL USUL LAE View Developer LocatexL Spatial XL s5 Columns P Geocode File C UWsers Desiree Deskt ructured Geocoding Custom Data file C WUsers Desiree De hizi Reverse Geocoding 5003 5003 Help About Locate L Versi PrimeTh amp ught Software Solutions LocatelQ XL Geocoder and reverse Geocoder Copyright Primethought Software Solution
4. File name RSA New ged Once this starts loading you will see in the information section of the Ribbon that the loading is happening Geocode File G Users Desiree Desktop LocatexL RSA New ged Pa No custom data loaded Cancel Loading street 140000 6892935 LocateXl Version 1 1 0 0 When it is completed it will look like this Geocode File C Wsers Desiree Desktop LocatexL RSA New gcd Mo custom data loaded Loaded 6 parts 669 293 streets 545 954 crossings LocateXl Version 1 1 0 0 Now you are ready to geocode Chapter 9 LocateXL Ribbon LocateXL Ribbon 9 Save se a Q ei Custom Geocode Geocode Show Quicl Data beocoder Made Save Geocode Data File Saves the currently loaded geocode data file LocateXL Press Fl for add in help You can save your own GCD file if you have made changes to the setting of the file The only problem that you might experience is when we build a new GCD file at PrimeThought due to map updates you will lose all these changes as the new GCD file will overwrite the old file It is better to save Custom Data Then you will never lose your changes This option is for clients who only work with their own data set or maintain their own data set Save AS S A Custom Geocode Geocode Show Qt Data E Geocodi Mlode Save the geocode data as a different file Prompts for a file name ans saves the currently loaded geocode data as th
5. Move Up Move Down A dialog come up where you can see the names of all of the extensions in the data set You can also search for a particular extension to see if it exists Chapter 9 LocateXL Ribbon 13 14 LocateXL Ribbon 9 E Extension straatststrstri In slaan Close cisclse ce Road pad rd Avenue aveavn Walk wik Place plcplek Drive dr Crescent crsntcresccres crs You can also add your own aliases for example for Street so that the words st str and so forth will also geocode correctly to Street Suffix A Suffix is North East South or West for instance Langebaan Road West The West would be the suffix If you right click on Suffix in the Settings gt Address Parts dialog and click on View Road Extension d Suffix Suburb View Town Province Move Up Mave Down EJ Delete A dialog come up where you can see the names of all of the suffixes in the data set You can also search for a particular suffix to see if it exists You can also add your own aliases so that the words S N and so on would geocode to South and North Chapter 9 LocateXL Ribbon LocateXL Ribbon Suburb If you right click on Suburb in the Settings gt Address Parts dialog and click on View View Move Up Move Down EJ Delete A dialog come up where you can see the names of all of the suburbs in the data set You can also search for a particula
6. Sse Maximum Score 5 Use Structured Geocoding re 57 M Developer LocateXL Spatial XL Geocode File G Users Desiree Desktop LocateXL RSA New gcd Custom Data file C Users Desiree Desktop LocateXL StreetCentric gcc a co m Geocode Custom Geocode Geocode Show Quick Help Data Data i v Geocoder 7 Geocoding 5003 5003 MPE Mode Settings Help About LocateXL Version 1 1 0 0 c8 fe BRAAMFONTEIN Eg B p D E F H i K L M 1 ID NEWSTREETADD SUBURB TOWN PROVINCE ZIPCODE Geocode Geocode Road Geocode tGeocode Geocode Geocode Geocode Geocode Geocode Geocode 2 1 POBox991 Port Alfred Port Alfred Eastern Cap 6170 0 Beach Drive Port Alfre Port Alfre Eastern Cape S 4 3 2 POBox 81 Klerksdorp Klerksdorp North West 2570 0 O R Tambo Street Klerksdor Klerksdor North West S 3 4 3 P O Box 109 Rivonia Rivonia Gauteng 2128 0 Box Street Wentwori Luipaards Gauteng S 3 5 4 79 Boeing Rd ESSEXWOLD BEDFORDVIEW Gauteng 2007 0 Boeing Road East Essexwolc Bedfordvi Gauteng S 4 6 5 22 David Draper Rd BRUMA JOHANNESBURG Gauteng 2198 0 David Street Jeppestov Johannes Gauteng S 4 7 6 30a Wordsworth Ay SENDERWOOD BEDFORDVIEW Gauteng 2007 30 Wordsworth Avenue Senderwc Bedfordvi Gauteng N 5 s 7 41 Stiemens St BRAAMFONTEIN HJOHANNESBURG Gauteng 2001 41 Stiemens Street Braamfon Johannes Gauteng N 5 9 8 53 Van Buuren Rd BEDFORDVIEW BEDFORDVIEW Gauteng 2007 0 Van Buuren Road Bedfordvi Bedfordvi Gauteng S 4 10 9 0 Pala
7. Pai rob Pung al Ale F aa Bujumbura bir as N Tanzania _ gt 3m m EA fs Angola Tas Zambia idian Lusaka s Mozambique Harare Sias ae Zimbabwe Namibia sa Aina ali Botewans K Giborone Text Pretoria Maputo i gt Hicemiontin South Africa Cape Toren e d T E i a GCS WG5_1984 Y 15 4189 2 5176 0 0000 EJ 1 47 176671 Geocode or Reverse Geocode Reverse Reverse 5how Quick Geocode Geocode T Geocoder a cee l a e Geocode NIS nae Reverse Geocode This flips between Geocode and Reverse Geocode Quick Geocoder The quick geocoder can come up docked as a task pane inside Excel or floating as a window For more information on the quick geocoder look at the Quick Geocoder section Chapter 9 LocateXL Ribbon 39 LocateXL Ribbon 9 Task pane This will open the quick geocoder as a task pane and dock it inside Excel For more information look at the Quick Geocoder section Minimum Score 10 g Lat Long Columns Geocode File G Users Desiree Desktop LocateXL RSA New gcd Maximum Score 57 Use Structured Geocoding Custom Data file G Users Desiree Desktop LocateXL StreetCentric gcc on Geocoding 5003 5003 TaskPane Settings Help About LocateXL Version 1 1 0 0 Windowed E RB TOWN E Layers q Ifred Port Alfr ription i dorp Klerksdo H w Ge ia Rivonia z WOLD BEDFORI mi y ff A JOHANN F RWOOD BEDFORI 9
8. These are advanced settings for clients that maintain their own geocode data sources Postal codes Geocode results can be to postal codes as well The only time that the data will geocode to a postal code is if there is some variant of PO Box in the street address field and a postal code in another column marked as postal code Then the address data will geocode to postal code and the status of that particular geocode will be P For more information on analysing the results please look at the Analysis of Results section Geocode Data Address Parts Crossings Projection Parameters Source Postal Codes Items to ignore Postal Codes CSV file path Postal Code Column Zip amp Column Lng Y Column Lat Part columns from most specific upwards areal City regionl Im port Postal Code Extract Regular Expression 7 s d is z Minimum Postal Code Length 4 f PO Box Extract Regular Expression 7 VANs 2 P s O s BC Postal Code Left Pad Char 0 3896 Postal codes Close Save import settings Postal codes are imported into the data set by PrimeThought Software Solutions This is an advanced tab in terms of managing postal codes Chapter 9 LocateXL Ribbon LocateXL Ribbon The only item of importance here is that the PO Box expression can be amended should your postal code fields have different characters than is usual in your particular address data set Ite
9. VIFONTEIN JOHANN a YRDVIEW BEDFORI OWN SANDTO i JENE JOHANN RWOOD BEDFORI IRDVIEW BEDFORI VATORY JOHANN VATORY JOHANN GLEN EDENVAI ROW JOHANN ALE EDENVAI IINGSIDE SANDTO AMS JOHANN VAY GARDENS GERMIST JA SANDTO NGTON JOHANN NGTON JOHANN ORE GARDENS JOHANN RRY SANDTO Fg PT Windowed This will show the quick geocoder as a floating window For more information have a look at the Quick Geocoder section 40 Chapter 9 LocateXL Ribbon LocateXL Ribbon 1 FUILANIEU Castelin Lap DL U U DEALII Klerksdorp North West 2570 0 O R Tambo 4 do SANDTON Gauteng 2196 77 Stella zermon z i 4 a n gt y GCS_WGS_1984 g 28 1438 26 1384 0 0000 FJ 1 2 500 Minimum Score 10 Maximum Score 57 The minimum and maximum score is also displayed for ease of reference on the LocateXL ribbon You can also change the minimum score directly on the ribbon How these scores are calculated is further discussed in the Parameters section Address Columns Address columns are applicable to the following 1 Reverse Geocode The address columns for the reverse geocode is where you choose the Lat Long columns Chapter 9 LocateXL Ribbon 42 LocateXL Ribbon Ea H5 9 r i GeocodingSample xlsx h Insert Fage Layout Formulas Data Review View Developer LocatexXL Q Minimum Score 10 amp t l ng Columns Get Via a Maximum Score 57 Ihi a Colum 7 Te 2
10. 1 1 You can also add all of the addresses on a map once you have X and Y locations but clicking inside the spreadsheet and clicking Add Excel Layer on the Ribbon or right clicking and then SpatialXL gt Add Layer This will put all of the data on the map Now you can do thematics in SpatialXL For detailed help on SpatialXL please visit SpatialXL online help The SpatialXL context menu right clicking on the spreadsheet row s matches the functionality in the ribbon ly Cut YA a gt i 5 Ba aain h oi a Nur M G3 Paste Options Wy we so age Paste Special sydenha l Insert ome im i i al Delete Clear Contents Filter gt oe Sort gt Eria E Insert comment pes E Format Cells Pick From Drop down List Define Name Link Range a Hyperlink LocatexL zoom selected Centre selected Goto map bookmark Highlight selection Clear all highlights Add selection to highlights 2001 _150 Jeppe ry Remove selection from highlights calibri ly ED Update layer extents Please note that these menu items will only be effective if the data has been added to the map using the X and Y locations to locate them Thereafter you can zoom to selected center selected highlight selections and so forth Once you have added a layer from the Xand Y data in the spreadsheet you can perform various actions such as thematic colouring a
11. 4 Load a geocode custom file if you have any gcc 5 Open your spreadsheet with the address data that you would like to geocode 6 Choose whether you are going to do a structured or unstructured geocode 7 Choose the address columns 6 Make sure your settings are correct for the particular address set that you are using 7 Choose your rows and Locate Selected or Locate All 8 Analyse your results 9 Tweak your settings and re run if necessary 10 Handle individual results where necessary 9 1 Handle by looking at Detailed Location Information 9 2 Handle by checking Quick Geocoder 9 3 You can also check out specific records by zooming to them on the map 9 3 Handle by applying changes and running selected rows again with different settings or changed data 11 Place data on map To do this you need to follow the steps to turn on SpatialXL spatial pane Set scene projection Load background map turn on crosshair and then you can right click on the spreadsheet and from the SpatialXL Context Menu choose Add Layer You can zoom in and out in the map by putting your cursor in the middle of the cross hair clicking once to bring the focus on the map and then mouse wheeling back and forth You can also right click on the new layer in SpatialXL and Zoom fit to that layer and so on Full functionality is in SpatialXL Help Chapter 5 Quick geocoder Batch geocoder 12 Do thematics or other actions to mapped data How to do this is described in
12. 9 LocateXL Ribbon 34 LocateXL Ribbon Home Insert Page Layout Formulas Data Review View Developer LocatexL Spatial XL Bf Show Sp Spatial Pane e ma al Refresh Map E gt ay F 4 ls E P O Show Floating m Auto refresh a a Add Excel Link Zoom Centre soto M Highlight Clearall Add Selection Remove selection Helpfor About Layer Range Selected Selected Bookmark Selection highlights to Highlights from highlights SpatialxlL Spatial XL Spatialxl Version 3 1 0 0 Show Spatial Pane Show Spatial pane and load saved layer information if any a SpatialXL Press Fl for add in help Set scene projection Once the Spatial Pane is showing set the scene projection if it is not correct to WGS 84 To do this you do the following 1 First double click on the scene projection area 2 Then the Scene properties dialog opens 3 Then click the drop down of the Predefined Projections 4 Then choose GCS_WGS_ 1984 from the drop down list 5 Then click Use 6 Then click OK Chapter 9 LocateXL Ribbon LocateXL Ribbon 9 Biahev CLT Description hv E g GeocodingSampl SIME gE j Predefined Projections GCS _WGS_1984 sanos eeg 0 Projection Well Known Text GEOGCS GCS _ Was _ 19 DATUM PD WasS_19 SPHEROID WGS_1984 63 78137 298 75 2235637 PRIMEM Greenwich 0 UNIT Degree 0 01 453292515943295 Met None gy 4 134 1742 253 2122 0 0000
13. A Substitutes the entire B4 AABBCC AAAABBCCC input string C Chapter 9 LocateXL Ribbon LocateXL Ribbon 9 Miscellaneous Constructs Miscellaneous constructs either modify a regular expression pattern or provide information about it The following table lists the miscellaneous constructs Supported Construct Definition Example imnsx Sets or disables options such as case insensitivity in bA 1 b w b matches ABA Able in imnsx the middle of a pattern ABA Able Act Inline comment The comment ends at the first bA Matches words starting with A w comment closing parenthesis b to end ofX mode comment The comment starts at an x bA w b Matches words starting line unescaped and continues to the end of the line with A Custom Data j Save SI aN Save As wu i You can set up parameters and aliases and save them in your own custom file For instance you can set up a road centric set and save it as a gcc file or you can set up a suburb centric data settings set and save that as a gcc and flip between them to handle different types of data sets As an example we have set up two different sets one for street centric data and one for town centric data rural areas with no or bad street names After setting up the data settings and testing them we saved each set as a separate GCC file Then when working with good road data one would use the RoadCentric gcc file and when working with le
14. Geocode Data Address Parts Projection Parameters Postal Codes Items to ignore Minimum Score Street Number Match Score Skip words in address Maximum match count 20000 Street part count save import settings Skip words maximum match count and street part count are advanced features You can leave these as they are Example of scoring The total maximum score would be calculated like this Name Weight Soundex Weight Click here to add a new row P Road Extension 3 2 E suttix 2 1 Suburb 10 7 ia Town 10 F 2 Province 10 POI Weight 7 POISoundex Weight 5s View POT Road Total possible 20 If it is a soundex match scores 15 Extension 3 Suffix 2 Suburb 10 Town 10 Province 10 Street number match 2 TOTAL POSSIBLE SCORE 20 3 2 10 10 10 2 57 Chapter 9 LocateXL Ribbon 21 22 LocateXL Ribbon 9 POI would replace the 20 3 2 2 with 7 total possible points and take into account the suburb town and province scores as well Minimum score 10 So matches on suburbs and towns will also give a correct match As an example the following address would score like this 32 Denise Road Buccleuch Sandton Gauteng It geocodes to 32 Denise Road Buccleuch Klipfontein Gauteng It matches on street number 2 Denise 20 Road 3 Buccleuch 10 Gauteng 10 So the total score is 45 out of a possible of 57 And this is of course a very good score Source
15. Pa 1 3651 te 390 00 0 00 0 00 Load background map layer In this instance we will load bing maps as a background layer 1 First right click on the workbook containing layer 2 and choose Add Sub Layer alternatively if you did not have a current open spreadsheet you could simply right click in the Layer area and choose Add Layer instead of Add Sub Layer Then in the New Layer dialog go to the Data tab and 4 Choose Bing Maps in the Loader area 5 You can choose either Satellite or Roads and then 6 Click OK Ce Chapter 9 LocateXL Ribbon 35 36 LocateXL Ribbon i EEES BPtteeeu seocodingSampl Loader Bing Maps Remove layer Save Layer properties x Seas 5 pk i Save Layer properties to E ENEE XO ti Copy layer data Theme Autoload Fit on Autoload Dynamic Load Text Labels e Save layer data B gt ji aa 5i Zoom fit ij aii tE Selection e kdi x gt Choose predefined connection settings LE Copy List Items F Make Internal eF Load Layer File ni View Data h j 5 z i Active e ocs_wes_1984 188 8144 201 0619 0 0000 Fy 1 428 177 612 E i we Bie o ie EI Update from worksheet This is what the map will look like if you have loaded bing maps You can also right click
16. SpatialXL Help r xii dW E 7 GeocodingSample xlsx Microsoft Excel oje 2 Home Insert Page Layout Formulas Data Review View Developer LocateXL Spatial XL a Q o ep X a P 9 2 62 Minimum Score 10 fE Address Columns Geocode File C Users Desiree Desktop LocateXL RSA New gcd Maximum Score 57 Use Structured Geocoding Custom Data file G Users Desiree Desktop LocateXL StreetCentric gcc Geocode Custom Geocode Geocode Show Quick Help Data Data Y Y Geocoder 7 Y Geocoding 5003 5003 Home Mode Settings Help About LocateXL Version 1 1 0 0 SpatialXL Rakes weiAC MegTtHo GS ws Layers HE E y gt a Victory Park Ps em 4 Nee R z gt any hs fe X R ear AEE Ah ma 2 i 2 gt Waterval ew Sis EH Oaklands 5 TA ail st ON a ay HAALAND Description E5Gl enhazel ge gt M w amp GeocodingSampl A v an a Fe er pee A Edenvale oY NE eee a we 6 ois Fa Nee me Ast Sydenham 4 Lae Pia ea Sa i Yate zE ivy R s1421 6 a Eastern B 3 za V AAS 0 Saas Parkwood es ep ae S z A denis y a Estates AA E i 10 to 30 SE 3 ag West Aaa Ks f Sado B Ook par SOS aR Cen Park y lt a mkinksfield O ee ge pe 8 Mil 2010 96 49 Pires E TT A f zr ee z ae UE gn r esr RNE RI A x a Di M7 _ 3 gt lt i we oe Se a he eee S i yo se a Sa pig Suniniyridde lt N peace a acai Main p T NA z ET e mA a ye T ye 4s n aay Sed G Saf
17. them Chapter 9 LocateXL Ribbon 25 26 LocateXL Ribbon character B The match must not occur on a b boundary Grouping Constructs Bend w b 9 ends ender in end sends endure lender Grouping constructs delineate subexpressions of a regular expression and typically capture substrings of an input string Grouping constructs include the language elements listed in the following table Grouping construct subexpression lt name gt subexpression lt name1 name2 gt subexpression subexpression imnsx imnsx subexpression subexpression subexpression lt Subexpression lt subexpression gt subexpression Quantifiers Description Pattern Captures the matched subexpression and assigns it a zero based ordinal number w 1 Captures the matched subexpression into a named group lt double gt w k lt double gt Defines a balancing group definition 2 Open 2 Defines a noncapturing Write Line group Applies or disables the specified options within A d 2 i w b Subexpression Zero width positive w 2 lookahead assertion Zero width negative 9 lookahead assertion b lun w4 b Zero width positive oe lookbehind assertion lt 19 d 2 b Zero width negative 9l lookbehind assertion lt 19 ate o Nonbacktracking or greedy Subexpression 13579 gt A B Open I 2 Close Open
18. y 1 Eus Geocode Custom Reverse Reverse Show Quick QM tongin AED Datar Datar Geocode Geocode Geocoder Latitude Column et Home Mode Settings Help About B2 hi Je 26 1995754241943 A C D E F G H l 1 2 3 A iis a OA X Y 28 08958 20 1996 28 16470 28 074935 28 0436 28 05051 28 04139 20 1323 26 195 20 1908 20 1007 20 2017 2 Structured Batch Geocode With a structured geocoder you choose each column to be an exact match oe Geo Road coding Cust Help Suburb Geo Town z Unused Province ID E NEW STREETADD PROVING SUBURB JRG Gauteng TOWN Gauteng PROVINCE JRG Gauteng ZIPCODE JRG Gauteng x Gauteng JRG Gauteng 3 Unstructured Batch Geocode With an unstructured batch geocode the data that you are geocoding is not exactly placed per column in the data the suburbs and towns might be mixed up or the towns and provinces might be mixed up Therefore there is not a structured approach but an approach of putting together each row adding the columns together and working through each address working out more possible options for the best match Unstructured geocodes are slower than structured but it is very workable where cleaning data would be very time consuming and tedious to still get a good result With an unstructured geocode you simply choose each column that you are using in the geocode Chapter 9 LocateXL Ribbon LocateXL Ribbon 9
19. 6378137 296 25 7223563 PRIMEM Greenwich 0 UNIT Degree 0 01 7453292519943295 Close save import settings Parameters The geocoding parameters are set up in this tab Please read this section together with the Analysis of Results section The minimum score out of the total possible score you can set up here or you can set it up directly in the ribbon Minimum Score 10 HH Fi As you can see from this example we require that at least 10 out of 57 scores to have a successful geocode result The lower the score the more results will come through but if the score is too low lots of invalid results will be passed as successful lf the score is too high then a lot of geocoding might fail instead of being passed successfully even though results might be quite accurate The geocode score is made up of the following items In Address Parts The road name score and soundex score It will be out of the total of the Weight if it is a Soundex match the Soundex weight will count The extension score and soundex score The suffix score and soundex score The suburb score and soundex score The town score and soundex score The province score and soundex score The point of interest score and soundex score This will replace the road name extension and suffix if it is a POI match In the parameters The Street number match score Chapter 9 LocateXL Ribbon LocateXL Ribbon 9
20. 9 r nThese in r nThese are ntwo lines vv in Avv AAF in MAAF r nThese in r nThese are ntwo lines x001B in x001B ab c d in a be d ab c d in q bc d x0003 in x0003 Ctrl C q b c d in a bc d d4 x J d 2 2 and 3 9 in 2 2 d4 f x d4 3 9 A character class matches any one of a set of characters Character classes include the language elements listed in the following table Character class Description Matches any single character in character_group character_group By default the match is character_group first last case sensitive Pattern ae Negation Matches any single character that is not in character_group By default characters in character_group are case sensitive Character range Matches any single character in the range from first to last Wildcard Matches any single character except n Chapter 9 LocateXL Ribbon aei A 4 a e Matches mar in gray a e in lane is a g n in reign A B in AB123 ave in nave LocateXL Ribbon 9 ate in water Matches any single character in the Unicode p Lu Cc L in City Lights p name general category or named block specified by name p Is Cyrillic en 0 em Matches any single character that is notin P Lu i t y in City P name the Unicode general category or named block specified by na
21. Avenue SES ees Get Locations All Seats spatiale Get Locations of Selected Rows JOHAN orere iii a SANDTON Gauteng Get Detailed Location Info of Current Row f He Sheeti Sheet za a zoom To Current Row on Map The context menu matches up with functionality in the LocateXL ribbon When you right click on a particular row you can Get Locations of All Get Locations of Selected Rows Get Detailed Location Info of Current Row Zoom to Current Row on Map Home Insert Page Layout Formulas Data Review View Developer LocatexL Spatial XL Be Show Spatial Pane B Refresh Map 2 POO me i z ig P otal gOS EPEN 5 h l A El Show Floating ai be Auto refresh S Add Excel Link Zoom Centre nto T Highlight Clearall Add Selection Remove selection Help for About Layer Range Selected Selected Bool marl S lectian highlights to Highlights fram highlights SpatialxXl Spatial XL SpatialXl Version 3 1 0 0 Functionality for SpatialXL is fully covered in the Help for SpatialXL section However the basic functionality for geocoding includes the following items First open up the spatial pane in SpatialXL Then make sure your projection is set right Then load Bing maps for a background If you want to see a cross hair you can set that up Then zoom on map from the Quick Geocoder or a row in the Spreadsheet after geocoding oe Chapter 10 LocateXL Context Menu 45 46 SpatialXL Ribbon
22. Chapter 9 LocateXL Ribbon LocateXL Ribbon Geocode Data Address Part Postal Codes Weight Extension Suffix Suburb Tewn 4 Province POI Weight Fi ea POI Soundex Weight 5 n Close Save import settings Road lf you right click on Road in the Settings gt Address Parts dialog and click on View Name View Move Up Move Down Province mc Delete Address Parts Soundex Wetght Click here to add a new row View POL A dialog come up where you can see the names of all of the roads in the data set You can also search for a particular road name to see if it exists Chapter 9 LocateXL Ribbon 11 12 LocateXL Ribbon You can also enter aliases for particular roads for instance Church Kerk alias so that the road name Kerk will also geocode to Church Remember to save the settings when you are done and to save the data as a custom data file otherwise the settings will be lost next time you load the map data Chapter 9 LocateXL Ribbon LocateXL Ribbon 9 Christie de Witt Chris Hani Chief Mhlabunzima Chota Motala Che Guevara Charlotte Mxeke Chris Ntuli Chakide Chakijane wj SET Se ao ne Signs Extension An extension is for example Road Lane Close Avenue and so forth If you right click on Extension in the Settings gt Address Parts dialog and click on View E Hfi Road a Extension View
23. Ew cau as Z0U7T ae g 7 41 Stiemens St___BRAAMFONTEIN JOHAN y cy 2001 a 8 53 Van Buuren Rd BEDFORDVIEW BEDFOI 7 2007 as z 9 0 Palala CI SANDOWN SAM a A z 2196 pR 8 Paste Options 10 51 Glanville Ave CYRILDENE JOHAN z 2198 OK 11 34 Spencer Ave SENDERWOOD BEDFO B z 2007 R 12 15 Arbroath Rd BEDFORDVIEW BEDFO Paste Special g 2007 13 28 Innes St OBSERVATORY JOHAN lisat 7 2198 a gt 14 4 Gerard St OBSERVATORY JOHAN chien 7 2198 z 15 14 Granville Rd EDEN GLEN EDENV pm i 1609 me 16 5 Pietersen St HILLBROW JOHAN 7 2001 17 203 1st Ave EDENVALE EDENV S Eormat Cens 1609 x 18 11 Aberfeldy Ave MORNINGSIDE SANDE BowHeight f 2191 19 122 Berea Rd BERTRAMS JOHAN Hide z 2094 i 20 8 Aletta Ave HIGHWAY GARDENS GERMIS Unhide 7 1609 21 58 Rivonia Rd INANDA SANDT leaa 22 123 Oxford Rd KENSINGTON JOHAN SpatialXL gt calana iie mre ai amc JOHANNESBURG Gauten Get Detailed Location Info of Current Row 24 100 Benmore Rd BENMORE GARDENS JOHANNESBURG Oe EEO A O 25 11 Acacia Ave DUXBERRY SANDTON Gauteng imi E aw ae a need SANTOS al Sauteng aa 3 i GCS_wes_1984 28 0362 26 1913 0 0000 F3 1 2 500 If you want to zoom out slightly click in the middle of the crosshair and roll your mousewheel back Open up Spatial Pane 1 First go the the SpatialXL ribbon in Excel 2 Then click on Show Spatial Pane You can show it floating or docked Chapter
24. Glen Ext 35 Edenvale Gauteng 4 28 07495499 26 19495964 21 Kitchener Avenue Kensington Johannesburg Gauteng 5 28 04359818 26 19084549 27 Hospital Street Braamfontein Johannesburg Gauteng 6 28 05050659 26 10074043 0 West Street Morningside Ext 5 Sandton Gauteng 7 28 04139137 26 20173073 150 Jeppe Street Johannesburg Johannesburg Gauteng 8 28 16775894 26 11881638 24 Price Road illiondale Edenvale Gauteng 9 28 11761475 26 16337585 0 John Avenue Bedford Park Ext 6 Bedfordview Gauteng 10 28 07301331 26 20470428 119 Park Street Jeppestown Johannesburg Gauteng 11 28 05039406 26 10078239 0 West Street Morningside Ext 5 Sandton Gauteng 12 28 04390907 26 09675407 108 12th Street Parkmore Sandton Gauteng 13 28 05187607 26 09491348 0 Benmore Road Benmore Gardens Sandton Gauteng 14 28 03373528 26 19516945 1 Bertha Street Braamfontein Johannesburg Gauteng 15 28 15351486 26 16014671 21 Harry Street Dunvegan Edenvale Gauteng 16 28 044384 26 0762291 31 Tugela Road River Club Ext 1 Sandton Gauteng 17 28 11927605 26 19486618 19 Sovereign Street Kensington Bedfordview Gauteng 18 28 04581451 26 19207764 0 Klein Street Hillbrow Johannesburg Gauteng 19 28 08299065 26 18832016 1 4th Avenue Bezuidenhout Valley Johannesburg Gauteng 20 28 14203453 26 14460945 10 Olienhout Street Dowerglen Ext 5 Edenvale Gauteng 21 28 07847786 26 19046593 41 5th Avenue Bezuidenhout Valley Johannesburg Gauteng 22 28 03965378 26 19304657 124 De Korte Street Braamfontein Joh
25. alala Close MorningsiSandton Gauteng S 10 51 Glanville Ave IcyriLDENE JOHANNESBURG Gauteng 2198 51 Glanville Avenue Cyrildene Bedfordvi Gauteng N 11 34SpencerAve SENDERWOOD BEDFORDVIEW Gauteng 2007 34 Spencer Avenue Senderwc Bedfordvi Gauteng N Zoom on map To zoom on map you follow these steps First open up the spatial pane in SpatialXL Then make sure your projection is set right Then load Bing maps for a background If you want to see a cross hair you can set that up Then zoom on map as shown below a i Now you can zoom to a particular area by either right clicking on a particular address line and clicking zoom to map or placing your cursor on the line and clicking zoom to map on the ribbon Chapter 9 LocateXL Ribbon LocateXL Ribbon Minimum Score 10 Maximum Score 57 Geocode Show Quick va Geocod ci Locate All Locate Selected Zoom to SpatialXL map location Gq LocateXL Press Fl for add in help ID NEWSTREETADD SUBURB TOWN PROVINCE ZIPCODE Geoc v Glakew t iAaeteaegs gi ke gi Layers q 1 P O Box 991 Port Alfred Port Alfred Eastern Cap 6170 X Description Pa 2 PO Box 81 Klerksdorp Klerksdorp North West 2570 Ae Vey eae 8 3 P O Box 109 Rivonia Rivonia Gauteng 2128 aN ZW amp Bing maps a 4 79 Boeing Rd ESSEXWOLD BEDFO Calibri 11 A a SB 9 Ba Dx fe 5 22 David Draper Rd BRUMA JOHAN B IZQ A H WMG DA ig 6 30a Wordsworth A SENDERWOOD BED Okov
26. andton Gauteng S 10 51 Glanville Ave CYRILDENE JOHANNESBURG 51 Glanville Avenue Cyrildene Bedfordvi Gauteng N PROVINCE ZIPCODE Geocode IGeocode Road Geocode Geocode Geocode Geocode Geocode Geocode Geocode This will get the detailed locations of the current selected row and will give various searchable options of which one can choose to improve results You can get this by clicking the button on the ribbon or right clicking on the row and choosing Get detailed locations Then by going through the results you can choose a different result Address choices a e epte lesktop LocateXL RSA New gcd e Desktop LocateXL StreetCentric gcc 35 Glanville Avenue Cyrildene Bedfordview Gauteng 35 Glenavon Road Glenesk Johannesburg Gauteng thes Etatens 35 Glenavon Road Glenesk Johannesburg Gauteng 35 Glenavon Road Glenesk Johannesburg Gauteng J K L M N O 33 Glanville Avenue Cyrildene Bedfordview Gauteng le Geocode Geocode Geocode Geocode Geocode Geocode 1 Port Alfre Eastern Cape p E ille Avenue Cyrildene Bedfordview Gauteng Klerksdor North West p Locate ille Avenue Cyrildene Bedfordview Gauteng Gauteng P 32 N Bezuidenhout Street Bellevue Johannesburg Gauteng Essexwolc Bedfordvi Gauteng n Se ae SE PEE ae ieee Jeppestov Johannes Gauteng S Senderwc Bedfordvi Gauteng N Braamfon Johannest Gauteng N 3 53 Van Buure ay auteng OVanB Bedfordvi Bedfordvi Gauteng S 9 0 Palala CI SANDOWN SANDTON Gauteng 2196 0 P
27. annesburg Gauteng 72 70 19105794 26 13260961 Nn Usarric Auraniian Cdan Clan Cvi 7A Cdanwuala NRantann E tome Insert Page Layout Formulas Data Review View Developer LocateXL Spatial XL A 2 o ep 2 mg a a Geocode File G Users Desiree Desktop LocateXL RSA New gcd ww Minimum Score 10 fH Address Columns Se Maximum Score 57 Use Structured Geocoding No custom data loaded Geocode Custom Geocode Geocode Show Quick Help Home Mode Settings Help About LocateXL Version 1 1 0 0 Click on a button to find out more about each section Geocode Data s amp Custom Geac Datar Click on a button to find out more about it This is where you work with the GCD file Your Geocode Data file and where you set up settings to do the geocoding with The Settings is related to the Bulk Geocode and getting detailed information on particular geocoded Chapter 8 Reverse geocoder 7 8 LocateXL Ribbon 9 records Open This is what you do before you start doing any geocoding When you click on open you must go to the most recent gcd file that you have and open that FA Browse for geocode data file OO F LocateXL 4 Organize New folder i lt r PEER Eine Name Date modified Type E Desktop 0 RSA New gcd 2012 02 05 05 03 GCD File dp Downloads b Recent Places Libraries E3 Documents a Music Pictures Videos pl a IE Cormnuuter bai E
28. ape 5 S 25 43 86 27 85581 32 9933 Susan Road Amalinda Gompo Eastern Cape 6 S 15 26 32 28 30349 25 7787 Michigan Street Faerie Gle Mamelodi Gauteng 7 l 30 52 63 30 60973 29 7931 Georgedale Primary School Georgedale KwaZulu Natal 8 N 52 91 23 18 66291 33 8126 Darling Street Vierlande Durbanvil Western Cape 9 5 25 43 86 26 87484 31 9228 Fern Street _Victoria Pi Mlungisi Eastern Cape B 4 4 gt Address Random Sample lt 3 li 4 ll ae au SpatialXL Pane Loaded TES Oj 100 i Important Please use a download manager such as Free Download Manager at http www freedownloadmanager org to assist with the download This will give you a much quicker download and will ensure better quality and less corrupted downloads especially if you have a slow internet link Prerequisites 1 Admin privileges speak to IT for this 2 Excel 2007 or 2010 already installed You have to download and install both SpatialXL and LocateXL Here are the instructions To watch the video on How to download and install Locate XL and SpatialXL click on this link to view it in YouTube To download and install SpatialXL you do the following 1 Go to http www PrimeThought biz and register on the website or log in if you have already registered 2 Once logged in you will see the Download section on the left hand side as one of the tabs 3 Go to the Download section and download SpatialXL exe 4 lf you have already installed a prior version of S
29. ask pane If you would like to view the results on a map read below Ole ONS Every field in the quick geocoder serves as a filter for other fields It works best if you type in a particular road name or a town etc When you then choose other fields the choices of the other fields will be limited to the correct values for those areas Then click Locate and then if you want to see the location locate on map You can also click Locate and then click Crossing to see if there are any crossings that you can choose from with that street Chapter 4 Download and installation 3 Quick geocoder 5 To use the Locate on Map you should first follow the steps to turn on SpatialXL spatial pane Set scene projection Load background map turn on crosshair and then you can locate on map You can zoom in and out in the map by putting your cursor in the middle of the cross hair clicking once to bring the focus on the map and then mouse wheeling back and forth SpatialXL KwaZulu Natal Blakew et ARM ese Te a OE Yellowwood Park PAR Bing maps sade Eh Bellair Whitby jam wt ja uy o E E a at Lea Crossing Coordinates 30 9624 29 8947 7 i ab GCS_WGS_1984 26 5184 33 3097 0 0000 4 1 2500 amp 90 00 0 00 0 00 The steps to use the Batch Geocoder is the following 1 Install SpatialXL 2 Install LocateXL 3 Load the geocode data file gcd
30. at file jy LocateXL Press Fl for add in help You can save your own GCD file as another file if you have made changes to the setting of the file The only problem that you might experience is when we build a new GCD file at PrimeThought due to map updates you will lose all these changes as the new GCD file is not able to be merged with an old GCD file Chapter 9 LocateXL Ribbon 10 LocateXL Ribbon 9 It is better to save Custom Data Then you will never lose your changes This option is for clients who only work with their own data set or maintain their own data set Settings The geocode data settings sets up scoring weights soundex weights points of interest weights and aliases for road names and other All of the settings you can tweak for the best result geocoding are in these tabs Source Postal Codes Items to ignore Address Parts Crossings Projection Parameters Click on one of the tabs to find out more about the settings contained therein Address Parts How to set up the settings for geocoding in the Address Parts Weight This is the weight that the match or non match counts in the total geocode score For instance in the above example have decided that the weight for a road is 10 points Should a road match exactly letter for letter the name of the road in the geocode data file you will get the full 10 points for the road record If the
31. ateXL Ribbon 17 18 LocateXL Ribbon 9 Province North West Mpumalanga KwaZulu Natal Northem Cape Limpopo Free State Eastern Cape Western Ca pe Points of interest If you click on the View POI button it will bring up a list of Points of Interest in the country View POI Here you can search for POI s in the dataset and put in aliases as well LocateXL can geocode to POI if the POI does exist in the dataset Chapter 9 LocateXL Ribbon LocateXL Ribbon 9 AVIS Strand AVIS Stellenbosch AVIS Seapoint AVIS Plettenberg Bay AVIS Parow AVIS Paarl AVIS Oudtshoorn Conjunction List separated Click here to add a new row amp and amp and amp and amp en amp en amp and amp en Close Save impart settings LocateXL will geocode to crossings where they exist in the data set You can set up the list of possible namings of the crossings in the Crossings tab of the settings Projection The Well Known Text projection of the data is set up in the Projection tab In this instance we are using the WGS 84 projection Chapter 9 LocateXL Ribbon 19 20 LocateXL Ribbon 9 Geocode Data Address Parts Crossings Projection Parameters Source Postal Codes Items to ignore Projection Well Known Text GEOGCS GCS WGS_1984 DATUM D_W03_1984 SPHEROID PWiG5S_1984
32. ded suburb town province as well as the geocoded status which tells you whether it geocoded to the street number the road name the suburb the postal code and so forth and the score as well as the confidence level of the result This is what every column represents The geocode number example 93 This is the street number result If there is no street number the geocode number will be 0 The geocode road example Barkly This is the geocoded roade result If LocateXL cannot find the road but geocodes to suburb or town level it will return the first road it found for that town in the data The geocode extension example Road This is usually road lane street etc This can be blank if the extension in the base data was left blank The geocode suffix example West for instance Kei Road West The West would be the Suffix The geocode crossing example Main if the data geocoded to a crossing the second street s name would be in here and the geocode status would be S for Street The geocode suburb example Galeshwe The geocode town or city example Kimberley The geocode province example North Cape The geocode status We use codes the following codes for showing the status of the geocoded record Chapter 6 Batch geocoder Analysis of results r N means it geocoded to the exact street with the street number S means it geocoded to street level O means it geocoded to suburb level 1 means it geocoded to the city or to
33. e http www locatexl com temp RSA new gcd http www locatex com temp StreetCentric gcc and http www locatex com temp TownCentric gcc These links will be e mailed to you 3 If you have already got the latest download of the data gcd file you do not have to download this file again 4 If you have already installed a prior version of LocateXL please go to Add and Remove Programs in your Control Panel and remove LocateXL 5 Then run LocateXL exe following the prompts 6 When successfully installed open Excel 2007 or 2010 and go the the LocateXL ribbon 7 Open Help and then About by clicking on the button and then click on Update License 8 Click Load license from file and choose the Locate XLLicense lic or other named license file for LocateXL that you received from PrimeThought it will have the extension lic to bring in the key of the license Then when you click OK your license will be valid and you will be able to use LocateXL 9 To test that LocateXL is working go to the LocateXL ribbon and under Geocode Data choose open and then choose the gcd geocode data file that you have been provided with If this data loads you will see this information in the ribbon under the information section then you know that LocateXL has been successfully installed These are the steps to using the quick geocoder Install SpatialXL Install LocateXL Load the geocode data file gcd Open the quick geocoder as a window or t
34. e Microsoft NET 4 environment on a machine running SpatialXL Admin rights are needed to install LocateXL Standard Microsoft MSI install procedures are used for the installation Windows XP service pack 3 or above is fine for user machines Requires Excel 2007 or 2010 Will NOT work in Excel 2003 The licence is locked to the machine name The software can run on 32 bit or 64 bit versions of Windows epending on the need of You need to have SpatialXL installed to have full functionality in terms of mapping TECHNICAL INFORMATION ABOUT SPATIALXL SpatialXL is an add in to Excel and is written using the C language You need the Microsoft NET 4 environment on a machine running SpatialXL Admin rights are needed to install SpatialXL Standard Microsoft MSI install procedures are used for the installation Windows XP service pack 3 or above is fine for user machines Requires Excel 2007 or 2010 Will NOT work in Excel 2003 The licence is locked to the machine name The software can run on 32 bit or 64 bit versions of Windows Chapter 13 SpatialXL Thematics 47 48 Contact and Support For any bugs supportor any type of assistance please contact us PrimeTh sught Software Solutions Tel 27 0 46 624 8654 HQ Tel 27 0 11 275 0172 Joburg Office Cel 27 0 83 395 3484 Technical Support Cell 27 0 72 049 8091 Sales amp Client Liaison F
35. eXL Ribbon 31 32 LocateXL Ribbon Home Insert Page Layout Formulas Data Review View Developer LocateXL Spatial XL A 2 ry a amp Minimum Score 10 fifi Address Columns Geocode File G Users Desiree Desktop LocateXL RSA New gcd S ot laximum Score 57 Use Structured Geocoding Custom Data file G Users Desiree Desktop LocateXL StreetCentric gcc Geocode Custom Geocode Show Quick Help Data Data Geocoder gt Loaded 6 parts 689 295 streets 543 954 crossings 3 elp Ab XLV 1 0 0 ame J Locate All settings i Heip hout i LocateXL Version 1 1 0 0 A4 1 ID NEW d 1 PO E 4 7 Geocode the selected rows on the worksheet Eastern Cap 6170 0 Post Code 6170 3 2POE ae nicubatet North West 0 Post Code 2570 crainrnaaAAn ArArANMMsaria ee n anna aa Detailed Locations 3 POE P 2 Press F1 for add in help 0 Post Code 2128 Gauteng p 479 Be BEDFORDVIEW Gauteng 2007 79 Boeing Road East Essexwolc Bedfordvi Gauteng N 5 22 Diwrerereprer rereren JOHANNESBURG Gauteng 2198 0 David Street Jeppestov Johannes Gauteng S 6 30a Wordsworth Ay SENDERWOOD BEDFORDVIEW Gauteng 2007 30 Wordsworth Avenue Senderwc Bedfordvi Gauteng N 7 41 StiemensSt BRAAMFONTEIN JOHANNESBURG Gauteng 2001 41 Stiemens Street Braamfon Johannest Gauteng N 8 53 Van Buuren Rd BEDFORDVIEW BEDFORDVIEW Gauteng 2007 0 Van Buuren Road Bedfordvi Bedfordvi Gauteng S 9 0 Palala CI SANDOWN SANDTON Gauteng 2196 0 Palala Close Morningsi S
36. ee eae 3 1 MW X Bing maps Bf eS ATN ea jt Primrose S i sanos eeg Te as eid seme tS itoy an R ee T st en 3 win pon By 5 mone 7 a DanS oare at a er j ere Mae err ae 4 a a aera MS 92 Pgs a at ee eet ERIVeriea Ps Se ages ae ie Ftrancois Bbetholz teks M 4 Ga R aol ea Ne rk x Eae gt M Woa S T so Xe T 1 GCS_WGS_1984 S 27 9836 26 1836 0 0000 F 1 119525 amp 90 00 0 00 0 00 fe Rivonia A B D ee e e H ee ee ee e Ll mM w f oil l 1 JID NEWSTREETADD SUBURB TOWN PROVINCE ZIPCODE Geocode Geocode Road Geocode Geocode Geocode Geocode Geocode Geocode Geocode Ged 3 4 79 Boeing Rd ESSEXWOLD BEDFORDVIEW Gauteng 2007 0 Boeing Essexwolc Bedfordvi Gauteng S 6 5 22 David Draper Rd BRUMA JOHANNESBURG Gauteng 2198 0 David Street Jeppestov Johannes Gauteng S 7 6 30a Wordsworth Ay SENDERWOOD BEDFORDVIEW Gauteng 2007 30 Wordsworth Avenue Senderwc Bedfordvi Gauteng N 8 7 41 Stiemens St BRAAMFONTEIN JOHANNESBURG Gauteng 2001 41 Stiemens Street Braamfon Johannes Gauteng N 9 8 53 Van Buuren Rd BEDFORDVIEW BEDFORDVIEW Gauteng 2007 0 Van Buuren Road Bedfordvi Bedfordvi Gauteng S 4 gt gt Sheeti I 7 i i i i l li 4 il SpatialXL Pane Loaded EE 100 gG F When having completed geocoding in a spreadsheet LocateXL creates columns for the geocoded number of the street the geocoded road the geoco
37. ene projection Load background map turn on crosshair and then you can right click on the spreadsheet and from the SpatialXL Context Menu choose Add Layer You can zoom in and out in the map by putting your cursor in the middle of the cross hair clicking once to bring the focus on the map and then mouse wheeling back and forth You can also right click on the new layer in SpatialXL and Zoom fit to that layer and so on Full functionality is in SpatialXL Help 10 Do thematics or other actions to mapped data How to do this is described in SpatialXL Help OP ANOS oS NY a Chapter 7 Analysis of results Reverse geocoder Page Layout Formulas Data Review View Developer LocateXL Spatial XL Fite Home Insert z ag es ed Minimum Score 10 g Lat Long Columns Maximum Score 57 E Use Structured Geocoding tax m Geocode Custom Reverse Reverse Show Quick Help Data Data Geocodey Geocode Geocoder Geocode File G Users Desiree Desktop LocateXL RSA New gcd Custom Data file G Users Desiree Desktop LocatexL StreetCentric gcc Reverse Geocoding 5003 5005 Home Mode Settings Help About LocateXL Version 1 1 0 0 E5 X fe Street A B l D F l Go H 1 x Y Geocode Number Geocode Road Geocode Extension Geocode Suffix Geocode Suburb Geocode Town Geocode Province 2 28 08958435 26 19957542 1 Ernest Road Kensington Johannesburg Gauteng 3 28 18476295 26 13233185 35 Palm Street Eden
38. frican countries so please request a specific country should you want to geocode outside of South Africa LocateXL is developed and owned by PrimeThought Software Solutions Company website www PrimeThought biz Product website www LocateXL com Platform product website www SpatialXL com To getregular updates on LocateXL visit the product website at www LocateXL com Prime ThoughtSoftware Prime Thought South Africa Follow our tw eets on Like the Prime Thought page on facebook Linked i http w w w linkedin comcompany prime thought softw are solutions and all our Demo and Training Videos are on Youu YouTube video Placing 5000 addresses on a map in under 5 minutes YouTube video How to download and install LocateXL and SpatialXL YouTube video LocateXL Quick Geocoder in Excel YouTube video LocateXL Geocode town centric addresses in Excel YouTube video LocatexL Geocode road centric addresses in Excel YouTube video LocateXL Unstructured Geocoding in Excel YouTube video LocatexL Classification and Complex Geocoding in Excel YouTube video LocateXL Reverse Geocoding in Excel SpatialXL is an add in to Microsoft Excel 2007 or 2010 which gives Excel full mapping or GIS functionality SpatialXL is needed if you would like to view the data against a map background because LocateXL utilises the mapping environment of SpatialXL to place geocoded points and to check that geocodes are Chapter 1 Introduct
39. ion LocateXL and SpatialXL done correctly against a map background 3 When you purchase the LocateXL product you usually have to either purchase the SpatialXL product alongside or you will get a free copy of SpatialXL depending on the structuring of the particular sale r x id f gt Address Random Sample xlsx Microsoft Excel E X Home Insert Page Layout Formulas Data Review View Developer LocateXL Spatial XL A o EP i Qa Q Minimum Score 10 JH Address Columns Geocode File C Users Desiree Desktop LocateXL RSA New gcd So ead V Use Structured Geocoding No custom data loaded Geocode Custom Geocode Geocode Show Quick Help Home Mode Settings Help About LocateXL Version 1 1 0 0 SpatialXL x Saker wei A Qeg TH a ww Layers q Description A MW amp Address Rando 6 AED E ga ap 17 54 to 20 28 Yi 20 to 96 49 a S Neen CE mE gt E esothg w VI Y Bing maps E South Afte se 3 OK La 4 m Abdi i GCS_WGS_1984 7 4593 23 8211 0 0000 F 1 22 524760 amp 90 00 0 00 0 00 P2 v fe Naude v J K L M N O p Q R 5 T U V WwW X 1 Geocode Geocode Geocode Score Geocode X Y Geocode I Geocode t Geocode Geocode Geocode Geocode Province 2 S 22 38 6 28 13348 27 6496 Naude Street Petrus Ste Petrus Ste Free State 3 N 32 56 14 18 69068 34 0034 Lima Road Malibu VilMfuleni Western Cape 4 N 42 73 68 23 05649 27 6943 Taaibos Street Kathu Kathu Northern C
40. la Cl SANDOWN SANDTON Gauteng 2196 0 Palala Close MorningsiSandton Gauteng S 4 11 10 51 Glanville Ave CYRILDENE JOHANNESBURG Gauteng 2198 51 Glanville Avenue Cyrildene Bedfordvi Gauteng N 4 12 11 34 Spencer Ave SENDERWOOD BEDFORDVIEW Gauteng 2007 0 Spencer Avenue Senderwc Bedfordvi Gauteng S 5 13 12 15 Arbroath Rd BEDFORDVIEW BEDFORDVIEW Gauteng 2007 0 Arbroath Road Bedfordvi Bedfordvi Gauteng S 5 14 13 28 Innes St OBSERVATORY JOHANNESBURG Gauteng 2198 28 Innes Street Observatc Johannes Gauteng N 5 15 14 4Gerard St OBSERVATORY JOHANNESBURG Gauteng 2198 4 Gerard Street Observatc Johannes Gauteng N 5 16 15 14 Granville Rd EDEN GLEN EDENVALE Gauteng 1609 14 Granville Road Eden Gler Edenvale Gauteng N 5 17 16 5 Pietersen St HILLBROW JOHANNESBURG Gauteng 2001 5 Pietersen Street Hillbrow Johannest Gauteng N 5 18 17 203 1st Ave EDENVALE EDENVALE Gauteng 1609 0 1st Avenue Edenvale Edenvale Gauteng Ss 5 19 18 11 Aberfeldy Ave MORNINGSIDE SANDTON Gauteng 2191 11 Aberfeldy Avenue MorningsiSandton Gauteng N 5 20 19 122 Berea Rd BERTRAMS JOHANNESBURG Gauteng 2094 0 Berea Road Bertrams Johannes Gauteng S 5 21 20 8 Aletta Ave HIGHWAY GARDENS GERMISTON Gauteng 1609 0 Aletta Avenue Highway Edenvale Gauteng Ss 4 22 21 58 Rivonia Rd INANDA SANDTON Gauteng 2196 0 Rivonia Road Inanda Sandton Gauteng S 5 Locate Selected Will locate whatever row that the cursor is on or if you select multiple rows will locate the address in thos e rows Chapter 9 Locat
41. me P IsCyrillic e m in em w Matches any word character w I D A 1 3 in ID A1 3 W Matches any non word character W mm in ID A1 3 s Matches any white space character w s D in ID A1 3 S Matches any non white space character s S in int ctr d Matches any decimal digit d 4 in 4 IV D Matches any non decimal digit D y o ety a Anchors Anchors or atomic zero width assertions cause a match to succeed or fail depending on the current position in the string but they do not cause the engine to advance through the string or consume characters The metacharacters listed in the following table are anchors _ Description Pattern Matches Th a 901 in i e match must start at the beginning of the nd 3 string or line 901 333 333 in The match must occur at the end of the string or before n at the end of the line or string Ad l 901 333 901 in A The match must occur at the start of the string A d 3 901 333 333 in The match must occur at the end of the string or 5 before n at the end of the string ARINA l 901 333 333 in Z The match must occur at the end of the string d 3 z 901 333 The match must occur at the point where the 1 3 5 in 1 38 5 7 G G d 4 previous match ended 9 The match must occur on a boundary between a them them in them theme b b w s w b n w alphanumeric and a W nonalphanumeric them
42. ms to ignore Geocode Data Address Parts Crossings Projection Parameters Source Postal Codes Items to ignore Descnpton Used Regular Expression Replacement Click here to add a new row Comma Shop Room Unit 7 s shop room s d abcd s 7 s unit s d abed s 7 ground 1st 4nd 3rd 4th Sth 6th th Sth 9th 10th s floorfflr t 7 towers house building bldg geboulbldg s Floor SS 8s Building Test address shop 23 3rd floor union building pretoria gauteng Result pretoria gauteng Close Save import settings You can ignore certain items in your address such as shop room floor building and so forth to make your results more accurate This is set up in the above tab using regular expressions Using regular expressions is described in the next section Regular Expressions Regular Expression Language Elements A regular expression is a pattern that the regular expression engine attempts to match in input text A pattern consists of one or more character literals operators or constructs Each section in this quick reference lists a particular category of characters operators and constructs that you can use to define regular expressions Character Escapes The backslash character in a regular expression indicates that the character that follows it either is a special character as shown in the following table or should be interpreted lite
43. nd so on This is all described fully in the SpatialXL help Chapter 11 SpatialXL Ribbon SpatialxXL Thematics T Ekiz E W Layer Properties Excel layer Sheet1 O S 28 ee T 2 ae General Data Styling Text Projection Thematics Links Input Transform quae P ie a 5 r y ih Colours Images Charts Themes Scene Tips A 7 Ja A eet are y fa AES Colour Column Geocode Status v Alexan a 5 ro e5 ECS Colour based on number ranges Colour based on text values Amo a J 1 236 390 amp 90 00 0 00 0 00 TON G H l Geococ Geocode Road Geococ y h GO la s Description M GeocodingSample xlsx siale M w EES aE mE mE s I U Bing maps sanos eead Ai IX ziy v The base data set that is used for LocateXL comes from our data partners MaplT www MaplT co za This data set is included in the LocateXL product The postal codes are currently sourced from a different provider but all the national address registry data used for geocoding and reverse geocoding originates from MaplT Our map data partners MaplT provides data for South Africa and African countries d the client The map data and the cost of map data is included in the price for LocateXL TECHNICAL INFORMATION ABOUT LOCATEXL LocateXL is an add in to Excel and is written using the C language You need th
44. on the Bing maps layer and select Zoom fit from the menu if you don t see the map Chapter 9 LocateXL Ribbon LocateXL Ribbon 9 SpatialAL TX E a i 3 rd i H we a CE z COA Layers E ia Description Be een M g GeocodingSampl z 208 VI T Bing maps S En k a z cu on x gt DK th ER Ten i gt Pe ben tol E i ii x i 4 om por z3 GCS_WG5_1984 155 1340 183 7113 0 0000 F4 1 428 177 612 a Crosshair If you want to display a cross hair on the map you follow the following steps Go to the settings button in the spatial area and double click In the Scene Properties Set the Cross Hair colour by double clicking and then Set it in the Select colour dialog Then choose to Show Crosshair make sure the renderer is GDI plus and choose the crosshair thickness 6 Click OK oS 2 o Chapter 9 LocateXL Ribbon 38 LocateXL Ribbon Ee Jz 41 Siemens 5T DimGray LightGray Silver WhiteSmoke B cray C Gainsboro P Darkcray B Maroon O E a Ea Red Brown Firebrick ae IndianRed Snow LightCoral Ss RosyBrown MistyRose salmon Tomato A Darksalmon Chapter 9 LocateXL Ribbon Background Colour Show Axes Renderer Fill mode Show Crosshair Crosshair colour Q L m 3 Crosshair thickness k Ae 199 8351 193 9175 nonno i 4 1498177 617 LocateXL Ribbon SpatialXL HXT SE s E hi we
45. or sales Desiree PrimeThought biz or Desiree LocateXL com Support LocateXL com for Product support Company website www Prime T hought biz Product website www LocateXL com Platform product website www spatialXL com Chapter 16 Contact and Support 2012 PrimeThought Softw are Solutions All rights reserved Product and company names mentioned in this manual may be trademarks or registered trademarks of their respective companies Mention of third party products is for informational purposes only and constitutes neither an endorsement nor a recommendation The author assumes no responsibility w ith regard to the performance or use of these products All understandings agreements or w arranties if any take place directly betw een the vendors and the prospective users Every effort has been made to ensure that the information in this manual is accurate The author is not responsible for printing or clerical errors The product described in this manual incorporates copyright protection technology that is protected by method claims of certain U S patents and other intellectual property rights This user manual w as created with Help amp Manual
46. patialXL please go to Add and Remove Programs in your Control Panel and remove SpatialXL Chapter 3 LocateXL and SpatialXL Download and installation 4 Then run SpatialXL exe following the prompts When successfully installed open Excel 2007 or 2010 and go the the SpatialXL ribbon Open About SpatialXL by clicking on the button and then click on Update License Click Load license from file and choose the SpatialXLLicense lic or other named license file for SpatialXL that you received from PrimeThought it will have the extension lic to bring in the key of the license Then when you click OK your license will be valid and you will be able to use SpatialXL 9 To test that SpatialXL is working go to the Show Spatial Pane button in the SpatialXL Ribbon and make sure that your Spatial Pane appears If it does you have correctly installed and license SpatialXL 10 Close Excel and continue to the download and installation of LocateXL a O20 jee To download and install LocateXL you do the following 1 To download LocateXL you have to get a download link from PrimeThought Software Solutions This might be in the form of an ftp site or an internet website 2 You would generally download two files LocateXL exe AND RSA new gcd or another gcd file You could also download the gcc files called StreetCentric gcc and TownCentric gcc So in other words you might get links that looks like this http www locatex com temp LocateXL ex
47. possible Matches the previous element one or more times but as few times as be be in been be in bent possible Matches the previous element zero or one time but as few times as rai n ran rain possible n Matches the preceding element d3 043 in 1 043 6 876 543 and exactly n times 210 in 9 876 543 210 Matches the previous element at least n n times but as few times as d 2 2 166 29 1930 possible Matches the previous element 166 17668 n m between n and m times but as few d 3 5 times as possible 193 024 in 193024 Backreference Constructs A backreference allows a previously matched subexpression to be identified subsequently in the same regular expression The following table lists the backreference constructs supported by regular expressions Backreference Description Pattern Matches construct Tumbe Backreference Matches the value of a numbered w 1 li 7 subexpression seek Named backreference Matches the value of a named lt char gt w ee in k lt name gt i i expression k lt char gt seek Chapter 9 LocateXL Ribbon 27 28 LocateXL Ribbon 9 Alternation Constructs Alternation constructs modify a regular expression to enable either or matching These constructs include the language elements listed in the following table Alternation Description Pattern Matches construct Matches any one element separated by the ver
48. r suburb to see if it exists You can also add your own aliases so that other known names of particular suburbs also geocode Chapter 9 LocateXL Ribbon 15 16 LocateXL Ribbon 9 Klein Nederburg ay Groenheuwel Groen Heuwel Northem Paarl i smartie Town Fairyland Milky town New Orleans ie Paarlberg Nature Reserve z Groenvlei Chicago Lemoenkloot Town Towns and Cities If you right click on Town in the Settings gt Address Parts dialog and click on View Extension Suffix Suburb Town View Province Move Up Move Down EJ Delete A dialog come up where you can see the names of all of the towns in the data set You can also search for a particular town to see if it exists You can also add your own aliases so that the new names of towns or the old names of towns and cities are taken into account when geocoding Chapter 9 LocateXL Ribbon LocateXL Ribbon 9 Name Aliases Southport Sunwich Part Anerley Polokwane Pietersburg Welkom Esikhawini Mafikeng Khutsong Welverdiend Doornfontein Deelkraal Province If you right click on Province in the Settings gt Address Parts dialog and click on View View Move Up Move Down EJ Delete A dialog come up where you can see the names of all of the provinces in the data set and you can add aliases where needed Chapter 9 Loc
49. rally Escaped Description Pattern Matches character a Matches a bell character u0007 a u0007 in Error u0007 b In a character class matches a backspace b 3 b b b b in b b b b u0008 t Matches a tab u0009 Aw t O e GEA Ie titem2 t Chapter 9 LocateXL Ribbon 23 24 LocateXL Ribbon r v f n e nnn xnn cX CX unnnn Matches a carriage return u000D r is not equivalent to the newline character n Matches a vertical tab UOOOB Matches a form feed u000C Matches a new line u000A Matches an escape u001B Uses octal representation to specify a character nnn consists of up to three digits Uses hexadecimal representation to specify a character nn consists of exactly two digits Matches the ASCII control character that is specified by Xor x where Xor x is the letter of the control character Matches a Unicode character by using hexadecimal representation exactly four digits as represented by nnnn When followed by a character that is not recognized as an escaped character in this and other tables in this topic matches that character For example is the same as x2A This allows the regular expression engine to disambiguate language elements such as or and character literals represented by or Character Classes r n w vit2 2 r n w e w 040 w w x20 w cC w u0020 w
50. road is slightly misspelt the Soundex might still match You use weight for also changing a set to be more suburb or town centric or more road centric Some rural areas might have terribly bad names for the roads in that case your suburb or town weights need to be much higher than roads Remember also to bring down the Soundex scores appropriately Soundex Weight The Soundex is an index for sounds like type of names If you have slightly misspelt a name you can still match it in certain circumstances to the original name depending on how bad the spelling mistake is For instance in the following example have Cros Street and Cross Street in Port Elizabeth They both geocode to Cross Street but the one is a Soundex match at 5 points lower than the other Key ADDR_LINE1 SUBURB TOWN PROV Gec Geocod Geocod Geocode Suburk Geocode Town Geocode Provin X Y Geo Geo Geocode Confide 1 Cros Street PORT ELIZABETH PORT ELIZABETH Eastern Cape 0 Cross Street PortElizabeth PortElizabeth Eastern Cape 25 61328 33 958 S 48 84 21 2 Cross Street PORT ELIZABETH PORT ELIZABETH Eastern Cape 0 Cross Street Port Elizabeth Port Elizabeth Eastern Cape 25 61328 33 958 S 53 92 98 SQL These items can be extracted from a database where available so there is provision here to put in the SQL to do that Our current geocoding is based on shape file loading so this feature is not used currently except for clients who have and maintain there own geocoding databases
51. s 2011 License Status OK Company PTS Machine Expires 2012 02 29 Serial Dew Update License In the Help and About section you get the help documentation and you can also update license information related to SpatialXL Information Geocode File C Users Desiree Desktop LocatexL RSA New gcd Custom Data file C Users Desiree Desktop LocatexLStreetCentric gcc Reverse Geocoding 5003 5003 LocateXL Version 1 1 0 0 The information section of the LocateXL ribbon shows information about the loading progress of the geocode data file the geocode custom file the reverse geocoding progress or the geocoding progress depending on what action you are performing at that particular time Chapter 9 LocateXL Ribbon LocateXL Context Menu 1 0 SANDTOM leantans 2196 0 West Street JOHAN Ut 1001 150 Jeppe Street EDENY 2 Copy 609 24 Price Road BEDFO i Paste Options 1007 0 John Avenue JOHAN E 094 0 Park Street SANDT Paste Special 1196 0 West Street SANDT 1196 0 12th Street SANDY UE 1010 0 Benmore Road JOHAN Belete 1001 1 Bertha Street EDENV ERTEAN 1609 21 Harry Street SANDT Filter 2191 31 Tugela Road BEDFO Sort POO 19 Sovereign Street JOHAN 1001 0 Klein Street iQ Insert Comment JOHAN 1094 1 Ath Avenue EDENY f Format Cells 1609 10 Olienhout Street JOHAN Pick From Drop down List 9994 A 5th Avenue JOHAR Define Name 001 0 De Korte Street EDEN Hyperlink 1609 O Harris
52. ss than great road data one would load and work with TownCentric gcc You could also save your own set of aliases for every different address part and save these in a particular gcc file Chapter 9 LocateXL Ribbon 29 LocateXL Ribbon 9 Open This you use when you have an already saved gcc file that you would like to use x Browse for custom data file Organize New folder UY Favorites Name Date modified Type ME Desktop StreetCentric gec 2012 02 11 05 28 GCC File dp Downloads TownCentric gec 2012 02 11 05 19 GCC File Recent Places Libraries E3 Documents al Music Pictures Videos el Cornuuter hii File name Save You can save the current gcc file that you are working with by clicking save save As You can save as to create a newly named gcc file 30 Chapter 9 LocateXL Ribbon LocateXL Ribbon Geocode This is the button you use for geocoding and it gives different options fa Geocode Show Qui F Geocoder Locate All ki Locate Selected ia Detailed Locations Zoom on Map See aM ane bt fe ot Be bo 1S Click on a button to show more information about that Locate All Locate All will locate all of the records on the current spreadsheet The information section on the ribbon will show the progress Fie E Home Insert Page Layout Formulas Data Review View az a a ed Minimum Score 10 HH Address Columns
53. tical bar th elis at character the this in this is the day Matches yes if expression matches otherwise matches the expression yes no optional no part expression is interpreted as a zero width assertion Z A A d 2 bib d 3 A10 910 in A10 C103 910 Matches yes if the named Capture ia uoted gt Dogs jpg Yiska name yes no name has a match otherwise a a Pi playing jpg in Dogs jpg quoted S s mz D ea matches the optional no Yiska playing jpg Substitutions Substitutions are regular expression language elements that are supported in replacement patterns The metacharacters listed in the following table are atomic zero width assertions Replacement h D p Character escription attern pattern Input string Result string Substitutes the substring number matched by group b w s w o 3 2 1 one two two one number Substitutes the substring b lt word1 gt w s word2 name SS the named lt word2 gt ws b wordt one two two one Substitutes a literal b d s USD 1 103 USD 103 amp Substitutes a copy of the d d 9 F amp 1 30 next 30 whole match C Ini Substitutes all the text of the input string before the B AABBCC AAAACC match Substitutes all the text of the input string after the B AABBCC AACCCC match 4 Substitutes the last group B C 4 AABBCCDD y AccpD that was captured
54. wn level means it geocoded to a point of interest s location P means it geocoded to postal code F means the geocode failed Geocode score For a more complete description of geocode score please look at the Parameters section Example 29 is the score of the geocode This is calculated out of the total possible score as discussed previously Geocode confidence Then we give a percentage confidence which is the score out of the total possible score expressed as a percentage so in this case 82 85 percent This way you can look at lower scoring records and see if the settings or the records can be refined to score higher confidence levels and of course most importantly you will get the X coordinate and the Y coordinate of the geocoded data so that you may place these on a map Reverse geocoding is to get address data for X and Y locations Reverse Show Quick Reverse ay Geocode Geocode 7 Geocoder be G Geacode ka nae Reverse Geocode qz e Install SpatialXL Install LocateXL Load the geocode data file gcd Load a geocode custom file if you have any gcc Open your spreadsheet with the X and Y coordinates that you would like to reverse geocode Choose the lat long columns Choose your rows and Locate Selected or Locate All Place data on map Place data on map To do this you need to follow the steps to turn on SpatialXL spatial pane Set sc
Download Pdf Manuals
Related Search