HK6WTQej

1CREATE EXTENSION "uuid-ossp";
2CREATE EXTENSION IF NOT EXISTS postgis;
3
4
5CREATE TABLE addresses (
6    id bigint NOT NULL,
7    latitude double precision,
8    longitude double precision,
9    line1 character varying NOT NULL,
10    "position" geography(Point,4326),
11    CONSTRAINT enforce_srid CHECK ((st_srid("position") = 4326))
12);
13
14CREATE INDEX index_addresses_on_position ON addresses USING gist ("position");
15
16CREATE TABLE locations (
17    id bigint NOT NULL,
18    uuid uuid DEFAULT uuid_generate_v4() NOT NULL,
19    address_id bigint NOT NULL
20);
21
22CREATE TABLE shops (
23    id bigint NOT NULL,
24    name character varying NOT NULL,
25    location_id bigint NOT NULL
26);
27
28CREATE TABLE inventories (
29    id bigint NOT NULL,
30    shop_id bigint NOT NULL,
31    status character varying NOT NULL
32); 
33     
34SELECT
35    s.id AS shop_id,
36    s.name AS shop_name,
37    i.status AS inventory_status,
38    l.uuid AS location_uuid,
39    a.line1 AS addr_line,
40    a.latitude AS lat,
41    a.longitude AS lng
42FROM addresses a
43JOIN locations l ON l.address_id = a.id
44JOIN shops s ON s.location_id = l.id
45JOIN inventories i ON i.shop_id = s.id
46WHERE ST_DWithin(
47    a.position,                             -- the position of each address
48    ST_SetSRID(ST_Point(10.0, 10.0), 4326), -- the center of the circle
49    1000,                                   -- radius distance in meters
50    true
51); 
52     
53--- only search for the addresses
54SELECT
55    a.id as addr_id,
56    a.line1 AS addr_line,
57    a.latitude AS lat,
58    a.longitude AS lng
59FROM addresses a
60WHERE ST_DWithin(
61    a.position,                             -- the position of each address
62    ST_SetSRID(ST_Point(10.0, 10.0), 4326), -- the center of the circle
63    1000,                                   -- radius distance in meters
64    true
65);
66
67--- get the rest of the data
68SELECT
69    s.id AS shop_id,
70    s.name AS shop_name,
71    i.status AS inventory_status,
72    l.id AS location_id,
73    l.uuid AS location_uuid
74FROM locations l
75JOIN shops s ON s.location_id = l.id
76JOIN inventories i ON i.shop_id = s.id
77WHERE
78    l.address_id IN (1, 2, 3, 4, 5)  -- potentially thousands of values
79;