Feedback

×

• Feedback
• Was this page helpful?*
  • Yes
  • No
• Do you have any additional feedback?
  • Yes
  • No
• Additional Feedback

verticapy.sql.geo.create_index

verticapy.sql.geo.create_index(vdf: Annotated[str | vDataFrame, ''], gid: str, g: str, index: str, overwrite: bool = False, max_mem_mb: int = 256, skip_nonindexable_polygons: bool = False) → TableSample

Creates a spatial index on a set of polygons to speed up spatial intersection with a set of points.

Parameters

vdf: SQLRelation

vDataFrame used to compute the spatial join.

gid: str

Name of an integer column that uniquely identifies the polygon. The gid cannot be NULL.

g: str

Name of a geometry or geography (WGS84) column or expression that contains polygons and multipolygons. Only polygon and multipolygon can be indexed. Other shape types are excluded from the index.

index: str

Name of the index.

overwrite: bool, optional

BOOLEAN value that specifies whether to overwrite the index, if an index exists.

max_mem_mb: int, optional

A positive integer that assigns a limit to the amount of memory in megabytes that create_index can allocate during index construction.

skip_nonindexable_polygons: bool, optional

In rare cases, intricate polygons (for instance, those with too high resolution or anomalous spikes) cannot be indexed. These polygons are considered non-indexable. When set to False, non-indexable polygons cause the index creation to fail. When set to True, index creation can succeed by excluding non-indexable polygons from the index.

Returns

TableSample

geospatial indexes.

Examples

For this example, we will use the Cities and World dataset.

import verticapy.datasets as vpd

cities = vpd.load_cities()
world = vpd.load_world()

	Abc city Varchar(82)	Abc Long varchar(2411724)
1	Abu Dhabi
2	Amsterdam
3	Apia
4	Ashgabat
5	Bangui
6	Budapest
7	Bujumbura
8	Castries
9	Colombo
10	Conakry
11	Cotonou
12	Dakar
13	Dublin
14	Georgetown
15	Havana
16	Kuala Lumpur
17	København
18	Lilongwe
19	Ljubljana
20	London
21	Monrovia
22	Oslo
23	Panama City
24	Paris
25	Phnom Penh
26	Podgorica
27	Pretoria
28	Rangoon
29	San Marino
30	San Salvador
31	Seoul
32	Suva
33	Tegucigalpa
34	Tehran
35	Thimphu
36	Vatican City
37	Warsaw
38	Yaounde
39	Zagreb
40	Andorra
41	Athens
42	Banjul
43	Basseterre
44	Belgrade
45	Bloemfontein
46	Bogota
47	Brasilia
48	Accra
49	Addis Ababa
50	Ankara
51	Antananarivo
52	Asmara
53	Astana
54	Asuncion
55	Bamako
56	Bangkok
57	Beijing
58	Berlin
59	Brussels
60	Buenos Aires
61	Chisinau
62	Damascus
63	Dar es Salaam
64	Dhaka
65	Djibouti
66	Doha
67	Jakarta
68	Jerusalem
69	Kampala
70	Kinshasa
71	Kuwait
72	La Paz
73	Lisbon
74	Luxembourg
75	Madrid
76	Manila
77	Maputo
78	Mbabane
79	Melekeok
80	Minsk
81	Nairobi
82	Naypyidaw
83	New Delhi
84	Nicosia
85	Nouakchott
86	Palikir
87	Port-au-Prince
88	Praia
89	Quito
90	Rome
91	Saint George's
92	San Jose
93	Sarajevo
94	Taipei
95	Tbilisi
96	Tokyo
97	Tripoli
98	Valletta
99	Vienna
100	Vilnius

Rows: 1-100 | Columns: 2

	123 pop_est Integer	Abc continent Varchar(32)	Abc country Varchar(82)	Abc Long varchar(2411724)
1	57713	North America	Greenland
2	279070	Oceania	New Caledonia
3	339747	Europe	Iceland
4	360346	North America	Belize
5	1291358	Asia	Timor-Leste
6	1467152	Africa	eSwatini
7	1772255	Africa	Gabon
8	1792338	Africa	Guinea-Bissau
9	1895250	Europe	Kosovo
10	1958042	Africa	Lesotho
11	2103721	Europe	Macedonia
12	2214858	Africa	Botswana
13	2484780	Africa	Namibia
14	2875422	Asia	Kuwait
15	3045191	Asia	Armenia
16	3351827	North America	Puerto Rico
17	3474121	Europe	Moldova
18	3500000	Africa	Somaliland
19	3753142	North America	Panama
20	3758571	Africa	Mauritania
21	4510327	Oceania	New Zealand
22	4954674	Africa	Congo
23	5445829	Europe	Slovakia
24	6653210	Africa	Libya
25	8754413	Europe	Austria
26	9850845	Europe	Hungary
27	9960487	Europe	Sweden
28	11138234	South America	Bolivia
29	11147407	North America	Cuba
30	15460732	North America	Guatemala
31	15972000	Africa	Zambia
32	17885245	Africa	Mali
33	18556698	Asia	Kazakhstan
34	23508428	Asia	Taiwan
35	24184810	Africa	Côte d'Ivoire
36	25054161	Africa	Madagascar
37	25248140	Asia	North Korea
38	29310273	Africa	Angola
39	29384297	Asia	Nepal
40	29748859	Asia	Uzbekistan
41	265100	Asia	N. Cyprus
42	603253	Africa	W. Sahara
43	758288	Asia	Bhutan
44	920938	Oceania	Fiji
45	1221549	Asia	Cyprus
46	3360148	South America	Uruguay
47	4543126	Asia	Palestine
48	4689021	Africa	Liberia
49	4930258	North America	Costa Rica
50	5011102	Europe	Ireland
51	5625118	Africa	Central African Rep.
52	5918919	Africa	Eritrea
53	6025951	North America	Nicaragua
54	6072475	Asia	United Arab Emirates
55	8468555	Asia	Tajikistan
56	9038741	North America	Honduras
57	10734247	North America	Dominican Rep.
58	10768477	Europe	Greece
59	11901484	Africa	Rwanda
60	13805084	Africa	Zimbabwe
61	17789267	South America	Chile
62	19196246	Africa	Malawi
63	21529967	Europe	Romania
64	28571770	Asia	Saudi Arabia
65	31304016	South America	Venezuela
66	39192111	Asia	Iraq
67	39570125	Africa	Uganda
68	40969443	Africa	Algeria
69	54841552	Africa	South Africa
70	80594017	Europe	Germany
71	82021564	Asia	Iran
72	83301151	Africa	Dem. Rep. Congo
73	96160163	Asia	Vietnam
74	105350020	Africa	Ethiopia
75	207353391	South America	Brazil
76	1281935911	Asia	India
77	282814	Oceania	Vanuatu
78	329988	North America	Bahamas
79	443593	Asia	Brunei
80	594130	Europe	Luxembourg
81	737718	South America	Guyana
82	865267	Africa	Djibouti
83	1218208	North America	Trinidad and Tobago
84	1251581	Europe	Estonia
85	1972126	Europe	Slovenia
86	2051363	Africa	Gambia
87	2823859	Europe	Lithuania
88	2990561	North America	Jamaica
89	3068243	Asia	Mongolia
90	3856181	Europe	Bosnia and Herz.
91	4292095	Europe	Croatia
92	5789122	Asia	Kyrgyzstan
93	6163195	Africa	Sierra Leone
94	6172011	North America	El Salvador
95	7111024	Europe	Serbia
96	7126706	Asia	Laos
97	7531386	Africa	Somalia
98	7965055	Africa	Togo
99	8236303	Europe	Switzerland
100	8299706	Asia	Israel

Rows: 1-100 | Columns: 4

Note

VerticaPy 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 VerticaPy environment.

Let’s preprocess the datasets by extracting latitude and longitude values and creating an index.

