Geospatial analytics

• 1: Best practices for geospatial analytics
• 2: Spatial objects
• 2.1: Supported spatial objects
• 2.2: Spatial reference identifiers (SRIDs)
• 3: Working with spatial objects in tables
• 3.1: Defining table columns for spatial data
• 3.2: Exporting spatial data from a table
• 3.3: Identifying null spatial objects
• 3.4: Loading spatial data from shapefiles
• 3.5: Loading spatial data into tables using COPY
• 3.6: Retrieving spatial data from a table as well-known text (WKT)
• 3.7: Working with GeoHash data
• 3.8: Spatial joins with ST_Intersects and STV_Intersect
• 3.8.1: Best practices for spatial joins
• 3.8.2: Ensuring polygon validity before creating or refreshing an index
• 3.8.3: STV_Intersect: scalar function vs. transform function
• 3.8.4: Performing spatial joins with STV_Intersect functions
• 3.8.4.1: Spatial indexes and STV_Intersect
• 3.8.5: When to use ST_Intersects vs. STV_Intersect
• 3.8.5.1: Performing spatial joins with ST_Intersects
• 4: Working with spatial objects from client applications
• 4.1: Using LONG VARCHAR and LONG VARBINARY data types with ODBC
• 4.2: Using LONG VARCHAR and LONG VARBINARY data types with JDBC
• 4.3: Using GEOMETRY and GEOGRAPHY data types in ODBC
• 4.4: Using GEOMETRY and GEOGRAPHY data types in JDBC
• 4.5: Using GEOMETRY and GEOGRAPHY data types in ADO.NET
• 5: OGC spatial definitions
• 5.1: Spatial classes
• 5.1.1: Point
• 5.1.2: Multipoint
• 5.1.3: Linestring
• 5.1.4: Multilinestring
• 5.1.5: Polygon
• 5.1.6: Multipolygon
• 5.2: Spatial object representations
• 5.2.1: Well-known text (WKT)
• 5.2.2: Well-known binary (WKB)
• 5.3: Spatial definitions
• 5.3.1: Boundary
• 5.3.2: Buffer
• 5.3.3: Contains
• 5.3.4: Convex hull
• 5.3.5: Crosses
• 5.3.6: Disjoint
• 5.3.7: Envelope
• 5.3.8: Equals
• 5.3.9: Exterior
• 5.3.10: GeometryCollection
• 5.3.11: Interior
• 5.3.12: Intersection
• 5.3.13: Overlaps
• 5.3.14: Relates
• 5.3.15: Simple
• 5.3.16: Symmetric difference
• 5.3.17: Union
• 5.3.18: Validity
• 5.3.19: Within
• 6: Spatial data type support limitations

1 - Best practices for geospatial analytics

Vertica recommends the following best practices when performing geospatial analytics in Vertica.

Performance optimization

Spatial joins with points and polygons

Vertica provides two ways to identify whether a set of points intersect with a set of polygons. Depending on the size of your data set, choose the approach that gives the best performance.

For a detailed example of best practices with spatial joins, see Best practices for spatial joins.

Spatial indexes

The STV_Create_Index function can consume large amounts of processing time and memory. When you index new data for the first time, monitor memory usage to be sure it stays within safe limits. Memory usage depends on:

• Number of polygons

• Number of vertices

• Amount of overlap among polygons

Checking polygon validity

2 - Spatial objects

Vertica implements several data types for storing spatial objects, Well-Known Text (WKT) strings, and Well-Known Binary (WKB) representations. These data types include:

• Supported spatial objects
• Spatial reference identifiers (SRIDs)

2.1 - Supported spatial objects

Vertica supports two spatial data types. These data types store two- and three-dimensional spatial objects in a table column:

• GEOMETRY: Spatial object with coordinates expressed as (x,y) pairs, defined in the Cartesian plane. All calculations use Cartesian coordinates.
• GEOGRAPHY: Spatial object defined as on the surface of a perfect sphere, or a spatial object in the WGS84 coordinate system. Coordinates are expressed in longitude/latitude angular values, measured in degrees. All calculations are in meters. For perfect sphere calculations, the sphere has a radius of 6371 kilometers, which approximates the shape of the earth.

  Note

  Some spatial programs use an ellipsoid to model the earth, resulting in slightly different data.

The maximum size of a GEOMETRY or GEOGRAPHY data type is 10,000,000 bytes (10 MB). You cannot use either data type as a table's primary key.

2.2 - Spatial reference identifiers (SRIDs)

A spatial reference identifier (SRID) is an integer value that represents a method for projecting coordinates on the plane. A SRID is metadata that indicates the coordinate system in which a spatial object is defined.

Geospatial functions using Geometry arguments must contain the same SRID. If the functions do not contain the same SRID, then the query returns an error.

For example, in this query the two points have different SRIDs. As a result the query returns an error:

=> SELECT ST_Distance(ST_GeomFromText('POINT(34 9)',2749), ST_GeomFromText('POINT(70 12)', 3359));
ERROR 5861:  Error calling processBlock() in User Function ST_Distance at [src/Distance.cpp:65],
error code: 0, message: Geometries with different SRIDs found: 2749, 3359

Supported SRIDs

Vertica supports SRIDs derived from the EPSG standards. Geospatial functions using Geometry arguments must use supported SRIDs when performing calculations. SRID values of 0 to 2^32-1 are valid. Queries with SRID values outside of this range will return an error.

3 - Working with spatial objects in tables

• Defining table columns for spatial data
• Exporting spatial data from a table
• Identifying null spatial objects
• Loading spatial data from shapefiles
• Loading spatial data into tables using COPY
• Retrieving spatial data from a table as well-known text (WKT)

3.1 - Defining table columns for spatial data

To define columns to contain GEOMETRY and GEOGRAPHY data, use this command:

=> CREATE TABLE [[db-name.]schema.]table-name (
   column-name GEOMETRY[(length)],
   column-name GEOGRAPHY[(length)]);

If you omit the length specification, the default column size is 1 MB. The maximum column size is 10 MB. The upper limit is not enforced, but the geospatial functions can only accept or return spatial data up to 10 MB.

You cannot modify the size or data type of a GEOMETRY or GEOGRAPHY column after creation. If the column size you created is not sufficient, create a new column with the desired size. Then copy the data from the old column, and drop the old column from the table.

You cannot import data to or export data from tables that contain spatial data from another Vertica database.

3.2 - Exporting spatial data from a table

You can export spatial data from a table in your Vertica database to a shapefile.

To export spatial data from a table to a shapefile:

1. As the superuser., set the shapefile export directory.

   => SELECT STV_SetExportShapefileDirectory(USING PARAMETERS path = '/home/geo/temp');
                  STV_SetExportShapefileDirectory
   ------------------------------------------------------------
    SUCCESS. Set shapefile export directory: [/home/geo/temp]
   (1 row)
   

2. Export your spatial data to a shapefile.

   => SELECT STV_Export2Shapefile(*
                 USING PARAMETERS shapefile = 'visualizations/city-data.shp',
                               shape = 'Polygon') OVER() FROM spatial_data;
    Rows Exported |                          File Path
   ---------------+----------------------------------------------------------------
          185873 | v_geo-db_node0001: /home/geo/temp/visualizations/city-data.shp
   (1 row)
   

   • The value asterisk (*) is the equivalent to listing all columns in the FROM clause.

   • You can specify sub-directories when exporting your shapefile.

   • Your shapefile must end with the file extension .shp.

3. Verify that three files now appear in the shapefile export directory.

   $ ls
   city-data.dbf  city-data.shp   city-data.shx
   

3.3 - Identifying null spatial objects

You can identify null GEOMETRY and GEOGRAPHY objects using the Vertica IS NULL and IS NOT NULL constructs.

This example uses the following table, where the row with id=2 has a null value in the geog field.

=> SELECT id, ST_AsText(geom), ST_AsText(geog) FROM locations
   ORDER BY 1 ASC;
 id |           ST_AsText              |                 ST_AsText
