No need for ST_MakePoint
, just chain your coordinates in ST_GeomFromText
. You can just write something like ST_GeomFromText('LINESTRING(27.69858 85.28154, 27.69804 85.28155, 27.69337 85.28174, 27.69356 85.28275, 27.69378 85.28370, 27.69409 85.28449)', 900913)
. x and y parts of coordinates are separated by space, coordinate-pairs by commas. See also the ST_GeomFromText documentation for more information.
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.
Best Answer
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 usingST_AsText
to check the result.A full working demo to insert your data can be
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
?