Thread: OpenSource Map Vector Data

Originally Posted by s003apr

I initially tried my projects with mysql and postgresql. In many ways they are overkill for this project from both a administration and features standpoint. Gigabase and sqlite both of support for limited subsets of SQL, however they do not have the heavy admin burden, they are small and crossplatform.

I've used sqlite in several applications (including my mp3 player). I highly recommend it.

I like where this thread is headed

Does Sqlite have spatial extensions?

My SF registration was denied because of some small details. Now I had to submit a trouble ticket to get excepted, so it will be a little bit longer before it is up. I will going on vacation over the next week and a half, so will delay things further. I had hoped to have something up before I left.

In the meantime I was wondering if someone would be interested in doing some proof of concepts with sqlite or mysql. I can help in understanding the Tiger data if necessary. I also have a useful script for downloading entire states of tiger data. Personally I am torn between wanting to continue on my progress with Gigabase and improve upon it, and trying other solutions to find out if they would work better, so if I could get someone else to help with those that would be really helpful because then I would not have to completely learn another databases interface. Perhaps we can come up with a common benchmark test: ie. store this data from this region, and query for these subregions. The two main goals are fast access with small file size.

There are lots of things I like about Gigabase and a few that I do not (my file size turned out to be nearly twice as large as I had expected).

As for spatial queries with sqlite I think that you may be able to work around this. For example we may be able to just store a midpoint for a line feature, then we just query the midpoint latitude column for a certain range and the midpoint longitude column for a certain range. A small analysis I did for a large single county showed that all roads in that county were under 2 miles long and 98% were under one mile. So we could for example expand the search window when querying by >0.5 miles in all directions and split roads over one mile in length and then you would be guaranteed to get all the roads within a certain area of interest.

Sourceforge project page is up:
http://sourceforge.net/projects/navdb

This would be great! Finally!

s003apr,

Tell me how I can help you. Regarding storing the midpoint of features and fetching everything within 'x miles' of your location, yes you can do that. But it is a linear search. The advantage that spatial databases give you is that the select takes log(n) amount of time, n being the number of rows in your database. This becomes significant when you do routing. In the A-star search, you have to repeatedly fetch adjoining line segments.

I have been reading up on the R-Tree algorithm. Maybe we can bypass the database dependency altogether. Just a thought...

Originally Posted by sumit_b

s003apr,

Tell me how I can help you. Regarding storing the midpoint of features and fetching everything within 'x miles' of your location, yes you can do that. But it is a linear search. The advantage that spatial databases give you is that the select takes log(n) amount of time, n being the number of rows in your database. This becomes significant when you do routing. In the A-star search, you have to repeatedly fetch adjoining line segments.

I have been reading up on the R-Tree algorithm. Maybe we can bypass the database dependency altogether. Just a thought...

By linear search I assume that you mean it is a scan through every record without using an index? That is not what I had in mind. SQLite may not even be able to do it, but I know that in Postgres and Mysql you can have an index across multiple columns:
http://sunsite.mff.cuni.cz/MIRRORS/f...n_indexes.html
So we would use this and have two columns: "MidLat" and "MidLon".

Does anybody know if sqlite can have an index across multiple columns?

As far as implementing our own database, I am willing to work with anybody who has the technical abilities to do so. I am a rather amatuerish programmer which is why I tried to work on ssomething that involved using a database and not trying to make my own database app.

This is why it is important to get inputs from someone with more programming knowledge and who would use the database.

Once the script I have built gets into cvs, maybe you can compile it and give me some ideas on what will/will not work well.

I leave on vacation for a week tomorow. So I won't get much work done for awhile.

What about curves in roads and polygons? Speaking of polygons, have you figured out how to extract that information from the TIGER files? US Census Bureau is in the process of increasing the accuracy of its data. They have a new 2004 set out which is the first step in this process. This is good news for us, because with the current accuracy (or lack thereof), routing is pretty unreliable.