DataTables is a fantastic software that turns any HTML table into an interactive and editable one.
Chicago Cityscape uses DataTables on pretty much every page. DataTables also provides server-side processing for PHP to grab the right data from a database and show it to the user, 10 records at a time (the number of records can be changed by the user to show more records at a time).
Screenshot showing my new function, using the function, and the results.
Problem
One of the problems I’ve had to get around is that the DataTables Editor script recognizes a SELECT statement with only one function per field. If there’s a second function that’s manipulating a field in the SELECT statement then the ALIAS won’t work and the JavaScript will not be able to read and show the data properly.
I almost always use the two functions ST_AsGeoJSON and ST_Transform on my geographic data to (1) transform it from the SRID of 3435 to 4326 for displaying on web mercator maps, and (2) converting the GIS data into GeoJSON strings for easy transference to the Leaflet JavaScript library.
This is a known issue – see this thread on the DataTables forums – that Allan, the DataTables creator, has acknowledged and provided an alternative solution for.
Solution
It turns out that it’s easy to write a function that combines both functions.
CREATE OR REPLACE FUNCTION ST_TransformAsGeoJSON (field geometry, srid int =4326, simplify int =5)
RETURNS text
RETURN ST_AsGeoJSON(ST_Transform(field, srid), simplify);
The code above is a function I call ST_TransformAsGeoJSON that combines the two functions I already described, and is flexible by letting the user specify in the arguments the table and field, the SRID to transform (reproject) to, and the simplify variable that can be used in ST_AsGeoJSON that tells it how many decimal points to use in coordinates.
One process I’ve relied on in the past to keep my Twitter postings fresh is automatically cross-posting photos that I upload to Flickr to Twitter. This is done through Zapier and inspired by this answer in their forums.
Zapier currently doesn’t have any Mastodon integrations, but it’s possible to use Mastodon’s API and Zapier’s webhook function to notice your newly-uploaded photos from Zapier’s Flickr integration and “toot” them to your Mastodon account.
Note: I originally set this up as an RSS feed to Mastodon Zap before realizing that Zapier already has a Flickr integration.
First, to prepare for creating a Zap later, you’ll need to create an app in your Mastodon server. You can create an app by going to edit your profile and then opening the “Development” tab.
Select the “New application” button, give it a name, change the privileges, and use “http://zapier.com” as the only Redirect URI. Then check the appropriate boxes so that only a single privilege is checked: “write:statuses”.
Give your application a name, enter https://zapier.com as the Redirect URI, and check only the box next to “write:statuses”.
Mastodon will create a token for you to insert at the end of this URL that Zapier needs: https://mastodon.social/api/v1/statuses?access_token=INSERT_YOUR_ACCESS_TOKEN
If your Mastodon account is on a different server, then replace “mastodon.social” with the domain name of the server where your Mastodon account is.
Secondly, go to your Zapier account and create a new Zap. Search for the trigger app “Flickr” and then connect Zapier to your Flickr account if not already connected. The trigger should be something like what you see in the screenshot below.
When you make a new zap, use these settings: “Connect this app…Flickr…with this one…Webhooks by Zapier”. Then, “When this happens…New Photo…then do this! POST”. Zapier will automatically give it a name that you can try later and then you can select the “Try it” button to get stated and customize.
You’ll need to customize the action that Zapier takes each time there’s a new photo. Until there’s an integration with Mastodon there’s a little complexity to defining the webhook action that Zapier will do.
The type of webhook you’ll create is a “POST” and you only need to add one field to the payload that’s sent from Zapier to your Mastodon: “status”. The value of “status” can be whatever combination of text and fields that Zapier pulls from your Flickr.
The minimum field to insert is the link to the Flickr photo page. Mastodon will need this to generate a rich media preview to add to the Toot (since it’s not possible to send image attachments).
ChicagoCrashes dot org was, for many years, the only source for people to get information about traffic crashes in Chicago. I started it in 2011.
A screenshot of Chicago Crash Browser v0.2 showing what the website looked like on December 30, 2011.
It was updated annually with data from two years ago, because of how the Illinois Department of Transportation processed the reports from all over the state. I shut it down because it had outdated code, I was maintaining it in my free time, and I didn’t want to update the code or spend all the time every year integrating the new data.
In 2015, the Chicago Police Department started testing an electronic crash reporting system in some districts that meant police officers could write reports and they would immediately show up in a public database (in the city’s data portal). The CPD expanded this to all districts in September 2017. (A big caveat to using the new dataset is that it has citywide data for only four and a half years.)
Since then, whenever someone asked me for crash data (mostly from John to illustrate Streetsblog Chicago articles), I would head to the data portal and grab data from just the block or intersection where someone had recently been injured or killed. I would load the traffic crash data into QGIS and visualize it. I found this also to be painstaking.
Now, with renewed attention on the common and unfixed causes of KSIs (“industry” term for killed or seriously injured) that we’re seeing repeatedly across Chicago – read about the contributing cause of Gerardo Marciales’s death – I decided to relaunch a version of Chicago Crash Browser.
The new version doesn’t have a name, because it’s part of the “Transportation Snapshot” in Chicago Cityscape, the real estate information platform I operate. It’s also behind a paywall, because that’s how Chicago Cityscape is built.
I wanted to make things a lot easier for myself this round and it comes with a lot of benefits:
Explore all crash reports in a given area, whether that’s one you draw yourself or predefined in the Cityscape database.
Quickly filter by crash type (bicyclist, pedestrian, etc.) and injury severity.
Download the data for further analysis.
What the crash data looks like within Chicago Cityscape.
How to access the Chicago Crash Browser
The crash data requires a Cityscape membership. I created a new tier of membership that cannot be signed up – I must grant it to you. It will give you access only to Transportation Snapshots.
Mention or DM me on Twitter, @stevevance, saying you’d like access to the crash data. Tell me what your email you used to create an account on Chicago Cityscape.
I’ll modify your membership to give you access to the “transportation tier” and tell you to sign out and sign back in to activate it.
Once you’re in, this video shows you how to draw a “Personal Place” and explore the traffic crash data there. Text instructions are below.
From the Chicago Cityscape homepage, click on “Maps” in the menu bar and then click “Draw your own map”.
On the “Personal Place” page that appears with a large map, decide which shape you’d like to draw: a circle with a radius that you specify (good for intersections), a square or rectangle (good for street blocks), or an arbitrary polygon (good for winding streets in parks). Click the shape and draw it according to the onscreen instructions. For intersections I recommend making the circle 150 feet for small intersections and 200 feet for long intersections; this is because intersections have an effect on driving beyond the box.
Once you’ve completed drawing the shape, a popup window appears with the button to “view & save this Personal Place”. Click that button and a new browser tab will open with something called a “Place Snapshot”.
In the Place Snapshot enter a name for your Personal Place and click the “Save” button.
Scroll down and, under the “Additional Snapshots” heading, click the link for “Transportation & Jobs Snapshot”; a new browser tab will open.
In Transportation Snapshot, scroll down and look for “Traffic crashes”. You’ve made it to the new Chicago Crash Browser.
Short answer: To provide more shoppers for the local businesses. Read on for the longer answer.
Over on Chicago Cityscape I added a new feature called “market analysis” which measures the number of people who live within specific walking areas (measured by time) and driving areas (measured by distance).
I am in favor of removing apartment & condo bans in Chicago, especially in areas where they were previously allowed and near train stations.
Jefferson Park is centered around two co-located train stations, serviced by CTA and Metra respectively. There have been multiple proposals for multi-family housing near the stations (collectively called the Jefferson Park Transit Center) and some have been approved.
Always, however, there are residents who resist these proposals and the number of originally proposed apartments or condos gets reduced in the final version (classic NIMBYism).
There’re four reasons – at least – why more housing should be allowed near the Jefferson Park Transit Center:
Locally owned businesses require a significant amount of shoppers who live nearby and walk up traffic
More people should have the opportunity to live near low-cost transportation
It will include more affordable housing, through Chicago’s inclusionary zoning rules (the Affordable Requirements Ordinance, ARO)
There will be less driving, and therefore lower household transportation costs and less neighborhood pollution
To support the first reason, I used the “market analysis” tool to see just how many people live in a walkable area centered around Veterans Square, a mixed-use office and retail development adjacent to the train stations.
Comparatively, 19,707 people live within a 10 minute walk to The Crotch, or the center of Wicker Park, at the intersection of Milwaukee/North/Damen (get the Address Snapshot). The Blue Line station is about 75 feet south of the center point.
I would grant the low Veterans Square number a small discount based on the proximity to the Kennedy Expressway, which severely truncates walking areas up and down the northwest side. Still, even with that discount, ending up with less than half the amount as the one in Wicker Park, is disturbing. Wicker Park is hardly characterized by high-density housing. In fact, all of the new high-rises are just outside the 10 minute walk shed!
This infographic visually compares the difference between running a PostGIS comparison query like ST_Intersects on a large shape versus a subdivided version of that large shape. Click to embiggen.
Hundreds of GIS intersection comparisons are completed every hour on Chicago Cityscape.*
People are looking at, say, a map of the South Shore community area. That “Place” page then grabs all of the building permits, building violations, business licenses, and other “feature layers” that are stored as points.
A classic “point in polygon” comparison is made using the ST_Intersects(place_geometry, permits_geometry) function.
This has worked very well for several years.
The problem
But as Chicago Cityscape handles larger shapes – they come from users drawing their own, large shapes, and from large shapes like the downtown Chicago area – this query doesn’t cut it.
Setting indexes on the geometry is imperative, but it’s not the end of the to optimize performance. That’s because the index of the geometry is a rectangular bounding box (which is also called an “envelope” in GIS) that contains the entire shape of the South Shore community area.
The downtown Chicago area, however, is not even the largest shape I have. That belongs to the new Place, “Neighborhood Opportunity Fund investment zones” (NOF). Combined, they cover 75 square miles of Chicago. Downtown is only 7.7 square miles.
After I added the NOF map and tested its Place page, it “crashed” my server, metaphorically speaking. The query to just count the number of building permits in the area would take about five minutes.
There had to be a better way; in the meantime, however, I divided the NOF map into the West and South sections. This hardly improved the counting time.
The solution
Thankfully, today, I saw a tweet from Paul Ramsey linking to his blog that linked to his slides from a recent presentation about the use of PostgreSQL to store and manipulate GIS data.
In it he explained how the ST_Subdividefunction worked. I’m going to demonstrate it using graphics from my own maps.
A normal intersection comparison, using ST_Intersects(place_geometry, permits_geometry) in a query creates a bounding box (envelope) around each geometry and quickly determines whether the two envelopes overlap. If they do, then it checks again to see if the actual geometries overlap. If they do, that data is returned as a response to your query.
When your two datasets are massive, like the NOF zones, which collectively cover 1/3rd of Chicago, and the building permits, which are found across the entire city…well, that led to the five minutes counting time.
Enter ST_Subdivide. To use it properly you would run it against your existing geometry and store the much smaller shapes, derived from the big shape, in a new table. I applied the function to all the 22,203 maps that Chicago Cityscape has and stored their unique IDs and subdivided geometries in a new “lookup” table.
Now, any time I want to compare the building permits against the NOF, the building permits are instead compared to the small shapes that were subdivided.
The query
Chicago Cityscape uses a single table (created as a materialized view) to combine all 22,203 maps. Each map is stored in a source table (for example, there’s a table to hold the 77 community areas) and the materialized view runs once a day to combine all of the maps in the source tables. This ensures our data is managed well: different source tables can hold different information, and the single table holds only the name, type, and geometry of the source tables, for faster comparison. Each entry in the single table also has a “slug”, its unique identifier.
Thus, the materialized view of the subdivided maps is created from the aforementioned single table, using this query:
create materialized view view_places_subdivided as
select gid || '_' || random() as gid, slug, st_subdivide(geom) as geom
from view_places;
The “gid” is designed to create a new unique ID field, as the slug field will be repeated for every subdivided of each map. A unique ID field is necessary if you want to refresh the materialized view concurrently (to allow for other queries to access the materialized view while it’s being refreshed).
* The results are cached for a few hours, because the feature layers change 1-2 times per day and at different times each day, so the limited duration cache accommodates that. Ideally I would code a way to invalidate the cache when the feature layer data is updated.
Update 12/31/19: ST_Subdivide will fail if your geometries have any or certain geometry errors (I don’t know if it’s any kind of error, or certain kinds of errors that make the function fail). Chicago Cityscape has over 37,000 features that ST_Subdivide is attempting to process, and there is a lot of room for error in managing that many features from dozens of sources.
