January 2012
M T W T F S S
« Aug    
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

Other Blog Entries I Like

Hello world! – Again

Welcome to the new Earththing blog. New Hosting provider/new look etc. This will still be changing as it all settles in.

Who knew that changing hosting providers would be so difficult? Well I had a pretty good idea actually but as it turns out that if you are not REALLY picky, it can actually be quite easy. (Or so I hope as I am still working out kinks.) The hardest part trying to figure out which of the MANY guides out there will work for me. I tried quite a few of them and gotten close… but usually there was a show stopper that made me start over.

I migrated hosting of my wordPress blog from GoDaddy to DreamHost. Here is basically what I did that finally worked well enough.

  1. Set up site viewing before switching over the DNS with these instructions.
  2. Then I installed WordPress for my site on DreamHost
  3. Go to my original site and export everything in the Tool -> Export menu of the wp-admin page.
  4. Do initial set up of WordPress on new site.
  5. On new site Install the WordPress Importer plug-in and then use the Import option in the “Tools” menu of the wp-admin page. Be sure to select download and import file attachments.
  6. This is the point I am actually at while writing this post. The blog is up. I can view it with it’s temporary location, no errors, images are showing up, etc. Best attempt so far.
  7. Next steps will be to do a few more fiddly adjustments
  8. Switch over the main DNS entries to DreamHost and remove the mirroring/temporary site address
  9. Continue to make adjustments while writing brilliant and witty blog posts that will educate, entertain, and inspire you.

So there you have it! Another guide to migrating your WordPress site to another hosting provider. I am assuming you have had some experience managing your WordPress blog before attempting to migrate it so I did not go into excruciating detail. I also assume that this may not work for all versions of WordPress.

Have you migrated hosting providers? Let us know in the comments your experiences.

What type of crime gets reported in Seattle?

Have you wondered where the bad areas of town are? Do you think that the neighborhood that you live or work  in is safe? If you live in the Seattle area, now you can check and see. The data is interesting enough, but not easy to work with. I’ll tell you the story of how I did this after the graphics.

 

This dataset was a bit challenging to work with. The city of Seattle was kind enough to provide the data but the shape files showing the boundaries of the police beats came in 2 formats, neither of which Tableau can use. The shape file is reasonably easy to convert for use in Tableau. I found the instructions here and it seemed to work just fine, except when brought into Tableau the features did not line up. It was not using a latitude and longitude format I have seen before. Even after manipulating the numbers with math to get it close to line up I could see that it was distorted. So I had to start over with the KMZ file format. I looked and looked for a converter but could only find ones that converted a KML file.  But I also discovered a KMZ file is a ZIP compressed container containing a KML file. So I unzipped the KMZ to KML file and tried out a few KML to shape file converters. the one I ended up using was an on-line converter that apparently converts KMZ files as well *SIGH* still I went through all that effort, so I used the KML that I extracted. :)   Converting the resulting shape file I finally had something I could use in Tableau! I combined the shape data and the crime data in Tableau Public to create the viz that you can see above. I wanted to link the shapes and graphics so if you click on a detail you can see it filtered in each graph but to join my data in a way that would allow me to do that, creates more rows then Tableau Public will allow you. Let me know if you try this your self and get it to work either in Tableau Public of the full version of Tableau.

Earththing’s Year in Review

Inspired by Nicholas Felton and his Felton Reports I decided last year to try to track some of my own statistics through out the year. I used many different methods of gathering data such as DAYTUM and took advantage of what information could be gleaned from other services like toutapp to provide some visualizations.

 

Let’s start with email:

I had to deal with over 4,509 personal emails in 2011. I tried to delete most of the junk mail I received before they were counted: deleting old and new ones I did not need and unsubscribing from various newsletters. What managed to make it past the purge was then counted with toutapp: (It looks much better on their site. Check it out for your self!)



Email Overview

 
image
I sent 83% less email than I received
  • I sent the most emails in December
  • Thursday is my busiest day of sending emails through the week
  • I sent the most emails between the hours of 11AM-12PM
image

I Responded to 8% of the emails received

image

27% Emails sent, received replies.

image Monthly Average:
54 Emails Sent
322 Emails Received
image

Weekly Average:

12 Emails Sent

74 Emails Received

image

Email by the day

Most Sent around 11AM

Most Received around 2PM

   
Now let’s move on to other stuff. I used DAYTUM to track.  

