Archive

Archive for the ‘sqlite3’ Category

iPhone Dev Note #21: Route-Me Offline Mapping from Database

August 12th, 2010 rupert 22 comments

Part I: Download the osm (openstreetmap) tiles
1. Download the tiles from osm using downloadosmtiles.pl
- Download Geo-OSM-Tiles-0.02.tar.gz from CPAN
- See README file. Compile and build

   perl Makefile.PL
   make
   make test
   make install

- Copy downloadosmtiles.pl to /usr/bin
- Usage:

downloadosmtiles.pl --lat=min_lat:max_lat --lon=min_long:max_long --zoom=min_zoom:max_zoom

How do you set the min_lat, max_lat and min_long, max_long?
- Go to www.openstreetmap.org
- Click on the “Edit” tab
- You will see the extent or bounds of the map under the section “Area to Export”
- Alternatively, you can click on “Manually select a different area” to specifically choose an area.

osm.png

downloadosmtiles.pl --lat=6.9443:7.2261 --lon=125.5082:125.7104 --zoom=6:12

The tiles will be downloaded to the current directory.
dir.png

Part II:Put the tiles in the sqlite database
1. Read Frank’s email to route-me group regarding map2sqlite

2. Download map2sqlite-1.0.tar.bz2.

3. Build the map2sqlite XCodeProj. Afterwards, find map2sqlite and drop it in /usr/bin.

cp map2sqlite /usr/bin

4. Run map2sqlite to import the tiles in sqlite.

map2sqlite -db ph-1.0.0.db -mapdir ph-osm-map/
 
2010-08-12 17:24:40.749 map2sqlite[14113:903] map2sqlite 1.0
2010-08-12 17:24:40.756 map2sqlite[14113:903] Creating ph-1.0.0.db
2010-08-12 17:24:40.761 map2sqlite[14113:903] Importing map tiles at ph-osm-map/
2010-08-12 17:25:03.169 map2sqlite[14113:903] 
2010-08-12 17:25:03.170 map2sqlite[14113:903] Map statistics
2010-08-12 17:25:03.170 map2sqlite[14113:903] --------------
2010-08-12 17:25:03.171 map2sqlite[14113:903] map db:            ph-1.0.0.db
2010-08-12 17:25:03.171 map2sqlite[14113:903] file size:         13758464 bytes
2010-08-12 17:25:03.172 map2sqlite[14113:903] tile directory:    ph-osm-map/
2010-08-12 17:25:03.172 map2sqlite[14113:903] number of tiles:   9091
2010-08-12 17:25:03.173 map2sqlite[14113:903] zoom levels:       6 - 11
2010-08-12 17:25:03.218 map2sqlite[14113:903] zoom level  6:        12 tiles, (    28,    52)x(    31,    54), {x=112.500000,y=21.943047}x{x=129.375000,y=0.000000}
2010-08-12 17:25:03.219 map2sqlite[14113:903] zoom level  7:        35 tiles, (    56,   105)x(    62,   109), {x=115.312500,y=21.943047}x{x=129.375000,y=2.811371}
2010-08-12 17:25:03.222 map2sqlite[14113:903] zoom level  8:       117 tiles, (   112,   210)x(   124,   218), {x=115.312500,y=21.943047}x{x=127.968750,y=4.214943}
2010-08-12 17:25:03.223 map2sqlite[14113:903] zoom level  9:       450 tiles, (   225,   420)x(   249,   437), {x=115.312500,y=21.289375}x{x=127.968750,y=4.214943}
2010-08-12 17:25:03.225 map2sqlite[14113:903] zoom level 10:      1715 tiles, (   450,   841)x(   498,   875), {x=115.664062,y=21.289375}x{x=127.968750,y=4.565474}
2010-08-12 17:25:03.231 map2sqlite[14113:903] zoom level 11:      6762 tiles, (   900,  1683)x(   997,  1751), {x=115.839844,y=21.289375}x{x=127.968750,y=4.565474}

5. ph-osm-map is 43.9 MB but was compressed to ph-1.0.0.db (13.8 MB)

Part III: Downlaod the route-me code from trunk and run some examples.
- Follow this previous tutorial

Part IV: Patch the trunk to incorporate the RMDBMapSource from Frank Schroder

1. What we need to add to the trunk. Download RMDBMapSource.zip

+ RMDBMapSource.h
+ RMDBMapSource.m
+ RMDBTileImage.h
+ RMDBTileImage.m

Copy the files above to the “Map” directory.

route-me-1.png

2. Edit RMTileImage.h and RMTileImage.m base on the patch below.

