vastorbit.sql.geo.coordinate_converter

vastorbit.sql.geo.coordinate_converter(vdf: Annotated[str | VastFrame, ''], x: str, y: str, x0: float = 0.0, earth_radius: Annotated[int | float | Decimal, 'Python Numbers'] = 6371, reverse: bool = False) → VastFrame

Converts between geographic coordinates (latitude and longitude) and Euclidean coordinates (x,y).

Parameters:

• vdf (SQLRelation) – Input VastFrame.

• x (str) – VastColumn used as the abscissa (longitude).

• y (str) – VastColumn used as the ordinate (latitude).

• x0 (float, optional) – The initial abscissa.

• earth_radius (PythonNumber, optional) – Earth radius in km.

• reverse (bool, optional) – If set to True, the Euclidean coordinates are converted to latitude and longitude.

Returns:

result of the transformation.

Return type:

VastFrame

Examples

For this example, we will use the Cities dataset.

import vastorbit.datasets as vod

cities = vod.load_cities()

	Abc name Varchar(82)	Abc geometry Varchar
1	Vatican City	POINT (12.45338654497177 41.90328217996012)
2	San Marino	POINT (12.44177015780014 43.936095834768)
3	Vaduz	POINT (9.516669472907267 47.13372377429357)
4	Luxembourg	POINT (6.130002806227083 49.61166037912108)
5	Palikir	POINT (158.1499743237623 6.916643696007725)
6	Majuro	POINT (171.3800001757465 7.103004311216239)
7	Funafuti	POINT (179.2166470940289 -8.516651999041073)
8	Melekeok	POINT (134.6265484669922 7.487396172977981)
9	Monaco	POINT (7.406913173465057 43.73964568785249)
10	Tarawa	POINT (173.0175708285494 1.338187505624603)
11	Moroni	POINT (43.24024409869332 -11.70415769566847)
12	Andorra	POINT (1.51648596050552 42.5000014435459)
13	Port-of-Spain	POINT (-61.51703088544974 10.65199708957726)
14	Kigali	POINT (30.05858591906411 -1.95164421006325)
15	Mbabane	POINT (31.13333451205636 -26.31665077840921)
16	Juba	POINT (31.58002559278731 4.829975198277964)
17	Ljubljana	POINT (14.51496903347413 46.0552883087945)
18	Bratislava	POINT (17.11698075223461 48.15001832996171)
19	Doha	POINT (51.5329678942993 25.28655600890659)
20	Podgorica	POINT (19.26630692411823 42.46597251288171)
21	Bern	POINT (7.466975462482424 46.91668275866772)
22	Pristina	POINT (21.16598425159987 42.66670961411938)
23	Roseau	POINT (-61.38701298180337 15.30101564428332)
24	Djibouti	POINT (43.14800166705226 11.59501446425548)
25	Banjul	POINT (-16.5917014892126 13.45387646031594)
26	Skopje	POINT (21.4334614651425 42.00000612290586)
27	Bridgetown	POINT (-59.61652673505159 13.10200258275114)
28	Bujumbura	POINT (29.3600060615284 -3.376087220374643)
29	Kingstown	POINT (-61.21206242027932 13.14827882786841)
30	Castries	POINT (-61.00000818036955 14.00197348933034)
31	Basseterre	POINT (-62.71700931969934 17.30203045548939)
32	Port Louis	POINT (57.49999385460973 -20.16663857135325)
33	Saint George's	POINT (-61.74164322607476 12.05263340172041)
34	Manama	POINT (50.58305171591019 26.23613629048594)
35	Saint John's	POINT (-61.85003381513866 17.11803651831411)
36	Montevideo	POINT (-56.17299814703597 -34.85609570759073)
37	Lome	POINT (1.22081126074562 6.133882930268385)
38	Tunis	POINT (10.17967809921203 36.80277813623144)
39	Abu Dhabi	POINT (54.36659338259199 24.46668357237991)
40	Ashgabat	POINT (58.38329911177465 37.94999493311099)
41	Lusaka	POINT (28.28138173611427 -15.41469840933593)
42	Harare	POINT (31.04276357206282 -17.8158438357778)
43	Dili	POINT (125.5794559317051 -8.559388408546454)
44	Port Vila	POINT (168.3166405835686 -17.73335040402583)
45	Tegucigalpa	POINT (-87.2194751979415 14.1039907590764)
46	Georgetown	POINT (-58.16702864748061 6.801973692752028)
47	Reykjavík	POINT (-21.95001448717954 64.15002361973922)
48	Port-au-Prince	POINT (-72.33798044690553 18.54297045473237)
49	Kampala	POINT (32.58137766712105 0.318604813383331)
50	Paramaribo	POINT (-55.16703088542437 5.835030129922586)
51	Niamey	POINT (2.114710186530374 13.51865181050647)
52	Dushanbe	POINT (68.77387935270173 38.56003521631658)
53	Asuncion	POINT (-57.64345102790134 -25.29445711705768)
54	Managua	POINT (-86.27043751890119 12.15496243875612)
55	Freetown	POINT (-13.23616159901275 8.471957271098177)
56	Islamabad	POINT (73.16468862105955 33.70194180895959)
57	Kathmandu	POINT (85.31469635222788 27.71863777247722)
58	Bloemfontein	POINT (26.22991288117737 -29.1199938773787)
59	Pretoria	POINT (28.22748321723384 -25.70497469518443)
60	Port Moresby	POINT (147.1925036205936 -9.464707825867777)
61	Honiara	POINT (159.9497657336057 -9.437994295089595)
62	Panama City	POINT (-79.53498301041077 8.969963049094872)
63	Rabat	POINT (-6.83640815612614 34.02530731107282)
64	Chisinau	POINT (28.85771113965143 47.00502361967062)
65	Maputo	POINT (32.58721710397009 -25.95333162877898)
66	Mogadishu	POINT (45.36473175245874 2.068627192947531)
67	Muscat	POINT (58.59331213260884 23.61332480772813)
68	Colombo	POINT (79.85775060925641 6.931965758182116)
69	Ulaanbaatar	POINT (106.9146699037465 47.91861925856074)
70	Wellington	POINT (174.7832742912769 -41.29997393927641)
71	Windhoek	POINT (17.08354610054181 -22.57000608438381)
72	Abuja	POINT (7.53138214293233 9.085279007754195)
73	Bissau	POINT (-15.59836084132075 11.86502382298056)
74	Amman	POINT (35.93135406687412 31.95197110582745)
75	Vilnius	POINT (25.3166352932829 54.68336631175862)
76	Riga	POINT (24.09996537140319 56.95002382316096)
77	Bishkek	POINT (74.58325836390367 42.87502530509011)
78	Maseru	POINT (27.48327306998448 -29.31667437868163)
79	Antananarivo	POINT (47.51467804152986 -18.91469149203215)
80	Quito	POINT (-78.5019969671124 -0.2130423220355624)
81	San Jose	POINT (-84.08599721127536 9.936958288356607)
82	San Salvador	POINT (-89.2049870794599 13.71194750549404)
83	Kingston	POINT (-76.7674337136691 17.9770766238309)
84	Ndjamena	POINT (15.0472024554623 12.11504239481064)
85	Malabo	POINT (8.783277545821136 3.750015278026183)
86	Asmara	POINT (38.93332352575931 15.33333925268192)
87	Zagreb	POINT (15.99999466824568 45.80000673327254)
88	Tallinn	POINT (24.72804072947855 59.43387737948592)
89	Lilongwe	POINT (33.78330195998353 -13.98329506546918)
90	Guatemala	POINT (-90.52891143656154 14.62308052144817)
91	Libreville	POINT (9.457965045823698 0.3853886097185182)
92	Suva	POINT (178.4417073153799 -18.13301593137123)
93	Nouakchott	POINT (-15.97534041489001 18.08642702124752)
94	Bamako	POINT (-8.001984963249697 12.65196052632325)
95	Beirut	POINT (35.50776235137766 33.8739209756269)
96	Tbilisi	POINT (44.78884959099798 41.72695584707759)
97	Astana	POINT (71.427774209483 51.18112530425759)
98	Vientiane	POINT (102.5999800201548 17.96669272762739)
99	Brazzaville	POINT (15.28274363384867 -4.257239913197509)
100	Conakry	POINT (-13.68218088612394 9.53346870502179)

