copy PSQL command not working with POINT(…) geometry type
0
I have been trying to find the solution for quite a while...
The copy command in Postgres is not working with the datatype geography(Point,4326).
The error it gives is:
ERROR: parse error - invalid geometry
HINT: "ST" <-- parse error at position 2 within geometry
CONTEXT: COPY data2, line 1, column loc: "ST_GeomFromText('POINT(62.0271954112486 87.9028962135794)')"
Here is the command I am using:
copy data2(loc,d1,d2,d3,d4,d5,d6,d7,d8,d9,d10) from 'fake_data.csv' delimiter ',' csv;
I have inserted into the table using the exact same format as the csv file and it has been successful. It seems it is just something with the copy command that doesn't like the format.
Here is an example row from my csv file:
ST_GeomFromEWKT('SRID=4326;POINT(28.872109890126964 160.10529558104636)'),24.237968,129.512386,227.032799,27.644993,60.959401,25.178026,201.229746,34.178728,250.975993,3.635878
postgresql postgis
asked Nov 20 '18 at 1:36
Sam HansonSam Hanson
12
add a comment |
0
I have been trying to find the solution for quite a while...
The copy command in Postgres is not working with the datatype geography(Point,4326).
The error it gives is:
ERROR: parse error - invalid geometry
HINT: "ST" <-- parse error at position 2 within geometry
CONTEXT: COPY data2, line 1, column loc: "ST_GeomFromText('POINT(62.0271954112486 87.9028962135794)')"
Here is the command I am using:
copy data2(loc,d1,d2,d3,d4,d5,d6,d7,d8,d9,d10) from 'fake_data.csv' delimiter ',' csv;
I have inserted into the table using the exact same format as the csv file and it has been successful. It seems it is just something with the copy command that doesn't like the format.
Here is an example row from my csv file:
ST_GeomFromEWKT('SRID=4326;POINT(28.872109890126964 160.10529558104636)'),24.237968,129.512386,227.032799,27.644993,60.959401,25.178026,201.229746,34.178728,250.975993,3.635878
postgresql postgis
asked Nov 20 '18 at 1:36
Sam HansonSam Hanson
12
add a comment |
0
0
0
I have been trying to find the solution for quite a while...
The copy command in Postgres is not working with the datatype geography(Point,4326).
The error it gives is:
ERROR: parse error - invalid geometry
HINT: "ST" <-- parse error at position 2 within geometry
CONTEXT: COPY data2, line 1, column loc: "ST_GeomFromText('POINT(62.0271954112486 87.9028962135794)')"
Here is the command I am using:
copy data2(loc,d1,d2,d3,d4,d5,d6,d7,d8,d9,d10) from 'fake_data.csv' delimiter ',' csv;
I have inserted into the table using the exact same format as the csv file and it has been successful. It seems it is just something with the copy command that doesn't like the format.
Here is an example row from my csv file:
ST_GeomFromEWKT('SRID=4326;POINT(28.872109890126964 160.10529558104636)'),24.237968,129.512386,227.032799,27.644993,60.959401,25.178026,201.229746,34.178728,250.975993,3.635878
postgresql postgis
asked Nov 20 '18 at 1:36
Sam HansonSam Hanson
12
I have been trying to find the solution for quite a while...
The copy command in Postgres is not working with the datatype geography(Point,4326).
The error it gives is:
ERROR: parse error - invalid geometry
HINT: "ST" <-- parse error at position 2 within geometry
CONTEXT: COPY data2, line 1, column loc: "ST_GeomFromText('POINT(62.0271954112486 87.9028962135794)')"
Here is the command I am using:
copy data2(loc,d1,d2,d3,d4,d5,d6,d7,d8,d9,d10) from 'fake_data.csv' delimiter ',' csv;
I have inserted into the table using the exact same format as the csv file and it has been successful. It seems it is just something with the copy command that doesn't like the format.
Here is an example row from my csv file:
ST_GeomFromEWKT('SRID=4326;POINT(28.872109890126964 160.10529558104636)'),24.237968,129.512386,227.032799,27.644993,60.959401,25.178026,201.229746,34.178728,250.975993,3.635878
postgresql postgis
postgresql postgis
asked Nov 20 '18 at 1:36
Sam HansonSam Hanson
12
asked Nov 20 '18 at 1:36
Sam HansonSam Hanson
12
edited Nov 26 '18 at 18:11
Sam Hanson
asked Nov 20 '18 at 1:36
Sam HansonSam Hanson
12
asked Nov 20 '18 at 1:36
Sam HansonSam Hanson
12
asked Nov 20 '18 at 1:36
Sam HansonSam Hanson
12
12
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
0
You cannot use COPY to process an expression like this. Rather, the file has to contain the extended well-known binary (EWKB) format, which is what you get when you run
SELECT ST_GeomFromEWKT('SRID=4326;POINT(81.5538863138809 42.72341176405514)');
In your case, the CVS file will have to look like this:
0101000020E61000000C9D009842DF3C403DA0D6945E036440,24.237968,129.512386,227.032799,27.644993,60.959401,25.178026,201.229746,34.178728,250.975993,3.635878
answered Nov 20 '18 at 3:57
Laurenz AlbeLaurenz Albe
47.7k102748
Hi @laurenz , thanks for the quick response. I changed it to EWKB format and ran the same command but got the same error message unfortunately. Did I misinterpret your answer?
– Sam Hanson
Nov 26 '18 at 17:27
Please edit the question and show your CSV file and yourcopycommand.
– Laurenz Albe
Nov 26 '18 at 17:56
okay I updated the question. I was not sure if you meant thatSELECTshould be in the file so I tried including it and not including it beforeST_GeomFromEWKT.... Both failed. The error when the csv file began withSELECTwas the exact same as in the question except that theHINTwasSEinstead ofST.
– Sam Hanson
Nov 26 '18 at 18:16
Ok, I have added how your CSV file should look.
– Laurenz Albe
Nov 26 '18 at 18:25
thanks again for all your help. So my confusion is how to get the CSV file to look like your example above. This is my understanding of how the process should go, please correct me if I am wrong. Create a CSV file that looks like my example row above. Use thecopycommand to insert all of the rows into a table which will automatically create a geometry (in my example calledloc).
– Sam Hanson
Nov 28 '18 at 17:02
|
show 1 more comment
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53385019%2fcopy-psql-command-not-working-with-point-geometry-type%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
You cannot use COPY to process an expression like this. Rather, the file has to contain the extended well-known binary (EWKB) format, which is what you get when you run
SELECT ST_GeomFromEWKT('SRID=4326;POINT(81.5538863138809 42.72341176405514)');
In your case, the CVS file will have to look like this:
0101000020E61000000C9D009842DF3C403DA0D6945E036440,24.237968,129.512386,227.032799,27.644993,60.959401,25.178026,201.229746,34.178728,250.975993,3.635878
answered Nov 20 '18 at 3:57
Laurenz AlbeLaurenz Albe
47.7k102748
Hi @laurenz , thanks for the quick response. I changed it to EWKB format and ran the same command but got the same error message unfortunately. Did I misinterpret your answer?
– Sam Hanson
Nov 26 '18 at 17:27
Please edit the question and show your CSV file and yourcopycommand.
– Laurenz Albe
Nov 26 '18 at 17:56
okay I updated the question. I was not sure if you meant thatSELECTshould be in the file so I tried including it and not including it beforeST_GeomFromEWKT.... Both failed. The error when the csv file began withSELECTwas the exact same as in the question except that theHINTwasSEinstead ofST.
– Sam Hanson
Nov 26 '18 at 18:16
Ok, I have added how your CSV file should look.
– Laurenz Albe
Nov 26 '18 at 18:25
thanks again for all your help. So my confusion is how to get the CSV file to look like your example above. This is my understanding of how the process should go, please correct me if I am wrong. Create a CSV file that looks like my example row above. Use thecopycommand to insert all of the rows into a table which will automatically create a geometry (in my example calledloc).
– Sam Hanson
Nov 28 '18 at 17:02
|
show 1 more comment
0
You cannot use COPY to process an expression like this. Rather, the file has to contain the extended well-known binary (EWKB) format, which is what you get when you run
SELECT ST_GeomFromEWKT('SRID=4326;POINT(81.5538863138809 42.72341176405514)');
In your case, the CVS file will have to look like this:
0101000020E61000000C9D009842DF3C403DA0D6945E036440,24.237968,129.512386,227.032799,27.644993,60.959401,25.178026,201.229746,34.178728,250.975993,3.635878
answered Nov 20 '18 at 3:57
Laurenz AlbeLaurenz Albe
47.7k102748
Hi @laurenz , thanks for the quick response. I changed it to EWKB format and ran the same command but got the same error message unfortunately. Did I misinterpret your answer?
– Sam Hanson
Nov 26 '18 at 17:27
Please edit the question and show your CSV file and yourcopycommand.
– Laurenz Albe
Nov 26 '18 at 17:56
okay I updated the question. I was not sure if you meant thatSELECTshould be in the file so I tried including it and not including it beforeST_GeomFromEWKT.... Both failed. The error when the csv file began withSELECTwas the exact same as in the question except that theHINTwasSEinstead ofST.
– Sam Hanson
Nov 26 '18 at 18:16
Ok, I have added how your CSV file should look.
– Laurenz Albe
Nov 26 '18 at 18:25
thanks again for all your help. So my confusion is how to get the CSV file to look like your example above. This is my understanding of how the process should go, please correct me if I am wrong. Create a CSV file that looks like my example row above. Use thecopycommand to insert all of the rows into a table which will automatically create a geometry (in my example calledloc).
– Sam Hanson
Nov 28 '18 at 17:02
|
show 1 more comment
0
0
0
You cannot use COPY to process an expression like this. Rather, the file has to contain the extended well-known binary (EWKB) format, which is what you get when you run
SELECT ST_GeomFromEWKT('SRID=4326;POINT(81.5538863138809 42.72341176405514)');
In your case, the CVS file will have to look like this:
0101000020E61000000C9D009842DF3C403DA0D6945E036440,24.237968,129.512386,227.032799,27.644993,60.959401,25.178026,201.229746,34.178728,250.975993,3.635878
answered Nov 20 '18 at 3:57
Laurenz AlbeLaurenz Albe
47.7k102748
You cannot use COPY to process an expression like this. Rather, the file has to contain the extended well-known binary (EWKB) format, which is what you get when you run
SELECT ST_GeomFromEWKT('SRID=4326;POINT(81.5538863138809 42.72341176405514)');
In your case, the CVS file will have to look like this:
0101000020E61000000C9D009842DF3C403DA0D6945E036440,24.237968,129.512386,227.032799,27.644993,60.959401,25.178026,201.229746,34.178728,250.975993,3.635878
answered Nov 20 '18 at 3:57
Laurenz AlbeLaurenz Albe
47.7k102748
edited Nov 26 '18 at 18:25
answered Nov 20 '18 at 3:57
Laurenz AlbeLaurenz Albe
47.7k102748
answered Nov 20 '18 at 3:57
Laurenz AlbeLaurenz Albe
47.7k102748
answered Nov 20 '18 at 3:57
Laurenz AlbeLaurenz Albe
47.7k102748
47.7k102748
Hi @laurenz , thanks for the quick response. I changed it to EWKB format and ran the same command but got the same error message unfortunately. Did I misinterpret your answer?
– Sam Hanson
Nov 26 '18 at 17:27
Please edit the question and show your CSV file and yourcopycommand.
– Laurenz Albe
Nov 26 '18 at 17:56
okay I updated the question. I was not sure if you meant thatSELECTshould be in the file so I tried including it and not including it beforeST_GeomFromEWKT.... Both failed. The error when the csv file began withSELECTwas the exact same as in the question except that theHINTwasSEinstead ofST.
– Sam Hanson
Nov 26 '18 at 18:16
Ok, I have added how your CSV file should look.
– Laurenz Albe
Nov 26 '18 at 18:25
thanks again for all your help. So my confusion is how to get the CSV file to look like your example above. This is my understanding of how the process should go, please correct me if I am wrong. Create a CSV file that looks like my example row above. Use thecopycommand to insert all of the rows into a table which will automatically create a geometry (in my example calledloc).
– Sam Hanson
Nov 28 '18 at 17:02
|
show 1 more comment
Hi @laurenz , thanks for the quick response. I changed it to EWKB format and ran the same command but got the same error message unfortunately. Did I misinterpret your answer?
– Sam Hanson
Nov 26 '18 at 17:27
Please edit the question and show your CSV file and yourcopycommand.
– Laurenz Albe
Nov 26 '18 at 17:56
okay I updated the question. I was not sure if you meant thatSELECTshould be in the file so I tried including it and not including it beforeST_GeomFromEWKT.... Both failed. The error when the csv file began withSELECTwas the exact same as in the question except that theHINTwasSEinstead ofST.
– Sam Hanson
Nov 26 '18 at 18:16
Ok, I have added how your CSV file should look.
– Laurenz Albe
Nov 26 '18 at 18:25
thanks again for all your help. So my confusion is how to get the CSV file to look like your example above. This is my understanding of how the process should go, please correct me if I am wrong. Create a CSV file that looks like my example row above. Use thecopycommand to insert all of the rows into a table which will automatically create a geometry (in my example calledloc).
– Sam Hanson
Nov 28 '18 at 17:02
Hi @laurenz , thanks for the quick response. I changed it to EWKB format and ran the same command but got the same error message unfortunately. Did I misinterpret your answer?
– Sam Hanson
Nov 26 '18 at 17:27
Hi @laurenz , thanks for the quick response. I changed it to EWKB format and ran the same command but got the same error message unfortunately. Did I misinterpret your answer?
– Sam Hanson
Nov 26 '18 at 17:27
Please edit the question and show your CSV file and your
copy command.– Laurenz Albe
Nov 26 '18 at 17:56
Please edit the question and show your CSV file and your
copy command.– Laurenz Albe
Nov 26 '18 at 17:56
okay I updated the question. I was not sure if you meant that
SELECT should be in the file so I tried including it and not including it before ST_GeomFromEWKT.... Both failed. The error when the csv file began with SELECT was the exact same as in the question except that the HINT was SE instead of ST.– Sam Hanson
Nov 26 '18 at 18:16
okay I updated the question. I was not sure if you meant that
SELECT should be in the file so I tried including it and not including it before ST_GeomFromEWKT.... Both failed. The error when the csv file began with SELECT was the exact same as in the question except that the HINT was SE instead of ST.– Sam Hanson
Nov 26 '18 at 18:16
Ok, I have added how your CSV file should look.
– Laurenz Albe
Nov 26 '18 at 18:25
Ok, I have added how your CSV file should look.
– Laurenz Albe
Nov 26 '18 at 18:25
thanks again for all your help. So my confusion is how to get the CSV file to look like your example above. This is my understanding of how the process should go, please correct me if I am wrong. Create a CSV file that looks like my example row above. Use the
copy command to insert all of the rows into a table which will automatically create a geometry (in my example called loc).– Sam Hanson
Nov 28 '18 at 17:02
thanks again for all your help. So my confusion is how to get the CSV file to look like your example above. This is my understanding of how the process should go, please correct me if I am wrong. Create a CSV file that looks like my example row above. Use the
copy command to insert all of the rows into a table which will automatically create a geometry (in my example called loc).– Sam Hanson
Nov 28 '18 at 17:02
|
show 1 more comment
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53385019%2fcopy-psql-command-not-working-with-point-geometry-type%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
