API Reference

close_connection(conn::MySQL.Connection)

Close the given MySQL/MariaDB database connection.

Arguments

• conn::MySQL.Connection: The connection object to close.

elevation_data_full(ws_host_port::String, selection::Dict) -> DataFrame

Retrieve comprehensive elevation data for specified geographic regions from a web service.

This function sends POST requests to a web service to obtain various elevation statistics (minimum, maximum, mean, standard deviation, median, and entropy) for specified geographic regions. The regions are defined by their shapes and IDs provided in the selection dictionary.

Arguments

• ws_host_port::String: The host and port of the web service providing the elevation data.
• selection::Dict: A dictionary containing geo_id and shape_obj, representing geographic identifiers and their corresponding shapes.

Returns

• DataFrame: A DataFrame with columns for geographic ID, minimum elevation, maximum elevation, mean elevation, standard deviation of elevation, median elevation, and entropy of elevation data.

Example

wshostport = "http://example.com:5003" selection = Dict("geoid" => ["ID1", "ID2"], "shapeobj" => ["SHAPE1", "SHAPE2"]) df = elevationdatafull(wshostport, selection)

In this example, the function queries a web service for elevation data for specified regions and returns the results in a DataFrame.

establish_connection(host, user, password, dbname; port=3306) -> MySQL.Connection

Establish a connection to a MySQL/MariaDB database.

Arguments

• host::String: Database host
• user::String: Username
• password::String: Password
• dbname::String: Database name
• port::Int=3306: Port number (default is 3306)

Returns

• MySQL.Connection: A connection object to the database.

eu_air(conn::MySQL.Connection, selection::Dict) -> DataFrame

Retrieve air traffic data for specified geographic regions within Europe from a MySQL database.

This function queries a database for air traffic information, such as the number of airports and total flights, within specified geographic regions in Europe. The query filters data based on the shapes and IDs provided in the selection dictionary. Acceptable years for the data are 1961, 71, 81, 91, 2001, 2011.

Arguments

• conn::MySQL.Connection: An active connection to a MySQL database.
• selection::Dict: A dictionary containing geo_id and shape_obj, representing geographic identifiers and their corresponding shapes.

Returns

• DataFrame: A DataFrame with columns for the geographic ID, the number of airports, the number of flights, and the region set to 'EU'.

Example

conn = MySQL.connect("localhost", "username", "password", db = "dbname") selection = Dict("geoid" => ["ID1", "ID2"], "shapeobj" => ["SHAPE1", "SHAPE2"]) df = eu_air(conn, selection)

This example queries air traffic data for specified European regions and returns the results in a DataFrame.

eu_countries(conn::MySQL.Connection) -> DataFrame

Retrieve geographical data for a specific level of administrative division from a MySQL database.

This function queries a database for geographical information such as the unique identifier (geoid), the shape of the region in WKT format (shapeobj), and the area of each geographical entity. The specific data retrieved depends on the function being called, corresponding to different administrative divisions like countries, regions, provinces, etc.

Arguments

• conn::MySQL.Connection: An active connection to a MySQL database.

Returns

• DataFrame: A DataFrame containing columns for the geographic ID, WKT representation of the shape, area of the geographical entity, and the region (either 'EU' or 'US').

Example

conn = MySQL.connect("localhost", "username", "password", db = "dbname") df = eu_countries(conn)

This example queries geographical data for European countries and returns the results in a DataFrame.

eu_lau(conn::MySQL.Connection, selection::Dict) -> DataFrame

Retrieve European Union (EU) Local Administrative Units (LAU) population data for specified geographic areas and years.

This function retrieves population data for specified LAUs in the European Union for a given year. It communicates with a MySQL database connection to execute SQL queries and returns the results as a DataFrame.

Arguments

• conn::MySQL.Connection: The MySQL database connection to use for querying EU LAU population data.
• selection::Dict: A dictionary specifying the selection criteria including year (acceptable years: 1961, 71, 81, 91, 2001, 2011), and geo_id (LAU codes).

Returns

• DataFrame: A DataFrame containing columns for geo_id (LAU code), region (region identifier, always "EU"), and population representing the population count for the specified LAUs in the given year.

Example

conn = MySQL.Connection(user="user", password="password", host="localhost", db="eu_population")
selection = Dict("year" => 2011, "geo_id" => ["LAU1", "LAU2"])
df = eu_lau(conn, selection)

This example retrieves population data for two EU LAUs in the year 2011.

eu_lau(conn::MySQL.Connection) -> DataFrame # Description Retrieve geographical data for European LAU (local administrative units) from a MySQL database.

# Arguments
- `conn::MySQL.Connection`: An active connection to a MySQL database.

# Returns
- `DataFrame`: A DataFrame containing columns for the geographic ID, WKT representation of the shape, area of the municipality, and the region 'EU'.

eu_lfs(conn::MySQL.Connection, selection::Dict) -> DataFrame
# Description
Retrieve annual job data for EU regions using the LFS dataset from a MySQL database.

# Arguments
- `conn::MySQL.Connection`: An active connection to a MySQL database.
- `selection::Dict`: A dictionary specifying the selection criteria including the year and geo_ids.

# Returns
- `DataFrame`: A DataFrame containing columns for the region ('EU'), geographic ID, aggregation level, industry identifier, and average number of annual jobs.

eu_macro_regions(conn::MySQL.Connection) -> DataFrame

Retrieve geographical data for European macro regions from a MySQL database.

This function queries a MySQL database for geographical information, including the unique identifier (geoid), the shape of the region in WKT format (shapeobj), and the area of each macro region.

Arguments

• conn::MySQL.Connection: An active connection to a MySQL database.

Returns

• DataFrame: A DataFrame containing columns for the geographic ID, WKT representation of the shape, area of the macro region, and the region 'EU'.

Example

conn = MySQL.connect("localhost", "username", "password", db = "dbname") df = eumacroregions(conn)

This example queries geographical data for European macro regions and returns the results in a DataFrame.

eu_nuts(conn::MySQL.Connection, selection::Dict) -> DataFrame

Retrieve European Union (EU) Nomenclature of Territorial Units for Statistics (NUTS) population data for specified geographic areas and years.

This function retrieves population data for specified NUTS regions in the European Union for a given year. It communicates with a MySQL database connection to execute SQL queries and returns the results as a DataFrame.

Arguments

• conn::MySQL.Connection: The MySQL database connection to use for querying EU NUTS population data.
• selection::Dict: A dictionary specifying the selection criteria including year (acceptable years: 2014-2022), and geo_id (NUTS codes).

Returns

• DataFrame: A DataFrame containing columns for geo_id (NUTS code), region (region identifier, always "EU"), and population representing the population count for the specified NUTS regions in the given year.

Example

conn = MySQL.Connection(user="user", password="password", host="localhost", db="eupopulation") selection = Dict("year" => 2018, "geoid" => ["NUTS1", "NUTS2"]) df = eu_nuts(conn, selection)

This example retrieves population data for two EU NUTS regions in the year 2018.

eu_ports(conn::MySQL.Connection, selection::Dict) -> DataFrame

Retrieve information about European Union (EU) ports, including the number of ports and total tonnage, for specified geographic areas.

This function communicates with a MySQL database connection to execute SQL queries and retrieve information about EU ports. It calculates the number of ports and total tonnage for the specified geographic areas based on their intersections with port shapes.

Arguments

• conn::MySQL.Connection: The MySQL database connection to use for querying EU port data.
• selection::Dict: A dictionary specifying the selection criteria including geo_id (port IDs) and shape_obj (geometric shapes).

Returns

• DataFrame: A DataFrame containing columns for geo_id (port ID), region (region identifier, always "EU"), ports (number of ports), and tonnage (total tonnage in tonnage units) representing port information for the specified areas.

Example

conn = MySQL.Connection(user="user", password="password", host="localhost", db="euportsdb") selection = Dict("geoid" => ["PORT1", "PORT2"], "shapeobj" => ["POINT(1 2)", "POINT(3 4)"]) df = eu_ports(conn, selection)

This example retrieves information about two EU ports based on their specified port IDs and shapes.

eu_power(conn::MySQL.Connection, selection::Dict) -> DataFrame

Retrieve information about power stations and transmission lines in the European Union (EU) for specified geographic areas.