----+----------------------------------+--------------------------------------
  1 | POINT (2 3)                      | POINT (-85 15)
  2 | POINT (4 5)                      |
  3 | POLYGON ((-1 2, 0 3, 1 2, -1 2)) | POLYGON ((-24 12, -15 23, -20 27, -24 12))
  4 | LINESTRING (-1 2, 1 5)           | LINESTRING (-42.74 23.98, -62.19 23.78)
(4 rows)

Identify all the rows that have a null geog value:

=> SELECT id, ST_AsText(geom), (ST_AsText(geog) IS NULL) FROM locations
   ORDER BY 1 ASC;
 id |            ST_AsText             | ?column?
----+----------------------------------+----------
  1 | POINT (2 3)                      | f
  2 | POINT (4 5)                      | t
  3 | POLYGON ((-1 2, 0 3, 1 2, -1 2)) | f
  4 | LINESTRING (-1 2, 1 5)           | f
(4 rows)

Identify the rows where the geog value is not null:

=> SELECT id, ST_AsText(geom), (ST_AsText(geog) IS NOT NULL) FROM locations
   ORDER BY 1 ASC;
 id |            st_astext             | ?column?
----+----------------------------------+----------
  1 | POINT (2 3)                      | t
  2 | POINT (4 5)                      | f
  3 | LINESTRING (-1 2, 1 5)           | t
  4 | POLYGON ((-1 2, 0 3, 1 2, -1 2)) | t
(4 rows)

3.4 - Loading spatial data from shapefiles

Vertica provides the capability to load and parse spatial data that is stored in shapefiles. Shapefiles describe points, lines, and polygons. A shapefile is made up of three required files; all three files must be present and in the same directory to define the geometries:

• .shp—Contains the geometry data.

• .shx—Contains the positional index of the geometry.

• .dbf—Contains the attributes for each geometry.

To load spatial data from a shapefile:

1. Use STV_ShpCreateTable to generate a CREATE TABLE statement.

   => SELECT STV_ShpCreateTable ( USING PARAMETERS file = '/home/geo/temp/shp-files/spatial_data.shp')
                                  OVER() AS spatial_data;
              spatial_data
   ----------------------------------
    CREATE TABLE spatial_data(
      gid IDENTITY(64) PRIMARY KEY,
      uniq_id INT8,
      geom GEOMETRY(85)
   );
   (5 rows)
   

2. Create the table.

   => CREATE TABLE spatial_data(
      gid IDENTITY(64) PRIMARY KEY,
      uniq_id INT8,
      geom GEOMETRY(85));
   

3. Load the shapefile.

   => COPY spatial_data WITH SOURCE STV_ShpSource(file='/home/geo/temp/shp-files/spatial_data.shp')
       PARSER STV_ShpParser();
    Rows Loaded
   -------------
             10
   (1 row)
   

Supported shapefile shape types

The following table lists the shapefile shape types that Vertica supports.

3.5 - Loading spatial data into tables using COPY

You can load spatial data into a table in Vertica using a COPY statement.

To load data into Vertica using a COPY statement:

1. Create a table.

   => CREATE TABLE spatial_data (id INTEGER, geom GEOMETRY(200));
   CREATE TABLE
   

2. Create a text file named spatial.dat with the following data.

   1|POINT(2 3)
   2|LINESTRING(-1 2, 1 5)
   3|POLYGON((-1 2, 0 3, 1 2, -1 2))
   

3. Use COPY to load the data into the table.

   => COPY spatial_data (id, gx FILLER LONG VARCHAR(605), geom AS ST_GeomFromText(gx)) FROM LOCAL 'spatial.dat';
    Rows Loaded
   -------------
              3
   (1 row)
   

   The statement specifies a LONG VARCHAR(32000000) filler, which is the maximum size of WKT. You must specify a filler value large enough to hold the largest WKT you want to insert into the table.

3.6 - Retrieving spatial data from a table as well-known text (WKT)

GEOMETRY and GEOGRAPHY data is stored in Vertica tables as LONG VARBINARY, which isn't human readable. You can use ST_AsText to return the spatial data as Well-Known Text (WKT).

To return spatial data as WKT:

=> SELECT id, ST_AsText(geom) AS WKT FROM spatial_data;
 id |               WKT
----+----------------------------------
  1 | POINT (2 3)
  2 | LINESTRING (-1 2, 1 5)
  3 | POLYGON ((-1 2, 0 3, 1 2, -1 2))
(3 rows)

3.7 - Working with GeoHash data

Vertica supports GeoHashes. A GeoHash is a geocoding system for hierarchically encoding increasingly granular spatial references. Each additional character in a GeoHash drills down to a smaller section of a map.

You can use Vertica to generate spatial data from GeoHashes and GeoHashes from spatial data. Vertica supports the following functions for use with GeoHashes:

• ST_GeoHash - Returns a GeoHash in the shape of the specified geometry.

• ST_GeomFromGeoHash - Returns a polygon in the shape of the specified GeoHash.

• ST_PointFromGeoHash - Returns the center point of the specified GeoHash.

For example, to generate a full precision and partial precision GeoHash from a single point.

=> SELECT ST_GeoHash(ST_GeographyFromText('POINT(3.14 -1.34)')), LENGTH(ST_GeoHash(ST_GeographyFromText('POINT(3.14 -1.34)'))),
                     ST_GeoHash(ST_GeographyFromText('POINT(3.14 -1.34)') USING PARAMETERS numchars=5) partial_hash;
      ST_GeoHash      | LENGTH | partial_hash
----------------------+--------+--------------
 kpf0rkn3zmcswks75010 |     20 | kpf0r
(1 row)

This example shows how to generate a GeoHash from a multipoint point object. The returned polygon is a geometry object of the smallest tile that encloses that GeoHash.

=> SELECT ST_AsText(ST_GeomFromGeoHash(ST_GeoHash(ST_GeomFromText('MULTIPOINT(0 0, 0.0002 0.0001)')))) AS region_1,
                    ST_AsText(ST_GeomFromGeoHash(ST_GeoHash(ST_GeomFromText('MULTIPOINT(0.0001 0.0001, 0.0003 0.0002)')))) AS region_2;
 -[ RECORD 1 ]---------------------------------------------------------------------------------------------
    region_1 | POLYGON ((0 0, 0.00137329101562 0, 0.00137329101562 0.00137329101562, 0 0.00137329101562, 0 0))
    region_2 | POLYGON ((0 0, 0.010986328125 0, 0.010986328125 0.0054931640625, 0 0.0054931640625, 0 0))

3.8 - Spatial joins with ST_Intersects and STV_Intersect

Spatial joins allow you to identify spatial relationships between two sets of spatial data. For example, you can use spatial joins to:

• Calculate the density of mobile calls in various regions to determine the location of a new cell phone tower.

• Identify homes that fall within the impact zone of a hurricane.

• Calculate the number of users who live within a certain ZIP code.

• Calculate the number of customers in a retail store at any given time.

3.8.1 - Best practices for spatial joins

Use these best practices to improve overall performance and optimize your spatial queries.

Best practices for using spatial joins in Vertica include:

• Table segmentation to speed up index creation

• Adequately sizing a geometry column to store point data

• Loading Well-Known Text (WKT) directly into a Geometry column, using STV_GeometryPoint in a COPY statement

• Using OVER (PARTITION BEST) with STV_Intersect transform queries

Best practices example

