In-memory spatial database for geocoding/geoprocessing

• US 8,996,545 B2
• Filed: 11/13/2012
• Issued: 03/31/2015
• Est. Priority Date: 05/02/2012
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP SE
• Inventors: Chen, Yingyu, Dong, Bin, Shen, Yongyuan
• Primary Examiner(s): LE, HUNG D
• Application Number:
  US13/675,871
• Publication Number:
  US 20130297556A1
• Time in Patent Office: 868 Days
• Field of Search: None
• US Class Current: 707/756
• CPC Class Codes: G06F 16/00 Information retrieval; Database structures therefor; File system structures therefor
• CPC Class Codes: G06F 16/29 Geographical information databases

Abstract

The embodiments provide an in-memory database system having an extraction module configured to extract data (e.g., business data and address data) from one or more external data sources and transform the data into a standard format, a geocoder configured to geocode the address data including obtaining spatial data based on the address data using an internal reference table, and an internal database configured to store the internal reference table, the business data, the address data, and the spatial data.

Claims

1. A system including instructions recorded on a non-transitory computer-readable medium and executable by at least one processor, the system comprising:

an in-memory database configured to interface with an application server hosting one or more web services, the in-memory database configured to perform extract, transform, and load (ETL) operations to collect data from one or more external data sources and perform geocoding and geoprocessing on the data such that the geocoding and geoprocessing are not performed at the application server but rather at a database level represented by the in-memory database, the in-memory database including,

an extraction module configured to cause the at least one processor to perform the ETL operations including extracting the data from the one or more external data sources, and transforming the data into a standard format associated with the in-memory database, the data including business data and address data;

a geocoder configured to cause the at least one processor to geocode the address data including obtaining spatial data based on the address data using an internal reference table stored in the in-memory database, the spatial data including geographical coordinate data corresponding to the address data; and

a geoprocessing module configured to cause the at least one processor to geographically process the spatial data including calculating geographical topology information using the geocoded address data including the geographical coordinate data; and

a storage configured to store the spatial data and the business data.

2. The system of claim 1, wherein the extraction module is configured to extract the data from the one or more external data sources in real-time via one or more real-time jobs.

3. The system of claim 1, wherein the extraction module is configured to extract the data from the one or more external data sources in time intervals via one or more batch jobs.

4. The system of claim 1, wherein the extraction module includes:

a data quality unit configured to clean and merge the data into the standard format.

5. The system of claim 1, wherein the data from the one or more external data sources includes data having different formats.

6. The system of claim 1, wherein the geocoder configured to geocode the address data includes:

a job status monitor configured to monitor a status of the ETL operations of by the extraction module,

the job status monitor configured to determine that the address data is contained in the extracted data,

wherein the geocoder is configured to query the internal reference table using the address data as determined by the job status monitor, determine matching address data in the internal reference table, and obtain corresponding spatial data from the internal reference table.

7. The system of claim 1, wherein the spatial data includes longitude and latitude information corresponding to the address data.

8. The system of claim 1, wherein the storage of the in-memory database includes a column-based storage, a row-based storage, and an object-based storage, the spatial data being stored in the column-based storage.

9. The system of claim 1, wherein the geoprocessing module is configured to geographically process the spatial data according to spatial procedure information including performing one or more spatial queries on the spatial data according to the spatial procedure information.

10. The system of claim 9, wherein the geoprocessing module is configured to transmit results of the one or more spatial queries to an outside geographic information system (GIS) application for further processing.

11. The system of claim 1, wherein the geoprocessing module, the extraction module, the geocoder, and the storage are located in a same database tier.

12. The system of claim 1, wherein the in-memory database further includes:

a modeling module configured to create database views for the business data and the spatial data stored in the storage, the database views including at least one of an attribute view, analytic view and calculation view.

13. The system of claim 12, wherein the modeling module is configured to transmit one or more database views to a user interface component for display.

14. The system of claim 1, wherein the spatial data includes earth based real surface feature information, earth based geographical coordinate information, world map information, and regional map information corresponding to the address data.

15. The system of claim 1, wherein calculating geographical topology information includes calculating intersection points between one or more real surface features and a specific line using the geocoded address data including the geographical coordinate data.

16. A method for geocoding address data by an in-memory database, the method comprising:

performing, by an in-memory database, extract, transform, and load (ETL) operations, on data from one or more external data sources including transforming the data into a standard format associated with the in-memory database;

triggering, by the in-memory database, a geocoding process if address data is discovered within the data;

geocoding, by the in-memory database, the address data including obtaining spatial data based on the address data using a geographical information system (GIS) table stored in the in-memory database such that the geocoding is performed at a database level represented by the in-memory database as opposed to an application server, the spatial data including geographical coordinate data corresponding to the address data;

geographically processing, by the in-memory database, the spatial data including calculating geographical topology information using the geocoded address data including the geographical coordinate data such that the geographically processing is performed at the database level as opposed to the application server; and

storing the spatial data and the data in a storage of the in-memory database.

17. The method of claim 16, wherein the performing the ETL operations include extracting the data from the one or more external data sources in real-time via one or more real-time jobs.

18. The method of claim 16, wherein the performing the ETL operations include extracting the data from the one or more external data sources in time intervals via one or more batch jobs.

19. The method of claim 16, wherein the geographically processing includes geographically processing the spatial data according to spatial procedure information including performing one or more spatial queries on the spatial data according to the spatial procedure information.

20. The method of claim 16, further comprising:

creating, by the in-memory database, database views for the data and the spatial data stored in the storage of the in-memory database, the database views including at least one of an attribute view, analytic view and calculation view.

21. A computer program product tangibly embodied on a non-transitory computer-readable storage medium and including executable code that, when executed, is configured to cause at least one processor to:

perform, by an in-memory database, extract, transform, and load (ETL) operations on business data from one or more data sources including executable code to extract the business data from the one or more external data sources and transform the business data into a format associated with the in-memory database;

trigger, by the in-memory database, a geocoding process if address data is discovered within the extracted business data;

geocode, by the in-memory database, the address data including obtaining spatial data based on the address data using a map reference table stored in the in-memory database such that the geocoding is performed at a database level represented by the in-memory database as opposed to an application server, the spatial data including geographical coordinate data corresponding to the address data; and

perform, by the in-memory database, geoprocessing on the business data and the spatial data jointly such that correlations between locations and the business data are derived, the geoprocessing being performed at the database level as opposed to the application server.

22. The computer program product of claim 21, wherein the business data includes sales data associated with existing locations, and the derived correlations predict one or more new locations based on the sales data.