1

I am relatively new to GIS, so have been searching around to try find a similar case to the one I am facing but have come up short.

Specifically I want to store multiple sets of geometry features in PostgreSQL (the polygon features from separate geojson files) and be able to query them using a bbox. I would also like these files to have different resolutions that can be queried.

Update (9/5/2020) I have added my specific code as below to enable more context:

setup.sql

-- Extensions
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- Schema
CREATE SCHEMA IF NOT EXISTS geojson_testing;

-- Table & Columns
CREATE TABLE IF NOT EXISTS geojson_testing.layermapgeometry();
ALTER TABLE geojson_testing.layermapgeometry 
    ADD COLUMN IF NOT EXISTS tinyid TEXT NOT NULL;
ALTER TABLE geojson_testing.layermapgeometry 
    ADD COLUMN IF NOT EXISTS zoom BIGINT NOT NULL;
ALTER TABLE geojson_testing.layermapgeometry 
    ADD COLUMN IF NOT EXISTS geometrybbox GEOMETRY NOT NULL;
ALTER TABLE geojson_testing.layermapgeometry 
    ADD COLUMN IF NOT EXISTS geometry GEOMETRY NOT NULL;
ALTER TABLE geojson_testing.layermapgeometry 
    ADD COLUMN IF NOT EXISTS geojsonfeature JSONB NOT NULL;
ALTER TABLE geojson_testing.layermapgeometry 
    ADD COLUMN IF NOT EXISTS properties JSONB NOT NULL;

-- Indexes
CREATE INDEX geojson_testing_tinyid_idx 
    ON geojson_testing.layermapgeometry USING GIST(tinyid, zoom, geometrybbox);


-- Insert GeoJson features as geometry
CREATE OR REPLACE FUNCTION geojson_testing.insertGeoJson(opts JSONB) 
RETURNS JSONB AS $$
DECLARE
    _tinyid         TEXT    := opts->>'tinyid';
    _zoom           BIGINT  := COALESCE(opts->>'zoom', '-1')::BIGINT;
    _geojson        JSONB   := opts->'geojson';
BEGIN
    -- Remove any previous geometry
    DELETE FROM geojson_testing.layermapgeometry WHERE tinyid = _tinyid AND zoom = _zoom;

    -- Add new geometry rows based on 'features'
    INSERT INTO 
        geojson_testing.layermapgeometry 
        (
            tinyid, 
            zoom, 
            properties, 
            geojsonfeature,
            geometry,
            geometrybbox
        )
    SELECT 
        _tinyid as tinyid, 
        _zoom as zoom,
        props.properties as properties,
        props.geometry as geojsonfeature,
        ST_GeomFromGeoJSON(props.geometry) as geometry,
        ST_Envelope(ST_GeomFromGeoJSON(props.geometry)) as geometrybbox
    FROM 
        jsonb_to_recordset(_geojson->'features') AS props(properties jsonb, geometry jsonb);

    RETURN jsonb_build_object('success', TRUE);
END; 
$$ LANGUAGE PLPGSQL;

-- Retrieve GeoJson based on BBOX
CREATE OR REPLACE FUNCTION geojson_testing.fetchGeoJson(opts JSONB) 
RETURNS JSONB AS $$
DECLARE
    _tinyid         TEXT    := opts->>'tinyid';
    _zoom           BIGINT  := COALESCE(opts->>'zoom', '-1')::BIGINT;
    _xmin           FLOAT   := (opts->'bbox'->>'xmin')::FLOAT;
    _ymin           FLOAT   := (opts->'bbox'->>'ymin')::FLOAT;
    _xmax           FLOAT   := (opts->'bbox'->>'xmax')::FLOAT;
    _ymax           FLOAT   := (opts->'bbox'->>'ymax')::FLOAT;
    _result         JSONB;
BEGIN

    SELECT jsonb_build_object(
        'type',     'FeatureCollection',
        'features', jsonb_agg(features.feat)
    )
    INTO _result
    FROM 
    (
        SELECT 
            jsonb_build_object(
                'type', 'Feature', 
                'geometry', geojsonfeature, 
                'properties', properties
            ) AS feat
            FROM geojson_testing.layermapgeometry 
            WHERE 
                tinyid = _tinyid AND 
                zoom = _zoom AND
                geometrybbox && ST_MakeEnvelope(_xmin, _ymin, _xmax, _ymax)
    ) AS features;

    RETURN jsonb_build_object('success', TRUE, 'result', _result);
