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