Článek přečtěte do 5 min.

Mapy jsou standardním způsobem vizualizace informací o poloze. Chcete-li vykreslit jakékoli místo na mapě, musíte k němu mít přiřazený pár zeměpisných souřadnic. V závislosti na odkazovaném souřadnicovém systému se dvojice souřadnic nazývá například x/y, zeměpisná délka/šířka nebo východ/sever.

Oracle Autonomous Database on Shared Infrastructure (ADB-S) nyní obsahuje balíček PL/SQL, který přímo převádí informace o adrese na takové dvojice geografických souřadnic (geokódované adresy) jako operaci v databázi. Tato operace se nazývá geokódování.

Oracle poskytuje několik možností, jak geokódovat data adresy nebo názvy míst. Dříve jste museli získat referenční datovou sadu geokódování a uložit ji do databáze, abyste mohli provádět geokódování v databázi. Několik poskytovatelů obsahu, jako například HERE nebo TomTom, poskytuje referenční data, která si uživatelé mohou licencovat samostatně, obvykle jako soubory DataPump nebo Transportable Tablespaces. Pro uživatele Oracle Autonomous Database již není potřeba mít referenční data. Nyní můžete k provádění geokódování použít balíček PL/SQL s názvem SDO_GCDR. To vám usnadňuje, urychluje a snižuje náklady vizualizovat vaše obchodní data na mapách a aplikovat na ně perspektivu umístění.

Balíček ADB Geocoder

Balíček obsahuje následující procedury a funkce:

  • Dva postupy pro udělení přístupu uživateli databáze a odebrání přístupu uživateli databáze.
    • Tyto funkce provádějte jako uživatel ADMIN.
  • Funkce ELOC_GEOCODE je přetížená funkce, kterou lze použít pro (dopředné) geokódování i zpětné geokódování. V závislosti na dostupných adresových datech a požadovaném formátu návratové hodnoty zavoláte funkci následovně:
    1. ELOC_GEOCODE( ulice VARCHAR2, město VARCHAR2, region VARCHAR2, PSČ VARCHAR2, cc2 VARCHAR2, režim shody V VARCHAR2 DEFAULT ‚RELAX_POSTAL_CODE‘)
      • Návratová hodnota: JSON
      • GeoJSON může obsahovat nulu, jednu nebo více shod, které jsou nejlepší shody podle vstupních parametrů včetně MATCH_MODE (viz vysvětlení po tomto shrnutí).
      • Tuto funkci použijte, pokud jsou pole adresy známá a odpovídají parametrům funkce.
    2. ELOC_GEOCODE( řádek_adresy VARCHAR2)
      • Návratová hodnota: JSON
      • Tuto funkci použijte, když pole adresy nejsou známa a je k dispozici pouze nestrukturovaná adresa (jako na jednom řádku adresy).
    3. ELOC_GEOCODE_AS_GEOM( ulice VARCHAR2, město VARCHAR2, region VARCHAR2, PSČ VARCHAR2, cc2 VARCHAR2, režim shody V VARCHAR2 DEFAULT ‚RELAX_POSTAL_CODE‘)
      • Návratová hodnota objektu SDO_GEOMETRY (geometrie bodu definovaná zeměpisnou délkou a šířkou)
      • Stejné jako vstup strukturované adresy výše (viz 1).
    4. ELOC_GEOCODE_AS_GEOM( řádek_adresy VARCHAR2)
      • Stejné jako funkce nestrukturovaného zadávání adresy výše (viz 2).
    5. ELOC_GEOCODE( zeměpisná délka NUMBER, zeměpisná šířka NUMBER)
      • Funkce zpětného geokodéru, která určuje skutečnou adresu na základě daných hodnot zeměpisné délky a šířky.

Každou z funkcí provádíte na jedné adrese. Dávkové geokódování pro seznam adres není podporováno.

