How to save WKT to postgres column with geometry data type using typeorm?

Question

I have a table with a geom column which has the datatype of geometry. I need to save data into this table and the request being sent has the type wkt for this column. Currently there are some existing rows in the table with data in this format:

SRID=4326;POLYGON((-82.42186119969756 35.50904833429925,-82.41310709710558 35.5138338218795,-82.40448173131567 35.50461189763692,-82.41851404282427 35.500629375676766,-82.42186119969756 35.509013402684445,-82.42186119969756 35.50904833429925))

Is the "SRID=4326" portion of the data autogenerated?

Currently I have the follow set up:

dto:

 @IsString()
 geom: string; //geometry

entity:

@Column('geometry', {spatialFeatureType: 'Polygon', srid: 4326 })
geom: string;

And I and inserting the data like this, with dto.geom as a wtk string:

create(dto: CreatePlaceDto): Promise<number> {        
    return <any>this.repository
      .insert(dto)
      .then((data) => data.identifiers[0][Object.keys(data.identifiers[0])[0]])
      .catch((err) => {
        console.error(err);
      });
  }

When I send through a request, I get the QueryFailedError: unknown GeoJSON type error. When checking the raw sql query from the logs, it looks like the that the wkt is getting an extra set of quotes. And typeorm is trying to convert a geojson from to geom. I think i'd need typeorm to use ST_GeomFromText right? Since wkt isn't geojson.

INSERT INTO "place"("customerId", "geom", "industry", "createdAt", "updatedAt") 
VALUES ($1, ST_SetSRID(ST_GeomFromGeoJSON($2), 4326)::geometry, DEFAULT, DEFAULT) 
RETURNING "placeId", "createdAt", "updatedAt" 
-- PARAMETERS: [13,"\"POLYGON ((-82.42186119969756 35.50904833429925, -82.41310709710558 35.5138338218795, -82.40448173131567 35.50461189763692, -82.41851404282427 35.500629375676766, -82.42186119969756 35.509013402684445, -82.42186119969756 35.50904833429925))\"","industry1"]

Am I correct in typing geom as string in dto and entity? How do I get the format with "SRID=4326;POLYGON((-8 etc etc" ?

geozelot · Accepted Answer · 2023-06-04T15:19:45.317

TypeORM doesn't support WKT as transport format for PostgreSQL/PostGIS; the native driver enforces the GeoJSON format (note that ST_GeomFromGeoJSON supports parsing of individual geometry members of GeoJSON Features only).

Instead of relying on the DTO repository mapping, you can work with a QueryStringBuilder and directly influence parameter handling in the INSERT SQL statement:

await dataSource
  .createQueryBuilder()
  .insert()
  .into(<Entity>)
  .values({
    <columns>: <values>,
    geom: () =>
      `ST_GeomFromText('${<WKT_string>}', 4326)`,
      // or `'SRID=4326;${<WKT_string>}'`,
  })
  .execute()

Taken and altered from the docs; see e.g.

https://stackoverflow.com/a/72866248/6782776

for a more generic IRL example, and a note about natively supported format transformations between WKT and GeoJSON as an alternative.

If all you ever do is insertions you can probably get away with declaring the entities' column type as string - PostGIS defines implicit string casting for its GEOMETRY type and should handle the import correctly.

The SRID=4326 part is a PostGIS specific extension to the WKT type (EWKT), which has no notion of CRS - the respective I/O functions are ST_GeomFromEWKT and ST_AsEWKT, respectively. The auto-cast mentioned above can handle EWKT, too.

How to save WKT to postgres column with geometry data type using typeorm?

1 Answers1