Tag: Saved for Later

I think most people will agree that downloading RPMs from a website is kind of old fashioned when there are yum repos. After a number of user requests, we have now launched the official yum repos for MySQL. Can’t wait? Neither can I. On a fresh install: Download a setup package for your distribution from http://dev.mysql.com/downloads/repo yum […]
via MySQL Server Blog
MySQL yum repositories

Ever tried to control your reaction when you were really, really mad? Having good intentions is one thing—reality is quite another. You can think all you want that the next time your kids provoke you, you will not react angrily no matter how mad you are. But seriously, when you’re really mad, can you even think straight, let alone control your reaction?

This post originally appeared on A Fine Parent.

The devil is in the details. Unless you have a solid plan of action under your sleeve, you will probably just end up screaming at your kids, feeling guilty, possibly apologizing, and then repeating the whole behavior all over again. If anything, that just erodes your connection with your kids further. That’s certainly not what we’re going after here.

If you really want to give your good intentions a fighting chance of success and ensure that you will indeed not scream at your kids no matter how mad you are, you need to act now. Assuming you’re not angry at the moment, now is the time to decide how you’ll respond at a later time when you’re indeed angry. Making a list of possible responses and then reaching out to your pre-committed choices when you are angry, substantially increases your chances of success. (There’s a whole body of research to support this.)

Research consistently shows that the more in advance you make a decision—irrespective of whether it’s about your money, exercise, or even which movie to watch—the more likely you are to make better choices. The closer you are to the decision point, the more short-sighted your decision gets, with spot decisions made under pressure being some of your worst ones. Additionally, if you make a choice and commit to it, you completely bypass your brain and reach out to a pre-committed choice, avoiding the detrimental outcomes of short-sighted decisions.

I’ll get the ball rolling with the list of eight things I rely on to get me through. The key is to become aware of the choices you have, and pre-commit to them so when you’re angry, you can bypass your short-sighted response of yelling at your kids.

Get Out of the Situation

If I’m at home, I will ask my husband if he can take over, and I just walk out of the room for a few minutes. Yes, that means I literally dump the situation on him. From past experience, I’ve noticed that most often—since he was not in the middle of it when the storm brewed—he’s in a much better situation to handle it without yelling and I get a break to cool off.

Of course this works vice versa too. When I notice that my husband is starting to lose it, I’ll step in and scoop up, so he can step out. As for my daughter, just the change of scene, where one parent steps out and the other steps in, seems to help a lot to diffuse the situation.

Let My Daughter Know that I’m Angry

Again, from past experience I know this one works. Instead of just snapping, I take a deep breath and tell her, "Mommy is getting really mad now, Sweetie." Sometimes, she’ll stop the offending behavior, but more often than not, she counters back with “I’m mad too” or “No, you CANNOT be mad” (she is five years old and that’s a valid argument in her book). Either way, I think a channel of communication is established. After that I just walk away and let her be for a few minutes to calm herself down. And take my time to calm myself down. Or hold her in my lap and say, "Let’s both be very quiet for a few minutes until we calm down."

I got this last step from the book Peaceful Parent, Happy Kids: How to Stop Yelling and Start Connecting. I felt really hokey the first time I tried it—I mean, I was hopping mad and she was bawling at the top of her voice! But then, quite unexpectedly, my daughter visibly calmed down, and when I was setting her down from my lap she said “I want to hug some more, mama.” So we just sat there for some more time, in a sappy scene I wouldn’t have envisioned myself a few minutes before, her sniffling and me trying to calm down. And then she declared "I’m done now," and hopped off and started playing as though nothing ever happened. Ah, to be five and be able to forgive and forget so easily!

Set a Time Limit to End the Hostilities

The scariest thing about getting mad is that there is no clear boundary to when things will settle back to normal again. I’ve found that intentionally setting that boundary helps a lot to get the situation under control. I remember, once when I started getting mad, I hissed out, “I am very mad now, so I am going to go wash the dishes and try to calm down. When I am done with the dishes I will be done being mad.” My daughter pitifully cried “I don’t want you to be mad at me, mama” and I said as calmly as I could “I am not mad at you. I still love you. But you have not finished eating yet and it’s getting very late. I am tired and feeling crabby (a term she understands). I need some time to calm down. And you need to finish eating. I will be over here washing dishes.”