Before performing the steps in the following example, download place_output.csv.zip from the Vertica Place GitHub repository (https://github.com/vertica/Vertica-Geospatial). You need to use the data set from this repository.

1. Create the table for the polygons. Use a GEOMETRY column width that fits your data without being excessively large. A good column-width fit improves performance. In addition, segmenting the table by HASH provides the advantages of parallel computation.

   => CREATE TABLE artworks (gid int, g GEOMETRY(700)) SEGMENTED BY HASH(gid) ALL NODES;
   

2. Use a copy statement with ST_Buffer to create and load the polygons on which to run the intersect. By using ST_Buffer in your copy statement, you can use that function to create the polygons.

   => COPY artworks(gid, gx FILLER LONG VARCHAR, g AS ST_Buffer(ST_GeomFromText(gx),8)) FROM STDIN DELIMITER ',';
   >> 1, POINT(10 45)
   >> 2, POINT(25 45)
   >> 3, POINT(35 45)
   >> 4, POINT(35 15)
   >> 5, POINT(30 5)
   >> 6, POINT(15 5)
   >> \.
   

3. Create a table for the location data, represented by points. You can store point data in a GEOMETRY column of 100 bytes. Avoid over-fitting your GEOMETRY column. Doing so can significantly degrade spatial intersection performance. Also, segment this table by HASH, to take advantage of parallel computation.

   => CREATE TABLE usr_data (gid identity, usr_id int, date_time timestamp, g GEOMETRY(100))
        SEGMENTED BY HASH(gid) ALL NODES;
   

4. During the copy statement, transform the raw location data to GEOMETRY data. You must perform this transformation because your location data needs to use the GEOMETRY data type. Use the function STV_GeometryPoint to transform the x and y columns of the source table.

   => COPY usr_data (usr_id, date_time, x FILLER LONG VARCHAR,
                     y FILLER LONG VARCHAR, g AS STV_GeometryPoint(x, y))
      FROM LOCAL 'place_output.csv' DELIMITER ',' ENCLOSED BY '';
   

5. Create the spatial index for the polygons. This index helps you speed up intersection calculations.

   => SELECT STV_Create_Index(gid, g USING PARAMETERS index='art_index', overwrite=true) OVER() FROM artworks;
   

6. Write an analytic query that returns the number of intersections per polygon. Specify that Vertica ignore any usr_id that intersects less than 20 times with a given polygon.

   => SELECT pol_gid,
          COUNT(DISTINCT(usr_id)) AS count_user_visit
      FROM
        (SELECT pol_gid,
          usr_id,
          COUNT(usr_id) AS user_points_in
       FROM
          (SELECT STV_Intersect(usr_id, g USING PARAMETERS INDEX='art_index') OVER(PARTITION BEST) AS (usr_id,
                                                           pol_gid)
       FROM usr_data
         WHERE date_time BETWEEN '2014-07-02 09:30:20' AND '2014-07-02 17:05:00') AS c
       GROUP BY pol_gid,
       usr_id HAVING COUNT(usr_id) > 20) AS real_visits
       GROUP BY pol_gid
       ORDER BY count_user_visit DESC;
   

Optimizations in the example query

This query has the following optimizations:

• The time predicated appears in the subquery.

• Using the location data table avoids the need for an expensive join.

• The query uses OVER (PARTITION BEST), to improve performance by partitioning the data.

• The user_points_in provides an estimate of the combined time spent intersecting with the artwork by all visitors.

3.8.2 - Ensuring polygon validity before creating or refreshing an index

When Vertica creates or updates a spatial index it does not check polygon validity. To prevent getting invalid results when you query your spatial index, you should check the validity of your polygons prior to creating or updating your spatial index.

The following example shows you how to check the validity of polygons.

1. Create a table and load spatial data.

   => CREATE TABLE polygon_validity_test (gid INT, geom GEOMETRY);
   CREATE TABLE
   => COPY polygon_validity_test (gid, gx FILLER LONG VARCHAR, geom AS St_GeomFromText(gx)) FROM STDIN;
   Enter data to be copied followed by a newline.
   End with a backslash and a period on a line by itself.
   >> 2|POLYGON((-31 74,8 70,8 50,-36 53,-31 74))
   >> 3|POLYGON((-38 50,4 13,11 45,0 65,-38 50))
   >> 4|POLYGON((-12 42,-12 42,27 48,14 26,-12 42))
   >> 5|POLYGON((0 0,1 1,0 0,2 1,1 1,0 0))
   >> 6|POLYGON((3 3,2 2,2 1,2 3,3 3))
   >> \.
   

2. Use ST_IsValid and STV_IsValidReason to find any invalid polygons.

   => SELECT gid, ST_IsValid(geom), STV_IsValidReason(geom) FROM polygon_validity_test;
    gid | ST_IsValid |            STV_IsValidReason
   -----+------------+------------------------------------------
      4 | t          |
      6 | f          | Self-intersection at or near POINT (2 1)
      2 | t          |
      3 | t          |
      5 | f          | Self-intersection at or near POINT (0 0)
   (5 rows)
   

Now that we have identifed the invalid polygons in our table, there are a couple different ways you can handle the invalid polygons when creating or refreshing a spatial index.

Filtering invalid polygons using a WHERE clause

This method is slower than filtering before creating an index because it checks the validity of each polygon at execution time.

The following example shows you how to exclude invalid polygons using a WHERE clause.

```
=> SELECT STV_Create_Index(gid, geom USING PARAMETERS index = 'valid_polygons') OVER()
   FROM polygon_validity_test
   WHERE ST_IsValid(geom) = 't';
```

Filtering invalid polygons before creating or refreshing an index

This method is faster than filtering using a WHERE clause because you incur the performance cost prior to building the index.

The following example shows you how to exclude invalid polygons by creating a new table excluding invalid polygons.

```
=> CREATE TABLE polygon_validity_clean AS
   SELECT *
   FROM polygon_validity_test
   WHERE ST_IsValid(geom) = 't';
CREATE TABLE
=> SELECT STV_Create_Index(gid, geom USING PARAMETERS index = 'valid_polygons') OVER()
   FROM polygon_validity_clean;
```

3.8.3 - STV_Intersect: scalar function vs. transform function

The STV_Intersect functions are similar in purpose, but you use them differently.

In the following example, the STV_Intersect scalar function compares the points in the points table to the polygons in a spatial index named my_polygons. STV_Intersect returns all points and polygons that match exactly:

=> SELECT gid AS pt_gid
   STV_Intersect(geom USING PARAMETERS index='my_polygons') AS pol_gid
   FROM points ORDER BY pt_gid;
 pt_gid | pol_gid
--------+---------
    100 |       2
    101 |
    102 |       2
    103 |
    104 |
    105 |       3
    106 |
    107 |
 (8 rows)

The following example shows how to use the STV_Intersect transform function to return information about the three point-polygon pairs that match and each of the polygons they match:

=> SELECT STV_Intersect(gid, geom
   USING PARAMETERS index='my_polygons')
   OVER (PARTITION BEST) AS (pt_gid, pol_id)
   FROM points;
 pt_gid | pol_id
--------+--------
    100 |      1
    100 |      2
    100 |      3
    102 |      2
    105 |      3
(3 rows)

See also

• STV_Intersect scalar function
• STV_Intersect transform function

3.8.4 - Performing spatial joins with STV_Intersect functions

Suppose you want to process a medium-to-large spatial data set and determine which points intersect with which polygons. In that case, first create a spatial index using STV_Create_Index. A spatial index provides efficient access to the set of polygons.

Then, use the STV_Intersect scalar or transform function to identify which point-polygon pairs match.

3.8.4.1 - Spatial indexes and STV_Intersect

Before performing a spatial join using one of the STV_Intersect functions, you must first run STV_Create_Index to create a database object that contains information about polygons. This object is called a spatial index of the set of polygons. The spatial index improves the time it takes for the STV_Intersect functions to access the polygon data.

Vertica creates spatial indexes in a global space. Thus, any user with access to the STV_*_Index functions can describe, rename, or drop indexes created by any other user.

Vertica provides functions that work with spatial indexes:

• STV_Create_Index—Stores information about polygons in an index to improve performance.

• STV_Describe_Index—Retrieves information about an index.

• STV_Drop_Index—Deletes a spatial index.

• STV_Refresh_Index—Refreshes a spatial index.

• STV_Rename_Index—Renames a spatial index.

3.8.5 - When to use ST_Intersects vs. STV_Intersect

Vertica provides two capabilities to identify whether a set of points intersect with a set of polygons. Depending on the size of your data set, choose the approach that gives the best performance:

• When comparing a set of geometries to a single geometry to see if they intersect, use the ST_Intersects function.

• To determine if a set of points intersects with a set of polygons in a medium-to-large data set, first create a spatial index using STV_Create_Index. Then, use one of the STV_Intersect functions to return the set of pairs that intersect.

  Note

  You can only perform spatial joins on GEOMETRY data.

3.8.5.1 - Performing spatial joins with ST_Intersects

The ST_Intersects function determines if two GEOMETRY objects intersect or touch at a single point.

Use ST_Intersects when you want to identify if a small set of geometries in a column intersect with a given geometry.

Example

The following example uses ST_Intersects to compare a column of point geometries to a single polygon. The table that contains the points has 1 million rows.

ST_Intersects returns only the points that intersect with the polygon. Those points represent about 0.01% of the points in the table:

=> CREATE TABLE points_1m(gid IDENTITY, g GEOMETRY(100)) ORDER BY g;
=> COPY points_1m(wkt FILLER LONG VARCHAR(100), g AS ST_GeomFromText(wkt))
   FROM LOCAL '/data/points.dat';
 Rows Loaded
-------------
     1000000
(1 row)
=> SELECT ST_AsText(g) FROM points_1m WHERE
   ST_Intersects
     (
      g,
      ST_GeomFromText('POLYGON((-71 42, -70.9 42, -70.9 42.1, -71 42.1, -71 42))')
      );
         st_astext
----------------------------
 POINT (-70.97532 42.03538)
 POINT (-70.97421 42.0376)
 POINT (-70.99004 42.07538)
 POINT (-70.99477 42.08454)
 POINT (-70.99088 42.08177)
 POINT (-70.98643 42.07593)
 POINT (-70.98032 42.07982)
 POINT (-70.95921 42.00982)
 POINT (-70.95115 42.02177)
...
(116 rows)

Vertica recommends that you test the intersections of two columns of geometries by creating a spatial index. Use one of the STV_Intersect functions as described in STV_Intersect: scalar function vs. transform function.

4 - Working with spatial objects from client applications

The Vertica client driver libraries provide interfaces for connecting your client applications to your Vertica database. The drivers simplify exchanging data for loading, report generation, and other common database tasks.

There are three separate client drivers:

• Open Database Connectivity (ODBC)—The most commonly-used interface for third-party applications and clients written in C, Python, PHP, Perl, and most other languages.

• Java Database Connectivity (JDBC)—Used by clients written in the Java programming language.

• ActiveX Data Objects for .NET (ADO.NET)—Used by clients developed using Microsoft's .NET Framework and written in C#, Visual Basic .NET, and other .NET languages.

Vertica Place supports the following new data types:

• LONG VARCHAR

• LONG VARBINARY

• GEOMETRY

• GEOGRAPHY

The client driver libraries support these data types; the following sections describe that support and provide examples.

4.1 - Using LONG VARCHAR and LONG VARBINARY data types with ODBC

The ODBC drivers support the LONG VARCHAR and LONG VARBINARY data types similarly to VARCHAR and VARBINARY data types. When binding input or output parameters to a LONG VARCHAR or LONG VARBINARY column in a query, use the SQL_LONGVARCHAR and SQL_LONGVARBINARY constants to set the column's data type. For example, to bind an input parameter to a LONG VARCHAR column, you would use a statement that looks like this:

rc = SQLBindParameter(hdlStmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_LONGVARCHAR,
             80000, 0, (SQLPOINTER)myLongString, sizeof(myLongString), NULL);

4.2 - Using LONG VARCHAR and LONG VARBINARY data types with JDBC

Using LONG VARCHAR and LONG VARBINARY data types in a JDBC client application is similar to using VARCHAR and VARBINARY data types. The JDBC driver transparently handles the conversion (for example, between a Java String object and a LONG VARCHAR).

The following example code demonstrates inserting and retrieving a LONG VARCHAR string. It uses the JDBC Types class to determine the data type of the string returned by Vertica, although it does not actually need to know whether the database column is a LONG VARCHAR or just a VARCHAR in order to retrieve the value.

import java.sql.*;
import java.util.Properties;

public class LongVarcharExample {
    public static void main(String[] args) {
        try {
            Class.forName("com.vertica.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.err.println("Could not find the JDBC driver class.");
            e.printStackTrace();
            return;
        }
        Properties myProp = new Properties();
        myProp.put("user", "ExampleUser");
        myProp.put("password", "password123");
        Connection conn;
        try {
            conn = DriverManager.getConnection(
                            "jdbc:vertica://VerticaHost:5433/ExampleDB",
                            myProp);
            // establish connection and make a table for the data.
            Statement stmt = conn.createStatement();

            // How long we want the example string to be. This is
            // larger than can fit into a traditional VARCHAR (which is limited
            // to 65000.
            int length = 100000;

            // Create a table with a LONG VARCHAR column that can store
            // the string we want to insert.
            stmt.execute("DROP TABLE IF EXISTS longtable CASCADE");
            stmt.execute("CREATE TABLE longtable (text LONG VARCHAR(" + length
                            + "))");
            // Build a long string by appending an integer to a string builder
            // until we hit the size limit. Will result in a string
            // containing 01234567890123....
            StringBuilder sb = new StringBuilder(length);
            for (int i = 0; i < length; i++)
            {
                sb.append(i % 10);
            }
            String value = sb.toString();

            System.out.println("String value is " + value.length() +
                            " characters long.");

            // Create the prepared statement
            PreparedStatement pstmt = conn.prepareStatement(
                            "INSERT INTO longtable (text)" +
                            " VALUES(?)");
            try {
                // Insert LONG VARCHAR value
                System.out.println("Inserting LONG VARCHAR value");
                pstmt.setString(1, value);
                pstmt.addBatch();
                pstmt.executeBatch();

                // Query the table we created to get the value back.
                ResultSet rs = null;
                rs = stmt.executeQuery("SELECT * FROM longtable");

                // Get metadata about the result set.
                ResultSetMetaData rsmd = rs.getMetaData();
                // Print the type of the first column. Should be
                // LONG VARCHAR. Also check it against the Types class, to
                // recognize it programmatically.
                System.out.println("Column #1 data type is: " +
                                rsmd.getColumnTypeName(1));
                if (rsmd.getColumnType(1) == Types.LONGVARCHAR) {
                    System.out.println("It is a LONG VARCHAR");
                } else {
                    System.out.println("It is NOT a LONG VARCHAR");
                }

                // Print out the string length of the returned value.
                while (rs.next()) {
                    // Use the same getString method to get the value that you
                    // use to get the value of a VARCHAR.
                    System.out.println("Returned string length: " +
                                    rs.getString(1).length());
                }
            } catch (SQLException e) {
                System.out.println("Error message: " + e.getMessage());
                return; // Exit if there was an error
            }
            // Cleanup
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4.3 - Using GEOMETRY and GEOGRAPHY data types in ODBC

Vertica GEOMETRY and GEOGRAPHY data types are backed by LONG VARBINARY native types and ODBC client applications treat them as binary data. However, these data types have a format that is unique to Vertica. To manipulate this data in your C++ application, you must use the functions in Vertica that convert them to a recognized format.

To convert a WKT or WKB to the GEOMETRY or GEOGRAPHY format, use one of the following SQL functions:

• ST_GeographyFromText—Converts a WKT to a GEOGRAPHY type.

• ST_GeographyFromWKB—Converts a WKB to a GEOGRAPHY type.

• ST_GeomFromText—Converts a WKT to a GEOMETRY type.

• ST_GeomFromWKB—Converts a WKB to GEOMETRY type.

To convert a GEOMETRY or GEOGRAPHY object to its corresponding WKT or WKB, use one of the following SQL functions:

• ST_AsText—Converts a GEOMETRY or GEOGRAPHY object to a WKT, returns a LONGVARCHAR.

• ST_AsBinary—Converts a GEOMETRY or GEOGRAPHY object to a WKB, returns a LONG VARBINARY.

The following code example converts WKT data into GEOMETRY data using ST_GeomFromText and stores it in a table. Later, this example retrieves the GEOMETRY data from the table and converts it to WKT and WKB format using ST_AsText and ST_AsBinary.

// Compile on Linux using:
//  g++ -g -I/opt/vertica/include -L/opt/vertica/lib64 -lodbc -o SpatialData SpatialData.cpp
// Some standard headers
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <assert.h>
#include <sstream>
// Only needed for Windows clients
// #include <windows.h>
// Standard ODBC headers
#include <sql.h>
#include <sqltypes.h>
#include <sqlext.h>
// Helper function to print SQL error messages.
template <typename HandleT>
void reportError(int handleTypeEnum, HandleT hdl)
{
    // Get the status records.
    SQLSMALLINT   i, MsgLen;
    SQLRETURN     ret2;
    SQLCHAR       SqlState[6], Msg[SQL_MAX_MESSAGE_LENGTH];
    SQLINTEGER    NativeError;
    i = 1;
    printf("\n");
    while ((ret2 = SQLGetDiagRec(handleTypeEnum, hdl, i, SqlState, &NativeError,
                                Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA) {
        printf("error record %d\n", i);
        printf("sqlstate: %s\n", SqlState);
        printf("detailed msg: %s\n", Msg);
        printf("native error code: %d\n\n", NativeError);
        i++;
    }
    exit(EXIT_FAILURE); // bad form... but Ok for this demo
}
int main()
{
    // Set up the ODBC environment
    SQLRETURN ret;
    SQLHENV hdlEnv;
    ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hdlEnv);
    assert(SQL_SUCCEEDED(ret));
    // Tell ODBC that the application uses ODBC 3.
    ret = SQLSetEnvAttr(hdlEnv, SQL_ATTR_ODBC_VERSION,
        (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);
    assert(SQL_SUCCEEDED(ret));
    // Allocate a database handle.
    SQLHDBC hdlDbc;
    ret = SQLAllocHandle(SQL_HANDLE_DBC, hdlEnv, &hdlDbc);
    assert(SQL_SUCCEEDED(ret));
    // Connect to the database
    printf("Connecting to database.\n");
    const char *dsnName = "ExampleDB";
    const char* userID = "dbadmin";
    const char* passwd = "password123";
    ret = SQLConnect(hdlDbc, (SQLCHAR*)dsnName,
        SQL_NTS,(SQLCHAR*)userID,SQL_NTS,
        (SQLCHAR*)passwd, SQL_NTS);
    if(!SQL_SUCCEEDED(ret)) {
        printf("Could not connect to database.\n");
        reportError<SQLHDBC>(SQL_HANDLE_DBC, hdlDbc);

    } else {
        printf("Connected to database.\n");
    }

    // Disable AUTOCOMMIT
    ret = SQLSetConnectAttr(hdlDbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF,
        SQL_NTS);

    // Set up a statement handle
    SQLHSTMT hdlStmt;
    SQLAllocHandle(SQL_HANDLE_STMT, hdlDbc, &hdlStmt);

    // Drop any previously defined table.
    ret = SQLExecDirect(hdlStmt, (SQLCHAR*)"DROP TABLE IF EXISTS polygons",
        SQL_NTS);
    if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}

    // Run query to create a table to hold a geometry.
    ret = SQLExecDirect(hdlStmt,
        (SQLCHAR*)"CREATE TABLE polygons(id INTEGER PRIMARY KEY, poly GEOMETRY);",
        SQL_NTS);
    if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}

    // Create the prepared statement. This will insert data into the
    // table we created above. It uses the ST_GeomFromText function to convert the
    // string-formatted polygon definition to a GEOMETRY datat type.
    printf("Creating prepared statement\n");
    ret = SQLPrepare (hdlStmt,
        (SQLTCHAR*)"INSERT INTO polygons(id, poly) VALUES(?, ST_GeomFromText(?))",
        SQL_NTS) ;
    if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}

    SQLINTEGER id = 0;
    int numBatches = 5;
    int rowsPerBatch = 10;

    // Polygon definition as a string.
    char polygon[] = "polygon((1 1, 1 2, 2 2, 2 1, 1 1))";
    // Bind variables to the parameters in the prepared SQL statement
    ret = SQLBindParameter(hdlStmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER,
        0, 0, &id, 0 , NULL);
    if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT,hdlStmt);}
    // Bind polygon string to the geometry column
    SQLBindParameter(hdlStmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_LONGVARCHAR,
        strlen(polygon), 0, (SQLPOINTER)polygon, strlen(polygon), NULL);
     if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT,hdlStmt);}
    // Execute the insert
    ret = SQLExecute(hdlStmt);
    if(!SQL_SUCCEEDED(ret)) {
       reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);
    }  else {
        printf("Executed batch.\n");
    }

    // Commit the transaction
    printf("Committing transaction\n");
    ret = SQLEndTran(SQL_HANDLE_DBC, hdlDbc, SQL_COMMIT);
    if(!SQL_SUCCEEDED(ret)) {
        reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);
    }  else {
        printf("Committed transaction\n");
    }
    // Now, create a query to retrieve the geometry.
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hdlDbc, &hdlStmt);
    if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}
    printf("Getting data from table.\n");
    // Execute a query to get the id, raw geometry data, and
    // the geometry data as a string. Uses the ST_AsText SQL function to
    // format raw data back into a string polygon definition
    ret = SQLExecDirect(hdlStmt,
        (SQLCHAR*)"select id,ST_AsBinary(poly),ST_AsText(poly) from polygons ORDER BY id;",
        SQL_NTS);
    if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT,hdlStmt);}

    SQLINTEGER idval;
    // 10MB buffer to hold the raw data from the geometry (10Mb is the maximum
    // length of a GEOMETRY)
    SQLCHAR* polygonval = (SQLCHAR*)malloc(10485760);
    SQLLEN polygonlen, polygonstrlen;
    // Buffer to hold a LONGVARCHAR that can result from converting the
    // geometry to a string.
    SQLTCHAR* polygonstr = (SQLTCHAR*)malloc(33554432);

    // Get the results of the query and print each row.
    do {
        ret = SQLFetch(hdlStmt);
        if (SQL_SUCCEEDED(ret)) {
            // ID column
            ret = SQLGetData(hdlStmt, 1, SQL_C_LONG, &idval, 0, NULL);
            if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}
            printf("id: %d\n",idval);
            // The WKB format geometry data
            ret = SQLGetData(hdlStmt, 2, SQL_C_BINARY, polygonval, 10485760,
                &polygonlen);
            if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}
            printf("Polygon in WKB format: ");
            // Print each byte of polygonval buffer in hex format.
            for (int z = 0; z < polygonlen; z++)
                printf("%02x ",polygonval[z]);
            printf("\n");
            // Geometry data formatted as a string.
            ret = SQLGetData(hdlStmt, 3, SQL_C_TCHAR, polygonstr, 33554432, &polygonstrlen);
            if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}
            printf("Polygon in WKT format: %s\n", polygonstr);
        }
    } while(SQL_SUCCEEDED(ret));


    free(polygonval);
    free(polygonstr);
    // Clean up
    printf("Free handles.\n");
    ret = SQLFreeHandle(SQL_HANDLE_STMT, hdlStmt);
    if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}
    ret = SQLFreeHandle(SQL_HANDLE_DBC, hdlDbc);
    if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}
    ret = SQLFreeHandle(SQL_HANDLE_ENV, hdlEnv);
    if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}
    exit(EXIT_SUCCESS);
}

