“What is this you call property?”, asked Massasoit, the leader of the Native American Wampanoag tribe. “It cannot be the earth, for the land is our mother, nourishing all her children, beasts, birds, fish and all men. The woods, the streams, everything on it belongs to everybody and is for the use of all. How can one man say it belongs only to him?”

Good question, Massasoit. Yet, due to a tragic combination of the pathogenic bacteria Leptospira and aggressive colonists the answer became irrelevant and the concept of land ownership proliferated through the majestic lands of the new world like a virus.

Today, the dust has settled and the iron horse has carried the white man to the west coast, where I currently reside. As I explored the wonderful city of Los Angeles I began to wonder to whom, exactly do I owe the pleasure of my environment? Who “owns” the dirt I stand on? So I did some research.

The “United States” is divided into 3,144 counties and county equivalents. Of these, Los Angeles county is the most populous, with over 10 million residents. The least populous, Loving County, Texas has only 82. Funny story, in 2006 a group of Libertarians attempted to buy up land and seize power in Loving County with the goal of establishing their ideals, but were thwarted by the local sheriff. The group is currently featured on a “Wanted” poster in the county’s sole courthouse.

LA County has an area of 4,751 mi², divided across 88 cities and 2,379,680 parcels. However, much of the land is “unincorporated”, meaning it does not fall within the jurisdiction of an established city. If you would like to establish your own city in LA County you can apply to the LAFCO for as little as $2,500¹. The information regarding parcel owner, location, and “assessed value” for collecting property taxes is maintained by the Assessor’s Office².

Formats and Tools

Most, if not all, counties use GIS (geographic information systems) to maintain this data³. The LA office uses Microsoft Access for ownership and assessed value information, and the popular Shapefile format for geometry and mapping. ESRI (environmental systems research institute), founded in 1969, dominates land-use consulting with their popular ArcGIS software and Shapefile format developed in the early 1990s. A Shapefile consists of several different files, 3 of which are mandatory:

.shp – feature geometry as a set of either WKT (well known text) of WKB (well known binary) coordinates. Each of these entries can be one of several different simple datatypes such as

POINT (30 10)

LINESTRING (30 10, 10 30, 40 40)

POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))

MULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)), ((15 5, 40 10, 10 20, 5 10, 15 5)))

.shx – index of positional geometry to allow quickly stepping forward and backward

.dbf – old school simple database format popular in the 1990s, here stores attributes for each shape

There are several optional files, the most important of which though is

.prj – represents the projection information of the coordinates in the shapes. More on this soon.

I performed extensive cleaning and simplification on the assessor’s office data as part of this analysis, the bulk of which was done with PostgreSQL and the fantastic PostGIS extension⁴.

If you want to follow along, say with an EC2 instance, first grab some dependencies.

 sudo apt-get -y install postgresql postgresql-contrib postgis
postgresql-9.3-postgis-2.1-scripts

Now let’s create a database for our geospatial data

createdb gis
psql -d gis -c 'create extension postgis'

Projections

The earth is not a perfect sphere. We represent it instead as a “geoid”, a mathematical object that, ideally, represents the precise shape of the earth if it were only under the influence of gravitation and rotation. While imperfect, the geoid, combined with satellite data provides a somewhat close approximation to the actual shape of the earth. The geoid works in tandem with different “datum”, which are coordinate systems used by regions to define a coordinate system consistent with the geoid. Today, improvement to the model and coordinate systems has led to the possibility of a single global standard for the globe, WGS84, that is gaining in popularity. Still, datums are typically more precise when defined only for a single region.

The datum used by the LA Assessor’s office is NAD1983. The naming convention originated with the first North American survey in 1901, based on an ellipsoid geoid model developed in 1866. The system was updated in 1927 based on surveys of the entire continent but using the same geoid, and updated again in 1983 using satellite and remote sensing data using GRS 80 as the geoid, the same model originally used by the popular global standard WGS84. If it sounds simple, it is not, but if you’re interested it’s a great reason to learn spherical harmonics.

Just remember a datum is a coordinate system defined on a geoid, which is a model of the earth. Geoids and datums exist for other planets too, like Mars.