Miles Exercised:

276.9
  • Miles Walked: 178.8
  • Miles Ridden – Bicycle: 97.4
  • Miles Ran: 0.7
 
   

Floors climbed via Stairs:

22
   

Cups of Coffee:

628
   

Pager Pages:

1068

(But only had pager for first 5 Months of the year)

I am working on getting information from other websites, but need to figure out how with their API. As I gather, I’ll update this post or make it into a “page” on my blog.

Creating a Date Table in SQL

How do you create a table in SQL in your database that contains the many details about a particular date? I have encountered tables like this at various places I have worked and found then to be convenient  ways to do many date operations that otherwise would require some intricate manipulation of SQL date functions. These tables have several uses such as making date manipulation available to people who may not be experts in SQL, and allowing for some date details that may vary from one company to another like determining the fiscal year and standardizing on the ways dates are formatted.

I thought creating such a table would be an interesting exercise. Using what was available to me at the time, MS SQL, I have created a few date related tables, a view to display them, and an example of a T-SQL query that was made easier with the creation of the date table.

In the following code sections can all be put in a single query and ran at one time. I am not going to walk through the process I took to creating the table as there was much back and forth, I’ll just describe what I created.

To ease the re-running of the query so that tweaks and changes can be made I look for the existence of the tables and indexes I create and drop them if there are there, thus avoiding “already exists” error messages as I make modifications and re-run the query. (Please forgive any formatting quirks in the source code, I am trying to find a good source code formatting plug-in)

/******************************************

** Drop Table and index if already there **

******************************************/

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[dDateTable]') AND name = N'PK_Dates')

ALTER TABLE [dbo].[DateTable] DROP CONSTRAINT [PK_Dates]

GO

 

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND ID = OBJECT_ID('[dbo].[dDateTable]'))

DROP TABLE [dbo].[dDateTable]

GO

Then I created and populated the table. The starting date I used is the smallest date supported by the SQL Server’s datetime datatype. Why 1753? It is because by this time most of the world switched from the Julian to the Gregorian calendar. To calculate date prior to this, you would need to do the conversions based on the country that is your frame of reference. I found some of the details why written in a succinct way on the nixioncorp team blog.

/**********************

** Create date table **

**********************/

 

CREATE TABLE dbo.dDateTable ( -- Define table

    DateID            int NOT NULL IDENTITY(1, 1),

    [Date]            date NOT NULL,

    [Year]            smallint NOT NULL, 

    [MonthofYear]    tinyint NOT NULL,

    [DayofWeek]        tinyint NOT NULL,

    [DayofMonth]    tinyint NOT NULL,

    [DayofYear]        smallint NOT NULL,

    [Quarter]        tinyint NOT NULL,

    [WeekofYear]    tinyint NOT NULL,

--    [WeekofMonth]    tinyint NOT NULL, -- Still need to define (probably first partial week of month) 

 CONSTRAINT PK_Dates PRIMARY KEY CLUSTERED (DateID)

)

GO

 

/*******************

** Populate table **

*******************/

 

-- declare variables to hold the start and end date

DECLARE @StartDate date

DECLARE @EndDate date

 

-- assign values to the start date and end date we 

-- want our reports to cover (this should also take

-- into account any future reporting needs)

SET @StartDate = '01/01/1753'

SET @EndDate = '12/31/2020' 

 

-- using a while loop increment from the start date 

-- to the end date

DECLARE @LoopDate datetime

SET @LoopDate = @StartDate

 

WHILE @LoopDate <= @EndDate

BEGIN

 -- add a record into the date dimension table for this date

    INSERT INTO dDateTable VALUES(

        @LoopDate,

        Year(@LoopDate),

        Month(@LoopDate),

        datepart(weekday,@LoopDate),

        Day(@LoopDate),

        datepart(dayOfYear,@LoopDate),

        datepart(quarter,@LoopDate),

        datepart(week,@LoopDate)

        --'Week of Month'

    )

    

 -- increment the LoopDate by 1 day before

 -- we start the loop again

 SET @LoopDate = DateAdd(d, 1, @LoopDate)

END

GO

 

-- now we have inserted the data we can check how it appears in our table

SELECT TOP 100 * FROM dDateTable

GO

This is all fine and good, but there are other things that may be useful such as weekday and month names as well as the month abbreviations:

