How can I index all of the OpenStreetMap data efficiently?

linq-to-sql openstreetmap sqlbulkcopy sql-server-ce

Question

Note: Although Windows Phone 7 is my objective, it just introduces a size constraint.

I'm attempting to use OpenStreetMap for this and I want to have my data put in a Compact SQL Server Edition database on my Seven Windows Phone in an effort to create a GPS/Routing/Map application for the Windows Phone 7. I'm having a lot of difficulty with this, so I'm unsure of the proper approach.

Here is where I am at:

  1. I downloaded Belgium.osm.pbf, which includes all of the OSM data for Belgium in Format PBF.

    Belgium isn't too big, but because it's where I reside, it seems like a nice place to start.

    Given that my database is just 80 MB in size, it would be wonderful if it were somewhat larger than that PBF file.

  2. I've created a parser using Marc Gravell's protobuf-net that gives me all the OSM data.

  3. I first attempted loading it all into RAM, but that appears to be too much for my Windows Phone 7, since the size ends up being > 512 MB. Then I had the notion that I required a database to save this data, therefore it only made sense to keep it in a SQL Server Compact Edition.sdf file.

  4. So, using LINQ to SQL, I created the following DataContext and Tables:

    public class RoutingContext : DataContext
    {
        public RoutingContext()
    #if WINDOWS_PHONE
            : base("Data Source = 'isostore:/RoutingDB.sdf'; Max Database Size = 1024; Max Buffer Size = 65536")
    #else
            : base("Data Source = './RoutingDB.sdf'; Max Database Size = 1024; Max Buffer Size = 65536")
    #endif
        {
    
        }
    
        public Table<Node> Nodes;
        public Table<Road> Roads;
        public Table<RoadNode> RoadNodes;
        public Table<NodeProperty> NodeProperties;
        public Table<RoadProperty> RoadProperties;
        public Table<StringData> Strings;
    }
    
    [Table]
    public class Node
    {
        [Column(IsPrimaryKey = true)]
        public int Id { get; set; }
    
        [Column()]
        public int Lon { get; set; }
    
        [Column()]
        public int Lat { get; set; }
    }
    
    [Table]
    public class NodeProperty
    {
        [Column()]
        public int NodeId { get; set; }
    
        [Column(DbType = "NVarChar(255) NOT NULL")]
        public int Key { get; set; }
    
        [Column(DbType = "NVarChar(255) NOT NULL")]
        public int Value { get; set; }
    }
    
    [Table]
    public class RoadProperty
    {
        [Column()]
        public int RoadId { get; set; }
    
        [Column(DbType = "NVarChar(255) NOT NULL")]
        public int Key { get; set; }
    
        [Column(DbType = "NVarChar(255) NOT NULL")]
        public int Value { get; set; }
    }
    
    [Table]
    public class Road
    {
        [Column(IsPrimaryKey = true)]
        public int Id { get; set; }
    }
    
    [Table]
    public class RoadNode
    {
        [Column()]
        public int RoadId { get; set; }
    
        [Column()]
        public int NodeId { get; set; }
    }
    
    [Table]
    public class StringData
    {
        [Column(IsPrimaryKey = true)]
        public int Id { get; set; }
    
        [Column(DbType = "NVarChar(255) NOT NULL")]
        public String String { get; set; }
    }
    
  5. I began by going to theInsertOnSubmitTour() with aSubmitChanges() sometimes, but that seems to be too slowly asSubmitChanges() row by row insertion. So I tried it after that.SqlBulkCopy It, it seems, does not function for SQL Server Compact Edition. As a result, I ended up with SqlCeBulkCopy, which, although seeming to be quicker, is still sluggish.

The two issues I have with this approach are as follows:

  1. Still moving rather slowly.

  2. The size that results is much larger. Remember thatBelgium.osm.pbf only has 80 MB. The.sdf Is there anything I can do about this because it looks to be 592 MB?

Here are my inquiries:

  1. Where did I totally screw up? Instead, what should I do?

    It's pretty strange to me that processing an 80 MB file correctly is so difficult. Please be aware that I am currently doing all of this computing on my computer. Once it works well on the computer, I will test it out on the Windows Phone 7.

  2. Would it make sense to create an indexed PBF if there truly isn't a practical LINQ solution?

    However, doing so forces me to rediscover what a database could already do for me.

  3. Would it be logical to expand the size on my machine, effectively creating a writing converter, and then sending the 592 MB.sdf to my phone a database file?

    This seems to be a last-ditch effort that falls between options 1 and 2, but it prevents the program from being uploaded to MarketPlace since it is unpleasant to have to convert on the computer first and then find a way to get it to the phone.

If that is shown to be impossible, I concentrate on issue 1 and the other issues are essentially answers. I'm only missing something that would make things work smoothly, but I have no clue what it is.

1
5
10/3/2014 8:30:15 PM

Popular Answer

Utilizing a database for this makes logical. Keep in mind that all data in SQL CE is unicode as well as the possibility that the size is related to how small the pbf file is. What is sluggish is not obvious in your inquiry. After the import, you may also attempt compacting the database file; this can make the file somewhat smaller. Your.xap could still be tiny enough for MarketPlace based on the final size. (Because the.xap also zips the sdf file)

1
2/6/2012 10:27:53 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow