0

Trying to get a valid geojson response from my PostgreSQL(9.6.3) + PostGIS (2.3.2) to track routes. I have a table of GPS positions of the form:

CREATE TABLE public.gps
(
  id integer NOT NULL DEFAULT nextval('gps_id_seq'::regclass),
  device_id integer, -- Device ID of GPS Device
  timecol bigint, -- Timestamp we use for linking video and GPS
  distance_from_hub double precision, -- Distance from nearest hub
  distance_from_hub_id integer, -- ID of nearest hub
  itudes_3d geography(PointM,4326)
)

I am trying to get a valid GeoJSON with the route as a LineString. The coordinates of each point are stored in the itudes_3d. I am querying with:

select jsonb_build_object(
        'type', 'FeatureCollection',
        'features', jsonb_agg(feature)) AS route
        FROM ( select jsonb_build_object(
            'type','Feature',
            'properties',jsonb_build_object(
              'opa', 1,
              'fillopa', 0.5,
              'type', 0,
              'weight', 1,
              'start_time', :start_time:,
              'end_time', :end_time:,
              'colour', 
              '#333'),
            'geometry', geometry.geoline::json) 
            FROM (select ST_AsGeoJSON(ST_MakeLine(geom.itudes_3d))::jsonb as geoline 
              FROM (select itudes_3d::geometry, ST_M(itudes_3d::geometry) as gtime
                FROM gps 
                WHERE device_id=:device_id: AND 
                timecol > :start_time: AND 
                timecol < :end_time: 
                ORDER BY timecol ASC) 
              AS geom) 
            AS geometry)
         AS feature

where :start_time:,:end_time: and ":device_id:" are placeholders for this explanation.

What I get back is:

{"type": "FeatureCollection", "features": [{"jsonb_build_object": {"type": "Feature", "geometry": {"type": "LineString", "coordinates": [[174.821949, -35.829088], [174.820518, -35.82855], [174.818997, -35.827884], [174.818997, -35.827884], [174.818696, -35.827857], [174.818363, -35.827694], [174.816604, -35.826887], [174.816275, -35.826737], [174.815868, -35.826569], [174.815188, -35.826255], etc...

The problem I have is the {"jsonb_build_object": label in the features... really I want:

{"type": "FeatureCollection", "features": [{"type": "Feature", "geometry": {"type": "LineString", "coordinates": [[174.821949, -35.829088], [174.820518, -35.82855], [174.818997, -35.827884], [174.818997, -35.827884], [174.818696, -35.827857], [174.818363, -35.827694], [174.816604, -35.826887], [174.816275, -35.826737], [174.815868, -35.826569], [174.815188, -35.826255], etc...

Can anyone identify where I have gone wrong in constructing this?

To me it looks like the subquery is being named in the output.

1

Try changing:

'features', jsonb_agg(feature)) AS route

to

'features', jsonb_agg(feature.jsonb_build_object)) AS route

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.