Co je MATCH MODE? Režim shody pro operaci geokódování určuje, jak blízko se musí atributy vstupní adresy shodovat s referenčními daty používanými pro geokódování. Výchozí hodnota používaná SDO_GCDR je ‚RELAX_POSTAL_CODE‘. Seznam všech dostupných režimů shody naleznete v příručce Oracle Spatial Developer’s Guide 19c.

Dopředné geokódování

Dopředné geokódování znamená začít s adresou nebo pojmenovaným místem a převést je na zeměpisné souřadnice. Podívejme se, jak funkce voláte, co vracejí a jak můžete pomocí dalších funkcí extrahovat konkrétní hodnoty z výsledků, ať už jako JSON nebo SDO_GEOMETRY. Používáme sadu vzorových adres z několika zemí uložených ve vzorové tabulce.

-- Sample address data
street,city,region,postal_code,country
Bötzower Str. 24,Berlin,BE,10407,DE
Bd Carl-Vogt 67,Genève,,1205,CH
100 N Renfrew St,Vancouver,BC,V5K 4W3,CA
2 Chome-5-8 Kitaaoyama,Tokyo,,107-0061,JP
123 Beacon St,Boston,MA,02116,US

-- Structured input returning JSON or SDO_GEOMETRY
select sdo_gcdr.eloc_geocode(street, city, region, postal_code, country, 'RELAX_POSTAL_CODE') from sample_addresses;         -- Request 1
select sdo_gcdr.eloc_geocode_as_geom(street, city, region, postal_code, country, 'RELAX_POSTAL_CODE') from sample_addresses; -- Request 2

-- Unstructured input returning JSON or SDO_GEOMETRY
select sdo_gcdr.eloc_geocode(address_line) from sample_addresses;                                       -- Request 3
select sdo_gcdr.eloc_geocode_as _geom(address_line) from sample_addresses;                              -- Request 4

Poznámka: ADDRESS_LINE je jednoduše zřetězený řetězec s ulicí, městem, regionem, PSČ a zemí pomocí čárky jako oddělovače.

Zde je odpověď na každou žádost.

-- Return values for request 1
{
        "id": "0",
        "matchCount": "1",
        "matches": [
            {
                "sequence": "0",
                "x": 13.43335,
                "y": 52.53107,
                "houseNumber": "24",
                "street": "Bötzowstraße",
                "settlement": "Prenzlauer Berg",
                "municipality": "Berlin",
                "region": "BERLIN",
                "postalCode": "10407",
                "country": "DE",
                "language": "GER",
                "name": "",
                "edgeId": 53499872,
                "percent": 0.82,
                "side": "R",
                "matchCode": 4,
                "matchVector": "??010121010??200?"
            }
        ]
    }
]
[
    {
        "id": "0",
        "matchCount": "1",
        "matches": [
            {
                "sequence": "0",
                "x": 6.13693,
                "y": 46.19739,
                "houseNumber": "67",
                "street": "Boulevard Carl-Vogt",
                "settlement": "Genève",
                "municipality": "Genève",
                "region": "GENÈVE",
                "postalCode": "1205",
                "country": "CH",
                "language": "GER",
                "name": "",
                "edgeId": 56045106,
                "percent": 0.4,
                "side": "R",
                "matchCode": 1,
                "matchVector": "??010101010??400?"
            }
        ]
    }
]
[
    {
        "id": "0",
        "matchCount": "1",
        "matches": [
            {
                "sequence": "0",
                "x": -123.04406,
                "y": 49.28577,
                "houseNumber": "100",
                "street": "N Renfrew St",
                "settlement": "Vancouver",
                "municipality": "Vancouver",
                "region": "BC",
                "postalCode": "V5K",
                "country": "CA",
                "language": "ENG",
                "name": "",
                "edgeId": 811555206,
                "percent": 0.13,
                "side": "R",
                "matchCode": 1,
                "matchVector": "??010001010??000?"
            }
        ]
    }
]
[
    {
        "id": "0",
        "matchCount": "1",
        "matches": [
            {
                "sequence": "0",
                "x": 139.7333,
                "y": 35.65,
                "houseNumber": "",
                "street": "",
                "settlement": "",
                "municipality": "null",
                "region": "TOKYO TO",
                "postalCode": "107-0061",
                "country": "JP",
                "language": "JPN",
                "name": "",
                "edgeId": -1,
                "percent": 0.0,
                "side": "",
                "matchCode": 4,
                "matchVector": "???10101110??400?"
            }
        ]
    }
]
[
    {
        "id": "0",
        "matchCount": "1",
        "matches": [
            {
                "sequence": "0",
                "x": -71.07355166666666,
                "y": 42.355174166666664,
                "houseNumber": "123",
                "street": "Beacon St",
                "settlement": "Boston",
                "municipality": "Suffolk",
                "region": "MA",
                "postalCode": "02116",
                "country": "US",
                "language": "ENG",
                "name": "",
                "edgeId": 946710796,
                "percent": 0.08333333333333333,
                "side": "R",
                "matchCode": 1,
                "matchVector": "???10101010??000?"
            }
        ]
    }
]

