By Rupert
sqlite3
iPhone Dev Note #21: Route-Me Offline Mapping from Database
Aug 12th
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.
downloadosmtiles.pl --lat=6.9443:7.2261 --lon=125.5082:125.7104 --zoom=6:12
The tiles will be downloaded to the current directory.
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.
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 this line 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]; +}
4. Still with me? Comment NSAssert on 609 on RMMapContents.m
Part V: RouteMeSampleMapDBOffline code
1. Download RouteMeSampleMapDBOffline.zip
2. Drop the project in the samples directory.
3. Build. You should be able to build this since the header path is relative to the route-me trunk.
4. Run from the simulator
iPhone Note #8: Exporting Oracle to SQLite3
Aug 16th
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. More >
iPhone Note #5: SQLite3
Aug 12th
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:
SQLite3 Cheatsheet
Aug 12th
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
Comments