-2

I have a table called areas_map with two columns a_id (bigint) and geom (geometry(MultiPolygon, 4326)).

Table "public.areas_map" Column | Type | Collation | Nullable | Default ----------+-----------------------------+-----------+----------+--------- gid | integer | | not null | a_id | bigint | | | gtype | smallint | | | geom | geometry(MultiPolygon,4326) | | | mod_op | character(1) | | | mod_date | timestamp without time zone | | | Indexes: "areas_map_pkey" PRIMARY KEY, btree (gid) "idx_areas_map_geom" gist (geom) "idx_areas_map_a_id" btree (a_id) Triggers: trig_mod_op_date BEFORE INSERT OR UPDATE ON areas_map FOR EACH ROW EXECUTE PROCEDURE add_mod_op_date()

I have another table called areas with columns a_id_1 (character varying) and wkt to insert in the above table, areas_map. I need to convert the wkt geometry to wkb geometry in order to put it in the 'areas_map'.

These are the columns in the 'areas' table in my database.

Table "public.areas" Column | Type | Collation | Nullable | Default --------------+-------------------+-----------+----------+---------------------------------------- ogc_fid | integer | | not null | nextval('areas_ogc_fid_seq'::regclass) a_id_1 | character varying | | | wkt | character varying | | | wkb_geometry | geometry | | | Indexes: "areas_pkey" PRIMARY KEY, btree (ogc_fid) "areas_wkb_geometry_geom_idx" gist (wkb_geometry)

And this is data in the table 'areas'.

ogc_fid | a_id_1 | wkt | wkb_geometry
---------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 | 3002618658 | MULTIPOLYGON(((-76.5473560619672 42.9413419720012,-76.5461994270791 42.9408894029933,-76.5428849220592 42.9424413310329,-76.540085618445 42.9435130207937,-76.5403432456807 42.9445933354756,-76.5417136457824 42.9446447959923,-76.5423695540135 42.9449534586902,-76.5424515359008 42.9454507456148,-76.5446238177076 42.9456536187894,-76.5449061034313 42.9473752994633,-76.5465216192731 42.9473555320693,-76.5464451670431 42.9465415450345,-76.547964643124 42.9465042162103,-76.5475477862325 42.9444728204718,-76.5473560619672 42.9413419720012))) | 0106000000010000000103000000010000000F000000A85AB8E1072353C0740CCCE47D784540841971EEF42253C02D815F106F784540176F66A0BE2253C0AF97E3EAA1784540B81045C3902253C05ED4DF08C57845400A34D6FB942253C092F1356FE8784540AB1BB66FAB2253C0D08FE41EEA7845406235CA2EB62253C083A8243CF4784540F896A586B72253C07B32B08704794540516BDB1DDB2253C0B305832D0B7945403568D9BDDF2253C0D61D0498437945406F55D035FA2253C00FF631F242794540796026F5F82253C03F1DFA45287945403E654BDA112353C01E13D70C2779454069B7DE050B2353C092F1417CE4784540A85AB8E1072353C0740CCCE47D784540 2 | 3003264375 | MULTIPOLYGON(((-118.20883035065 33.8744540214534,-118.208788426448 33.8811453764129,-118.214318573149 33.8811626720137,-118.213868789377 33.8764587738789,-118.214025554548 33.8755041061261,-118.214192790618 33.8749920466446,-118.213910539173 33.8744626447363,-118.20883035065 33.8744540214534))) | 010600000001000000010300000001000000080000000E9DF9795D8D5DC0C0FFFF1BEEEF404028D821CA5C8D5DC0865B275FC9F040406CA63F65B78D5DC09E603DF0C9F040409900B806B08D5DC0130D15CD2FF04040AF523D98B28D5DC0A4A9BF8410F0404092AFAD55B58D5DC0A14C48BDFFEF40402381D4B5B08D5DC0A85B5664EEEF40400E9DF9795D8D5DC0C0FFFF1BEEEF4040 (2 rows)

I tried the following sql command but it kept giving me error.

INSERT INTO areas_map(a_id, geom)
SELECT a_id_1, ST_AsBinary(ST_GeomFromText(wkt)) AS geom
FROM areas WHERE a_id_1=CAST(a_id_1 as bigint);

I really appreciate your help. Thank you.

Lily
  • 3
  • 3
  • 1
    Can you post the error it gives? – NuLo Sep 10 '21 at 17:10
  • 2
    Can you also post your table schema (`create table ...`) – NuLo Sep 10 '21 at 17:11
  • This is the error I am getting. ERROR: operator does not exist: character varying = bigint LINE 3: FROM areas WHERE qid_1=CAST(qid_1 as bigint); ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. – Lily Sep 10 '21 at 18:33
  • This is my data. – Lily Sep 10 '21 at 18:35
  • qid_1 wkt 3002618658 MULTIPOLYGON(((-76.5473560619672 42.9413419720012,-76.5461994270791 42.9408894029933,-76.5428849220592 42.9424413310329,-76.540085618445 42.9435130207937,-76.5403432456807 42.9445933354756,-76.5417136457824 42.9446447959923,-76.5423695540135 42.9449534586902,-76.5424515359008 42.9454507456148,-76.5446238177076 42.9456536187894,-76.5449061034313 42.9473752994633,-76.5465216192731 42.9473555320693,-76.5464451670431 42.9465415450345,-76.547964643124 42.9465042162103,-76.5475477862325 42.9444728204718,-76.5473560619672 42.9413419720012))) – Lily Sep 10 '21 at 18:36
  • I don't know what qid_1 is, it is not described in the question – NuLo Sep 10 '21 at 18:40
  • INSERT INTO areas_map(a_id, geom) SELECT a_id_1, ST_AsBinary(ST_GeomFromText(wkt)) AS geom FROM areas WHERE a_id_1=CAST(a_id_1 as bigint); – Lily Sep 10 '21 at 18:43
  • Table "public.areas_map" Column | Type | Collation | Nullable | Default ----------+-----------------------------+-----------+----------+--------- gid | integer | | not null | a_id | bigint | | | geom | geometry(MultiPolygon,4326) | | | This is the table I have in posstgres. – Lily Sep 10 '21 at 18:44