The output of running the above example is:

Connecting to database.
Connected to database.
Creating prepared statement
Executed batch.
Committing transaction
Committed transaction
Getting data from table.
id: 0
Polygon in WKB format: 01 03 00 00 00 01 00 00 00 05 00 00 00 00 00 00 00 00 00 f0 3f 00 00 00 00 00 00 f0 3f 00 00 00 00 00 00 f0 3f 00 00 00 00 00 00 00 40 00 00 00 00 00 00 00 40 00 00 00 00 00 00 00 40 00 00 00 00 00 00 00 40 00 00 00 00 00 00 f0 3f 00 00 00 00 00 00 f0 3f 00 00 00 00 00 00 f0 3f
Polygon in WKT format: POLYGON ((1 1, 1 2, 2 2, 2 1, 1 1))
Free handles.

4.4 - Using GEOMETRY and GEOGRAPHY data types in JDBC

Vertica GEOMETRY and GEOGRAPHY data types are backed by LONG VARBINARY native types and JDBC client applications treat them as binary data. However, these data types have a format that is unique to Vertica. To manipulate this data in your Java application, you must use the functions in Vertica that convert them to a recognized format.

To convert a WKT or WKB to the GEOMETRY or GEOGRAPHY format, use one of the following SQL functions:

• ST_GeographyFromText—Converts a WKT to a GEOGRAPHY type.