I re-projected the assessor’s office data from NAD1983 to WGS84 using QGIS. All projections have a corresponding SRID (spatial reference system identifier). Let’s load the shapefile into a PostGIS table, making sure to tell it the projection WGS84, which has an SRID of 43265. You can download it from me.

wget http://dwur9qzdkvp67.cloudfront.net/la_parcels.tar.xz
tar -xvf la_parcels.tar.xz
shp2pgsql -I -s 4326 -g geom la_parcels.shp la_parcels | psql -d gis > import.log

Basic Queries

Next let’s get an SQL prompt

psql -d gis

And run a simple query. This should improve performance a bit.

vacuum analyze;

Now let’s have some fun. What are the most expensive pieces of land in LA County?

select land_value, owner_name, address_number, street_name
from la_parcels order by land_value desc limit 10;

Entertainment and oil companies dominate here. That is land only though. I wonder which of these has the most expensive “improvement”, or building? Let’s use a nested query.

select improvement_value, owner_name, address_number, street_name from
(select land_value, improvement_value, owner_name, address_number, street_name
from la_parcels order by land_value desc limit 10)
as lands order by improvement_value desc;

20th Century Fox wins with $265 million. Exxon Mobil’s sprawling refinery is assessed at only $19 million. Someone must be trying hard to keep property taxes low. Okay what about the most expensive properties overall? Combining land and building value?

select (land_value+improvement_value) as
total_value, owner_name, address_number, street_name
from la_parcels order by total_value desc limit 20;

Hospitals monopolize the top spots. Healthcare is expensive. No surprise to see the magnificent Getty Center either. I wonder if the multiple entries are redundant or it’s worth $2 billion. Wouldn’t be surprised either way. Did you know it’s free? Free! Unlike the hospital.

Let’s find another landmark. How about Dodger Stadium? We’ll use a forgiving string compare to make sure we match the street.

select (land_value+improvement_value) as
total_value, owner_name, address_number, street_name
from la_parcels
where address_number = 1000 and street_name ilike 'elysian park%'

Dodgers stadium must be worth more than $84 million. How do the assessed values compare to real world values? Let’s use One Wilshire as an example. It sold in 2013 for $437.5 million and its assessed value is $297.5 million. Not too far off.

Now let’s use the aggregation function sum() and group by to find the most expensive cities by area in LA County. Since the city column is still a bit messy we’ll use having to eliminate the outliers.

select city, price_per_area from
    (select sum(area) as area, sum(land_value) as land_value, count(ain) as parcels, city,
    (sum(land_value) / sum(area)) as price_per_area
    from la_parcels group by city having count(ain) > 1000
    order by price_per_area desc)
as cities

I still want an answer to my original question. Who owns the most land?

select owner_name, count(ain),
sum(land_value)+sum(improvement_value) as holding,
sum(area) as lands from la_parcels
group by owner_name order by lands desc limit 10

By area huge swaths of the county is controlled by the federal and state government, with a few agriculture companies such as Tejon Ranch and Newhall Land and Farming Company sprinkled in.

More Advanced Queries

I wonder what percentage of LA County is not held by one of the 10 entities above or is unincorporated? Here we will use a view, which allows you to treat a query like its own table.

create view top_owners as select owner_name from
(select owner_name, count(ain),
sum(land_value)+sum(improvement_value) as holding,
sum(area) as lands from la_parcels
group by owner_name
order by lands desc limit 10) as owners

Now in order to select the lands these guys own we’ll use a join statement, specifying where the view and table intersect. Join by default is inner, meaning we’ll only get rows where the field matches. Now we can succinctly find the parcels owned by these entities.

select count(*) from la_parcels join
top_owners on la_parcels.owner_name = top_owners.owner_name

And finally use a union operation, which can combine multiple select statements into a single column.

select sum(area) from la_parcels join
top_owners on la_parcels.owner_name = top_owners.owner_name
union select sum(area) from la_parcels

About 30%. And finally what % of the land in the city of LA is devoted to public space? I think this is an important metric for any city.

