shortest_path_for_mapping_buildings.sql

drop table if exists building_travel_curve;
create table building_travel_curve as (
    select
        ST_MakeLine(ST_Centroid(p.way)
        order by tsp.seq) as geom,
        1                 as id
    from pgr_tsp(
             $$
             select
                 osm_id :: int4         as id,
                 st_X(ST_Centroid(way)) as x,
                 st_Y(ST_Centroid(way)) as y
             from planet_osm_polygon
             where building is not null and not tags ? 'building:levels'
             order by way <-> ST_Transform(ST_SetSRID(ST_MakePoint(27.57241, 53.91993), 4326), 3857)
             limit 100
             $$,
             (
                 select osm_id :: int4
                 from planet_osm_polygon
                 where building is not null and not tags ? 'building:levels'
                 order by way <-> ST_Transform(ST_SetSRID(ST_MakePoint(27.57241, 53.91993), 4326), 3857)
                 limit 1
             )
         ) tsp
        join planet_osm_polygon p on (tsp.id2 = p.osm_id)
);