SQL Server 2008 Geometry: Reduce

I once had a go at using the Douglas-Peucker Algorithm in C# (for RaceReplay.net), so it was interesting to see it "pop-up" in SQL Server 2008 as the Reduce() function.

To understand what it does, you could try these queries (using country polygon data from the Google Earth community):

-- Tasmania from kmz
DECLARE @a GEOMETRY
SET @a = geometry::STGeomFromText('POLYGON((146.5861 41.18666, 147.9718 40.74479, 148.2733 40.90111, 148.3638 42.22243, 148.1953 41.94545, 148.0791 42.11722, 147.8429 42.87292, 147.9997 42.90708, 147.9955 43.22759, 147.7897 43.24695, 147.6316 43.06555, 147.7065 42.93833, 147.8994 43.02687, 147.8258 42.93195, 147.5575 42.83056, 147.4271 43.04174, 147.3175 42.84666, 147.2475 43.26917, 146.9913 43.11243, 147.0953 43.28872, 146.8336 43.64806, 146.0383 43.49805, 145.9327 43.37632, 146.2345 43.32514, 145.8369 43.29723, 145.4597 42.90444, 145.2052 42.25695, 145.4694 42.52306, 145.552 42.35111, 144.8586 41.54445, 144.6372 41.03194, 144.7014 40.75917000000001, 146.5861 41.18666))',0)
SELECT @a.STNumPoints() AS [PointsInRawPolygon]
, @a.Reduce(0.2).STNumPoints() AS [PointsReduce1]
, @a.Reduce(1).STNumPoints() AS [PointsReduce5]



But it would be even better if we could visualize the effects that Reduce() has on the polygon data... introducing Geoquery Analyzer

Polygon data without Reduce()



Polygon data with moderate Reduce() tolerance



Polygon data with agressive Reduce() tolerance



Polygon data with overly agressive Reduce() tolerance!



And of course, if you raise the tolerance too high,
System.AgumentException: 24127: Using Reduce with the specified tolerance will yield an instance that is not valid. Using a smaller tolerance may achieve a valid result.


A few additional examples - some countries missing purely because the KML import had some errors...