select sum(area) from la_parcels where owner_name ilike 'l a city'
 or owner_name ilike 'l a city park' union select sum(area)
from la_parcels where city ilike 'los angeles'

8%. Not bad. Griffith park, Elysian park, MacArthur park, Runyon Canyon, Grand park, Vista Hermosa, LA has some fantastic public spaces. The largest green areas are owned by the federal or state government and are outside the city, though not terribly far. Due to lack of Zoning Code standardization it is difficult to get a good picture of what the lands are used for.

Spatial Queries

At last, we unleash PostGIS. Note that since we are using WGS84 our results will be in latitude and longitude rather than meters as above.

First it’s good to know what we’re working with.

select distinct GeometryType(geom) from la_parcels

The geom field is exclusively MULTIPOLYGON. If we want to work with simpler shapes we can unroll them with ST_Dumps() in to the POLYGON type.

Alright I wonder where is the geographic center of LA County? We’ll use ST_Extent() to roll up all of our geometries in to a bounding box, and find the X and Y coordinates of its center with ST_Centroid().

select ST_Y(ST_Centroid(ST_Extent(geom))),
ST_X(ST_Centroid(ST_Extent(geom))) from la_parcels

This result is from a simple box around our shapes. That is not very rigorous. Instead we should roll up all of our shapes together and form a “convex hull”, the minimum geometry that encloses them, and find the centroid of that. Let’s find how far the center of Malibu is from that point. Here we use a geometry constructor. the true in the ST_Distance() function gets us the distance across the geoid rather than straight through.

select ST_Distance(malibu_center, ST_MakePoint(-118.29553231211, 33.80924996626, 1))
from (select ST_Centroid(ST_ConvexHull(ST_Collect(geom)))
as malibu_center from la_parcels where city ilike 'malibu') as malibu

0.505358312522112 is our answer. That’s quite a drive. Especially in traffic.

Last but not least, I took the liberty of creating a web interface to this dataset. Moving around the whole dataset would be inefficient, so I use google maps and javascript to ask a minimal flask application, which in turn asks the database which parcels are within the bounding box of the map’s current view. The @ operator finds the geometries inside the envelope I build. The core query is simply:

select distinct
    ain,
    land_value + improvement_value as total_value,
    land_value,
    improvement_value,
    owner_name,
    year_built,
    address_number,
    street_name,
    city,
    state,
    zip_code,
    zoning_code,
    area,
    perimeter,
    ST_AsGeoJSON(ST_MakeValid(geom)),
    ST_AsGeoJSON(ST_Centroid(ST_MakeValid(geom)))
    from la_parcels
    where geom @ ST_MakeEnvelope(%s,%s,%s,%s)

The bounding box of the map is passed to the %s parameters. A few of the geometries are invalid so ST_MakeValid() helps us out there. The query is amazingly fast, around 12ms. Drawing on the map is the slow part. Google Maps has gotten too complicated. But it still works pretty well as long as you don’t zoom out too far and avoid residential areas. The american dream of individual home ownership is slowing down my app. Hopefully I can speed it up but for now you can play around with it here. Click on the geometry to see the value. Some buildings are broken up in to many individual parcels in three dimensions. That is the difference between an apartment and a condominium. In a condo, you own the parcel from the county.

Conclusion

I was disappointed with how difficult it was to obtain this data initially and the poor quality it came in. Governments and constituencies of all sizes stand to benefit enormously from investment in modern software tools and stronger commitments to transparency.

While building I began to dream of having all the parcels of the United States in a single database. That would be a fascinating study, but the data is horribly spread about and fragmented. If you are interested in obtaining the data for your county, or another, and structuring it in to the same schema I would be very grateful, and promise to share the collected information. You can track the completeness of what has been gathered for California here. Please contact me if interested in contributing.

What if we had the data for other nations too? Could we put the entire world in a computer?

References

[1] http://lalafco.org/Forms/Application%20Form12-11%27.pdf
[2] http://assessor.lacounty.gov/extranet/default.aspx
[3] to the pedants that still insist on using “these data” give it a rest, it’s confusing to most people
[4] http://postgis.net/
[5] http://spatialreference.org/ref/epsg/wgs-84/
[6] http://postgis.net/docs/manual-1.3/ch06.html

