search

notum-cz / strapi-plugin-location

This plugin allows users to create location inputs and store latitude and longitude values as geometry types in a PostGIS database. It also provides functionality to filter items based on their location.
MIT License
26 stars 13 forks source link

_geom fields are geometry, but queries always cast to geography, preventing any DB indices #182

Open JannikZed opened 6 months ago

JannikZed commented 6 months ago

Bug report

I just realized, that this plugin is creating _geom fields for every coordinate entry. The column is actually using GIST "geometry". All queries that are produced through the plugin are looking like this: 

select id from "cities" where ST_DWithin(wk_coordinates_geom,ST_SetSRID(ST_MakePoint('11.5772753', '48.1365601'), 4326)::geography, 30000);

so are casted on the fly from geometry to geography. As this plugin is just working with geopoints, I would recommend to move from geometry directly to geography and directly set an index on it. I tried it out on my data and came down from 1 second to 0,037 seconds for the same data with this approach. Everything works the same, just the query looks like this:

SELECT id
FROM cities
WHERE ST_DWithin(
    wk_coordinates_geog,
    ST_SetSRID(ST_MakePoint(11.5772753, 48.1365601), 4326)::GEOGRAPHY,
    30000
);

I would recommend to set an index on the geom fields directly (or should we let the customer decide that with a setting flag?

CREATE INDEX IF NOT EXISTS cities_wk_coordinates_geom_idx ON public.cities USING GIST (wk_coordinates_geom)
JannikZed commented 6 months ago

I created a small PR for that https://github.com/notum-cz/strapi-plugin-location/pull/183 we should just decide, if we want to add database index creation here as well. this change shouldn't break anything, as we leave the typecast in the query.