NOTE 1: Geoquery Analyzer is currently in PRE alpha development status (ie. it's pretty dodgy). If/when the code gets to a fairly stable/usable point, it'll be uploaded here.

NOTE 2: if you looked really closely at the coordinates, you'll see I've changed the latitude values to be positive... the reason is NOTE 1.

NOTE 3: on the world maps, the 0,0 point is top/left which makes it appear upsidedown

Battleship 2008 - GEOMETRY datatype sample

Here's a quick example of the new coordinate datatypes in SQL Server 2008, in the form of a game!

Battleship 2008 - GEOMETRY datatype and spatial functions in SQL Server (and on CodeProject) uses the basics - POINT and LINESTRING - to build a simple, single-player version of the game Battleship

This is what a game "looks like" (13 is the GameId, 4 is my PlayerId... the other numbers are the co-ordinates I'm shooting at)

it's played in Management Studio, like a 'command line', so you'll need to keep your own copy of the board on paper (like this?)


Enjoy!

SQL Server 2008 Geography vs Geometry

Really basic comparison of GEOGRAPHY and GEOMETRY data types in SQL Server 2008.

First, some queries:
DECLARE @ll geography;
DECLARE @g geometry;

PRINT 'LatLong polygon length'
SET @ll = geography::STGeomFromText(
'POLYGON((1 1,1 5,5 5,5 1,1 1))', 4326);
SELECT @ll.STLength() -- 1773426.60585491

PRINT 'Geometry polygon length'
SET @g = geometry::STGeomFromText(
'POLYGON((1 1,1 5,5 5,5 1,1 1))', 4326);
SELECT @g.STLength() -- 16

PRINT 'LatLong polygon area'
SET @ll = geography::STGeomFromText(
'POLYGON((3 3,6 4,6 2,3 3))', 4326);
SELECT @ll.STArea() -- 36749145501.1338

PRINT 'Geometry polygon area'
SET @g = geometry::STGeomFromText(
'POLYGON((3 3,6 4,6 2,3 3))', 4326);
SELECT @g.STArea() -- 3

You might want to read this excellent explanation of the 'Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation.' requirement before looking at the next query.

PRINT 'Ring-orientation: counterclockwise'
DECLARE @ll geography;
SET @ll = geography::STGeomFromText('POLYGON((3 3,6 4,6 2,3 3))', 4326);
-- Command(s) completed successfully.
SET @ll = geography::STGeomFromText('POLYGON((3 3,6 2,6 4,3 3))', 4326);
-- The specified input does not represent a valid geography instance because it exceeds a single hemisphere.

Make sense? Maybe this diagram will help (green, anti-clockwise arrows good; red, clockwise arrows bad)


The first attempt at MUTLIPOLYGON didn't work, and at first I couldn't understand why. I got this example WKT from Wikipedia:
DECLARE @ll geography;
SET @ll = geography::STGeomFromText(
'MULTIPOLYGON(((1 1,5 1,5 5,1 5,1 1),(2 2, 3 2, 3 3, 2 3,2 2)),((3 3,6 2,6 4,3 3)))'
, 4326);
-- The specified input does not represent a valid geography instance.
Here it is 'on paper'

you'll notice they overlap and are all described clockwise... no problem, right? After all, the example came from Wikipedia! Hmmm, the Reduce doco just happens to mention for example, an invalid MultiPolygon instance is created if Reduce() is applied to simplify each ring in the instance and the resulting rings overlap..

Ah ha! So SQL Server doesn't like overlapping elements within a MULTIPOLYGON? Let's try removing the 'overlaps' (making the green polygon smaller) like this:
DECLARE @l1 geography;
SET @l1 = geography::STGeomFromText(
'MULTIPOLYGON(((2 2, 2 3, 3 3, 3 2, 2 2)
,(3 3,6 4,6 2,3 3))
,((1 1,2 1,2 2,1 2,1 1)))', 4326);
-- The specified input does not represent a valid geography instance


Still not working - hmm, maybe the ring-orientation is also a problem?

DECLARE @l1 geography;
SET @l1 = geography::STGeomFromText(
'MULTIPOLYGON(((2 2, 2 3, 3 3, 3 2, 2 2)
,(3 3,6 4,6 2,3 3))
,((1 1,1 2,2 2,2 1,1 1)))'
, 4326); -- IT WORKS!


Interestingly, as long one of the POLYGONs is anti-clockwise, the query seems to work! Also interestingly, the previous query works using the GEOMETRY type (just to prove that the 'hemisphere problem' only applies to GEOGRAPHY!).

DECLARE @l1 geometry;
SET @l1 = geometry::STGeomFromText(
'MULTIPOLYGON(((2 2, 2 3, 3 3, 3 2, 2 2)
,(3 3,6 4,6 2,3 3))
,((1 1,2 1,2 2,1 2,1 1)))', 4326);
-- WORKS FOR GEOMETRY

Finally, here's another MULITPOLYGON example
DECLARE @ll geography;
SET @ll = geography::STGeomFromText(
'MULTIPOLYGON ((
(35.003075386599093 136.49999931072208,
35.004100499758216 136.00000001975212,
35.000000005283141 136.00000001974058,
35.003075386599093 136.49999931072208)),
((34.999999973649494 138.00000000826108,
36.5 136,
35.761206832024406 136.00000002190484,
35.004100371491738 136.99469190018391,
34.999999973649494 138.00000000826108)))'
, 4326);

SELECT @ll.STLength() -- 634673.083368519
SELECT @ll.STArea() -- 11370068204.2658
SELECT @ll.STIntersects(geography::STGeomFromText('POINT(-30.041 153.166)',4326)) -- 0
SELECT @ll.STIntersects(geography::STGeomFromText('POINT(36.5 136)',4326)) -- 1

