Afstand bepalen tussen groepen postcodes

[R.vdBergh]

Goedemiddag,

Ik heb in QlikSense twee groepen postcodes geladen:

a) postcodes van klanten en
b) postcodes van locaties

Nu wil ik graag de afstand bepalen, hemelsbreed in kilometers tussen elke a en elke b. Heeft er iemand tips?

Vriendelijk dankl

[raymondnijssen]

1. Geocoderen (de xy-coordinaten opzoeken) van de postcodes met bijvoorbeeld de PDOK-locatieserver.

2. De afstanden berekenen met de stelling van Pythagoras. (Bleek die toch nog ergens goed voor te zijn…!)

a = verschil tussen x-coordinaten van 2 punten
b = verschil tussen y-coordinaten van 2 punten
c = wortel van a kwadraat + b kwadraat

[geotiles]

Zou je hier niet ook iets mee kunnen: https://nationaalgeoregister.nl/geonetwork/srv/dut/catalog.search#/metadata/e9841ae6-8f1d-460f-9506-a6b5ed2dc15f ?

[sbjager]

Ik vraag me altijd af waarom je dit zou willen weten als het niet voor luchtvaart bedoelt is.
Ik woon in Hoorn, en bij dergelijke zoekfuncties krijg ik (als ik de zoekafstand op 50 km zet) altijd resultaten terug uit Almere bijvoorbeeld. Nou klopt het dat de gemeenten Almere en Hoorn aan elkaar grenzen, maar met een gewone auto is het nog altijd ruim meer dan 50 km… Vermoedelijk heb je in Zeeland ook dergelijke situaties, en in andere delen van het land kan er een kanaal of rivier tussen twee naast elkaar gelegen postcodes liggen.
Dus wat fysieke afstand van een locatie tot een klant betreft, geeft dit in mijn ervaring geen goede resultaten terug… En als ik dan bijvoorbeeld zie hoe snel Openrouteservice een route over de weg teruggeeft, en bereikbaarheids isochronen…

Nog even afgezien van het feit dat een postcode helemaal geen gebied is maar een verzameling punten met een gemeenschappelijk attribuut, maar dat terzijde.

[R.vdBergh]

Het vraagstuk is opgelost dankzij de hulp van een externe specialist. Hartelijk dank voor jullie bijdrage!

[Anton]

Vertel eens, wat was de oplossing

[R.vdBergh]

Krijg een foutmelding dat ik maar 2 gebruikers mag antwoorden oid

[raymondnijssen]

Schrijf je antwoord gewoon aan iedereen hier!

[antonbakker]

Hier mijn bijdrage; gesteld dat je in QlikSense SQL queries kan uitvoeren op een GeoPackage met sqlite kan je dmv van deze query afstanden tussen postcode gebieden uitrekenen.

select load_extension('mod_spatialite')
create view locaties as SELECT * FROM cbs_pc4_2020 ORDER BY RANDOM() LIMIT 5; -- selecteer 5 willekeurige postcodes als locaties
create view klanten as SELECT * FROM cbs_pc4_2020, locaties WHERE cbs_pc4_2020.postcode <> locaties.postcode ORDER BY RANDOM() LIMIT 50; -- selecteer 50 willekeurige postcodes (die niet al in locaties view zitten) als klanten
SELECT locaties.postcode as locatie_postcode, klanten.postcode as klant_postcode, ST_Distance(ST_Centroid(GeomFromGPB(locaties.geom)), ST_Centroid(GeomFromGPB(klanten.geom))) as distance  FROM locaties, klanten GROUP BY klant_postcode HAVING MIN(distance) ORDER BY distance;

Zie deze gist: Calculate distance between two groups of geometries with GeoPackage/SQLite · GitHub

[R.vdBergh]

Ik heb dit mirakel ontvangen:

– Haversine formula requiring 2 cities as input throwing distance
– source → Haversine formula - Wikipedia

DECLARE @pnumto int;
DECLARE @pnumfrom int;

SET @pnumto = 2201
SET @pnumfrom = 3511

Declare @latlons table (pnum int not null, lat float null, lon float null)
insert into @latlons
SELECT pnum, AVG(lat), AVG(lon) --/ 10 
FROM [GEODATA].[Postcode] s0
where s0.pnum = @pnumto or s0.pnum = @pnumfrom
group by pnum

Declare @TestTable table (    
    pnumfrom int, latfrom float, lonfrom float,
    pnumto int, latto float, lonto float, 
    phi1 float, phi2 float, 
    deltaphi float, deltalambda float
    )
Declare @R bigint = convert(real, '6371E3') -- Radius of earth
;
with s1 as (
    SELECT s.*, ROW_NUMBER()
        OVER(ORDER BY s.pnum desc) as rownum
    FROM @latlons s
    where s.pnum = @pnumfrom
    ),
    s2 as (
    SELECT s.*, ROW_NUMBER()
        OVER(ORDER BY s.pnum asc) as rownum
    FROM @latlons s
    where s.pnum = @pnumto
    )
insert into @TestTable 
SELECT    s1.pnum as pnumfrom, s1.lat as latfrom, s1.lon as lonfrom,
        s2.pnum as pnumto, s2.lat as latto, s2.lon as lonto,
        (s1.lat * PI() / 180) as phi1, (s2.lat * PI() / 180) as phi2,
        ((s2.lat - s1.lat) * PI() / 180) as deltaphi, ((s2.lon - s1.lon) * PI() / 180) as deltalambda
FROM s1
JOIN s2 on s1.rownum = s2.rownum
;
WITH    s1 as (
        SELECT t.pnumfrom, t.pnumto, 
        SIN(t.deltaphi/2) * SIN(t.deltaphi/2) + COS(t.phi1) * COS(t.phi2) * SIN(t.deltalambda/2) * SIN(t.deltalambda/2) as a
        FROM @TestTable t
        )
SELECT    s1.pnumfrom, s1.pnumto, 
        2 * ATN2(SQRT(s1.a), SQRT(1 - s1.a)) * @R / 1000 as distance
FROM    s1

[R.vdBergh]

Die is een stuk overzichtelijker dan de oplossing die ik ontving

[sbjager]

Tsja. Je maakt het jezelf, naar mijn mening, ook wel erg moeilijk. De formule die je liet zien berekent de grote cirkel tussen twee punten in lat/lon graden. Tenzij je de afstanden tussen postcodes wereldwijd wilt berekenen, is dat nogal overkill.
Als je alleen in NL werkt, is het vele malen simpeler om de RD coordinaten te gebruiken. Want dan kun je eenvoudig phytagoras gebruiken, oftewel de afstand is de wortel van het verschil van de coordinaten in het kwadraat ( L^2 = (xb-xa)^2 + (yb-ya)^2 ). En omdat de eenheid van RD de meter is, heb je dan meteen je afstand in meters (delen door 1000 = kilometers).
Bovendien durf ik te wedden dat je je postcode-coordinaten al in RD hebt, dus hiermee hoef je die niet eerst om te rekenen naar lat/lon…

@raymondnijssen raadde je dit ook al aan zo’n 3 weken geleden…