How young is too young … for WordCamp?

April 18, 2012 By 6 Comments

WordCamp NYC

I was thinking about attending WordCamp NYC 2012 this coming June, and I thought, “Hey, Charlie has a WordPress blog, maybe she’d like to attend, too.”

I mentioned this to Samantha, and it brought up the question: Is she too young to attend? I’m not so sure. Charlie’s quite bright for her age, she’s occasionally shown an interest in blogging, and seeing the wide range of possibility by meeting and interacting with other bloggers could give her new ideas, inspire her, generate additional interest, etc.

What say you, blogosphere? Are there likely to be too many age-inappropriate topics discussed within earshot? Will she just be ignored and written off as too young to interact with, and therefore be bored and discouraged?

Filed Under: Family, Geeking out, WordPress 6 Comments

I may not have the plumbing, but I have a brain

February 10, 2012 By 2 Comments

Especially in election years, the following cliché is often trotted around in its many forms:

“If you don’t have a vagina, don’t tell someone else what to do with theirs.”

While I absolutely agree with this statement’s underlying principles, as I’m a huge fan of “personal liberty” in most of its forms, I think the cliché is so flawed, it might actually hurt the argument it’s trying to win.

For example, take the following form of the cliché that follows the same approach:

“If you don’t have children, don’t tell someone else how to raise theirs.”

This might sound reasonable at first, but let me elaborate it to make it clearer:

“Just because one does not have children, they couldn’t possibly–shouldn’t ever–be able to describe what child abuse is, and try to prevent others from committing it.”

If you agree with the very last statement, you’re wrong and are no longer intellectually qualified to discuss this matter. You can stop reading now, thanks for trying, at least.

…

Often, that’s how I feel when folks use the “if you have no vagina, you have no basis to prevent others from using theirs however they see fit” argument. It makes for a good sound bite, but it’s wrong, and it’s hard to actually win an argument when you’re actually wrong.

Maybe you still disagree. That’s fine. Assume for a moment that you’re actually right, and as a consequence, men become totally silent on the matter of women’s health. Are you trying to really convince me that there aren’t women out there who are pro-life or anti-abortion? Just because they have the plumbing, does that give them the right to tell other women what to do with theirs? Does it make them right? Should women listen to them?

Of course not. What plumbing you have is not what qualifies you as having the “right” to tell someone what to do with theirs.

We mutually agree on rules and establish laws in order to maintain order. Some folks try to abuse the system to enforce their personal sense of ethics and morality, and that’s unfortunate.

In America, we have rules and laws around rape. “If you don’t have a penis, don’t tell me what to do with mine” is clearly a ridiculous defense of rape, but “if you don’t have a vagina, don’t tell me what to do with mine” isn’t a ridiculous defense of abortion? See what I did there?

If you want to argue in favor of personal liberty, and I think every person who enjoys any sense of freedom should do so, if you want to ever win, you must stop arguing using a framework that can never win–one that’s clearly flawed and wrong.

Filed Under: Piss and moan (Rants) 2 Comments

Oh, Starbucks, you really know how to get my attention!

January 18, 2012 By 5 Comments

Anyone who knows me should understand why this ad on the New York Times website home page caught my attention …

Starbucks blonde roast

(Zoom! Enhance!)

Starbucks Blonde Roast

I’m sure it tastes like garbage like all other Starbucks coffee, but it did get my attention.

Reminds me of that old cliché, “You can’t polish a sneaker.” Even great marketing can’t sell a bad product.

Filed Under: Marketing, Observations 5 Comments

2011 In Review, Part 1

December 31, 2011 By Leave a Comment

On the cusp between 2011 and 2012, I decided to take advantage of the much-maligned Facebook Timeline feature and briefly summarize some highlights from the past year.

January

Ted Williams, the man with the &dquot;golden radio voice&dquot;

January brought us the great story of Ted Williams, a homeless man with that deep radio-personality voice. It’s the kind of feel-good story that was a perfect way to start out the year. Or, at least try to …

