00-nearest-buildings-lesotho.sql

----------------------------------------
--- Some preperation - create a new table for the center point and the buildings themselves

-- get all the buildings into one table
DROP TABLE IF EXISTS buildings;
CREATE TABLE buildings as (
SELECT osm_id, tags, way_area, ST_Transform(way,4326) as way FROM planet_osm_polygon where building IS NOT NULL LIMIT 5000
);

-- WE NEED TO MAKE THESE INDEXS SO THINGS RUN FASTER
CREATE INDEX buildings_index
  ON public.buildings
  USING gist
  (way);

-- Index: public.planet_osm_polygon_pkey

-- DROP INDEX public.planet_osm_polygon_pkey;

CREATE INDEX buildings_pkey
  ON public.buildings
  USING btree
  (osm_id);

-- Index: public.planet_osm_polygon_tags_index

-- DROP INDEX public.planet_osm_polygon_tags_index;
-- skip this for now

-- CREATE INDEX buildings_tags_index
--   ON public.buildings
--   USING gin
--   (tags);


DROP TABLE IF EXISTS buildings_ct;
CREATE TABLE buildings_ct as (
SELECT osm_id, ST_Centroid(way) as way FROM buildings
);

-- WE NEED TO MAKE THESE INDEXS SO THINGS RUN FASTER
CREATE INDEX buildingsct_index
  ON public.buildings_ct
  USING gist
  (way);

-- Index: public.planet_osm_polygon_pkey

-- DROP INDEX public.planet_osm_polygon_pkey;

CREATE INDEX buildingsct_pkey
  ON public.buildings_ct
  USING btree
  (osm_id);




-------------------
-- Use this to find the nearest neighbour and reference that in the buildsings table aswell as the distance to it.
-- Currentyl set to only calculate for buildings > 99m away

ALTER TABLE buildings ADD COLUMN nearest_id bigint; --Add a column to the hexagon table
ALTER TABLE buildings ADD COLUMN distance real; --Add a column to the hexagon table

UPDATE buildings 
SET nearest_id = temporary_holder.nearby_id,
distance = temporary_holder.distance
FROM
(SELECT
buildings.osm_id,
nearby.osm_id as nearby_id,
ST_Distance(geography(nearby.way), geography(buildings.way)) as distance
FROM
(SELECT DISTINCT ON (way) *
FROM buildings) AS buildings
CROSS JOIN LATERAL
(SELECT osm_id, way
FROM buildings_ct
ORDER BY buildings.way <-> way
LIMIT 2) AS nearby
WHERE ST_Distance(geography(nearby.way), geography(buildings.way)) > 99
) as temporary_holder
WHERE buildings.osm_id = temporary_holder.osm_id