Here's the query that confirms they don't overlap and therefore make a valid MULTIPOLYGON:
DECLARE @l1 geography;
DECLARE @l2 geography;
SET @l1 = geography::STGeomFromText('POLYGON ((
35.003075386599093 136.49999931072208,
35.004100499758216 136.00000001975212,
35.000000005283141 136.00000001974058,
35.003075386599093 136.49999931072208))', 4326);
SET @l2 = geography::STGeomFromText('POLYGON ((
34.999999973649494 138.00000000826108,
36.5 136,
35.761206832024406 136.00000002190484,
35.004100371491738 136.99469190018391,
34.999999973649494 138.00000000826108))'
, 4326);
SELECT @l1.STIntersects(@l2) -- 0 -- doesn't intersect!


That last example is from this post (in Japanese) on these Microsoft.SqlServer.Types. There is also a couple of excellent images that demonstrate the difference between GEOGRAPHY and GEOMETRY (remember to imagine the 2nd image is on a sphere!).

SQL Server 2008 Geography: STIntersects, STArea

Having read (and tried out) John's first impression of SQL 2008 Spatial, I went about extending the dataset to try some of the other capabilities of SQL Server 2008's geography datatypes and functions.

The following tables were added:

and the data inserted. The KMZ file was parsed with the XSD generator and C# (similar to John's example);



the CSV files were opened in Excel



and INSERT statements generated.



It's worthwhile noting that the data import didn't go 100% according to plan. Some electorates consist of more than one polygon (to be unioned together, I suppose) - I've only imported the first Polygon for each electorate in the KMZ file. Other electorates failed with SQL errors - still to determine why the coordinates listed cause SQL Server to fail (no, it's not the ring-orientation...).


The following queries were then possible...

select state
, electorate
, ROUND(Location.STArea()/1000000,0) As [AreaSqKm]
from electorates


select state
, electorate
, COUNT(p.Title) AS [NumberOfPostcodes]
, ROUND(e.Location.STArea()/1000000,0) AS [AreaSqKms]
, ROUND(e.Location.STArea()/1000000 /COUNT(p.Title),0) AS [AreaPerPostcodeSqKm]
from electorates e
inner join Postcodes p on (p.Location.STIntersects(e.Location) = 1)
group by State, electorate, e.Location.STArea()/1000000


select state
, e.electorate
, p.Title as [Suburb]
, COUNT(pp.PollingPlaceID) AS NumberPollingPlaces
from electorates e
inner join Postcodes p on (p.Location.STIntersects(e.Location) = 1)
inner join PollingPlaces pp on (pp.Electorate = e.Electorate AND pp.Postcode = p.Postcode)
group by state, e.electorate, p.Title


select state
, e.electorate
, ROUND(e.Location.STArea(),0) AS [AreaSqMs]
, SUM(ppv.TotalVotes) AS [TotalVotes]
, ROUND(e.Location.STArea() /(1+SUM(ppv.TotalVotes)),0) AS [AreaPerVoterM]
from electorates e
inner join Postcodes p
on (p.Location.STIntersects(e.Location) = 1)
inner join PollingPlaces pp
inner join PollingPlaceVotes ppv on ppv.PollingPlaceId = pp.PollingPlaceId
on (pp.Electorate = e.Electorate AND pp.Postcode = p.Postcode)
group by State, e.electorate, e.Location.STArea()


It is this query I've attempted to map at the top of the post - hopefully demonstrating finding points inside a polygon to group postcode data together for couting and summation; and using the area function to determine 'density'.

The next step is to get the POLYGON data into a map (as well as the POINTS)...

select electorate, Location.STAsText() as PolygonWKT
from electorates order by electorate

SQL Server 2008 (Katmai) Diagram Tool

The Database Diagram Tool in SQL Server 2008 (Katmai) Management Studio has (once again) not changed much from the previous version.

Some notes:

Scripted Diagram export still works
This script to 'export' diagrams to SQL file (designed for SQL Server 2005) works in 2008. Implies there hasn't been any change to the storage format, and probably diagrams scripted in 2005 can be 'rehydrated' in 2008.

Diagram context menu unchanged
Nothing new in this menu - although I had forgotten the Copy to Clipboard item existed...


Table context menu adds Spatial Indexes... item
New spatial types can have their indexes setup just like other index types


