treeadvisor/db.sql

41 lines
907 B
MySQL
Raw Permalink Normal View History

2021-08-26 01:13:49 +02:00
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));
2021-08-26 18:32:58 +02:00
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))));
2021-08-27 23:11:06 +02:00
CREATE TABLE osm_multipolygons (
id bigint,
attrs jsonb
);
CREATE TABLE osm_multipolygon_members (
id bigint,
m_geo polygon,
m_role text,
m_id bigint
);
2021-08-28 00:33:35 +02:00
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));