Connecting to databases and executing SQL queries

There is an ST_ spatial prefix, which stands for Spatial Type, on all evolving PostGIS functions.

This evolution is aligned with the ISO standard SQL-MM defining the spatial type and associated routines. In this case, MM stands for multimedia.

This is all you need to know about it because all prior formats have been deprecated (and are tolerated but not supported). Not all of the functions will automatically spatially index but fortunately for us, the most popular ones do. The && operator in Figure 3.3 selects bounding boxes that overlap or touch. The operator is an index-only query.

Now run the following code and notice how many rows are returned. The output for Figure 3.3 yields 2,790 rows that meet the criteria of having extremely low-income units.

SELECT * FROM ch3."Affordable_Housing_Production_by_Building" borough
JOIN ch3."DOHMH_Indoor_Environmental_Complaints" Incident_Address_Borough...