world["id"] = "ROW_NUMBER() OVER(ORDER BY country, pop_est)"
display(world)

cities["id"] = "ROW_NUMBER() OVER (ORDER BY city)"
cities["lat"] = "ST_X(geometry)"
cities["lon"] = "ST_Y(geometry)"
display(cities)

	123 pop_est Int 100%	...	🌎 Geometry(1048576) 100%	123 id Integer 100%
1	34124811	...		1
2	3047987	...		2
3	40969443	...		3
4	29310273	...		4
5	4050	...		5
6	44293293	...		6
7	3045191	...		7
8	23232413	...		8
9	8754413	...		9
10	9961396	...		10
11	329988	...		11
12	157826578	...		12
13	9549747	...		13
14	11491346	...		14
15	360346	...		15
16	11038805	...		16
17	758288	...		17
18	11138234	...		18
19	3856181	...		19
20	2214858	...		20

	Abc city Varchar(82) 100%	...	🌎 lat Float(22) 100%	🌎 lon Float(22) 100%
1	Accra	...	-0.218661598960694	5.55198046444593
2	Addis Ababa	...	38.6980585753487	9.03525622129575
3	Ankara	...	32.8624457823566	39.9291844440755
4	Antananarivo	...	47.5146780415299	-18.9146914920322
5	Asmara	...	38.9333235257593	15.3333392526819
6	Astana	...	71.427774209483	51.1811253042576
7	Asuncion	...	-57.6434510279013	-25.2944571170577
8	Bamako	...	-8.0019849632497	12.6519605263233
9	Bangkok	...	100.514698793695	13.751945064088
10	Beijing	...	116.386339825659	39.9308380899091
11	Berlin	...	13.3996027647005	52.5237645222512
12	Brussels	...	4.33137074969045	50.8352629353303
13	Buenos Aires	...	-58.3994772323314	-34.6005557499074
14	Chisinau	...	28.8577111396514	47.0050236196706
15	Damascus	...	36.2980500304171	33.5019798542061
16	Dar es Salaam	...	39.2663959776946	-6.79806673612438
17	Dhaka	...	90.4066336081075	23.7250055703128
18	Djibouti	...	43.1480016670523	11.5950144642555
19	Doha	...	51.5329678942993	25.2865560089066
20	Jakarta	...	106.82749176247	-6.17247184679889

Let’s create the geo-index.

from verticapy.sql.geo import create_index

create_index(world, "id", "geometry", "world_polygons", True)

	Abc type Varchar(20)	...	123 max_y Float(22)	Abc info Varchar(500)
1	GEOMETRY	...	83.64513

Let’s calculate the intersection between the cities and the various countries by using the GEOMETRY data type.

from verticapy.sql.geo import intersect

intersect(cities, "world_polygons", "id", "geometry")

	123 point_id Integer 100%	123 polygon_gid Integer 100%
1	202	36
2	1	40
3	2	165
4	3	116
5	4	61
6	5	51
7	6	3
8	7	81
9	8	111
10	9	56
11	10	161
12	11	96
13	13	162
14	14	49
15	15	82
16	16	124
17	26	32
18	27	89
19	28	137
20	29	15

The same can be done using directly the longitude and latitude.

intersect(cities, "world_polygons", "id", x="lat", y="lon")

	123 point_id Integer 100%	123 polygon_gid Integer 100%
1	1	40
2	2	165
3	3	116
4	4	61
5	5	51
6	6	3
7	7	81
8	8	111
9	9	56
10	10	161
11	11	96
12	13	162
13	14	49
14	15	82
15	16	124
16	17	62
17	18	75
18	19	10
19	20	99
20	21	22

Note

For geospatial functions, Vertica utilizes indexing to expedite computations, especially considering the potentially extensive size of polygons. This is a unique optimization approach employed by Vertica in these scenarios.

See also

describe_index() : Describes the geo index.

intersect() : Spatially intersects a point or points with a set of polygons.

rename_index() : Renames the geo index.