TypeORM inserting Geometry type with ST_GeomFromGeoJSON

Question

I have a postgres table who is using Geometry column type.

This is my table:

CREATE TABLE areas (
    id SERIAL PRIMARY KEY,
    name VARCHAR(64),
    polygon GEOMETRY
);

And, normally I was inserting data like:

INSERT INTO areas (name, polygon) VALUES (
    'A',
    ST_GeometryFromText('POLYGON ((-123.11386585235593 49.284015800344065, 
                        -123.11882257461549 49.28074038150665, 
                        -123.11337232589727 49.27721276406796, 
                        -123.1078577041626 49.281104327676616, 
                        -123.10996055603025 49.28152426222755, 
                        -123.11386585235593 49.284015800344065))'));

And currently is working properly if I run the statement from postgres.

But in my NestJS/TypeORM entity I have defined:

@Column('geometry', {nullable: true, name: 'polygon ' }
@ApiProperty()
polygon : string;

And once I assign the value with:

areas .polygon = 'POLYGON ((-123.11386585235593 49.284015800344065, -123.11882257461549 49.28074038150665, -123.11337232589727 49.27721276406796, -123.1078577041626 49.281104327676616, -123.10996055603025 49.28152426222755, -123.11386585235593 49.284015800344065))';

I get this error:

error: error: unknown GeoJSON type at Parser.parseErrorMessage (C:\Users\myuserpath\myproject\node_modules\pg-protocol\dist\parser.js:278:15)

But I notice the raw query is:

INSERT INTO "areas VALUES ('A', ST_GeomFromGeoJSON('POLYGON ((-123.11386585235593 49.284015800344065, 
                            -123.11882257461549 49.28074038150665, 
                            -123.11337232589727 49.27721276406796, 
                            -123.1078577041626 49.281104327676616, 
                            -123.10996055603025 49.28152426222755, 
                            -123.11386585235593 49.284015800344065)))'::geometry)

I'm not sure where the column has defined ST_GeomFromGeoJSON How can insert that polygon using TypeORM?

MrMins MrMins · Accepted Answer · 2020-09-10T13:46:36

After long research, I found a way to do it. There is another way to perform an insertion:

const myPolygon = [[-123.11882257461549 49.28074038150665],[...],[...],[...]]

    const areas = await getConnection()
          .createQueryBuilder()
          .insert()
          .into(Areas) //HERE YOUR TABLE NAME
          .values({
            name: 'MrMins',
            columnX: 'lorem ipsum', //LIST OF YOUR COLUMNS
            active: true, //LIST OF YOUR COLUMNS
            polygon: () =>
              `ST_GeomFromGeoJSON( '{ "type": "Polygon", "coordinates": [${myPolygon}] }' )`,
          })
          .execute();

The entity is declared as in the question. But this way to perform an Insert you can use any function you want: ST_GeometryFromText or even if you are using POINT.

TypeORM inserting Geometry type with ST_GeomFromGeoJSON

2 Answers