Why missing address IDs in postgresql/postgis query results?

Question

I have two tables "ad" and "st" in my postgres DB. Table ad has 87 rows while st has 16,060 rows. Following code works perfectly to select st.geom (streets' geometry) within 50 meter radius with respect to every address locations.

SELECT 
 ad.gid As address_id,
 ad.geom As address,
 st.geom As streets
FROM st
JOIN ad ON ST_DWithin(ad.geom, st.geom, 50.0)
Order by address_id

However, what I noticed that in the query results some address IDs are missing (for example; 5, 51, 56 etc.) which is not desired as I needed to search nearest streets for all 87 rows of ad table. Here is the screen shot of query results.

Query results: address_id = 5 is missing

Using this command, I cross checked that address_id_5 does exist in the respective column.

Select ad.gid from ad;

address ID query

Can some one please help me to understand this ambiguity? There are seven address locations missing out of 87. I have tried the same on other data as well and the problem persists. Both tables contain spatial indexes already.

Answer 1

It means that those addresses are located at more than 50 units from a street. They are therefore filtered out by your (inner) JOIN.

You can

• increase the distance in ST_Dwithin
• or use a LEFT JOIN while inverting st and ad in your query.

But your query does not return the nearest street, it returns all street/address combinations which are less than 50 m away from each other.

If you want to get the nearest street for each address, you can write for example:

SELECT DISTINCT ON (ad.gid)
 ad.gid AS address_id,
 st.gid AS street_id,
 ad.geom AS geom
FROM ad
JOIN st ON ST_DWithin(ad.geom, st.geom, 50.0)
ORDER BY ad.gid, ST_Distance(ad.geom, st.geom)

ST_DWithin is only used to restrict the search to streets that are less than 50 m away from the address to speed up the query if a spatial index is available.

• khajlk: Thank you. Although, use of 'Left Join' increases the execution time a bit. Nevertheless, solved the problem.