Modify values inside sql SELECT query

Question

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)

I also see you added a row number to the SQL, but you don't state it in your question? — Mark C.
– Mark C., Commented Nov 14, 2014 at 20:05
@ConradFrix I was asking if the Row Number was something the OP wanted in the desired results, actually. Not sure what you are referring to. — Mark C.
– Mark C., Commented Nov 14, 2014 at 20:07
@Invent-Animate doh! I misunderstood the question thinking that you misunderstood the OP's question — Conrad Frix
– Conrad Frix, Commented Nov 14, 2014 at 20:09

Barmar · Accepted Answer · 2014-11-14 20:07:38Z

2

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

answered Nov 14, 2014 at 20:07

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mark C. Over a year ago

Doesn't FLOOR(RAND() * 0.0030) always return 0?

Barmar Over a year ago

@Invent-Animate Yes. The answer is really more generic, any expression can be used. Whether it's the right formula for what he wants is orthogonal.

Mauricio Trajano · Accepted Answer · 2014-11-14 20:14:37Z

0

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

edited Nov 14, 2014 at 20:14

answered Nov 14, 2014 at 20:09

Mauricio Trajano

2,9772 gold badges22 silver badges25 bronze badges

Collectives™ on Stack Overflow

Modify values inside sql SELECT query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related