태그 보관물: sql

sql

PostGIS에서 완전한 geojson 기능을 가진 SQL 쿼리? SELECT ‘FeatureCollection’

PostGIS의 속성이있는 geojson 기능을 얻고 싶습니다. 기능 모음이있는 예제 를 찾았 지만 기능에 대해서만 작동하도록 만들 수는 없습니다.

SELECT row_to_json(fc)
 FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
 FROM (SELECT 'Feature' As type
    , ST_AsGeoJSON(lg.geog)::json As geometry
    , row_to_json(lp) As properties
   FROM locations As lg
         INNER JOIN (SELECT loc_id, loc_name FROM locations) As lp
       ON lg.loc_id = lp.loc_id  ) As f )  As fc;

지금까지 예제의 기능 모음 쿼리를 수정하려고했습니다. 그러나 출력이 유효하지 않습니다.

답변

이 작업은 json_build_objectPostgreSQL 9.4 이상에서 조금 더 간단하게 수행 할 수 있으며 ,이를 통해 키 / 값 인수를 번갈아 제공하여 JSON을 구축 할 수 있습니다. 예를 들면 다음과 같습니다.

SELECT json_build_object(
    'type',       'Feature',
    'id',         gid,
    'geometry',   ST_AsGeoJSON(geom)::json,
    'properties', json_build_object(
        'feat_type', feat_type,
        'feat_area', ST_Area(geom)::geography
     )
 )
 FROM input_table;

PostgreSQL 9.5+에서는 jsonb데이터 유형 ( docs )에 일부 새로운 연산자가 추가되는 상황이 훨씬 좋아졌습니다 . 이것은 id와 geometry를 제외한 모든 것을 포함하는 “properties”객체를 쉽게 설정할 수있게합니다 .

SELECT jsonb_build_object(
    'type',       'Feature',
    'id',         gid,
    'geometry',   ST_AsGeoJSON(geom)::jsonb,
    'properties', to_jsonb(row) - 'gid' - 'geom'
) FROM (SELECT * FROM input_table) row;

FeatureCollection을 만들고 싶습니까? 그냥 다음과 같이 마무리하십시오 jsonb_agg.

SELECT jsonb_build_object(
    'type',     'FeatureCollection',
    'features', jsonb_agg(features.feature)
)
FROM (
  SELECT jsonb_build_object(
    'type',       'Feature',
    'id',         gid,
    'geometry',   ST_AsGeoJSON(geom)::jsonb,
    'properties', to_jsonb(inputs) - 'gid' - 'geom'
  ) AS feature
  FROM (SELECT * FROM input_table) inputs) features;

답변

이 답변은 9.4 이전의 PostgreSQL 버전에서 사용할 수 있습니다. PostgreSQL 9.4 이상에 dbaston의 답변 사용

조회는 다음과 같습니다 ( 'GEOM'지오메트리 필드, idjson 특성에 포함 할 필드 shapefile_feature, 테이블 이름 및 489445원하는 피처의 ID 임)

SELECT row_to_json(f) As feature \
     FROM (SELECT 'Feature' As type \
     , ST_AsGeoJSON('GEOM')::json As geometry \
     , row_to_json((SELECT l FROM (SELECT id AS feat_id) As l)) As properties \
     FROM shapefile_feature As l WHERE l.id = 489445) As f;

산출:

{
   "geometry":{
      "type":"MultiPolygon",
      "coordinates":[
         [
            [
               [
                  -309443.24253826,
                  388111.579584133
               ],
               [
                  -134666.391073443,
                  239616.414560895
               ],
               [
                  -308616.222736376,
                  238788.813082666
               ],
               [
                  -309443.24253826,
                  388111.579584133
               ]
            ]
         ]
      ]
   },
   "type":"Feature",
   "properties":{
      "feat_id":489445
   }
}

답변

dbaston의 답변 을 약간 수정했습니다 (댓글을 달았지만 포인트는 없습니다) ST_AsGeoJSON의 출력을 json ( ::json일) 으로 캐스팅해야합니다 .

SELECT json_build_object(
  'type',       'Feature',
  'id',         gid,
  'geometry',   ST_AsGeoJSON(geom)::json,
  'properties', json_build_object(
    'feat_type', feat_type,
    'feat_area', ST_Area(geom)::geography
  )
)
FROM input_table;

그렇지 않으면 지오메트리 멤버가 문자열이됩니다. 유효한 GeoJSON이 아닙니다.

답변

@dbaston의 답변 은 최근 @John Powell aka Barça에 의해 수정되었으며, 결국 내 잘못된 geojson을 생성합니다. 수정 된대로 기능에 대한 집계는 json 오브젝트 내에 중첩 된 각 기능을 리턴하며 이는 유효하지 않습니다.

나는 대답에 직접 언급하는 평판을 얻지 못했지만 최종 jsonb_agg는 “기능”하위 쿼리가 아니라 “기능”열에 있어야합니다. 열 이름 (또는 “features.feature”를 더 가깝게 찾는 경우)을 집계하면 집계 후 “features”배열에 모든 요소가 바로 배치됩니다.

따라서 다음은 몇 주 전까지 (@Jonh Powell의 하위 쿼리 명명에 대한 수정) 작동하므로 @dbaston의 답변과 매우 유사합니다.

SELECT jsonb_build_object(
  'type',     'FeatureCollection',
  'features', jsonb_agg(feature)
)
FROM (
  SELECT jsonb_build_object(
    'type',       'Feature',
    'id',         gid,
    'geometry',   ST_AsGeoJSON(geom)::jsonb,
    'properties', to_jsonb(inputs) - 'gid' - 'geom'
  ) AS feature
  FROM (
    SELECT * FROM input_table
  ) inputs
) features;

답변