Zaktualizuj wiersz bazy danych o punkty mieszczące się w wielokątach

10

Mam PostGIS / Postgresql DB, który zawiera dwie tabele. Jedna z geometriami punktów, a druga reprezentująca granice kraju jako wielokąty. Chciałbym dodać nazwę kraju, z którą przecina się każdy punkt, do każdego wiersza w mojej tabeli punktów. Może jako jedno duże zapytanie dotyczące aktualizacji. Myślę, że można to zrobić przy użyciu prostego SQL, ale nie wiem od czego zacząć. Wszelkie porady na ten temat byłyby bardzo mile widziane ...

postgis postgresql point-in-polygon

— AdamEstrada
źródło

9

Inna opcja, bez potrzeby korzystania z funkcji

update points set country = t1.country from 
(
    select points.oid, countries.name as country from
    countries INNER JOIN points on st_contains(countries.wkb_geometry,points.wkb_geometry)
) t1 
where t1.oid = points.oid

Podejrzewam (choć nie testowałem), że będzie to szybsze niż użycie funkcji zagnieżdżonej, jak w twoim przykładzie.

Moje wyniki z biegania wyjaśniają (mam nadzieję, że wyglądasz podobnie). Jeśli masz więcej wyników Seq Scan, to jest coś, na co warto zwrócić uwagę, być może indeksy nie są właściwie skonfigurowane.

Update on points  (cost=1.18..29.40 rows=121 width=129)"
  ->  Nested Loop  (cost=1.18..29.40 rows=121 width=129)"
        Join Filter: _st_contains(countries.geometry, public.points.geometry)"
        ->  Hash Join  (cost=1.18..2.37 rows=28 width=220)"
              Hash Cond: (public.points.oid = public.points.oid)"
              ->  Seq Scan on points  (cost=0.00..1.08 rows=28 width=114)"
              ->  Hash  (cost=1.08..1.08 rows=28 width=110)"
                    ->  Seq Scan on points  (cost=0.00..1.08 rows=28 width=110)"
        ->  Index Scan using "countries_Idx" on countries  (cost=0.00..0.91 rows=1 width=414)"
              Index Cond: (geometry && public.points.geometry)"

— Kelso
źródło

Niesamowite! To także wydaje się być o wiele szybsze. Dzięki!

— AdamEstrada,

4

OK ... Zrobiłem trochę hakowania i odkryłem, że FUNKCJA SQL prowadzi mnie przez większość czasu. Czy ktoś myśli o zabraniu tego na most?

 CREATE OR REPLACE FUNCTION getcountry (
       country_geom geometry
    ) RETURNS TABLE(country text) AS $$
        SELECT b.name as country FROM  
                    geonames d, world_borders b WHERE
                    $1 && b.wkb_geometry 
                    AND intersects($1, b.wkb_geometry) ;
  $$ LANGUAGE SQL;

UPDATE geonames 
    SET country = val
    FROM (SELECT getcountry(point_geom) FROM geonames) AS val

— AdamEstrada
źródło