CODEWITHSUNDEEP
javascriptmysqlpolygon
bdbd
bdbd

Reputation: 91

How to format a Javascript object as a polygon in Mysql

I want to store a Javascript object as a polygon in a Mysql database. I can define start and end points of x and y to give me four corners:

var polygon = [
    xstart +' '+ ystart,
    xend +' '+ ystart,
    xend +' '+ yend,
    xstart +' '+ yend,
    xstart +' '+ ystart
];

I can format this object as a string

polygon = "ST_GeomFromText('POLYGON(("+ polygon.toString() +"))'";

When I insert this into a Mysql database

INSERT INTO `caption` (`caption_id`, `caption_area`) VALUES (NULL, '\'POLYGON((0.28 0.33,0.35 0.33,0.35 0.45,0.28 0.45,0.28 0.33))\'')

the query fails

#1416 - Cannot get geometry object from data you send to the GEOMETRY field

How can I format the Javascript object, or the query, to correctly insert a polygon in to a database?

Upvotes: 2

Views: 389

Answers (2)

Ieshua
Ieshua

Reputation: 154

POLYGON is a Spatial Data Type from MySQL spatial extension defined in the Open Geospatial Consortium OpenGIS. You can define a Spatial Data Type in MySQL for your four polygon. The following is an example using LINESTRING: 

    LINESTRING(0.28540775806607094 0.3356063323928521, 0.35407728596306665      0.3356063323928521, 0.35407728596306665 0.45764498813705906, 0.28540775806607094 0.45764498813705906, 0.28540775806607094 0.3356063323928521)

So, it stores caption_area as TEXT using:

    SET @g = 'LINESTRING(0.28540775806607094 0.3356063323928521, 0.35407728596306665 0.3356063323928521, 0.35407728596306665 0.45764498813705906, 0.28540775806607094 0.45764498813705906, 0.28540775806607094 0.3356063323928521)';

    INTO `caption` (`caption_id`, `caption_area`) VALUES (NULL,ST_AsText(ST_GeomFromText(@g)));

Also, an example with POLYGON:

    SET @g = 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';
    INTO `caption` (`caption_id`, `caption_area`)
    VALUES  (NULL,ST_AsText(ST_GeomFromText(@g)));

Upvotes: 0

nbk
nbk

Reputation: 49375

in mysql you must use ST_GeomFromText

polygon = "ST_GeomFromText('POLYGON("+ polygon.toString() +")')";

I am unsure what you need the number 0 at the end.

Upvotes: 1

Related Questions