I have created three tables ch01.restaurants (SRID 2163;columnsfranchise, geom), ch01.lu_franchises (lookup table, no geom field;columnsid, franchise), ch01.highways (SRID 2163;columnsgid, 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

How can I alter this code so that my result will contain all the franchise restaurants within one mile of a highway?

  • Please Edit the question to indicate what data is contained in the tables. It's very easy to get no rows in a poorly formed join. You should also specify what SRID was used. – Vince Aug 21 '17 at 11:05
  • Why do you need (DISTINCT r.id). Else, query is looking good to me. I also suspect some CRS/SRID issue. – pLumo Aug 21 '17 at 13:41
  • Hello Vince and RoVo, I edited my question and figured out that the geographic data have the same SRID 1609... – geofee Aug 23 '17 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.