Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'd like to store some geometric positions in my MySQL database. For this I use the POINT datatype. Almost everywhere I read that the function GeomFromText should be used to insert data in the table.

However, I found out that POINT(X,Y) also works. I didn't find any description why GeomFromText should be used instead of POINT.

For example I have the following simple relation:

CREATE TABLE Site (
    SiteID      BIGINT UNSIGNED,
    Position    POINT
);

And I can insert values using the following two variants:

INSERT INTO Site (
    1,
    GeomFromText( 'POINT(48.19976 16.45572)' )
);

INSERT INTO Site (
    2,
    POINT(48.19976, 16.45572)
);

When I view the table (SELECT * FROM Site) I see the same binary blob for the location, and when I view the coordinates (SELECT *, AsText(Position) FROM Site) I also see the same values.

So why should GeomFromText be used? Are there any (known) performance differences between these two variants? How is this solved in other database systems than MySQL?

share|improve this question
    
I don't know if there are any performance differences (I'd guess not but that's a guess only). But the second approach would be simpler when converting latitude and longitude values from another table. INSERT INTO Site (Position) SELECT POINT(latitude, longitude) FROM tmp is simpler than ...SELECT GeomFromText(CONCAT('POINT(',latitude,' ',longitude,')' )) ... –  ypercube Jan 23 '13 at 15:12
    
I also finde the second variant much simpler to construct, that's why I'm wondering that usually the first one is used almost everywhere where I've seen MySQL spatial extensions used. –  ComSubVie Jan 23 '13 at 15:17
    
I just tried inserting 10.000.000 locations in the table above (on my host) using both variants and didn't detect any measurable performance difference. –  ComSubVie Jan 23 '13 at 20:11

1 Answer 1

up vote 5 down vote accepted

There are two different binary formats related to the MySQL spatial extensions, the "well-known binary" (WBK) format from the standards, and the MySQL internal GEOMETRY data type.

Prior to MySQL 5.1.35, functions like POINT() didn't return the MySQL internal data type; they returned WKB... so prior to then, you had to do this:

INSERT INTO t1 (pt_col) VALUES (GeomFromWKB(Point(1,2)));

But now, as in your example, this works:

INSERT INTO t1 (pt_col) VALUES(Point(1,2));

To the developers' credit, when they changed Point() and similar functions to (more sanely) return GEOMETRY objects, they allowed GeomFromWKB() and similar functions to actually accept either WKB or MySQL Geometry data as input even though the functions are intended to accept WKB as input.

The fact that the 1st method works (in spite of being technically wrong) on newer servers and the 2nd method doesn't work at all prior to MySQL 5.1.35 might explain why examples were written using the approach you've seen -- to avoid the issue entirely. Otherwise... I've got nothing, here.

Concatenating and then parsing text seems intuitively slower and more error-prone than functions that accept proper variables as input, so I can't think of any reason to craft concatenated strings and use the text-based functions.

http://dev.mysql.com/doc/refman/5.1/en/creating-spatial-values.html#gis-wkb-functions

http://dev.mysql.com/doc/relnotes/mysql/5.1/en/news-5-1-35.html

share|improve this answer
    
Thanks, interesting that this is only mentioned as a "footnote" in the release notes and nowhere in the documentation. So I'll stay away from the text based methods. –  ComSubVie Jan 24 '13 at 8:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.