PostGIS Geography points - test

gistfile1.txt

DROP  TABLE api.geog_point;

CREATE TABLE api.geog_point (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name        TEXT,
  latitude    NUMERIC,
  longitude   NUMERIC,
  geog        geography(Point, 4326) DEFAULT NULL
);

CREATE INDEX geog_points_gix ON api.geog_point USING GIST ( geog );

GRANT SELECT ON api.geog_point TO app_anonymous;


INSERT INTO api.geog_point (name, latitude, longitude, geog) 
VALUES ('Beach', '57.159754', '-2.079346', 'SRID=4326;POINT(-2.079346 57.159754)');

INSERT INTO api.geog_point (name, latitude, longitude, geog) 
VALUES ('Park Street', '57.152317', '-2.090250', 'SRID=4326;POINT(-2.090250 57.152317)');

INSERT INTO api.geog_point (name, latitude, longitude, geog) 
VALUES ('Stadium', '57.159814', '-2.088322', 'SRID=4326;POINT(-2.088322 57.159814)');

INSERT INTO api.geog_point (name, latitude, longitude, geog) 
VALUES ('University', '57.164531', '-2.101711', 'SRID=4326;POINT(-2.101711 57.164531)');

INSERT INTO api.geog_point (name, latitude, longitude, geog) 
VALUES ('Stadium Bus Stop', '57.159267', '-2.086056', 'SRID=4326;POINT(-2.086056 57.159267)');





Golf centre starting point
57.158675, -2.084919

# ST_Distance in 4326 projection

SELECT 
  gp.*
  , ST_Distance(gp.geog, ST_SetSRID(ST_MakePoint(-2.084919, 57.158675),4326)) AS distance_4326
  , ST_DistanceSphere(gp.geog::geometry, ST_SetSRID(ST_MakePoint(-2.084919, 57.158675),4326)) AS distance_meters
FROM api.geog_point AS gp
WHERE ST_Distance(gp.geog, ST_SetSRID(ST_MakePoint(-2.084919, 57.158675),4326)) < 5000
ORDER BY ST_Distance(gp.geog, ST_SetSRID(ST_MakePoint(-2.084919, 57.158675),4326));



# ST_DWithin in 4326 projection

SELECT 
  gp.*
  , ST_Distance(gp.geog, ST_SetSRID(ST_MakePoint(-2.084919, 57.158675),4326)) AS distance_4326
  , ST_DistanceSphere(gp.geog::geometry, ST_SetSRID(ST_MakePoint(-2.084919, 57.158675),4326)) AS distance_meters
FROM api.geog_point AS gp
WHERE ST_DWithin(gp.geog, ST_Point(-2.084919, 57.158675)::geography, 5000)
ORDER BY ST_Distance(gp.geog, ST_SetSRID(ST_MakePoint(-2.084919, 57.158675),4326));



# ST_DWithin and KNN in 4326 projection !!! Best so far

EXPLAIN ANALYZE VERBOSE
SELECT 
  gp.*
  , ST_Distance(gp.geog, ST_SetSRID(ST_MakePoint(-2.084919, 57.158675),4326)) AS distance_4326
FROM api.geog_point AS gp
WHERE ST_DWithin(gp.geog, ST_Point(-2.084919, 57.158675)::geography, 5000)
ORDER BY gp.geog::geometry <-> ST_SetSRID(ST_MakePoint(-2.084919, 57.158675),4326)
LIMIT 10;



# ST_DWithin and KNN in 4326 and 3857 projection

SELECT 
  gp.*
  , ST_Distance(ST_Transform(gp.geog::geometry, 3857), ST_Transform(ST_SetSRID(ST_MakePoint(-2.084919, 57.158675), 4326), 3857)) AS distance_3857
  , ST_Distance(gp.geog, ST_SetSRID(ST_MakePoint(-2.084919, 57.158675),4326)) AS distance_4326
FROM api.geog_point AS gp
WHERE ST_DWithin(gp.geog, ST_Point(-2.084919, 57.158675)::geography, 5000)
ORDER BY gp.geog::geometry <-> ST_SetSRID(ST_MakePoint(-2.084919, 57.158675),4326);



# KNN only in 4326 projection !!! Best performance without magic distance number

EXPLAIN ANALYZE VERBOSE
SELECT 
  gp.*
  , ST_Distance(gp.geog, ST_SetSRID(ST_MakePoint(-2.084919, 57.158675),4326)) AS distance_4326
FROM api.geog_point AS gp
ORDER BY gp.geog::geometry <-> ST_SetSRID(ST_MakePoint(-2.084919, 57.158675),4326)
LIMIT 3;