Saving javascript array into Postgres polygon field

Question

I'm having trouble trying to save a polygon that I have formatted like GeoJSON and the problem is that I've got the polygon as an array of arrays of coordinates but postgres polygon field is expecting an array of tuples but javascript doesn't have support for tuples and thus I can't figure out how to insert the data into Postgres.

Example of how postgres wants the data:

INSERT INTO table VALUES(default, '[(x,y), (x,y)]');

Example of the data i have:

"coordinates": [
          [
            [
              49.5703125,
              59.5343180010956
            ],
            [
              54.84375,
              54.77534585936447
            ],
            [
              63.28125,
              59.5343180010956
            ],
            [
              54.84375,
              61.77312286453146
            ],
            [
              49.5703125,
              60.930432202923335
            ],
            [
              49.5703125,
              59.5343180010956
            ]
          ]
        ]

Error that I've got when trying to save the array to postgres:

{
    "message": "invalid input syntax for type polygon: \"{{\"-64.1892249612655\",\"-31.4212119274207\"},{\"-64.1896863245919\",\"-31.4223122073094\"},{\"-64.1900957427429\",\"-31.423283040535\"},{\"-64.1901970936061\",\"-31.4235231632172\"},{\"-64.190677427225\",\"-31.4246610035708\"},{\"-64.1892249612655\",\"-31.4212119274207\"}}\"",
    "name": "QueryFailedError",
    "length": 353,
    "severity": "ERROR",
    "code": "22P02",
    "file": "float.c",
    "line": "542",
    "routine": "float8in_internal",
    "query": "INSERT INTO \"zones\"(\"title\", \"boundary_points\", \"created_at\", \"updated_at\", \"iconFileId\", \"backgroundFileId\") VALUES ($1, $2, DEFAULT, DEFAULT, $3, $4) RETURNING \"id\", \"created_at\", \"updated_at\"",
    "parameters": [
        "BAJO GENERAL PAZ",
        [
            [
                -64.1892249612655,
                -31.4212119274207
            ],
            [
                -64.1896863245919,
                -31.4223122073094
            ],
            [
                -64.1900957427429,
                -31.423283040535
            ],
            [
                -64.1901970936061,
                -31.4235231632172
            ],
            [
                -64.190677427225,
                -31.4246610035708
            ],
            [
                -64.1892249612655,
                -31.4212119274207
            ]
        ],
        null,
        null
    ]
}

every point of this array should become a record in your table? — Jim Jones
– Jim Jones, Commented Jan 20, 2020 at 13:21
No, It's only one record, there's a field that I've called 'boundary_points' which is of type Polygon, in order to define a polygon in postgres you need to pass an array of tuples (x,y) of coordinates. — Matias Mir
– Matias Mir, Commented Jan 20, 2020 at 13:34

Community · Accepted Answer · 2021-10-07 08:46:00Z

Your GeoJSON polygon is invalid - it is missing the type: "type":"Polygon". If you want to store a GeoJSON polygon into a GEOMETRY column you should use ST_GeomFromGeoJson() in your INSERT INTO:

CREATE TEMPORARY TABLE t (the_geom GEOMETRY);

INSERT INTO t (the_geom) VALUES (ST_GeomFromGeoJSON(
        '{"type":"Polygon",
          "coordinates": [
          [
            [49.5703125,59.5343180010956],
            [54.84375,54.77534585936447],
            [63.28125,59.5343180010956],
            [54.84375,61.77312286453146],
            [49.5703125,60.930432202923335],
            [49.5703125,59.5343180010956]
          ]
        ]}'::json));

SELECT ST_AsText(the_geom,2) FROM t;

                                     st_astext                                      
------------------------------------------------------------------------------------
 POLYGON((49.57 59.53,54.84 54.78,63.28 59.53,54.84 61.77,49.57 60.93,49.57 59.53))
(1 Zeile)

Collectives™ on Stack Overflow

Saving javascript array into Postgres polygon field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related