17

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?

  • 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
  • Please consider re-evaluating this in light of MySQL 8+ and for posterity: dba.stackexchange.com/a/227049/2639 – Evan Carroll Jan 13 at 21:30
15

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

  • 1
    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
  • 1
    Why is it that 5 years later the MySQL docs still give examples of using the ST_GeomFromText() function when inserting? Is this answer still relevant? It a bit of confusing.. dev.mysql.com/doc/refman/5.7/en/populating-spatial-columns.html – Matt Kieran Apr 8 '18 at 22:20
  • 1
    @MattKieran WKB and WKT are standardized, open formats for expressing geospatial data. The examples use them because standards-oriented geospatial applications may already hold data in these formats, allowing MySQL to accept external geometries as a single argument to ST_GeomFromText() and similar conversion functions rather than requiring external applications to use the native SQL functions that construct geometry objects, which are found in the Spatial Function Reference. The docs could be organized better. – Michael - sqlbot Apr 8 '18 at 22:54
  • Also @MattKieran this answer is only still relevant in the sense that it explains why older examples may be written contrary to what the docs indicate, any why MySQL works with the apparent type mismatches that using the functions this way would seem to indicate. All three methods -- the native SQL functions, WKB (binary), or WKT (text) -- are valid. What is not needed any more is convering the native function return values from WKB, because their return types are no longer WKB as they were many years ago. – Michael - sqlbot Apr 8 '18 at 23:04
2

MySQL 8+

For posterity the only thing that matters it 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.

-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)
  • This should be the other way around i.e. POINT(lng lat) instead of POINT(lat lng) – Zishan Jan 29 '18 at 9:32
  • MySQL doesn't use SRIDs anyway. So that's pretty useless. If you need SRIDs, migrate to PostgreSQL / PostGIS. – Evan Carroll Jun 12 '18 at 6:39
  • MySQL 8 does use SRIDs. In fact, I am having trouble with a MySQL DB migrated from 5.7 to 8 precisely because of SRIDs. – cmoran92 Apr 23 at 1:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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