This function communicates with a MySQL database connection to execute SQL queries and retrieve information about power stations and transmission lines in the EU. It calculates the number of power stations, total capacity, and total length of transmission lines for the specified geographic areas based on their intersections with station and line shapes.

Arguments

• conn::MySQL.Connection: The MySQL database connection to use for querying EU power data.
• selection::Dict: A dictionary specifying the selection criteria including geo_id (station IDs) and shape_obj (geometric shapes).

Returns

• DataFrame: A DataFrame containing columns for geo_id (station ID), region (region identifier, always "EU"), stations (number of power stations), capacity (total capacity in MW), and lines (total length of transmission lines in meters) representing power station and transmission line information for the specified areas.

Example

conn = MySQL.Connection(user="user", password="password", host="localhost", db="eupowerdb") selection = Dict("geoid" => ["STATION1", "STATION2"], "shapeobj" => ["POINT(1 2)", "POINT(3 4)"]) df = eu_power(conn, selection)

This example retrieves information about two EU power stations and their associated transmission lines based on specified station IDs and shapes.

eu_provinces(conn::MySQL.Connection) -> DataFrame
# Description
Retrieve geographical data for European provinces from a MySQL database.

# Arguments
- `conn::MySQL.Connection`: An active connection to a MySQL database.

# Returns
- `DataFrame`: A DataFrame containing columns for the geographic ID, WKT representation of the shape, area of the province, and the region 'EU'.

eu_rail(conn::MySQL.Connection, selection::Dict) -> DataFrame

Retrieve information about railway stations and lines in the European Union (EU) for specified geographic areas.

This function communicates with a MySQL database connection to execute SQL queries and retrieve information about railway stations and lines in the EU. It calculates the number of railway stations and the total length of railway lines for the specified geographic areas based on their intersections with station and line shapes.

Arguments

• conn::MySQL.Connection: The MySQL database connection to use for querying EU railway data.
• selection::Dict: A dictionary specifying the selection criteria including geo_id (station IDs) and shape_obj (geometric shapes).

Returns

• DataFrame: A DataFrame containing columns for geo_id (station ID), region (region identifier, always "EU"), stations (number of railway stations), and lines (total length of railway lines in meters) representing railway station and line information for the specified areas.

Example

conn = MySQL.Connection(user="user", password="password", host="localhost", db="euraildb") selection = Dict("geoid" => ["STATION1", "STATION2"], "shapeobj" => ["POINT(1 2)", "POINT(3 4)"]) df = eu_rail(conn, selection)

This example retrieves information about two EU railway stations and their associated railway lines based on specified station IDs and shapes.

eu_rea(conn::MySQL.Connection, selection::Dict) -> DataFrame
# Description
Retrieve annual job data for EU regions using the REA dataset from a MySQL database.

# Arguments
- `conn::MySQL.Connection`: An active connection to a MySQL database.
- `selection::Dict`: A dictionary specifying the selection criteria including the year and geo_ids.

# Returns
- `DataFrame`: A DataFrame containing columns for the region ('EU'), geographic ID, aggregation level, industry identifier, and average number of annual jobs.

eu_regions(conn::MySQL.Connection) -> DataFrame

Retrieve geographical data for European regions from a MySQL database.

This function queries a MySQL database for geographical information, including the unique identifier (geoid), the shape of the region in WKT format (shapeobj), and the area of each region.

Arguments

• conn::MySQL.Connection: An active connection to a MySQL database.

Returns

• DataFrame: A DataFrame containing columns for the geographic ID, WKT representation of the shape, area of the region, and the region 'EU'.

Example

conn = MySQL.connect("localhost", "username", "password", db = "dbname") df = eu_regions(conn)

This example queries geographical data for European regions and returns the results in a DataFrame.

eu_sbs(conn::MySQL.Connection, selection::Dict) -> DataFrame
# Description
Retrieve annual job data for EU regions using the SBS dataset from a MySQL database.

# Arguments
- `conn::MySQL.Connection`: An active connection to a MySQL database.
- `selection::Dict`: A dictionary specifying the selection criteria including the year and geo_ids.

# Returns
- `DataFrame`: A DataFrame containing columns for the region ('EU'), geographic ID, aggregation level, industry identifier, and average number of annual jobs.

tot_footprint_avg_height(conn::MySQL.Connection, selection::Dict) -> DataFrame

Retrieve the total footprint and average height of buildings for specified geographic regions from a MySQL database.

This function executes two SQL queries to calculate the total footprint area and the average height of buildings within specified geographic regions. The geographic regions are defined by the shapes and IDs provided in the selection dictionary.

Arguments

• conn::MySQL.Connection: An active connection to a MySQL database.
• selection::Dict: A dictionary containing geo_id and shape_obj, representing geographic identifiers and their corresponding shapes.

Returns

• DataFrame: A DataFrame with columns for the geographic ID, total footprint area, average height of buildings.

Example

conn = MySQL.connect("localhost", "username", "password", db = "dbname") selection = Dict("geoid" => ["ID1", "ID2"], "shapeobj" => ["SHAPE1", "SHAPE2"]) df = totfootprintavg_height(conn, selection)

This example queries the total footprint information and average height for specified regions and returns the results in a DataFrame.

geology_full(conn::MySQL.Connection, selection::Dict) -> DataFrame

Retrieve geology data for specified geographic areas and attributes.

This function retrieves geology data for specified geographic areas based on the provided selection criteria. It communicates with a MySQL database connection to execute SQL queries and returns the results as a DataFrame.

Arguments

• conn::MySQL.Connection: The MySQL database connection to use for querying geology data.
• selection::Dict: A dictionary specifying the selection criteria including geo_id, shape_obj, region, and area.

Returns

• DataFrame: A DataFrame containing columns for geo_id (geographic ID), region (region identifier), and various attributes representing the percentage of geological eras and rock types within the specified geographic areas.

Example

conn = MySQL.Connection(user="user", password="password", host="localhost", db="geologydata") selection = Dict("geoid" => ["geo1", "geo2"], "shapeobj" => ["WKT1", "WKT2"], "region" => "US", "area" => [1000, 2000]) df = geologyfull(conn, selection)

This example retrieves geology data for two geographic areas in the "US" region with specified shape data and areas.

getairportstraffic( table::String, selection::Dict, system::OrchestratorRegistry.ServiceRegistry ) -> DataFrame

Retrieves airport traffic data for Europe or the United States based on the specified table and selection criteria.

This function dynamically connects to a service retrieved from the provided ServiceRegistry, fetches the appropriate data based on the table argument, and applies the selection criteria. It supports retrieving data for European air traffic (eu_air) or U.S. air traffic (us_air).

Arguments

• table::String: Specifies the table to query, such as 'eu_air' for Europe or 'us_air' for the United States.
• selection::Dict: A dictionary containing selection criteria, such as geo_id and shape_obj.
• system::OrchestratorRegistry.ServiceRegistry: A service registry used to retrieve the FACT_air service for database connection details.

Returns

• DataFrame: A DataFrame containing the queried air traffic data, with columns such as geo_id, airports, and flights.

Behavior

• Retrieves the FACT_air service from the ServiceRegistry to configure the database connection.
• Dynamically maps the table argument to the appropriate data-fetching function (eu_air or us_air).
• Fetches and filters the data according to the specified selection criteria.

Raises

• Error: If the table argument does not match a supported table (eu_air or us_air).

Example

Define the selection criteria and retrieve European air traffic data:

# Define selection criteria
selection = Dict("geo_id" => ["ID1"], "shape_obj" => ["SHAPE1"])

# Create a ServiceRegistry and add the required service
system = ServiceRegistry()
add_service!(system, Service("FACT_air", "FACT_air_service", "http://localhost", 8080))

# Fetch data for European air traffic
df = get_airports_traffic("eu_air", selection, system)

println(df)

In this example, the function retrieves air traffic data for the specified geo_id and shape_obj from the European dataset.

get_all_objects_from_table(scale::String, df::DataFrame, table_dict::Dict; host::String="127.0.0.1") -> DataFrame

Fetches and compiles objects into a DataFrame based on a specified scale, using predefined actions for each table.

This function interprets a scale string to determine which tables to query. For each table identified in the scale, it executes a corresponding action from table_dict and appends the results to the provided DataFrame df. It ensures that the types of the columns in the appended data match the types in df.

Arguments