1 Answers1

0

From your error, I think that the query you wish is:

INSERT INTO areas_map(a_id, geom)
VALUES (3002618658, ST_SetSRID(ST_GeomFromText('MULTIPOLYGON(((-76.5473560619672 42.9413419720012,-76.5461994270791 42.9408894029933,-76.5428849220592 42.9424413310329,-76.540085618445 42.9435130207937,-76.5403432456807 42.9445933354756,-76.5417136457824 42.9446447959923,-76.5423695540135 42.9449534586902,-76.5424515359008 42.9454507456148,-76.5446238177076 42.9456536187894,-76.5449061034313 42.9473752994633,-76.5465216192731 42.9473555320693,-76.5464451670431 42.9465415450345,-76.547964643124 42.9465042162103,-76.5475477862325 42.9444728204718,-76.5473560619672 42.9413419720012)))'), 4326))

you just have to insert your data into the table

NuLo
  • 1,298
  • 1
  • 11
  • 16
  • Can you please explain what you did differently so other can understand why your solution works. – Ryan Wheale Sep 10 '21 at 18:43
  • It gave me the following error when I put the cast in the select part ERROR: Geometry SRID (0) does not match column SRID (4326) – Lily Sep 10 '21 at 19:34
  • try the edited answer, I added a conversion to the proper srid – NuLo Sep 10 '21 at 20:34
  • It still gave me the same error. Geometry SRID (0) does not match column SRID (4326) – Lily Sep 11 '21 at 17:29
  • @Lily, I cannot reproduce your error, can you give a reproducible example (`create table` for `areas` and `areas_map` and the `insert` clause to areas) so we can sort this out? – NuLo Sep 13 '21 at 11:01
  • Table "public.areas_map" Column | Type | Collation | Nullable | Default ----------+-----------------------------+-----------+----------+--------- gid | integer | | not null | vid | bigint | | | gtype | smallint | | | geom | geometry(MultiPolygon,4326) | | | mod_op | character(1) | | | mod_date | timestamp without time zone | | | – Lily Sep 13 '21 at 18:33
  • @lily can you please also post table `areas` ? – NuLo Sep 13 '21 at 18:36
  • I have posted the table areas in the answer box. – Lily Sep 13 '21 at 18:47
  • 3002618658 is the first a_id_1 and 3003264375 is the second a_id_1. – Lily Sep 13 '21 at 18:49
  • @lily what you posted was content and not a table. So I am assuming this is data that comes from somewhere else that is not the database. – NuLo Sep 13 '21 at 18:56
  • Table "public.areas" Column | Type | Collation | Nullable | Default --------------+-------------------+-----------+----------+---------------------------------------- ogc_fid | integer | | not null | nextval('areas_ogc_fid_seq'::regclass) a_id | character varying | | | wkt | character varying | | | wkb_geometry | geometry | | | Indexes: "areas_pkey" PRIMARY KEY, btree (ogc_fid) "areas_wkb_geometry_geom_idx" gist (wkb_geometry) – Lily Sep 13 '21 at 22:49
  • I have included my table (areas) from my database in the question box. – Lily Sep 13 '21 at 23:11
  • When I tried to insert the data in the table it gave me this error. ERROR: null value in column "gid" violates not-null constraint DETAIL: Failing row contains (null, 3002618658, null, 0106000020E6100000010000000103000000010000000F000000A85AB8E10723..., I, 2021-09-13 23:21:45.47066). – Lily Sep 13 '21 at 23:24
  • @NuLo When I tried to insert my data in the table it gave me this error. ERROR: null value in column "gid" violates not-null constraint DETAIL: Failing row contains (null, 3002618658, null, 0106000020E6100000010000000103000000010000000F000000A85AB8E10723..., I, 2021-09-13 23:21:45.47066). – Lily Sep 14 '21 at 15:36
  • You have to decide the value you want on the column gid, you haven't explained what it is – NuLo Sep 14 '21 at 15:57
  • In the areas_map table in the database, the gid is integer and has index as "areas_map_pkey" PRIMARY KEY, btree (gid) – Lily Sep 14 '21 at 16:01
  • What should the value of gid be for the row that is being inserted in the example? – NuLo Sep 14 '21 at 16:17
  • I just put 1 for gid and the query worked but I have more data to insert and the gid column is supposed to be an automatic serial number. – Lily Sep 14 '21 at 16:32
  • Then you have to change that column in the db's table to be a serial – NuLo Sep 14 '21 at 16:38
  • @NuLo I have multiple data to insert in the table, is there an easier way to do so instead of inserting it one by one or a few at a time? – Lily Sep 16 '21 at 19:32
  • There are many ways but that depends on how you have the original data you want to import – NuLo Sep 16 '21 at 20:14
  • I have 375 same kind of data, geom in wkt and their respective id numbers, in a table called 'areas' and I created a table called 'areas_map' CREATE TABLE areas_map(a_id bigint,geom geometry(MultiPolygon,4326)); I want to put the 375 records in wkb format in 'areas_map'. – Lily Sep 16 '21 at 23:12