2

I have created a struct to store spatial types and I have created a scan function to help query rows in my database. I am having issues inserting this type.

I can insert data using the following sql;

INSERT INTO 'table' ('spot') VALUES (GeomFromText('POINT(10 10)'));

If I use Value interface in database/sql/driver;

type Value interface{}

Value is a value that drivers must be able to handle. It is either nil or an instance of one of these types:

int64

float64

bool

[]byte

string [*] everywhere except from Rows.Next.

time.Time

And use this code;

func (p Point) Value() (driver.Value, error) {
    return "GeomFromText('" + p.ToWKT() + "')", nil
}

I end up with the following sql statement going to the database;

INSERT INTO 'table' ('spot') VALUES ('GeomFromText('POINT(10 10)')');

The issue being that the function GeomFromText is in quotes. Is there a way to avoid this scenario? I am using gorm and trying to keep raw sql queries to a minimum.

The mysql type being used on the database end is a point.

Community
  • 1
  • 1
devfubar
  • 436
  • 2
  • 14
  • typically the table names and columns would be wrapped in back-ticks or nothing. For instance, a back-tick would be spectacular if the column name was `limit` or `My Column` or `this-thing` – Drew Dec 11 '15 at 23:16
  • Have you tried keeping the function call `GeomFromText` in the query and have your `Point` `Value` method return `p.ToWKT()`? – jmaloney Dec 11 '15 at 23:25
  • Thanks for the replies. I suppose I didn't make my difficulties clear. I have no problem with my schema and I know how to insert spatial data, it's more of a sql driver issue. I know that `db.Exec("INSERT INTO 'table' ('spot') VALUES (GeomFromText($1))", p.ToWKT())` will work however I am trying to avoid using raw sql queries usch as that. I know that the reason my code is failing is because `func (p Point) Value()` is returning a string and my MySQL function is being placed in quotes. Is there a way of using this interface and have only part of the string placed in quotes? – devfubar Dec 12 '15 at 00:53

1 Answers1

1

Please see the two urls below where the concept was poached from

Schema

-- http://howto-use-mysql-spatial-ext.blogspot.com/

create table Points
(   id int auto_increment primary key,
    name VARCHAR(20) not null, 
    location Point NOT NULL, 
    description VARCHAR(200) not null, 
    SPATIAL INDEX(location),
    key(name)
)engine=MyISAM; -- for use of spatial indexes and avoiding error 1464

-- insert a row, so we can prove Update later will work
INSERT INTO Points (name, location, description) VALUES 
( 'point1' , GeomFromText( ' POINT(31.5 42.2) ' ) , 'some place');

Update statement

-- concept borrowed from http://stackoverflow.com/a/7135890
UPDATE Points 
set location = PointFromText(CONCAT('POINT(',13.33,' ',26.48,')'))
where id=1;

Verify

select * from points;

(when you open the Value Editor to see the blob, the point is updated)

So, the takeaway is to play with the concat() inside of the update statement.

Drew
  • 24,851
  • 10
  • 43
  • 78
  • I am running into the same issue. Is this writing raw SQL query? I don't understand how you make it work. – huggie Mar 05 '20 at 03:58