pick&deliver "time" types

[cvvergara]

discussion about types started here:

https://github.com/pgRouting/pgrouting/issues/520

[cvvergara]

ingore the names, the names are not yet the final names

 orders_sql
      id BIGINT,
      demand FLOAT,
      pick_x FLOAT,
      pick_y FLOAT,
      pick_open TIMESTAMP,
      pick_close TIMESTAMP,
      pick_service FLOAT,
      drop_x FLOAT,
      drop_y FLOAT,
      drop_open TIMESTAMP,
      drop_close TIMESTAMP,
      drop_service FLOAT
);

I must say that service is service_time :

      pick_service TIMESTAMP,
      drop_service TIMESTAMP

[cvvergara]

About TIMESTAMP:

postgres time handling user point of view

This is clear for a postgreSQL user: (which btw I am not one yet, except for testing pgRouting) https://www.postgresql.org/docs/9.1/static/datatype-datetime.html

The user can choose how to store the time:

type	size
timestamp [ (p) ] [ without time zone ]	8 bytes
timestamp [ (p) ] with time zone	8 bytes
date	4 bytes
time [ (p) ] [ without time zone ]	8 bytes
time [ (p) ] with time zone	12 bytes
interval [ fields ] [ (p) ]	16 bits

postgresql time handling developer point of view

Not much documented

I can read the code and try to figure out how to interpret the structure

C++ time handling is available on C++11; On v2.0 and before, pgRouting was using C++98, now its using C++0x, not yet using C++11 So, the time handling has to be done then with postgresql time structures and functions: pick&deliver is all times so: for example after calculating the euclidean distance and given a speed, get the time: time = distance / speed

• result is a plain value in the reals
• stored as double in C/C++
• (as far as I can see in 2 seconds glance) stored like this in postgresql snipet from btw, I don't see (at least not on that file) the time operators like adding times, might be in some other file probably on utils

/* keep this struct small; it gets used a lot */
  210 typedef struct
  211 {
  212     char        token[TOKMAXLEN + 1];   /* always NUL-terminated */
  213     char        type;           /* see field type codes above */
  214     int32       value;          /* meaning depends on type */
  215 } datetkn;
  216 

"meaning depends on the type" On postgreSQL what type has what meaning. or is it for the user what meaning has what type?

Postgres is all written on C, if going to use internally the TIMESTAMP code on C++, Then basically the C++ code will depend on postgres code

I already manage to only use postgres code on the C code and the C++ "driver" code that connects the C with C++ code uses only palloc & pfree (took me about 1 1/2 years to find out how to do it, before that was using malloc) (this possibility is top secret) The fact that the C++ code of pgRouting does not need the postgres code gives the opportunity to use pgRouting C++ code independently of the postgreSQL database because the heart of pgRouting is about the algorithms not about how the data is stored. the C/driver (inC++) codes are just connectors. So using the postgres C code in the C++ will have the draw back of not being able to do the top secret possibility

Difficult enough was to figure out how to use palloc and pfree on the dirvers! which by the way is the only postgres code used on the drivers.

[cvvergara]

About GEOMETRY

This is a geometry:

010200000002000000000000000000004000000000000000000000000000000040000000000000F03F

This is also a geometry:

010100000000000000000000400000000000000000

Validation is needed:

• SRID, is it valid?
• is it a 1 Dimension geometry?
• how are the postgis errors to be handled?

  ERROR:  Argument to ST_X() must be a point

This snipet shows how a pgRouting function is defined at postgres level:

CREATE OR REPLACE FUNCTION pgr_floydWarshall(edges_sql TEXT, directed BOOLEAN DEFAULT TRUE, 
  OUT start_vid BIGINT, OUT end_vid BIGINT, OUT agg_cost float)
  RETURNS SETOF RECORD AS
 '$libdir/${PGROUTING_LIBRARY_NAME}', 'floydWarshall'  
LANGUAGE c IMMUTABLE STRICT;

Suppose that one of the columns in edges_sql is of type GEOMETRY https://github.com/pgRouting/pgrouting/blob/master/src/common/src/get_check_data.c#L73 it will need to check if its a geometry then a new function similar to: https://github.com/pgRouting/pgrouting/blob/master/src/common/src/get_check_data.c#L125 and similar to this snipet

 PG_FUNCTION_INFO_V1(LWGEOM_length_linestring);
  312 Datum LWGEOM_length_linestring(PG_FUNCTION_ARGS)
  313 {
  314         GSERIALIZED *geom = PG_GETARG_GSERIALIZED_P(0);
  315         LWGEOM *lwgeom = lwgeom_from_gserialized(geom);
  316         double dist = lwgeom_length(lwgeom);
  317         lwgeom_free(lwgeom);
  318         PG_FREE_IF_COPY(geom, 0);
  319         PG_RETURN_FLOAT8(dist);
  320 }

to get the actual value geometry value. but for doing that, postGIS source code would be needed for the includes that might be needed:

   30 #include "utils/geo_decls.h"
   31 
   32 #include "../postgis_config.h"
   33 #include "liblwgeom.h"
   34 #include "lwgeom_pg.h"
   35 

Which means adding dependencies for compiling pgRouting like liblwgeom and maybe postgis itself.

[cvvergara]

I am closing, because at the end it looks like one of my monologues