
How to Make Maps Using Leaflet.js, PostGIS and Chicago Open Data - samblogs
https://samc1213.github.io/2019/04/29/rideshare-dataset-getting-started/
======
dbetteridge
A fun tip, if you're using PostGIS you can actually generate the Geojson
directly in the SQL query with
[http://www.postgis.org/docs/ST_AsGeoJSON.html](http://www.postgis.org/docs/ST_AsGeoJSON.html)

and if you're running a version of PostGIS with protobuffers enabled (Not AWS
RDS) you can generate vector tiles on the fly with
[https://postgis.net/docs/ST_AsMVT.html](https://postgis.net/docs/ST_AsMVT.html)
which are great for displaying large datasets.

~~~
samblogs
The vector file seems really useful. I ran a query that resulted in a 1.5 GB
geojson file... much too big for Leaflet. Maybe the Vector tile would be
compact enough? Thanks!

~~~
gloflo
Reduce the precision, 15 decimal places are useful for picometers, not a web
map. Try 5-6.

~~~
Demiurge
It never seems worth the effort in my testing, as you have to come up with
your own loop to do the truncating, and the benefit is marginal. In this case
he’d have 1.2gb file, I doubt that fixes the problem.

~~~
durkie
for what it's worth, ST_AsGeoJSON lets you specify decimal precision as an
optional argument, so you at least don't have to write a truncating loop.

Another good option with many polygon geometries is Base64 encoding the output
of ST_AsGeobuf -- it is many times smaller than GeoJSON and still pretty easy
to handle from Javascript. (Needs libprotobuf-c on the PostGIS side and
geobuf.js+pbf.js on the JS side)

~~~
Demiurge
Thats cool, I should try AsGeobuf as a soft upgrade to one of the older tile
GeoJSON site :)

------
steve-chavez
Nice! I think you could have used pg COPY .. CSV[0] plus a CREATE TABLE .. AS
to avoid the custom python script and do the importing with only SQL.

Also, as others have mentioned you can generate the st_asmvt format directly
from pg. This plus PostgREST binary output[1] can get you a way to query your
table directly through a REST API.

[0]:[https://www.postgresql.org/docs/current/sql-
copy.html](https://www.postgresql.org/docs/current/sql-copy.html)

[1]:[http://postgrest.org/en/v5.2/api.html#binary-
output](http://postgrest.org/en/v5.2/api.html#binary-output)

------
sailfast
Kudos on writing a solid comprehensive article on mapping, the tooling
available, and how to do it step by step. (GDAL and everything!) Not an easy
task, and I found the format engaging. Great stuff.

It looks like the city of Chicago might be sitting on top of the Socrata API,
which allows you to run a distinct - if you are visualizing counts for each
tract (or averages and other types of calculations) it would save you the
Digital Ocean resources to grab the API counts either on-the-fly or as static
files for the tracts in Chicago and render them that way via Leaflet, but
obviously this does not allow for quite as novel an analysis as PostGIS
offers! (here's the API page that the City of Chicago links to from their page
[https://dev.socrata.com/docs/functions/#2.1,](https://dev.socrata.com/docs/functions/#2.1,))

~~~
samblogs
Thanks for your feedback! I have 0 experience with mapping so it was fun to
see all the tools out there. There's much more out there than I expected. I
did notice they had the API, but figured I would learn something a little more
universally applicable by learning PostGIS. I should try that out though for a
more lightweight and cheaper option next time

~~~
sailfast
Absolutely and that's critical knowledge - PostGIS is super powerful and the
experience with GDAL is definitely good to have (and to understand the
transformations that are possible)

One of my favorite primers is by Robert Simmon who I had the pleasure of
seeing at OpenVis:
[https://youtu.be/N_dmiQI1s24](https://youtu.be/N_dmiQI1s24)

RE mapping tools - I've been working a lot lately with Vector Tiles and their
embedded props and from a web-mapping perspective it's nice to download the
geometry once and then style many times in terms of over-the-wire efficiency.

Happy hunting and thanks for your post.

------
Stevvo
A similar article, this one is a lot of fun:
[https://blog.patricktriest.com/game-of-thrones-map-node-
post...](https://blog.patricktriest.com/game-of-thrones-map-node-postgres-
redis/)

------
colinramsay
Does anyone have a link to a decent primer on PostGIS please?

~~~
gloflo
[https://medium.com/@tjukanov/why-should-you-care-about-
postg...](https://medium.com/@tjukanov/why-should-you-care-about-postgis-a-
gentle-introduction-to-spatial-databases-9eccd26bc42b)

------
serpi
I would seriously throw Leaflet down the cliff and replacd with Mapbox for
just the performance alone. All of the features plus documentation are there
with Mapbox and the performance is ridiculously better.