Scroll down to Spatial Indexes on this post and these explanations of spatial indexes for a brief explanation of 'how they work'

(and no, this dialog doesn't actually resize in Management Studio - it's been Photoshopped for clarity)

SQL Server 2008 (Katmai) Spatial/Geography Functions

Decided to take the 'night off' from SilverlightEarth.com to play with the new Geography types in the latest SQL Server 2008 CTP.

There is surprisingly little around in terms of demos and samples so far, John O'Brien's First Impression being the best by far (and uses Aussie data too!). Only took a few minute to get his postcode-data demo going in C# & WebDev Express 2008.

His code covers:

• [geography] SQL Data Type
• geography::STPointFromText(@WKT, 4326)
• POLYGON (Well Known Text syntax)
• geography::STPolyFromText(@WKTBounds, 4326)
• STIntersects 'function'
• SPATIAL INDEX on a column of type [geography]

and discusses the interesting 'side effect' of the datatype enforcing polygons not spanning greater than a hemisphere of area AND "Must be supplied in counter-clockwise order"! (the 4326 SRID is explained here)

This query over John's data:

SELECT Id, Postcode, Title, Location.STAsText()
FROM postcodes

returns the list of all the postcodes with 'human readable' co-ordinates, but that's about the only "interesting" thing you can do without adding other data...

So I visited the Google Election '07 site which has a link to http://voteaustralia.googlepages.com/aus.kmz that contains a 'collection' of polygons defining the electorates in Australia.

It was trivial to extract the data for just one electorate (Cook) using Excel to re-order (see counter-clockwise note above, and error below) and reformat the <coordinates> to fit in the following query:

DECLARE @g geography;
SET @g = geography::STGeomFromText('POLYGON((
-34.041 151.166,-34.036 151.178,-34.035 151.187,-34.037 151.195,
-- coordinates removed for clarity
-34.052 151.156,-34.044 151.162,-34.041 151.166))', 4326);

SELECT [Postcode] as Title
,      [Title] as Description
,      [Location].STAsText()
FROM   [dbo].[Postcodes]
WHERE  [Location].STIntersects(@g) = 1

Which returns the 31 postcodes in the Cook electorate, and

SELECT @g.STArea() -- square metres

which returns 195631.754900467 (square metres).

It shouldn't be hard to programmatically import the electorial boundaries to do more spatial queries; could also mash it up with aec polling place and voting population data (by postcode). Using the area and population we could do some density visualisation, and maybe some other useless calculations...

This is the error I got when I DIDN'T reverse the order of the coordinates from the KML file: localhost(MYPC\Administrator): Msg 6522, Level 16, State 1, Line 2
A .NET Framework error occurred during execution of user defined routine or aggregate 'geography':
Microsoft.SqlServer.Types.GLArgumentException: 24205: The specified input does not represent a valid geography instance because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation.
Microsoft.SqlServer.Types.GLArgumentException:
at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeometryData g) - as explained above, you must ensure the POLYGON is described in counter-clockwise order!


Link drop:
geography Data Type Reference (Books Online)

Demystifying Spatial Support in SQL Server 2008 is an EXCELLENT 'real world' example.

SQL Server 2008 Katmai will Include Spatial Support is also good reading

And finally, this is a rather old (May) 'introduction' but still worth a look.


WOT: TODO in SilverlightEarth.com - FlashEarth mash-up

Introducing 'Gaml' - Geographic Application Markup Language

Adding a few geographic tags to existing Xaml provides infinite flexibility for map "markup". This is a simple example of a flickr photo 'balloon' on Silverlight Earth



<Canvas xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:g="http://silverlightearth.com/2007/gaml"
g:Latitude="40.712459"
g:Longitude="-73.9497466875"
g:Zoom="13">
<Path Stroke="Black" Fill="Gray"
Data="m 30,20 v -20 h 200 v 85 h -200 v -45 l -30,-10 l 30,-10" />
<Image Canvas.Top="5" Canvas.Left="35" Width="75" Height="75"
Source="http://....flickr.com/..._dcdc289280_s.jpg" />
<TextBlock Canvas.Left="114" Canvas.Top="8"
Text="New York - East River"
FontSize="10" FontFamily="Verdana" Foreground="#FFFFFFFF" />
</Canvas>

