1

I'm running an Alteryx workflow where I am pulling data from one database to another PostgreSQL database containg PostGIS. However, when I run the workflow and write the data to the database, I am getting an error saying:

Output Data (18) DataWrap2ODBC::SendBatch: ERROR: WKB structure does not match expected size!;¶Error while executing the query Insert into "table_name"("column_1","column_2","column_3","geom") Values (?,?,?,ST_GeomFromWKB(?,4326))

or in other words, the error is saying that WKB structure does not match expected size. There is a geometry or Spatial Object column in the database that I am trying to send to PostgreSQL..

I thought it might be the Spatial Object Field Size (it's too small) but when I increased it, it didn't do anything. Does anybody know why I am getting this error and how to fix it?

EDIT: This is how the geom table looks like that I am trying to insert into my PostgreSQL PostGIS table:

https://jsfiddle.net/ovr1798k/1/

6
  • 1
    Can you show use your PostGIS destination table structure using \d table_name in psql? For Insert into "table_name"("column_1","column_2","column_3","geom") Values (?,?,?,ST_GeomFromWKB(?,4326), can you give use one sample for the value that should replace the question mark from ST_GeomFromWKB(?,4326)? Edit your question the infos. Thanks
    – ThomasG77
    Jul 29, 2021 at 0:10
  • @ThomasG77 I edited my question, I didn't add everything because it's like 30000+ characters but I think you can get the gist of it with just that Jul 29, 2021 at 17:23
  • Share the full geometry using pastebin.com or another code sharing site and give us the link, please. We can't troubleshoot without enough infos. It's not I do not want to help but just can't guess with your provided info at the moment.
    – ThomasG77
    Jul 29, 2021 at 20:26
  • @ThomasG77 it looks like this: pastebin.com/EGwKtQFT Jul 29, 2021 at 20:33
  • Not public: you copied the content but if not the owner not available to others. Change permissions to make it public. The issue with the pastebin can be reproduce if not connected.
    – ThomasG77
    Jul 29, 2021 at 22:42

2 Answers 2

1

We are able to use the provided string. The original_string provided is at https://jsfiddle.net/ovr1798k/1/

We are able to process it using the original string wrapped between SELECT ST_AsText(ST_GeomFromWKB(E'\\x and )) We made it human readable using ST_AsText to check the result.

A full working demo to insert your data can be

CREATE TABLE IF NOT EXISTS table_name(
  column_1 text,
  column_2 text,
  column_3 text,
  geom geometry(Polygon,4326)
);

-- We supposed your srid is 4326 but change it if necessary
-- Change <yourlongwkbstring> with your own wkb string
INSERT INTO table_name(column_1, column_2, column_3, geom)
VALUES ('content1', 'content2', 'content3',
ST_SetSrid(ST_GeomFromEWKB(decode('<yourlongwkbstring>', 'hex')), 4326);

You can see a full working code at https://gist.github.com/ThomasG77/dae8e8a76db4fc720fb18d261da4076f (because too long to fit in the answer here). You will need to adapt our solution as we did not use placeholders ?

5
  • Okay, I tried it but it looks like I get another error now saying: Output Data (18) DataWrap2ODBC::SendBatch: ERROR: Invalid endian flag value encountered.; ¶Error while executing the query Insert into "table_name"("column_1","column_2","column_3","geom") Values (?,?,?,?,ST_GeomFromWKB(?,4326)) Jul 30, 2021 at 21:21
  • "You tried it" What did you try? To use the INSERT INTO table_name(... I provided using psql command line? It's to be sure before the ODBC part issue. It's because I also encountered the same ERROR: Invalid endian flag value encountered.; and has been able to solve it with my exact syntax. So, when you tell me you got the same error I already solved, I smell a wrong use of my recipe (but could be wrong as the error could come from ODBC driver or system endianness)
    – ThomasG77
    Jul 30, 2021 at 22:01
  • Try ST_AsText(ST_SetSrid(ST_GeomFromEWKB(decode(v2.spectrum_band_geometries.geom, 'hex')), 4326)). I've considered that v2.spectrum_band_geometries.geom contain the same wkb as the one you provided in your sample.
    – ThomasG77
    Jul 31, 2021 at 0:07
  • Now I'm getting another error when doing ST_AsText(ST_SetSrid(ST_GeomFromEWKB(decode(v2.spectrum_band_geometries.geom, 'hex')), 4326)), the error is: Error running PostSQL on "table_name": ERROR: data type text has no default operator class for access method "gist";¶Error while executing the query Aug 2, 2021 at 18:00
  • St_AsText is for debugging purpose when using psql. Just use ST_SetSrid(ST_GeomFromEWKB(decode(v2.spectrum_band_geometries.geom, 'hex')), 4326)
    – ThomasG77
    Aug 2, 2021 at 19:50
0

Finally got it to work thanks to someone's help. What I did when first calling the data is SELECT column_1, column_2, column_3, ST_AsEWKB(geom) FROM table_name and then for the Post-Create SQL statement in the workflow, I did the following SQL statement:

ALTER TABLE table_name ALTER COLUMN geom TYPE geometry(geometry,4326) USING (ST_GeomFromEWKB(geom));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.