amazon web services - AWS Redshift - Loading GeoJSON Into Geometry Field

Question

Currently it is not possible to load GeoJSON directly into a redshift geometry column using the copy command, but a workaround has been suggested at:-

Copying GeoJSON data from S3 to Redshift

This involves ingesting as WKT then converting to geometry using a spatial function, however i'm not entirely sure how to get from geojson to wkt - i am sure there must be some converter available.

But this is where my limited understanding of spatial data comes in - let's say i want to load weather geojson objects like the one shown below into a table in redshift.

If I understand it correctly, each Feature in the FeatureCollection would be a row in the table with just the contents of json geometry field loaded into a field of type GEOMETRY i.e. this field does not take any of the properties or attributes of the feature. The properties would then be loaded into completely separate fields using conventional datatypes. Then if i wanted to export that feature as geojson, i would have to stitch the geometry and properties back together again.

Is that correct?

Or does the GEOMETRY type actually have the facility to store the properties as well as the geometry field contents?

{
  "type": "FeatureCollection",
  "features": [
      {
          "type": "Feature",
          "id": "abcd1234",
          "geometry": {
              "type": "MultiPolygon",
              "coordinates": [
                  [
                      [
                          [
                              7.51,
                              48.04
                          ],
                          [
                              8.12,
                              48.05
                          ],
                          [
                              8.19,
                              47.95
                          ]
                      ]
                  ]
              ]
          },
          "geometry_name": "contour",
          "properties": {
              "identifier": "abcd1234",
              "analysisTime": "2019-04-06T14:15:00Z",
              "convectionCellType": "CELL_BASE",
              "speed": 3,
              "area": "MSG",
              "phasetype": null,
              "top": 10363,
              "intensityValue": null,
              "ice": true,
              "created_at": "2020-07-21T12:01:25.651Z"
          }
      }
    ],
    "totalFeatures": 1,
    "numberMatched": 1,
    "numberReturned": 1,
    "timeStamp": "2021-02-03T16:39:48.963Z",
    "crs": {
        "type": "name",
        "properties": {
            "name": "urn:ogc:def:crs:EPSG::4326"
        }
    }
  }

question from:https://stackoverflow.com/questions/66045638/aws-redshift-loading-geojson-into-geometry-field

Answer 1

I found this Gist which provides the following Python snippet to do the conversion:

from shapely.geometry import shape

o = {
   "coordinates": [[[23.314208, 37.768469], [24.039306, 37.768469], [24.039306, 38.214372], [23.314208, 38.214372], [23.314208, 37.768469]]], 
   "type": "Polygon"
}
geom = shape(o)

# Now it's very easy to get a WKT/WKB representation
geom.wkt
geom.wkb