Saving array of objects in PostGIS field?

Question

It is possible in PostGIS to save an array of objects in a field, and GeoServer able to read it? (timestamps)

For example, a spatial table with the fields: "name", "timestamps" and "the_geom" would look like in a json response from GeoServer as:

{"type":"FeatureCollection","totalFeatures":1,"features":[{"type":"Feature","id":1,"geometry":{"type":"LineString","coordinates":[[-74.103465, 4.80778], [-74.103225, 4.8081966666666665], [-74.103225, 4.8081966666666665], [-74.10273666666667, 4.80802], [-74.10273666666667, 4.80802], [-74.10251, 4.807675], [-74.10251, 4.807675], [-74.102965, 4.80741], [-74.102965, 4.80741], [-74.10339166666667, 4.807745], [-74.10339166666667, 4.807745], [-74.10349, 4.807826666666667], [-74.10349, 4.807826666666667], [-74.10410333333333, 4.8071633333333335], [-74.10410333333333, 4.8071633333333335], [-74.10492833333333, 4.806211666666667], [-74.10492833333333, 4.806211666666667]]},"geometry_name":"the_geom","properties":{"name":"test", "timestamps":[358.0,1150.0,1705.0,1971.0,2385.0,3493.0,4506.0,4802.0,4815.0,4838.0,4874.0,4890.0,4995.0,5016.0,5051.0,5443.0,5552.0]}},]}

currently your question doesn't mention GeoServer, please edit it to say what you want GeoServer to do or remove the tag — Ian Turton, May 03 '16 at 08:05
Thanks for the recommendation @iant, I updated the question. — pateto777, May 04 '16 at 17:50

score 1 · Accepted Answer · answered May 02 '16 at 21:41

1

You can have array columns in a table.

For your example table the create statement would be along the following lines.

CREATE TABLE spatial_table (
    name VARCHAR(20),
    timestamps timestamp[],
    the_geom geometry
)

answered May 02 '16 at 21:41

MickyT

3,430
15
19

This is a good approach. Do you know if GeoServer is able to read the arrays for creating a json file? – pateto777 May 02 '16 at 21:46
@pateto777 sorry I misread your comment, so ignore my previous reply. Sorry I don't know if GeoServer can handle that. – MickyT May 02 '16 at 21:52
I added an array field to the spatial table, however GeoServer can't handle this kind of data. Your reply was very useful, thanks. – pateto777 May 02 '16 at 22:19

score 0 · Answer 2 · edited Apr 13 '17 at 12:34

0

You can generate a query that outputs GeoJSON objects with the ST_AsGeoJSON function. Here's a question and answer that explains how:

SQL query to have a complete geojson feature from PostGIS

edited Apr 13 '17 at 12:34

Community

1

answered May 02 '16 at 21:28

HeyOverThere

7,861
1
27
41

I don't want to generate a query. My question is if I can insert an array of objects in a single field. For instance the timestamps values. – pateto777 May 02 '16 at 21:35

Saving array of objects in PostGIS field?

2 Answers2