She started whining at first and when she got nothing out of me, suddenly (and very surprisingly!) there was silence on her end. All I could hear was the sound of spoon on the plate and a declaration a few minutes later that she was done. I rinsed and dried my hands, walked over to her, inspected her plate and gave her a big smile. And she gave me a big hug to make me feel better. All was well with the world again. One more dinnertime explosion avoided. And one more trick in my parenting toolkit that is proven to work!

Put Things in Perspective

Sometimes all I need to do to diffuse myself is to put things in perspective. If it’s the morning and she wants to play instead of getting ready, and it’s really late and driving me nuts, all I have to do is think back to a time long, long ago—same situation, different child. I suspect, I wouldn’t have wanted to stop playing either. Kids are kids. They want to play. That’s that. No point getting all worked up over it, right? Sigh. Take a deep breath. Think of an alternate plan to get her to do what I want her to do.

Start Counting

I don’t think this one will exactly fit in the “positive parenting” paradigm since it uses threats and fear of punishment and bribes. But I will put it out there since this works for me. Basically, instead of yelling at her, I tell her in as much of a calm voice as I can muster up, “I am going to count to five. If you are don’t start picking up the toys, they all go into the trash can.” And then I start counting. 1… 2….3….4…. Generally, by the time I get to 3 and my voice starts to rise she starts picking up. I’ll start helping her out as I continue counting, adjusting the pace of counting so we can finish up cleaning by the time I get to 5.

I have no idea why this works, but it does work really, really well to the point that a lot of the time I just say “You better do something before I count to 5” and I just start counting. I don’t even have to mention the consequences. I had picked this tip from a colleague and I remember her saying “One of these days she is going to call my bluff and let me finish counting. I have no idea what I will do then.” I have no idea either, but until that day, this trick is in.

Turn it Into a Fictional Story

My daughter loves stories. And ever since she was born, I have honed my storytelling skills to the point that I can turn any situation into a story. Last time we went to India, there was a little servant boy called “Heera” who had left a strong impression on my daughter. For almost a year after we got back, I have told her “Heera Boy” stories to diffuse all kinds of situations. For instance, when she wouldn’t want to go down for a nap, instead of arguing/pleading/yelling, I would start out with “Do you know what happened when Heera boy did not take a nap?” Before the story was done, she would be in bed with eyes starting to slowly close shut.

I’ve summoned Heera boy to get her to drink her milk, get dressed, brush teeth, take medicine and so many other things! The stories all have similar structure–Heera boy did (or did not) want to do something. This would result in some very nasty consequences. Then either his grandma or his fairy godmother would come and tell him what he should do to avoid the consequences, and so he changed his ways. Everything was well with the world and he lived happily ever after.

A good thing about trying to make up a story is that my brain is too busy thinking what I should say next, and hers is too busy imagining it, and neither of us has room in our brains to yell/rebel anymore.

Use Humor

Frankly, this doesn’t come naturally to me, especially when I’m mad. But when I do manage to pull it off, the results are quite awesome. Instead of getting mad at her, I turn it into a fun game. “You want to eat M&Ms before dinner? Before dinner? That makes me so mad… so mad that I’m going to eat you up!” And I start chasing her around the house. Sometimes it works, sometimes it doesn’t. But the times that it works, we have both run around all over the house and are tired and giggling and the rebellious moment is most often forgotten.

Visualize the Aftermath

Finally, here is one for those days when I’m just not in a good place. All of us have those days—days when we almost wait for someone to slip up so we can pounce on them. This last one is my final attempt to hold myself together on such days. One thing about not starting out being a patient mom (unfortunately) is that I’ve been there, done that. I know the dark places you can go to when you don’t get a hold of yourself.

I am all too familiar with the sick feeling you get at the bottom of your stomach when you drive all the way to the daycare without a word and leave your child among strangers without so much as a smile on your face. Or the nasty taste in your mouth when your child finally falls asleep because she’s cried so much there isn’t much energy left in her to do anything else. Or the real panic in her eyes when she senses that you really are over the edge.

Those are not my proudest moments, and I would never want to go back there. So, when I sense that I really am getting out of control, I grab hold of one of those images and keep telling myself over and over, “don’t go there.” In a very negative way, it works. Maybe it’s the tears stinging the back of my eyes, or the shame that numbs my anger. Whatever it is, I manage to not let loose. You’ve got to do what you’ve got to do, right?

So there you have it–the good, the bad, and the ugly of how I try to control my reaction when I am too angry to think straight. Now it’s your turn. How do you manage anger when it comes to your kids?