• scale::String: A string that, when interpreted, defines the tables and/or actions to be queried.
• df::DataFrame: The DataFrame to which the results will be appended, defining the data types of contents.
• table_dict::Dict: A dictionary mapping table names to functions. Each function is responsible for fetching data from the corresponding table and should return a DataFrame.
• host::String: (Optional) The host address for the MySQL database connection. Defaults to "127.0.0.1".

Returns

• DataFrame: The original DataFrame df appended with the results from each table's action.

Usage

The function is designed to work with a flexible scale definition. The scale string is interpreted to identify specific tables or actions. For each identified table, the function fetches data using the corresponding function in table_dict.

Notes

• The function establishes and closes a MySQL database connection within its scope.
• It performs type checking and conversion to ensure that the data types of the columns in the result match those in the original df.
• Additional data type conversions should be added as necessary for other column types.

getannualjobs( table::String, selection::Dict, system::OrchestratorRegistry.ServiceRegistry ) -> DataFrame

Retrieves annual job data from a specific dataset and table.

This function dynamically connects to a service retrieved from the provided ServiceRegistry, fetches job data based on the specified table, and applies the given selection criteria. It supports querying datasets for both U.S. and EU job data.

Arguments

• table::String: The name of the dataset and table to query, such as 'us_lodes' or 'eu_lfs'.
• selection::Dict: A dictionary specifying the selection criteria, such as:
  • "year": The year to query.
  • "geo_id": A list of geographic IDs to filter by.
  • "agg_level": The level of aggregation for the industrial classification.
• system::OrchestratorRegistry.ServiceRegistry: A service registry used to retrieve the FACT_jobs service for database connection details.

Returns

• DataFrame: A DataFrame containing the queried job data with the following columns:
  • geo_id: Geographic ID of the region.
  • agg_level: Aggregation level for industrial classification.
  • industry: Industry identifier.
  • jobs: Average number of annual jobs.

Behavior

• Retrieves the FACT_jobs service from the ServiceRegistry to configure the database connection.
• Dynamically maps the table argument to the appropriate data-fetching function.
• Fetches and filters job data based on the specified selection criteria.

Raises

• Error: If the table argument does not match a supported table.

Example

Define the selection criteria and retrieve annual job data for the U.S.:

# Define selection criteria
selection = Dict("year" => 2023, "geo_id" => ["US001", "US002"], "agg_level" => 2)

# Create a ServiceRegistry and add the required service
system = ServiceRegistry()
add_service!(system, Service("FACT_jobs", "FACT_jobs_service", "http://localhost", 8080))

# Fetch job data
df = get_annual_jobs("us_lodes", selection, system)

println(df)

In this example, the function retrieves job data for U.S. regions using the LODES dataset for the year 2023, filtering by specific geographic IDs and aggregation level.

getannualpopulation( table::String, selection::Dict, system::OrchestratorRegistry.ServiceRegistry ) -> DataFrame

Retrieves annual population data for specified geographic areas and years.

This function dynamically connects to a service retrieved from the provided ServiceRegistry, fetches population data based on the specified table, and applies the given selection criteria. It supports querying population datasets for both EU and US geographic regions.

Arguments

• table::String: The name of the table to query for population data, such as 'eu_lau', 'eu_nuts', 'us_block', or 'us_county'.
• selection::Dict: A dictionary specifying the selection criteria, including:
  • "year": The year to query.
  • "geo_id": A list of geographic IDs to filter by.
• system::OrchestratorRegistry.ServiceRegistry: A service registry used to retrieve the FACT_population service for database connection details.

Returns

• DataFrame: A DataFrame containing population data with the following columns:
  • geo_id: Geographic ID of the region.
  • population: Population count for the specified geographic areas in the given year.

Behavior

• Retrieves the FACT_population service from the ServiceRegistry to configure the database connection.
• Dynamically maps the table argument to the appropriate data-fetching function.
• Fetches and filters population data based on the specified selection criteria.

Raises

• Error: If the table argument does not match a supported table.

Example

Define the selection criteria and retrieve annual population data:

# Define selection criteria
table = "eu_lau"
selection = Dict(
    "year" => 2011,
    "geo_id" => ["LAU1", "LAU2"]
)

# Create a ServiceRegistry and add the required service
system = ServiceRegistry()
add_service!(system, Service("FACT_population", "FACT_population_service", "http://localhost", 8080))

# Fetch population data
df = get_annual_population(table, selection, system)

println(df)

In this example, the function retrieves population data for two EU LAUs in the year 2011 using the 'eu_lau' table.

getbldgsfootprint_height( table::String, selection::Dict, system::OrchestratorRegistry.ServiceRegistry ) -> DataFrame

Retrieves building footprint and height data based on the specified table and selection criteria.

This function dynamically connects to a service retrieved from the provided ServiceRegistry, queries data from the specified table, and applies the selection criteria. It retrieves data on building footprints and heights, such as percentages, maximum values, averages, and more.

Arguments

• table::String: Specifies the table to query, such as 'footprint_avg_height'.
• selection::Dict: A dictionary containing selection criteria, such as geo_id and shape_obj.
• system::OrchestratorRegistry.ServiceRegistry: A service registry used to retrieve the FACT_bldgs service for database connection details.

Returns

• DataFrame: A DataFrame containing the queried building data, with columns such as geo_id, perc_footprint, max_footprint, avg_footprint, and avg_height.

Behavior

• Retrieves the FACT_bldgs service from the ServiceRegistry to configure the database connection.
• Dynamically maps the table argument to the appropriate data-fetching function (e.g., footprint_avg_height).
• Fetches and filters the data according to the specified selection criteria.

Raises

• Error: If the table argument does not match a supported table (e.g., 'footprint_avg_height').

Example

Define the selection criteria and retrieve building footprint and height data:

# Define selection criteria
selection = Dict("geo_id" => ["ID1"], "shape_obj" => ["SHAPE1"])

# Create a ServiceRegistry and add the required service
system = ServiceRegistry()
add_service!(system, Service("FACT_bldgs", "FACT_bldgs_service", "http://localhost", 8080))

# Fetch building data
df = get_bldgs_footprint_height("footprint_avg_height", selection, system)

println(df)

In this example, the function retrieves building footprint and height data for the specified geo_id and shape_obj from the footprint_avg_height dataset.

getelevationdata( service_query::String, selection::Dict, system::OrchestratorRegistry.ServiceRegistry ) -> DataFrame

Retrieves elevation data based on the specified service query and selection criteria.

This function dynamically connects to a service retrieved from the provided ServiceRegistry, fetches data from the specified service query, and applies the selection criteria. It supports retrieving various statistics of elevation data, such as minimum, maximum, mean, standard deviation, median, and entropy.

Arguments

• service_query::String: Specifies the service query type to execute, such as 'elevation_data_full'.
• selection::Dict: A dictionary containing selection criteria, such as geo_id and shape_obj.
• system::OrchestratorRegistry.ServiceRegistry: A service registry used to retrieve the FACT_elevation service for connection details.

Returns

• DataFrame: A DataFrame containing elevation data with the following columns:
  • geo_id: Geographic ID of the region.
  • min_elevation: Minimum elevation in the region.
  • max_elevation: Maximum elevation in the region.
  • mean_elevation: Mean elevation in the region.
  • std_elevation: Standard deviation of elevation.
  • median_elevation: Median elevation in the region.
  • entropy_elevation: Shannon entropy measure of elevation data.

Behavior

• Retrieves the FACT_elevation service from the ServiceRegistry to configure the connection.
• Dynamically maps the service_query argument to the appropriate data-fetching function (e.g., elevation_data_full).
• Fetches and filters elevation data according to the specified selection criteria.

Raises

• Error: If the service_query argument does not match a supported service query (e.g., 'elevation_data_full').

Example

Define the selection criteria and retrieve elevation data:

# Define selection criteria
service_query = "elevation_data_full"
selection = Dict("geo_id" => ["ID1"], "shape_obj" => ["SHAPE1"])

# Create a ServiceRegistry and add the required service
system = ServiceRegistry()
add_service!(system, Service("FACT_elevation", "FACT_elevation_service", "http://localhost", 8080))

# Fetch elevation data
df = get_elevation_data(service_query, selection, system)

println(df)

In this example, the function retrieves elevation data for the specified geo_id and shape_obj using the elevation_data_full service query.

getgeoobjects( scale::String, selection::Dict, system::OrchestratorRegistry.ServiceRegistry ) -> DataFrame