Sadly, we learn of Bill Zeller‘s suicide, before which he posts an elaborate suicide note. I could write volumes on how I feel about this, but I’ll just leave well enough alone.

My friend Ian visited us in January, and his Prius wouldn’t start when he went to leave. I can now say that I’ve jump-started a Prius. With all those batteries, you’d think Toyota would have designed it to never need a jump start, right? Wrong.

I discovered that the Mobile Safari browser limits each open tab to 5 MB of memory.

In a fit of despair, I channeled my stress into a redesign of my blog.

Working briefly with Logan Zanelli, we launched Johnny B. Truant‘s new blog design.

Discovered an incredible YouTube video of Phish’s Meatstick from New Year’s 2010:

Egypt packed up its toys and went home, disconnecting the whole country from the Internet.

The North American blizzard dropped so much snow on us, it was the snowpocalypse.

I was able to get my email inbox down to 1,685 messages.

We tried to introduce the girls to Dungeons & Dragons. They both really seemed to like it.

February

Suzie turned 8, and Charlie turned 11. Both of them celebrate their birthday in February. This makes for a very hectic month, to say the least.

I was introduced to Van Canto’s version of Master of Puppets. A cappella heavy metal. Two great tastes that taste great together …

A brilliant bank robbery was committed, where the robber’s escape plan involved blending into a crowd of construction workers, crowdsourced from Craigslist, and the getaway vehicle was an inner-tube.

Through some friends-of-friends, I started doing some consulting work for BloomAvenue.com (which is now no longer). Later in the year, this would transform into me working from FromYouFlowers.com.

March

Good ol’ Charlie Sheen grabs a few minutes of fame with his “goddesses.”

Goodnight Dune

Julia Yu gave us Goodnight Dune, a fantastic parody of the age-old children’s classic of similar name.

The world reeled as Japan got pwnt by a tremendous earthquake and tsunami. The catastrophic event trashed the Fukushima nuclear power plants, and held the world’s attention with radiation scares.

Apple launched the iPad 2 tablet this month.

Mazda recalls a bunch of Mazda6′s because of some spiders nesting in its parts.

Blair River, the spokesman for the Heart Attack Grill, ironically dies from pneumonia, at the young age of 29.

Taylor Mali’s incredibly powerful poetry about the importance of teachers makes the rounds on the Internet, again:

The Passaic, Morris and Essex Counties were ravaged by intense flooding. Governor Chris Christie declared a state of emergency. Unfortunately, this won’t be the last time the area floods, this year.

Charlie and Suzie become “mages” at MagiQuest, starting with the one at the Funplex in East Hanover, NJ.

Facebook quietly starts working on Facebook Deals, most likely in response to Groupon’s tremendous success.

Facebook Deals

Lady Gaga reminds us that she was Born This Way. Matter of fact, we all are. Good to remind ourselves of this, once in a while.

My friend Maya returns to the United States, and I get to see her again after years since the last visit. It’s nice to catch up with old friends, even if it’s bittersweet.

Google finally launches their latest entry into the social web space with “Google +1.”

RSA, the company that’s well-known for creating one-time-password security fobs, gets hacked. This story will not end well …

April

Eric Whitacre leverages technology to create his “Virtual Choir 2.0.” This is the future I want to live in.

I published a HOWTO on tethering an Android phone via USB to MacOS X.

Epsilon has a data breach where millions of email addresses were acquired.

I did some DIY repair on my Mitsubishi Lancer, replacing the O2 sensor, so that the car would pass NJ state inspection.

We saw the US government nearly shut down because our politicians can’t agree on how to best overspend the American taxpayer dollar.

Steve Buscemi eyes

The eyes might be the windows to the soul, but Photoshopping Steve Buscemi’s eyes onto other people is a window into insanity. Thanks, Internet … everyone needs a new nightmare, every now and then.

My college alma mater, Rutgers, demonstrates that the “RU Screw” is still alive and well, by paying Snooki some $32k to perform.

