1.15 Location Data Enrichment

Oracle Spatial includes a place name data set, with hierarchical geographical data from HERE, that you can load into the database.

You can then then search this place name data set using the SDO_UTIL.GEO_SEARCH function. The data set includes commonly used textual location data such as place names, addresses and partial addresses, and latitude and longitude information.

Location tags are extracted from text data, and are matched with well known place names using Oracle Text and enhanced with other geographic information associated with the well known place names.

The results can be stored as additional attributes with the original data.

This feature enables you to process less structured geographic and location data so that the information can be categorized, compared, filtered, and associated with other data. For example, data with only partial names can be enriched to include city, county, state, and country, allowing it to be joined or analyzed with other data sets that may have state level information. This is especially useful when comparing Big Data results with structured information in operational systems and data warehouses.

Setting Up and Using Location Data Enrichment Support

To use the location data enrichment support, you just perform certain setup actions, such as editing scripts that will create the necessary database objects and load the data set into Oracle Database, and running those scripts.

1. Go to $ORACLE_HOME//md/demo/GeoSearch, which contains all the required files.

2. Read the README file, a text file containing an overview of the basic steps.

3. Perform the actions indicated in the README file.

   These actions include reading the LICENSES.TXT file, creating a single zip file from split files, editing the load_data.sql and create_index.sql script files (which contain explanatory comments), and running those scripts.

The create_index.sql file includes some example queries using the SDO_UTIL.GEO_SEARCH function. You can use those examples, plus the SDO_UTIL.GEO_SEARCH reference and usage information, to develop your own uses of the location data enrichment support.

• ELOC_ADMIN_AREA_SEARCH Table
  
• Adding User Data to the Geographic Name Hierarchy
  

1.15.1 ELOC_ADMIN_AREA_SEARCH Table

The ELOC_ADMIN_AREA_SEARCH table is used to store the data for location data enrichment. It is created only if you have performed the required setup actions described in Location Data Enrichment, and it is created in the database schema that you chose.

This table is accessed by the SDO_UTIL.GEO_SEARCH procedure. The table has the following columns.

Table 1-3 LOC_ADMIN_AREA_SEARCH Table

Column Name	Description
AREA_ID	Unique ID for the place name.
FULL_NAME	The name of the place as a searchable string. For example, “NASHUA,HILLSBOROUGH,NEW HAMPSHIRE,NH,UNITED STATES,USA” is the searchable name for the city of Nashua in NH, USA. This entry is a concatenated list of all possible names for each level of the name hierarchy. That is, for state it can have both the abbreviation and the full name. Similarly, for country it can have both the abbreviation and the full name. This enables the search to find this entry even when different search terms are used for each of these administrative areas.
AREA_NAME	The actual area name of the administrative place.
KEY	A standardized text key that is returned from the search API. This is a normalized standard key that can be used for joining the search term with other terms.
LANG_CODE	3- letter ISO code of the language used for this entry.
PART_ID	A number that is used when this table is partitioned (see the README for more details).
CENTER_LONG	Longitude of the place name.
CENTER_LAT	Latitude of the place name.
POPULATION	A number that is used to order the results when multiple matches are found for a given search term. The intent is to return more populated areas first before retuning less populated areas where multiple matches are found for the same search term.

1.15.2 Adding User Data to the Geographic Name Hierarchy

In some cases, users might want to add their own data to augment the data provided by Oracle. For example, if the users wants to create an entry for a park in the city (like Central Park in New York City) they can create an entry for it in this table.

For example, they can do :

insert into ELOC_ADMIN_AREA_SEARCH values (1469286010, 'CENTRAL PARK,NEW YORK CITY,NEW YORK,NYC,RICHMOND,NEW YORK,NY,UNITED STATES,USA', 'CENTRAL PARK', 'CENTRAL PARK|NEW YORK|RICHMOND|NEW YORK|UNITED STATES', 'ENG', 7, 73.9654,40.7829, 0);
commit;

The COMMIT statement after inserting new data is important, because the text index performs a synchronization only after the commit is issued.

In this example, the area_id is chosen to be some value that does not already exist in the table, and a random partition_id value is used (7 in this case). However, a suitable value should be chosen based on the partitioning scheme used for the table (see the README for more details).

Now a search for central park will result a match:

select * from table(sdo_util.geo_search('central park,new york,NY,UNITED STATES'));
CENTRAL PARK
CENTRAL PARK|NEW YORK|RICHMOND|NEW YORK|UNITED STATES
ENG	73.9654    40.7829	  100