Geo Spatial and Geometry – IPGeo (With Example)

Learn Data Science
Teradata Employee

Aster allows you to do some pretty cool things with data at scale.  If you have IP address information that you want to localize it is pretty easy to do.  With Aster you can also do it at scale across billions or trillions of IP addresses very quickly and easily.  In this post we will go over a background of Aster's IP Geo function as well as an example you can do with Aster Express.  

IPGeo lets you map IP addresses to location information (country, region, city, latitude, longitude, ZIP code, and ISP).
You can use the locations of web site visitors to improve the effectiveness of online applications.

For instance:


• Targeted online advertising
• Content localization
• Geographic rights management
• Enhanced analytics
• Online security and fraud prevention

The output of the IPGEO function includes the following fields

Syntax from Analytics Foundation 6.21

SELECT * FROM IPGeo (
ON input_table
IPAddressColumn ('ip_address_column')
[ Converter ('file', 'class') ]
[ IPDatabaseLocation ('geolocation_DB_loc') ]
[ Accumulate
({ 'accumulate_column' | 'accumulate_column_range' }[,...]')]
);

--BUILD TABLE AND POPULATE 

drop table if exists ipgeo_1;
create table ipgeo_1(id int,ip varchar) distribute by hash(id);
insert into ipgeo_1 values
(1, '156.251.45.52' )
,(1, '166.39.83.118' )
,(1, '20.202.165.69' )
,(1, '9.161.110.189' )
,(1, '233.41.134.35' )
,(1, '252.32.17.52' )
,(1, '51.16.207.123' )
,(1, '22.26.39.178' )
,(1, '23.83.229.188' )
,(1, '122.57.51.141' )
,(1, '4.216.210.13' )
,(1, '122.65.202.101' )
,(1, '106.82.135.103' )
,(1, '113.151.155.164' )
,(1, '167.107.32.188' )
,(1, '133.71.111.155' )
,(1, '153.85.88.21' )
,(1, '142.139.162.146' )
,(1, '100.117.159.222' )
,(1, '182.107.68.33' )
,(1, '188.203.135.47' )
,(1, '100.36.210.12' )
,(1, '142.242.200.21' )
,(1, '58.56.175.211' )
,(1, '141.9.231.29' )
,(1, '147.138.175.247' )
,(1, '1.79.214.182' )
,(1, '185.28.214.119' )
,(1, '230.95.165.75' )
,(1, '131.120.87.19' )
;

--SQL MR CALL FOR IPGEO:

SELECT * FROM IPGEO(
ON ipgeo_1
IpAddressColumn('ip')
ACCUMULATE('id', 'ip')
);

--OUTPUT OF IPGEO QUERY

#geospatial ipgeo #geology