In the “seriously, this happened?” department, the Med Peds Clinic of Fort Collins, Colorado, employed someone who thought it’d be a good idea to split a flu vaccine dose, by using half of the syringe and swapping out the needle. No, you can’t make this stuff up.

Just when I thought I lived in a sleepy little suburban town, tragedy strikes when someone brutally beats Frank the barber who dies from his injuries, right on Main Street in Bloomingdale.

Charlie's first communion

The four of us spent a week down in Atlantic City, at the Wyndham Skyline Tower.

Charlie has made her first communion at church this year, on Palm Sunday.

SETI stops operating the Allen Telescope Array. Millions of geeks cry out as they try to figure out what to do with their unused CPU cycles.

Adam Mansbach’s faux children’s book “Go the F— to Sleep” gets some press.

Sony’s PlayStation Network (PSN) gets hacked, officially. This won’t be the last time …

May

Just in case you forgot what the American “War on Terror” was all about, Osama bin Laden is finally dead. Little did Sohaib Athar realize at the time, but he live-tweeted the raid.

I finally broke down and bought myself a white iPad 2 (16 GB, Wi-Fi).

Nicktoons threatens to destroy more of my fond childhood memories, looting the corpse of the Voltron legacy.

The Library of Congress makes a wealth of old recordings available online.

Microsoft begins the process of acquiring Skype for $8.5 billion in cash. No shortage of “Sky.NET” jokes at this point.

Google launches its Music Beta service. Is it really the right time for a cloud-based music service?

We get to watch the FCC Commissioner Meredith Attwell Baker approve Comcast’s purchase of NBC, then go work for Comcast. No, I’m not kidding …

It’s finally official: I’m now working for FromYouFlowers.com as a Senior Solutions Architect.

His Holiness the Dalai Lama comes to Newark, New Jersey, and tries to tell Americans that we need to teach our kids ethics.

Watermelon farmers in China get screwed when they overdosed their crops with growth chemicals, resulting in exploding watermelons.

Sony’s PSN–you know, the Password Sharing Network–gets hacked again.

Macho Man vs Jesus

Harold Camping predicts the end of the world on May 21st, 2011. Sadly, the Macho Man Randy Savage dies on the 20th, just in time to stop Jesus from coming back to rapture us all. OHHHH, YEAHHHH!

Apparently some “faulty software” released 450 of the most dangerous inmates from California prisons. And people try and make fun of me for living in New Jersey … ??!

One of America’s largest defense contractors, Lockheed Martin, gets hacked. Allegedly, the breach may have involved the recently compromised RSA security technology.

The Ridgewood Cavaliers of Harmony, the barbershop society chapter I belong to, had a sing-out at Van Neste Square in Ridgewood, New Jersey, for Memorial Day.

June

Samuel L. Jackson, the quintessential Bad Mother F@#!ker, does the audiobook reading of “Go the F— to Sleep.” Naturally.

The girls have their Performing Arts Academy dance recital at the Shea Center on the William Paterson University campus.

Congressman Anthony Weiner resigns after a little sexting scandal, where he accidentally tweeted a link to a picture of his junk in his underwear. D’oh.

Neil Patrick Harris performs the opening number for the 2011 Tony Awards, pointing out that “Broadway’s not just for gays anymore.”

Web designers explore how repeating intervals that follow prime numbers in nature, specifically the cicada, can yield interesting visual effects, as well.

RIM’s stock drops in value by 20% overnight.

Michael Vick comes to Butler, New Jersey, to sign autographs, and winds up being greeted by protesters.

Google starts rolling out Google+.

Weird Al Yankovic releases his Lady Gaga parody, “Perform This Way.”

Britney Spears reminds us that she’s still a hottie, even after poppin’ out a baby, with her video for “I Wanna Go.” Hell, even if you don’t care for Britney, Guillermo Diaz who plays Scarface in Half Baked, appears in the video.

