0

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('''
  SELECT
  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)

       table_name
   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?

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

1
  • doh. Thank you for your quick response! – user190641 Sep 11 '19 at 16:45

Your Answer

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

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