node extraction via postgresql

Question

It is possible via commands in Postgres to process vector data. I have a table with data that represent the displacement of objects and need to extract final and initial Vertice. This is possible via Postgresql?

Answer 1

Short answer: yes. If you use data types that support arrays, in Postgres you can extract any elements from them, whatever their positions. Now let’s go to the long answer...

Postgresql without extensions

Assuming that you are dealing with geographical coordinate points such as those extracted from a GPS, and represent your data as series of latitude/longitude pairs, in "pure" Postgresql you can store them in arrays or json. Both methods make it possible to extract arbitrary items.

---

Arrays

So a series of geographical positions like the following, take a random GPX here...

34.295944 132.319809
34.295971 132.319809
34.295998 132.320129
34.296024 132.319763

...can be represented as a array of arrays with pairs of "floats":

ARRAY[
  [34.295944,  132.319809], [34.295971,  132.319809], 
  [34.295998,  132.320129], [34.296024,  132.319763]
]

To obtain the first and last elements of array, use the functions dedicated to array postgres:

with paths as (
 select ARRAY[
  [34.295944,  132.319809], [34.295971,  132.319809],
  [34.295998,  132.320129], [34.296024,  132.319763]
 ] as path
)
select path[1:1] as "primeiro", path[array_length(path, 1):] as "ultimo" from paths;
         primeiro         |          ultimo          
--------------------------+--------------------------
 {{34.295944,132.319809}} | {{34.296024,132.319763}}
(1 row)

A limitation of the use of arrays is the impossibility of creating mixed data type structures. A table field to contain the above data should be declared as array Numeric two-dimensional, the second being bounded by two elements:

create table teste (path numeric(9,6)[][2]);
insert into teste values (ARRAY[
  [34.295944,  132.319809], [34.295971,  132.319809],
  [34.295998,  132.320129], [34.296024,  132.319763]
 ]);
select * from teste;
                                             path                                              
-----------------------------------------------------------------------------------------------
 {{34.295944,132.319809},{34.295971,132.319809},{34.295998,132.320129},{34.296024,132.319763}}
(1 row)

---

JSON

The same data can be represented in array of JSON objects as follows:

'[
  {"lat": 34.295944, "lon": 132.319809},
  {"lat": 34.295971, "lon": 132.319809},
  {"lat": 34.295998, "lon": 132.320129},
  {"lat": 34.296024, "lon": 132.319763}
]'::json

And therefore consulted with JSON manipulation functions and operators:

with paths as (
 select '[
  {"lat": 34.295944, "lon": 132.319809},
  {"lat": 34.295971, "lon": 132.319809},
  {"lat": 34.295998, "lon": 132.320129},
  {"lat": 34.296024, "lon": 132.319763}
 ]'::json as path
)
select path->0 as primeiro, path->json_array_length(path)-1 as ultimo from paths;
               primeiro                |                ultimo                 
---------------------------------------+---------------------------------------
 {"lat": 34.295944, "lon": 132.319809} | {"lat": 34.296024, "lon": 132.319763}
(1 row)

The use of JSON is more flexible than that of array, support mixed data types. Both solutions support indexing, especially type data fields jsonb. Just create a column with the appropriate data type and put data into it in JSON of any type and size:

create table teste (path jsonb);
insert into teste values ('[
  {"lat": 34.295944, "lon": 132.319809},
  {"lat": 34.295971, "lon": 132.319809},
  {"lat": 34.295998, "lon": 132.320129},
  {"lat": 34.296024, "lon": 132.319763}
 ]');
select * from teste;
                                                                             path                                                                             
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"lat": 34.295944, "lon": 132.319809}, {"lat": 34.295971, "lon": 132.319809}, {"lat": 34.295998, "lon": 132.320129}, {"lat": 34.296024, "lon": 132.319763}]
(1 row)

---

Postgis

That said, for serious geoprocessing work you can also use the extension Postgis, that offers types of data, functions, indexes and other specific objects for this type of task. An interesting feature for your case may be the ability to interpolar a point along a line.

In Postgis, point series can be represented as a comma-separated string lat/Lon, and converted to geometry data types through a function:

select ST_GeomFromText('LINESTRING(34.295944 132.319809, 34.295971 132.319809, 34.295998 132.320129, 34.296024 132.319763)');

I don’t have, however, a Postgis environment created at the moment, and I imagine that the subject already runs a little away from the scope of the question "is it possible to extract the initial and final vertex of a vector with Postgresql?". If you want to know more about the Postgis extension, it has documentation as well as Postgresql’s own.