Non-spatial queries in PostGIS
A part of my PostGIS project
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 is the spatial dataset of the states and
fortune is the spatial information of Fortune 500 companies.
First of all, I query all the data in
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;
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,
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
You can confirm that two tables are joined. Thus, two geometry columns are in the table.