7

I have a table with a POINT geometry field. I enter latitude/longitude points into it like this:

INSERT INTO table( point )
VALUES( POINT( lon_value, lat_value );

Sometimes I do not have lat/lon values to enter. I am unable to enter a blank, a NULL, or an empty POINT() ... since POINT(0,0) is actually a location on the globe, that won't work either.

What is the solution here?

T. Brian Jones
  • 13,002
  • 25
  • 78
  • 117

3 Answers3

8

I would use coordinates of North Pole

INSERT INTO table( point )
VALUES( POINT(0.0000,90.0000);

If the actual coordinates of the pole may be an issue i would change the lon value from 0.0000.

david strachan
  • 7,174
  • 2
  • 23
  • 33
  • 3
    Really ?? There is no better way ? POINT uses [25 bytes](https://dev.mysql.com/doc/refman/5.7/en/gis-data-formats.html) from what I understand of the manual. This is a misusage of memory – Mike Casan Ballester Mar 14 '18 at 00:58
  • 1
    Has there been any update on this? Still looking for an answer. – pbarney Mar 29 '18 at 22:30
  • 1
    25 bytes is too much. They should introduce sort of `smallpoint` and `tinypoint` types in case people do not require double-precision coordinates. And not allowing `NULL` in 25-bytes is, well, absurd. And thanks for the North Pole idea. That would be cold enough to be used as null. :) – dotNET Sep 26 '18 at 16:50
3

Only GeometryCollection supports EMPTY: https://dev.mysql.com/doc/refman/8.0/en/gis-data-formats.html

INSERT INTO table( point )
VALUES( ST_GeomFromText('GEOMETRYCOLLECTION EMPTY') );
xiangyuecn
  • 31
  • 2
0

Since the valid range of latitude in degrees is -90 and +90 for the southern and northern hemisphere respectively; I'm able to switch values to and from NULL by passing in and using 99 (arbitrary out of range number) for Lat comparison like this:

("UPDATE
     tableName SET LatLng = IF(%Lat = 99, NULL, POINT(%Lat, %Lng))
     Where itemID = %itemID"
, array(
     'itemID' => $itemID, 
     'Lat' => $Lat == NULL ? 99 : $Lat,
     'Lng' => $Lng == NULL ? NULL : $Lng 
));
BrentH
  • 27
  • 4