I am following a tutorial for PostGIS. I have uploaded all the example datasets (all shapefiles) into a PostgreSQL database, although I have modified the names so they do not all match the names of those in the tutorial. I then ran the following code from the tutorial:

df = pd.read_sql_query('''
  subways.name AS subway_name,
  neighborhoods.name AS neighborhood_name,
  neighborhoods.boroname AS borough
  FROM neighborhoods AS neighborhoods
  JOIN stations AS subways
  ON ST_Contains(neighborhoods.geom, subways.geom);''', engine)

This worked and returned the expected output. But when I went back through this query I noticed that the table "subways" was referenced (maybe this is where I am confused) but I don't have a table named "subways" because I had titled this table "stations". Indeed, here is the list of available tables in the db:

pd.read_sql_query('''SELECT table_name FROM information_schema.tables
   WHERE table_schema = 'public';''',engine)

   0   geography_columns
   1    geometry_columns
   2     spatial_ref_sys
   3       neighborhoods
   4      raster_columns
   5    raster_overviews
   6             streets
   7               solar
   8            stations
   9              census
   10           vehicles

So, why is this query working?

I think I am asking for rows from a table named subways with the subway.name in the SELECT portion of the query, isn't it true?

  • Have you run \dt from the same place you ran the query - i. e. psql?
    – Vérace
    Commented Sep 11, 2019 at 4:59
  • Sorry, what I meant was run both queries from psql. p.s. welcome to the forum! :-)
    – Vérace
    Commented Sep 11, 2019 at 5:50
  • thank you for your comments!
    – user190641
    Commented Sep 11, 2019 at 16:45

1 Answer 1

FROM ...
     JOIN stations AS subways

This is called an alias. You aliased stations as subways. So for the rest of the query (ON, SELECT part) the table is called subways and you can only reference it as subways.

  • doh. Thank you for your quick response!
    – user190641
    Commented Sep 11, 2019 at 16:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.