CODEWITHSUNDEEP
mysqlinsert-into
AMahajan
AMahajan

Reputation: 189

error Cannot get geometry object from data you send to the GEOMETRY field while inserting result of query into column in mysql

I am trying to find out the center of the polygons of a Shapefile.. and inserting the result into a new column named center.. 

SELECT astext(centroid(`SHAPE`)) FROM `utdpolygon`

gives the centroid for all the polygons but when I try to insert the result into new column using 

INSERT INTO utdpolygon (`center`)
SELECT astext(centroid(`SHAPE`)) FROM `utdpolygon

` I got this error

Cannot get geometry object from data you send to the GEOMETRY field.

I searched for questions which pointed to similar error but they did not help. I gave point as type for center and later changed it to geometry because type for shape is geometry but that too doesn't help.

Upvotes: 0

Views: 5830

Answers (2)

Prathap Badavath
Prathap Badavath

Reputation: 1671

use "org.hibernate.spatial.dialect.mysql.MySQLSpatialDialect" 
insted of "org.hibernate.dialect.MySQLDialect".

Upvotes: 1

SeanN
SeanN

Reputation: 663

You have two problems in your code, first, you're trying to use an INSERT to change the value of the center column - you're not inserting new rows you're updating existing ones, so you need to change it to an UPDATE.

Then the values for the center column need to be geometry values not text values, so you need to use ST_GeometryFromText (or one of its synonyms) to get a geometry, although in your case the value you're using from the centroid function actually is a geometry, so you could just use it directly, otherwise you're converting it to text and then back to a geometry.

So - either of these UPDATEs will do what you want (the second one is better, I just kept the first one to show the point about creating a geometry when you have a text POINT):

UPDATE utdpolygon
SET `center` = ST_GeometryFromText(astext(centroid(`SHAPE`)));

or

UPDATE utdpolygon
SET `center` = centroid(`SHAPE`);

I'm assuming that you have already added the center column to the table because you replied above saying that its data type is POINT - if not you'll need to add it first with an ALTER TABLE.

Upvotes: 0

Related Questions