Do you love databases? Soylent is hiring a Chief Database Architect.

Which is more valuable: diamonds or water? It’s clear to me. Water is the perfect product. It is endlessly useful, cheap, simple, ubiquitous, and beautiful. Water is essential to all forms of life and is used in every industry, not to mention its calming presence. As our species continues to shape the planet I find it increasingly important that we take pains to conserve the purity of this critical chemical. This is why, when my friend George McGraw of digdeepwater.org invited me to take the 4 Liter challenge I jumped at the chance.

The rules are simple. Do not use more than 4 liters of water per day. I also decided to track ‘virtual’ water usage. That is, be mindful of the water used to produce a good that is used, in addition to water used directly. Given that a single toilet flush takes over 6L of water, and the cotton in a pair of jeans takes about 7000L to grow, some changes to my life were clearly in order.

Hygiene

I awake the morning of the challenge and throw off my cotton sheets with disgust. From this moment on every drop counts. I brush my teeth without water and put on dry deodorant. Relieving myself in the toilet is not an option. For a moment I consider following Clinton’s advice: “if it’s yellow let it mellow”, but decide to go full Bukowski instead: “sometimes you just have to piss in the sink”. Could we engineer more efficient kidneys?

For number two I had to plan in advance. Inadequate sanitation is an enormous quality of life problem globally. The most popular toilet in the world is no toilet at all as 4 in 10 people in the world defecate in the open. Flush toilets use enormous quantities of water so I needed a way to make it unnecessary.

Feces are almost entirely deceased gut bacteria and water. I massacred my gut bacteria the day before by consuming a DIY Soylent version with no fiber and taking 500mg of Rifaximin, an antibiotic with poor bioavailability, meaning it stays in your gut and kills bacteria. Soylent’s microbiome consultant advised that this is a terrible idea so I do not recommend it. However, it worked. Throughout the challenge I did not defecate.

In lieu of showering I sprayed myself with AOBiome’s custom skin bacteria blend. Body odor is caused by the emissions of proliferating skin bacteria, as unique as a fingerprint. The Nitrosomonas eutropha taking over my skin now metabolizes ammonia into odorless nitrite and nitric oxide. Success! I wish I had a strain that excreted lipases, as my hair was still greasy.

Direct water usage: 0L

Virtual water usage: 0L

Clothing

My standard outfit is mostly cotton, which takes 20,000L of water per kg so I had to improvise. I did some research and settled on Nomex, a meta-aramid invented by DuPont in the 1960’s. Nomex is a fantastic material used in applications as diverse as circuit boards, loudspeakers, and clothing. Made via condensation from m-phenylenediamine and isophthaloyl chloride, its production uses no water. I found a Nomex flight suit on Alibaba and added a “Soylent” patch. I love it. It’s cheap, simple, comfortable, and fireproof, just in case. I also did no laundry of course, which would have used 170L.

Direct water usage: 0L

Virtual water usage: 0L

Food

There are some species that never drink water. They obtain it from the food they eat, or synthesize it biochemically. Humans only synthesize about 10% of their water needs which means I’ll need to drink some. I also need food, the production and distribution of which is the single largest burden on the water system by a mile. Agriculture constitutes 80-90% of the water used in the United States. A mere kg of red meat takes 15,415L of water to produce. I was a little crestfallen to learn that a single cheeseburger can outweigh using a high efficiency toilet for a year.[1][2]

To avoid this issue I consumed nothing but Soylent throughout the challenge. Soylent uses no meat or dairy and only 1.6L of water. To avoid dishes and their subsequent water usage I poured my Soylent one meal at a time in to a polystyrene cup, which takes less than 1L of water to make.[3] Soylent does not contain enough water in itself so I begrudgingly drank an additional 400ml of tap water.

If there’s anything as amazing as water it’s petroleum. My clothing and dishes take less water to make than they do to clean.

