CODEWITHSUNDEEP
sql
Theodoros80
Theodoros80

Reputation: 796

Modify values inside sql SELECT query

I have the following sql query:

SELECT town,latitude,longitude FROM coordinates

The following will return e.g 

"California,10.02313,20.123213"

I am using the query results to place markers on gmaps. But if i have 10 markers for California, they will pile up, since the coordinates are the same.

What i want to add a (very small) random number to lat and lng so markers will appear inside california's limits but they wont pile up.

How can i add, lets say FLOOR(RAND() * 0.0030)+0.0040 to lat and lng so the result will be:

"1,California,10.02322,20.123254"
"2,California,10.02313,20.123213"

Notice the small change in coordinates, but still inside California's state limits(with very little variation)

Upvotes: 0

Views: 50

Answers (2)

Mauricio Trajano
Mauricio Trajano

Reputation: 2937

The +0.004 is unnecessary since it does not randomize the data, it just adds a constant to it

SELECT town, FLOOR(RAND() * 0.0030) + latitude as latitude, FLOOR(RAND() * 0.0030) + longitude as longitude FROM coordinates

I would also suggest not adding floor since it would probably just add 0 to the column (since it rounds the offset down to an integer) instead do something like

SELECT town, (RAND() * 0.0030 + latitude) as latitude, (RAND() * 0.0030 + longitude) as longitude FROM coordinates

Upvotes: 0

Barmar
Barmar

Reputation: 782785

SELECT can be followed by arbitrary expressions, they don't have to be just column names. So you can write:

SELECT town,
       latitude + FLOOR(RAND() * 0.0030)+0.0040 AS latitude,
       longitude + FLOOR(RAND() * 0.0030)+0.0040 AS longitude

Upvotes: 2

Related Questions