In the previous post, I imported two shapefiles into the PostGIS tables. In this post, I'm going to query data from the two tables. I note that the data queried in this post is not spatial data. All of my data is in the toyproject schema. There are two tables named state and fortune. state is the spatial dataset of the states and fortune is the spatial information of Fortune 500 companies.

Query Everything

First of all, I query all the data in state and fortune following the basic query command in PostgreSQL.

SELECT * from toyproject.state
SELECT * from toyproject.fortune

Then I obtain a table as follows for fortune table.

Group and order by a specific column

Next, I obtain a number of companies on the list for each city. To reduce the number of cities on the list, I set a condition that the cities with more than 5 companies are left.

SELECT city, COUNT(revenues) as total FROM toyproject.fortune
GROUP BY city
HAVING COUNT(revenues)>5
ORDER BY total DESC;

When GROUP BY is used to summarize the data by city, WHERE is unavailable. Instead, having is used to set a condition. in having, the alias name cannot be used (in this case, total). LA and Boston are not on this list!

Join two tables

Like the non-spatial SQL database, it is possible to join two tables.

SELECT * FROM toyproject.fortune
INNER JOIN toyproject.state as state ON fortune.state = state.stusps

All the columns in state table are appended to fortune table.

You can confirm that two tables are joined. Thus, two geometry columns are in the table.