Index: MapView/Map/RMTileImage.h
===================================================================
--- MapView/Map/RMTileImage.h	(revision 605)
+++ MapView/Map/RMTileImage.h	(working copy)
@@ -37,8 +37,10 @@
 #import "RMNotifications.h"
 #import "RMTile.h"
 #import "RMTileProxy.h"
+#import "FMDatabase.h"
 
 @class RMTileImage;
+@class NSData;
 
 @interface RMTileImage : NSObject {
 	// I know this is a bit nasty.
@@ -64,6 +66,7 @@
 + (RMTileImage*)imageForTile: (RMTile) tile withURL: (NSString*)url;
 + (RMTileImage*)imageForTile: (RMTile) tile fromFile: (NSString*)filename;
 + (RMTileImage*)imageForTile: (RMTile) tile withData: (NSData*)data;
++ (RMTileImage*)imageForTile: (RMTile) tile fromDB: (FMDatabase*)db;
 
 - (void)moveBy: (CGSize) delta;
 - (void)zoomByFactor: (float) zoomFactor near:(CGPoint) center;
Index: MapView/Map/RMTileImage.m
===================================================================
--- MapView/Map/RMTileImage.m	(revision 605)
+++ MapView/Map/RMTileImage.m	(working copy)
@@ -29,6 +29,7 @@
 #import "RMWebTileImage.h"
 #import "RMTileLoader.h"
 #import "RMFileTileImage.h"
+#import "RMDBTileImage.h"
 #import "RMTileCache.h"
 #import "RMPixel.h"
 #import <QuartzCore/QuartzCore.h>
@@ -108,6 +109,11 @@
 	return [image autorelease];
 }
 
++ (RMTileImage*)imageForTile:(RMTile) _tile fromDB: (FMDatabase*)db
+{
+	return [[[RMDBTileImage alloc] initWithTile: _tile fromDB:db] autorelease];
+}
+
 -(void) cancelLoading
 {
 	[[NSNotificationCenter defaultCenter] postNotificationName:RMMapImageLoadingCancelledNotification

We just need to add these lines on RMTileImage.h:

+#import "FMDatabase.h"
...
++ (RMTileImage*)imageForTile: (RMTile) tile fromDB: (FMDatabase*)db;

do the same for RMTileImage.m:

+#import "RMDBTileImage.h"
...
++ (RMTileImage*)imageForTile:(RMTile) _tile fromDB: (FMDatabase*)db
+{
+	return [[[RMDBTileImage alloc] initWithTile: _tile fromDB:db] autorelease];
+}

UPDATE (OCT 15, 2010): I am attaching my current RMTileImage.h and RMTileImage.m so you guys could double-check the changes I made. RMTileImage.zip

4. Still with me? Comment NSAssert on 609 on RMMapContents.m
rmcontents.png

Part V: RouteMeSampleMapDBOffline code
1. Download RouteMeSampleMapDBOffline.zip

2. Drop the project in the samples directory.
sample_proj.png

3. Build. You should be able to build this since the header path is relative to the route-me trunk.
build-config.png

4. Run from the simulator
finish.png

Categories: iphone, sqlite3 Tags: , ,

iPhone Note #8: Exporting Oracle to SQLite3

August 16th, 2009 Rupert No comments

Since I’ve been working most of my time with iPhone Dev for the last couple of months, I thought it will be worthwile to post how to import files to SQLite3. In a nutshell, use MesaSQLite’s IMPORT function. It will save you a lot of time. Now it is up to you how to export into a CSV or TAB delimited file. For Oracle, I used SQLDeveloper. For other databases, such as MySQL or Postgres, I’ve used Navicat. Read more…

Categories: iphone, oracle, sqlite3 Tags: , ,

iPhone Note #5: SQLite3

August 12th, 2009 Rupert No comments

Note this tutorial will be updated in the near future…

1. The code below (based from the SQLiteBooks Example) will copy the database from your bundle to the “Documents” directory. You don’t have to do this every time your app launches, so there is a check at “success” below. To check this, in the iPhone Simulator navigate to the Documents directory:

Read more…

Categories: iphone, sqlite3 Tags: ,

SQLite3 Cheatsheet

August 12th, 2009 rupert Comments off

Tools

1. MesaSQLite for MacOSX.

SQL

1. Getting the current time

MySQL = Now();

SQLite3 = CURRENT_TIMESTAMP;

insert into jobstemp(full_address, datecreated, dateupdated)
VALUES("9 Bishop Street", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

2. Getting the local current time

SELECT datetime(dateupdated, 'localtime')

3. Trim

SELECT trim(name) FROM table

4. Vacuum

rupert:Desktop rupert$ sqlite3 photos-2.0.0.db
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> vacuum;
sqlite> .quit;

5. Quit

rupert:Desktop rupert$ sqlite3 photos-2.0.0.db
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .quit;
Categories: sqlite3 Tags: