Spring Convert from PostgreSQL into Geojson FeatureCollection

Question

I've got problem with convert data from postgreSQL into geojson and expose the data to the specified endpoint.

Data in Database (db name: Shops):

shop_id => BIGINT shop_name => VARCHAR Shop_position => geography => after using ST_AsGeoJson we receive longitude and latitude Shop_radius => DOUBLE

Repository:

import com.fasterxml.jackson.core.JsonProcessingException;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;


import java.util.List;
import java.util.Map;


@Repository
@RequiredArgsConstructor
@Slf4j
public class GeoRepository {

    private final JdbcTemplate jdbcTemplate;
   
    String GET_SHOPS = "select 'FeatureCollection' As type, array_to_json(array_agg(f)) As features from (select 'Feature' As type,ST_AsGeoJSON(lg.\"Shop_position\") :: json as geometry,row_to_json((select t from (select \"Shop_id\",\"Shop_name\",\"Shop_radius\") As t )) As properties from public.\"Shops\" As lg) as f;";
   
    public List<Map<String,Object>> getDataFromDb(String query){

        List<Map<String,Object>> data = jdbcTemplate.queryForList(query);
        return data;
    }
    
    public List<Map<String,Object>> getShops() throws JsonProcessingException { return getDataFromDb(GET_SHOPS); }
}

Service:


import com.fasterxml.jackson.databind.ObjectMapper;
import com.liferay.portal.kernel.json.JSONObject;
import lombok.RequiredArgsConstructor;
import org.springframework.boot.admin_panel.exception.DataNotFoundException;
import org.springframework.boot.admin_panel.repository.GeoRepository;
import org.springframework.stereotype.Service;

@Service
@RequiredArgsConstructor
public class GeoService {

    private final GeoRepository geoRepository;
    private ObjectMapper mapper = new ObjectMapper();

    public String getGeoJsonShops(){
        try{
            if(geoRepository.getShops() == null){
                throw new DataNotFoundException("Shops data is not found");
            }else
            return mapper.writeValueAsString(geoRepository.getShops());
        }catch (Exception e){
            System.out.println("Error: " + e);
            throw new NullPointerException("NullPointException");
        }
    }
    
    }
}

Controller:


import com.liferay.portal.kernel.json.JSONObject;
import lombok.RequiredArgsConstructor;
import org.apache.tomcat.util.json.JSONParser;
import org.apache.tomcat.util.json.ParseException;
import org.springframework.boot.admin_panel.service.GeoService;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("/admin_panel")
@CrossOrigin
@RequiredArgsConstructor
public class DataController {

    private final DataService dataService;

    @RequestMapping(value = "/get_shops", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE)
    public ResponseEntity getShops() {
        String outputData = geoService.getGeoJsonShops();
        if(outputData == null){
            return ResponseEntity.notFound().build();
        }else {
            return ResponseEntity.ok(outputData);
        }
    }
}

This controller return:

[
    {
        "type": "FeatureCollection",
        "features": {
            "type": "json",
            "value": "[{\"type\":\"Feature\",\"geometry\":{\"type\":\"Point\",\"coordinates\":[34.642697,74.341718]},\"properties\":{\"Shop_id\":125,\"Shop_name\":\"Grocery_Lux_Shop\",Shop_radius\":0.34637}}]"
        }
    }
]

But there's one problem inside "value" property it's not json but a string. What has been wrongly made that I dont receive json? I guess it's a badly SQL syntax.

Could anyone help me ? I want to receive format similarly like this:

{"type" : "FeatureCollection", "features" : [{"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]}, "properties": {"id": 1, "name": "one"}}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[2,2]}, "properties": {"id": 2, "name": "two"}}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[3,3]}, "properties": {"id": 3, "name": "three"}}]}

score 0 · Answer 1 · answered Jul 20 '20 at 16:35

0

I guess it's a badly SQL syntax.

No, it is not a bad syntax. What is being returned is equivalent to javascripts JSON.stringify.

You need to map the 'value' to a POJO using Jackson object mapper. Look at this answer for a way to do it.

answered Jul 20 '20 at 16:35

Adil Khalil

2,073
3
21
33

Ok I understand, but I could reach this target without creating class Shop with attributes? I don't want to create other classes and focus only on repository,service and controller. – Dominic Jul 20 '20 at 16:47
try assigning it to a variable of type 'Object' and then read the value to that variable using ObjectMapper. I am not sure, how nested fields will be treated. – Adil Khalil Jul 20 '20 at 16:49
Object not supports nested fields, could You show me how to do this with POJO ? Maybe I must forEach my List> into Map and then assigning to variables? – Dominic Jul 20 '20 at 17:15

Spring Convert from PostgreSQL into Geojson FeatureCollection

1 Answers1