22

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?

ComSubVie
  • 323
  • 1
  • 2
  • 6

3 Answers3

21

There are two different binary formats related to the MySQL spatial extensions, the "well-known binary" (WKB) 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

Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
Michael - sqlbot
  • 22,715
  • 2
  • 49
  • 76
7

MySQL 8+

For posterity the only thing that matters is that

  • Point(X,Y) is a constructor for numbers with precision and does not require converting first to text making it faster. It's also guaranteed to RETURN A POINT OR FAIL. This makes it strongly typed if you want to think of it like that.
  • Well-Known text (WKT) constructors: these are always slower, as they require an addition step to parse the Well-Known text (WKT). Note in older versions these could be found without the ST_ prefix; where available, use the version with the ST_ prefix. Use the WKT-constructors only if your input is already Well-known text. If not, use the Point(x,y) constructor above.

Clarity

Skipping the history lesson, NEVER do GeomFromText(Point(x,y)). That's horrible, unsupported, and undocumented.

Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
-1

With GeomFromText or any other *FromText function you can specify the SRID. I don't think you can do it otherwise.

PointFromText('POINT(lat lng)', 4326)
lbrandao
  • 99