0_cog-sparql-recuperation-des-donnees.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "from SPARQLWrapper import SPARQLWrapper, JSON"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_all_ids_from_object(url):\n",
    "    objs = []\n",
    "\n",
    "    sparql = SPARQLWrapper(\"http://rdf.insee.fr/sparql\")\n",
    "    query = \"\"\"\n",
    "    SELECT * WHERE {\n",
    "     ?s a <\"\"\"+url+\"\"\">\n",
    "    }\n",
    "    \"\"\"\n",
    "    sparql.setQuery(query)\n",
    "    sparql.setReturnFormat(JSON)\n",
    "    results = sparql.query().convert()\n",
    "\n",
    "    for result in results[\"results\"][\"bindings\"]:\n",
    "        objs.append(result['s']['value'])\n",
    "    print('Nombre de {} : {}'.format(url.replace('http://rdf.insee.fr/def/geo#',''),len(objs)))\n",
    "    return objs\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nombre de Commune : 43287\n",
      "Nombre de CommuneAssociee : 1046\n",
      "Nombre de CommuneDeleguee : 2449\n",
      "Nombre de CommuneFusionee : 0\n",
      "Nombre de EvenementGeographique : 5086\n"
     ]
    }
   ],
   "source": [
    "coms = get_all_ids_from_object(\"http://rdf.insee.fr/def/geo#Commune\")\n",
    "comas = get_all_ids_from_object(\"http://rdf.insee.fr/def/geo#CommuneAssociee\")\n",
    "comds = get_all_ids_from_object(\"http://rdf.insee.fr/def/geo#CommuneDeleguee\")\n",
    "comfs = get_all_ids_from_object(\"http://rdf.insee.fr/def/geo#CommuneFusionee\")\n",
    "evt = get_all_ids_from_object(\"http://rdf.insee.fr/def/geo#EvenementGeographique\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_info_com(typeCom, comArray):\n",
    "    print(\"-----\"+typeCom+\"-----\")\n",
    "    i = 0\n",
    "    for com in comArray:\n",
    "        if(i%100 == 0):\n",
    "            print(i)\n",
    "        i = i + 1\n",
    "        sparql = SPARQLWrapper(\"http://rdf.insee.fr/sparql\")\n",
    "        sparql.setQuery(\"\"\"\n",
    "           DESCRIBE <{}>\n",
    "        \"\"\".format(com))\n",
    "        sparql.setReturnFormat(JSON)\n",
    "        results = sparql.query().convert()\n",
    "        newcom = {}\n",
    "\n",
    "        for result in results[\"results\"][\"bindings\"]:\n",
    "            if(result['subject']['value'] == com):\n",
    "                if(result['predicate']['value'] == 'http://rdf.insee.fr/def/geo#codeINSEE'):\n",
    "                    newcom['codeInsee'] = result['object']['value']\n",
    "                if(result['predicate']['value'] == 'http://rdf.insee.fr/def/geo#nom'):\n",
    "                    newcom['nom'] = result['object']['value']\n",
    "                if(result['predicate']['value'] == 'http://rdf.insee.fr/def/geo#nomSansArticle'):\n",
    "                    newcom['nomSansArticle'] = result['object']['value']\n",
    "            if(result['object']['value'] == com):\n",
    "                if(result['predicate']['value'] == 'http://rdf.insee.fr/def/geo#creation'):\n",
    "                    newcom['evtCreation'] = result['subject']['value'].replace('http://id.insee.fr/geo/evenementGeographique/','')\n",
    "                if(result['predicate']['value'] == 'http://rdf.insee.fr/def/geo#suppression'):\n",
    "                    newcom['evtSuppression'] = result['subject']['value'].replace('http://id.insee.fr/geo/evenementGeographique/','')\n",
    "\n",
    "        newcom['id'] = com.replace('http://id.insee.fr/geo/commune/','').replace('http://id.insee.fr/geo/communeAssociee/','').replace('http://id.insee.fr/geo/communeDeleguee/','').replace('http://id.insee.fr/geo/communeFusionnee/','')\n",
    "        newcom['type'] = typeCom\n",
    "\n",
    "        listallcoms.append(newcom)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Récupération du détail de toutes les communes (de tout type COMA COMD COM COMF) dans un tableau"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-----COM-----\n",
      "0\n",
      "100\n",
      "200\n",
      "300\n",
      "400\n",
      "500\n",
      "600\n",
      "700\n",
      "800\n",
      "900\n",
      "1000\n",
      "1100\n",
      "1200\n",
      "1300\n",
      "1400\n",
      "1500\n",
      "1600\n",
      "1700\n",
      "1800\n",
      "1900\n",
      "2000\n",
      "2100\n",
      "2200\n",
      "2300\n",
      "2400\n",
      "2500\n",
      "2600\n",
      "2700\n",
      "2800\n",
      "2900\n",
      "3000\n",
      "3100\n",
      "3200\n",
      "3300\n",
      "3400\n",
      "3500\n",
      "3600\n",
      "3700\n",
      "3800\n",
      "3900\n",
      "4000\n",
      "4100\n",
      "4200\n",
      "4300\n",
      "4400\n",
      "4500\n",
      "4600\n",
      "4700\n",
      "4800\n",
      "4900\n",
      "5000\n",
      "5100\n",
      "5200\n",
      "5300\n",
      "5400\n",
      "5500\n",
      "5600\n",
      "5700\n",
      "5800\n",
      "5900\n",
      "6000\n",
      "6100\n",
      "6200\n",
      "6300\n",
      "6400\n",
      "6500\n",
      "6600\n",
      "6700\n",
      "6800\n",
      "6900\n",
      "7000\n",
      "7100\n",
      "7200\n",
      "7300\n",
      "7400\n",
      "7500\n",
      "7600\n",
      "7700\n",
      "7800\n",
      "7900\n",
      "8000\n",
      "8100\n",
      "8200\n",
      "8300\n",
      "8400\n",
      "8500\n",
      "8600\n",
      "8700\n",
      "8800\n",
      "8900\n",
      "9000\n",
      "9100\n",
      "9200\n",
      "9300\n",
      "9400\n",
      "9500\n",
      "9600\n",
      "9700\n",
      "9800\n",
      "9900\n",
      "10000\n",
      "10100\n",
      "10200\n",
      "10300\n",
      "10400\n",
      "10500\n",
      "10600\n",
      "10700\n",
      "10800\n",
      "10900\n",
      "11000\n",
      "11100\n",
      "11200\n",
      "11300\n",
      "11400\n",
      "11500\n",
      "11600\n",
      "11700\n",
      "11800\n",
      "11900\n",
      "12000\n",
      "12100\n",
      "12200\n",
      "12300\n",
      "12400\n",
      "12500\n",
      "12600\n",
      "12700\n",
      "12800\n",
      "12900\n",
      "13000\n",
      "13100\n",
      "13200\n",
      "13300\n",
      "13400\n",
      "13500\n",
      "13600\n",
      "13700\n",
      "13800\n",
      "13900\n",
      "14000\n",
      "14100\n",
      "14200\n",
      "14300\n",
      "14400\n",
      "14500\n",
      "14600\n",
      "14700\n",
      "14800\n",
      "14900\n",
      "15000\n",
      "15100\n",
      "15200\n",
      "15300\n",
      "15400\n",
      "15500\n",
      "15600\n",
      "15700\n",
      "15800\n",
      "15900\n",
      "16000\n",
      "16100\n",
      "16200\n",
      "16300\n",
      "16400\n",
      "16500\n",
      "16600\n",
      "16700\n",
      "16800\n",
      "16900\n",
      "17000\n",
      "17100\n",
      "17200\n",
      "17300\n",
      "17400\n",
      "17500\n",
      "17600\n",
      "17700\n",
      "17800\n",
      "17900\n",
      "18000\n",
      "18100\n",
      "18200\n",
      "18300\n",
      "18400\n",
      "18500\n",
      "18600\n",
      "18700\n",
      "18800\n",
      "18900\n",
      "19000\n",
      "19100\n",
      "19200\n",
      "19300\n",
      "19400\n",
      "19500\n",
      "19600\n",
      "19700\n",
      "19800\n",
      "19900\n",
      "20000\n",
      "20100\n",
      "20200\n",
      "20300\n",
      "20400\n",
      "20500\n",
      "20600\n",
      "20700\n",
      "20800\n",
      "20900\n",
      "21000\n",
      "21100\n",
      "21200\n",
      "21300\n",
      "21400\n",
      "21500\n",
      "21600\n",
      "21700\n",
      "21800\n",
      "21900\n",
      "22000\n",
      "22100\n",
      "22200\n",
      "22300\n",
      "22400\n",
      "22500\n",
      "22600\n",
      "22700\n",
      "22800\n",
      "22900\n",
      "23000\n",
      "23100\n",
      "23200\n",
      "23300\n",
      "23400\n",
      "23500\n",
      "23600\n",
      "23700\n",
      "23800\n",
      "23900\n",
      "24000\n",
      "24100\n",
      "24200\n",
      "24300\n",
      "24400\n",
      "24500\n",
      "24600\n",
      "24700\n",
      "24800\n",
      "24900\n",
      "25000\n",
      "25100\n",
      "25200\n",
      "25300\n",
      "25400\n",
      "25500\n",
      "25600\n",
      "25700\n",
      "25800\n",
      "25900\n",
      "26000\n",
      "26100\n",
      "26200\n",
      "26300\n",
      "26400\n",
      "26500\n",
      "26600\n",
      "26700\n",
      "26800\n",
      "26900\n",
      "27000\n",
      "27100\n",
      "27200\n",
      "27300\n",
      "27400\n",
      "27500\n",
      "27600\n",
      "27700\n",
      "27800\n",
      "27900\n",
      "28000\n",
      "28100\n",
      "28200\n",
      "28300\n",
      "28400\n",
      "28500\n",
      "28600\n",
      "28700\n",
      "28800\n",
      "28900\n",
      "29000\n",
      "29100\n",
      "29200\n",
      "29300\n",
      "29400\n",
      "29500\n",
      "29600\n",
      "29700\n",
      "29800\n",
      "29900\n",
      "30000\n",
      "30100\n",
      "30200\n",
      "30300\n",
      "30400\n",
      "30500\n",
      "30600\n",
      "30700\n",
      "30800\n",
      "30900\n",
      "31000\n",
      "31100\n",
      "31200\n",
      "31300\n",
      "31400\n",
      "31500\n",
      "31600\n",
      "31700\n",
      "31800\n",
      "31900\n",
      "32000\n",
      "32100\n",
      "32200\n",
      "32300\n",
      "32400\n",
      "32500\n",
      "32600\n",
      "32700\n",
      "32800\n",
      "32900\n",
      "33000\n",
      "33100\n",
      "33200\n",
      "33300\n",
      "33400\n",
      "33500\n",
      "33600\n",
      "33700\n",
      "33800\n",
      "33900\n",
      "34000\n",
      "34100\n",
      "34200\n",
      "34300\n",
      "34400\n",
      "34500\n",
      "34600\n",
      "34700\n",
      "34800\n",
      "34900\n",
      "35000\n",
      "35100\n",
      "35200\n",
      "35300\n",
      "35400\n",
      "35500\n",
      "35600\n",
      "35700\n",
      "35800\n",
      "35900\n",
      "36000\n",
      "36100\n",
      "36200\n",
      "36300\n",
      "36400\n",
      "36500\n",
      "36600\n",
      "36700\n",
      "36800\n",
      "36900\n",
      "37000\n",
      "37100\n",
      "37200\n",
      "37300\n",
      "37400\n",
      "37500\n",
      "37600\n",
      "37700\n",
      "37800\n",
      "37900\n",
      "38000\n",
      "38100\n",
      "38200\n",
      "38300\n",
      "38400\n",
      "38500\n",
      "38600\n",
      "38700\n",
      "38800\n",
      "38900\n",
      "39000\n",
      "39100\n",
      "39200\n",
      "39300\n",
      "39400\n",
      "39500\n",
      "39600\n",
      "39700\n",
      "39800\n",
      "39900\n",
      "40000\n",
      "40100\n",
      "40200\n",
      "40300\n",
      "40400\n",
      "40500\n",
      "40600\n",
      "40700\n",
      "40800\n",
      "40900\n",
      "41000\n",
      "41100\n",
      "41200\n",
      "41300\n",
      "41400\n",
      "41500\n",
      "41600\n",
      "41700\n",
      "41800\n",
      "41900\n",
      "42000\n",
      "42100\n",
      "42200\n",
      "42300\n",
      "42400\n",
      "42500\n",
      "42600\n",
      "42700\n",
      "42800\n",
      "42900\n",
      "43000\n",
      "43100\n",
      "43200\n",
      "-----COMA-----\n",
      "0\n",
      "100\n",
      "200\n",
      "300\n",
      "400\n",
      "500\n",
      "600\n",
      "700\n",
      "800\n",
      "900\n",
      "1000\n",
      "-----COMD-----\n",
      "0\n",
      "100\n",
      "200\n",
      "300\n",
      "400\n",
      "500\n",
      "600\n",
      "700\n",
      "800\n",
      "900\n",
      "1000\n",
      "1100\n",
      "1200\n",
      "1300\n",
      "1400\n",
      "1500\n",
      "1600\n",
      "1700\n",
      "1800\n",
      "1900\n",
      "2000\n",
      "2100\n",
      "2200\n",
      "2300\n",
      "2400\n",
      "-----COMF-----\n"
     ]
    }
   ],
   "source": [
    "listallcoms = []\n",
    "get_info_com('COM',coms)\n",
    "get_info_com('COMA',comas)\n",
    "get_info_com('COMD',comds)\n",
    "get_info_com('COMF',comfs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sauvegarde dans un fichier local (pour éviter de rejouer ce code à chaque fois)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "with open('coms.json', 'w') as f:\n",
    "    json.dump(listallcoms, f, indent=4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Récupération du détail de tous les événements dans un tableau"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "100\n",
      "200\n",
      "300\n",
      "400\n",
      "500\n",
      "600\n",
      "700\n",
      "800\n",
      "900\n",
      "1000\n",
      "1100\n",
      "1200\n",
      "1300\n",
      "1400\n",
      "1500\n",
      "1600\n",
      "1700\n",
      "1800\n",
      "1900\n",
      "2000\n",
      "2100\n",
      "2200\n",
      "2300\n",
      "2400\n",
      "2500\n",
      "2600\n",
      "2700\n",
      "2800\n",
      "2900\n",
      "3000\n",
      "3100\n",
      "3200\n",
      "3300\n",
      "3400\n",
      "3500\n",
      "3600\n",
      "3700\n",
      "3800\n",
      "3900\n",
      "4000\n",
      "4100\n",
      "4200\n",
      "4300\n",
      "4400\n",
      "4500\n",
      "4600\n",
      "4700\n",
      "4800\n",
      "4900\n",
      "5000\n"
     ]
    }
   ],
   "source": [
    "listallevt = []\n",
    "i = 0\n",
    "for e in evt:\n",
    "    if(i%100 == 0):\n",
    "        print(i)\n",
    "    i = i + 1\n",
    "    sparql = SPARQLWrapper(\"http://rdf.insee.fr/sparql\")\n",
    "    sparql.setQuery(\"\"\"\n",
    "       DESCRIBE <{}>\n",
    "    \"\"\".format(e))\n",
    "    sparql.setReturnFormat(JSON)\n",
    "    results = sparql.query().convert()\n",
    "    newevt = {}\n",
    "    newevt['creation'] = []\n",
    "    newevt['suppression'] = []\n",
    "    \n",
    "    for result in results[\"results\"][\"bindings\"]:\n",
    "        if(result['predicate']['value'] == 'http://rdf.insee.fr/def/geo#creation'):\n",
    "            newevt['creation'].append(result['object']['value'].split('/')[-1])\n",
    "        if(result['predicate']['value'] == 'http://rdf.insee.fr/def/geo#suppression'):\n",
    "            newevt['suppression'].append(result['object']['value'].split('/')[-1])\n",
    "        if(result['predicate']['value'] == 'http://rdf.insee.fr/def/geo#date'):\n",
    "            newevt['date'] = result['object']['value']\n",
    "                \n",
    "    newevt['id'] = e.replace('http://id.insee.fr/geo/evenementGeographique/','')\n",
    "    newevt['type'] = 'evenement'\n",
    "\n",
    "    listallevt.append(newevt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sauvegarde dans un fichier json pour éviter de rejouer le traitement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "with open('evts.json', 'w') as f:\n",
    "    json.dump(listallevt, f, indent=4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "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.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}