Rows: 1-100 | Columns: 2

Note

vastorbit offers a wide range of sample datasets that are ideal for training and testing purposes. You can explore the full list of available datasets in the Datasets, which provides detailed information on each dataset and how to use them effectively. These datasets are invaluable resources for honing your data analysis and machine learning skills within the vastorbit environment.

Let’s extract the latitude and longitude.

cities["lat"] = "ST_X(geometry)"
cities["lon"] = "ST_Y(geometry)"
display(cities)

	Abc name Varchar(82) 100%	🌎 geometry Geometry 100%	🌎 lat Double 100%	🌎 lon Double 100%
1	Vatican City	POINT (12.45338654497177 41.90328217996012)	12.45338654497177	41.90328217996012
2	San Marino	POINT (12.44177015780014 43.936095834768)	12.44177015780014	43.936095834768
3	Vaduz	POINT (9.516669472907267 47.13372377429357)	9.516669472907267	47.13372377429357
4	Luxembourg	POINT (6.130002806227083 49.61166037912108)	6.130002806227083	49.61166037912108
5	Palikir	POINT (158.1499743237623 6.916643696007725)	158.1499743237623	6.916643696007725
6	Majuro	POINT (171.3800001757465 7.103004311216239)	171.3800001757465	7.103004311216239
7	Funafuti	POINT (179.2166470940289 -8.516651999041073)	179.2166470940289	-8.516651999041073
8	Melekeok	POINT (134.6265484669922 7.487396172977981)	134.6265484669922	7.487396172977981
9	Monaco	POINT (7.406913173465057 43.73964568785249)	7.406913173465057	43.73964568785249
10	Tarawa	POINT (173.0175708285494 1.338187505624603)	173.0175708285494	1.338187505624603
11	Moroni	POINT (43.24024409869332 -11.70415769566847)	43.24024409869332	-11.70415769566847
12	Andorra	POINT (1.51648596050552 42.5000014435459)	1.51648596050552	42.5000014435459
13	Port-of-Spain	POINT (-61.51703088544974 10.65199708957726)	-61.51703088544974	10.65199708957726
14	Kigali	POINT (30.05858591906411 -1.95164421006325)	30.05858591906411	-1.95164421006325
15	Mbabane	POINT (31.13333451205636 -26.31665077840921)	31.13333451205636	-26.31665077840921
16	Juba	POINT (31.58002559278731 4.829975198277964)	31.58002559278731	4.829975198277964
17	Ljubljana	POINT (14.51496903347413 46.0552883087945)	14.51496903347413	46.0552883087945
18	Bratislava	POINT (17.11698075223461 48.15001832996171)	17.11698075223461	48.15001832996171
19	Doha	POINT (51.5329678942993 25.28655600890659)	51.5329678942993	25.28655600890659
20	Podgorica	POINT (19.26630692411823 42.46597251288171)	19.26630692411823	42.46597251288171

