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.
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