work in progress
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 
 
 
treeadvisor/db.sql

40 lines
907 B

create extension fuzzystrmatch;
CREATE TABLE trees (
id text primary key,
coord point,
score float,
botanic text,
botanic_pfaf text,
german text,
planted date
);
CREATE INDEX trees_coord ON trees USING GIST (coord);
CREATE TABLE areas (
coords polygon,
src text,
attrs jsonb
);
CREATE INDEX areas_coords ON areas USING GIST (coords);
CREATE INDEX areas_box_coords ON areas USING GIST (box(coords));
CREATE TABLE osm_ways (
geo path,
id bigint,
attrs jsonb
);
CREATE INDEX osm_ways_box_coords ON osm_ways USING GIST (box(polygon(pclose(geo))));
CREATE TABLE osm_multipolygons (
id bigint,
attrs jsonb
);
CREATE TABLE osm_multipolygon_members (
id bigint,
m_geo polygon,
m_role text,
m_id bigint
);
CREATE INDEX osm_multipolygons_id ON osm_multipolygons USING BTREE (id);
CREATE INDEX osm_multipolygon_members_geo ON osm_multipolygon_members USING GIST (box(m_geo));