I have a table in postgresql 9.2 that stores the latitude and longitude of locations as integer values.
I intend to do something like when a user searches for a location, he also gets information on other locations that are within a 7 mile radius of that searched location.
How do i use postGIS for this since i am new to it. Any idea.?
You'll actually want to store the latitude and longitude as a point.
CREATE TABLE postal_codes
(
zip character varying(7) NOT NULL,
state character(2) NOT NULL,
city character varying(50) NOT NULL,
geoloc geography(Point,4326) NOT NULL
);
INSERT INTO postal_codes
VALUES (
'35004', 'AL', 'Moody',
ST_GeographyFromText('SRID=4326;POINT(-86.50249 33.606379)') -- lon lat
);
You can then get all points within a certain distance using STDWithin.
SELECT geoloc
FROM postal_codes
WHERE ST_DWithin(
geoloc, -- The current point being evaluated
'thegeolocyoustoredawaysomewhere', -- the location of the person
-- you're giving info to
7 * 1.6 * 1000 -- Miles to meters
);
Or:
SELECT geoloc
FROM postal_codes
WHERE ST_DWithin(
geoloc,
(
SELECT geoloc
FROM postal_codes
WHERE zip = '12345'
),
7 * 1.6 * 1000
);
To boost performance a bit for other kinds of queries (not any seen here), use a GiST index:
CREATE INDEX geoloc_idx
ON postal_codes
USING gist (geoloc);
