Newbie Question

[theslever]

Hello all. Absolute beginner here so be gentle.

 

I seem to have installed the LAMP stack no problem. Just getting my teeth sunk into MysQL trying to setup a database to record all goals scored by my favourite soccer team.

 

I am also wanting to capture the trajectory of the ball for every goal scored (and ultimately want to output this through a graphing program of some kind, in fact ultimately something like this image is my aim: http://yfrog.com/nf63662354j  )

 

So having setup my first test database/table, I've immediately come to a halt trying to store the start and end co-ordinates of each goal.

 

Essentially I don't know how to add data to LineString columns. Please see below image to see what I have tried, by MySQL don't like it!

 

http://yfrog.com/9e34119306j

 

Probably a stupidly simple answer but thanks for any help 

 

 

[s0c0]

I believe your problem is this.  For a value you put in 25 50.  That is not a valid integer.  To put in something with a space it would be a varchar and therefor you would need to put in with quotes on each side.  I don't know your schema, but that is your problem.  To do an insert assuming start/finish are INTS do as follows:

INSERT INTO goals (start,finish) VALUES (25,50);

 

That will insert a single row.  To do multiples:

INSERT INTO goals (start,finish) VALUES (25,50),(25,100);

 

[fenway]

What's a LineString?

[theslever]

11.17.2.5. Class LineString

 

A LineString is a Curve with linear interpolation between points.

 

LineString Examples

 

    *

 

      On a world map, LineString objects could represent rivers.

    *

 

      In a city map, LineString objects could represent streets.

 

LineString Properties

 

    *

 

      A LineString has coordinates of segments, defined by each consecutive pair of points.

    *

 

      A LineString is a Line if it consists of exactly two points.

 

OK seeing as I'm a noob I may be completely misinterpreting the above. But from what I can tell, if I want to store co-ordinates in a SQL database then it needs to be a linestring no?

 

Or can anyone tell me an easier method to store co-ordinates?

[theslever]

mysql> INSERT INTO goals (start,finish) VALUES (25,50);

ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

 

s0c0 that is the result when I use your instructions :/

[Pikachu2000]

You'll need to post your code that inserts into the DB before this can get solved. Without that, it's mostly a crapshoot.

[theslever]

Sorry to frustrate but I don't know what that means.

 

I have set up my table as below:

 

CREATE TABLE goals (

2     goalID SMALLINT UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,

3     Player VARCHAR(40) NOT NULL,

4     Team VARCHAR(14) NOT NULL,

5     Min smallint NOT NULL,

6          Start Linestring Not Null,

7          Finish Linestring Not Null,

8          Type VARCHAR(14) NOT NULL,

9          OG VARCHAR(14) NOT NULL,

10 PRIMARY KEY (GoalID)

11     );

 

 

I have no problems inserting data into VARCHAR columns or INT columns. It's these co-ordinates that are annoying me. I've read up on spatial data and apparently LineString is the type to use to store what I want.

 

But I can't seem to add data to the Linestring type columns.

 

I have tried:

 

INSERT INTO goals (start,finish) VALUES (25,50),(25,100);

 

but apparently for Spatial Data this doesnt work. The online Manual for MysQL is very unhelpful. What am I doing wrong?

[Pikachu2000]

I did manage to find the correct syntax for that insert, but beyond that I can't help, I'm afraid. I've never had to work with these types of values, and am completely unfamiliar with them.

 

INSERT INTO `goals` (start) VALUES (GeomFromText('LINESTRING(150 50,20 500)'))

[theslever]

Thanks Pikachu. That works.

 

Looks like I'm going to have to do some significant research into Geometry in MysQL /PHP.