PostGIS is capable of just about any GIS operation you're used to doing in a desktop GIS. Here we cover typical points-in-polygon queries in PostGIS.
If you're new to joins in SQL, you'll notice a few things about the queries here. First, we select from multiple tables and give each a unique name (so instead of FROM nycc you'll tend to use FROM nycc cc, listings l if your query is over nycc and listings. Second, you'll be grouping the polygons by a unique id. As I'm using CartoDB here, I will group by cartodb_id.
If we have a table called nycc of City Council districts and a table called listings of Airbnb listings, we can find the average price by city council district like so:
SELECT cc.*, AVG(l.price) AS avg_price
FROM nycc cc, listings l
WHERE ST_within(l.the_geom, cc.the_geom)
GROUP BY cc.cartodb_id
This selects all of the city council districts and adds a new column called avg_price that is the average price of an Airbnb listing inside that council district. We check if a listing is within a council district in the WHERE clause using ST_Within. CartoDB uses PostGIS, and you can see the output of this query in CartoDB.
This query is similar to above. The only difference is that we select the COUNT of listings rather than the average price. You can see it in action on this CartoDB map.
SELECT cc.*, COUNT(l.cartodb_id) AS listings_count
FROM nycc cc, listings l
WHERE ST_within(l.the_geom, cc.the_geom)
GROUP BY cc.cartodb_id
Boundless has an excellent workshop on PostGIS, including spatial relationships