1
\$\begingroup\$

I have a postgres table, etablissements with millions of rows, and a geom (st_point) column , spatially indexed (using gist). I'm using the PostGIS extension. Those rows may have a different category, activite_principale.

I want to find from a given, fixed point (coordinates) the closest etablissements for 5 or 6 different categories called "activite_principale" (1 closest company per category).

Here's what I did right now and which is working :

    (WITH closest_candidates AS (
      SELECT
        ent.id,
        ent.name,
        ent.geom
      FROM
        geo_data.etablissements ent
      WHERE ent.activite_principale = '1071C'
      ORDER BY
        ent.geom <->
        'SRID=4326;POINT (5.4153978921979125 43.271437384501965)'::geometry
      LIMIT 10
    )
    SELECT id
    FROM closest_candidates
    ORDER BY
      ST_Distance(
        geom,
        'SRID=4326;POINT (5.4153978921979125 43.271437384501965)'::geometry
        )
    LIMIT 1)
    UNION ALL
    (WITH closest_candidates AS (
      SELECT
        ent.id,
        ent.name,
        ent.geom
      FROM
        geo_data.etablissements ent
      WHERE ent.activite_principale = '4711D'
      ORDER BY
        ent.geom <->
        'SRID=4326;POINT (5.4153978921979125 43.271437384501965)'::geometry
      LIMIT 10
    )
    SELECT id
    FROM closest_candidates
    ORDER BY
      ST_Distance(
        geom,
        'SRID=4326;POINT (5.4153978921979125 43.271437384501965)'::geometry
        )
    LIMIT 1)
  --  UNION ALL
-- [...]  And so on...

I clustered the etablissements table around the geom spatial index and ran VACUUM ANALYZE geo_data.etablissements;

Here's the result of EXPLAIN ANALYZE after clustering.

The query planning is shorte and the execution too but it's still slow (350-450ms).

I use postgres 10 & postgis 2.4.

I'm not convinced my cascading UNION ALL is the best solution, that's why I'm asking. My problem : how to find the X closest points of table Y with distinct Z categories, and you are guaranteed each row is the closest X for category Z in PostGIS.

I don't know how to improve based on those explanations from the query planner. Can I do better performance ?

Note : this is a cross-post from the GIS stackexchange

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.