Retrieves geographical objects based on the specified scale and selection criteria.

This function dynamically connects to a service retrieved from the provided ServiceRegistry, fetches geographical data based on the scale argument, and structures the results in a DataFrame. It supports multiple geographical scales, such as countries, regions, municipalities, and more.

Arguments

• scale::String: Specifies the geographical scale to query, such as 'eu_countries' or 'us_states'.
• selection::Dict: A dictionary containing selection criteria, such as geo_id or shape_obj, to filter the data.
• system::OrchestratorRegistry.ServiceRegistry: A service registry used to retrieve the FACT_geo service for database connection details.

Returns

• DataFrame: A DataFrame containing geographical data with the following columns:
  • region: Specifies whether the data belongs to EU or US regions.
  • geo_id: The geographic identifier for the region.
  • shape_obj: WKT (Well-Known Text) representation of the shape.
  • area: The area of the geographic entity.

Behavior

• Retrieves the FACT_geo service from the ServiceRegistry to configure the database connection.
• Dynamically maps the scale argument to the appropriate data-fetching function.
• Fetches and filters geographical data based on the specified selection criteria.

Raises

• Error: If the scale argument does not match a supported scale.

Example

Define the selection criteria and retrieve geographical data for European countries:

# Define selection criteria
selection = Dict("geo_id" => ["ID1"], "shape_obj" => ["SHAPE1"])

# Create a ServiceRegistry and add the required service
system = ServiceRegistry()
add_service!(system, Service("FACT_geo", "FACT_geo_service", "http://localhost", 8080))

# Fetch geographical data
df = get_geo_objects("eu_countries", selection, system)

println(df)

In this example, the function retrieves geographical data for the specified geo_id and shape_obj using the eu_countries scale.

get_geology( table::String, selection::Dict, system::OrchestratorRegistry.ServiceRegistry ) -> DataFrame

Retrieves geology data for specified geographic areas and attributes.

This function dynamically connects to a service retrieved from the provided ServiceRegistry, fetches geology data based on the specified table, and applies the given selection criteria. The retrieved data includes percentages of geological eras and rock types within the specified geographic areas.

Arguments

• table::String: The name of the table to query for geology data, such as 'geology_full'.
• selection::Dict: A dictionary specifying the selection criteria, including:
  • "geo_id": A list of geographic IDs to filter by.
  • "shape_obj": A list of WKT (Well-Known Text) representations of shapes to query.
  • "region": A string representing the region (e.g., "US", "EU").
  • "area": A list of area sizes to filter by.
• system::OrchestratorRegistry.ServiceRegistry: A service registry used to retrieve the FACT_mine service for database connection details.

Returns

• DataFrame: A DataFrame containing geology data with the following columns:
  • geo_id: Geographic ID of the region.
  • Geological eras:
    • cenozoic: Percentage of geology with era = Cenozoic.
    • mesozoic: Percentage of geology with era = Mesozoic.
    • paleoMeso: Percentage of geology with era = Paleozoic-Mesozoic.
    • Additional geological eras (e.g., paleozoic, precambrian, archPaleoprotero, etc.).
  • Rock types:
    • volcanic: Percentage of volcanic rocks.
    • sedimentary: Percentage of sedimentary rocks.
    • intrusive: Percentage of intrusive rocks.
    • Additional rock types (e.g., crystalline, tectonic, ice, etc.).
  • unclassified: Percentage of unclassified rocks.

Behavior

• Retrieves the FACT_mine service from the ServiceRegistry to configure the database connection.
• Dynamically maps the table argument to the appropriate data-fetching function.
• Fetches and filters geology data based on the specified selection criteria.

Raises

• Error: If the table argument does not match a supported table.

Example

Define the selection criteria and retrieve geology data:

# Define selection criteria
table = "geology_full"
selection = Dict(
    "geo_id" => ["geo1", "geo2"],
    "shape_obj" => ["WKT1", "WKT2"],
    "region" => "US",
    "area" => [1000, 2000]
)

# Create a ServiceRegistry and add the required service
system = ServiceRegistry()
add_service!(system, Service("FACT_mine", "FACT_mine_service", "http://localhost", 8080))

# Fetch geology data
df = get_geology(table, selection, system)

println(df)

In this example, the function retrieves geology data for the specified geo_id, shape_obj, region, and area using the 'geology_full' table.

getlandusedata( servicequery::String, selection::Dict, system::OrchestratorRegistry.ServiceRegistry ) -> DataFrame

Retrieves land use data from a specified service query and selection criteria.

This function dynamically connects to a service retrieved from the provided ServiceRegistry, fetches land use data based on the specified service_query, and applies the given selection criteria. The retrieved data includes various land use percentages, such as water, trees, grass, and more.

Arguments

• service_query::String: The name of the service query to use for data retrieval, such as 'land_use_perc'.
• selection::Dict: A dictionary specifying the selection criteria, including:
  • "geo_id": A list of geographic IDs to filter by.
  • "shape_obj": A list of WKT (Well-Known Text) representations of shapes to query.
  • "year": The year to filter the data.
• system::OrchestratorRegistry.ServiceRegistry: A service registry used to retrieve the FACT_lulc service for connection details.

Returns

• DataFrame: A DataFrame containing land use data with the following columns:
  • geo_id: Geographic ID of the region.
  • water_perc: Percentage of water coverage.
  • tree_perc: Percentage of tree coverage.
  • grass_perc: Percentage of grass coverage.
  • flooded_perc: Percentage of flooded land.
  • crop_perc: Percentage of crops.
  • scrub_perc: Percentage of scrub land.
  • built_perc: Percentage of built land.
  • bare_perc: Percentage of bare land.
  • snow_perc: Percentage of land covered with snow.
  • cloud_perc: Percentage of land covered by clouds.

Behavior

• Retrieves the FACT_lulc service from the ServiceRegistry to configure the connection.
• Dynamically maps the service_query argument to the appropriate data-fetching function.
• Fetches and filters land use data based on the specified selection criteria.

Raises

• Error: If the service_query argument does not match a supported service query.

Example

Define the selection criteria and retrieve land use data:

# Define selection criteria
service_query = "land_use_perc"
selection = Dict(
    "geo_id" => ["geo1", "geo2"],
    "shape_obj" => ["WKT1", "WKT2"],
    "year" => 2023
)

# Create a ServiceRegistry and add the required service
system = ServiceRegistry()
add_service!(system, Service("FACT_lulc", "FACT_lulc_service", "http://localhost", 8080))

# Fetch land use data
df = get_land_use_data(service_query, selection, system)

println(df)

In this example, the function retrieves land use data for the specified geo_id, shape_obj, and year using the 'land_use_perc' service query.

get_minerals( table::String, selection::Dict, system::OrchestratorRegistry.ServiceRegistry ) -> DataFrame

Retrieves mineral data for specified geographic areas and attributes.

This function dynamically connects to a service retrieved from the provided ServiceRegistry, fetches mineral data based on the specified table, and applies the given selection criteria. The retrieved data includes percentages of different mineral types within the specified geographic areas.

Arguments

• table::String: The name of the table to query for mineral data, such as 'minerals_full'.
• selection::Dict: A dictionary specifying the selection criteria, including:
  • "geo_id": A list of geographic IDs to filter by.
  • "shape_obj": A list of WKT (Well-Known Text) representations of shapes to query.
  • "region": A string representing the region (e.g., "EU", "US").
  • "area": A list of area sizes to filter by.
• system::OrchestratorRegistry.ServiceRegistry: A service registry used to retrieve the FACT_mine service for database connection details.

Returns

• DataFrame: A DataFrame containing mineral data with the following columns:
  • geo_id: Geographic ID of the region.
  • Mineral types:
    • carbonatite: Percentage area of carbonatite.
    • global_selected: Percentage area of globally selected minerals.
    • laterite: Percentage area of laterite.
    • nicrpge: Percentage area of nickel, chromium, and platinum group elements.
    • phosphate: Percentage area of phosphate.
    • Additional mineral types (e.g., podchrome, porcu, potash, etc.).

Behavior

• Retrieves the FACT_mine service from the ServiceRegistry to configure the database connection.
• Dynamically maps the table argument to the appropriate data-fetching function.
• Fetches and filters mineral data based on the specified selection criteria.

Raises

• Error: If the table argument does not match a supported table.

Example

Define the selection criteria and retrieve mineral data:

