Skip to content

Instantly share code, notes, and snippets.

@gitfvb

gitfvb/readme.md

Last active January 31, 2023 12:55
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save gitfvb/65508e2be74c76a08bd3849c9b59fe00 to your computer and use it in GitHub Desktop.
Save gitfvb/65508e2be74c76a08bd3849c9b59fe00 to your computer and use it in GitHub Desktop.
Download ZIP
Using geo calculations in FastStats
Raw
readme.md

This is also especially important for german locale systems

Define longitude and latitude variables in Designer as Numeric with Precision of 4 and Geographic Format Latitude or Longitude. The name of the variable is not important.

grafik

The best is to create a csv file that contains the store number, name and the columns Longitude and Latitude. Best is tab delimited. The coordinates in this file should also use the . as decimal place as the server is doing the calculation in expressions. To use the file to visualise your stores on a map, please use , as a decimal place for lat/long. So in the end you need a file for server calculation and another file for client visualisation.

Then to bring the stores together with the customers make an expression like

GeoDistMin(
	
	/* Coordinates of customer */
	[KUO-Latitude];[KUO-Longitude]
	
	/* List of Latitude */
	;NumericListFromFile(	"Public:\stores.csv";5;1)
	
	/* List of Longitude */
	;NumericListFromFile("Public:\stores.csv";4;1)
		
	;"km"

)

to calculate the distance to the nearest store. The NumericListFromFile("Public:\stores.csv";4;1) skips the first 1 header line and reads the 4th column.

To get the name of the nearest store use something like this

