Efficient database searching
View Patent ↗Systems and methods are disclosed for efficiently searching a database including a table. The table is partitioned along a first dimension, such as latitude. Indexes in each of the partitions are built along a second dimension, such as longitude. The table may also be clustered according to an index to improve performance. When a processor receives a database query term and a starting location, the processor selects, from the table, a data partition within a first offset to the starting location along the first dimension. The processor selects, from the selected data partition, a first number of first data records related to the query term and within a second offset to the starting location along the second dimension.
1. A non-transitory storage medium configured to store a database, comprising:
a first table of records, the first table of records including a data object comprising:
a first data object attribute associated with a first coordinate in a first dimension along a first geographic reference coordinate;
a second data object attribute associated with a first coordinate in a second dimension along a second geographic reference coordinate; and
a third data object attribute that includes identification information of the data object;
wherein a first primary key of the first table combines the first data object attribute, the second data object attribute, and the third data object attribute;
wherein the first table of records includes a plurality of data objects partitioned into a plurality of data partitions corresponding to ranges of coordinate offsets along the first dimension; and
a second table of records comprising:
a second primary key comprising matching identification information corresponding to the third data object attribute and further comprising location based information related to at least one of the first coordinate in the first dimension associated with the first data object attribute or the first coordinate in the second dimension associated with the second data object attribute.
2. The non-transitory storage medium of claim 1 ,
wherein the second table of records includes location-based services (LBS) information to be displayed on a display screen.
3. The non-transitory storage medium of claim 2 , wherein the first dimension comprises latitude.
4. The non-transitory storage medium of claim 1 , wherein at least one record of a first partition of the first table is selected for storage in a disk block based upon a first index in the second dimension.
5. The non-transitory storage medium of claim 1 , wherein the first dimension is one of a latitude, a longitude, or a distance from a reference location.
6. The non-transitory storage medium of claim 1 , wherein the first primary key begins with the coordinate in the second dimension.
7. A computing device comprising a processor configured to:
receive a request to access a database comprising a first table of records and a second table of records, wherein the request comprises a query term and a starting location, the first table of records including a data object comprising: a first data object attribute associated with a first coordinate in a first dimension along a first geographic reference coordinate, a second data object attribute associated with a first coordinate in a second dimension along a second geographic reference coordinate, and a third data object attribute that includes identification information of the data object, wherein the first table of records includes a plurality of data objects partitioned into a plurality of data partitions corresponding to ranges of coordinate offsets along the first dimension;
select, from the plurality of data partitions included in the first table of records, a data partition within a first offset to a coordinate along the first dimension identified in the starting location; and
select, from the selected data partition, a first number of first records identifying location based information related to the query term and corresponding to a location within a second offset to a coordinate along the second dimension identified in the starting location,
wherein a first primary key of the first table of records combines the first data object attribute, the second data object attribute, and the third data object attribute; and
wherein the second table of records comprises a second primary key comprising matching identification information corresponding to the third data object attribute and further comprising location based information related to at least one of the first coordinate in the first dimension associated with the first data object attribute or the first coordinate in the second dimension associated with the second data object attribute.
8. The computing device of claim 7 , wherein the second table of records includes location-based services (LBS) information to be displayed on a display screen.
9. The computing device of claim 8 , wherein the processor is configured to select, from the second table of records, a second number of second data records based on the first number of first records selected from the first table of records.
10. The computing device of claim 7 , wherein at least one record of a first partition of the first table is selected for storage in a disk block based upon a first index in the second dimension.
11. The computing device of claim 7 , wherein the processor is configured to increase at least one of the first and second offsets and repeat the selections when the first number of first records is less than a preset number.
12. The computing device of claim 7 , wherein the first primary key starts with the coordinate in the second dimension.
13. The computing device of claim 3 , wherein the first dimension is one of a latitude, a longitude, or a distance from a reference location.
14. A computer implemented method, comprising:
receiving, by a processor, a request to access a database comprising a first table of records and a second table of records, wherein the request comprises query term and a starting location, the first table of records including a data object comprising: a first data object attribute associated with a first coordinate in a first dimension along a first geographic reference coordinate, a second data object attribute associated with a first coordinate in a second dimension along a second geographic reference coordinate, and a third data object attribute that includes identification information of the data object, wherein the first table of records includes a plurality of data objects partitioned into a plurality of data partitions corresponding to ranges of coordinate offsets along the first dimension;
selecting, by the processor, a data partition from the plurality of data partitions included in the first table of records within a first offset to a coordinate along the first dimension identified in the starting location; and
selecting, from the selected data partition, a first number of first records identifying location based information related to the query term and corresponding to a location within a second offset to a coordinate along the second dimension identified in the starting location,
wherein a first primary key of the first table of records combines the first data object attribute, the second data object attribute, and the third data object attribute; and
wherein the second table of records comprises a second primary key comprising matching identification information corresponding to the third data object attribute and further comprising location based information related to at least one of the first coordinate in the first dimension associated with the first data object attribute or the first coordinate in the second dimension associated with the second data object attribute.
15. The method of claim 14 , wherein the second table of records includes location-based services (LBS) information to be displayed on a display screen.
16. The method of claim 15 , further comprising selecting, from the second table of records, a second number of second data records based on the first number of first records selected from the first table of records.
17. The method of claim 14 , further comprising increasing at least one of the first and second offsets and repeat the selections when the first number of first records is less than a preset number.
18. The method of claim 14 , wherein the first dimension is one of a latitude, a longitude, or a distance from a reference location.