The state of Georgia enacts some anti-illegal-immigrant law, which totally screws the farmers in Georgia who can’t find enough affordable labor to harvest crops. If it wasn’t clear that illegal immigrants aren’t the problem before, it sure ought to be now.

Danous Estenor performs a heroic act of total badassery by lifting up a car, freeing a man who was trapped underneath.

Gary Foster made his alma mater Rutgers proud by embezzling some $19.2 million dollars. In America, you can only get away with it if you’re white …

***

Okay, it’s almost midnight … and I’m only up to July, so I’m going to post this now. Maybe I’ll get to do the second half of 2011, tomorrow.

Happy New Years, everyone!

Filed Under: Dossy, Dossy and more Dossy!, Observations Leave a Comment

MySQL replication connection error on non-standard ports

November 9, 2011 By Leave a Comment

or, “Why SELinux was the worst thing to happen to Linux since LinuxThreads.”

Setting up MySQL master-slave replication is pretty straightforward. I’ve done it plenty of times. Similarly, running multiple MySQL server instances on the same host using mysqld_multi, really painless. Give each instance its own server-id and port to listen on, and that’s it.

In this case, the server that was being replicated was listening on port 3307. I could connect to it just fine using the mysql client using “-P 3307” as the replication user. It was clearly possible for the two machines to communicate over TCP port 3307. But, then, why was the MySQL slave I/O thread failing to connect to the master?

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: x.x.x.x
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 60
...
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
...
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'repl@x.x.x.x:3307' - retry-time: 60  retries: 86400
...
1 row in set (0.00 sec)

Makes no sense, right? Right! Unless, of course, you’re on a machine–in this case, Red Hat Enterprise Linux 5.7–that has ever-so-helpfully screwed you by installing SELinux policies that restrict what ports mysqld can use, and has SELinux enabled by default.

How can you tell if SELinux is enabled and its policies are being enforced?

# grep 1 /selinux/enforce
1

If you get no output, then SELinux isn’t being enforced. But, if it is enabled, here’s how you can tell if mysqld‘s ability to make network connections is being restricted by a policy:

# semanage port -l | grep 3306
mysqld_port_t                  tcp      1186, 3306

You can tell if this is happening if you look in /var/log/audit/audit.log and see an entry like this:

type=AVC msg=audit(1320869122.773:56478): avc: denied { name_connect } for pid=2990 comm=”mysqld” dest=3307 scontext=user_u:system_r:mysqld_t:s0 tcontext=system_u:object_r:port_t:s0 tclass=tcp_socket

(I bolded the elements to keep an eye out for.)

So, how do we remedy the situation? Personally, my first recommendation is to turn SELinux off. It is always more trouble than it’s worth. I’ve never heard of a single anecdote where it improved security or otherwise did anything useful.

Disable SELinux by editing /etc/selinux/config and change the line that reads “SELINUX=enforcing” to “SELINUX=disabled” and then reboot the system. There’s some caveats around disabling SELinux with respect to re-enabling it in the future, but you’re never going to do that if you have a choice, so who cares.

If you absolutely must leave SELinux enabled, because you’re actually burning in Hell and being tortured by some unreasonable and irrational organizational policy that requires it, then this is how you can add ports to the list of ports that mysqld will be allowed to use:

# semanage port -a -t mysqld_port_t -p tcp 3307

This command might take a few seconds to complete, and won’t generate any output if successful. You can check to see if it did what you wanted using the same command we used before, but this time the newly added port should be in the list, as well:

# semanage port -l | grep 3306
mysqld_port_t                  tcp      3307, 1186, 3306

To make sure this really did fix the problem, lets look at the MySQL replication status, again:

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: x.x.x.x
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 60
...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...
                Last_IO_Errno: 0
                Last_IO_Error:
...
1 row in set (0.00 sec)

And there you have it, replication configured on TCP port 3307, connected successfully, waiting for the master to send it updates, like it should be.

Hopefully, this has helped save you time, aggravation and much head-scratching as you tried to figure out why MySQL replication wasn’t working on a port other than 3306.

Got any other good gotcha stories like this? Share them in the comments below.

Filed Under: Linux, MySQL Leave a Comment

Business meals for self-employed, work-from-home consultants

November 2, 2011 By 4 Comments

I’d like to try and crowdsource an answer to a tax question I’ve had over the years but never felt like I had a good answer to:

IRS Topic 511, Business Travel Expenses, describes qualifying expenses while travelling. It is written with a very employee-centric tone, which isn’t particularly helpful for me as a self-employed consultant who works primarily out of his own home office.

1) Is New York City “close enough” to New Jersey to consider it the same “general area” or does travelling to New York City count as valid business travel in my case?

Suppose I have a client (one of many) for whom I travel, once a week, from my home office in New Jersey to their office in New York City. Since I earn the majority of my income working out of my home office in New Jersey, I think it’s appropriate to consider New Jersey my “tax home.” But, the tax topic is worded using the phrase “general area” which is vague. How is this supposed to be interpreted?

2) What does the IRS consider an “assignment”?

Topic 511 refers to temporary and indefinite “assignments” and talks about durations less and greater than one year. For the self-employed consultant, are clients considered “assignments”? Are individual projects for a client an “assignment”? I can’t find a definition for “assignment” in the IRS Tax Glossary.

Most clients of mine are of the “indefinite” kind, ones I intend to work with for at least a year or more. Individual projects for my clients generally span anywhere from less than a single day up to several months, but never a year or more.

I think this qualifies my one-day-a-week on-site visits as “short term travel” and therefore my travel expenses are deductible, but given sufficiently loose interpretations in the IRS’s favor, it makes me nervous. How is this supposed to be interpreted?

Naturally, I can arrive at my own conclusions and interpretations of what this all means. What I’d like to know is if anyone here has dealt with the IRS with respect to these two issues, and what their official stance is.

I suppose I could try and call the IRS and try to explain this to them and get an answer, but I’m not too keen on getting a verbal one-off interpretation by some call center representative. Getting this in writing is somewhat important, for obvious reasons.

Filed Under: Business, Death and Taxes 4 Comments

November Rain? How about October Snow …

October 30, 2011 By Leave a Comment

The northeast US got slapped with a nice blizzard this last week of October 2011, right on Halloween weekend. There are still green leaves (!) on the trees, and the weight of the snow has taken out many tree limbs and wires, resulting in Governor Christie declaring a statewide state of emergency. Millions of people across New England are without power, up to 600,000 of them in New Jersey alone.

Making the best out of the situation, our girls got bundled up and went outside to play in the snow. Here’s a short video I took of them:

Filed Under: Family, Observations Leave a Comment

The Oxford comma, let me teach you it

October 7, 2011 By 2 Comments

[ I originally wrote this as a comment on a friend's LJ in response to his reposting the below image, but wanted to also post the comment on my blog since it's a subject I feel strongly about. ]

With the Oxford comma: we invited the strippers, jfk, and stalin. Without the Oxford comma: we invited the strippers, jfk and stalin.

I personally, dislike, the Oxford comma, and, prefer, the Shatner comma.
:-)

Seriously, though, most of the examples folks use when illustrating the value of “the Oxford comma” follows this pattern:

“… Noun, ProperNoun and ProperNoun.”

In this specific case, I agree, the series (Oxford) comma is necessary. However, if the last three of the list elements are proper nouns, it’s not necessary:

“I went to England, Spain and France.”

Or, if none of the last three elements in the list are proper nouns:

“At the grocery store, please buy carrots, mangos and limes.”

What’s worse is when folks use the serial (Oxford) comma before a conjunction when NOT enumerating a list, like:

“I went to the store, and bought a book.” FUCK NO WRONG WRONG WRONG. This is NOT an Oxford comma, it’s called WRONG WRONGITY WRONG WRONG.

*neck-twitch*

Filed Under: Linguistics, Piss and moan (Rants) 2 Comments