The sample currently works, but due to a hosting issue (not serving .gaml files), the other demo's are not currently online. When this is resolved, the above example will be available on http://www.silverlightearth.com/?q=http://www.silverlightearth.com/gaml/Samples/flickr_nyc1.gaml

There's more information on Gaml here, but there's a little more work to be done on the implementation... if you're wondering why some of the samples are flag-markers, they were the first examples of Xaml clipart that I could find!

Silverlight Google Maps (inc new Topo view)

SilverlightEarth.com now has 11 map options, including Yahoo's map tiles and the new Google Topo view. mapstraction had some helpful pointers on the Yahoo tile-names (so thanks to them).

The 'hybrid' layers don't "overlay" anything yet - that's still to come...

And finally, the design has been tweaked a bit - hopefully it looks a bit nicer?

p.s. yes, jerky panning fix to come...

p.p.s. another 'before & after' shot - check out the baseball diamond on the east side before (google) and after (virtual earth)... here it is in October.

Silverlight Virtual Earth - now with (jumpy) drag-panning

Visit Auckland on SilverlightEarth.com and try out drag-panning across the map.

The dragging 'jumps' because it's currently aligning the "tile grid" to the nearest tile-boundary (256 pixels)... just a few more tweaks to get it behaving properly.

The "Link to this map" feature now includes the map type, so you can view the London Eye via (Virtual Earth) satellite, London (Virtual Earth) road map or even the London Eye on Google Maps.






And yes, it works on the Mac (Safari 3 and Firefox 2)!

Silverlight (Virtual) Earth with Ink

Thanks to Gavin's pointer to an MSDN sample, SilverlightEarth.com now has Ink! Click [Draw on this map] at top-right to try it out.

Sorry, I haven't figured out saving the Ink yet (although got some ideas from search sample and converting to xaml), but it's kinda fun to play with.

Still to do: drag-panning and bookmarking map-type Virtual Earth or Google Maps... so yes, it's still in beta.

Virtual Earth 6 - new map 'shading'

Microsoft Virtual Earth 6 has been released - along with various different improvements to the "UI" (javascript and the controls that drive it), one noticable difference is the addition of 'topographical shading' in Road view. Here's the Golden Gate Recreation Area 'before' and 'after'...



Of course, SilverlightEarth.com now uses the new tiles - visit San Francisco and switch between (road) and (roadshade) views.

Seems to be available "down under", but only to zoom '13' and no closer...

(thanks to via windows live and Darren Neimke for the heads-up)

Silverlight 'map tile client' finds a home - SilverlightEarth.com

While the map tile client isn't quite yet for release, I'd already paid for the domain so decided to post it anyway... which means

SilverlightEarth.com

is now LIVE! Granted the dragging still doesn't work; but keyboard (arrows, page up, page down to zoom and pan) does, and the mouse-wheel zooms too. The search box 'searches' and the 'link to this page' does just that.

What's still to come? Well centering the search location for a start, then proper drag-panning on mouse-still-down. After that - maybe uploading some form of geo-coded Xaml to overlay directly on the map... and finally animation a-la RaceReplay.net.

While I had already started working on the 'tile client' before coming across FlashEarth, that is basically the kind of result I'd like to get from Silverlight. Of course, since the Silverlight 1.0 version is (will be) Javascript-based, the code will be easily available for all to see...

Add recipes to Notes.app (for iPhone/iPod Touch)

Following these instructions for adding data to the Notes application for iPhone, you could add your favourite iPhone/iPod Recipes for easy reference.



i.recipenow.net provides preformatted SQL to use with those instructions -- here is an example link for Sugar-grilled Banana Bagels, and how the resulting Note looks:



To 'import' any recipe as a Note, find it's ID by browing i.recipenow.net with Firefox or Safari, taking note of the URL when you find a recipe link you like

