Polygon from json file to Postgres geometry format

0
votes

I have a Json file with zip code and polygon data. I want to convert and store polygon to a Postgres table in geometry data type. My postgres 9.5 with PostGIS extensions.

my file : myjson.json

data:

{
  "zipcode": "97358",
  "polygon": [
    {
      "lat": 44.730418,
      "lng": -122.544798
    },
    {
      "lat": 44.763411,
      "lng": -122.685015
    },
    {
      "lat": 44.831045,
      "lng": -122.616831
    },
    {
      "lat": 44.863487,
      "lng": -122.401282
    },
    {
      "lat": 44.80795,
      "lng": -122.40953
    },
    {
      "lat": 44.730418,
      "lng": -122.544798
    }
  ]
}

Postges mytable: zipcode varchar, polygon geometry

1
jsonpostgresql

1 Answers

0
votes

It's not pretty, but this should do the trick:

WITH polygon AS ( SELECT json_array_elements(t->'polygon') AS data FROM json($$ { "zipcode": "97358", "polygon": [ {"lat": 44.730418, "lng": -122.544798}, {"lat": 44.763411, "lng": -122.685015}, {"lat": 44.831045, "lng": -122.616831}, {"lat": 44.863487, "lng": -122.401282}, {"lat": 44.80795, "lng": -122.40953}, {"lat": 44.730418, "lng": -122.544798} ] }$$) as t) SELECT ST_MakePolygon(ST_GeometryFromText( 'LINESTRING(' || string_agg((p.data->'lng')::text || ' ' || (p.data->'lat')::text, ',') || ')', 4326)) FROM polygon AS p;

Note that I've hard-coded the SRID (projection) as 4326. If this polygon isn't in Oregon then you probably need to correct it.