# Define selection criteria
table = "minerals_full"
selection = Dict(
    "geo_id" => ["geo1", "geo2"],
    "shape_obj" => ["WKT1", "WKT2"],
    "region" => "EU",
    "area" => [1500, 2500]
)

# Create a ServiceRegistry and add the required service
system = ServiceRegistry()
add_service!(system, Service("FACT_mine", "FACT_mine_service", "http://localhost", 8080))

# Fetch mineral data
df = get_minerals(table, selection, system)

println(df)

In this example, the function retrieves mineral data for the specified geo_id, shape_obj, region, and area using the 'minerals_full' table.

getportstonnage( table::String, selection::Dict, system::OrchestratorRegistry.ServiceRegistry ) -> DataFrame

Retrieves port information, including the number of ports and total tonnage, for specified geographic areas.

This function dynamically connects to a service retrieved from the provided ServiceRegistry, fetches port data based on the specified table, and applies the given selection criteria. It supports querying datasets for both EU and US ports.

Arguments

• table::String: The name of the table to query for port information, such as 'eu_ports' or 'us_ports'.
• selection::Dict: A dictionary specifying the selection criteria, including:
  • "geo_id": A list of geographic IDs to filter by.
  • "shape_obj": A list of WKT (Well-Known Text) representations of shapes to query.
• system::OrchestratorRegistry.ServiceRegistry: A service registry used to retrieve the FACT_ports service for database connection details.

Returns

• DataFrame: A DataFrame containing port information with the following columns:
  • geo_id: Geographic ID of the region.
  • ports: Number of ports in the specified geographic area.
  • tonnage: Total tonnage of goods handled by the ports (in tonnage units).

Behavior

• Retrieves the FACT_ports service from the ServiceRegistry to configure the database connection.
• Dynamically maps the table argument to the appropriate data-fetching function.
• Fetches and filters port information based on the specified selection criteria.

Raises

• Error: If the table argument does not match a supported table.

Example

Define the selection criteria and retrieve port information:

# Define selection criteria
table = "eu_ports"
selection = Dict(
    "geo_id" => ["PORT1", "PORT2"],
    "shape_obj" => ["POINT(1 2)", "POINT(3 4)"]
)

# Create a ServiceRegistry and add the required service
system = ServiceRegistry()
add_service!(system, Service("FACT_ports", "FACT_ports_service", "http://localhost", 8080))

# Fetch port information
df = get_ports_tonnage(table, selection, system)

println(df)

In this example, the function retrieves port information for two EU ports based on their geographic IDs and shapes using the 'eu_ports' table.

getpowerstations_lines( table::String, selection::Dict, system::OrchestratorRegistry.ServiceRegistry ) -> DataFrame

Retrieves power station and transmission line information for specified geographic areas.

This function dynamically connects to a service retrieved from the provided ServiceRegistry, fetches power station and transmission line data based on the specified table, and applies the given selection criteria. It supports querying datasets for both EU and US power infrastructure.

Arguments

• table::String: The name of the table to query for power station and transmission line information, such as 'eu_power' or 'us_power'.
• selection::Dict: A dictionary specifying the selection criteria, including:
  • "geo_id": A list of geographic IDs to filter by.
  • "shape_obj": A list of WKT (Well-Known Text) representations of shapes to query.
• system::OrchestratorRegistry.ServiceRegistry: A service registry used to retrieve the FACT_power service for database connection details.

Returns

• DataFrame: A DataFrame containing power station and transmission line information with the following columns:
  • geo_id: Geographic ID of the region.
  • stations: Number of power stations in the specified geographic area.
  • capacity: Total capacity of power stations (in MW).
  • lines: Total length of transmission lines (in meters).

Behavior

• Retrieves the FACT_power service from the ServiceRegistry to configure the database connection.
• Dynamically maps the table argument to the appropriate data-fetching function.
• Fetches and filters power infrastructure data based on the specified selection criteria.

Raises

• Error: If the table argument does not match a supported table.

Example

Define the selection criteria and retrieve power infrastructure information:

# Define selection criteria
table = "eu_power"
selection = Dict(
    "geo_id" => ["STATION1", "STATION2"],
    "shape_obj" => ["POINT(1 2)", "POINT(3 4)"]
)

# Create a ServiceRegistry and add the required service
system = ServiceRegistry()
add_service!(system, Service("FACT_power", "FACT_power_service", "http://localhost", 8080))

# Fetch power station and transmission line information
df = get_power_stations_lines(table, selection, system)

println(df)

In this example, the function retrieves power station and transmission line information for two EU power stations based on their geographic IDs and shapes using the 'eu_power' table.

getrailstations_lines( table::String, selection::Dict, system::OrchestratorRegistry.ServiceRegistry ) -> DataFrame

Retrieves railway station and line information for specified geographic areas.

This function dynamically connects to a service retrieved from the provided ServiceRegistry, fetches railway station and line data based on the specified table, and applies the given selection criteria. It supports querying datasets for both EU and US railway infrastructure.

Arguments

• table::String: The name of the table to query for railway station and line information, such as 'eu_rail' or 'us_rail'.
• selection::Dict: A dictionary specifying the selection criteria, including:
  • "geo_id": A list of geographic IDs to filter by.
  • "shape_obj": A list of WKT (Well-Known Text) representations of shapes to query.
• system::OrchestratorRegistry.ServiceRegistry: A service registry used to retrieve the FACT_rail service for database connection details.

Returns

• DataFrame: A DataFrame containing railway station and line information with the following columns:
  • geo_id: Geographic ID of the region.
  • stations: Number of railway stations in the specified geographic area.
  • lines: Total length of railway lines (in meters).

Behavior

• Retrieves the FACT_rail service from the ServiceRegistry to configure the database connection.
• Dynamically maps the table argument to the appropriate data-fetching function.
• Fetches and filters railway infrastructure data based on the specified selection criteria.

Raises

• Error: If the table argument does not match a supported table.

Example

Define the selection criteria and retrieve railway infrastructure information:

# Define selection criteria
table = "eu_rail"
selection = Dict(
    "geo_id" => ["STATION1", "STATION2"],
    "shape_obj" => ["POINT(1 2)", "POINT(3 4)"]
)

# Create a ServiceRegistry and add the required service
system = ServiceRegistry()
add_service!(system, Service("FACT_rail", "FACT_rail_service", "http://localhost", 8080))

# Fetch railway station and line information
df = get_rail_stations_lines(table, selection, system)

println(df)

In this example, the function retrieves railway station and line information for two EU railway stations based on their geographic IDs and shapes using the 'eu_rail' table.

get_results_from_service(service_query::String, df::DataFrame, service_dict::Dict, selection::Dict; host::String="127.0.0.1", port::Int=80) -> DataFrame

Query a specified service and append the results to a given DataFrame after type conversion.

Arguments

• service_query::String: The query string to identify which service to use.
• df::DataFrame: The DataFrame to which the results will be appended.
• service_dict::Dict: A dictionary mapping service queries to their corresponding functions.
• selection::Dict: A dictionary containing parameters for the service selection.
• host::String (optional): The hostname or IP address of the service, defaults to "127.0.0.1".
• port::Int (optional): The port number of the service, defaults to 80.

Returns

• DataFrame: The original DataFrame combined with the results from the service query.

Description

This function constructs a URL from the provided host and port and uses it to query a specified service. The service is determined by the service_query parameter and the corresponding function is fetched from service_dict. The selection dictionary is used as parameters for the query.

The function retrieves data and appends it to the provided DataFrame df. Before appending, it ensures that the data types of the retrieved results match those in df. If there's a mismatch, the function attempts to convert the types in the results to match those in df.

The function is particularly useful for dynamically querying different services and aggregating the results in a structured format. It's designed to handle common issues such as type mismatches often encountered when dealing with external data sources.

Example

