↓Skip to main content
  1. Tags/

Geolytix

dataset: Supermarket Retail Points

·1 min
geolab Geolytix

created: 30/05/2024
updated: 30/05/2024

dataset source #

source:

Retail Points is a comprehensive data set of supermarket and convenience store locations across the UK. Geolytix release this as open data allowing for unrestricted use, no licensing requirements and without cost.

loading into geolab #

connect to geolab database:

psql -h localhost -U geolab -d geolab -W

create source schema:

CREATE SCHEMA __geolytix AUTHORIZATION geolab;

create table:

CREATE TABLE __geolytix.supermarket_retail_points_import
(
id numeric PRIMARY KEY,
retailer varchar(36),
fascia varchar(29),
store_name varchar(60),
add_one varchar(76),
add_two varchar(34),
town varchar(41),
suburb varchar(28),
postcode varchar(8),
long_wgs numeric,
lat_wgs numeric,
bng_e numeric,
bng_n numeric,
pqi varchar(28),
open_date varchar(8),
size_band varchar(38),
county varchar(24)
);

load data into geolab source schema:

\copy __geolytix.supermarket_retail_points_import FROM 'geolytix_retailpoints_v31_202403.csv' WITH (FORMAT csv, HEADER True, QUOTE '"')

create topic schema:

CREATE SCHEMA supermarkets AUTHORIZATION geolab;

create view:

N.B. using a view is slower than a table because the geometry is constructed every time the view is queried.

CREATE VIEW supermarkets.all
 AS
SELECT
  id,
  retailer,
  fascia,
  store_name,
  add_one,
  add_two,
  town,
  suburb,
  postcode,
  long_wgs,
  lat_wgs,
  bng_e,
  bng_n,
  pqi,
  open_date,
  size_band,
  county,
  ST_SetSRID(ST_MakePoint(long_wgs,lat_wgs),4326) AS geom
FROM __geolytix.supermarket_retail_points_import
;