ANTON KADACH

Generation

text to sql syntaxWed, 18 Jan 2023

The recursive part of the query builds a table r that has all objects at all levels. This is done by iteratively joining the table itself with the geo_objects_attributes and geo_objects tables. The top level region is added in the first part of the union (select 1) and then the geo_objects_attributes and geo_objects tables are joined with the table r itself. This is done for all levels of the hierarchy. In the end, the result is simply joined with the geo_polygons table.

WITH RECURSIVE r AS ( SELECT 1 AS level, id, name, parent_id FROM geo_objects_attributes WHERE parent_id IS NULL UNION ALL SELECT r.level + 1, geo_objects_attributes.id, geo_objects_attributes.name, geo_objects_attributes.parent_id FROM geo_objects_attributes, r WHERE geo_objects_attributes.parent_id = r.id ) SELECT r.id, r.name, geo_polygons.polygon FROM r LEFT JOIN geo_polygons ON r.id = geo_polygons.object_id;

Questions about programming?Chat with your personal AI assistant