/***************************************************************************

Supporting Tables

****************************************************************************/

 

-- Drop Tables if they already exist

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND ID = OBJECT_ID('[dbo].[dWeekTable]'))

DROP TABLE [dbo].[dWeekTable]

GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND ID = OBJECT_ID('[dbo].[dMonthTable]'))

DROP TABLE [dbo].[dMonthTable]

GO

 

CREATE TABLE dbo.dWeekTable ( -- Define table

    [DayofWeek]    tinyint NOT NULL,

    [Day_Name]    varchar(10) NOT NULL

)

GO

INSERT INTO [dWeekTable] VALUES 

     (1, 'Sunday')

    ,(2, 'Monday')

    ,(3, 'Tuesday')

    ,(4, 'Wednesday')

    ,(5, 'Thursday')

    ,(6, 'Friday')

    ,(7, 'Saturday')

;

GO

 

CREATE TABLE dbo.dMonthTable ( -- Define table

      [MonthofYear]           tinyint NOT NULL,

      [Month_Name]            varchar(10) NOT NULL,

      [Month_Abrv]            varchar(3) NOT NULL

)

GO

INSERT INTO [dMonthTable] VALUES

       ( 1, 'January'     ,'Jan')

      ,( 2, 'Febuary'   ,'Feb')

      ,( 3, 'March'       ,'Mar')

      ,( 4, 'April'       ,'Apr')

      ,( 5, 'May'         ,'May')

      ,( 6, 'June'        ,'Jun')

      ,( 7, 'July'        ,'Jul')

      ,( 8, 'August'      ,'Aug')

      ,( 9, 'September' ,'Sep')

      ,(10, 'October'     ,'Oct')

      ,(11, 'November'  ,'Nov')

      ,(12, 'December'  ,'Dec')

;

GO

And now I create a view to tie it all together:

/***************************************************************************

Create View

****************************************************************************/

-- If view is already there then drop it

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vCalendar]'))

DROP VIEW [dbo].[vCalendar]

GO

 

-- Create view

CREATE VIEW [dbo].[vCalendar]

AS

SELECT d.[DateID]

      ,d.[Date]

      ,d.[Year]

      ,d.[MonthofYear]

      ,m.[Month_Name]

      ,m.[Month_Abrv]

      ,d.[DayofWeek]

      ,w.[Day_Name]

      ,d.[DayofMonth]

      ,d.[DayofYear]

      ,d.[Quarter]

      ,d.[WeekofYear]

  FROM [dbo].[dDateTable] d

  JOIN dbo.dMonthTable m on d.[MonthofYear] = m.[MonthofYear]

  JOIN dbo.dWeekTable w on d.[DayofWeek] = w.[DayofWeek]

GO

 

-- Check to see how the view looks

Select top 100 * from [dbo].[vCalendar]

Put all the above in a single query file and it should run on MS SQL. Other flavors of SQL should also work with possibly minor modifications.

 

Now as a demonstration of a query made easier because of this table consider the scenario of getting paid every 2 weeks on a Friday. Which months in the next few years would you have 3 paychecks a month?

-- *** Triple Paycheck Months ***

-- Assume that Aug 12th 2011 is the start payday. (Which happens to be dateID = 94456)

 

-- Drop if already there. NOTE: this is not needed when using temp tables in a stored procedure

IF  EXISTS (SELECT Table_Catalog, Table_Name FROM TEMPDB.information_schema.tables WHERE table_name like '%#2WKs%')

drop table #2WKs

GO

--

CREATE TABLE #2WKs (COL1 INT)

GO

DECLARE @loop int

SET @Loop = 94456 -- Startdate Aug 12th 2011

WHILE @Loop <= 96100 -- Some future date

BEGIN

insert into #2WKs values (@Loop)

SET @Loop = (@Loop+14)

END

