Demo1-Keyword-search.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ce0b0c8a",
   "metadata": {},
   "source": [
    "### Semantic search vs  Keywords Search \n",
    "Semantic search seeks to improve search accuracy by understanding the content of the search query. In contrast to traditional search engines which only find documents based on lexical matches, semantic search can also find synonyms.\n",
    "\n",
    "Its advantages include: \n",
    "- Content understanding (attention mechanism) \n",
    "- Handling of Natural language queries and complex queries \n",
    "- Dealing with synonyms and variations \n",
    "- Improved accuracy\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f6b9e48",
   "metadata": {},
   "source": [
    "### Fine-tuning vs Pretrain\n",
    "Fine-tuning is the process of adjusting a pre-trained model (a model trained on a large dataset to learn general features) for a specific task by continuing its training on a new, often smaller dataset.\n",
    "\n",
    "This approach leverages the model's learned knowledge, reducing the need for extensive data and computational power for new tasks. Pre-trained models serve as adaptable starting points for various machine learning tasks, enabling efficient model development without starting from scratch.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "141f5f21",
   "metadata": {},
   "source": [
    "### Amazon OpenSearch \n",
    "\n",
    "Amazon OpenSearch Service (formerly known as Amazon Elasticsearch Service) is a managed service offered by AWS that makes it easy to deploy, operate, and scale OpenSearch, an open-source search and analytics suite.\n",
    "\n",
    "Its advantages include: \n",
    "- Managed services, secure, and scalable \n",
    "- Supports semantic search, keywords search, hybrid search, etc. \n",
    "- Provides search relevance sorting \n",
    "- Supports geospatial search and indexing \n",
    "- Provide features such as paginate, sort, autocomplete, did-you-mean \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae602552",
   "metadata": {},
   "source": [
    "### Amazon SageMaker \n",
    "Amazon SageMaker is a fully managed service provided by AWS to quickly and easily build, train, and deploy machine learning (ML) models at scale. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4790111b",
   "metadata": {},
   "source": [
    "### Keyords Search with Amazon OpenSearch"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de79eb73",
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip install -q boto3\n",
    "# !pip install -q requests\n",
    "# !pip install -q requests-aws4auth\n",
    "# !pip install -q opensearch-py\n",
    "# !pip install -q tqdm\n",
    "# !pip install -q boto3\n",
    "# !pip install -q install transformers[torch]\n",
    "# !pip install -q transformers\n",
    "# !pip install -q sentence-transformers rank_bm25\n",
    "# !pip install -q pandas "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91b1288e",
   "metadata": {},
   "source": [
    "### 1. Get Cloud Formation stack output variables\n",
    "\n",
    "We also need to grab some key values from the infrastructure we provisioned using CloudFormation. To do this, we will list the outputs from the stack and store this in \"outputs\" to be used later.\n",
    "\n",
    "You can ignore any \"PythonDeprecationWarning\" warnings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c6506c8e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'OpenSearchDomainEndpoint': 'search-semantic-search-dfcizxxxuj62dusl5skmeu3czu.ca-central-1.es.amazonaws.com',\n",
       " 'Region': 'ca-central-1',\n",
       " 'OpenSearchDomainName': 'semantic-search',\n",
       " 'OpenSearchSecret': 'arn:aws:secretsmanager:ca-central-1:006288227511:secret:OpenSearchSecret-semantic-search-with-opensearch-dFOH1b'}"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import boto3\n",
    "import json\n",
    "\n",
    "cfn = boto3.client('cloudformation')\n",
    "kms = boto3.client('secretsmanager')\n",
    "\n",
    "\n",
    "def get_cfn_outputs(stackname):\n",
    "    outputs = {}\n",
    "    for output in cfn.describe_stacks(StackName=stackname)['Stacks'][0]['Outputs']:\n",
    "        outputs[output['OutputKey']] = output['OutputValue']\n",
    "    return outputs\n",
    "\n",
    "## Setup variables to use for the rest of the demo\n",
    "cloudformation_stack_name = \"semantic-search-with-opensearch\"\n",
    "\n",
    "outputs = get_cfn_outputs(cloudformation_stack_name)\n",
    "aos_host = outputs['OpenSearchDomainEndpoint']\n",
    "aos_credentials = json.loads(kms.get_secret_value(SecretId=outputs['OpenSearchSecret'])['SecretString'])\n",
    "\n",
    "outputs\n",
    "#print(aos_credentials)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ad4b89e",
   "metadata": {},
   "source": [
    "### 2. Create an OpenSearch cluster connection.\n",
    "Next, we'll use OpenSearch Python SDK to set up connection with Amazon Opensearch Service domain."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e7399383",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<OpenSearch([{'host': 'search-semantic-search-dfcizxxxuj62dusl5skmeu3czu.ca-central-1.es.amazonaws.com', 'port': 443}])>\n"
     ]
    }
   ],
   "source": [
    "from opensearchpy import OpenSearch,RequestsHttpConnection\n",
    "import boto3\n",
    "\n",
    "#update the region if you're working other than us-east-1\n",
    "region = 'ca-central-1' \n",
    "\n",
    "# #Alternatively, auth can be get using the AWS4SignerAuth library\n",
    "# from opensearchpy import AWSV4SignerAuth\n",
    "# credentials = boto3.Session().get_credentials()\n",
    "# auth = AWSV4SignerAuth(credentials, region)\n",
    "\n",
    "auth = (aos_credentials['username'], aos_credentials['password'])\n",
    "aos_client = OpenSearch(\n",
    "    hosts = [{'host': aos_host, 'port': 443}],\n",
    "    http_auth = auth,\n",
    "    use_ssl = True,\n",
    "    verify_certs = True,\n",
    "    connection_class = RequestsHttpConnection\n",
    ")\n",
    "print(aos_client)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99155e61",
   "metadata": {},
   "source": [
    "### 3. Create a index in Opensearch Service \n",
    "We will use the aos_client connection we initiated ealier to create an index in Amazon OpenSearch Service. \n",
    "\n",
    "An OpenSearch index includes components such as Documents (json format), Aliases, Analyzers (part of settings), Templates, shards and replicas (part of settings) . \n",
    "In the index, an English analyzer is used to strip the common stopwords like `the`, `is`, `a`, `an`, etc.\n",
    "- OpenSearch [Index Setting](https://opensearch.org/docs/latest/install-and-configure/configuring-opensearch/index-settings/) \n",
    "- OpenSearch [Index Analyzer](https://opensearch.org/docs/latest/analyzers/index-analyzers/):Index analyzers are specified at indexing time and are used to analyze text fields when indexing a document.Analyzers define how text data is processed before indexing, including tokenization and the application of token filters\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab84c82e",
   "metadata": {},
   "source": [
    "### Create a index with dynamic mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "83ef268b",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Static index-level index settings: https://opensearch.org/docs/latest/install-and-configure/configuring-opensearch/index-settings/#static-index-level-index-settings)\n",
    "index_name = 'search'\n",
    "metadata_default_index = {\n",
    "    \"settings\": {\n",
    "        \"number_of_replicas\": 1,\n",
    "        \"number_of_shards\": 1,\n",
    "        \"analysis\": {\n",
    "          \"analyzer\": {\n",
    "            \"default\": {\n",
    "              \"type\": \"standard\",\n",
    "              \"stopwords\": \"_english_\"\n",
    "            }\n",
    "          }\n",
    "        }\n",
    "    }\n",
    "   \n",
    "}\n",
    "\n",
    "# Retrieve cluster settings including default settings\n",
    "response = aos_client.cluster.get_settings(include_defaults=True)\n",
    "print(response)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "554abaf7",
   "metadata": {},
   "source": [
    "If the index is already exist, we will delete the index and recreate "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "85a3defc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Current indexes: ['nlp_knn', '.opensearch-observability', '.plugins-ml-config', 'keyword_search', 'search', '.ql-datasources', '.opendistro_security', '.kibana_1']\n",
      "Deleted index: search\n",
      "Response: {'acknowledged': True}\n",
      "Indexes after deletion attempt: ['nlp_knn', '.opensearch-observability', '.plugins-ml-config', 'keyword_search', '.ql-datasources', '.opendistro_security', '.kibana_1']\n"
     ]
    }
   ],
   "source": [
    "from opensearchpy import OpenSearch\n",
    "\n",
    "def delete_index_if_exists(aos_client, index_to_delete):\n",
    "    \"\"\"\n",
    "    Deletes the specified index if it exists.\n",
    "\n",
    "    :param aos_client: An instance of OpenSearch client.\n",
    "    :param index_to_delete: The name of the index to delete.\n",
    "    \"\"\"\n",
    "    # List all indexes and check if the specified index exists\n",
    "    all_indices = aos_client.cat.indices(format='json')\n",
    "    existing_indices = [index['index'] for index in all_indices]\n",
    "    print(\"Current indexes:\", existing_indices)\n",
    "\n",
    "    if index_to_delete in existing_indices:\n",
    "        # Delete the specified index\n",
    "        try:\n",
    "            response = aos_client.indices.delete(index=index_to_delete)\n",
    "            print(f\"Deleted index: {index_to_delete}\")\n",
    "            print(\"Response:\", response)\n",
    "        except Exception as e:\n",
    "            print(f\"Error deleting index {index_to_delete}:\", e)\n",
    "    else:\n",
    "        print(f\"Index {index_to_delete} does not exist.\")\n",
    "\n",
    "    # List all indexes again to confirm deletion\n",
    "    all_indices_after_deletion = aos_client.cat.indices(format='json')\n",
    "    existing_indices_after_deletion = [index['index'] for index in all_indices_after_deletion]\n",
    "    print(\"Indexes after deletion attempt:\", existing_indices_after_deletion)\n",
    "\n",
    "delete_index_if_exists(aos_client, index_to_delete=index_name)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "983d95cf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'acknowledged': True, 'shards_acknowledged': True, 'index': 'search'}"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Create a index \n",
    "aos_client.indices.create(index=index_name,body=metadata_default_index,ignore=400)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cabf093d",
   "metadata": {},
   "source": [
    "Let's verify the created index information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "fbb4cabb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'search': {'aliases': {},\n",
       "  'mappings': {},\n",
       "  'settings': {'index': {'replication': {'type': 'DOCUMENT'},\n",
       "    'number_of_shards': '1',\n",
       "    'provided_name': 'search',\n",
       "    'creation_date': '1710198966505',\n",
       "    'analysis': {'analyzer': {'default': {'type': 'standard',\n",
       "       'stopwords': '_english_'}}},\n",
       "    'number_of_replicas': '1',\n",
       "    'uuid': 'k-7zDllvRDepbKqEfTsxUg',\n",
       "    'version': {'created': '136327827'}}}}}"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aos_client.indices.get(index=index_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c02532a",
   "metadata": {},
   "source": [
    "### Alternatively, create a index with explicit mapping \n",
    "Amazon OpenSearch is default for dynamic mapping. For certain OpenSearch features, like aggregations, sorting, or specific queries (e.g., geo_shape queries), require fields to be indexed in a particular way. Pre-defining mappings (e.g., explicit mapping) ensures these features will work as expected. \n",
    "\n",
    "In our case, we would like to explicit mapping the \"features_geometry_coordinates\" as a geo_shape\n",
    "- [OpenSearch Mapping](https://opensearch.org/docs/latest/field-types/)\n",
    "- [Supported field types](https://opensearch.org/docs/latest/field-types/supported-field-types/index/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "b3cad293",
   "metadata": {},
   "outputs": [],
   "source": [
    "index_name = 'search'\n",
    "metadata_default_index = {\n",
    "    \"settings\": {\n",
    "        \"number_of_replicas\": 1,\n",
    "        \"number_of_shards\": 1,\n",
    "        \"analysis\": {\n",
    "          \"analyzer\": {\n",
    "            \"default\": {\n",
    "              \"type\": \"standard\",\n",
    "              \"stopwords\": \"_english_\"\n",
    "            }\n",
    "          }\n",
    "        },\n",
    "        \"index\": {#default relevancy score is BM25: https://opensearch.org/docs/latest/search-plugins/keyword-search/\n",
    "            \"similarity\": {\n",
    "                \"custom_similarity\": {\n",
    "                    \"type\": \"BM25\",\n",
    "                    \"k1\": 1.2,\n",
    "                    \"b\": 0.75,\n",
    "                    \"discount_overlaps\": \"true\"\n",
    "                }\n",
    "            }\n",
    "        }\n",
    "    },\n",
    "    \"mappings\": {\n",
    "        \"properties\": {\n",
    "            \"coordinates\":{\n",
    "              \"type\": \"geo_shape\", \n",
    "              \"store\": True \n",
    "            }\n",
    "        }\n",
    "    }\n",
    "}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "dccfcf46",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Current indexes: ['nlp_knn', '.opensearch-observability', '.plugins-ml-config', 'keyword_search', 'search', '.ql-datasources', '.opendistro_security', '.kibana_1']\n",
      "Deleted index: search\n",
      "Response: {'acknowledged': True}\n",
      "Indexes after deletion attempt: ['nlp_knn', '.opensearch-observability', '.plugins-ml-config', 'keyword_search', '.ql-datasources', '.opendistro_security', '.kibana_1']\n"
     ]
    }
   ],
   "source": [
    "#delete_index_if_exists(aos_client, index_to_delete=index_name)\n",
    "#aos_client.indices.create(index=index_name,body=metadata_default_index,ignore=400)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c98b7cb4",
   "metadata": {},
   "source": [
    "List all the indexes in the OpenSearch domain \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "id": "673f0b2f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['nlp_knn', '.opensearch-observability', '.plugins-ml-config', 'search', 'keyword_search', '.ql-datasources', '.opendistro_security', '.kibana_1']\n"
     ]
    }
   ],
   "source": [
    "all_indices = aos_client.cat.indices(format='json')\n",
    "print([index['index'] for index in all_indices]) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3db9c013",
   "metadata": {},
   "source": [
    "Check the data in the index "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "id": "6d109a0d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Records loaded into the index search is 10000.\n"
     ]
    }
   ],
   "source": [
    "index_name = 'search'\n",
    "res = aos_client.search(index=index_name, body={\"query\": {\"match_all\": {}}})\n",
    "print(f\"Records loaded into the index {index_name} is {res['hits']['total']['value']}.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73590781",
   "metadata": {},
   "source": [
    "### 4. Load the raw data - records.parquet from S3 buckets \n",
    "Next, let's load the metadata (records.parquet) from the S3 bucket into the index we've just created.\n",
    "Note, raw data needs to be in JSON format "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "3cad7157",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "#import boto3\n",
    "#import json\n",
    "from requests_aws4auth import AWS4Auth\n",
    "import io"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61e74683",
   "metadata": {},
   "source": [
    "First, we will read the parquet file from S3. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "9ff992c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_parquet_from_s3_to_df(region, s3_bucket, s3_key):\n",
    "    \"\"\"\n",
    "    Load a Parquet file from an S3 bucket into a pandas DataFrame.\n",
    "\n",
    "    Parameters:\n",
    "    - region: AWS region where the S3 bucket is located.\n",
    "    - s3_bucket: Name of the S3 bucket.\n",
    "    - s3_key: Key (path) to the Parquet file within the S3 bucket.\n",
    "\n",
    "    Returns:\n",
    "    - df: pandas DataFrame containing the data from the Parquet file.\n",
    "    \"\"\"\n",
    "    \n",
    "    # Setup AWS session and clients\n",
    "    session = boto3.Session(region_name=region)\n",
    "    s3 = session.resource('s3')\n",
    "\n",
    "    # Load the Parquet file as a pandas DataFrame\n",
    "    object = s3.Object(s3_bucket, s3_key)\n",
    "    body = object.get()['Body'].read()\n",
    "    df = pd.read_parquet(io.BytesIO(body))\n",
    "    return df\n",
    "\n",
    "df = load_parquet_from_s3_to_df('ca-central-1', 'webpresence-geocore-geojson-to-parquet-dev', 'records.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "7a4a2da7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>features_type</th>\n",
       "      <th>features_geometry_type</th>\n",
       "      <th>features_geometry_coordinates</th>\n",
       "      <th>features_properties_id</th>\n",
       "      <th>features_properties_title_en</th>\n",
       "      <th>features_properties_title_fr</th>\n",
       "      <th>features_properties_description_en</th>\n",
       "      <th>features_properties_description_fr</th>\n",
       "      <th>features_properties_keywords_en</th>\n",
       "      <th>features_properties_keywords_fr</th>\n",
       "      <th>...</th>\n",
       "      <th>features_properties_distributor</th>\n",
       "      <th>features_properties_options</th>\n",
       "      <th>features_properties_temporalExtent_end_@indeterminatePosition</th>\n",
       "      <th>features_properties_temporalExtent_end_#text</th>\n",
       "      <th>features_properties_plugins</th>\n",
       "      <th>features_properties_sourceSystemName</th>\n",
       "      <th>features_properties_eoCollection</th>\n",
       "      <th>features_properties_eoFilters</th>\n",
       "      <th>features_popularity</th>\n",
       "      <th>features_similarity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Feature</td>\n",
       "      <td>Polygon</td>\n",
       "      <td>[[[-141.003, 41.6755], [-52.6174, 41.6755], [-...</td>\n",
       "      <td>000183ed-8864-42f0-ae43-c4313a860720</td>\n",
       "      <td>Principal Mineral Areas, Producing Mines, and ...</td>\n",
       "      <td>Principales régions minières, principales mine...</td>\n",
       "      <td>This dataset is produced and published annuall...</td>\n",
       "      <td>Ce jeu de données est produit et publié annuel...</td>\n",
       "      <td>mineralization, mineral occurrences, mines, hy...</td>\n",
       "      <td>minéralisation, indices minéralisés, mines, hy...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{\"individual\": \"null\", \"position\": {\"en\": \"nu...</td>\n",
       "      <td>[{\"url\": \"https://maps-cartes.services.geo.ca/...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>[]</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>[]</td>\n",
       "      <td>1250806</td>\n",
       "      <td>[{\"sim\": \"sim1\", \"features_properties_id\": \"b6...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Feature</td>\n",
       "      <td>Polygon</td>\n",
       "      <td>[[[-142, 41], [-52, 41], [-52, 84], [-142, 84]...</td>\n",
       "      <td>7f245e4d-76c2-4caa-951a-45d1d2051333</td>\n",
       "      <td>Canadian Digital Elevation Model, 1945-2011</td>\n",
       "      <td>Modèle numérique d'élévation du Canada, 1945-2011</td>\n",
       "      <td>This collection is a legacy product that is no...</td>\n",
       "      <td>Ce produit fait maintenant partie du patrimoin...</td>\n",
       "      <td>Canada, Earth Sciences, elevation, relief, geo...</td>\n",
       "      <td>Canada, Sciences de la Terre, élévation, relie...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{\"individual\": \"null\", \"position\": {\"en\": \"nu...</td>\n",
       "      <td>[{\"url\": \"https://maps.geogratis.gc.ca/wms/ele...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>[]</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>[]</td>\n",
       "      <td>210798</td>\n",
       "      <td>[{\"sim\": \"sim1\", \"features_properties_id\": \"76...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Feature</td>\n",
       "      <td>Polygon</td>\n",
       "      <td>[[[-141.003, 41.6755], [-52.6174, 41.6755], [-...</td>\n",
       "      <td>085024ac-5a48-427a-a2ea-d62af73f2142</td>\n",
       "      <td>Canada's National Earthquake Scenario Catalogue</td>\n",
       "      <td>Catalogue national de scénarios de tremblement...</td>\n",
       "      <td>The National Earthquake Scenario Catalogue, pr...</td>\n",
       "      <td>Le dépôt est utilisé pour l’élaboration du cat...</td>\n",
       "      <td>Emergency preparedness, Earth sciences, Earthq...</td>\n",
       "      <td>Protection civile, Sciences de la terre, Tremb...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{\"individual\": \"null\", \"position\": {\"en\": \"nu...</td>\n",
       "      <td>[{\"url\": \"https://github.com/OpenDRR/earthquak...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>[]</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>[]</td>\n",
       "      <td>140088</td>\n",
       "      <td>[{\"sim\": \"sim1\", \"features_properties_id\": \"4c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Feature</td>\n",
       "      <td>Polygon</td>\n",
       "      <td>[[[-104.75571511, 50.42392886], [-104.56356008...</td>\n",
       "      <td>03ccfb5c-a06e-43e3-80fd-09d4f8f69703</td>\n",
       "      <td>Temporal Series of the National Air Photo Libr...</td>\n",
       "      <td>Série temporelle de la photothèque nationale d...</td>\n",
       "      <td>Note: To visualize the data in the viewer, zoo...</td>\n",
       "      <td>Note: Pour visualiser les données dans l’outil...</td>\n",
       "      <td>Mosaic, Aerial photography, Access to informat...</td>\n",
       "      <td>Mosaïque, Photographie aérienne, Accès à l'inf...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{\"individual\": \"null\", \"position\": {\"en\": \"nu...</td>\n",
       "      <td>[{\"url\": \"https://datacube-prod-data-public.s3...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>[]</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>[]</td>\n",
       "      <td>120162</td>\n",
       "      <td>[{\"sim\": \"sim1\", \"features_properties_id\": \"23...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Feature</td>\n",
       "      <td>Polygon</td>\n",
       "      <td>[[[-141.003, 41.6755], [-52.6174, 41.6755], [-...</td>\n",
       "      <td>488faf70-b50b-4749-ac1c-a1fd44e06f11</td>\n",
       "      <td>Indigenous Mining Agreements</td>\n",
       "      <td>Ententes minières autochtones</td>\n",
       "      <td>The Indigenous Mining Agreements dataset provi...</td>\n",
       "      <td>Les données des ententes minières autochtones ...</td>\n",
       "      <td>Indigenous, First Nations, Métis, Indigenous a...</td>\n",
       "      <td>Autochtones, Premières nations, Métis, Affaire...</td>\n",
       "      <td>...</td>\n",
       "      <td>[{\"individual\": \"null\", \"position\": {\"en\": \"nu...</td>\n",
       "      <td>[{\"url\": \"https://atlas.gc.ca/imaema/en/\", \"pr...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>[]</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>[]</td>\n",
       "      <td>111036</td>\n",
       "      <td>[{\"sim\": \"sim1\", \"features_properties_id\": \"CG...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 71 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  features_type features_geometry_type  \\\n",
       "0       Feature                Polygon   \n",
       "1       Feature                Polygon   \n",
       "2       Feature                Polygon   \n",
       "3       Feature                Polygon   \n",
       "4       Feature                Polygon   \n",
       "\n",
       "                       features_geometry_coordinates  \\\n",
       "0  [[[-141.003, 41.6755], [-52.6174, 41.6755], [-...   \n",
       "1  [[[-142, 41], [-52, 41], [-52, 84], [-142, 84]...   \n",
       "2  [[[-141.003, 41.6755], [-52.6174, 41.6755], [-...   \n",
       "3  [[[-104.75571511, 50.42392886], [-104.56356008...   \n",
       "4  [[[-141.003, 41.6755], [-52.6174, 41.6755], [-...   \n",
       "\n",
       "                 features_properties_id  \\\n",
       "0  000183ed-8864-42f0-ae43-c4313a860720   \n",
       "1  7f245e4d-76c2-4caa-951a-45d1d2051333   \n",
       "2  085024ac-5a48-427a-a2ea-d62af73f2142   \n",
       "3  03ccfb5c-a06e-43e3-80fd-09d4f8f69703   \n",
       "4  488faf70-b50b-4749-ac1c-a1fd44e06f11   \n",
       "\n",
       "                        features_properties_title_en  \\\n",
       "0  Principal Mineral Areas, Producing Mines, and ...   \n",
       "1        Canadian Digital Elevation Model, 1945-2011   \n",
       "2    Canada's National Earthquake Scenario Catalogue   \n",
       "3  Temporal Series of the National Air Photo Libr...   \n",
       "4                       Indigenous Mining Agreements   \n",
       "\n",
       "                        features_properties_title_fr  \\\n",
       "0  Principales régions minières, principales mine...   \n",
       "1  Modèle numérique d'élévation du Canada, 1945-2011   \n",
       "2  Catalogue national de scénarios de tremblement...   \n",
       "3  Série temporelle de la photothèque nationale d...   \n",
       "4                      Ententes minières autochtones   \n",
       "\n",
       "                  features_properties_description_en  \\\n",
       "0  This dataset is produced and published annuall...   \n",
       "1  This collection is a legacy product that is no...   \n",
       "2  The National Earthquake Scenario Catalogue, pr...   \n",
       "3  Note: To visualize the data in the viewer, zoo...   \n",
       "4  The Indigenous Mining Agreements dataset provi...   \n",
       "\n",
       "                  features_properties_description_fr  \\\n",
       "0  Ce jeu de données est produit et publié annuel...   \n",
       "1  Ce produit fait maintenant partie du patrimoin...   \n",
       "2  Le dépôt est utilisé pour l’élaboration du cat...   \n",
       "3  Note: Pour visualiser les données dans l’outil...   \n",
       "4  Les données des ententes minières autochtones ...   \n",
       "\n",
       "                     features_properties_keywords_en  \\\n",
       "0  mineralization, mineral occurrences, mines, hy...   \n",
       "1  Canada, Earth Sciences, elevation, relief, geo...   \n",
       "2  Emergency preparedness, Earth sciences, Earthq...   \n",
       "3  Mosaic, Aerial photography, Access to informat...   \n",
       "4  Indigenous, First Nations, Métis, Indigenous a...   \n",
       "\n",
       "                     features_properties_keywords_fr  ...  \\\n",
       "0  minéralisation, indices minéralisés, mines, hy...  ...   \n",
       "1  Canada, Sciences de la Terre, élévation, relie...  ...   \n",
       "2  Protection civile, Sciences de la terre, Tremb...  ...   \n",
       "3  Mosaïque, Photographie aérienne, Accès à l'inf...  ...   \n",
       "4  Autochtones, Premières nations, Métis, Affaire...  ...   \n",
       "\n",
       "                     features_properties_distributor  \\\n",
       "0  [{\"individual\": \"null\", \"position\": {\"en\": \"nu...   \n",
       "1  [{\"individual\": \"null\", \"position\": {\"en\": \"nu...   \n",
       "2  [{\"individual\": \"null\", \"position\": {\"en\": \"nu...   \n",
       "3  [{\"individual\": \"null\", \"position\": {\"en\": \"nu...   \n",
       "4  [{\"individual\": \"null\", \"position\": {\"en\": \"nu...   \n",
       "\n",
       "                         features_properties_options  \\\n",
       "0  [{\"url\": \"https://maps-cartes.services.geo.ca/...   \n",
       "1  [{\"url\": \"https://maps.geogratis.gc.ca/wms/ele...   \n",
       "2  [{\"url\": \"https://github.com/OpenDRR/earthquak...   \n",
       "3  [{\"url\": \"https://datacube-prod-data-public.s3...   \n",
       "4  [{\"url\": \"https://atlas.gc.ca/imaema/en/\", \"pr...   \n",
       "\n",
       "  features_properties_temporalExtent_end_@indeterminatePosition  \\\n",
       "0                                               <NA>              \n",
       "1                                               <NA>              \n",
       "2                                               <NA>              \n",
       "3                                               <NA>              \n",
       "4                                               <NA>              \n",
       "\n",
       "  features_properties_temporalExtent_end_#text features_properties_plugins  \\\n",
       "0                                         <NA>                          []   \n",
       "1                                         <NA>                          []   \n",
       "2                                         <NA>                          []   \n",
       "3                                         <NA>                          []   \n",
       "4                                         <NA>                          []   \n",
       "\n",
       "  features_properties_sourceSystemName features_properties_eoCollection  \\\n",
       "0                                 <NA>                             <NA>   \n",
       "1                                 <NA>                             <NA>   \n",
       "2                                 <NA>                             <NA>   \n",
       "3                                 <NA>                             <NA>   \n",
       "4                                 <NA>                             <NA>   \n",
       "\n",
       "  features_properties_eoFilters features_popularity  \\\n",
       "0                            []             1250806   \n",
       "1                            []              210798   \n",
       "2                            []              140088   \n",
       "3                            []              120162   \n",
       "4                            []              111036   \n",
       "\n",
       "                                 features_similarity  \n",
       "0  [{\"sim\": \"sim1\", \"features_properties_id\": \"b6...  \n",
       "1  [{\"sim\": \"sim1\", \"features_properties_id\": \"76...  \n",
       "2  [{\"sim\": \"sim1\", \"features_properties_id\": \"4c...  \n",
       "3  [{\"sim\": \"sim1\", \"features_properties_id\": \"23...  \n",
       "4  [{\"sim\": \"sim1\", \"features_properties_id\": \"CG...  \n",
       "\n",
       "[5 rows x 71 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()\n",
    "#df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1abfd4e",
   "metadata": {},
   "source": [
    "Subset to columns that are required in the app.geo.ca [api response](https://geocore.api.geo.ca/geo?north=81.77364370720657&east=360&south=-8.407168163601076&west=-359.6484375&keyword=&lang=en&min=1&max=10&sort=popularity-desc). \n",
    "##### Note, we are focus on the english search at the moment. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "95e9190a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Extract organization from contact json, English only \n",
    "def extract_organisation_en(contact_str):\n",
    "    try:\n",
    "        # Parse the stringified JSON into Python objects\n",
    "        contact_data = json.loads(contact_str)\n",
    "        # If the parsed data is a list, iterate through it\n",
    "        if isinstance(contact_data, list):\n",
    "            for item in contact_data:\n",
    "                # Check if 'organisation' and 'en' keys exist\n",
    "                if 'organisation' in item and 'en' in item['organisation']:\n",
    "                    return item['organisation']['en']\n",
    "        elif isinstance(contact_data, dict):\n",
    "            # If the data is a dictionary, extract 'organisation' in 'en' directly\n",
    "            return contact_data.get('organisation', {}).get('en', None)\n",
    "    except json.JSONDecodeError:\n",
    "        # Handle cases where the contact string is not valid JSON\n",
    "        return None\n",
    "    except Exception as e:\n",
    "        # Catch-all for any other unexpected errors\n",
    "        return f\"Error: {str(e)}\"\n",
    "\n",
    "# Subset to selected columns \n",
    "col_names_list = ['features_properties_id','features_geometry_coordinates','features_properties_title_en','features_properties_description_en','features_properties_date_published_date','features_properties_keywords_en','features_properties_options','features_properties_contact','features_properties_topicCategory','features_properties_date_created_date','features_properties_spatialRepresentation','features_properties_type','features_properties_temporalExtent_begin','features_properties_temporalExtent_end','features_properties_graphicOverview','features_properties_language','features_popularity','features_properties_sourceSystemName','features_properties_eoCollection','features_properties_eoFilters']\n",
    "df_en = df[col_names_list]\n",
    "#df_en = df_en[:100]\n",
    "    \n",
    "# Create new column 'organization_en'\n",
    "df_en['organisation_en'] = df_en['features_properties_contact'].apply(extract_organisation_en)\n",
    "\n",
    "# Create a new column 'temporalExtent' as a dictionary of {'begin': ..., 'end': ...}\n",
    "values_to_replace = {'Present': None, 'Not Available; Indisponible': None}\n",
    "columns_to_replace = ['features_properties_temporalExtent_begin', 'features_properties_temporalExtent_end']\n",
    "df_en[columns_to_replace] = df_en[columns_to_replace].replace(values_to_replace)\n",
    "\n",
    "df_en['temporalExtent'] = df_en.apply(lambda row: {'begin': row['features_properties_temporalExtent_begin'], 'end': row['features_properties_temporalExtent_end']}, axis=1)\n",
    "df_en = df_en.drop(columns =['features_properties_temporalExtent_begin', 'features_properties_temporalExtent_end'])\n",
    "\n",
    "#modifies dates to acceptable values\n",
    "values_to_replace = {'Not Available; Indisponible': None}\n",
    "columns_to_replace = ['features_properties_date_published_date', 'features_properties_date_created_date']\n",
    "df_en[columns_to_replace] = df_en[columns_to_replace].replace(values_to_replace)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "ab6aeeb4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(10993, 20)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>features_properties_id</th>\n",
       "      <th>features_geometry_coordinates</th>\n",
       "      <th>features_properties_title_en</th>\n",
       "      <th>features_properties_description_en</th>\n",
       "      <th>features_properties_date_published_date</th>\n",
       "      <th>features_properties_keywords_en</th>\n",
       "      <th>features_properties_options</th>\n",
       "      <th>features_properties_contact</th>\n",
       "      <th>features_properties_topicCategory</th>\n",
       "      <th>features_properties_date_created_date</th>\n",
       "      <th>features_properties_spatialRepresentation</th>\n",
       "      <th>features_properties_type</th>\n",
       "      <th>features_properties_graphicOverview</th>\n",
       "      <th>features_properties_language</th>\n",
       "      <th>features_popularity</th>\n",
       "      <th>features_properties_sourceSystemName</th>\n",
       "      <th>features_properties_eoCollection</th>\n",
       "      <th>features_properties_eoFilters</th>\n",
       "      <th>organisation_en</th>\n",
       "      <th>temporalExtent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000183ed-8864-42f0-ae43-c4313a860720</td>\n",
       "      <td>[[[-141.003, 41.6755], [-52.6174, 41.6755], [-...</td>\n",
       "      <td>Principal Mineral Areas, Producing Mines, and ...</td>\n",
       "      <td>This dataset is produced and published annuall...</td>\n",
       "      <td>2020-02-27</td>\n",
       "      <td>mineralization, mineral occurrences, mines, hy...</td>\n",
       "      <td>[{\"url\": \"https://maps-cartes.services.geo.ca/...</td>\n",
       "      <td>[{\"individual\": \"null\", \"position\": {\"en\": \"nu...</td>\n",
       "      <td>economy</td>\n",
       "      <td>2019-04-12</td>\n",
       "      <td>vector; vecteur</td>\n",
       "      <td>series; série</td>\n",
       "      <td>[{\"overviewFileName\": \"http://ftp.maps.canada....</td>\n",
       "      <td>eng; CAN</td>\n",
       "      <td>1250806</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>[]</td>\n",
       "      <td>Government of Canada; Natural Resources Canada...</td>\n",
       "      <td>{'begin': '2020-01', 'end': '2020-12'}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7f245e4d-76c2-4caa-951a-45d1d2051333</td>\n",
       "      <td>[[[-142, 41], [-52, 41], [-52, 84], [-142, 84]...</td>\n",
       "      <td>Canadian Digital Elevation Model, 1945-2011</td>\n",
       "      <td>This collection is a legacy product that is no...</td>\n",
       "      <td>2015</td>\n",
       "      <td>Canada, Earth Sciences, elevation, relief, geo...</td>\n",
       "      <td>[{\"url\": \"https://maps.geogratis.gc.ca/wms/ele...</td>\n",
       "      <td>[{\"individual\": \"null\", \"position\": {\"en\": \"nu...</td>\n",
       "      <td>elevation</td>\n",
       "      <td>2012-11-06</td>\n",
       "      <td>grid; grille</td>\n",
       "      <td>dataset; jeuDonnées</td>\n",
       "      <td>[{\"overviewFileName\": \"https://ftp.maps.canada...</td>\n",
       "      <td>eng; CAN</td>\n",
       "      <td>210798</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>[]</td>\n",
       "      <td>Government of Canada; Natural Resources Canada...</td>\n",
       "      <td>{'begin': '1945', 'end': '2011'}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>085024ac-5a48-427a-a2ea-d62af73f2142</td>\n",
       "      <td>[[[-141.003, 41.6755], [-52.6174, 41.6755], [-...</td>\n",
       "      <td>Canada's National Earthquake Scenario Catalogue</td>\n",
       "      <td>The National Earthquake Scenario Catalogue, pr...</td>\n",
       "      <td>2021-07-06</td>\n",
       "      <td>Emergency preparedness, Earth sciences, Earthq...</td>\n",
       "      <td>[{\"url\": \"https://github.com/OpenDRR/earthquak...</td>\n",
       "      <td>[{\"individual\": \"Dr. Tiegan Hobbs\", \"position\"...</td>\n",
       "      <td>geoscientificInformation</td>\n",
       "      <td>2021-07-06</td>\n",
       "      <td>vector; vecteur</td>\n",
       "      <td>series; série</td>\n",
       "      <td>[]</td>\n",
       "      <td>eng; CAN</td>\n",
       "      <td>140088</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>[]</td>\n",
       "      <td>Government of Canada;Natural Resources Canada;...</td>\n",
       "      <td>{'begin': '2021-07-06', 'end': &lt;NA&gt;}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>03ccfb5c-a06e-43e3-80fd-09d4f8f69703</td>\n",
       "      <td>[[[-104.75571511, 50.42392886], [-104.56356008...</td>\n",
       "      <td>Temporal Series of the National Air Photo Libr...</td>\n",
       "      <td>Note: To visualize the data in the viewer, zoo...</td>\n",
       "      <td>2021-03-31</td>\n",
       "      <td>Mosaic, Aerial photography, Access to informat...</td>\n",
       "      <td>[{\"url\": \"https://datacube-prod-data-public.s3...</td>\n",
       "      <td>[{\"individual\": \"null\", \"position\": {\"en\": \"nu...</td>\n",
       "      <td>imageryBaseMapsEarthCover</td>\n",
       "      <td>2020-08-01</td>\n",
       "      <td>grid; grille</td>\n",
       "      <td>dataset; jeuDonnées</td>\n",
       "      <td>[{\"overviewFileName\": \"http://datacube-prod-da...</td>\n",
       "      <td>eng; CAN</td>\n",
       "      <td>120162</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>[]</td>\n",
       "      <td>Government of Canada;Natural Resources Canada;...</td>\n",
       "      <td>{'begin': '1947', 'end': '1967'}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 features_properties_id  \\\n",
       "0  000183ed-8864-42f0-ae43-c4313a860720   \n",
       "1  7f245e4d-76c2-4caa-951a-45d1d2051333   \n",
       "2  085024ac-5a48-427a-a2ea-d62af73f2142   \n",
       "3  03ccfb5c-a06e-43e3-80fd-09d4f8f69703   \n",
       "\n",
       "                       features_geometry_coordinates  \\\n",
       "0  [[[-141.003, 41.6755], [-52.6174, 41.6755], [-...   \n",
       "1  [[[-142, 41], [-52, 41], [-52, 84], [-142, 84]...   \n",
       "2  [[[-141.003, 41.6755], [-52.6174, 41.6755], [-...   \n",
       "3  [[[-104.75571511, 50.42392886], [-104.56356008...   \n",
       "\n",
       "                        features_properties_title_en  \\\n",
       "0  Principal Mineral Areas, Producing Mines, and ...   \n",
       "1        Canadian Digital Elevation Model, 1945-2011   \n",
       "2    Canada's National Earthquake Scenario Catalogue   \n",
       "3  Temporal Series of the National Air Photo Libr...   \n",
       "\n",
       "                  features_properties_description_en  \\\n",
       "0  This dataset is produced and published annuall...   \n",
       "1  This collection is a legacy product that is no...   \n",
       "2  The National Earthquake Scenario Catalogue, pr...   \n",
       "3  Note: To visualize the data in the viewer, zoo...   \n",
       "\n",
       "  features_properties_date_published_date  \\\n",
       "0                              2020-02-27   \n",
       "1                                    2015   \n",
       "2                              2021-07-06   \n",
       "3                              2021-03-31   \n",
       "\n",
       "                     features_properties_keywords_en  \\\n",
       "0  mineralization, mineral occurrences, mines, hy...   \n",
       "1  Canada, Earth Sciences, elevation, relief, geo...   \n",
       "2  Emergency preparedness, Earth sciences, Earthq...   \n",
       "3  Mosaic, Aerial photography, Access to informat...   \n",
       "\n",
       "                         features_properties_options  \\\n",
       "0  [{\"url\": \"https://maps-cartes.services.geo.ca/...   \n",
       "1  [{\"url\": \"https://maps.geogratis.gc.ca/wms/ele...   \n",
       "2  [{\"url\": \"https://github.com/OpenDRR/earthquak...   \n",
       "3  [{\"url\": \"https://datacube-prod-data-public.s3...   \n",
       "\n",
       "                         features_properties_contact  \\\n",
       "0  [{\"individual\": \"null\", \"position\": {\"en\": \"nu...   \n",
       "1  [{\"individual\": \"null\", \"position\": {\"en\": \"nu...   \n",
       "2  [{\"individual\": \"Dr. Tiegan Hobbs\", \"position\"...   \n",
       "3  [{\"individual\": \"null\", \"position\": {\"en\": \"nu...   \n",
       "\n",
       "  features_properties_topicCategory features_properties_date_created_date  \\\n",
       "0                           economy                            2019-04-12   \n",
       "1                         elevation                            2012-11-06   \n",
       "2          geoscientificInformation                            2021-07-06   \n",
       "3         imageryBaseMapsEarthCover                            2020-08-01   \n",
       "\n",
       "  features_properties_spatialRepresentation features_properties_type  \\\n",
       "0                           vector; vecteur            series; série   \n",
       "1                              grid; grille      dataset; jeuDonnées   \n",
       "2                           vector; vecteur            series; série   \n",
       "3                              grid; grille      dataset; jeuDonnées   \n",
       "\n",
       "                 features_properties_graphicOverview  \\\n",
       "0  [{\"overviewFileName\": \"http://ftp.maps.canada....   \n",
       "1  [{\"overviewFileName\": \"https://ftp.maps.canada...   \n",
       "2                                                 []   \n",
       "3  [{\"overviewFileName\": \"http://datacube-prod-da...   \n",
       "\n",
       "  features_properties_language features_popularity  \\\n",
       "0                     eng; CAN             1250806   \n",
       "1                     eng; CAN              210798   \n",
       "2                     eng; CAN              140088   \n",
       "3                     eng; CAN              120162   \n",
       "\n",
       "  features_properties_sourceSystemName features_properties_eoCollection  \\\n",
       "0                                 <NA>                             <NA>   \n",
       "1                                 <NA>                             <NA>   \n",
       "2                                 <NA>                             <NA>   \n",
       "3                                 <NA>                             <NA>   \n",
       "\n",
       "  features_properties_eoFilters  \\\n",
       "0                            []   \n",
       "1                            []   \n",
       "2                            []   \n",
       "3                            []   \n",
       "\n",
       "                                     organisation_en  \\\n",
       "0  Government of Canada; Natural Resources Canada...   \n",
       "1  Government of Canada; Natural Resources Canada...   \n",
       "2  Government of Canada;Natural Resources Canada;...   \n",
       "3  Government of Canada;Natural Resources Canada;...   \n",
       "\n",
       "                           temporalExtent  \n",
       "0  {'begin': '2020-01', 'end': '2020-12'}  \n",
       "1        {'begin': '1945', 'end': '2011'}  \n",
       "2    {'begin': '2021-07-06', 'end': <NA>}  \n",
       "3        {'begin': '1947', 'end': '1967'}  "
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df_en.shape)\n",
    "df_en.head(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bac9d581",
   "metadata": {},
   "source": [
    "### 5 Import the raw data to the index \n",
    "To import the raw data, we first need to convert the parquet data frame to JSON format.\n",
    "\n",
    "The following attributes are in nested json format, and it needs to be a Python list object after conversion, and a mapping field type [object](https://opensearch.org/docs/latest/field-types/supported-field-types/object-fields/)\n",
    "- features_properties_eoFilters\n",
    "- features_similarity\n",
    "- features_properties_options\n",
    "- features_properties_distributor\n",
    "- features_properties_cited\n",
    "- features_properties_credits\n",
    "- features_properties_contact\n",
    "- features_properties_graphicOverview\n",
    "- geometry_coordinate "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "34e18dda",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "from tqdm import tqdm\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1c0c3927",
   "metadata": {},
   "outputs": [],
   "source": [
    "for x in test_json:\n",
    "    try: \n",
    "        _={\n",
    "            'id':x.get('features_properties_id',''), #str\n",
    "            'title':x.get('features_properties_title_en',''), #str\n",
    "            'description':x.get('features_properties_description_en',''), #str\n",
    "            'keywords':x.get('features_properties_keywords_en',''), #str\n",
    "            'topicCategory':x.get('features_properties_topicCategory',''), #str\n",
    "            'organisation':x.get('organisation_en', ''), #str\n",
    "            'systemName':x.get('features_properties_sourceSystemName',''),\n",
    "            \"vector\":x.get(\"vector\", \"\"), #str\n",
    "        }\n",
    "        aos_client.index(index = index_name,body = _)\n",
    "    except Exception as e:\n",
    "        print(e)\n",
    "        pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cfab76c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "def index_data_to_opensearch(df_en, aos_client, index_name, log_level=\"INFO\"):\n",
    "    \"\"\"\n",
    "    Index data from a pandas DataFrame to an OpenSearch index.\n",
    "\n",
    "    Parameters:\n",
    "    - df_en: DataFrame containing the data to index.\n",
    "    - aos_client: OpenSearch client.\n",
    "    - index_name: Name of the OpenSearch index to which the data will be indexed.\n",
    "    - log_level: Logging level, defaults to \"INFO\". Set to \"DEBUG\" for detailed logs.\n",
    "    \"\"\"\n",
    "    start_time = time.time()\n",
    "\n",
    "    # Convert DataFrame to a list of dictionaries (JSON)\n",
    "    json_en = df_en.to_dict(\"records\")\n",
    "\n",
    "    # Index the data\n",
    "    for x in tqdm(json_en, desc=\"Indexing Records\"):\n",
    "        try:\n",
    "            bounding_box = json.loads(x.get('features_geometry_coordinates', '[]'))\n",
    "            coordinates = {\n",
    "                \"type\": \"polygon\",\n",
    "                \"coordinates\": bounding_box\n",
    "            }\n",
    "            \n",
    "            document = {\n",
    "                'id': x.get('features_properties_id', ''),\n",
    "                'coordinates': coordinates,\n",
    "                'title': x.get('features_properties_title_en', ''),\n",
    "                'description': x.get('features_properties_description_en', ''),\n",
    "                'published': x.get('features_properties_date_published_date', ''),\n",
    "                'keywords': x.get('features_properties_keywords_en', ''),\n",
    "                'options': json.loads(x.get('features_properties_options', '[]')),\n",
    "                'contact': json.loads(x.get('features_properties_contact', '[]')),\n",
    "                'topicCategory': x.get('features_properties_topicCategory', ''),\n",
    "                'created': x.get('features_properties_date_created_date', ''),\n",
    "                'spatialRepresentation': x.get('features_properties_spatialRepresentation', ''),\n",
    "                'type': x.get('features_properties_type', ''),\n",
    "                'temporalExtent': x.get('temporalExtent', ''),\n",
    "                'graphicOverview': json.loads(x.get('features_properties_graphicOverview', '[]')),\n",
    "                'language': x.get('features_properties_language', ''),\n",
    "                'organisation': x.get('organisation_en', ''),\n",
    "                'popularity': int(x.get('features_popularity', '0')),\n",
    "                'systemName': x.get('features_properties_sourceSystemName', ''),\n",
    "                'eoCollection': x.get('features_properties_eoCollection', ''),\n",
    "                'eoFilters': json.loads(x.get('features_properties_eoFilters', '[]'))\n",
    "            }\n",
    "\n",
    "            if log_level == \"DEBUG\":\n",
    "                print((json.dumps(document, indent=4)))\n",
    "\n",
    "            aos_client.index(index=index_name, body=document)\n",
    "\n",
    "        except Exception as e:\n",
    "            print(e)\n",
    "\n",
    "    # Final record count check (Optional, can slow down the script if the index is large)\n",
    "    res = aos_client.search(index=index_name, body={\"query\": {\"match_all\": {}}})\n",
    "    total_time = time.time() - start_time\n",
    "    print(f\"Completed indexing. Records loaded into the index {index_name}: {res['hits']['total']['value']}. Total time taken: {total_time:.2f} seconds.\")\n",
    "\n",
    "# Example usage\n",
    "index_data_to_opensearch(df_en, aos_client, index_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "2bea2f3d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Records loaded into the index search is 10000.\n"
     ]
    }
   ],
   "source": [
    "res = aos_client.search(index=index_name, body={\"query\": {\"match_all\": {}}})\n",
    "print(f\"Records loaded into the index {index_name} is {res['hits']['total']['value']}.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "442222ff",
   "metadata": {},
   "source": [
    "### 6. Run a \"simple text search\" "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "id": "e53af8aa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>relevancy_score</th>\n",
       "      <th>title</th>\n",
       "      <th>uuid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K5VKOI4BdooaaHUeJKHO</td>\n",
       "      <td>11.244217</td>\n",
       "      <td>Wildfire Information</td>\n",
       "      <td>c4a1037c-cf9e-491a-2e3f-70cf13ee29a2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BpVIOI4BdooaaHUe9I74</td>\n",
       "      <td>8.911254</td>\n",
       "      <td>Wildfire hotspots Cumulative Effects products</td>\n",
       "      <td>574c32db-aba7-4919-9c9f-c58398754173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>fJVJOI4BdooaaHUeiJdU</td>\n",
       "      <td>8.334815</td>\n",
       "      <td>Wildfire Year/dNBR/Mask 1985-2015</td>\n",
       "      <td>9eb51c7b-5336-4b50-a9f8-a6791bad15a9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     id  relevancy_score  \\\n",
       "0  K5VKOI4BdooaaHUeJKHO        11.244217   \n",
       "1  BpVIOI4BdooaaHUe9I74         8.911254   \n",
       "2  fJVJOI4BdooaaHUeiJdU         8.334815   \n",
       "\n",
       "                                           title  \\\n",
       "0                           Wildfire Information   \n",
       "1  Wildfire hotspots Cumulative Effects products   \n",
       "2              Wildfire Year/dNBR/Mask 1985-2015   \n",
       "\n",
       "                                   uuid  \n",
       "0  c4a1037c-cf9e-491a-2e3f-70cf13ee29a2  \n",
       "1  574c32db-aba7-4919-9c9f-c58398754173  \n",
       "2  9eb51c7b-5336-4b50-a9f8-a6791bad15a9  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "search_query = \"wildfire\"\n",
    "query={\n",
    "  \"size\": 20,\n",
    "  \"query\": {\n",
    "    \"match\": {\n",
    "      \"title\": search_query\n",
    "    }\n",
    "  }\n",
    "}\n",
    "res = aos_client.search(index=index_name, body=query)\n",
    "query_result=[]\n",
    "for hit in res['hits']['hits']:\n",
    "    row=[hit['_id'],hit['_score'],hit['_source']['title'],hit['_source']['id']]\n",
    "    query_result.append(row)\n",
    "\n",
    "query_result_df = pd.DataFrame(data=query_result,columns=[\"id\",\"relevancy_score\",\"title\",'uuid'])\n",
    "display(query_result_df)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ecac2974",
   "metadata": {},
   "source": [
    "### 7. Keyword search across multiple fields \n",
    "The current geo.ca search is a multi-column keywords search based on the following properties: features_properties_topicCategory, features_properties_keywords_en, features_properties_description_en, features_properties_title_en, features_properties_contact (organization), features_properties_sourceSystemName, features_properties_geometry "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 210,
   "id": "adcb1b6a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>relevancy_score</th>\n",
       "      <th>title</th>\n",
       "      <th>uuid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>fZVIOI4BdooaaHUe_Y4G</td>\n",
       "      <td>17.015722</td>\n",
       "      <td>River Ice in Canada - Current Year</td>\n",
       "      <td>8ca6f047-ddef-43d7-81c2-47654f4c69bd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>k5VIOI4BdooaaHUe_o5l</td>\n",
       "      <td>17.015722</td>\n",
       "      <td>Active Monitoring of River Ice in Canada</td>\n",
       "      <td>7b210c58-2fc7-47c5-8b8a-2605c77d725c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>x5VJOI4BdooaaHUeAY7B</td>\n",
       "      <td>17.015722</td>\n",
       "      <td>River Ice in Canada - Archive</td>\n",
       "      <td>5e6b40bf-299f-4e05-87c8-d10b9c8210f9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>QZVIOI4BdooaaHUe-I7m</td>\n",
       "      <td>15.963156</td>\n",
       "      <td>River Ice State in Canada - Cartographic Produ...</td>\n",
       "      <td>d1fcb44f-5f86-4957-bdb4-e6fd1aa69283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>M5VKOI4BdooaaHUeUqTs</td>\n",
       "      <td>14.826182</td>\n",
       "      <td>River Ice in Canada - Current Year</td>\n",
       "      <td>CGDIWH-150516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>T5VKOI4BdooaaHUeVKS6</td>\n",
       "      <td>14.826182</td>\n",
       "      <td>Active Monitoring of River Ice in Canada - Pro...</td>\n",
       "      <td>CGDIWH-150521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>xJVKOI4BdooaaHUe6K0V</td>\n",
       "      <td>14.434877</td>\n",
       "      <td>River Ice in Canada - Archive - Footprints</td>\n",
       "      <td>CGDIWH-150519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>xZVKOI4BdooaaHUe6K0k</td>\n",
       "      <td>14.253883</td>\n",
       "      <td>Active monitoring of river ice in Canada - Foo...</td>\n",
       "      <td>CGDIWH-150522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>MZVKOI4BdooaaHUeUqSs</td>\n",
       "      <td>13.886137</td>\n",
       "      <td>River ice in Canada - Current year</td>\n",
       "      <td>CGDIWH-150514</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>w5VKOI4BdooaaHUe6K0H</td>\n",
       "      <td>12.656118</td>\n",
       "      <td>River ice in Canada - Archive - Footprints on ...</td>\n",
       "      <td>CGDIWH-150518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>6ZVKOI4BdooaaHUeP6Jh</td>\n",
       "      <td>12.445482</td>\n",
       "      <td>History (public) of ice jams recorded at the MSP</td>\n",
       "      <td>16b5e6bf-3fb6-4d4d-a87d-c79b7bfe8d9d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>m5VJOI4BdooaaHUeL5EN</td>\n",
       "      <td>11.823161</td>\n",
       "      <td>Probability of Ice freeze days (woody crops) i...</td>\n",
       "      <td>8a09413a-0a01-4aab-8925-720d987deb20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>35VJOI4BdooaaHUeMpGX</td>\n",
       "      <td>11.823161</td>\n",
       "      <td>Probability of Ice freeze days (woody crops) d...</td>\n",
       "      <td>7a4c7560-3f86-4210-9561-ff77709acb96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>qJVKOI4BdooaaHUe5q1Y</td>\n",
       "      <td>11.466285</td>\n",
       "      <td>Flood_Inondation_EGS_Flood_Product_Archive_en</td>\n",
       "      <td>CGDIWH-150151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>4ZVJOI4BdooaaHUeJJC8</td>\n",
       "      <td>11.395887</td>\n",
       "      <td>Probability of Ice freeze days (herbaceous cro...</td>\n",
       "      <td>14b1d307-82d2-4e88-a518-a51ae38b9e58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>jJVJOI4BdooaaHUeLpFB</td>\n",
       "      <td>11.395887</td>\n",
       "      <td>Probability of Ice freeze days (herbaceous cro...</td>\n",
       "      <td>caeb0592-8c95-4461-b9a5-5fde7f2ccbb3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>gJVJOI4BdooaaHUeHpAQ</td>\n",
       "      <td>10.898115</td>\n",
       "      <td>Ice freeze days (herbaceous  crops) in dormant...</td>\n",
       "      <td>4e81a467-fc14-4fa0-a1d6-9d65336587c6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>LJVJOI4BdooaaHUeKZEV</td>\n",
       "      <td>10.898115</td>\n",
       "      <td>Ice freeze days (herbaceous crops) during non-...</td>\n",
       "      <td>21d9b53d-bf5b-4913-b1f7-8ba5211d6f81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>sJVJOI4BdooaaHUeMJEn</td>\n",
       "      <td>10.898115</td>\n",
       "      <td>Ice freeze days (woody crops) in dormant perio...</td>\n",
       "      <td>65b8fc8e-7d82-441f-b588-9575401ecb48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>EpVJOI4BdooaaHUeNZJ7</td>\n",
       "      <td>10.898115</td>\n",
       "      <td>Ice freeze days (woody crops) during non-growi...</td>\n",
       "      <td>89b14c19-69b3-491b-9b78-d0ae2c4dffd6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      id  relevancy_score  \\\n",
       "0   fZVIOI4BdooaaHUe_Y4G        17.015722   \n",
       "1   k5VIOI4BdooaaHUe_o5l        17.015722   \n",
       "2   x5VJOI4BdooaaHUeAY7B        17.015722   \n",
       "3   QZVIOI4BdooaaHUe-I7m        15.963156   \n",
       "4   M5VKOI4BdooaaHUeUqTs        14.826182   \n",
       "5   T5VKOI4BdooaaHUeVKS6        14.826182   \n",
       "6   xJVKOI4BdooaaHUe6K0V        14.434877   \n",
       "7   xZVKOI4BdooaaHUe6K0k        14.253883   \n",
       "8   MZVKOI4BdooaaHUeUqSs        13.886137   \n",
       "9   w5VKOI4BdooaaHUe6K0H        12.656118   \n",
       "10  6ZVKOI4BdooaaHUeP6Jh        12.445482   \n",
       "11  m5VJOI4BdooaaHUeL5EN        11.823161   \n",
       "12  35VJOI4BdooaaHUeMpGX        11.823161   \n",
       "13  qJVKOI4BdooaaHUe5q1Y        11.466285   \n",
       "14  4ZVJOI4BdooaaHUeJJC8        11.395887   \n",
       "15  jJVJOI4BdooaaHUeLpFB        11.395887   \n",
       "16  gJVJOI4BdooaaHUeHpAQ        10.898115   \n",
       "17  LJVJOI4BdooaaHUeKZEV        10.898115   \n",
       "18  sJVJOI4BdooaaHUeMJEn        10.898115   \n",
       "19  EpVJOI4BdooaaHUeNZJ7        10.898115   \n",
       "\n",
       "                                                title  \\\n",
       "0                  River Ice in Canada - Current Year   \n",
       "1            Active Monitoring of River Ice in Canada   \n",
       "2                       River Ice in Canada - Archive   \n",
       "3   River Ice State in Canada - Cartographic Produ...   \n",
       "4                  River Ice in Canada - Current Year   \n",
       "5   Active Monitoring of River Ice in Canada - Pro...   \n",
       "6          River Ice in Canada - Archive - Footprints   \n",
       "7   Active monitoring of river ice in Canada - Foo...   \n",
       "8                  River ice in Canada - Current year   \n",
       "9   River ice in Canada - Archive - Footprints on ...   \n",
       "10   History (public) of ice jams recorded at the MSP   \n",
       "11  Probability of Ice freeze days (woody crops) i...   \n",
       "12  Probability of Ice freeze days (woody crops) d...   \n",
       "13      Flood_Inondation_EGS_Flood_Product_Archive_en   \n",
       "14  Probability of Ice freeze days (herbaceous cro...   \n",
       "15  Probability of Ice freeze days (herbaceous cro...   \n",
       "16  Ice freeze days (herbaceous  crops) in dormant...   \n",
       "17  Ice freeze days (herbaceous crops) during non-...   \n",
       "18  Ice freeze days (woody crops) in dormant perio...   \n",
       "19  Ice freeze days (woody crops) during non-growi...   \n",
       "\n",
       "                                    uuid  \n",
       "0   8ca6f047-ddef-43d7-81c2-47654f4c69bd  \n",
       "1   7b210c58-2fc7-47c5-8b8a-2605c77d725c  \n",
       "2   5e6b40bf-299f-4e05-87c8-d10b9c8210f9  \n",
       "3   d1fcb44f-5f86-4957-bdb4-e6fd1aa69283  \n",
       "4                          CGDIWH-150516  \n",
       "5                          CGDIWH-150521  \n",
       "6                          CGDIWH-150519  \n",
       "7                          CGDIWH-150522  \n",
       "8                          CGDIWH-150514  \n",
       "9                          CGDIWH-150518  \n",
       "10  16b5e6bf-3fb6-4d4d-a87d-c79b7bfe8d9d  \n",
       "11  8a09413a-0a01-4aab-8925-720d987deb20  \n",
       "12  7a4c7560-3f86-4210-9561-ff77709acb96  \n",
       "13                         CGDIWH-150151  \n",
       "14  14b1d307-82d2-4e88-a518-a51ae38b9e58  \n",
       "15  caeb0592-8c95-4461-b9a5-5fde7f2ccbb3  \n",
       "16  4e81a467-fc14-4fa0-a1d6-9d65336587c6  \n",
       "17  21d9b53d-bf5b-4913-b1f7-8ba5211d6f81  \n",
       "18  65b8fc8e-7d82-441f-b588-9575401ecb48  \n",
       "19  89b14c19-69b3-491b-9b78-d0ae2c4dffd6  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "search_query = \"River Ice events in Canada in 2020\"\n",
    "query={\n",
    "  \"size\": 20,\n",
    "  \"query\": {\n",
    "    \"multi_match\": {\n",
    "      \"query\": search_query,\n",
    "      \"fields\": [\"topicCategory\",\"keywords\", \"description\", \"title*\", \"organisation\", \"systemName\"]\n",
    "    }\n",
    "  }\n",
    "}\n",
    "res = aos_client.search(index=index_name, size=20,body=query)\n",
    "\n",
    "query_result=[]\n",
    "for hit in res['hits']['hits']:\n",
    "    row=[hit['_id'],hit['_score'],hit['_source']['title'],hit['_source']['id']]\n",
    "    query_result.append(row)\n",
    "\n",
    "query_result_df = pd.DataFrame(data=query_result,columns=[\"id\",\"relevancy_score\",\"title\",'uuid'])\n",
    "display(query_result_df)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "687298a6",
   "metadata": {},
   "source": [
    "### Keywords Search Relevance  \n",
    "Search relevance evaluates the accuracy of the search results returned by a query. The higher the relevance, the better the search engine.\n",
    "- [Search features in OpenSearch](https://opensearch.org/docs/latest/search-plugins/)\n",
    "- [keyword (BM25) search](https://opensearch.org/docs/latest/search-plugins/keyword-search/):By default, OpenSearch calculates document scores using the Okapi BM25 algorithm. BM25 is a keyword-based algorithm that performs lexical search for words that appear in the query.\n",
    "- [k-NN search](https://opensearch.org/docs/latest/search-plugins/knn/index/):Searches for k-nearest neighbors to a search term across an index of vectors\n",
    "- [Hybrid search](https://opensearch.org/docs/latest/search-plugins/hybrid-search/)\n",
    "\n",
    "[Okapi BM25](https://en.wikipedia.org/wiki/Okapi_BM25)(Best Matching 25) is a keyword-based algorithm that performs lexical search for words that appear in the query.When determining a document’s relevance, BM25 considers term frequency/inverse document frequency (TF/IDF). It improves upon the TF-IDF model by incorporating document length normalization and a saturation function, which prevents the term frequency part from growing indefinitely with the frequency of the term in the document.\n",
    "\n",
    "The K-NN search feature in OpenSearch enables users to perform similarity search by finding the \"nearest\" vectors in high-dimensional space. This approach is particularly useful for scenarios like product recommendations, image search, and finding similar documents, where items can be represented as vectors in a multidimensional space."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cee1ecf7",
   "metadata": {},
   "source": [
    "### 8. Search with Field preference or boosting\n",
    "\n",
    "When searching across fields, all fields given the same priority by default. But you can control the preference by giving static boost score to each field"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9d5a3758",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "search_query = \"Urban planning and development trends\"\n",
    "query={\n",
    "  \"size\": 20,\n",
    "  \"query\": {\n",
    "    \"multi_match\": {\n",
    "      \"query\": search_query,\n",
    "      \"fields\": [\"topicCategory\",\"keywords^2\", \"description^1.5\", \"title^1.5\", \"organisation\", \"systemName\"]\n",
    "    }\n",
    "  }\n",
    "}\n",
    "res = aos_client.search(index=index_name, size=20,body=query)\n",
    "\n",
    "query_result=[]\n",
    "for hit in res['hits']['hits']:\n",
    "    row=[hit['_id'],hit['_score'],hit['_source']['title'],hit['_source']['id']]\n",
    "    query_result.append(row)\n",
    "\n",
    "query_result_df = pd.DataFrame(data=query_result,columns=[\"id\",\"relevancy_score\",\"title\",'uuid'])\n",
    "display(query_result_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "438d0400",
   "metadata": {},
   "source": [
    "### 9. Keywords Search geo_shape search\n",
    "\n",
    "Ottawa, being the capital city of Canada, is located in the province of Ontario. The geographical coordinates for Ottawa roughly center around latitude 45.4215° N and longitude -75.6972° W."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "5fc02e20",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>relevancy_score</th>\n",
       "      <th>title</th>\n",
       "      <th>uuid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>y5VKOI4BdooaaHUeuqqR</td>\n",
       "      <td>13.991396</td>\n",
       "      <td>Geo-Floods: Map showing the presence of mapped...</td>\n",
       "      <td>5690277b-cef2-45bc-a50f-b07efc521fe6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a5VJOI4BdooaaHUeh5dj</td>\n",
       "      <td>13.101778</td>\n",
       "      <td>Schedule for the presence of a flood zone iden...</td>\n",
       "      <td>0d9de0d6-9873-4a8c-adc7-0e94d51b3fa0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9JVJOI4BdooaaHUef5b-</td>\n",
       "      <td>12.810937</td>\n",
       "      <td>Flood Risk Areas Database (BDZI)</td>\n",
       "      <td>3ac8ddff-fe0a-4a7a-8393-d5938e8f35e5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>OpVIOI4BdooaaHUe-I5-</td>\n",
       "      <td>11.411324</td>\n",
       "      <td>Active Floods in Canada</td>\n",
       "      <td>9cad712a-5ac5-4248-b7d7-2db1a3892509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>RpVIOI4BdooaaHUe-Y5B</td>\n",
       "      <td>11.411324</td>\n",
       "      <td>Floods in Canada - Current Year</td>\n",
       "      <td>b1afd8d2-6e14-4ec4-9a09-652221a6cb71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>V5VKOI4BdooaaHUeRqMf</td>\n",
       "      <td>11.173750</td>\n",
       "      <td>Large and low-current areas of the Outaouais a...</td>\n",
       "      <td>b1e7c9a9-a34a-457c-8671-bae4529749fb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>TJVIOI4BdooaaHUe-Y7L</td>\n",
       "      <td>10.672247</td>\n",
       "      <td>Floods in Canada - Archive</td>\n",
       "      <td>74144824-206e-4cea-9fb9-72925a128189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5ZVJOI4BdooaaHUef5Yh</td>\n",
       "      <td>10.318212</td>\n",
       "      <td>2017-2019 Special Intervention Zone (ZIS annex...</td>\n",
       "      <td>fef0a7a8-07ee-43d5-93d7-979d12dfcff3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>yJVKOI4BdooaaHUe6K1P</td>\n",
       "      <td>9.487976</td>\n",
       "      <td>Floods in Canada - Archive</td>\n",
       "      <td>CGDIWH-150527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>6pVKOI4BdooaaHUeP6Ju</td>\n",
       "      <td>9.432468</td>\n",
       "      <td>Limnimetric scales (public)</td>\n",
       "      <td>cbc3a496-cc0f-4930-bc91-b351373110f6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0ZVKOI4BdooaaHUe6a0D</td>\n",
       "      <td>9.296536</td>\n",
       "      <td>Floods in Canada — Current Year</td>\n",
       "      <td>CGDIWH-150540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>zJVKOI4BdooaaHUe6K2x</td>\n",
       "      <td>9.114386</td>\n",
       "      <td>Ongoing floods in Canada</td>\n",
       "      <td>CGDIWH-150533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>XJVKOI4BdooaaHUen6n-</td>\n",
       "      <td>8.967092</td>\n",
       "      <td>Collection - Flood Susceptibility Index (FSI)</td>\n",
       "      <td>ccmeo-flood-susceptibility</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>XZVKOI4BdooaaHUeoKkM</td>\n",
       "      <td>8.967092</td>\n",
       "      <td>2015 - Flood Susceptibility Index (FSI)</td>\n",
       "      <td>ccmeo-flood-susceptibility-FS-national-2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>y5VKOI4BdooaaHUe6K2g</td>\n",
       "      <td>8.930139</td>\n",
       "      <td>Floods in Canada - Archive</td>\n",
       "      <td>CGDIWH-150530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>zZVIOI4BdooaaHUe8Y0V</td>\n",
       "      <td>8.884867</td>\n",
       "      <td>Territories for which a ministerial order has ...</td>\n",
       "      <td>0206674f-c06b-4664-b36f-414a0cbb224f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>J5VJOI4BdooaaHUeg5ce</td>\n",
       "      <td>8.690298</td>\n",
       "      <td>Special intervention zone 2017-2019 (ZIS annex...</td>\n",
       "      <td>20dfdb56-8619-42a2-bac4-42259a5fc38b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>EpVLOI4BdooaaHUeg7hQ</td>\n",
       "      <td>8.643290</td>\n",
       "      <td>Flood Susceptibility Index (FSI)</td>\n",
       "      <td>df106e11-4cee-425d-bd38-7e51ac674128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>zpVKOI4BdooaaHUe6K3W</td>\n",
       "      <td>8.538696</td>\n",
       "      <td>Active Floods in Canada</td>\n",
       "      <td>CGDIWH-150535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>05VKOI4BdooaaHUe6a0e</td>\n",
       "      <td>8.538696</td>\n",
       "      <td>Floods in Canada - Current Year</td>\n",
       "      <td>CGDIWH-150543</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      id  relevancy_score  \\\n",
       "0   y5VKOI4BdooaaHUeuqqR        13.991396   \n",
       "1   a5VJOI4BdooaaHUeh5dj        13.101778   \n",
       "2   9JVJOI4BdooaaHUef5b-        12.810937   \n",
       "3   OpVIOI4BdooaaHUe-I5-        11.411324   \n",
       "4   RpVIOI4BdooaaHUe-Y5B        11.411324   \n",
       "5   V5VKOI4BdooaaHUeRqMf        11.173750   \n",
       "6   TJVIOI4BdooaaHUe-Y7L        10.672247   \n",
       "7   5ZVJOI4BdooaaHUef5Yh        10.318212   \n",
       "8   yJVKOI4BdooaaHUe6K1P         9.487976   \n",
       "9   6pVKOI4BdooaaHUeP6Ju         9.432468   \n",
       "10  0ZVKOI4BdooaaHUe6a0D         9.296536   \n",
       "11  zJVKOI4BdooaaHUe6K2x         9.114386   \n",
       "12  XJVKOI4BdooaaHUen6n-         8.967092   \n",
       "13  XZVKOI4BdooaaHUeoKkM         8.967092   \n",
       "14  y5VKOI4BdooaaHUe6K2g         8.930139   \n",
       "15  zZVIOI4BdooaaHUe8Y0V         8.884867   \n",
       "16  J5VJOI4BdooaaHUeg5ce         8.690298   \n",
       "17  EpVLOI4BdooaaHUeg7hQ         8.643290   \n",
       "18  zpVKOI4BdooaaHUe6K3W         8.538696   \n",
       "19  05VKOI4BdooaaHUe6a0e         8.538696   \n",
       "\n",
       "                                                title  \\\n",
       "0   Geo-Floods: Map showing the presence of mapped...   \n",
       "1   Schedule for the presence of a flood zone iden...   \n",
       "2                    Flood Risk Areas Database (BDZI)   \n",
       "3                             Active Floods in Canada   \n",
       "4                     Floods in Canada - Current Year   \n",
       "5   Large and low-current areas of the Outaouais a...   \n",
       "6                          Floods in Canada - Archive   \n",
       "7   2017-2019 Special Intervention Zone (ZIS annex...   \n",
       "8                          Floods in Canada - Archive   \n",
       "9                         Limnimetric scales (public)   \n",
       "10                    Floods in Canada — Current Year   \n",
       "11                           Ongoing floods in Canada   \n",
       "12     Collection - Flood Susceptibility Index (FSI)    \n",
       "13           2015 - Flood Susceptibility Index (FSI)    \n",
       "14                         Floods in Canada - Archive   \n",
       "15  Territories for which a ministerial order has ...   \n",
       "16  Special intervention zone 2017-2019 (ZIS annex...   \n",
       "17                   Flood Susceptibility Index (FSI)   \n",
       "18                            Active Floods in Canada   \n",
       "19                    Floods in Canada - Current Year   \n",
       "\n",
       "                                           uuid  \n",
       "0          5690277b-cef2-45bc-a50f-b07efc521fe6  \n",
       "1          0d9de0d6-9873-4a8c-adc7-0e94d51b3fa0  \n",
       "2          3ac8ddff-fe0a-4a7a-8393-d5938e8f35e5  \n",
       "3          9cad712a-5ac5-4248-b7d7-2db1a3892509  \n",
       "4          b1afd8d2-6e14-4ec4-9a09-652221a6cb71  \n",
       "5          b1e7c9a9-a34a-457c-8671-bae4529749fb  \n",
       "6          74144824-206e-4cea-9fb9-72925a128189  \n",
       "7          fef0a7a8-07ee-43d5-93d7-979d12dfcff3  \n",
       "8                                 CGDIWH-150527  \n",
       "9          cbc3a496-cc0f-4930-bc91-b351373110f6  \n",
       "10                                CGDIWH-150540  \n",
       "11                                CGDIWH-150533  \n",
       "12                   ccmeo-flood-susceptibility  \n",
       "13  ccmeo-flood-susceptibility-FS-national-2015  \n",
       "14                                CGDIWH-150530  \n",
       "15         0206674f-c06b-4664-b36f-414a0cbb224f  \n",
       "16         20dfdb56-8619-42a2-bac4-42259a5fc38b  \n",
       "17         df106e11-4cee-425d-bd38-7e51ac674128  \n",
       "18                                CGDIWH-150535  \n",
       "19                                CGDIWH-150543  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "query = 'flood risk mapping'\n",
    "# Example coordinates that cover a rough area around Ottawa\n",
    "coordinates = [[[-75.9, 45.2], [-75.9, 45.5], [-75.6, 45.5], [-75.6, 45.2], [-75.9, 45.2]]]\n",
    "\n",
    "query = {\n",
    "    \"query\": {\n",
    "        \"bool\": {\n",
    "            \"must\": [\n",
    "                {\n",
    "                    \"multi_match\": {\n",
    "                        \"query\": query,\n",
    "                        \"fields\": [\"topicCategory\",\"keywords\", \"description\", \"title*\", \"organisation\", \"systemName\"]\n",
    "                    }\n",
    "                },\n",
    "                {\n",
    "                    \"geo_shape\": {\n",
    "                        \"coordinates\": {  # Geo_shape field\n",
    "                            \"shape\": {\n",
    "                                \"type\": \"polygon\",\n",
    "                                \"coordinates\": coordinates\n",
    "                            },\n",
    "                            \"relation\": \"intersects\"  # Change as needed (e.g., 'within\n",
    "                        }\n",
    "                    }\n",
    "                }\n",
    "            ]\n",
    "        }\n",
    "    }\n",
    "}\n",
    "\n",
    "res = aos_client.search(index=index_name, size=20,body=query)\n",
    "\n",
    "query_result=[]\n",
    "for hit in res['hits']['hits']:\n",
    "    row=[hit['_id'],hit['_score'],hit['_source']['title'],hit['_source']['id']]\n",
    "    query_result.append(row)\n",
    "\n",
    "query_result_df = pd.DataFrame(data=query_result,columns=[\"id\",\"relevancy_score\",\"title\",'uuid'])\n",
    "display(query_result_df)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d037b6d3",
   "metadata": {},
   "source": [
    "### Construct the geolambda API response directly from the search results "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c2d346bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_to_top_of_dict(original_dict, key, value):\n",
    "    \"\"\"\n",
    "    Adds a new key-value pair to the top of an existing dictionary.\n",
    "    \"\"\"\n",
    "    # Check if the key or value is empty\n",
    "    if key is None or value is None:\n",
    "        print(\"Key and value must both be non-empty.\")\n",
    "        return original_dict  # Optionally handle this case differently\n",
    "\n",
    "    # Create a new dictionary with the new key-value pair\n",
    "    new_dict = {key: value}\n",
    "    \n",
    "    # Update the new dictionary with the original dictionary\n",
    "    new_dict.update(original_dict)\n",
    "    \n",
    "    # Return the updated dictionary\n",
    "    return new_dict\n",
    "\n",
    "def create_api_response(search_results):\n",
    "    \"\"\"\n",
    "    Creates an API response from the search results.\n",
    "    \n",
    "    :param search_results: The search results returned by Elasticsearch/OpenSearch.\n",
    "    :return: A list of items with added metadata (total, relevancy, and row number).\n",
    "    \"\"\"\n",
    "    items = []\n",
    "    total_hits = len(search_results['hits']['hits'])\n",
    "    \n",
    "    for count, hit in enumerate(search_results['hits']['hits'], start=1):\n",
    "        try:\n",
    "            # Extract the source data\n",
    "            source_data = hit['_source']\n",
    "            \n",
    "            # Check and delete 'vector' key if it exists\n",
    "            source_data.pop('vector', None)  # Remove 'vector' key without raising an error if it's not present\n",
    "        \n",
    "            # Add custom metadata to the source data\n",
    "            source_data = add_to_top_of_dict(source_data, 'total', total_hits)\n",
    "            source_data = add_to_top_of_dict(source_data, 'relevancy', hit.get('_score', ''))\n",
    "            source_data = add_to_top_of_dict(source_data, 'row_num', count)\n",
    "            \n",
    "            items.append(source_data)\n",
    "        except Exception as e:\n",
    "            print(f\"Error processing hit: {e}\")\n",
    "    \n",
    "    return items\n",
    "\n",
    "api_response = create_api_response(res)\n",
    "#print(json.dumps(api_response[0], indent=4))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_pytorch_p310",
   "language": "python",
   "name": "conda_pytorch_p310"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}