Display a map

[schliesser-p]

Hi everyone,

is there any tutorial on how to display a world map and how to display defined points on that map?

Best

[lovasoa]

Hello ! Did you have a look at the documentation for the map component ?

You can create a simple map with

select 
    'map' as component;
select 
    'New Delhi' as title,
    28.6139     as latitude,
    77.209      as longitude;

If you have a database table that looks like this:

CREATE TABLE my_locations (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    latitude NUMERIC(9, 6) NOT NULL,
    longitude NUMERIC(9, 6) NOT NULL
);

Then you can show them on a map with

-- Display map component
SELECT  'map'  AS component;

-- Display each location
SELECT 
    name as title,
    latitude,
    longitude,
    CONCAT('details.sql?id=', id) as link
FROM my_locations;

[schliesser-p]

Oh wow sorry did not saw that before. Thanks

[lovasoa]

Here is a short step by step tutorial, supposing you have your data in a csv file

Step 1: Prepare Your CSV File

Ensure you have a CSV file with your geo data. For this example, let's assume the CSV file is named locations.csv and has the following structure:

title,icon,color,latitude,longitude,description_md,link
Notre Dame,building-castle,indigo,48.853,2.3498,A beautiful cathedral.,https://en.wikipedia.org/wiki/Notre-Dame_de_Paris
Eiffel Tower,building,blue,48.8584,2.2945,An iconic symbol of Paris.,https://en.wikipedia.org/wiki/Eiffel_Tower

Step 2: Create a SQLite Database

First, you need to create a SQLite database and a table to store your data. You can use the sqlite3 command-line tool for this.

sqlite3 sqlpage/sqlpage.db

Once inside the SQLite prompt, create the table:

CREATE TABLE important_locations (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    icon TEXT,
    color TEXT,
    latitude REAL NOT NULL,
    longitude REAL NOT NULL,
    description_md TEXT,
    link TEXT
);

Step 3: Import CSV Data into SQLite

Exit the SQLite prompt and use the .import command to load data from the CSV file into the table. Make sure your CSV file is in the same directory as your SQLite database file or provide the full path to the file.

sqlite3 sqlpage/sqlpage.db <<EOF
.mode csv
.import locations.csv important_locations
EOF

Step 4: Query the Data with sqlpage

With your data loaded into the SQLite database, you can now query it using sqlpage.

1. Start sqlpage: Ensure you downloaded the sqlpage binary in the current folder and start it. Run the following command in your terminal:

   ./sqlpage.bin

2. Create an SQL Query to Display the Map and Locations:

   • Create a new index.sql file to include the following SQL queries:

   -- Display map component
   SELECT 
       'map'   AS component,
       'Paris' AS title,
       11      AS zoom,
       48.85   AS latitude,
       2.34    AS longitude;
   
   -- Display each location
   SELECT 
       title,
       icon,
       color,
       latitude,
       longitude,
       description_md,
       link
   FROM important_locations;

3. Open http://localhost:8080 in your browser

[lovasoa]

Out of curiosity, what is it that you are building, @schliesser-p ?

[schliesser-p]

@lovasoa Thinking of building a website to show all surfspots i visited. And i was looking around for a simple tool to build that myself. Your repo look awesome :)

[lovasoa]

Thank you! Let us know if we can help, and make sure to share your website when you are done!