insert GEOGRAPHY_POINT type data

[sp-suresh]

How to insert data in table which contains GEOGRAPHY_POINT type column?. The currently supported data types are must be one of: array, null tinyint, smallint, integer, bigint, float, string, timestamp or decimal.

[benjaminballard]

The library doesn't yet support GEOGRAPHY or GEOGRAPHY_POINT datatypes. It might be possible as a workaround to pass in some arrays of supported data types to a java stored procedure, and then convert these within the procedure, but it would be nice to add support for these datatypes to the library.

Are you asking out of curiosity, or do you plan to use these datatypes for an application? I will log a feature request ticket, and it will help to know the priority.

Thanks,
Ben

[sp-suresh]

Yes, my application receives location details which I insert in voltdb using default upsert procedure. I have to avoid the use of Java for creating procedures. It will be better if you log a feature request ticket for this.

[benjaminballard]

Do you need only GEOGRAPHY_POINT or also GEOGRAPHY (polygon)? I can do a feature request for both, but want to prioritize what you actually need. Also how are the location details provided. Can you share a few lines of javascript code where you create variables that look like the location details your application would receive?

[sp-suresh]

Well, I need an index which can enable querying rows for specified geometrical region. Coming to what kind of location details my application receives, It's actually Latitude and Longitude values.
Here is my partial java code which inserts randomly generated data.

var pingsUpsertProc = new VoltProcedure('DEVICEPINGS.upsert', [
  'integer', 'bigint', 'float', 'float', 'float', 'float', 'float', 'float', 'float', 'float', 'tinyint', 'tinyint', 'tinyint', 'bigint']
);

function insertTestData(deviceCount, totalCount){
  var upsertQuery = pingsUpsertProc.getQuery();

  for(var devId = 1; devId <= deviceCount; devId++){
    var cuuDate = Math.round(Date.now()/1000);
    for(var i = 0; i < totalCount; i++){
      upsertQuery.setParameters([
        devId,
        1,
         Math.floor(Math.random()*(361)-180),
         Math.floor(Math.random()*(181)-90),
         Math.floor(Math.random()*(101)),
         Math.floor(Math.random()*(181)-90),
         Math.floor(Math.random()*(181)-90),
         Math.floor(Math.random()*(181)-90),
         Math.floor(Math.random()*(181)-90),
         Math.floor(Math.random()*(181)-90),
         Math.floor(Math.random()*(2)),
         Math.floor(Math.random()*(2)),
         Math.floor(Math.random()*(2)), cuuDate + devId + i]);
      
      client.callProcedure(upsertQuery);
    }
  }
}

Here is Table Schema:

CREATE TABLE devicepings 
  ( 
     deviceid        INTEGER, 
     rideid          BIGINT, 
     latitude        FLOAT, 
     longitude       FLOAT, 
     speed           FLOAT, 
     bearing         FLOAT, 
     angle           FLOAT, 
     horizontalerror FLOAT, 
     verticalerror   FLOAT, 
     temprature      FLOAT, 
     ignition        TINYINT, 
     motion          TINYINT, 
     relaystate      TINYINT, 
     ts              BIGINT, 
     PRIMARY KEY (deviceid) 
  ); 

[benjaminballard]

Sorry for the delay, and thanks for sharing this detail.

To get the best use out of the geospatial features in VoltDB, I think you'll want to modify your table to use the GEOGRAPHY_POINT datatype rather than separate lat/long float columns.

VoltDB doesn't support indexes on GEOGRAPHY_POINT columns however, but we have a special type of index for GEOGRAPHY columns (polygons representing regions). There is an optimization that leverage such an index for a query that uses an expression WHERE CONTAINS(region, point). In other words, with an index on the regions, you can quickly find (using an index) the regions that contain a given point.

As far as the client-side datatype support, I don't know if this is very important or useful. We do provide support for geography datatypes in Java and C++, but not in any of the other client libraries. In the case of the java client, the support is a set of custom types (org.voltdb.types.GeographyPointValue and org.voltdb.types.GeographyValue). These provide some very useful methods, but they aren't an industry standard datatype. They can be created from WKT (well-known text) strings, which are an industry standard.

You can use those java datatypes in a stored procedure, but that would only be useful if you needed to make use of their methods such as for scaling, add/subtract, rotate, etc.

Alternatively, you could pass in WKT strings from a client, use String inputs to a procedure (or use DDL-declared procedures where you just specify ?) and convert these to the datatypes in SQL using the SQL functions POINTFROMTEXT(?) or POLYGONFROMTEXT(?).

[dpz3579]

Hi @benjaminballard ,

I did try that and the client is saving the geography point in my voltdb.

I still have one question.
Why is the GEOGRAPHY_POINT datatype not supporting indexing?
Whereas GEOGRAPHY datatype supports indexing.

What's the reason behind this behaviour can you help me with it.

[benjaminballard]

I think it's just that this hasn't been implemented. Indexes on VARCHAR, timestamp, or numerical columns are more general where you can use the index to find matches, to perform range scans, or to scan pre-sorted records. In the case of GEOGRAPHY, it helps more quickly find geography values that contain a given GEOGRAPHY_POINT. With GEOGRAPHY_POINT, there isn't necessarily an order of records, and one is more often looking for approximate matches or using contains than looking for exact matches. I believe you can still use function-based indexes, such as if you wanted to create an index on LATITUDE( point ) so you could do a scan within latitude, but there isn't a pure index on GEOGRAPHY_POINT because nothing was implemented that way to optimize for any particular type of query.