• ST_GeographyFromWKB—Converts a WKB to a GEOGRAPHY type.

• ST_GeomFromText—Converts a WKT to a GEOMETRY type.

• ST_GeomFromWKB—Converts a WKB to GEOMETRY type.

To convert a GEOMETRY or GEOGRAPHY object to its corresponding WKT or WKB, use one of the following SQL functions:

• ST_AsText—Converts a GEOMETRY or GEOGRAPHY object to a WKT, returns a LONGVARCHAR.

• ST_AsBinary—Converts a GEOMETRY or GEOGRAPHY object to a WKB, returns a LONG VARBINARY.

The following code example converts WKT and WKB data into GEOMETRY data using ST_GeomFromText and ST_GeomFromWKB and stores it in a table. Later, this example retrieves the GEOMETRY data from the table and converts it to WKT and WKB format using ST_AsText and ST_AsBinary.

import java.io.InputStream;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class GeospatialDemo
{
    public static void main(String [] args) throws Exception
    {
        Class.forName("com.vertica.jdbc.Driver");
        Connection conn =
              DriverManager.getConnection("jdbc:vertica://localhost:5433/db",
                                          "user", "password");
        conn.setAutoCommit(false);

        Statement stmt = conn.createStatement();
        stmt.execute("CREATE TABLE polygons(id INTEGER PRIMARY KEY, poly GEOMETRY)");

        int id = 0;
        int numBatches = 5;
        int rowsPerBatch = 10;

        //batch inserting WKT data
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO polygons
                                  (id, poly) VALUES(?, ST_GeomFromText(?))");
        for(int i = 0; i < numBatches; i++)
        {

            for(int j = 0; j < rowsPerBatch; j++)
            {
                //Insert your own WKT data here
                pstmt.setInt(1, id++);
                pstmt.setString(2, "polygon((1 1, 1 2, 2 2, 2 1, 1 1))");
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        }

        conn.commit();
        pstmt.close();
        //batch insert WKB data
        pstmt = conn.prepareStatement("INSERT INTO polygons(id, poly)
                                      VALUES(?, ST_GeomFromWKB(?))");
        for(int i = 0; i < numBatches; i++)
        {
            for(int j = 0; j < rowsPerBatch; j++)
            {
                //Insert your own WKB data here
                byte [] wkb = getWKB();
                pstmt.setInt(1, id++);
                pstmt.setBytes(2, wkb);
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        }

        conn.commit();
        pstmt.close();
        //selecting data as WKT
        ResultSet rs = stmt.executeQuery("select ST_AsText(poly) from polygons");
        while(rs.next())
        {
            String wkt = rs.getString(1);
            Reader wktReader = rs.getCharacterStream(1);
            //process the wkt as necessary
        }
        rs.close();

        //selecting data as WKB
        rs = stmt.executeQuery("select ST_AsBinary(poly) from polygons");
        while(rs.next())
        {
            byte [] wkb = rs.getBytes(1);
            InputStream wkbStream = rs.getBinaryStream(1);
            //process the wkb as necessary
        }
        rs.close();

        //binding parameters in predicates
        pstmt = conn.prepareStatement("SELECT id FROM polygons WHERE
                                      ST_Contains(ST_GeomFromText(?), poly)");
        pstmt.setString(1, "polygon((1 1, 1 2, 2 2, 2 1, 1 1))");
        rs = pstmt.executeQuery();
        while(rs.next())
        {
            int pk = rs.getInt(1);
            //process the results as necessary
        }
        rs.close();

        conn.close();
    }
}

4.5 - Using GEOMETRY and GEOGRAPHY data types in ADO.NET

Vertica GEOMETRY and GEOGRAPHY data types are backed by LONG VARBINARY native types and ADO.NET client applications treat them as binary data. However, these data types have a format that is unique to Vertica. To manipulate this data in your C# application, you must use the functions in Vertica that convert them to a recognized format.

To convert a WKT or WKB to the GEOMETRY or GEOGRAPHY format, use one of the following SQL functions:

• ST_GeographyFromText—Converts a WKT to a GEOGRAPHY type.

• ST_GeographyFromWKB—Converts a WKB to a GEOGRAPHY type.

• ST_GeomFromText—Converts a WKT to a GEOMETRY type.

• ST_GeomFromWKB—Converts a WKB to GEOMETRY type.

To convert a GEOMETRY or GEOGRAPHY object to its corresponding WKT or WKB, use one of the following SQL functions:

• ST_AsText—Converts a GEOMETRY or GEOGRAPHY object to a WKT, returns a LONGVARCHAR.

• ST_AsBinary—Converts a GEOMETRY or GEOGRAPHY object to a WKB, returns a LONG VARBINARY.

The following C# code example converts WKT data into GEOMETRY data using ST_GeomFromText and stores it in a table. Later, this example retrieves the GEOMETRY data from the table and converts it to WKT and WKB format using ST_AsText and ST_AsBinary.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Vertica.Data.VerticaClient;
namespace ConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            VerticaConnectionStringBuilder builder =
                             new VerticaConnectionStringBuilder();
            builder.Host = "VerticaHost";
            builder.Database = "VMart";
            builder.User = "ExampleUser";
            builder.Password = "password123";
            VerticaConnection _conn = new
                              VerticaConnection(builder.ToString());
            _conn.Open();

            VerticaCommand command = _conn.CreateCommand();
            command.CommandText = "DROP TABLE IF EXISTS polygons";
            command.ExecuteNonQuery();
            command.CommandText =
              "CREATE TABLE polygons (id INTEGER PRIMARY KEY, poly GEOMETRY)";
            command.ExecuteNonQuery();
            // Prepare to insert a polygon using a prepared statement. Use the
            // ST_GeomFromtText SQl function to convert from WKT to GEOMETRY.
            VerticaTransaction txn = _conn.BeginTransaction();
            command.CommandText =
             "INSERT into polygons VALUES(@id, ST_GeomFromText(@polygon))";
            command.Parameters.Add(new
                           VerticaParameter("id", VerticaType.BigInt));
            command.Parameters.Add(new
                           VerticaParameter("polygon", VerticaType.VarChar));
            command.Prepare();
            // Set the values for the parameters
            command.Parameters["id"].Value = 0;
            //
            command.Parameters["polygon"].Value =
                               "polygon((1 1, 1 2, 2 2, 2 1, 1 1))";
            // Execute the query to insert the value
            command.ExecuteNonQuery();

            // Now query the table
            VerticaCommand query = _conn.CreateCommand();
            query.CommandText =
               "SELECT id, ST_AsText(poly), ST_AsBinary(poly) FROM polygons;";
            VerticaDataReader dr = query.ExecuteReader();
            while (dr.Read())
            {
                Console.WriteLine("ID: " + dr[0]);
                Console.WriteLine("Polygon WKT format data type: "
                    + dr.GetDataTypeName(1) +
                    " Value: " + dr[1]);
                // Get the WKB format of the polygon and print it out as hex.
                Console.Write("Polygon WKB format data type: "
                               + dr.GetDataTypeName(2));
                Console.WriteLine(" Value: "
                               + BitConverter.ToString((byte[])dr[2]));
            }
            _conn.Close();
        }
    }
}

The example code prints the following on the system console:

ID: 0
Polygon WKT format data type: LONG VARCHAR Value: POLYGON ((1 1, 1 2,
2 2, 2 1,1 1))
Polygon WKB format data type: LONG VARBINARY Value: 01-03-00-00-00-01
-00-00-00-05-00-00-00-00-00-00-00-00-00-F0-3F-00-00-00-00-00-00-F0-3F
-00-00-00-00-00-00-F0-3F-00-00-00-00-00-00-00-40-00-00-00-00-00-00-00
-40-00-00-00-00-00-00-00-40-00-00-00-00-00-00-00-40-00-00-00-00-00-00
-F0-3F-00-00-00-00-00-00-F0-3F-00-00-00-00-00-00-F0-3F

5 - OGC spatial definitions

Using Vertica requires an understanding of the Open Geospatial Consortium (OGC) concepts and capabilities. For more information, see the OGC Simple Feature Access Part 1 - Common Architecture specification.

5.1 - Spatial classes

Vertica supports several classes of objects, as defined in the OGC standards.

5.1.1 - Point

A location in two-dimensional space that is identified by one of the following:

• X and Y coordinates

• Longitude and latitude values

A point has dimension 0 and no boundary.

Examples

The following example uses a GEOMETRY point:

=> CREATE TABLE point_geo (gid int, geom GEOMETRY(100));
CREATE TABLE
=> COPY point_geo(gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter ',';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>1, POINT(3 5)
>>\.
=> SELECT gid, ST_AsText(geom) FROM point_geo;
 gid |  ST_AsText
-----+-------------
   1 | POINT (3 5)
(1 row)

The following example uses a GEOGRAPHY point:

=> CREATE TABLE point_geog (gid int, geog geography(100));
CREATE TABLE
=> COPY point_geog(gid, gx filler LONG VARCHAR, geog AS ST_GeographyFromText(gx)) FROM stdin delimiter ',';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>1, POINT(42 71)
>>\.
=> SELECT gid, ST_AsText(geog) FROM point_geog;
 gid |   ST_AsText
-----+---------------
   1 | POINT (42 71)
(1 row)

5.1.2 - Multipoint

A set of one or more points. A multipoint object has dimension 0 and no boundary.

Examples

The following example uses a GEOMETRY multipoint:

=> CREATE TABLE mpoint_geo (gid int, geom GEOMETRY(1000));
CREATE TABLE
=> COPY mpoint_geo(gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>1|MULTIPOINT(4 7, 8 10)
>>\.
=> SELECT gid, ST_AsText(geom) FROM mpoint_geo;
 gid |       st_astext
-----+-----------------------
   1 | MULTIPOINT (7 8, 6 9)
(1 row)

The following example uses a GEOGRAPHY multipoint:

=> CREATE TABLE mpoint_geog (gid int, geog GEOGRAPHY(1000));
CREATE TABLE
=> COPY mpoint_geog(gid, gx filler LONG VARCHAR, geog AS ST_GeographyFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>1|MULTIPOINT(42 71, 41.4 70)
>>\.
=> SELECT gid, ST_AsText(geom) FROM mpoint_geo;
 gid |       st_astext
-----+-----------------------
   1 | MULTIPOINT (42 71, 41.4 70)
(1 row)

5.1.3 - Linestring

One or more connected lines, identified by pairs of consecutive points. A linestring has dimension 1. The boundary of a linestring is a multipoint object containing its start and end points.

The following are examples of linestrings:

Examples

The following example uses the GEOMETRY type to create a table, use copy to load a linestring to the table, and then queries the table to view the linestring:

=> CREATE TABLE linestring_geom (gid int, geom GEOMETRY(1000));
CREATE TABLE
=> COPY linestring_geom(gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>1|LINESTRING(0 0, 1 1, 2 2, 3 4, 2 4, 1 5)
>>\.
=> SELECT gid, ST_AsText(geom) FROM linestring_geom;
 gid |                 ST_AsText
-----+-------------------------------------------
   1 | LINESTRING (0 0, 1 1, 2 2, 3 4, 2 4, 1 5)
(1 row)

The following example uses the GEOGRAPHY type to create a table, use copy to load a linestring to the table, and then queries the table to view the linestring:

=> CREATE TABLE linestring_geog (gid int, geog GEOGRAPHY(1000));
CREATE TABLE
=> COPY linestring_geog(gid, gx filler LONG VARCHAR, geog AS ST_GeographyFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>1|LINESTRING(42.1 71, 41.4 70, 41.3 72.9, 42.99 71.46, 44.47 73.21)
>>\.
=> SELECT gid, ST_AsText(geog) FROM linestring_geog;
 gid |                             ST_AsText
-----+--------------------------------------------------------------------
   1 | LINESTRING (42.1 71, 41.4 70, 41.3 72.9, 42.99 71.46, 44.47 73.21)
(1 row)

5.1.4 - Multilinestring

A collection of zero or more linestrings. A multilinestring has no dimension. The boundary of a multilinestring is a multipoint object containing the start and end points of all the linestrings.

The following are examples of multilinestrings:

Examples

The following example uses the GEOMETRY type to create a table, use copy to load a multilinestring to the table, and then queries the table to view the multilinestring:

=> CREATE TABLE multilinestring_geom (gid int, geom GEOMETRY(1000));
CREATE TABLE
=> COPY multilinestring_geom(gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>1|MULTILINESTRING((1 5, 2 4, 5 3, 6 6),(3 5, 3 7))
>>\.
=> SELECT gid, ST_AsText(geom) FROM multilinestring_geom;
 gid |                     ST_AsText
-----+----------------------------------------------------
   1 | MULTILINESTRING ((1 5, 2 4, 5 3, 6 6), (3 5, 3 7))
(1 row)

The following example uses the GEOGRAPHY type to create a table, use copy to load a multilinestring to the table, and then queries the table to view the multilinestring:

=> CREATE TABLE multilinestring_geog (gid int, geog GEOGRAPHY(1000));
CREATE TABLE
=> COPY multilinestring_geog(gid, gx filler LONG VARCHAR, geog AS ST_GeographyFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>1|MULTILINESTRING((42.1 71, 41.4 70, 41.3 72.9), (42.99 71.46, 44.47 73.21))
>>\.
=> SELECT gid, ST_AsText(geog) FROM multilinestring_geog;
 gid |                                  ST_AsText
-----+----------------------------------------------------------------------------
   1 | MULTILINESTRING((42.1 71, 41.4 70, 41.3 72.9), (42.99 71.46, 44.47 73.21))
(1 row)

5.1.5 - Polygon

An object identified by a set of closed linestrings. A polygon can have one or more holes, as defined by interior boundaries, but all points must be connected. Two examples of polygons are:

Inclusive and exclusive polygons

Polygons that include their points in clockwise order include all space inside the perimeter of the polygon and exclude all space outside that perimeter. Polygons that include their points in counterclockwise order exclude all space inside the perimeter and include all space outside that perimeter.

Examples

The following example uses the GEOMETRY type to create a table, use copy to load a polygon into the table, and then queries the table to view the polygon:

=> CREATE TABLE polygon_geom (gid int, geom GEOMETRY(1000));
CREATE TABLE
=> COPY polygon_geom(gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>1|POLYGON(( 2 6, 2 9, 6 9, 7 7, 4 6, 2 6))
>>\.
=> SELECT gid, ST_AsText(geom) FROM polygon_geom;
 gid |                 ST_AsText
-----+------------------------------------------
   1 | POLYGON((2 6, 2 9, 6 9, 7 7, 4 6, 2 6))
(1 row)

The following example uses the GEOGRAPHY type to create a table, use copy to load a polygon into the table, and then queries the table to view the polygon:

=> CREATE TABLE polygon_geog (gid int, geog GEOGRAPHY(1000));
CREATE TABLE
=> COPY polygon_geog(gid, gx filler LONG VARCHAR, geog AS ST_GeographyFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>1|POLYGON((42.1 71, 41.4 70, 41.3 72.9, 44.47 73.21, 42.99 71.46, 42.1 71))
>>\.
=> SELECT gid, ST_AsText(geog) FROM polygon_geog;
 gid |                                ST_AsText
-----+---------------------------------------------------------------------------
   1 | POLYGON((42.1 71, 41.4 70, 41.3 72.9, 44.47 73.21, 42.99 71.46, 42.1 71))
(1 row)

5.1.6 - Multipolygon

A collection of zero or more polygons that do not overlap.

Examples

The following example uses the GEOMETRY type to create a table, use copy to load a multipolygon into the table, and then queries the table to view the polygon:

=> CREATE TABLE multipolygon_geom (gid int, geom GEOMETRY(1000));
CREATE TABLE
=> COPY multipolygon_geom(gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>9|MULTIPOLYGON(((2 6, 2 9, 6 9, 7 7, 4 6, 2 6)),((0 0, 0 5, 1 0, 0 0)),((0 2, 2 5, 4 5, 0 2)))
>>\.
=> SELECT gid, ST_AsText(geom) FROM polygon_geom;
 gid |                                           ST_AsText
-----+----------------------------------------------------------------------------------------------
   9 | MULTIPOLYGON(((2 6, 2 9, 6 9, 7 7, 4 6, 2 6)),((0 0, 0 5, 1 0, 0 0)),((0 2, 2 5, 4 5, 0 2)))
(1 row)

The following example uses the GEOGRAPHY type to create a table, use copy to load a multipolygon into the table, and then queries the table to view the polygon:

=> CREATE TABLE multipolygon_geog (gid int, geog GEOGRAPHY(1000));
CREATE TABLE
=> COPY polygon_geog(gid, gx filler LONG VARCHAR, geog AS ST_GeographyFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>1|POLYGON((42.1 71, 41.4 70, 41.3 72.9, 44.47 73.21, 42.99 71.46, 42.1 71))
>>\.
=> SELECT gid, ST_AsText(geog) FROM polygon_geog;
 gid |                                ST_AsText
-----+---------------------------------------------------------------------------
   1 | POLYGON(((42.1 71, 41.4 70, 41.3 72.9, 42.1 71)),((44.47 73.21, 42.99 71.46, 42.1 71, 44.47 73.21)))
(1 row)

5.2 - Spatial object representations

The OGC defines two ways to represent spatial objects:

• Well-known text (WKT)
• Well-known binary (WKB)

5.2.1 - Well-known text (WKT)

Well-Known Text (WKT) is an ASCII representation of a spatial object.

WKTs are not case sensitive; Vertica recognizes any combination of lowercase and uppercase letters.

Some examples of valid WKTs are:

Invalid WKTs are:

• POINT(1 NAN), POINT(1 INF)—Coordinates must be numbers.

• POLYGON((1 2, 1 4, 3 4, 3 2))—A polygon must be closed.

• POLYGON((1 4, 2 4))—A linestring is not a valid polygon.

5.2.2 - Well-known binary (WKB)

Well-Known Binary (WKB) is a binary representation of a spatial object. This format is primarily used to port spatial data between applications.

5.3 - Spatial definitions

The OGC defines properties that describe

• Characteristics of spatial objects

• Spatial relationships that can exist among objects

Vertica provides functions that test for and analyze the following properties and relationships.

5.3.1 - Boundary

The set of points that define the limit of a spatial object:

• Points, multipoints, and geometrycollections do not have boundaries.

• The boundary of a linestring is a multipoint object. This object contains its start and end points.

• The boundary of a multilinestring is a multipoint object. This object contains the start and end points of all the linestrings that make up the multilinestring.

• The boundary of a polygon is a linestring that begins and ends at the same point. If the polygon has one or more holes, the boundary is a multilinestring that contains the boundaries of the exterior polygon and any interior polygons.

• The boundary of a multipolygon is a multilinestring that contains the boundaries of all the polygons that make up the multipolygon.

5.3.2 - Buffer

The set of all points that are within or equal to a specified distance from the boundary of a spatial object. The distance can be positive or negative.

Positive buffer:

Negative buffer:

5.3.3 - Contains

One spatial object contains another spatial object if its interior includes all points of the other object. If an object such as a point or linestring only exists along a polygon's boundary, the polygon does not contain it. If a point is on a linestring, the linestring contains it; the interior of a linestring is all the points on the linestring except the start and end points.

Contains(a, b) is spatially equivalent to within(b, a).

5.3.4 - Convex hull

The smallest convex polygon that contains one or more spatial objects.

In the following figure, the dotted lines represent the convex hull for a linestring and a triangle.

5.3.5 - Crosses

Two spatial objects cross if both of the following are true:

• The two objects have some but not all interior points in common.

• The dimension of the result of their intersection is less than the maximum dimension of the two objects.

5.3.6 - Disjoint

Two spatial objects have no points in common; they do not intersect or touch.

5.3.7 - Envelope

The minimum bounding rectangle that contains a spatial object.

The envelope for the following polygon is represented by the dotted lines in the following figure.

5.3.8 - Equals

Two spatial objects are equal when their coordinates match exactly. Synonymous with spatially equivalent.

The order of the points do not matter in determining spatial equivalence:

• LINESTRING(1 2, 4 3) equals LINESTRING(4 3, 1 2).

• POLYGON ((0 0, 1 1, 1 2, 2 2, 2 1, 3 0, 1.5 –1.5, 0 0)) equals POLYGON((1 1 , 1 2, 2 2, 2 1, 3 0, 1.5 –1.5, 0 0, 1 1)).

• MULTILINESTRING((1 2, 4 3),(0 0, –1 –4)) equals MULTILINESTRING((0 0, –1 –4),(1 2, 4 3)).

5.3.9 - Exterior

The set of points not contained within a spatial object nor on its boundary.

5.3.10 - GeometryCollection

A set of zero or more objects from any of the supported classes of spatial objects.

5.3.11 - Interior

The set of points contained in a spatial object, excluding its boundary.

5.3.12 - Intersection

The set of points that two or more spatial objects have in common.

5.3.13 - Overlaps

If a spatial object shares space with another object, but is not contained within that object, the objects overlap. The objects must overlap at their interiors; if two objects touch at a single point or intersect only along a boundary, they do not overlap.

5.3.14 - Relates

When a spatial object is spatially related to another object as defined by a DE-9IM pattern matrix string.

A DE-9IM pattern matrix string identifies how two spatial objects are spatially related to each other. For more information about the DE-9IM standard, see Understanding Spatial Relations.

5.3.15 - Simple

For points, multipoints, linestrings, or multilinestrings, a spatial object is simple if it does not intersect itself or has no self-tangency points.

Polygons, multipolygons, and geometrycollections are always simple.

5.3.16 - Symmetric difference

The set of all points of a pair of spatial objects where the objects do not intersect. This difference is spatially equivalent to the union of the two objects less their intersection. The symmetric difference contains the boundaries of the intersections.

In the following figure, the shaded areas represent the symmetric difference of the two rectangles.

The following figure shows the symmetric difference of two overlapping linestrings.

5.3.17 - Union

For two or more spatial objects, the set of all points in all the objects.

5.3.18 - Validity

For a polygon or multipolygon, when all of the following are true:

• It is closed; its start point is the same as its end point.

• Its boundary is a set of linestrings.

• No two linestrings in the boundary cross. The linestrings in the boundary may touch at a point but they cannot cross.

• Any polygons in the interior must be completely contained; they cannot touch the boundary of the exterior polygon except at a vertex.

Valid polygons:

Invalid polygon:

5.3.19 - Within

A spatial object is considered within another spatial object when all its points are inside the other object's interior. Thus, if a point or linestring only exists along a polygon's boundary, it is not considered within the polygon. The polygon boundary is not part of its interior.

If a point is on a linestring, it is considered within the linestring. The interior of a linestring is all the points along the linestring, except the start and end points.

Within(a, b) is spatially equivalent to contains(b, a).

6 - Spatial data type support limitations

Vertica does not support all types of GEOMETRY and GEOGRAPHY objects. See the respective function page for a list of objects that function supports. Spherical geometry is generally more complex than Euclidean geometry. Thus, there are fewer spatial functions that support the GEOGRAPHY data type.

Limitations of spatial data type support:

• A non-WGS84 GEOGRAPHY object is a spatial object defined on the surface of a perfect sphere of radius 6371 kilometers. This sphere approximates the shape of the earth. Other spatial programs may use an ellipsoid to model the earth, resulting in slightly different data.

• You cannot modify the size or data type of a GEOMETRY or GEOGRAPHY column after creation.

• You cannot import data to or export data from tables that contain spatial data from another Vertica database.

• You can only use the STV_Intersect functions with points and polygons.

• GEOGRAPHY objects of type GEOMETRYCOLLECTION are not supported.

• Values for longitude must be between -180 and +180 degrees. Values for latitude must be between –90 and +90 degrees. The Vertica geospatial functions do not validate these values.

• GEOMETRYCOLLECTION objects cannot contain empty objects. For example, you cannot specify GEOMETRYCOLLECTION (LINESTRING(1 2, 3 4), POINT(5 6), POINT EMPTY).

• If you pass a spatial function a NULL geometry, the function returns NULL, unless otherwise specified. A result of NULL has no value.

• Polymorphic functions, such as NVL and GREATEST, do not accept GEOMETRY and GEOGRAPHY arguments.