Polygon insertion issue (due to SRID) on Postgres

Question

I'm having trouble inserting a polygon into my table structure. I'm relatively new to PostGIS, so I may be making a pretty amateur mistake on this.

My table is setup as "Regions" and I'm adding a column for my geometry:

"SELECT AddGeometryColumn(" +
"'public', 'Regions', 'geom', 4326, 'POLYGON', 2" +
");"

From what I've read this sets the column geometry to accept WGS-83 as my projection standard. I'm using GeoJSON to insert my polygon because it's the easiest option for me. Here's an example of my update statement:

UPDATE "Regions"
    SET geom = ST_GeomFromGeoJSON(
        '{"type":"Polygon","coordinates":[[[-114.017347,51.048005],[-114.014433,51.047927],[-114.005899,51.045381],[-114.001598,51.04509],[-114.001631,51.055109],[-114.01618,51.055062],[-114.016949,51.056508],[-114.016181,51.056511],[-114.01659,51.057251],[-114.017318,51.057237],[-114.018672,51.059928],[-114.020528,51.0593],[-114.023615,51.059311],[-114.021148,51.055829],[-114.018807,51.052583],[-114.017347,51.048005]]]}'
    )
    WHERE id = 'ab8326c0-beb3-11e4-89eb-b3372c283c42'

The response I'm getting from my query is:

{ [SequelizeDatabaseError: Geometry SRID (0) does not match column SRID (4326)]
  name: 'SequelizeDatabaseError',
  message: 'Geometry SRID (0) does not match column SRID (4326)',
  parent: 
   { [error: Geometry SRID (0) does not match column SRID (4326)]
     name: 'error',
     length: 121,
     severity: 'ERROR',
     code: '22023',
     detail: undefined,
     hint: undefined,
     position: undefined,
     internalPosition: undefined,
     internalQuery: undefined,
     where: undefined,
     schema: undefined,
     table: undefined,
     column: undefined,
     dataType: undefined,
     constraint: undefined,
     file: 'gserialized_typmod.c',
     line: '128',
     routine: 'postgis_valid_typmod',

I've had the coordinates verified as WGS84, but now I'm thinking that the issue is unrelated to the SRID type I'm using.

Thanks for your help.

Answer 1

Two things:

• You can add a geometry column directly in PostGIS 2.0+ just using the ALTER TABLE command. ALTER TABLE foo ADD COLUMN geom Geometry(Polygon,4326)
• As the error says, the geometry you're creating from JSON lacks the SRID of the column. So you need to ensure it is set. The easiest way is with ST_SetSrid, so (abbreviated) UPDATE foo SET geom = ST_SetSRID(ST_GeomFromGeoJSON(...),4326)