Mehr

Auswählen von POIs in der Nähe bestimmter Gebäude mit PostGIS

Auswählen von POIs in der Nähe bestimmter Gebäude mit PostGIS


Das Problem: Ich muss für jedes Wohngebäude in meiner Tabelle, das mindestens 2 Apotheken und 2 Bildungszentren im Umkreis von 1 km hat, alle POIs (Apotheken, Handelszentren, Ärztehäuser, Bildungszentren, Polizeistationen, Feuerwehr) auswählen Stationen), die sich im Umkreis von 1 km um das jeweilige Gebäude befinden. Tabellenstruktur->

Gebäude (Ich würde seriell, Name varchar )

poi_kategorie(Ich würde seriell, cname varchar) --cname ist natürlich der Kategoriename

poi (Ich würde seriell, Name varchar, c_id integer)-- c_id ist die FK, die auf poi_category(Ich würde)

alle Koordinatenspalten sind vom Typ Geometrie, nicht Geographie (nennen wir sie so) geom)

Ich dachte, es sollte so gemacht werden, aber ich bin mir nicht sicher, ob es richtig ist, geschweige denn die optimale Lösung für dieses Problem

SELECT r.id_b, r.id_p FROM ( SELECT b.id AS id_b, p.id AS id_p, pc.id AS id_pc,pc.cname FROM Gebäude AS b, poi AS p, poi_category AS pc WHERE ST_DWithin(b.geom ,p.geom, 1000) AND p.c_id=pc.id ) AS r, ( SELECT * FROM r GROUP BY id_b ) AS r1 HAVING count ( SELECT * FROM r, r1 WHERE r1.id_b=r.id_b AND r. id_pc='pharmacy' )>1 UND zählen ( SELECT * FROM r, r1 WHERE r1.id_b=r.id_b AND r.id_pc='ed. center' )>1

Ist das der Weg für das, was ich brauche? Welche Lösung wäre aus Performance-Sicht besser? Wie wäre es mit der elegantesten Lösung?


Basierend auf dem zweiten Beispiel auf dieser Seite würde ich Folgendes versuchen:

SELECT b.gid, b.name, [+ beliebige Felder] -- hier erhalten Sie POI-Daten VON Gebäude b LEFT JOIN Gebäude f ON ST_DWithin(b.the_geom, f.the_geom, 1000) -- farmacy LEFT JOIN Gebäude ec ON ST_DWithin (b.the_geom, ec.the_geom, 1000) -- ed. center -- LEFT JOIN POI AND POI_CATEGORY Tabellen + Join-Bedingungen WHERE f.id_pc='pharmacy' AND ec.id_pc='ed. Zentrum' AND COUNT(f.gid) >1 AND COUNT(ec.gid)>1;

Natürlich wird es nicht getestet und die Abfrage ist nicht vollständig, aber die Logik wäre, Tabellen nach geometrischen Kriterien zu verknüpfen. Wenn dies funktioniert, fügen Sie Anweisungen zum Abrufen Ihrer POI-Daten hinzu.


Normalerweise mache ich diese in plpgsql, da ich es viel einfacher zu lesen finde und die Ergebnisse normalerweise effizienter sind, weil Sie den Fluss steuern können (frühzeitiges Beenden usw.):

FUNKTION ERSTELLEN ODER ERSETZEN getpois() RETURNS text as $$ DECLARE br record; phcount-Ganzzahl; edcount ganze Zahl; FOR br IN SELECT id, name, geom FROM Building LOOP SELECT count(id) from poi wobei ST_DWITHIN(br.geom, geom, 1000) und c_id = Apothekenkateg in phcount; WENN phcount < 2 THEN EXIT; --Ich denke, dies ist das Äquivalent von Continue, d.h. zum nächsten Gebäude ENDIF springen; SELECT count(id) from poi wobei ST_DWITHIN(br.geom, geom, 1000) und c_id = Apothekenkategorie in edcount; WENN edcount < 2 THEN EXIT; ENDIF; -- wähle jetzt alle POIS innerhalb von 1KM aus und gib sie vielleicht als Array zurück usw. END LOOP; END $$ LANGUAGE 'plpgsql' strikt;

Schau das Video: DB creation PostGIS in cloud for QGIS in 10 minutes DigitalOcean + Postgres