Index(

	/* Find out the index of the nearest store location */
	GeoNearest(
	
  	/* Coordinates of customer */
		[KUO-Latitude];[KUO-Longitude]
		
		/* List of Latitude */
		;NumericListFromFile("Public:\stores.csv";5;1)
		
		/* List of Longitude */
		;NumericListFromFile("Public:\stores.csv";4;1)
		
	)
	
	/* Find out the Name of the nearest store location */
	;TextListFromFile("Public:\stores.csv";2;1)
	
)
Raw
stores_address.csv
We can make this file beautiful and searchable if this error is corrected: It looks like row 2 should actually have 3 columns, instead of 2. in line 1.
Aquis Plaza, Adalbertstraße 100, 52062 Aachen
Aachener Straße 1253, 50858 Köln
Breite Straße 80-90, 50667 Köln
Friedrichstraße 133, 40217 Düsseldorf
Mittelstraße 18, 40213 Düsseldorf
Remigiusplatz 5, 53111 Bonn
Centroallee 100, 46047 Oberhausen
Ruhr Park, Am Einkaufszentrum, 44791 Bochum
Löhrstraße 56-58, 56068 Koblenz
Simeonstraße 45, 54290 Trier
Westenhellweg 85-89, 44137 Dortmund
Am Bahnhof 40, 57072 Siegen
MÜNSTER ARKADEN, Ludgeristraße 100, 48143 Münster
Kirchgasse 6, 65185 Wiesbaden
Zeil 65-69, 60313 Frankfurt am Main
Kamp 49, 49074 Osnabrück
Bahnhofstraße 43, 33602 Bielefeld
Q7 1, 68161 Mannheim
Königsplatz 53, 34117 Kassel
Groner Straße 39, 37073 Göttingen
Deutschhofstraße 19, 74072 Heilbronn
Lange Straße 66, 26122 Oldenburg
Wilhelmstraße 26, 71638 Ludwigsburg
Ernst-August-Platz 2, 30159 Hannover
Sophienstraße 21, 70178 Stuttgart
Oase im Weserpark, Hans-Bredow-Straße 19, 28307 Bremen
Anger 62/63, 99084 Erfurt
Damm 11, 38100 Braunschweig
Ritterbrunnen 1, 38100 Braunschweig
Porschestraße 45, 38440 Wolfsburg
Nürnberger Straße 7, 91052 Erlangen
Breite Gasse 59, 90402 Nürnberg
Elbe-Einkaufszentrum, Osdorfer Landstraße 131/135, 22609 Hamburg
Mercado, Ottenser Hauptstraße 20, 22765 Hamburg
Europa Passage, Ballindamm 40, 20095 Hamburg
Spitalerstraße 22-26, 20095 Hamburg
Hamburger Straße 27, 22083 Hamburg
Leipziger Straße 95, 06108 Halle (Saale)
Heegbarg 31, 22391 Hamburg
Annastraße 39, 86150 Augsburg
Am Westpark 6, 85057 Ingolstadt
Grimmaische Straße 29, 04109 Leipzig
Paunsdorfer Allee 1, 04329 Leipzig
Citti-Park, Herrenholz 14, 23556 Lübeck
Breite Straße 51, 23552 Lübeck
Donau-Einkaufszentrum, Weichser Weg 5, 93059 Regensburg
Mühlendamm 1, 24113 Kiel
Sophienhof, Sophienblatt 20, 24103 Kiel
Pasinger Bahnhofsplatz 5, 81241 München
Karlsplatz 11, 80335 München
Sendlinger Straße 37, 80331 München
Holm 1-3, 24937 Flensburg
Willy-Brandt-Platz 5, 81829 München
Stern-Center 1, 14480 Potsdam
Klosterstraße 3, 13581 Berlin
Schloßstraße 30, 12163 Berlin
Schloßstraße 12, 12163 Berlin
Prager Straße 1, 01069 Dresden
Johannisthaler Chaussee 317, 12351 Berlin
Grunerstraße 20, 10179 Berlin
Schönhauser Allee 80, 10439 Berlin
Frankfurter Allee 111, 10247 Berlin
A10 Center, Chausseestraße 1, 15745 Wildau
Raw
stores_server.csv
We can make this file beautiful and searchable if this error is corrected: No commas found in this CSV file in line 0.
7013 Aachen - Aquis Plaza de 50.7746175 6.0930914
7017 Köln - Rhein-Center de 50.9375977 6.8356296
7049 Köln - Quincy de 50.9389923 6.9504161999999994
7034 Düsseldorf - Arcaden de 51.2083854 6.774566
7037 Düsseldorf - Mittelstrasse de 51.2240106 6.7744886
7025 Bonn - Remigiusplatz de 50.734588 7.1015136
7015 Oberhausen - CentrO de 51.4907715 6.8809857999999986
7040 Bochum - Ruhr Park de 51.4942284 7.2816917000000014
7047 Koblenz - Löhrstrasse de 50.3589916 7.5930326
7051 Trier - Simeonstraße de 49.7577001 6.6419973999999993
7046 Dortmund - Westenhellweg de 51.5142789 7.4592034000000007
7060 Siegen - City Galerie de 50.8741699 8.0151297
7018 Münster - Münster Arkaden de 51.9602122 7.6269366000000014
7045 Wiesbaden - Kirchgasse de 50.0786591 8.238030199999999
7053 Frankfurt - Zeil de 50.1145087 8.685638599999999
7016 Osnabrück - Kamp de 52.273528199999987 8.047303
7033 Bielefeld - Bahnhofstrasse de 52.0267782 8.5312951
7023 Mannheim - Q6 Q7 de 49.4868502 8.4727184
7029 Kassel - Königsplatz de 51.315368 9.4969896
7054 Göttingen - Groner Straße de 51.5320943 9.9340520999999988
7039 Heilbronn de 49.1400367 9.2162173
7004 Oldenburg de 53.140719999999988 8.213099999999999
7030 Ludwigsburg - Wilhelm Galerie de 48.8970709 9.1885997
7005 Hannover - Ernst-August-Galerie de 52.377806500000013 9.7382774
7020 Stuttgart - Das Gerber de 48.7717669 9.1724473
7006 Bremen - Weserpark de 53.0491296 8.9577218
7044 Erfurt - Anger de 50.9764559 11.0335712
7061 Braunschweig - Damm de 52.2624846 10.5231056
7019 Braunschweig - Schloss-Arkaden de 52.2630596 10.5275427
7063 Wolfsburg - City-Galerie de 52.4221485 10.7888258
7036 Erlangen - Erlangen Arcaden de 49.5926373 11.0039522
7050 Nürnberg - Breite Gasse de 49.4500283 11.0740434
7001 Hamburg - Elbe Einkaufszentrum (EEZ) de 53.5707099 9.8624983
7011 Hamburg - Mercado Altona de 53.5521638 9.9307971999999989
7010 Hamburg - Europa Passage de 53.5513847 9.9959516
7055 Hamburg - Spitalerstraße de 53.5514508 10.000541
7000 Hamburg Meile de 53.5736082 10.0322208
7058 Halle (Saale) de 51.4813383 11.9737611
7002 Hamburg - Alstertal-Einkaufszentrum de 53.6539964 10.0902617
7041 Augsburg - Annastrasse de 48.3670278 10.895112
7038 Ingolstadt - Am Westpark de 48.7756684 11.3898565
7035 Leipzig - Grimmaische straße de 51.3395951 12.3793838
7024 Leipzig - Paunsdorf Center de 51.3470155 12.4765289
7014 Lübeck - Citti-Park de 53.858083000000008 10.6284304
7065 Lübeck de 53.868355400000013 10.6867354
7043 Regensburg - Donau-Einkaufszentrum. Weichser Weg de 49.025617999999987 12.1160558
7066 Kiel. Citti-Park de 54.3123662 10.0988589
7008 Kiel - Sophienhof de 54.316299099999988 10.1304738
7057 München - Pasinger Bahnhofsplatz de 48.149161799999987 11.4609808
7032 München - Stachus Passagen de 48.1386418 11.5658465
7056 München - Sendlinger Straße de 48.134523400000013 11.5689606
7042 Flensburg - Holm de 54.785927699999988 9.4344734
7027 München - Riem Arcaden de 48.1321982 11.6905527
7031 Potsdam - Stern-center de 52.3715321 13.1329835
7026 Berlin - Spandau Arcaden de 52.533816300000012 13.197043
7059 Berlin - Schloßstraße de 52.4588001 13.3219457
7012 Berlin - Boulevard Berlin de 52.462313 13.3252137
7028 Dresden - Prager Straße de 51.0417133 13.7341617
7067 Gropius Passagen. Berlin de 52.430829300000013 13.4559258
7022 Berlin - Alexa de 52.5199027 13.4147937
7021 Berlin - Schönhauser Allee Arcaden de 52.5497574 13.4149401
7052 Berlin - Berlin Ring-Center de 52.5142282 13.4738877
7007 Wildau - A10 Center de 52.313510699999988 13.6072511
Raw
stores_UI.csv
We can make this file beautiful and searchable if this error is corrected: It looks like row 2 should actually have 1 column, instead of 3. in line 1.
id name country latitude longitude
7013 Aachen - Aquis Plaza de 50,7746175 6,0930914
7017 Köln - Rhein-Center de 50,9375977 6,8356296
7049 Köln - Quincy de 50,9389923 6,9504161999999994
7034 Düsseldorf - Arcaden de 51,2083854 6,774566
7037 Düsseldorf - Mittelstrasse de 51,2240106 6,7744886
7025 Bonn - Remigiusplatz de 50,734588 7,1015136
7015 Oberhausen - CentrO de 51,4907715 6,8809857999999986
7040 Bochum - Ruhr Park de 51,4942284 7,2816917000000014
7047 Koblenz - Löhrstrasse de 50,3589916 7,5930326
7051 Trier - Simeonstraße de 49,7577001 6,6419973999999993
7046 Dortmund - Westenhellweg de 51,5142789 7,4592034000000007
7060 Siegen - City Galerie de 50,8741699 8,0151297
7018 Münster - Münster Arkaden de 51,9602122 7,6269366000000014
7045 Wiesbaden - Kirchgasse de 50,0786591 8,238030199999999
7053 Frankfurt - Zeil de 50,1145087 8,685638599999999
7016 Osnabrück - Kamp de 52,273528199999987 8,047303
7033 Bielefeld - Bahnhofstrasse de 52,0267782 8,5312951
7023 Mannheim - Q6 Q7 de 49,4868502 8,4727184
7029 Kassel - Königsplatz de 51,315368 9,4969896
7054 Göttingen - Groner Straße de 51,5320943 9,9340520999999988
7039 Heilbronn de 49,1400367 9,2162173
7004 Oldenburg de 53,140719999999988 8,213099999999999
7030 Ludwigsburg - Wilhelm Galerie de 48,8970709 9,1885997
7005 Hannover - Ernst-August-Galerie de 52,377806500000013 9,7382774
7020 Stuttgart - Das Gerber de 48,7717669 9,1724473
7006 Bremen - Weserpark de 53,0491296 8,9577218
7044 Erfurt - Anger de 50,9764559 11,0335712
7061 Braunschweig - Damm de 52,2624846 10,5231056
7019 Braunschweig - Schloss-Arkaden de 52,2630596 10,5275427
7063 Wolfsburg - City-Galerie de 52,4221485 10,7888258
7036 Erlangen - Erlangen Arcaden de 49,5926373 11,0039522
7050 Nürnberg - Breite Gasse de 49,4500283 11,0740434
7001 Hamburg - Elbe Einkaufszentrum (EEZ) de 53,5707099 9,8624983
7011 Hamburg - Mercado Altona de 53,5521638 9,9307971999999989
7010 Hamburg - Europa Passage de 53,5513847 9,9959516
7055 Hamburg - Spitalerstraße de 53,5514508 10,000541
7000 Hamburg Meile de 53,5736082 10,0322208
7058 Halle (Saale) de 51,4813383 11,9737611
7002 Hamburg - Alstertal-Einkaufszentrum de 53,6539964 10,0902617
7041 Augsburg - Annastrasse de 48,3670278 10,895112
7038 Ingolstadt - Am Westpark de 48,7756684 11,3898565
7035 Leipzig - Grimmaische straße de 51,3395951 12,3793838
7024 Leipzig - Paunsdorf Center de 51,3470155 12,4765289
7014 Lübeck - Citti-Park de 53,858083000000008 10,6284304
7065 Lübeck de 53,868355400000013 10,6867354
7043 Regensburg - Donau-Einkaufszentrum, Weichser Weg de 49,025617999999987 12,1160558
7066 Kiel, Citti-Park de 54,3123662 10,0988589
7008 Kiel - Sophienhof de 54,316299099999988 10,1304738
7057 München - Pasinger Bahnhofsplatz de 48,149161799999987 11,4609808
7032 München - Stachus Passagen de 48,1386418 11,5658465
7056 München - Sendlinger Straße de 48,134523400000013 11,5689606
7042 Flensburg - Holm de 54,785927699999988 9,4344734
7027 München - Riem Arcaden de 48,1321982 11,6905527
7031 Potsdam - Stern-center de 52,3715321 13,1329835
7026 Berlin - Spandau Arcaden de 52,533816300000012 13,197043
7059 Berlin - Schloßstraße de 52,4588001 13,3219457
7012 Berlin - Boulevard Berlin de 52,462313 13,3252137
7028 Dresden - Prager Straße de 51,0417133 13,7341617
7067 Gropius Passagen, Berlin de 52,430829300000013 13,4559258
7022 Berlin - Alexa de 52,5199027 13,4147937
7021 Berlin - Schönhauser Allee Arcaden de 52,5497574 13,4149401
7052 Berlin - Berlin Ring-Center de 52,5142282 13,4738877
7007 Wildau - A10 Center de 52,313510699999988 13,6072511
@gitfvb
Copy link
Author

gitfvb commented Jan 31, 2023

Some example files are attached here as files

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment