I have created three tables
ch01.restaurants (SRID 2163;columns
franchise, geom), ch01.lu_franchises (lookup table, no geom field;columns
id, franchise), ch01.highways (SRID 2163;columns
gid, feature, name, state, geom) in PostGIS that I want to use for a query which includes a spatial join. Running the query in pgadmin3, the following happens:
Total query runtime: 32 msec 0 Zeilen geholt.
My problem is that the table that will be created doesn't contain any data. I get a table with the heading
franchise | total
Here's my code:
SELECT f.franchise, COUNT(DISTINCT r.id) As total FROM ch01.restaurants As r INNER JOIN ch01.lu_franchises As f ON r.franchise = f.id INNER JOIN ch01.highways As h ON ST_DWITHIN(r.geom,h.geom,1609) GROUP BY f.franchise ORDER BY total DESC;
How can I alter this code so that my result will contain all the franchise restaurants within one mile of a highway?