via Lifehacker
How to Stay Calm and Not Yell at Your Kids (Even If You’re Really Mad)

When Mountain Lion was released, a bunch of fantastic wallpapers were hidden away and only used for screen savers. It turns out Apple hid those wallpapers in a different place this time with Mavericks, but users over at StackExchange have managed to find them again.

Like last time, these are the images that Apple uses in their screen savers, which means they’re high resolution nature photos. To grab these, just press Command+Shift+G from your desktop to bring up the "Go to Folder" menu and type in:

/Library/Screen Savers/Default Collections/

That takes you to the new folder location where you can copy those high resolution images and use them for your wallpapers.

What un(der)-documented features ahve you stumbled upon in Mavericks? | StackExchange

via Lifehacker
Uncover Mavericks’ 43 Secret Wallpapers

Good stuff:
via National Review Online – Media Blog
Jon Stewart Not Happy with Obamacare Launch

Do you sometimes feel like you spend all your time managing crises? That your life is basically spent putting out one proverbial fire after another? At the end of the day do you feel completely sapped and drained of energy, and yet can’t point to anything you accomplished of real significance? Yes? Then you, my […]

via The Art of Manliness
The Eisenhower Decision Matrix: How to Distinguish Between Urgent and Important Tasks and Make Real Progress in Your Life

