Tuesday, September 07, 2010

ve: massaging an amorphous pile of stuff into a tags table

Posted by david on 16. January 2009 22:28

in the last episode of 24, Jack tortured the column names of the park services from the import table, forcing them into the tags table. I need to link those tags to the Parks via my import table.

the import table has a park ID, and 54 or so columns, one for each service. the record has 1 if the park has that service, Null if not.

Excel function to strip out part of a URL:

if B2 = ‘http://www.seattle.gov/parks/park_detail.asp?id=4010”

RIGHT(B2,(LEN(B2)-FIND("=",B2)))

returns 4010

this groups on parkURLID, giving column name like tagid, data points already changed to tagid 

/****** Script for SelectTopNRows command from SSMS  ******/

SELECT TOP 1000 [ParkURLid]

      ,MAX([Baseball Softball]) AS A1

      ,MAX([Basketball Full Court]) AS A2

      ,MAX([Basketball Half Court]) AS A3

      ,MAX([Bike Trail]) AS A4

      ,MAX([Cricket]) AS A5 

      ,MAX([Lacrosse])AS A6

      ,MAX([Lawn Bowling])AS A7

      ,MAX([Rugby])AS A8

      ,MAX([Skate Park])AS A9

      ,MAX([Soccer]) AS A10

      ,MAX([Disc Golf]) AS A11

      ,MAX([Flag Football])AS A12

      ,MAX([Football])AS A13      

      ,MAX([Golf])AS A14

      ,MAX([Horseshoe Pits])AS A15

      ,MAX([Tball])AS A16

      ,MAX([Tennis])AS A17

      ,MAX([Track])AS A18

      ,MAX([Ultimate Frisbee])AS A19

      ,MAX([Play Area])AS A20

      ,MAX([Play Area ADA])AS A21

      ,MAX([Wading Pool])AS A22

      ,MAX([Dog Off Leash Area])AS A23

      ,MAX([Boat Ramp Motorized])AS A24

      ,MAX([Boat Ramp Hand Carry])AS A25

      ,MAX([Boat Moorage])AS A26

      ,MAX([Fishing])AS A27

      ,MAX([Guarded Beach])AS A28

      ,MAX([Pool Outdoor])AS A29

      ,MAX([Pool Indoor])AS A30

      ,MAX([Creek])AS A31

      ,MAX([Garden Conservatory])AS A32

      ,MAX([Greenspace])AS A33

      ,MAX([Ppatch])AS A34

      ,MAX([Pesticide Free])AS A35

      ,MAX([Tidelands])AS A36

      ,MAX([View])AS A37

      ,MAX([Waterfront])AS A38

      ,MAX([Woods])AS A39

      ,MAX([Community Center])AS A40

      ,MAX([Environmental Learning Center])AS A41

      ,MAX([Picnic Sites])AS A42

      ,MAX([Rental Facilities])AS A43

      ,MAX([Wedding and Ceremonies])AS A44

      ,MAX([Fire Pit])AS A45

      ,MAX([Decorative Fountain])AS A46

      ,MAX([Hiking Trails])AS A47

      ,MAX([Trails and or Paths ADA])AS A48

      ,MAX([Restrooms])AS A49

      ,MAX([Historic Landmarks])AS A50

      ,MAX([Trails and or Paths])AS A52

      ,MAX([Restrooms ADA])AS A53

      ,MAX([WiFi Enabled])AS A54

  FROM [egerton].[dbo].[Sheet1$]

  

    GROUP BY ParkURLid

there had to be an easier way to do this – crosstab or some set, subquery doodad …

I got closer to the goal … 

create table #URLid2tagID 

    (

        URLID int,

        tagID varchar(10)

    )

 

DECLARE @tagCount int

SET @tagCount = 54

 

DECLARE @counter int

SET @counter = 1

 

DECLARE @tag varchar(35)

DECLARE @SQL varchar(1000)

 

WHILE @Counter <= @tagCount

BEGIN

    SET @tag = (SELECT mapsite.Tags.Tag FROM mapsite.Tags WHERE mapsite.Tags.TagID = @Counter)

    Set @Counter = @Counter+1

    SELECT @SQL = 'INSERT INTO #URLid2tagID '    

    SELECT @SQL = @SQL + 'SELECT [ParkURLid] as URLID, '+CONVERT(varchar(10),@Counter)+' AS tagID FROM [egerton].[dbo].[Sheet1$]'

    SELECT @SQL = @SQL + ' WHERE ['+@tag+'] IS NOT NULL'

    Exec ( @SQL)

END    

 

GO  

 

INSERT INTO [egerton].[mapsite].[Tags2POI]

           ([POI_ID]

           ,[TagID])  

SELECT POI.POI_ID, #URLid2tagID.tagID AS TagID

FROM 

    mapsite.POI JOIN #URLid2tagID

        ON mapsite.POI.POI_Note2 = #URLid2tagID.URLID

 

a combo of temp table & dynamic sql and was able to plug data into my Tags2Parks table.  & the data almost looked right, some parks had a bunch of services, others only a few … in fact, the parks had the correct number of services … unfortunately not the right ones.

… but I’m farther along than when I started.

I am disappointed to have to jump through so many hoops.  I feel like I’m missing some simple concept that I could have applied much earlier to whip all this data into shape. 

Comments: 104

Filed Under:

Tags: , , ,

Comments

Add comment


 

biuquote
Loading