json - Spring 从 PostgreSQL 转换为 Geojson FeatureCollection
问题描述
我在将数据从 postgreSQL 转换为 geojson 并将数据公开到指定端点时遇到问题。
数据库中的数据(数据库名称:商店):
shop_id => BIGINT shop_name => VARCHAR Shop_position => geography => 使用 ST_AsGeoJson 后我们收到经度和纬度 Shop_radius => DOUBLE
存储库:
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); }
}
服务:
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");
}
}
}
}
控制器:
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);
}
}
}
此控制器返回:
[
{
"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}}]"
}
}
]
但是“值”属性内部存在一个问题,它不是 json 而是一个字符串。我没有收到json的错误是什么?我猜这是一个糟糕的 SQL 语法。
谁能帮助我?我想接收类似这样的格式:
{"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"}}]}
解决方案
我猜这是一个糟糕的 SQL 语法。
不,这不是一个糟糕的语法。返回的内容相当于 javascripts JSON.stringify
。
您需要使用 Jackson 对象映射器将“值”映射到 POJO。看看这个答案的方法来做到这一点。
推荐阅读
- reactjs - 反应原生
调试模式打开时未在 Android 上调用回调 - javascript - 我无法使用 JavaScript 访问画布上的输入值
- r - 如何从一行中删除除模式之外的所有内容
- ios - ionic3 ios -14 架构 x86_64 的重复符号
- google-chrome-extension - 添加了小促销磁贴,但即使已通过审核,它也不会出现在商店中
- shell - Raspberry Pi 屏幕保护程序/DMPS 打开/关闭以在更改时触发 shell 脚本
- amazon-web-services - 将使用 docker-compose 创建的应用程序部署到 AWS
- javascript - 每次事件发生时随机数变化
- c++ - 确定将圆弧转换为线段所需的点数
- kotlin - Kotlin:枚举中的泛型