I am designing a database to store geo-position. I want to implement similar function to Google Map. The usage scenario is, I have a large number of points, and related X, Y position. The database is seldom update (e.g. adding new points or modify X,Y position for existing points), but query frequently. The query scenario is, for a given square (the square's 4 corner points X,Y positions are known), find all points and related X,Y positions in the square.
I am wondering how to design database so that query performance is optimized. My design issue is very similar to map database design. I am also wondering whether Google Map or traditional map database is implemented to achieve the best performance?
I am new to the area of map database design, appreciate if anyone could refer some tutorials for newbie.
thanks in advance, George
George, you'll find your positions with
where (geoX between %x1 and %x2) and (geoY between %Y1 and %y2)
Regarding indexing, since you probably always will query for both X and Y a single index will do: idx_XY (geoX, geoY)
If any need to search on just Y, add second index
idx_Y (geoY)
(And I'd rename X / Y to long/lat, but that's more or less a personal matter, ;-)
regards, /t