Let’s leverage the coordinate_converter function to calculate Euclidean distances. We’ll project the latitude and longitude into x, y coordinates.

from vastorbit.sql.geo import coordinate_converter

convert_xy = coordinate_converter(cities, "lon", "lat")
display(convert_xy)

	Abc name Varchar(82) 100%	🌎 geometry Geometry 100%	🌎 lat Double 100%	🌎 lon Double 100%
1	Vatican City	POINT (12.45338654497177 41.90328217996012)	1395.7870907061615	4659.432388166911
2	San Marino	POINT (12.44177015780014 43.936095834768)	1394.464313692637	4885.470953395329
3	Vaduz	POINT (9.516669472907267 47.13372377429357)	1063.1048727305497	5241.030957567467
4	Luxembourg	POINT (6.130002806227083 49.61166037912108)	682.9293266047553	5516.56493657113
5	Palikir	POINT (158.1499743237623 6.916643696007725)	nan	769.0956884041286
6	Majuro	POINT (171.3800001757465 7.103004311216239)	nan	789.8180433416741
7	Funafuti	POINT (179.2166470940289 -8.516651999041073)	nan	-947.0084942906066
8	Melekeok	POINT (134.6265484669922 7.487396172977981)	nan	832.5604682130363
9	Monaco	POINT (7.406913173465057 43.73964568785249)	825.9148342058396	4863.626693719747
10	Tarawa	POINT (173.0175708285494 1.338187505624603)	nan	148.79966152459278
11	Moroni	POINT (43.24024409869332 -11.70415769566847)	5342.626251721591	-1301.4429564062034
12	Andorra	POINT (1.51648596050552 42.5000014435459)	168.64523667710458	4725.784542908726
13	Port-of-Spain	POINT (-61.51703088544974 10.65199708957726)	-8735.733719538395	1184.4480349935966
14	Kigali	POINT (30.05858591906411 -1.95164421006325)	3507.153915093358	-217.01293477426088
15	Mbabane	POINT (31.13333451205636 -26.31665077840921)	3645.992942090856	-2926.278052835681
16	Juba	POINT (31.58002559278731 4.829975198277964)	3704.1583176657364	537.0687378675563
17	Ljubljana	POINT (14.51496903347413 46.0552883087945)	1631.5369931940181	5121.114405090408
18	Bratislava	POINT (17.11698075223461 48.15001832996171)	1932.2819436639877	5354.037756134252
19	Doha	POINT (51.5329678942993 25.28655600890659)	6708.601419094414	2811.7367405038945
20	Podgorica	POINT (19.26630692411823 42.46597251288171)	2183.868029595881	4722.0006984597285