DM Weil art gallery in New Paltz, NY

September 25, 2011 By

My wife and I visited DM Weil’s gallery as part of our anniversary weekend away, somewhat randomly, looking for things to do in the Hudson River Valley area on TripAdvisor.

DM Weil's gallery

While we enjoy the finer things in life, we’re not art snobs. Often, the art gallery experience is dull — sure, there’s some great art, or at least that’s what everyone calls it — but it’s totally different at DM Weil. Ken, the gallery manager, makes you really feel welcome and will gladly answer your questions about the art, the products available, and so on. Donna shared her story with us about her art, that gave us a real connection to her gift and talent on display.

The art itself covers a broad range — busy, energetic, curious, transporting, nuanced, whimsy, vibrant. The gallery space is wonderful, airy, bright and crisp. It really gives you the opportunity to get lost in the art.

In comparison to upscale New York City galleries, DM Weil’s gallery is beautiful, peaceful and relaxed. As guests, you feel so welcome and the hosts are generous, offering refreshments and conversation.

We will certainly be back to see new works on display in the future. Even if you don’t think you care for art, if you’re in the area, and you can keep an open mind, definitely stop in when the gallery is open. Beyond the paints and canvas is a wonderful person who shares her time and talent with you, with everyone.

Filed Under: Arts and Crafts

MySQL Geo Distance Code and Samples

September 22, 2011 By 1 Comment

Over the years, I’ve implemented and re-implemented the haversine formula whenever I’ve needed to compute great-circle distances, which is pretty common when you’re doing geospatial proximity searches, e.g., “What’s near me?” or “How far are these two locations from each other?”

For an implementation of the formula in MySQL, Alexander Rubin’s presentation is quite useful, and is what I’ve based the following code on this time around.

Hopefully, rather than re-implementing this yet again the next time I need it, I’ll find my own blog entry on it and just reuse this code. I keep forgetting where I’ve stashed the most recent copy, which is why I keep ending up re-implementing this every time I’ve needed it.

Here’s the code, tested on MySQL 5.1.41:

DELIMITER $$

DROP FUNCTION IF EXISTS geodist $$
CREATE FUNCTION geodist (
  src_lat DECIMAL(9,6), src_lon DECIMAL(9,6),
  dst_lat DECIMAL(9,6), dst_lon DECIMAL(9,6)
) RETURNS DECIMAL(6,2) DETERMINISTIC
BEGIN
  SET @dist := 3956 * 2 * ASIN(SQRT(
      POWER(SIN((src_lat - ABS(dst_lat)) * PI()/180 / 2), 2) +
      COS(src_lat * PI()/180) *
      COS(ABS(dst_lat) * PI()/180) *
      POWER(SIN((src_lon - dst_lon) * PI()/180 / 2), 2)
    ));
  RETURN @dist;
END $$

DROP FUNCTION IF EXISTS geodist_pt $$
CREATE FUNCTION geodist_pt (src POINT, dst POINT)
RETURNS DECIMAL(6,2) DETERMINISTIC
BEGIN
  RETURN geodist(Y(src), X(src), Y(dst), X(dst));
END $$

DROP PROCEDURE IF EXISTS geobox $$
CREATE PROCEDURE geobox (
  IN src_lat DECIMAL(9,6), IN src_lon DECIMAL(9,6), IN dist DECIMAL(6,2),
  OUT lat_top DECIMAL(9,6), OUT lon_lft DECIMAL(9,6),
  OUT lat_bot DECIMAL(9,6), OUT lon_rgt DECIMAL(9,6)
) DETERMINISTIC
BEGIN
  /*
   * src_lat, src_lon -> center point of bounding box
   * dist -> distance from center in miles
   * lat_top, lon_lft -> top left corner of bounding box
   * lat_bot, lon_rgt -> bottom right corner of bounding box
   */
  SET lat_top := src_lat - (dist / 69);
  SET lon_lft := src_lon - (dist / ABS(COS(RADIANS(src_lat)) * 69));
  SET lat_bot := src_lat + (dist / 69);
  SET lon_rgt := src_lon + (dist / ABS(COS(RADIANS(src_lat)) * 69));
