Leveraging the Power of Esri’s ArcGIS Enterprise Through Amazon Redshift

By Sid Singh, Tech BD, WWPS Partner Service Integration and Sales – AWS
By Jeff Demuth, Sr. Solutions Architect – AWS
By Abhishek Ram, Head WWPS Partner Service Integration – AWS
By Nemanja Boric, Sr. Development Engineer, DBS Redshift – AWS

Esri

Esri‘s geographic information system (GIS) solutions create, manage, analyze, and map various types of geospatial data. Their flagship GIS mapping software, ArcGIS, is a powerful mapping and spatial analytics technology.

Esri and Amazon Web Services (AWS) are extending their collaboration through an extensive integration of product suites and services. This will include Amazon QuickSight leveraging Esri basemap tiles through Amazon Location Service.

Esri also supports ArcGIS Enterprise on Kubernetes running with Amazon Elastic Kubernetes Service (Amazon EKS). In addition, ArcGIS will support interoperability with Amazon Redshift.

Starting with ArcGIS Pro, customers now have the ability to directly connect from ArcGIS Pro and ArcGIS Enterprise 10.9.1 to an Amazon Redshift cluster.

Customers can access and store their geospatial data using Amazon Redshift and seamlessly interoperate with ArcGIS Pro and ArcGIS Enterprise. This new capability enables higher performance and support for big datasets, allowing customers to easily analyze their GIS data and cloud-native databases together.

“As the global leader in GIS, Esri is proud to partner with Amazon to deliver a powerful new generation of geospatial infrastructure that is designed for enterprises, cloud-native, based on microservices, and fully orchestrated within Amazon EKS,” says Jay Theodore, CTO, Enterprise and Artificial Intelligence at Esri.

“This cloud-native deployment of enterprise GIS now expands its reach to support various cloud warehouses, including Amazon Redshift, for geospatial analysis, mapping, and visualization,” adds Jay.

Esri is an AWS Public Sector Partner with the AWS Public Safety and Disaster Response Competency. Inspiring positive change in industry and society through mapping and geospatial analytics is at the heart of their business. Esri provides powerful GIS solutions to more than 350,000 clients in more than 150 countries.

Case for Connecting Amazon Redshift with ArcGIS

With continuous growth in the size of datasets and declining cost of storage, it’s becoming more common to use a data warehouse like Amazon Redshift for geospatial datasets. Customers can unlock more value from their data by connecting Amazon Redshift to ArcGIS Pro to geospatially analyze petabyte datasets.

With the increase in frequency of data and their attributes being collected, it’s also getting increasingly difficult to manage large datasets in traditional databases. When analyzing large datasets like NYC Trip Data, using a data warehouse with geospatial support like Amazon Redshift is a natural fit for the best performance.

Another example of vast and dynamic data can be found in this Formula 1 case study, where it’s noted that each car has 120 sensors generating three GB of data each second. Use cases like these will continue to expand as sensors and sensor data become easier to work with.

Spatial data plays a critical role in business analytics, reporting, and forecasting. Spatial data contains fields used to represent geographic features. Examples of this type of data include weather reports, map directions, tweets with geographic positions, store locations, and airline routes.

Geography Data Type in Amazon Redshift

Amazon Redshift is a petabyte-scale data warehouse capable of querying and filtering billions of rows. To get started, let’s create a table in Amazon Redshift using the new Geography data type, and then we can connect it to ArcGIS Pro.

The Geography data type is used in queries requiring higher precision results for spatial data with geographic features that can be represented with a spheroid model of the Earth, and referenced using latitude and longitude as the spatial coordinate system.

To create a table with the new Geography data type, navigate to the Amazon Redshift console, then:

• Click on your Cluster
• Click on Query data
• Click on Query editor v2

For this example, we’ll use GDELT, the global database of events that is available on the AWS Open Data Registry.

We will import the data from Amazon Simple Storage Service (Amazon S3) into a table. Next, we’ll select a subset of fields including latitude and longitude. We will use ST_MakePoint and insert the data into a new table with the Geography data type.

Run the following commands to create the table:

CREATE TABLE gdeltinput (
  globaleventid varchar,day varchar,monthyear varchar,year varchar,fractiondate varchar,
  actor1code varchar,actor1name varchar,actor1countrycode varchar,actor1knowngroupcode varchar,
  actor1ethniccode varchar,actor1religion1code varchar,actor1religion2code varchar,
  actor1type1code varchar,actor1type2code varchar,actor1type3code varchar,
  actor2code varchar,actor2name varchar,actor2countrycode varchar,actor2knowngroupcode varchar,
  actor2ethniccode varchar,actor2religion1code varchar,actor2religion2code varchar,
  actor2type1code varchar,actor2type2code varchar,actor2type3code varchar,
  isrootevent BOOLEAN,eventcode varchar,eventbasecode varchar,eventrootcode varchar,
  quadclass varchar,goldsteinscale varchar,nummentions varchar,numsources varchar,numarticles varchar,avgtone varchar,
  actor1geo_type varchar,actor1geo_fullname varchar,actor1geo_countrycode varchar,actor1geo_adm1code varchar,
  actor1geo_lat varchar,actor1geo_long varchar,actor1geo_featureid varchar,
  actor2geo_type varchar,actor2geo_fullname varchar,actor2geo_countrycode varchar,actor2geo_adm1code varchar,
  actor2geo_lat varchar,actor2geo_long varchar,actor2geo_featureid varchar,
  actiongeo_type varchar,actiongeo_fullname varchar,actiongeo_countrycode varchar,actiongeo_adm1code varchar,
  actiongeo_lat varchar,actiongeo_long varchar,actiongeo_featureid varchar,
  dateadded varchar,sourceurl varchar);
  
COPY gdeltinputtwo FROM 's3://gdelt-open-data/events/20190918.export.csv' CSV DELIMITER '\t';

SELECT globaleventid, actor1code, actor1name, actor1countrycode, ST_SetSRID(ST_MakePoint(actor1geo_long::float8,actor1geo_lat::float8), 4326)::geography as geog
INTO gdeltgeogfinal
FROM gdeltinput
WHERE actor1geo_lat != '' and actor1geo_long != '' and LEN(actor1geo_lat) > 5 and LEN(actor1geo_long) > 5;

select count(globaleventid) from gdeltgeomfinal;

Figure 1 – Amazon Redshift Query Editor V2 table creation and data prep.

To start connecting ArcGIS Pro to Amazon Redshift, right-click on Databases from the Catalog menu in ArcGIS Pro and select New Database Connection from the context menu.

Figure 2 – Connecting ArcGIS Pro to database connection (Amazon Redshift).

This will open up the new connection prompt to enter cluster details.

Figure 3 – Database connection details.

After creating the connection, you will see your Amazon Redshift database show up in your catalog.

To add some points to the map, simply drag your table with your coordinates in the form of Geometry or Geography onto the map.

Figure 4 – Amazon Redshift table information.

After dragging the table to the map, ArcGIS Pro will auto-detect the column with the coordinates and map the respective locations.

Figure 5 – New query layer definition.

Here, you can see we have added around 100,000 points from Amazon Redshift table showing various events around the world.

Figure 6 – ArcGIS Pro with imported data points from Amazon Redshift.

There are Amazon Redshift SQL functions that support a variety of representations of spatial data including:

• GeoJSON
• Well-known text (WKT)
• Extended well-known text (EWKT)
• Well-known binary (WKB)
• Extended well-known binary (EWKB)

Conclusion

Amazon Redshift is a fully managed enterprise-grade data warehouse that provides many SQL functions to query spatial data. Customers can now analyze petabyte-scale datasets in Amazon Redshift and bring that data into ArcGIS Pro and ArcGIS Enterprise for additional mapping and analysis.

This new capability provides support for maximizing usage of larger datasets, thereby allowing customers to analyze their GIS data using ArcGIS and cloud-native data warehouses together.

In this post, we created a table in Amazon Redshift with the new Geography data type, inserted 100,000 points into our table, and connected ArcGIS Pro to our Amazon Redshift cluster to display those points on a map.

Users can benefit from the advanced features in Amazon Redshift such as Spectrum, Data Sharing, Automatic Workload Management, Concurrency Scaling, AQUA, RA3 instances, and Redshift Serverless to evaluate how geospatial workloads can benefit from these features today.

Reach out to your AWS and Esri account teams for details around the launched features and to understand how they can help solve your business needs.

.

.

Esri – AWS Partner Spotlight

Esri is an AWS Competency Partner whose GIS solutions create, manage, analyze, and map various types of geospatial data. Their flagship GIS mapping software, ArcGIS, is a powerful mapping and spatial analytics technology.

Contact Esri | Partner Overview | AWS Marketplace

*Already worked with Esri? Rate the Partner

*To review an AWS Partner, you must be a customer that has worked with them directly on a project.