We can effortlessly reverse the operation.

convert_reverse_xy = coordinate_converter(convert_xy, "lon", "lat", reverse = True)
display(convert_reverse_xy)

	Abc name Varchar(82) 100%	🌎 geometry Geometry 100%	🌎 lat Double 100%	🌎 lon Double 100%
1	Vatican City	POINT (12.45338654497177 41.90328217996012)	12.453386544971766	41.90328217996012
2	San Marino	POINT (12.44177015780014 43.936095834768)	12.441770157800145	43.93609583476798
3	Vaduz	POINT (9.516669472907267 47.13372377429357)	9.516669472907264	47.13372377429357
4	Luxembourg	POINT (6.130002806227083 49.61166037912108)	6.130002806227084	49.61166037912108
5	Palikir	POINT (158.1499743237623 6.916643696007725)	nan	6.916643696007725
6	Majuro	POINT (171.3800001757465 7.103004311216239)	nan	7.103004311216238
7	Funafuti	POINT (179.2166470940289 -8.516651999041073)	nan	-8.516651999041073
8	Melekeok	POINT (134.6265484669922 7.487396172977981)	nan	7.48739617297798
9	Monaco	POINT (7.406913173465057 43.73964568785249)	7.4069131734650515	43.73964568785248
10	Tarawa	POINT (173.0175708285494 1.338187505624603)	nan	1.338187505624603
11	Moroni	POINT (43.24024409869332 -11.70415769566847)	43.24024409869333	-11.704157695668473
12	Andorra	POINT (1.51648596050552 42.5000014435459)	1.5164859605055188	42.50000144354589
13	Port-of-Spain	POINT (-61.51703088544974 10.65199708957726)	-61.51703088544973	10.65199708957726
14	Kigali	POINT (30.05858591906411 -1.95164421006325)	30.058585919064114	-1.9516442100632503
15	Mbabane	POINT (31.13333451205636 -26.31665077840921)	31.13333451205635	-26.316650778409205
16	Juba	POINT (31.58002559278731 4.829975198277964)	31.580025592787322	4.829975198277965
17	Ljubljana	POINT (14.51496903347413 46.0552883087945)	14.514969033474136	46.0552883087945
18	Bratislava	POINT (17.11698075223461 48.15001832996171)	17.116980752234614	48.150018329961725
19	Doha	POINT (51.5329678942993 25.28655600890659)	51.53296789429928	25.28655600890659
20	Podgorica	POINT (19.26630692411823 42.46597251288171)	19.266306924118226	42.465972512881706

Note

This function can be employed to operate on the Euclidean plane instead of a sphere, significantly improving computation speed.