Then simply visit the link below, substituting the ID http://i.recipenow.net/i/r-note.aspx?id=ID_GOES_HERE and follow the instructions.

iPhone/iPod Touch Notes - fonts and styles

Turns out there is a lot you can do once you've got pscp to grab your notes.db, and a copy of SQLite Browser.

The basic structure of notes.db seems to be:

CREATE TABLE Note 
( creation_date INTEGER
, title TEXT
, summary TEXT)

CREATE TABLE note_bodies 
( note_id INTEGER
, data)

There doesn't appear to be a direct relationship between them, however the [Note] rows are always sorted by [creation_date] ASC, and the [note_bodies].[note_id] ascends in the same 'order'.

[summary] does not appear to be used on the iPhone/iPod Touch (Notes.app puts the first 50 chars of the note data in that column); and [creation_date] is iPhone_Unix_time (seconds since January 1st, 2001 UTC).

Anyway, without further ado, the steps to get this font-test source onto an iPhone/iPod Touch follow (for Windows):

1. Get required software

pscp (part of the PuTTY download) and
SQLite Browser

2. Download your notes.db

Create this batch file somewhere on your PC, along with a Library\Notes folder tree. Run the .BAT (entering the IP address when asked). Assuming the file downloads successfully, MAKE A BACKUP COPY of it somewhere.