-- Return values for request 2
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(13.43335, 52.53107, NULL), NULL, NULL)
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(6.13693, 46.19739, NULL), NULL, NULL)
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(-123.04406, 49.28577, NULL), NULL, NULL)
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(139.7333, 35.65, NULL), NULL, NULL)
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(-71.07355166666666, 42.355174166666664, NULL), NULL, NULL)

-- Return values for request 3
[
    {
        "id": "0",
        "matchCount": "1",
        "matches": [
            {
                "sequence": "0",
                "x": 13.45507,
                "y": 52.53206,
                "houseNumber": "",
                "street": "STORKOWER STRAßE",
                "settlement": "PRENZLAUER BERG",
                "municipality": "BERLIN",
                "region": "BERLIN",
                "postalCode": "10407",
                "country": "DE",
                "language": "GER",
                "name": "REIFENBESTELLUNG 24",
                "edgeId": 53594421,
                "percent": 0.29,
                "side": "R",
                "matchCode": 1,
                "matchVector": "???01101010??000?"
            }
        ]
    }
]
[
    {
        "id": "0",
        "matchCount": "1",
        "matches": [
            {
                "sequence": "0",
                "x": 6.1358,
                "y": 46.19802,
                "houseNumber": "46",
                "street": "Boulevard Carl-Vogt",
                "settlement": "Genève",
                "municipality": "Genève",
                "region": "GENÈVE",
                "postalCode": "1205",
                "country": "CH",
                "language": "GER",
                "name": "",
                "edgeId": 723493856,
                "percent": 0.0,
                "side": "L",
                "matchCode": 3,
                "matchVector": "???14101010??000?"
            }
        ]
    }
]
[
    {
        "id": "0",
        "matchCount": "1",
        "matches": [
            {
                "sequence": "0",
                "x": -123.04406,
                "y": 49.28577,
                "houseNumber": "100",
                "street": "N Renfrew St",
                "settlement": "Vancouver",
                "municipality": "Vancouver",
                "region": "BC",
                "postalCode": "V5K",
                "country": "CA",
                "language": "ENG",
                "name": "",
                "edgeId": 811555206,
                "percent": 0.13,
                "side": "R",
                "matchCode": 1,
                "matchVector": "??010001010??000?"
            }
        ]
    }
]
[
    {
        "id": "0",
        "matchCount": "1",
        "matches": [
            {
                "sequence": "0",
                "x": 139.80885,
                "y": 35.68408,
                "houseNumber": "",
                "street": "",
                "settlement": "Tokyo",
                "municipality": "Tokyo",
                "region": "Japan",
                "postalCode": "null",
                "country": "JP",
                "language": "DAN",
                "name": "",
                "edgeId": 720645277,
                "percent": 0.0,
                "side": "L",
                "matchCode": 4,
                "matchVector": "???31111110??201?"
            }
        ]
    }
]
[
    {
        "id": "0",
        "matchCount": "1",
        "matches": [
            {
                "sequence": "0",
                "x": -71.07355166666666,
                "y": 42.355174166666664,
                "houseNumber": "123",
                "street": "Beacon St",
                "settlement": "Boston",
                "municipality": "Suffolk",
                "region": "MA",
                "postalCode": "02116",
                "country": "US",
                "language": "ENG",
                "name": "",
                "edgeId": 946710796,
                "percent": 0.08333333333333333,
                "side": "R",
                "matchCode": 1,
                "matchVector": "???10101010??000?"
            }
        ]
    }
]

