Vertica & ArcGIS, QGIS connectivity

This document describes the steps required for connecting GIS tools like ArcGIS or QGIS to Vertic database using ODBC drivers.
I am using Windows 10 64 Bit architecture for this demonstration. Both ArGIS SW and QGIS SW are 64 bit versions. As well the ODBC drivers from Vertica are 64 bit drivers.Vertica CE version 9.x server is installed on the same machine in VMWare CentOS environment.
Please note if you have any 32 bit components installed, especially ODBC drivers on your environment, please ensure you do not have name conflict and you are using the correct ODBC installer versions. This could cause some challenges as there are several open source and vendor specific components and Google and documentation could send you in different directions.
After some conflicts and struggle I base-lined all the software components for 64 bit architecture. Once the base-line is established everything works out great!

So lets’s get started.

Download and install ArcGIS.

ArcGIS is a licensed software. You can get a 21 days Pro trial license, which is what I am using. 
Download and instruction is available here: https://pro.arcgis.com/en/pro-app/get-started/install-and-sign-in-to-arcgis-pro.htm
For ArcGIS Pro you  must create and account with ESRI and activate your trail license.

Download and install QGIS.

QGIS is open-source GIS software and is free. Download and installation instructions are here: https://qgis.org/en/site/forusers/download.html
Please download long term release (LTR) version for windows (as of this writing 3.10.x) which is 64 Bit.
You can optionally create an online account. Community support and forum are available here: https://www.qgis.org/en/site/forusers/support.html

Download and install Vertica Analytics Database (VMWare Box).

If you already have a Vertica database running in your machine or have access to Vertica DB cluster, please skip this step.

You can download self-contained and pre-configured VMWare Box of Vertica CE database SW here: https://www.vertica.com/dashboard/
Please note you have to a create a (free account) with Microfocus for this download.
Installation, configuration direction can be found here: https://www.vertica.com/documentation/vertica/9-3-x-documentation/

Download and install Windows ODBC Client drivers for Vertica.

Download and install the ODBC drivers for Vertica on your client machine from here: https://www.vertica.com/download/vertica/client-drivers/
Please note: ODBC client driver package includes components for several windows tools. As well both 32 Bit and 64 Bit driver are included in same package. 

My downloads look like below:

image.png

Install 64-bit Vertica ODBC Driver in your machine.

You should install ODBC driver using 64-Bit installer. Do not use 32 bit installer.From computer start, type ODBC: You’ll be prompted for ODBC application launch.

image.png

Create and Configure Vertica System DSN.

Click Add to create a new DSN. Please ensure you are under System DSN tab.Ensure that you have ONE 64-Bit Vertica ODBC driver installed using 64-bit installer under SYSTEM DSN. Though QGIS documentation does not specifically say this, I have not been able to make it work under User DSN.

image.png

You’ll get the following window. If your Vertica windows installation completed without error, you’ll find the driver listed. The version may be different, please ensure the driver version is is same high-level as the database. 9.x in my case. SElect Vertica and click finish.

image.png

You’ll get a window to configure Vertica Driver for the DSN.

image.png

Under Basic Setting tab, provide the following. The fields with * are required.

  • Provide a Unique DSN name. You’ll be using this value in ArcGIS and QGIS. This is CaSe-SeNsItIvE.
  • Optional description.
  • Name of the Vertica Database. Not the server name!
  • Server name or IP Address for the Vertica cluster.
  • Port default for Vertica is 5433. If this is different in your case please specify.
  • Database user Name used for logging into Vertica database. I created a test user for this demonstration.
  • Optionally database password for the database user.

All other values can be left to default.
Click “Test Connection”. You should have “Connection Successful” response. If you get any error, please stop. This is the best time to resolve any error.
Click OK to create the DSN.

Load some sample data into Vertica Database.

If you already have data loaded and accessible in a Vertica DB, then you can skip this step.I download a sample data set in CSV format from internet here (the zip file). This data set has 6000 rows.

image.png

Note that this is just TEXT file with No GEO attributes. I’ll use this demonstrate the connectivity. I’ll load and render GEOM/GEOG data from Vertica into GIS tools in subsequent posts.

Create a table and load the data in Vertica.create table public.population(id int,name varchar(50),address varchar(100),citystatezip varchar(100),country varchar(30)); 

Use COPY command to load the data.

Verify the data is loaded is accessible by the user.

test=> select * from public.population limit 5; 
id |       name        |           address           |      citystatezip      | country
----+-------------------+-----------------------------+------------------------+---------  1 | RICHARD SANDERSON | 1B CALLE VILLA PARC MAGINAS | SABANA GRANDE PR 00637 | USA  2 | ALEXANDER BUTLER  | 2A RES DOS RIOS             | CIALES PR 00638        | USA  3 | BLAKE HUGHES      | 2A CALLE NUEVO LONDRES      | UTUADO PR 00641        | USA  4 | MICHAEL RAMPLING  | 1A CALLE NUEVO LONDRES      | UTUADO PR 00641        | USA  5 | ISAAC MATHIS      | 4A BDA LA GRANJA            | UTUADO PR 00641        | USA(5 rows) test=> \q[dbadmin@localhost ~]$ 

Connect from ArcGIS Pro and access this data.

  • Open ArcGIS Pro.
  • Create a new project or open an existing one.
  • Create a new toolbox.
  • Import the attached python script. This is a very generic but minimal script. It take a Vertica table name as parameter and imports the data into ArcGIS database/table.
  • Define the three required parameters.
image.png
  • Save the toolbox.
  • Open the script and specify the parameters values.
  • Click on Run to run the script. All 6000 rows will be imported into ArcGIS database called population.gdb.
  • Open the population.gdb and view the population table.
image.png

This demonstration just shows the functional ability to import data (Non-Geo) from Vertica database into ArcGIS gdb table.Ideally, a scalable robust ArcGIS toolbox should be developed and deployed in the organization.

Connect from QGIS and access this data.

  • Open QGIS.
  • Click Data Source Manager to open the dialog box.
  • Select Vector Layer.
  • Select the Database Option.
  • Leave Encoding as system. Depending on the data you are using you may need choose different encoding.
  • Select ODBC as Data Type and cluck on new.
  • “Create a new OGR Database Connection” dialog box will open.
image.png
  • Select ODBC as Type, if it is not selected.
  • Specify a name.
  • Specify “localhost” for Host.
  • Leave DSN using OGR ODBC DSN format. In my case it is “vertica64,public.population.
image.png
  • Under the Basic tab, specify the database userid and password.
  • Click on Test Connection. You should get a connection successful.
image.png
  • Click OK. Back on Data Source Manager window click Add to save the Data Source.
  • Under Layer pane the data source will be save and data can be viewed by “Open Attribute Table” from context menu.
image.png

In my next post, I’ll load GEOM/GEOG data into Vertica and render the same QGIS.
If you run into any issue, please post comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s