```julia df = DataFrame(id = Int[], name = String[]) servicedict = Dict("getUserData" => userDataFunc, "getOrderData" => orderDataFunc) selection = Dict("userId" => 123) resultdf = getresultsfromservice("getUserData", df, servicedict, selection, host="api.example.com", port=8080) '''

get_selected_objects_from_table(table::String, df::DataFrame, table_dict::Dict, database::String, selection::Dict; host::String="127.0.0.1", port::Int=3306) -> DataFrame

Retrieve and append selected objects from a specified table in the database to a given DataFrame.

Arguments

• table::String: The name of the table from which to retrieve objects.
• df::DataFrame: The DataFrame to which the results will be appended.
• table_dict::Dict: A dictionary mapping table names to their corresponding functions.
• database::String: The name of the database.
• selection::Dict: A dictionary containing selection parameters.
• host::String (optional): The database host address, default is "127.0.0.1".
• port::Int (optional): The port number for the database connection, default is 3306.

Returns

• DataFrame: A DataFrame that combines the original df and the results retrieved from the database.

Description

This function connects to a MySQL database and retrieves data based on the specified selection criteria from the table. The retrieved data is then processed to match the data types of the columns in the provided df DataFrame. If the types differ, conversion is attempted to align them with df. The results are appended to df and returned. The connection to the database is closed before returning the DataFrame.

Example

df = DataFrame(id = Int[], name = String[])
table_dict = Dict("users" => users_func, "orders" => orders_func)
selection = Dict("id" => 123)
database = "my_database"
result_df = get_selected_objects_from_table("users", df, table_dict, database, selection)

gettraveldataeu( servicequery::String, selection::Dict, system::OrchestratorRegistry.ServiceRegistry ) -> DataFrame

Retrieves travel time data for European Union (EU) geographic locations.

This function dynamically connects to a service retrieved from the provided ServiceRegistry, fetches travel time data based on the specified service_query, and applies the given selection criteria. It supports querying travel time data for pairs of EU geographic locations.

Arguments

• service_query::String: The name of the service query to use for retrieving travel time data, such as 'travel_time_in_minutes'.
• selection::Dict: A dictionary specifying the selection criteria, including:
  • "geo_id": A list of geographic IDs to filter by.
  • "shape_obj": A list of WKT (Well-Known Text) representations of shapes to query.
• system::OrchestratorRegistry.ServiceRegistry: A service registry used to retrieve the FACT_road_eu service for connection details.

Returns

• DataFrame: A DataFrame containing travel time data with the following columns:
  • o_geo_id: Origin geographic ID.
  • d_geo_id: Destination geographic ID.
  • travel_time: Travel time in seconds between the origin and destination.

Behavior

• Retrieves the FACT_road_eu service from the ServiceRegistry to configure the connection.
• Dynamically maps the service_query argument to the appropriate data-fetching function.
• Fetches and filters travel time data based on the specified selection criteria.

Raises

• Error: If the service_query argument does not match a supported service query.

Example

Define the selection criteria and retrieve travel time data:

# Define selection criteria
service_query = "travel_time_in_minutes"
selection = Dict(
    "geo_id" => ["Location1", "Location2", "Location3"],
    "shape_obj" => ["POINT(1 2)", "POINT(3 4)", "POINT(5 6)"]
)

# Create a ServiceRegistry and add the required service
system = ServiceRegistry()
add_service!(system, Service("FACT_road_eu", "FACT_road_eu_service", "http://localhost", 8080))

# Fetch travel time data
df = get_travel_data_eu(service_query, selection, system)

println(df)

In this example, the function retrieves travel time data for pairs of EU geographic locations using the 'travel_time_in_minutes' service query.

gettraveldatana( servicequery::String, selection::Dict, system::OrchestratorRegistry.ServiceRegistry ) -> DataFrame

Retrieves travel time data for North America (NA) geographic locations.

This function dynamically connects to a service retrieved from the provided ServiceRegistry, fetches travel time data based on the specified service_query, and applies the given selection criteria. It supports querying travel time data for pairs of NA geographic locations.

Arguments

• service_query::String: The name of the service query to use for retrieving travel time data, such as 'travel_time_in_minutes'.
• selection::Dict: A dictionary specifying the selection criteria, including:
  • "geo_id": A list of geographic IDs to filter by.
  • "shape_obj": A list of WKT (Well-Known Text) representations of shapes to query.
• system::OrchestratorRegistry.ServiceRegistry: A service registry used to retrieve the FACT_road_na service for connection details.

Returns

• DataFrame: A DataFrame containing travel time data with the following columns:
  • o_geo_id: Origin geographic ID.
  • d_geo_id: Destination geographic ID.
  • travel_time: Travel time in seconds between the origin and destination.

Behavior

• Retrieves the FACT_road_na service from the ServiceRegistry to configure the connection.
• Dynamically maps the service_query argument to the appropriate data-fetching function.
• Fetches and filters travel time data based on the specified selection criteria.

Raises

• Error: If the service_query argument does not match a supported service query.

Example

Define the selection criteria and retrieve travel time data:

# Define selection criteria
service_query = "travel_time_in_minutes"
selection = Dict(
    "geo_id" => ["Location1", "Location2", "Location3"],
    "shape_obj" => ["POINT(1 2)", "POINT(3 4)", "POINT(5 6)"]
)

# Create a ServiceRegistry and add the required service
system = ServiceRegistry()
add_service!(system, Service("FACT_road_na", "FACT_road_na_service", "http://localhost", 8080))

# Fetch travel time data
df = get_travel_data_na(service_query, selection, system)

println(df)

In this example, the function retrieves travel time data for pairs of NA geographic locations using the 'travel_time_in_minutes' service query.

handle_selected_action(service_query::String, service_dict::Dict, ws_host_port::String, selection::Dict) -> DataFrame

Executes a selected action based on a given service query and returns the result as a DataFrame.

Arguments

• service_query::String: The query string used to identify the specific action to be executed.
• service_dict::Dict: A dictionary mapping service queries to their corresponding functions.
• ws_host_port::String: The host and port of the web service as a string (e.g., "http://127.0.0.1:80").
• selection::Dict: A dictionary containing parameters for the action.

Returns

• DataFrame: The result of the executed action. If the service_query does not correspond to any function in service_dict, a DataFrame with an error message is returned.

Description

This function looks up the service_query in service_dict to find the corresponding function to execute. If the query matches a key in the dictionary, the associated function is called with ws_host_port and selection as arguments. If no matching key is found, a default function is called that returns a DataFrame with an error message indicating no corresponding web service query function was found.

This function is particularly useful for handling various service actions dynamically, allowing for flexible and scalable service interactions.

Example

service_dict = Dict("getData" => getDataFunc, "postData" => postDataFunc)
selection = Dict("param1" => "value1", "param2" => "value2")
ws_host_port = "http://127.0.0.1:80"
result = handle_selected_action("getData", service_dict, ws_host_port, selection)

handle_selection_action(table::String, table_dict::Dict, conn::MySQL.Connection, selection::Dict) -> DataFrame

Handle the selection action for a given table and return the appropriate DataFrame.

Arguments

• table::String: The name of the table to handle.
• table_dict::Dict: A dictionary mapping table names to their corresponding functions.
• conn::MySQL.Connection: The MySQL database connection.
• selection::Dict: A dictionary containing selection parameters.

Returns

• DataFrame: A DataFrame resulting from the called function for the given table. If the table is not found in table_dict, it returns a DataFrame with a message indicating no corresponding table.

Description

This function looks up the table name in table_dict and retrieves a function associated with that table. It then calls this function, passing the MySQL connection and the selection dictionary as arguments. If the table name is not found in table_dict, it returns a default DataFrame with a message indicating that no corresponding table was found.

Example

conn = MySQL.connect("host", "user", "password", "dbname")
table_dict = Dict("users" => users_func, "orders" => orders_func)
selection = Dict("id" => 123)
result = handle_selection_action("users", table_dict, conn, selection)

handle_simple_action(table::String, table_dict::Dict, conn::MySQL.Connection) -> DataFrame

Executes a specific action based on the given table name, using a dictionary of functions and a MySQL connection.

This function looks up a corresponding function in table_dict based on the table name and executes it using the provided MySQL connection. If no matching function is found in table_dict for the given table name, a default function is executed that returns a DataFrame with a message indicating no corresponding table was found.

Arguments

• table::String: The name of the table for which an action needs to be executed.
• table_dict::Dict: A dictionary mapping table names to specific functions that handle actions for those tables.
• conn::MySQL.Connection: The MySQL connection to use for executing any database queries within the action functions.

Returns

• DataFrame: A DataFrame containing the results of the action executed. If no action is found for the given table, returns a DataFrame with a single column Message and a message indicating no corresponding table.

Notes

• The functions in table_dict are expected to be of the form func(conn::MySQL.Connection) -> DataFrame.
• This allows for flexible and dynamic execution of table-specific actions based on the table name.

interpret_scale(scale::String) -> Tuple

Interprets the scale string by splitting it into components based on specified separators.

This function splits the input scale string into parts using a regular expression that matches any non-word character (excluding underscores) or whitespace. It is designed to parse a string into distinct elements based on these separators.

Arguments

• scale::String: The input string to be parsed.

Returns

• Tuple: A tuple containing the separated parts of the input string.

Example

interpret_scale("EU_2020 USA_2021")
# Returns: ("EU_2020", "USA_2021")

Notes

The function uses a regular expression to identify separators, which include any non-word character (like punctuation) and whitespace, but excludes underscores. This is useful for parsing strings where different components are separated by spaces, commas, or other non-word characters, while underscores are part of the individual components.

land_use_perc(ws_host_port::String, selection::Dict) -> DataFrame

Retrieve land use percentages for specified geographic areas and year using a web service.

This function calculates land use percentages for specified geographic areas based on the provided shape data and year. It communicates with a web service to retrieve land use statistics for each area and returns the results as a DataFrame.

Arguments

• ws_host_port::String: The host and port of the web service in the format "http://host:port".
• selection::Dict: A dictionary specifying the selection criteria including geo_id, shape_obj, and year.

Returns

• DataFrame: A DataFrame containing columns for geo_id (geographic ID), water_perc (percentage of water), tree_perc (percentage of trees), grass_perc (percentage of grass), flooded_perc (percentage of flooded land), crop_perc (percentage of crops), scrub_perc (percentage of scrub land), built_perc (percentage of built land), bare_perc (percentage of bare land), snow_perc (percentage of land covered with snow), and cloud_perc (percentage of land cloud-covered).

Example

wshostport = "http://localhost:5000" selection = Dict("geoid" => ["geo1", "geo2"], "shapeobj" => ["WKT1", "WKT2"], "year" => 2023) df = landuseperc(wshostport, selection)

This example calculates land use percentages for two geographic areas using the provided shape data and year.

minerals_full(conn::MySQL.Connection, selection::Dict) -> DataFrame

Retrieve mineral data for specified geographic areas and attributes.

This function retrieves mineral data for specified geographic areas based on the provided selection criteria. It communicates with a MySQL database connection to execute SQL queries and returns the results as a DataFrame.

Arguments

• conn::MySQL.Connection: The MySQL database connection to use for querying mineral data.
• selection::Dict: A dictionary specifying the selection criteria including geo_id, shape_obj, region, and area.

Returns

• DataFrame: A DataFrame containing columns for geo_id (geographic ID), region (region identifier), and various attributes representing the percentage area of different mineral types within the specified geographic areas.

Example

conn = MySQL.Connection(user="user", password="password", host="localhost", db="mineraldata") selection = Dict("geoid" => ["geo1", "geo2"], "shapeobj" => ["WKT1", "WKT2"], "region" => "EU", "area" => [1500, 2500]) df = mineralsfull(conn, selection)

This example retrieves mineral data for two geographic areas in the "EU" region with specified shape data and areas.

query_connection(conn::MySQL.Connection, query::String) -> DataFrame

Executes a SQL query using a MySQL connection and returns the results as a DataFrame.

This function executes the given SQL query on the provided MySQL connection. It fetches the query results, converting all data to strings, and returns them in a DataFrame. Each column in the DataFrame is of type String.

Arguments

• conn::MySQL.Connection: The MySQL connection to use for executing the query.
• query::String: The SQL query string to be executed.

Returns

• DataFrame: A DataFrame containing the query results, with all data converted to strings.

Notes

• The function assumes the connection (conn) is already open and valid.
• It converts all columns in the query result to String to avoid type interpretation issues.

travel_time_in_minutes(ws_host_port::String, selection::Dict) -> DataFrame

Calculate travel times in minutes between pairs of geographic locations.

This function calculates travel times in minutes between pairs of geographic locations using the Mapbox Directions API. It takes a Mapbox Directions API endpoint as ws_host_port and a selection of geographic locations specified as geo_id and shape_obj in a dictionary. The travel times are computed for all possible pairs of locations and stored in a DataFrame.

Arguments

• ws_host_port::String: The URL of the Mapbox Directions API endpoint to use for calculating travel times.
• selection::Dict: A dictionary specifying the selection criteria including geo_id (geographic IDs) and shape_obj (geometric shapes) for the locations.

Returns

• DataFrame: A DataFrame containing columns for o_geo_id (origin geographic ID), d_geo_id (destination geographic ID), and travel_time (travel time in minutes) representing travel times between pairs of geographic locations.

Example

wshostport = "https://api.mapbox.com/directions/v5/mapbox/driving" selection = Dict( "geoid" => ["Location1", "Location2", "Location3"], "shapeobj" => ["POINT(1 2)", "POINT(3 4)", "POINT(5 6)"] ) df = traveltimeinminutes(wshost_port, selection)

This example calculates travel times in minutes between three geographic locations using the Mapbox Directions API.

us_air(conn::MySQL.Connection, selection::Dict) -> DataFrame

Retrieve air traffic data for specified geographic regions within the United States from a MySQL database.

This function queries a database for air traffic information, such as the number of airports and total flights, within specified geographic regions in the United States. The query filters data based on the shapes and IDs provided in the selection dictionary. Acceptable years for the data are 1961, 71, 81, 91, 2001, 2011.

Arguments

• conn::MySQL.Connection: An active connection to a MySQL database.
• selection::Dict: A dictionary containing geo_id and shape_obj, representing geographic identifiers and their corresponding shapes.

Returns

• DataFrame: A DataFrame with columns for the geographic ID, the number of airports, the number of flights, and the region set to 'US'.

Example

conn = MySQL.connect("localhost", "username", "password", db = "dbname") selection = Dict("geoid" => ["ID1", "ID2"], "shapeobj" => ["SHAPE1", "SHAPE2"]) df = us_air(conn, selection)

This example queries air traffic data for specified U.S. regions and returns the results in a DataFrame.

us_block(conn::MySQL.Connection, selection::Dict) -> DataFrame

Retrieve United States (US) Census Block population data for specified geographic areas and years.

This function retrieves population data for specified US Census Blocks for a given year. It communicates with a MySQL database connection to execute SQL queries and returns the results as a DataFrame.

Arguments

• conn::MySQL.Connection: The MySQL database connection to use for querying US Census Block population data.
• selection::Dict: A dictionary specifying the selection criteria including year (acceptable years: 1961, 71, 81, 91, 2001, 2011, 2020), and geo_id (Block IDs).

Returns

• DataFrame: A DataFrame containing columns for geo_id (Block ID), region (region identifier, always "US"), and population representing the population count for the specified US Census Blocks in the given year.

Example

conn = MySQL.Connection(user="user", password="password", host="localhost", db="uspopulation") selection = Dict("year" => 2020, "geoid" => ["010010201001000"]) df = us_block(conn, selection)

This example retrieves population data for a US Census Block in the year 2020.

us_block_groups(conn::MySQL.Connection) -> DataFrame
# Description
Retrieve geographical data for US census block groups from a MySQL database.

# Arguments
- `conn::MySQL.Connection`: An active connection to a MySQL database.

# Returns
- `DataFrame`: A DataFrame containing columns for the geographic ID, WKT representation of the shape, area of the block group, and the region 'US'.

us_blocks(conn::MySQL.Connection) -> DataFrame
# Description
Retrieve geographical data for US census blocks from a MySQL database.

# Arguments
- `conn::MySQL.Connection`: An active connection to a MySQL database.

# Returns
- `DataFrame`: A DataFrame containing columns for the geographic ID, WKT representation of the shape, area of the block, and the region 'US'.

us_counties(conn::MySQL.Connection) -> DataFrame
# Description
Retrieve geographical data for US counties from a MySQL database.

# Arguments
- `conn::MySQL.Connection`: An active connection to a MySQL database.

# Returns
- `DataFrame`: A DataFrame containing columns for the geographic ID, WKT representation of the shape, area of the county, and the region 'US'.

us_county(conn::MySQL.Connection, selection::Dict) -> DataFrame

Retrieve United States (US) County population data for specified geographic areas and years.

This function retrieves population data for specified US Counties for a given year. It communicates with a MySQL database connection to execute SQL queries and returns the results as a DataFrame.

Arguments

• conn::MySQL.Connection: The MySQL database connection to use for querying US County population data.
• selection::Dict: A dictionary specifying the selection criteria including year (acceptable years: 2014-2022), and geo_id (GeoIDs).

Returns

• DataFrame: A DataFrame containing columns for geo_id (GeoID), region (region identifier, always "US"), and population representing the population count for the specified US Counties in the given year.

Example

conn = MySQL.Connection(user="user", password="password", host="localhost", db="uspopulation") selection = Dict("year" => 2022, "geoid" => ["01001"]) df = us_county(conn, selection)

This example retrieves population data for a US County in the year 2022.

us_lodes(conn::MySQL.Connection, selection::Dict) -> DataFrame

Retrieve annual job data for US regions using the LODES dataset from a MySQL database.

This function queries a MySQL database to retrieve annual job data for US regions based on the LODES dataset. It allows for customization of the query based on the selection dictionary, which specifies the year and geographic IDs to be included in the query.

Arguments

• conn::MySQL.Connection: An active connection to a MySQL database.
• selection::Dict: A dictionary specifying the selection criteria including the year and geo_ids.

Returns

• DataFrame: A DataFrame containing columns for the region ('US'), geographic ID, aggregation level, industry identifier, and average number of annual jobs.

Example

conn = MySQL.connect("localhost", "username", "password", db = "dbname") selection = Dict("year" => 2023, "geoid" => ["US001", "US002"]) df = uslodes(conn, selection)

This example queries annual job data for US regions using the LODES dataset for the year 2023 and specific geographic IDs.

us_ports(conn::MySQL.Connection, selection::Dict) -> DataFrame

Retrieve information about United States (US) ports, including the number of ports and total tonnage, for specified geographic areas.

This function communicates with a MySQL database connection to execute SQL queries and retrieve information about US ports. It calculates the number of ports and total tonnage for the specified geographic areas based on their intersections with port shapes.

Arguments

• conn::MySQL.Connection: The MySQL database connection to use for querying US port data.
• selection::Dict: A dictionary specifying the selection criteria including geo_id (port IDs) and shape_obj (geometric shapes).

Returns

• DataFrame: A DataFrame containing columns for geo_id (port ID), region (region identifier, always "US"), ports (number of ports), and tonnage (total tonnage in tonnage units) representing port information for the specified areas.

Example

conn = MySQL.Connection(user="user", password="password", host="localhost", db="usportsdb") selection = Dict("geoid" => ["PORT1", "PORT2"], "shapeobj" => ["POINT(1 2)", "POINT(3 4)"]) df = us_ports(conn, selection)

This example retrieves information about two US ports based on their specified port IDs and shapes.

us_power(conn::MySQL.Connection, selection::Dict) -> DataFrame

Retrieve information about power stations and transmission lines in the United States (US) for specified geographic areas.

This function communicates with a MySQL database connection to execute SQL queries and retrieve information about power stations and transmission lines in the US. It calculates the number of power stations, total capacity, and total length of transmission lines for the specified geographic areas based on their intersections with station and line shapes.

Arguments

• conn::MySQL.Connection: The MySQL database connection to use for querying US power data.
• selection::Dict: A dictionary specifying the selection criteria including geo_id (station IDs) and shape_obj (geometric shapes).

Returns

• DataFrame: A DataFrame containing columns for geo_id (station ID), region (region identifier, always "US"), stations (number of power stations), capacity (total capacity in MW), and lines (total length of transmission lines in meters) representing power station and transmission line information for the specified areas.

Example

conn = MySQL.Connection(user="user", password="password", host="localhost", db="uspowerdb") selection = Dict("geoid" => ["STATION1", "STATION2"], "shapeobj" => ["POINT(1 2)", "POINT(3 4)"]) df = us_power(conn, selection)

This example retrieves information about two US power stations and their associated transmission lines based on specified station IDs and shapes.

us_qcew(conn::MySQL.Connection, selection::Dict) -> DataFrame

Retrieve annual job data for US regions using the QCEW dataset from a MySQL database.

This function queries a MySQL database to retrieve annual job data for US regions based on the QCEW (Quarterly Census of Employment and Wages) dataset. It allows for customization of the query based on the selection dictionary, which specifies the year, geographic IDs, and aggregation level to be included in the query.

Arguments

• conn::MySQL.Connection: An active connection to a MySQL database.
• selection::Dict: A dictionary specifying the selection criteria including the year, geo_ids, and aggregation level.

Returns

• DataFrame: A DataFrame containing columns for the region ('US'), geographic ID, aggregation level, industry identifier, and average number of annual jobs.

Example

conn = MySQL.connect("localhost", "username", "password", db = "dbname") selection = Dict("year" => 2023, "geoid" => ["US001", "US002"], "agglevel" => 2) df = us_qcew(conn, selection)

This example queries annual job data for US regions using the QCEW dataset for the year 2023, specific geographic IDs, and aggregation level.

us_rail(conn::MySQL.Connection, selection::Dict) -> DataFrame

Retrieve information about railway stations and lines in the United States (US) for specified geographic areas.

This function communicates with a MySQL database connection to execute SQL queries and retrieve information about railway stations and lines in the US. It calculates the number of railway stations and the total length of railway lines for the specified geographic areas based on their intersections with station and line shapes.

Arguments

• conn::MySQL.Connection: The MySQL database connection to use for querying US railway data.
• selection::Dict: A dictionary specifying the selection criteria including geo_id (station IDs) and shape_obj (geometric shapes).

Returns

• DataFrame: A DataFrame containing columns for geo_id (station ID), region (region identifier, always "US"), stations (number of railway stations), and lines (total length of railway lines in meters) representing railway station and line information for the specified areas.

Example

conn = MySQL.Connection(user="user", password="password", host="localhost", db="usraildb") selection = Dict("geoid" => ["STATION1", "STATION2"], "shapeobj" => ["POINT(1 2)", "POINT(3 4)"]) df = us_rail(conn, selection)

This example retrieves information about two US railway stations and their associated railway lines based on specified station IDs and shapes.

us_states(conn::MySQL.Connection) -> DataFrame
# Description
Retrieve geographical data for US states from a MySQL database.

# Arguments
- `conn::MySQL.Connection`: An active connection to a MySQL database.

# Returns
- `DataFrame`: A DataFrame containing columns for the geographic ID, WKT representation of the shape, area of the state, and the region 'US'.

us_tracts(conn::MySQL.Connection) -> DataFrame
# Description
Retrieve geographical data for US census tracts from a MySQL database.

# Arguments
- `conn::MySQL.Connection`: An active connection to a MySQL database.

# Returns
- `DataFrame`: A DataFrame containing columns for the geographic ID, WKT representation of the shape, area of the tract, and the region 'US'.

module FACT_data_API_reader

A Julia module for retrieving various types of data including geographic objects, travel time information, job and population statistics, airport traffic data, elevation data, rail station and line data, port tonnage data, power station and transmission line data, land use data, geology and mineral data, and building footprint and height data.

Exported Functions

• get_geo_objects: Retrieves geographic objects from the FACT_geo database on Port 3307.
• get_travel_data_eu: Retrieves travel time data for European Union (EU) geographic locations using a service query and a specified host and port.
• get_travel_data_na: Retrieves travel time data for North America (NA) geographic locations using a service query and a specified host and port.
• get_annual_jobs: Retrieves annual job data from the FACT_jobs database on Port 3308.
• get_annual_population: Retrieves annual population data from the FACT_population database on Port 3309.
• get_airports_traffic: Retrieves airports traffic data from the FACT_air database on Port 3310.
• get_elevation_data: Retrieves elevation data using an HTTP request to the specified host and port.
• get_rail_stations_lines: Retrieves data related to rail stations and lines from the FACT_rail database on Port 3311.
• get_ports_tonnage: Retrieves data related to ports and tonnage from the FACT_ports database on Port 3312.
• get_power_stations_lines: Retrieves data related to power stations and transmission lines from the FACT_power database on Port 3313.
• get_land_use_data: Retrieves land use data using an HTTP request to the specified host and port.
• get_geology: Retrieves geology data from the FACT_mine database on Port 3314.
• get_minerals: Retrieves mineral data from the FACT_mine database on Port 3314.
• get_bldgs_footprint_height: Retrieves building footprint and height data from the FACT_bldgs database on Port 3315.

The module is designed to help with data retrieval and analysis for various domains.