In the very early days of Percona Vadim wrote very nice post about GROUP_CONCAT.But I want to show you a bit more about it.When is GROUP_CONCAT useful? Usually while working with Support customers I recommend it when you have aggregation of many-to-many info. It makes the view simpler and more beautiful and it doesn’t need much effort to make it work.Some simple examples:This is a test table:CREATE TABLE `group_c` (
`parent_id` int(11) DEFAULT NULL,
`child_id` int(11) DEFAULT NULL
) ENGINE=InnoDB;
INSERT INTO group_c(parent_id, child_id)
VALUES (1,1),(1,2),(1,3),(2,1),(2,4),(1,4),(2,6),(3,1),(3,2),(4,1),(4,1),(1,1),(5,0);Without grouping info the only way you can check things is:mysql> SELECT DISTINCT
-> parent_id, child_id
-> FROM group_c
-> ORDER BY parent_id;
+———–+———-+
| parent_id | child_id |
+———–+———-+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 1 |
| 2 | 3 |
| 2 | 4 |
| 2 | 6 |
| 3 | 1 |
| 3 | 2 |
| 4 | 1 |
| 5 | 0 |
+———–+———-+
12 rows in set (0.00 sec)But it looks much better and easier to read with GROUP_CONCAT:mysql> SELECT DISTINCT
-> parent_id, GROUP_CONCAT(DISTINCT child_id ORDER BY child_id) AS child_id_list
-> FROM group_c
-> group by parent_id
-> ORDER BY parent_id;
+———–+—————+
| parent_id | child_id_list |
+———–+—————+
| 1 | 1,2,3,4 |
| 2 | 1,3,4,6 |
| 3 | 1,2 |
| 4 | 1 |
| 5 | 0 |
+———–+—————+
5 rows in set (0.00 sec)Easy? Let’s go to production usage and some “real” examples Assume you have 4 Support Engineers who were working with 6 Customers this week on 15 issues.As it usually happens: everyone (sure, except those who are on vacation ) worked on everything with everybody.How you would represent it?Here is my way:Create test tables:engineers (id, name, surname, URL) – list of engineerscustomers (id, company name, URL) – list of customersissues (id, customer_id, description) – list of issues assigned to customersworkflow (id, engineer_id, issue_id) – list of actions: issues and engineers who worked on them– Engineers
CREATE TABLE engineers (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
e_name VARCHAR(30) NOT NULL,
e_surname VARCHAR(30) NOT NULL,
url VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
— Customers
CREATE TABLE customers (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
company_name VARCHAR(30) NOT NULL,
url VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
— Issues (Issue-Customer)
CREATE TABLE issues (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id VARCHAR(30) NOT NULL,
description TEXT,
PRIMARY KEY (id)
) ENGINE=InnoDB;
— Workflow (Action: Engineer-Issue(Customer))
CREATE TABLE workflow (
action_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
engineer_id SMALLINT UNSIGNED NOT NULL,
issue_id SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (action_id)
) ENGINE=InnoDB;
INSERT INTO engineers (e_name, e_surname, url)
VALUES
(‘Miguel’, ‘Nieto’, ‘http://www.percona.com/about-us/our-team/miguel-angel-nieto’),
(‘Marcos’, ‘Albe’, ‘http://www.percona.com/about-us/our-team/marcos-albe’),
(‘Valerii’, ‘Kravchuk’, ‘http://www.percona.com/about-us/our-team/valerii-kravchuk’),
(‘Michael’, ‘Rikmas’, ‘http://www.percona.com/about-us/our-team/michael-rikmas’);
INSERT INTO customers (company_name, url)
VALUES
(‘OT’,’http://www.ovaistariq.net/’),
(‘PZ’,’http://www.peterzaitsev.com/’),
(‘VK’,’http://mysqlentomologist.blogspot.com/’),
(‘FD’,’http://www.lefred.be/’),
(‘AS’,’http://mysqlunlimited.blogspot.com/’),
(‘SS’,’https://www.flamingspork.com/blog/’);
INSERT INTO issues(customer_id, description)
VALUES
(1,’Fix replication’),
(2,’Help with installation of Percona Cluster’),
(3,’Hardware suggestions’),
(4,’Error: no space left’),
(5,’Help with setup daily backup by Xtrabackup’),
(6,’Poke sales about Support agreement renewal’),
(4,’Add more accounts for customer’),
(2,’Create Hot Fix of Bug 1040735′),
(1,’Query optimisation’),
(1,’Prepare custom build for Solaris’),
(2,’explain about Percona Monitoring plugins’),
(6,’Prepare access for customer servers for future work’),
(5,’Decribe load balancing for pt-online-schema-change’),
(4,’Managing deadlocks’),
(1,’Suggestions about buffer pool size’);
INSERT INTO workflow (engineer_id, issue_id)
VALUES (1,1),(4,2),(2,3),(1,4),(3,5),(2,6),(3,7),(2,8),(2,9),(1,10),(3,11),(2,12),(2,13),(3,14),(1,15),(1,9),(4,14),(2,9),(1,15),(3,10),(4,2),(2,15),(4,8),(4,4),(3,11),(1,7),(3,7),(1,1),(1,9),(3,4),(4,3),(1,5),(1,7),(1,4),(2,4),(2,5);Examples:List of issues for each engineer (GROUP_CONCAT):mysql> SELECT
-> CONCAT (e_name, ‘ ‘, e_surname) AS engineer,
-> GROUP_CONCAT(DISTINCT issue_id, ‘ (‘, c.company_name,’)’ ORDER BY issue_id SEPARATOR ‘, ‘ ) AS ‘issue (customer)’
-> FROM
-> workflow w,
-> engineers e,
-> customers c,
-> issues i
-> WHERE
-> w.engineer_id = e.id
-> AND w.issue_id = i.id
-> AND i.customer_id = c.id
-> GROUP BY
-> e.id
-> ORDER BY
-> e_name, e_surname;
+——————+—————————————————————————+
| engineer | issue (customer) |
+——————+—————————————————————————+
| Marcos Albe | 3 (VK), 4 (FD), 5 (AS), 6 (SS), 8 (PZ), 9 (OT), 12 (SS), 13 (AS), 15 (OT) |
| Michael Rikmas | 2 (PZ), 3 (VK), 4 (FD), 8 (PZ), 14 (FD) |
| Miguel Nieto | 1 (OT), 4 (FD), 5 (AS), 7 (FD), 9 (OT), 10 (OT), 15 (OT) |
| Valerii Kravchuk | 4 (FD), 5 (AS), 7 (FD), 10 (OT), 11 (PZ), 14 (FD) |
+——————+—————————————————————————+
4 rows in set (0.00 sec)List of engineers for each customer (GROUP_CONCAT inside of GROUP_CONCAT):mysql> SELECT
-> c.company_name AS company,
-> GROUP_CONCAT(DISTINCT issue_id, ‘ (‘, engineer_list, ‘)’ ORDER BY issue_id SEPARATOR ‘, ‘ ) AS issue
-> FROM
-> workflow w,
-> engineers e,
-> customers c,
-> issues i,
-> (SELECT
-> i.id AS i_id,
-> GROUP_CONCAT(DISTINCT CONCAT(e_name, ‘ ‘, e_surname) ORDER BY e_name SEPARATOR ‘, ‘) AS engineer_list
-> FROM
-> workflow w,
-> engineers e,
-> issues i
-> WHERE
-> w.engineer_id = e.id
-> AND w.issue_id = i.id
-> GROUP BY
-> i.id) AS e_list
-> WHERE
-> w.engineer_id = e.id
-> AND w.issue_id = i.id
-> AND i.customer_id = c.id
-> AND w.issue_id = e_list.i_id
-> GROUP BY
-> c.id
-> ORDER BY
-> c.company_name;
+———+——————————————————————————————————————————————–+
| company | issue (engineer) |
+———+——————————————————————————————————————————————–+
| AS | 5 (Marcos Albe, Miguel Nieto, Valerii Kravchuk), 13 (Marcos Albe) |
| FD | 4 (Marcos Albe, Michael Rikmas, Miguel Nieto, Valerii Kravchuk), 7 (Miguel Nieto, Valerii Kravchuk), 14 (Michael Rikmas, Valerii Kravchuk) |
| OT | 1 (Miguel Nieto), 9 (Marcos Albe, Miguel Nieto), 10 (Miguel Nieto, Valerii Kravchuk), 15 (Marcos Albe, Miguel Nieto) |
| PZ | 2 (Michael Rikmas), 8 (Marcos Albe, Michael Rikmas), 11 (Valerii Kravchuk) |
| SS | 6 (Marcos Albe), 12 (Marcos Albe) |
| VK | 3 (Marcos Albe, Michael Rikmas) |
+———+——————————————————————————————————————————————–+
6 rows in set (0.00 sec)PHP/HTML? Why not? It’s easy Source Code:’, CONCAT(c.company_name), ” ORDER BY e_name SEPARATOR ‘, ‘) AS company,
i.description,
GROUP_CONCAT(DISTINCT ”, CONCAT(e_name, ‘ ‘, e_surname), ” ORDER BY e_name SEPARATOR ‘, ‘) AS engineer_list
FROM
workflow w,
engineers e,
customers c,
issues i
WHERE
w.engineer_id = e.id
AND w.issue_id = i.id
AND i.customer_id = c.id
GROUP BY
i.id
ORDER BY
i.id";
$result = $mysqli->query($query);
while($row = $result->fetch_array())
{
$rows[] = $row;
}
echo "";
foreach($rows as $row)
{
echo "’;
}
echo "“.$row["id"].’‘.$row["company"].’‘.$row["description"].’‘.$row["engineer_list"].’";
$result->close();
$mysqli->close();
?>Result:1OTFix replicationMiguel Nieto2PZHelp with installation of Percona ClusterMichael Rikmas3VKHardware suggestionsMarcos Albe, Michael Rikmas4FDError: no space leftMarcos Albe, Michael Rikmas, Miguel Nieto, Valerii Kravchuk5ASHelp with setup daily backup by XtrabackupMarcos Albe, Miguel Nieto, Valerii Kravchuk6SSPoke sales about Support agreement renewalMarcos Albe7FDAdd more accounts for customerMiguel Nieto, Valerii Kravchuk8PZCreate Hot Fix of Bug 1040735Marcos Albe, Michael Rikmas9OTQuery optimisationMarcos Albe, Miguel Nieto10OTPrepare custom build for SolarisMiguel Nieto, Valerii Kravchuk11PZexplain about Percona Monitoring pluginsValerii Kravchuk12SSPrepare access for customer servers for future workMarcos Albe13ASDecribe load balancing for pt-online-schema-changeMarcos Albe14FDManaging deadlocksMichael Rikmas, Valerii Kravchuk15OTSuggestions about buffer pool sizeMarcos Albe, Miguel NietoThat’s a power of GROUP_CONCAT!The post The power of MySQL’s GROUP_CONCAT appeared first on MySQL Performance Blog.
via Planet MySQL
The power of MySQL’s GROUP_CONCAT

WASHINGTON—Responding to widespread criticism regarding its health care website, the federal government today unveiled its new, improved Obamacare program, which allows Americans to purchase health insurance after installing a software bundle contai…

via The Onion
New, Improved Obamacare Program Released On 35 Floppy Disks

Geo-enabled (or location enabled) applications are very common nowadays and many of them use MySQL. The common tasks for such applications are:Find all points of interests (i.e. coffee shops) around (i.e. a 10 mile radius) the given location (latitude and longitude). For example we want to show this to a user of the mobile application when we know his/her approximate location. (This usually means we need to calculate a distance between 2 points on Earth).Find a ZIP code (U.S. Postal address) for the given location or determine if this location is within the given area. Another example is to find a school district for the given property.MySQL had the spatial functions originally (implementation follows a subset of OpenGIS standard). However, there are 2 major limitation of MySQL spatial functions that can make it difficult to use those functions in geo-enabled applications:Distance between 2 points.  The “distance” function was not implemented before MySQL 5.6. In addition (even in MySQL 5.6), all calculations (e.g. distance between 2 points) are done using a planar coordinate system (Euclidean geometry). For the distance between 2 points on Earth this can produce incorrect results.Determine if the point is inside a polygon. Before MySQL 5.6 the functions that test the spatial relationships between 2 geometries (i.e. find if the given point is within a polygon) only used a Minimum Bounding Rectangle (MBR). This is a major limitation for example #2 above (I will explain it below).In my old presentation for the 2006 MySQL User Conference I  showed how to calculate distances on Earth in MySQL without using the MySQL spatial functions. In short, one can store the latitude and longitude coordinates directly in MySQL fields (decimal) and use a haversine  formula to calculate distance.New MySQL 5.6 Geo Spatial Functions The good news is:1) MySQL 5.6 adds a set of new functions (some of them are not 100% documented though) that use the object shapes rather than the MBR to calculate spatial relationships. Those new functions begins with “ST_”, i.e.contains(g1, g2)  uses MBR only (not exact!)st_contains(g1, g2) uses exact shapes2) MySQL 5.6 implements st_distance(g1, g2) function that calculates the distance between 2 geometries, which is currently not documented (I’ve filed the feature request to document the st_distance function in MySQL)The bad news is:1) All functions still only use the planar system coordinates. Different SRIDs are not supported.2) Spatial indexes (RTREE) are only supported for MyISAM tables. One can use the functions for InnoDB tables, but it will not use spatial keys.Example of MySQL’s MBR “false positives”To illustrate why we do not want to use MBR-based functions for geospatial search, I’ve generated 2 polygons that represent 2 zip code boundaries in San Francisco, CA and placed it on Google Maps.The blue rectangle represents the Minimum Bounding Rectangle of Zip code “91102″ (I’ve used envelope() mysql function to obtain coordinates for the MBR). As we can see it covers both zip code 94103 and 94102. In this case if we have coordinates of a building in the city’s “south of market” district (ZIP 91103) and try to find a zip code it belongs to using the “contains()” function we will have a “false positives”:mysql> select zip from postalcodes where contains(geom, point(-122.409153, 37.77765));
+——-+
| zip |
+——-+
| 94102 |
| 94103 |
| 94158 |
+——-+
3 rows in set (0.00 sec)In this particular example we got 3 zip codes as the MBR of 94158 also overlaps this area. Another point in “south of market” can actually produce 4 different zip codes. However, in MySQL 5.6 we can use the new st_contains function:mysql> select zip from postalcodes where st_contains(geom, point(-122.409153, 37.77765));
+——-+
| zip |
+——-+
| 94103 |
+——-+
1 row in set (0.00 sec)As we can see st_contains() produces the correct results.Find a ZIP code for the given locationStarting with MySQL 5.6 one can use the MySQL spatial functions st_contains or st_within to find if the given point is inside the given polygon. In our scenario we will need to find the zip code for the given latitude and longitude. To do that in MySQL we can perform the following steps:Load the zip code boundaries into MySQL as a multipoligon. There are a number of ways to get this done, one way is to download the shape files from the Census website and convert them to MySQL using org2org utility. (I will describe this in more detail in upcoming blog posts). The data will be stored as MySQL Geometry object, to convert it to text we can use astext(geom) function.Use the st_contains() or st_within() functions:mysql> select zip from postalcodes where st_contains(geom, point(-122.409153, 37.77765));
+——-+
| zip |
+——-+
| 94103 |
+——-+
1 row in set (0.00 sec) ormysql> select zip from postalcodes where st_within(point(-122.409153, 37.77765), geom);
+——-+
| zip |
+——-+
| 94103 |
+——-+
1 row in set (0.00 sec)Spatial Index for “ST_” functionsMyISAM tables support Spatial indexes, so the above queries will use those indexes. Example:mysql> alter table postalcodes add spatial index zip_boundaries_spatial (geom);
Query OK, 35679 rows affected (5.30 sec)
Records: 35679 Duplicates: 0 Warnings: 0
mysql> explain select zip from postalcodes where st_contains(geom, point(-122.409153, 37.77765))\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: postalcodes
type: range
possible_keys: zip_boundaries_spatial
key: zip_boundaries_spatial
key_len: 34
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.01 sec)As we can see our spatial index is used for those functions. If we ignore or remove the index, the query will run significantly slower:mysql> select zip from postalcodes where st_within(point(-122.409153, 37.77765), geom);
+——-+
| zip |
+——-+
| 94103 |
+——-+
1 row in set (0.00 sec)
mysql> select zip from postalcodes ignore index (zip_boundaries_spatial) where st_contains(geom, point(-122.409153, 37.77765));
+——-+
| zip |
+——-+
| 94103 |
+——-+
1 row in set (4.24 sec)The InnoDB engine does not support spatial indexes, so those queries will be slow. As zip boundaries does not change often we can potentially use MyISAM tables for them.Find all coffee shops in a 10-mile radiusMySQL 5.6 supports st_distance functions with 2 drawbacks:It only supports planar coordinatesIt does not use indexGiven those major limitations, it is not very easy to use st_distance function for the geo enabled applications. If we simply need to find a distance between 2 points it is easier to store lat, lon directly and use harvesine expression (as described above).However it is still possible to use the st_distance() if we do not need exact numbers for the distance between 2 points (i.e. we only need to sort by distance). In our example, to find all coffee shops we will need to:Get the 10 mile radius MBR and use “within()” or “st_within()” functionUse st_distance function in the order by clauseFirst, we will calculate an envelope (square) to include approximately 10 miles, using the following approximations:1 degree of latitude ~= 69 miles1 degree of longitude ~= cos(latitude)*69 milesset @lat= 37.615223;
set @lon = -122.389979;
set @dist = 10;
set @rlon1 = @lon-@dist/abs(cos(radians(@lat))*69);
set @rlon2 = @lon+@dist/abs(cos(radians(@lat))*69);
set @rlat1 = @lat-(@dist/69);
set @rlat2 = @lat+(@dist/69);@lat and @lon in this example are the coordinates for the San Francisco International Airport (SFO).This will give us a set of coordinates (points) for the lower left and upper right corner of our square. Then we can use a MySQL’s envelope function to generate the MBR (we use linestring to draw a line between the 2 generated points and then envelope to draw an square):select astext(envelope(linestring(point(@rlon1, @rlat1), point(@rlon2, @rlat2))));The “envelope” will look like this:This is not exactly a 10-mile radius, however it may be close enough. Now we can find all points around SFO airport and sort by distance.mysql> select astext(shape), name from waypoints
where st_within(shape, envelope(linestring(point(@rlon1, @rlat1), point(@rlon2, @rlat2))))
order by st_distance(point(@lon, @lat), shape) limit 10;
+——————————–+——————————-+
| astext(shape) | name |
+——————————–+——————————-+
| POINT(-122.3890954 37.6145378) | Tram stop:Terminal A |
| POINT(-122.3899 37.6165902) | Tram stop:Terminal G |
| POINT(-122.3883973 37.6150806) | Fast Food Restaurant |
| POINT(-122.388929 37.6164584) | Restaurant:Ebisu |
| POINT(-122.3885347 37.6138365) | Fast Food Restaurant:Firewood |
| POINT(-122.38893 37.6132399) | Cafe:Amoura Café |
| POINT(-122.3894594 37.6129537) | Currency exchange |
| POINT(-122.39197849 37.614026) | Parking:Garage A |
| POINT(-122.3919031 37.6138567) | Tram stop:Garage A |
| POINT(-122.389176 37.612886) | Public telephone |
+——————————–+——————————-+
10 rows in set (0.02 sec)
mysql> explain select astext(shape), name from waypoints
where st_within(shape, envelope(linestring(point(@rlon1, @rlat1), point(@rlon2, @rlat2))))
order by st_distance(point(@lon, @lat), shape) limit 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: waypoints
type: range
possible_keys: SHAPE
key: SHAPE
key_len: 34
ref: NULL
rows: 430
Extra: Using where; Using filesort
1 row in set (0.00 sec)As we can see from the explain it will use the spatial key on SHAPE and will only scan 430 rows, rather than millions of POIs.The query does not show the exact distance (this may be ok if we only need to output the points on the map).  If we need to show the distance we can use the harvesine formula to calculate that. For example we can create the following stored function to implement the calculations:create DEFINER = CURRENT_USER function harvesine (lat1 double, lon1 double, lat2 double, lon2 double) returns double
return 3956 * 2 * ASIN(SQRT(POWER(SIN((lat1 – abs(lat2)) * pi()/180 / 2), 2)
+ COS(abs(lat1) * pi()/180 ) * COS(abs(lat2) * pi()/180) * POWER(SIN((lon1 – lon2) * pi()/180 / 2), 2) )) ;And then use it for both order by and to displaying the distance. This query will also filter by “coffee”:mysql> select harvesine(y(shape), x(shape), @lat, @lon ) as dist, name from waypoints
where st_within(shape, envelope(linestring(point(@rlon1, @rlat1), point(@rlon2, @rlat2))))
and name like ‘%coffee%’ 
order by dist limit 10;
+——————-+———————————-+
| dist | name |
+——————-+———————————-+
| 3.462439728799387 | Cafe:Peet’s Coffee |
| 8.907725074619638 | Cafe:Nervous Dog Coffee |
| 9.169043718528133 | Cafe:Peet’s Coffee & Tea |
| 9.252659680688794 | Cafe:Martha and Bros Coffee |
| 9.492498547771854 | Cafe:Manor Coffee Shop |
| 9.559275248726559 | Cafe:Dynamo Donut & Coffee |
| 9.57775126039776 | Cafe:Starbucks Coffee |
| 9.585378425394556 | Cafe:Muddy’s Coffeehouse |
| 9.66247951599322 | Cafe:Martha and Bros. Coffee Co. |
| 9.671254753804767 | Cafe:Starbucks Coffee |
+——————-+———————————-+
10 rows in set (0.02 sec)ConclusionMySQL 5.6 implements an additional set of functions that can help create geo-enabled applications with MySQL. Storing polygons boundaries (ZIP code boundaries for example) is efficient and the new spatial functions (st_within, st_contains, etc) will produce correct results and will use spatial (rtree) indexes (for MyISAM tables only). The OpenGIS standard is very common and it is easy to obtain the data in this format or use the standard application which can “talk” this language.Unfortunately, st_distance function is not very usable for calculating distance between 2 points on Earth and it does not use an index. In this case it is still more feasible to calculate distances manually using the harvesine formula. Hopefully this will be fixed in the next mysql release.There are also some other limitations, for example st_union() function only supports 2 arguments and does not support an array, so it can’t be used in a queries like “select st_union(geom) from zipcodes group by state”.LinksNew spatial functions in MySQL 5.6Harvesine formula in WikipediaMy 2006 MySQL User Conference talk: geo search in MySQL and how to effectively calculate distance between 2 points in MySQL using harvesine formulaHenric Ingo compares the sptatial functions in MySQL. MariaDB, Postgress and MongoDB: talk at Percona Live conference, blog postOpensource POI database downloadGoogle maps API: I’ve used it to generate pictures for the examples in this postDistance between to points on Earth: online distance calculatorAnd finally, let me know in the comments how you use MySQL for geo enabled applications. In my next post I will talk more about basics of the MySQL geo spatial extension as well as Sphinx Search‘s implementation of the Geospatial functions.The post Using the new spatial functions in MySQL 5.6 for geo-enabled applications appeared first on MySQL Performance Blog.
via Planet MySQL
Using the new spatial functions in MySQL 5.6 for geo-enabled applications

If you’re studying math or science, you are probably pretty familiar with Wolfram Alpha as a tool for figuring out complicated equations. That makes it a pretty good tool for cheating, but not necessarily for learning. Today, the Wolfram Alpha team is launching a new service for learners, the Wolfram Problem Generator, that turns the "computational knowledge engine" on its head.
via TechCrunch
Wolfram Alpha Launches Problem Generator To Help Students Learn Math

Whether you’re learning to code or are already an experienced programmer, this GitHub repository is an incredible resource of free programming books.

Victor Felder updated this Stack Overflow list with new and corrected links and shared it over on GitHub for collaborative updating. You’ll find books on professional development, specific platforms like Android and Oracle Server, and about 80 programming languages. There are also lists in other languages.

Definitely worth a look for your continuing coding education. There’s nothing quite like free books!

Free Programming Books | GitHub via ITworld

Photo by Linda N..

via Lifehacker
Grab Over 500 Free Programming Books from GitHub