END; 
$$ LANGUAGE PLPGSQL; 

populate.sql

SELECT geojson_testing.insertGeoJson($$
{
    "tinyid":"abcd1",
    "zoom": 1,
    "geojson": {/*Large 40mb geojson wiht many polygon features*/}
}
$$::jsonb)

My end goal is to optimize geojson_testing.fetchGeoJson to be as fast as possible.

While testing I have been playing with the raw SQL select call:

EXPLAIN ANALYSE
SELECT 
    count(*)
    FROM geojson_testing.layermapgeometry 
    WHERE 
        tinyid = 'abcd1' AND
        zoom = 1 AND
        geometry && ST_MakeEnvelope(xmin, ymin, xmax, ymax);

This is coming back at roughly 100ms in pgadmin each time, but what I find especially strange is that when I query the geometrybbox column instead of the geometry column it still returns in roughly the same time. I think it is not using my index, but even if it wasn't I would have expected geometrybbox to run much faster because it is querying simple bboxs instead of a complex polygons.

I am not very familiar with explain results, but this is what it looks like when querying using geometry:

"Aggregate  (cost=1597.38..1597.39 rows=1 width=8) (actual time=5.369..5.369 rows=1 loops=1)"
"  ->  Seq Scan on layermapgeometry  (cost=0.00..1597.12 rows=105 width=0) (actual time=0.248..5.337 rows=298 loops=1)"
"        Filter: ((tinyid = 'abcd1'::text) AND (zoom = 1) AND (geometry && '010300000001000000050000007B14AEF7EF4861403C8BBD70FC8141C07B14AEF7EF486140F9C2B362446941C07B14AEB7EE5C6140F9C2B362446941C07B14AEB7EE5C61403C8BBD70FC8141C07B14AEF7EF4861403C8BBD70FC8141C0'::geometry))"
"        Rows Removed by Filter: 1593"
"Planning Time: 0.160 ms"
"Execution Time: 5.392 ms"

and for geometrybbox:

"Aggregate  (cost=349.33..349.34 rows=1 width=8) (actual time=0.698..0.699 rows=1 loops=1)"
"  ->  Bitmap Heap Scan on layermapgeometry  (cost=9.22..349.07 rows=105 width=0) (actual time=0.356..0.671 rows=298 loops=1)"
"        Recheck Cond: ((tinyid = 'abcd1'::text) AND (geometrybbox && '010300000001000000050000007B14AEF7EF4861403C8BBD70FC8141C07B14AEF7EF486140F9C2B362446941C07B14AEB7EE5C6140F9C2B362446941C07B14AEB7EE5C61403C8BBD70FC8141C07B14AEF7EF4861403C8BBD70FC8141C0'::geometry))"
"        Filter: (zoom = 1)"
"        Heap Blocks: exact=118"
"        ->  Bitmap Index Scan on geojson_testing_tinyid_idx  (cost=0.00..9.20 rows=105 width=0) (actual time=0.325..0.325 rows=298 loops=1)"
"              Index Cond: ((tinyid = 'abcd1'::text) AND (geometrybbox && '010300000001000000050000007B14AEF7EF4861403C8BBD70FC8141C07B14AEF7EF486140F9C2B362446941C07B14AEB7EE5C6140F9C2B362446941C07B14AEB7EE5C61403C8BBD70FC8141C07B14AEF7EF4861403C8BBD70FC8141C0'::geometry))"
"Planning Time: 0.269 ms"
"Execution Time: 0.774 ms"

Any further help would be great.

7
  • 1
    what about multiple geometry columns with varying resolutions that are queried depending on scale? and above I think you use the word 'dataset' when you mean 'feature'... May 5, 2020 at 23:33
  • I hadn't considered that, that could work well with either of the directions I started above. I was hoping to seriously reduce the 1.5s it took before I even started considering resolution, do you think it is reasonable for me to hope to get it to like 200ms or am I being too hopeful?
    – Josh Mc
    May 5, 2020 at 23:47
  • 2
    can you add the EXPLAIN ANALYSE output to your question, and some clues as to size of table, geometries and bounding box - i.e. does your query return most of the table or one or two rows?
    – Ian Turton
    May 6, 2020 at 8:47
  • In fact looking at your query some more shouldn't ID=_ID match just one feature making the rest of the query pretty much irrelevant
    – Ian Turton
    May 6, 2020 at 8:48
  • Depending on the level of simplification of the geometry and the scale of the query, I would imagine so. Definitely worth exploring I think. May 6, 2020 at 14:12

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.