API Differences & Consolidating Data for GEOJson Performance

[Swamp2k9]

I'm new to working with databases, but I'm diving into a personal project where I need to display a large number of points on a map using the GEOJson format.

The app's performance degrades when each point has its own GEOJson source file. This is why I'm looking at ways to consolidate them all into a single call/file.

Below is the code I've been using. For some reason, the view isn't accessible via the API. Could someone help me identify any issues with my code or suggest possible improvements?

Also, I'm trying to understand the difference between using the List Records API and the View API when accessing data. Could someone explain how they differ and in which scenarios each should be used?

CREATE TABLE locations (
id INTEGER PRIMARY KEY,
name TEXT,
lat REAL,
long REAL,
geojson TEXT GENERATED ALWAYS AS (
'{"type": "Feature", "geometry": {"type": "Point", "coordinates": [' || long || ', ' || lat || ']}}'
) VIRTUAL
) STRICT;

** //// ////**

INSERT INTO
locations (name, lat, long)
VALUES
('Coffee Corner', 37.7749, -122.4194),
('Book Nook', 40.7128, -74.0060),
('Bike Hub', 34.0522, -118.2437);

//// ////

CREATE VIEW IF NOT EXISTS location_geojson AS
SELECT
geojson
FROM
locations;

//// ////

[Swamp2k9]

I've made some progress!

I’ve discovered how to combine all of my entries into a single FeatureCollection for a GeoJSON file.

Now I can clean up the GeoJSON output on each API call or subscription. Below is some sample code for anyone who needs it (and would like feedback).

CREATE TABLE locations (
id INTEGER PRIMARY KEY,
name TEXT,
lat REAL,
long REAL,
geojson TEXT GENERATED ALWAYS AS (
'{"type": "Feature", "geometry": {"type": "Point", "coordinates": [' || long || ', ' || lat || ']}}'
) VIRTUAL
) STRICT;

INSERT INTO
locations (name, lat, long)
VALUES
('Coffee Corner', 37.7749, -122.4194),
('Book Nook', 40.7128, -74.0060),
('Bike Hub', 34.0522, -118.2437);

CREATE VIEW location_geojson AS
SELECT
id,
CAST(
json_object (
'type',
'FeatureCollection',
'features',
json_group_array (
json_object (
'type',
'Feature',
'properties',
json_object ('name', COALESCE(name, '')),
'geometry',
json_object (
'type',
'Point',
'coordinates',
json_array (
COALESCE(CAST(long AS REAL), 0),
COALESCE(CAST(lat AS REAL), 0)
)
)
)
)
) AS TEXT
) AS geojson
FROM
locations;

[ignatz]

Hi @Swamp2k9 ,

Also, I'm trying to understand the difference between using the List Records API and the View API when accessing data. Could someone explain how they differ and in which scenarios each should be used?

View APIs are APIs based on views (as opposed to APIs based on materialized tables). "List records API" just means, it's a read-only API to access lists of records from a TABLE or VIEW, as opposed to a specific record by id.

The app's performance degrades when each point has its own GEOJson source file. This is why I'm looking at ways to consolidate them all into a single call/file.

Looking at your VIEW definition, you're still generating a GeoJSON per point. So using the list APIs would be a way to fetch multiple points. You can also use the filter parameters, e.g. to constrain the points to a bounding box (For more complex APIs you could also implement your own APIs using the WASM runtime).

Where it makes the most sense to construct JSON from your structured points depends a bit on your setup. What you do right now with VIEWs works but may get a bit clumsy for aggregations. A custom handler could also be an option. Or even just do it on the client, if your actual map implementation requires geojson. Keeping your data structured for longer, as opposed to smooshing it into TEXT, allows you to filter, cache, etc. based on coordinates for longer

[Swamp2k9]

I have developed a view that generates a FeatureCollection from all 'locations' entries in my database by following this guide.

According to the MapLibre guide, I need to create a FeatureCollection encapsulated within a single file that includes all feature entries. This process still necessitates cleaning up the file after making the API call.

Initially, I planned to handle this on the client-side but overlooked the capabilities of SQLite.

An alternative approach I've considered is executing the API call and performing file clean-up on the server-side. The cleaned GeoJSON file can then be uploaded to a server, such as a CloudFlare R2 bucket, allowing for file access within my app.

MapLibre offers a map-refresh method that updates point data. I can implement a function to download the latest GeoJSON file periodically (every few minutes) and then employ MapLibre's ComputedSource to update the map.

[ignatz]

I have developed a view that generates a FeatureCollection from all 'locations' entries in my database by following this guide.

Glad to hear. I misread the view definition then.

This process still necessitates cleaning up the file after making the API call.

If you have further clean-up to-do, you could think about ways to construct a correct FeatureCollection to begin with. Rather than constructing an verbose FeatureCollection on the server, deserializing it, filtering it, rebuilding a FeatureCollection, you could:

1. Read a list of structured locations and use the filter parameters and then construct FeatureCollection on the client (sounds like your fixing up there anyway)
2. Have a server-side custom API that constructs an appropriate feature collection

To be clear, there's nothing wrong with your approach, just offering options.

An alternative approach I've considered is executing the API call and performing file clean-up on the server-side. The cleaned GeoJSON file can then be uploaded to a server, such as a CloudFlare R2 bucket, allowing for file access within my app.

Sounds also very reasonable. You could leave a CMS as a future optimization and simply cache it in memory, should be pretty efficient.

Glad to hear you're on top of it 👍

(PS: have done similar stuff in the past, different mapping tools, but if possible i'd love to see your result one day)