PostGIS Geometry points - test

gistfile1.txt

DROP  TABLE api.geom_point;

CREATE TABLE api.geom_point (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name        TEXT,
  latitude    NUMERIC,
  longitude   NUMERIC,
  geom        geometry(Point, 4326) DEFAULT NULL
);

GRANT SELECT ON api.geom_point TO app_anonymous;


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

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

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

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

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



# Golf club - center of map
# 57.158675, -2.084919

# ST_Distance in 4326 projection with radius 5000m

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



# ST_DWithin in 3857 projection with radius 5000m

SELECT 
  gp.*
  , ST_Distance(ST_Transform(gp.geom, 3857), ST_Transform(ST_SetSRID(ST_MakePoint(-2.084919, 57.158675), 4326), 3857)) AS distance_3857
FROM api.geom_point AS gp
WHERE ST_DWithin(ST_Transform(gp.geom, 3857), ST_Transform(ST_SetSRID(ST_MakePoint(-2.084919, 57.158675), 4326), 3857), 5000)
ORDER BY ST_Distance(ST_Transform(gp.geom, 3857), ST_Transform(ST_SetSRID(ST_MakePoint(-2.084919, 57.158675), 4326), 3857));



# ST_DWithin in 2163 projection with radius 5000m

SELECT 
  gp.*
  , ST_Distance(ST_Transform(gp.geom, 2163), ST_Transform(ST_SetSRID(ST_MakePoint(-2.084919, 57.158675), 4326), 2163)) AS distance_2163
FROM api.geom_point AS gp
WHERE ST_DWithin(ST_Transform(gp.geom, 2163), ST_Transform(ST_SetSRID(ST_MakePoint(-2.084919, 57.158675), 4326), 2163), 5000)
ORDER BY ST_Distance(ST_Transform(gp.geom, 2163), ST_Transform(ST_SetSRID(ST_MakePoint(-2.084919, 57.158675), 4326), 2163));