select Month_Name,Year

      from dbo.vCalendar

      where DateID in (select * from #2WKs)

      group by Month_Name, Year

      having COUNT(*) > 2

 

--Possible adjustment to query: Use a table variable instead of a Temp table.

I have made the files available for download here.

Time for a good cry?

Listening to some music the other day I found my self getting a bit emotional. My response was to minimize the way the songs affected me and not let their effects to show. But not being a total hard case, started talking with the other guys in the office about it. Then I got curious, why do we cry? Here is what I found out…

First of all you are probably already aware that the tears are a lubricant for our eyes and help to protect them and keep them clean. Washing dirt and other irritants away the tears also have anti-bacterial properties that kill 90 to 95 percent of all bacteria within 5 to 10 min. So the tears are there to keep us healthy. Tears help the tissues around the eye to be moist, if they would dry out, we would not be able to see.

But there is another type of tears that are different then the ones that appear because of an irritant. The emotional tears also have a role to play. Interestingly they have a different chemical make up and have different results.

  • Crying helps to reduce stress – Crying helps to remove some hormones and chemicals in the body that are a produce of stress, holding back tears can increase the stress levels in your body.
  • Crying helps you feel better – Emotional crying helps to release endorphins that work to stabilize your mood and help us feel content. Holding back tears in an emotional situation will only prolong the suffering and make you feel worse.
  • Crying is also an effective form of communication, think about it, you may be oblivious to how some one is feeling until they start crying and then it becomes pretty clear. The act of emotional crying also help the people around them in instilling a sense of community and deepens the interest in one’s welfare

As a guy, I have wondered about my own ability to cry. The few times I feel like crying it is rare that I actually seem to do so more then tiny bit. I would then feel a bit guilty about it. However I found out that structurally men’s tear ducts are actually smaller than women’s, and that tears naturally drain from the eyes through a passage near the border of the lower eyelid. Tears streaming down the cheeks happen with an excess production of tears that the drainage system can not handle.

So there are many benefits to a good cry both in mental and physical health. So go ahead, cry it out. It will all be better soon.

———-

… In part because of the conversation I had with the guys in my office I have put together a collaborative play list on Spotify of Songs that make you cry. Feel free to contribute songs to the list but I do ask you to keep them clean and not to add songs with explicit lyrics. They can be sad or happy songs, but add them if they made you cry at some point.

And for more details, here are the sources of my research:

…and some other pages that all said pretty much the same thing.

Does data make you hungry?

data cake
Image by EpicGraphic

Pie-Pie Chart

Edward Tufte, August 19, 2009

Sitting is killing you!

I like having a standing desk at work. At my last few jobs I have gone out of my way to get one. Some places make it difficult but I think it is worth it.

Then I see this infographic. It speaks to me. Spread the word! Please companies of the world, don’t require your employees to jump through a bunch of hoops to get these desks. You will probably save the extra cost of the desk in time your workers are out due to medical needs. (To be honest  I don’t really know that, but check it out!)

Sitting is Killing You
Via: Medical Billing And Coding

My virtual dogs.

Hi! I’m Little-Thing, Earththing’s son. I want a blog about gaming, but first dad wants me to do a few guest posts. I enjoy a game called Minecraft.

A few recent up-dates are the following: dogs, new lettering for “MINECRAFT” at the top of the opening menu, labeling of blocks, allowing the making of new worlds ( I mean more than five-the classic number of worlds) & new blocks such as, beds and repeaters.

2011-04-11_16.07.32

My first dog.

2011-04-12_17.24.35

My 4 dogs.

2011-04-12_18.22.04

My five dogs.

2011-04-12_18.47.13

My five dogs again.

2011-04-12_19.21.04

My 8 dogs.

2011-04-12_19.32.35

My nine dogs.

 

I don’t have a pic of all my dogs, but I have 12 dogs so far.

The eyePad

You may remember my earlier post on one of Apple’s latest darlings the iPad (see: The New Apple iPad: Yea or Meh? ) My opinions have not changed much, but I kind of want one now… Still I can’t afford it so I made my own.

eyePad1eyePad2 Behold the eyePad!

It costs much less to manufacture then Apple’s version even more portable, no restrictions on the content placed in the eyePad, no approval needed for aps, etc. Granted it does not have all the glamor and styling of the iPad, but I made is and I like it.

You will have to admit there are few things that can’t be made better with the addition of googly eyes.

Bring out the Spork!

I am not sure what got this idea in my head, but I wanted to try out a spork. (or foon?) Not the cheap o’ plastic ones that you can get with some fast food lunches, but an actual serviceable one that you can really use. So I made my own.

spork2

AND I made enough for the whole family Smile (with an extra). My kids are delighted and my wife is tolerant of my project. so far I am the only one who has tried them out.

I went to Goodwill bought a few  spoons and cut away with my Dremel tool. The results, may not be the most attractive, but quite serviceable.

I have ideas for improvements and may be making a few more.