Punkte aus Tabelle in Datenbank einlesen möglich?

#!/usr/bin/bash

# retrieves from geoapify.com the geocoordinates as well as
#   other data for the places listed in the heredoc below

# requires:
#   an API key for Geoapify (https://www.geoapify.com/)
#   (3000 free geocoding requests/day at the time of writing)
#   `jq` (https://jqlang.github.io/jq/)
#   `gpsbabel` (https://www.gpsbabel.org)

# ----------------------------------
# USER DATA
# the key to all the magic -- delete it before you pass the script on!
APIKEY="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

# path to gpsbabel, if not in PATH
GPSBABEL='/home/tw/Geo/GPX/gpsbabel-1.9.0/bld/gpsbabel'

# list of search terms
# usually places; adapt the wanted fields in the `jq` line
PLACES=$(cat <<'EOPL'
    Baker Street, London, UK
    Rue Morgue
    Isola Montecristo
    Baskerville
    Eton Place
    Twin Peaks
    Mt. Everest
EOPL
) # ^^nothing but the delimiter must be on that line

# ----------------------------------
# SETUP
# use an intermediary csv file, mostly in case the place list is very long,
# but also for debug inspection
# very useful if the data need to be manually pruned in a spreadsheet!
TEMPFILE="comma-separated.csv"

# intermediate results for debugging and to check the extracted data after setting
# the wanted fields in the `jq` line
# root of the file name, it will be appended with  `-extracted`, `awk-1`, and ´-awk-2`
# the file are removed at script end
DEBUGFILE="DEBUG"

# the final product
OUTFILE="geoapify-coordinates.gpx"

# retrieved fields that are merged into one field can be visually separated with this
# because of a bug in gpsbabel (=> "Invalid ..." error), some characters cannot be uses, e.g. ";"
MERGESEP=" │ "

# ----------------------------------
# PREPARE A HEADER
# name of the fields in QMapShack GPX points
printf '"lat","lon","name","comment","desc","date","time"\n' > "${TEMPFILE}"
printf '"latitude","longitude","name","country","county","city","district","suburb","quarter","town","village","hamlet","street","address_line_1","address_line_2","date","time"\n' 2>/dev/null 1> "${DEBUGFILE}-1-extracted"
printf '"latitude","longitude","name","country","county","city","district","suburb","quarter","town","village","hamlet","street","address_line_1","address_line_2","date","time"\n' 2>/dev/null 1> "${DEBUGFILE}-2-awk-1"
printf '"latitude","longitude","name","country","county","city","district","suburb","quarter","town","village","hamlet","street","address_line_1","address_line_2","date","time"\n' 2>/dev/null 1> "${DEBUGFILE}-3-awk-2"

# ----------------------------------
# GO THROUGH PLACE NAME LIST, GET DATA, REORDER FIELDS
# field processing includes minimal mitigation of OSM inconsistencies
while IFS= read -r PLACE
do
    TIMESTAMP="$(date '+,"%F","%R:%S"')"
    # ^^^includes field separators

    # - fetch the data with `curl`
    #   the API has options to narrow the search with a filter and a bias
    #   for frequent use these could be moved up as a section below USER DATA TODO
    # - extract desired data with `jp` (the debug `tee` fails silently if DEBUGFILE is not set)
    #   THE FIELDS HERE MUST BE ADAPTED TO THE WANTED DATA
    # in the next steps, 0xEF (object replacement character, in `awk` as `\357\277\274`, in `sed` not printed!)
    #   is used as placeholder to be substituted afterwards, because `awk` cannot substitue variables
    # - the first `awk` keeps fields 1 and 2 (-> latitude, longitude),
    #   tries to fill the next field with fields 3 and 5 (name, county -> name),
    #   eliminates empty fields
    # - the second `awk` keeps fields 1..3 (latitude, longitude, new name),
    #   merges fields 4, 6..-3 (useful or clutter, it must become -> comment),
    #   joins second last and last field (two address lines -> description)
    # - the `sed` SHOULD replace placeholders with separators, and it appends date and time
    # g*d this is so ugly
    curl -s https://api.geoapify.com/v1/geocode/search\?apiKey=${APIKEY}\&text="$( echo "${PLACE}" | sed -E 's/ /%20/g' )" \
    \
    | jq -r '( .features.[] | .properties | ([ .lat, .lon, .name, .country, .county, .city, .district, .suburb, .quarter, .town, .village, .hamlet, .street, .address_line1, .address_line2 ]) | @csv )' \
    \
    | tee -a  2>/dev/null "${DEBUGFILE}-1-extracted" \
    \
    | awk --csv ' { printf "\""$1"\",\""$2"\""}  { if ($3 && $5) { printf ",\""$3"\357\277\274"$5"\"" } else if ($3 && !$5) { printf ",\""$3"\"" } else if (!$3 && $5){ printf ",\""$5"\"" }  }  $13{ printf ",\""$13"\"" }  $12{ printf ",\""$12"\"" }  $11{ printf ",\""$11"\"" }  $10{ printf ",\""$10"\"" }  $9{ printf ",\""$9"\"" }  $8{ printf ",\""$8"\"" }  $7{ printf ",\""$7"\"" }  $6{ printf ",\""$6"\"" }  $4{ printf ",\""$4"\"" }  $14{ printf ",\""$14"\"" }  $15{ printf ",\""$15"\"\n" } '  \
    \
    | tee -a  2>/dev/null "${DEBUGFILE}-2-awk-1" \
    \
    | awk --csv ' { printf "\""$1"\",\""$2"\",\""$3"\",\"" ; for(i=4; i<=(NF-3); ++i) printf "%s︂\357\277\274", $i ; printf "%s\",\"%s\357\277\274%s\"\n", $(NF-2), $(NF-1), $NF } '  \
    \
    | tee -a  2>/dev/null "${DEBUGFILE}-3-awk-2" \
    \
    | sed -E -e "s//$MERGESEP/g" -e "s/$/$TIMESTAMP/" >> "${TEMPFILE}"

  # >> "${TEMPFILE}"

done < <(printf '%s\n' "${PLACES}")

# ----------------------------------
# CONVERT CSV TO GPX
# `unicsv` can read the fields from the header
"${GPSBABEL}" -i unicsv -f "${TEMPFILE}" -o gpx -F "${OUTFILE}"

# ----------------------------------
# CLEANUP
# uncomment to keep for debugging
# rm "${TEMPFILE}"
# rm "${DEBUGFILE}"*