Cookies op Tweakers

Tweakers maakt gebruik van cookies, onder andere om de website te analyseren, het gebruiksgemak te vergroten en advertenties te tonen. Door gebruik te maken van deze website, of door op 'Ga verder' te klikken, geef je toestemming voor het gebruik van cookies. Wil je meer informatie over cookies en hoe ze worden gebruikt, bekijk dan ons cookiebeleid.

Meer informatie
Toon posts:

[PostgreSQL/-GIS] Onderlinge afstanden tussen boxes bepalen

Pagina: 1
Acties:

  • licensed
  • Registratie: augustus 2002
  • Laatst online: 05-01 20:51
Ik breek mijn hoofd al een tijdje over dit probleem. Ik heb een PostgreSQL 8.4 database met daarin 1 tabel met ruim 4.000.000 records. Deze tabel ziet er als volgt uit:
SQL:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE metadata (
  id serial NOT NULL,
  "value" text NOT NULL DEFAULT ''::text,
  segment_box box NOT NULL DEFAULT box(point(((-9223372036854775808)::bigint)::double precision, (1)::double precision), point((9223372036854775807::bigint)::double precision, ((-1))::double precision)),
  CONSTRAINT metadata_pk PRIMARY KEY (id)
)

CREATE INDEX metadata_segment_box_ix
  ON metadata
  USING gist
  (segment_box);

CREATE INDEX metadata_tag_value_ix
  ON metadata
  USING btree
  (value);

In de tabel worden segmenten in de tijd opgeslagen, gerepresenteerd als rechthoekige boxen. Deze segmenten worden geannoteerd (de kolom value).

Nu wil ik alle segmenten vinden die geannoteerd zijn met een bepaalde value en die niet verder dan een bepaalde afstand van elkaar liggen. De volgende query levert het gewenste antwoord:
SQL:
1
2
3
SELECT * FROM (SELECT * FROM metadata WHERE value='X') a, 
(SELECT * FROM metadata WHERE AND value='Y') b 
WHERE a.segment_box <-> b.segment_box <= 3000

Maar zoals je al had geraden is diet niet bijzonder efficient. Het cartesisch product van sub-queries a en b loopt nogal uit de hand. Is er een manier om dit efficienter te doen? In een willekeurige programmeertaal zou ik hier een sliding window aanpak kiezen. Kan dat ook in PostgreSQL? Bijvoorbeeld zoiets als:
SQL:
1
2
3
SELECT *, rank() OVER (
PARTITION BY "value" ORDER BY (segment_box[1])[0], (segment_box[0])[0]
) FROM metadata WHERE value='X' OR value='Y'


  • licensed
  • Registratie: augustus 2002
  • Laatst online: 05-01 20:51
Eén van de oplossingsrichtingen die ik zag was een custom PostgreSQL functie. De onderstaande functie heb ik geprobeerd, maar deze levert geen snelheidswinst op.
SQL:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE OR REPLACE FUNCTION within_window(size bigint DEFAULT 0)
  RETURNS setof metadata AS
$BODY$DECLARE
  segment RECORD;
  neighbour RECORD;
  newwindow box;
BEGIN
  FOR segment IN (
    SELECT * FROM metadata WHERE value='X' OR value='Y' 
      ORDER BY (segment_box[1])[0], (segment_box[0])[0]
  ) LOOP
    newwindow := box(segment.segment_box[0], 
      point((((segment.segment_box[1])[0]) + size), (segment.segment_box[1])[1]));
    FOR neighbour IN (
      SELECT DISTINCT ON (metadata_id) * FROM metadata WHERE value='X' OR value='Y') 
        AND segment_box &< newwindow
        AND segment_box &> newwindow 
    ) LOOP
      RETURN NEXT neighbour;
    END LOOP;
  END LOOP;
END;$BODY$
  LANGUAGE plpgsql;

Heeft er iemand andere ideeën?


Apple iPhone 11 Microsoft Xbox Series X LG OLED C9 Google Pixel 4 CES 2020 Samsung Galaxy S20 Sony PlayStation 5 Nintendo Switch Lite

'14 '15 '16 '17 2018

Tweakers vormt samen met Hardware Info, AutoTrack, Gaspedaal.nl, Nationale Vacaturebank, Intermediair en Independer DPG Online Services B.V.
Alle rechten voorbehouden © 1998 - 2020 Hosting door True