7

I have a table in PostGIS 2.3 (PostgreSQL 9.5, and pgadmin III) where I store Multipolygons. I have a csv which is a straight export from another database, including the geometry column in hexewkb format (as I understand). I cannot figure out a way to simply insert this data into the table. Here is an example :

CREATE TABLE tout.battest(  
 id_battest serial NOT NULL,
 geom geometry(MultiPolygon,2154),
 CONSTRAINT prk_constraint_battest PRIMARY KEY (id_battest));

 INSERT INTO tout.battest(geom) VALUES ('010600000001000000010300000001000000050000007041F528CB332C413B509BE9710A594134371E05CC332C4111F40B87720A594147E56566CD332C4198DF5D7F720A594185EF3C8ACC332C41C03BEDE1710A59417041F528CB332C413B509BE9710A5941');

This insert into raises the error "Geometry SRID (0) does not match column SRID (2154)". So I tried this:

INSERT INTO tout.battest(geom) VALUES (st_setsrid('010600000001000000010300000001000000050000007041F528CB332C413B509BE9710A594134371E05CC332C4111F40B87720A594147E56566CD332C4198DF5D7F720A594185EF3C8ACC332C41C03BEDE1710A59417041F528CB332C413B509BE9710A5941',2154));

Which then raises an error like "the function st_setsrid(unknown, integer) is not unique"

I then tried to call various functions, but without any success, sometime even getting an error "function does not exist" when I actually find it in the function list of my public schema. Any idea on how to do what I want?

  • ST_SetSRID, not set_srid – Vince Mar 23 '17 at 9:17
  • Indeed in my comment of the command I made a mistake, edited ;) – GuiOm Clair Mar 23 '17 at 9:34
12

When you see an error like:

st_setsrid(unknown, integer)

it doesn't mean the ST_SetSRID does not exist, it means that there is a problem with the argument type unknown.

So, you can fix this by casting the HexEWKB directly to a geometry, using the cast operator, ::, and then call ST_SetSRID on that:

INSERT INTO  tout.battest(geom) VALUES 
(ST_SetSRID('010600000001000000010300000001000000050000007041F528CB332C413B509BE9710A594134371E05CC332C4111F40B87720A594147E56566CD332C4198DF5D7F720A594185EF3C8ACC332C41C03BEDE1710A59417041F528CB332C413B509BE9710A5941'
::geometry, 2154));

This works because the HexWBK is already a geometry representation, but in a string format, hence the need for a cast (see this Stack Overflow answer explaining the :: operator as a shortcut for cast). This also means that you do not need any of the ST_From...WKB type operators.

If you want to see the geometry directly, just use ST_AsText, again demonstrating that it is already a direct representation of a geometry.

SELECT ST_AsText('010600000001000000010300000001000000050000007041F528CB332C413B509BE9710A594134371E05CC332C4111F40B87720A594147E56566CD332C4198DF5D7F720A594185EF3C8ACC332C41C03BEDE1710A59417041F528CB332C413B509BE9710A5941');

which returns:

MULTIPOLYGON(((924133.579996152 6564295.65010458,924134.009996152 6564298.11010458,924134.699996152 6564297.99010458,924134.269996152 6564295.53010458,924133.579996152 6564295.65010458)))

EDIT: It is worth mentioning that as you mention CSV in the title, if you have lots of rows to insert, it might be quicker to run the COPY command on the CSV file to load the data into a geometry (mulipolygon) column with no SRID set and then use:

ALTER TABLE 'battest' ALTER COLUMN geom TYPE geometry(MULTIPOLYGON, 2154) USING ST_SetSRID(geom, 2154)

where you need the using clause to prevent unknown SRID errors in the alter table part.

  • Works like a charm (even though I have no clue yet on what is this cast operator thing). – GuiOm Clair Mar 23 '17 at 9:38
  • For the MultiPolygon error, in the sake of simplicity for the potential future users in need to answer this question, should we edit the question and answer to make it dissapear ? – GuiOm Clair Mar 23 '17 at 9:39
  • The :: is just shorthand for cast('....' as geometry), ie, this is the hex representation of the geometry as a string, treat it as a geometry. – John Powell Mar 23 '17 at 9:40
  • Yes, will edit out the mutlipolygon part. – John Powell Mar 23 '17 at 9:41
  • Ok, simple and efficient explanation. Thanks for all. – GuiOm Clair Mar 23 '17 at 9:41
2

Check the docs. Here's the relevant page: http://www.postgis.net/docs/ST_GeomFromWKB.html

You want something like:

insert into tout.battest(geom) values ST_GeomFromWKB('010600000001000000010300000001000000050000007041F528CB332C413B509BE9710A594134371E05CC332C4111F40B87720A594147E56566CD332C4198DF5D7F720A594185EF3C8ACC332C41C03BEDE1710A59417041F528CB332C413B509BE9710A5941', 2154);

You need to tell PostGIS what you're inserting, as something with '' quotes just looks like a string. Since you've exported Well Known Binary, you'd better insert it as WKB! So, we use the function ST_GeomFromWKB(bytea geom, integer srid) to do it.

EDIT: this answer is incorrect. Since this is a HexWKB, you can cast it to a geometry type with:

ST_SetSRID('010600000001000000010300000001000000050000007041F528CB332C413B509BE9710A594134371E05CC332C4111F40B87720A594147E56566CD332C4198DF5D7F720A594185EF3C8ACC332C41C03BEDE1710A59417041F528CB332C413B509BE9710A5941'::geometry, 2154)
  • 1
    This fails for me, as this is not WKB, but HEXWKB. – John Powell Mar 23 '17 at 9:30
  • 1
    Yes it fails for me too, raise a syntax error. – GuiOm Clair Mar 23 '17 at 9:36
  • Indeed, turns out (as the other answer shows) you can simply cast this to geom. I'll update my answer. – Alex Leith Mar 23 '17 at 9:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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