Dying to know the virtual water footprint of Soylent, we contracted an analyst at a Chicago think tank to run a study on the product. The complicated formula and numerous sources, as well as proprietary manufacturing and process information made this difficult and a bit imprecise. However, there was enough published data to end up with a conservative estimate. Rice protein was the biggest issue, given that rice is relatively water intensive to grow and is only about 10% protein by mass. Our rice processor claims they have a 0 carbon footprint and reclaim much of the water used but I couldn’t get many specifics. Not accounting for this we still ended up at 2030L per day of Soylent, which is about 50% of the virtual water footprint of the standard american diet (SAD), 4000L. Not bad. I bet we could lower it though.

Complete analysis here (criticism encouraged): https://s3.amazonaws.com/robrhinehart.com/soylent-water-footprint.xlsx

Final tally:

Direct water usage: 2L

Virtual water usage: 2031L

US Average:

Direct water usage: 1135L[4]

Virtual water usage: 7570L[5]

Conclusion

Water is the most popular beverage in the world, and still 20% of us are living without enough even to drink[6]. Our foods, our bodies, and our planet are mostly water, and yet, we are spoiling and wasting what the cosmos has made[7] at an unsustainable rate. I don’t expect anyone to live as I did during the challenge. Even I missed coffee and a hot shower. However, I do think it is important to be mindful of the network effects of one’s lifestyle. With water, as with most things, it is better to do more with less.

References

[1] http://www.ers.usda.gov/topics/farm-practices-management/irrigation-water-use.aspx

[2] http://www.waterfootprint.org/?page=files/Animal-products

[3] http://plasticfoodservicefacts.com/Life-Cycle-Inventory-Foodservice-Products

[4] http://www.epa.gov/watersense/our_water/water_use_today.html

[5] http://environment.nationalgeographic.com/environment/freshwater/change-the-course/water-footprint-calculator/

[6] http://news.bbc.co.uk/2/hi/science/nature/4787758.stm

[7] http://www.nature.com/news/earth-has-water-older-than-the-sun-1.16011

Footnote

It is possible that a substantial amount of earth’s water was synthesized by purple sulfur bacteria in the photosynthetic reaction

This means we could manufacture water out of sunlight, CO2 and flatulence. What if we scaled this up?

cross-posted here: http://blog.soylent.me/post/66807143901/this-morning-vices-brian-merchant-published-a in regards to the Vice / Motherboard piece here: http://www.youtube.com/watch?v=t8NCigh54jg

This morning, Vice’s Brian Merchant published a documentary video and accompanying article that outlined his experience living off Soylent exclusively for thirty days, several months ago.  Brian sought to recreate the scrupulous conditions of my initial Soylent test. Though the overall tone of the article was positive and he concluded that Soylent is something he will likely continue to use as a cheap, healthy, and easy staple meal, there were a few points that we feel require clarification. We encourage you to watch the entire video and read the article as well.

Merchant was able to run his 30-day test because of the Soylent Beta program, which consisted of small-batch Soylent production by hand, for limited distribution to friends and family, as well as journalists who expressed an interest in trying out early versions of Soylent.  The Beta program was incredibly useful for discovering weaknesses in our workflow.  At one point in the video, mold was discovered in one of Brian’s bags. This was due to inadequate packaging that we were testing at the time being punctured in shipping, allowing ambient moisture inside. This was one of several important discoveries from the beta program and prompted us to utilize more robust packaging. The beta program, along with our tenancy at the Oakland warehouse, concluded in early October, very soon after the Vice shoot wrapped. Our former landlord has been informed of our issues with the space.

We would also like to emphasize the fact that Soylent 1.0, the product for which we have accepted over $1.5M in preorders, will not be manufactured by hand by our executive team.  We have signed a purchase order with RFI Ingredients, a contract manufacturer with over 20 years of experience producing FDA-approved food products. Soylent is designed and regulated as a food, not a supplement. Their safety record is unimpeachable, which is one of the major reasons we chose to partner with them. To learn more about RFI’s certifications, please visit http://www.rfiingredients.com/certifications.asp.

We are grateful to be featured in a well-made piece regarding food security, design, and production. If one thing from the video is clear, food and people are always changing. The company and idea has come a long way in a matter of months, and we are devoted to producing the highest quality product possible.