41 lines
907 B
SQL
41 lines
907 B
SQL
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));
|