Storing coordinates (lat/long) in MySQL

techguy

Hi,

I need to store the coordinates of a point in a MySQL table. I see there is a lot of talk online about spatial data types etc and how to store this type of data. I'm not sure I wan't/need to get into that.

I don't think I will need this field to be indexed i.e. I won't be searching it. I think the only time I will need it is to plot a point on Google maps.

My first thought would be to store the coord as is. i.e. x,y in a varchar field. The, when I retrieve it from the DB I can split by ',' to get x and y.

I have a feeling that this represents some bad DB deisgn principles with regards granularity.(is that the word )

Maybe I should go and have one field per coordinate component i.e. x and y ?

Any thoughts?

Webmonkey

techguy wrote: »

Hi,

I need to store the coordinates of a point in a MySQL table. I see there is a lot of talk online about spatial data types etc and how to store this type of data. I'm not sure I wan't/need to get into that.

I don't think I will need this field to be indexed i.e. I won't be searching it. I think the only time I will need it is to plot a point on Google maps.

My first thought would be to store the coord as is. i.e. x,y in a varchar field. The, when I retrieve it from the DB I can split by ',' to get x and y.

I have a feeling that this represents some bad DB deisgn principles with regards granularity.(is that the word )

Maybe I should go and have one field per coordinate component i.e. x and y ?

Any thoughts?

I done some stuff like this just the other day.

Why don't you store Lat/Lon in Decimal Degrees and use a double/decimal data type.

ocallagh

Store them as two fields latitidue and longitude (both floats). It's easy enough to index them and search on them bare like that.

If you want to do any meaningful queries for example grid/area searches you should definitely look into storing them as a point.

You can always convert a lat/long to a point at any time in the future and add the necessary spatial indexing then.


To add the point data type:

ALTER TABLE yourtable ADD location POINT NOT NULL;

Add the spatial index:

CREATE SPATIAL INDEX location ON yourtable(location);

Insert a single value into the location field:

UPDATE yourtable set location = PointFromText(CONCAT('POINT(',54.124,' ',-6.231,')')) WHERE .....

or just update the lot from your long/lat values

UPDATE yourtable set location = PointFromText(CONCAT('POINT(',latitude,' ',longitude,')'))

techguy

Thanks guys..

I think i'll go with ocallaghs suggestion and store them in individual FLOAT fields. I'm taking it one comma seperated field is a no no then?

i71jskz5xu42pb

Webmonkey wrote: »

I done some stuff like this just the other day.

Why don't you store Lat/Lon in Decimal Degrees and use a double/decimal data type.

Because MySQL has built in spatial support. If you store the data as double/decimal/float etc you will have to reimplement all the spatial functions yourself. ocallagh's suggestion on storing them as points is the way to go assuming you are going to want to run a query against the data.

techguy

I don't think i'll want to run queries on the data. All i'll need to do is get the points from the DB so I can place a marker on a Google Map.

Bob_Harris

techguy wrote: »

Thanks guys..

I think i'll go with ocallaghs suggestion and store them in individual FLOAT fields. I'm taking it one comma seperated field is a no no then?

Why add the extra work of splitting the comma-delimited data when you could use separate fields in a table?

It's easy to get the individual values from the Google Maps GLatLng object

GLatLng.lat()
GLatLng.lng()

and easy to create a new point with the individual values:

new GLatLng(latFromDB,lngFromDB)

Data type of FLOAT will be fine for simple storing and retrieving to place a point on a map.