Want to return all businesses within a distance of address using PostGIS. Keeps ignoring the distanc-CodePudding

I'm having an issue where I'm trying to return all companies within a certain distance of an address, but I can't figure out why I'm getting all the companies in my database as my response.

The desired outcome would be to provide the address id, specify a distance, and return all the companies within that distance.

I've outlined below some of the commands I ran to create the coordinates columns on my tables and some of the queries I've tried running that return all companies.

NOTE: The coordinates columns are: SRID=4326;POINT(-LONG LAT) but putting them in the table gave me some weird code formatting error, and StackOverflow wasn't letting me post it.

Ran these SQL commands

ADD COLUMN "coordinates" geometry(POINT, 4326)
CREATE INDEX "address_coordinates_idx" ON "addresses" USING GIST ("coordinates");

ADD COLUMN "coordinates" geometry(POINT, 4326)
CREATE INDEX "companies_coordinates_idx" ON "companies" USING GIST ("coordinates");

Addresses Table

id	address	city	state	coordinates
uuid	fake name	city	state	refer to note
uuid	fake name	city	state	refer to note
uuid	fake name	city	state	refer to note

Companies Table

id	name	phone	description	coordinates
uuid	fake name	fake phone	fake desc	refer to note
uuid	fake name	fake phone	fake desc	refer to note
uuid	fake name	fake phone	fake desc	refer to note

Current SQL Query:

SELECT companies.*
FROM companies
WHERE ST_DWithin(companies.coordinates,
                   (SELECT coordinates
                    FROM addresses
                    WHERE id = 'the address id' ), 80467.2);

Another query that gave me the same results

SELECT companies.*
FROM companies, addresses
WHERE ST_DWithin(companies.coordinates, addresses.coordinates, 80467.2)
AND addresses.id = 'the address id';

CodePudding user response：

st_dwithin() over geometries uses the CRS unit, so for 4326 it's in degrees, and 80467 degrees is the whole world. When used with geographies, it's in meters.

You would also have to index the geographies.

While there, the same query would be more efficient with a join on st_dwithin

CREATE INDEX "address_coordinates_geog_idx" ON "addresses" USING GIST (("coordinates"::geography));
CREATE INDEX "companies_coordinates_geog_idx" ON "companies" USING GIST (("coordinates"::geography));

SELECT companies.*
FROM addresses 
JOIN companies
  ON ST_DWithin(companies.coordinates::geography, addresses.coordinates::geography, 80467.2)
WHERE addresses.id = 'the address id';