END $$

DROP PROCEDURE IF EXISTS geobox_pt $$
CREATE PROCEDURE geobox_pt (
  IN pt POINT, IN dist DECIMAL(6,2),
  OUT top_lft POINT, OUT bot_rgt POINT
) DETERMINISTIC
BEGIN
  /*
   * pt -> center point of bounding box
   * dist -> distance from center in miles
   * top_lft -> top left corner of bounding box
   * bot_rgt -> bottom right corner of bounding box
   */
  CALL geobox(Y(pt), X(pt), dist, @lat_top, @lon_lft, @lat_bot, @lon_rgt);
  SET top_lft := POINT(@lon_lft, @lat_top);
  SET bot_rgt := POINT(@lon_rgt, @lat_bot);
END $$

DROP PROCEDURE IF EXISTS geobox_pt_bbox $$
CREATE PROCEDURE geobox_pt_bbox (
  IN pt POINT, IN dist DECIMAL(6,2), OUT bbox POLYGON
) DETERMINISTIC
BEGIN
  /*
   * pt -> center point of bounding box
   * dist -> distance from center in miles
   * bbox -> bounding box, dist miles from pt
   */
  CALL geobox_pt(pt, dist, @top_lft, @bot_rgt);
  SET bbox := Envelope(LineString(@top_lft, @bot_rgt));
END $$

DELIMITER ;

And, here’s examples on how to use the above function and procedures:

SELECT @src := pt FROM exp_geo
WHERE postal_code = '07405' AND city = 'Butler';

CALL GEOBOX_PT(@src, 10.0, @top_lft, @bot_rgt);
SELECT Y(@top_lft) AS lat_top, X(@top_lft) AS lon_lft,
       Y(@bot_rgt) AS lat_bot, X(@bot_rgt) AS lon_rgt;

CALL GEOBOX_PT_BBOX(@src, 10.0, @bbox);
SELECT AsWKT(@bbox);

-- Assuming INDEX on (lat, lon) and SPATIAL INDEX on (pt) ...

-- Fast:
SELECT g.postal_code, g.city, g.state,
       GEODIST(Y(@src), X(@src), lat, lon) AS dist
FROM exp_geo g
WHERE lat BETWEEN Y(@top_lft) AND Y(@bot_rgt)
AND lon BETWEEN X(@top_lft) AND X(@bot_rgt)
HAVING dist < 5.0
ORDER BY dist;

-- Also fast:
SELECT g.postal_code, g.city, g.state, GEODIST_PT(@src, g.pt) AS dist
FROM exp_geo g
WHERE lat BETWEEN Y(@top_lft) AND Y(@bot_rgt)
AND lon BETWEEN X(@top_lft) AND X(@bot_rgt)
HAVING dist < 5.0
ORDER BY dist;

-- Slow:
SELECT g.postal_code, g.city, g.state, GEODIST_PT(@src, g.pt) AS dist
FROM exp_geo g
WHERE Y(pt) BETWEEN Y(@top_lft) AND Y(@bot_rgt)
AND X(pt) BETWEEN X(@top_lft) AND X(@bot_rgt)
HAVING dist < 5.0
ORDER BY dist;

-- Also slow:
SELECT g.postal_code, g.city, g.state, GEODIST_PT(@src, g.pt) AS dist
FROM exp_geo g
WHERE MBRContains(@bbox, pt) = 1
HAVING dist < 5.0
ORDER BY dist;

Normally, I try to explain the stuff I post, but this time I’m doing it for somewhat selfish reasons — so I don’t lose this work yet again. I’m just throwing this up here so Google will index it, so I can find it the next time I need it.

I suppose if you read this and have questions, please ask them in the comments below. I’ll do my best to answer, if I can.

Filed Under: MySQL, Open Source 1 Comment
« Older Posts