-- Return values for request 4
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(13.45507, 52.53206, NULL), NULL, NULL)
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(6.1358, 46.19802, NULL), NULL, NULL)
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(-123.04406, 49.28577, NULL), NULL, NULL)
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(139.80885, 35.68408, NULL), NULL, NULL)
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(-71.07355166666666, 42.355174166666664, NULL), NULL, NULL)

Při pohledu na vrácený dokument JSON najdete více než jen vrácenou zeměpisnou délku a šířku. Získáte další informace, jako je rozdíl mezi obcí a osadou a stranou ulice. Tímto způsobem můžete obohatit své adresní údaje a využít geokódování pro vymazání adresních údajů. Hodnoty uvedené jako MATCH CODE a MATCH VECTOR v dokumentu JSON udávají, které atributy adresy byly použity pro geokódování, a poskytují podrobné informace o přesnosti výsledku geokódování.

Nyní můžete vrácený výsledek dále zpracovat, uchovat jej provedením UPDATE v tabulce dat adresy nebo v ní použít váš kód SQL a PL/SQL. Příklady posledně jmenovaného jsou extrahování hodnot zeměpisné délky a šířky z výsledku goding.

-- Extract lon/lat from returned JSON document
with lonlat_from_json as (
  select
    sdo_gcdr.eloc_geocode(street, city, region, postal_code, country) as j
  from
    sample_addresses
)
select
  json_value(j,'$[0].matches[0].matchCode') as matchCode,
  json_value(j,'$[0].matches[0].x') as lon,
  json_value(j,'$[0].matches[0].y') as lat
from
  lonlat_from_json;

-- Extrct lon/lat from SDO_GEOMETRY object
with lonlat_from_sdo_geometry as (
  select
    sdo_gcdr.eloc_geocode_as_geom(street, city, region, postal_code, country) geom
  from
    sample_addresses
)
select
  g.geom.sdo_point.x as lon,
  g.geom.sdo_point.y as lat
from
  lonlat_from_sdo_geometry g;

Zde jsou výsledky těchto dvou dotazů:

MATCHCODE LON LET
4 13.43335 52.53107
1 6.13693 46.19739
1 -123.04406 49.28577
4 139.7333 35.65
1 -71.07355166666666 42.355174166666664

 

LON LET
13.43335 52.53107
6.13693 46.19739
-123.04406 49.28577
139.7333 35.65
-71.07355166666666 42.355174166666664

Reverzní geokódování

S reverzním geokódováním začnete zeměpisnými souřadnicemi a převedete je na adresu.

Povolání:

select
    sdo_gcdr.eloc_geocode(13.4129, 52.52115)
  from
    dual;

vrací:

[
    {
        "id": "0",
        "matchCount": "1",
        "matches": [
            {
                "sequence": "0",
                "x": 13.412898244410691,
                "y": 52.521153112181054,
                "houseNumber": "",
                "street": "Alexanderplatz",
                "settlement": "Mitte",
                "municipality": "Berlin",
                "region": "BERLIN",
                "postalCode": "10178",
                "country": "DE",
                "language": "GER",
                "name": "",
                "edgeId": 860397302,
                "percent": 0.559600997510756,
                "side": "R",
                "matchCode": 1,
                "matchVector": "???11141414??404?"
            }
        ]
    }
]

Různé části adresy můžete znovu extrahovat pomocí JSON_VALUE, jak je popsáno výše.

Zdroj: Oracle