DownloadNotes.bat
set IP=
set /p IP=Enter your iPod's IP address (eg: 192.168.1.101): 
pscp -r -pw alpine root@%IP%:/var/root/Library/Notes/* Library/Notes
pause

3. Open notes.db in SQLite Browser

The Database Structure tab will look something like this (additional metadata may be shown)

Go to the Execute SQL tab (like this)

Execute the following four (4) SQL statements, one after the other:

-- BACKUP YOUR notes.db FILE BEFORE YOU START

-- These queries were run using http://sqlitebrowser.sourceforge.net/

-- First, insert the 'header' for the note
-- Get the creation_date from http://i.recipenow.net/i/time/
-- The title appears on the Notes index page; the summary
-- does not appear to be used on the iPhone/Touch (for now)
INSERT INTO Note(creation_date, title, summary) VALUES(216352013, 'Html and Font Test', 'American Typewriter')

-- Now that we've added a new header, 
-- determine the new id and using it:
-- IMPORTANT : *** REPLACE the string MAX_NOTE_ID *** 
--             *** in BOTH the queries below      ***
SELECT MAX(note_id) + 1 FROM note_bodies

-- Then add the new row with that id
INSERT INTO note_bodies (note_id) VALUES ( MAX_NOTE_ID )

-- Finally, update the new row with the note body
UPDATE note_bodies SET data='Html Test<div><br class="webkit-block-placeholder"></div>
<div style="font-family:American Typewriter;">American Typewriter  <b>bold</b> <i>italic</i> <u>underline</u></div>
<div style="font-family:Arial;">Arial  <b>bold</b> <i>italic</i> <u>underline</u></div>
<div style="font-family:Courier New;">Courier New  <b>bold</b> <i>italic</i> <u>underline</u></div>
<div style="font-family:Georgia;">Georgia  <b>bold</b> <i>italic</i> <u>underline</u></div>
<div style="font-family:Helvetica;">Helvetica  <b>bold</b> <i>italic</i> <u>underline</u></div>
<div style="font-family:Marker Felt;">Marker Felt  <b>bold</b> <i>italic</i> <u>underline</u></div>
<div style="font-family:Times New Roman;">Times New Roman  <b>bold</b> <i>italic</i> <u>underline</u></div>
<div style="font-family:Trebuchet MS;">Trebuchet MS  <b>bold</b> <i>italic</i> <u>underline</u></div>
<div style="font-family:Verdana;">Verdana  <b>bold</b> <i>italic</i> <u>underline</u></div>
<div style="font-family:Zapfino;">Zapfino <b>bold</b> <i>italic</i> <u>underline</u></div>
<div><br class="webkit-block-placeholder"></div>
<div style="font-family:Helvetica;"><sup>1</sup>/<sub>2</sub> cup</div>'
WHERE note_id = MAX_NOTE_ID

4. Save notes.db

Save the changes you have just made AND QUIT SQLite Browser. You must quite the program so that the upload will work.

5. Upload your notes.db

As with step 2 (but in reverse)

UploadNotes.bat
set IP=
set /p IP=Enter your iPod's IP address (eg: 192.168.1.101): 
pscp -r -pw alpine Library/Notes/notes.db root@%IP%:/var/root/Library/Notes/
pause

6. Open Notes on your iPhone/iPod Touch

The new note should appear in your Notes application, and display with the complete set of fonts shown above


If you choose to send the note as an email, the formatting stays... so delete the text you don't want and start 'overtyping' in your favourite style for fancy email formatting

RecipeNow food site for iPhone/iPod now listed!

Google Analytics shows lots of inbounds from:

• ilounge.com food category
• appSafari food category

Just a few more features to add before submitting to Apple's webapp list (basic emailing works now).

p.s. for iPhone 'bleeding edge' developers, it doesn't get any better than Joe Hewitt, iPhone God who now works at Facebook (and built the awesome m.facebook.com version). Cool...

Backup (Preferences, Notes, etc) data from jailbroken iPod Touch

If you have jailbroken your iPod Touch (not recommended, of course...) and perhaps added iPhone applications such as Maps, Notes, Mail, etc (not legal...), you might wish to back-up the data you create on said applications (given that iTunes isn't going to offer to help you out since you aren't using an iPhone).

So how to find and copy down the data to your PC?

This modmyifone thread: How to Backup lists a few of the 'data files' used by various applications. The tip that follows simply copies ALL files from /Library/ to your local disk.

Create a Windows batch file and place it in the same directory as folder named Library and a copy of pscp.exe, iBackup.bat

set IP=
set /p IP=Enter your iPod's IP address (eg: 192.168.1.101): 
pscp -r -pw alpine root@%IP%:/var/root/Library/* Library/
pause

When you run this, the command prompt will appear and wait for you to enter your iPod's IP Address [Settings » Wi-Fi » (your net) » IP Address] . It will then download everything from your iPod's /Library/ directory, so you get the main data files and a whole pile of cache data as well - about 13Mb in all (on my device).

Points to note (based on an iPhone, of course):

• /Library/Preferences/* is probably something you'd want to keep all of, just in case


• /Library/Notes/notes.db is apparently an sqlite data file. There's an sqlite gui too (which I haven't tested yet). Hopefully it'll be possible to download/edit and upload this file.


• /Library/AddressBook/AddressBook.sqlitedb is even named as an sqlite file. iTunes will sync this for iPod owners since it's built-in


• /Library/Calendar/Calendar.sqlitedb - iTunes will sync this for iPod owners since it's built-in; in fact, it's one-way for the iPod because the Touch Calendar is read-only (for now)


• /Library/Keyboard/dynamic-text.dat is apparently a mini-key-logger! I guess it's where they keyboard 'learns' (?)


• /Library/Mail/* contains a whole pile of different bits - probably best to use IMAP and rely on your server copy for that

Please remember that I've only downloaded the data described above - I haven't had the need to upload any of it, so there's no guarantee that the copies are a perfect backup. But if you did lose your iPod for some reason, there's a chance it'd be recoverable.

I can't test on a Mac; but if you have one you can probably figure out how to use Terminal to grab the files.

UPDATE: sqlitebrowser works a treat

Recipes on your iPhone/iPod Touch (Safari)

In a little break from working with Silverlight and maps, I've added an iPhone/mobile-Safari interface to recipenow.net, at

i.recipenow.net

There is at least one other mobile-Safari recipe site (already listed on Apple's iPhone application listing) - 101 Cookbooks - iPhone edition, and I'm sure there'll be more if sites like epicurious.com decide to tackle the mobile space.







More iPhone/iPod Touch ideas to come... thanks to Joe Hewitt's iUI js/css package for iPhone development.

NOTE: to access the mobile recipes from a PC, use the Safari beta or FireFox; for the Mac, use Safari (of course!).