verticapy.sql.geo.intersect¶
- verticapy.sql.geo.intersect(vdf: Annotated[str | vDataFrame, ''], index: str, gid: str, g: str | None = None, x: str | None = None, y: str | None = None) vDataFrame¶
Spatially intersects a point or points with a set of polygons.
Parameters¶
- vdf: SQLRelation
vDataFrameused to compute the spatial join.- index: str
Name of the index.
- gid: str
An
integercolumn orintegerthat uniquely identifies the spatial object(s) ofgorxandy.- g: str, optional
A geometry or geography (WGS84) column that contains points. The
gcolumn can contain only point geometries or geographies.- x: str, optional
x-coordinate or longitude.- y: str, optional
y-coordinate or latitude.
Returns¶
- vDataFrame
object containing the result of the intersection.
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()
AbccityAbc1 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: 2123pop_estAbccontinentAbccountryAbc1 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: 4Note
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)
123pop_est100%... 🌎100%123id100%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 Abccity100%... 🌎lat100%🌎lon100%1 Abu Dhabi ... 54.366593382592 24.4666835723799 2 Amsterdam ... 4.91469431740097 52.3519145466644 3 Apia ... -171.738641608603 -13.8415450424484 4 Ashgabat ... 58.3832991117746 37.949994933111 5 Bangui ... 18.5582881252873 4.36664430634909 6 Budapest ... 19.0813748187597 47.5019521849914 7 Bujumbura ... 29.3600060615284 -3.37608722037464 8 Castries ... -61.0000081803695 14.0019734893303 9 Colombo ... 79.8577506092564 6.93196575818212 10 Conakry ... -13.6821808861239 9.53346870502179 11 Cotonou ... 2.5180447405686 6.40195442278247 12 Dakar ... -17.4750759870506 14.7177775836233 13 Dublin ... -6.25085154039107 53.3350069945849 14 Georgetown ... -58.1670286474806 6.80197369275203 15 Havana ... -82.3661280299533 23.1339046995422 16 Kuala Lumpur ... 101.698037416746 3.16861173071237 17 København ... 12.5615398887033 55.6805100490259 18 Lilongwe ... 33.7833019599835 -13.9832950654692 19 Ljubljana ... 14.5149690334741 46.0552883087945 20 London ... -0.118667702475932 51.5019405883275 Let’s create the geo-index.
from verticapy.sql.geo import create_index create_index(world, "id", "geometry", "world_polygons", True)
Abctype... 123max_yAbcinfo1 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")
123point_id100%123polygon_gid100%1 197 113 2 9 56 3 10 161 4 11 96 5 13 162 6 14 49 7 15 82 8 16 124 9 17 62 10 18 75 11 19 10 12 20 99 13 21 22 14 22 156 15 23 29 16 24 58 17 1 40 18 2 165 19 3 116 20 4 61 The same can be done using directly the longitude and latitude.
intersect(cities, "world_polygons", "id", x="lat", y="lon")
123point_id100%123polygon_gid100%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.