Skip to content

GEOS 3.8 Benchmarks


newly minted PostGIS 3 / PostgreSQL 12 / GEOS 3.8 combo

PostgreSQL 12.0 (Ubuntu 12.0-2.pgdg18.04+1) on x86_64-pc-linux-gnu
  Ubuntu linux 4.15 x86_64    i7-2600 CPU @ 3.40GHz
  shared_buffers = 4096MB     work_mem=128MB
  
PostGIS 3.0.0 r17983;   Proj 4.9.3
database  geom  2D POLYGON  valid,simple,4326   3.1million rows
 * all times in milliseconds, lower is better

--  GEOS 3.7.1  postgresql-12-postgis-3_3.0.0+dfsg-2~exp1.pgdg18.04+1_amd64.deb
ST_IsValid(geom)         22023   21968   21976   21952
ST_PointOnSurface(geom)        53880   53668   53918
ST_Centroid(geom)        4610    4383   4384
ST_Buffer( geom,0.001)      98284   98111
ST_Union( geom, ST_Buffer(geom,0.001))      151677   151452

--  GEOS 3.8.1 r93be2e1d;  RelWithDebInfo
ST_IsValid(geom)         13761   13698   13734   13672
ST_PointOnSurface(geom)        4010   3929  3943
ST_Centroid(geom)        4106    4015   4018
ST_Buffer( geom,0.001)      68152   68387
ST_Union( geom, ST_Buffer(geom,0.001))      109546   109829

note: the graphic here shows only relative time between two runs
of the same operators, not absolute time between operators..

Compared to PostgreSQL 10 / PostGIS 2.4 / GEOS 3.6.2 only two years ago;
big evolution forward on several fronts.

GEOS on o13

Geometry Engine Open Source ecosystem overview on
OSGeoLive v13 (o13)   -LINK-   GEOS -LINK-

  

Buzzwords on Advanced Methods

osgeo

On the OSGeoJapan-discuss mailing list regarding FOSS4G 2019 KOBE.KANSAI :

今年は、Geo-AIのテーマにマッチした、ディープラーニングの勉強が初歩から体験できるコースが2つあります!

  translation: this year, there are two courses that match the theme of Geo-AI, where you can experience deep learning from the beginning!

A OSGeoJapan wiki page says:    今年のテーマは「Geo-AI」です。
    translation: this year’s theme is “Geo-AI”.

Geo-AI a closer look – “geo” is easy, what about AI?

a working definition of AI*:
 Artificial Intelligence is the theory and development of computer systems able to perform tasks which previously required human intelligence.  AI is a broad field, of which Machine Learning ML is a sub-category.

ds_ml_ai

Data Science overlaps Artificial Intelligence, Machine Learning exists in that overlap.

When people speak about Machine Learning these days, they are often speaking of Deep Learning, a subset of Artificial Neural Networks NN.. networks consisting of connected, simple processors called “neurons”  wikipedia -LINK-nn -LINK-dl This blog post -LINK- describes NN training methods, including “weakly supervised” and “transfer learning.”

Since 2009, supervised deep NN‘s have won many awards in international pattern recognition competitions, achieving better-than-human results in some limited domains. — Schmidhuber 2015 Survey of Deep Learning

Deep Learning is especially effective on data sets like images and sound, and improves with the amount of training data available. Deep Learning is not new, but what is new are the volumes of available labelled training data, and the capacity to compute on them. Many Deep Learning programming libraries are in Python, and many libraries run natively on a GPU. -LINK-sklearn

Data Science uses scientific methods, processes, algorithms and systems to extract knowledge and insights from structured and unstructured data. wikipedia -LINK-ds

Machine Learning as a subject, is the study and application of algorithms and statistical models that computers use to execute some tasks without explicit instruction; sometimes known as predictive analytics. -LINK-ml

* ai-and-the-global-economy-mark-carney-2018

Jupyter on o13

OSGeoLive v13 (o13)   -LINK-

jupyter o13

the Jupyter eco-system installed and integrated on osgeolive13

OSS License Strategy

this post is an edited, public version of a DRAFT internal discussion

Preamble
This blog post is intended to bring together topics, facts, considerations and other useful information pertaining to upcoming licensing decisions on full-stack software. It is assumed that all license decisions must be made prior to hand-off of the works to the contract originator. All license decisions must exist in writing.

As noted below, some person or entity must have signature authority to enact these license decisions. Subsequently in the project life cycle, it is recommended that a project steering committee be formed with the authority to make or reject further changes.

note:
 · not all components must have the same license
 · dual licensing of GPL and non-GPL is possible (more on this later)
 · this is a data-driven project – data (a lot) is the origin and outputs data sets require their own licenses in any event because GPL and other OSS licenses apply to source code, not data
 · the means to transform data into a base load, into a DOCUMENT, and read the outputs are code, and must be licensed

The deliverable to the contract originator is “—-” (confirm)

The Open Source Movement

Major software initiatives using an Open Source model have flourished in an unprecedented success over the last 15 years, with names like GNU, Linux, BSD Unix, Apache and Mozilla. If the ability of software developers to contribute to common source code, and to form functional communities focused on continuous improvements to their projects is the engine, then the application of standardized licensing to source code (and therefore to new contributions) is what keeps that engine on track.

Open Source by Definition

      redistribution is permitted
      source code is available
      licensee may make derived works

see OSI

 I  What is in a license?

First Principles – Copyright

copyright is held by the party who commissioned the work
passing through the contractor to the contract originator

Rights of the User of Open Source Software

Distribution Rights
 – who can copy the code (possession)
 – who can deploy the code (run an instance of the software)
 – what rights do users have with the copier/deployer (right to obtain source)

Modification Rights
 – who can modify this code
 – who can distribute their modifications, under what terms?
 – what responsibilities do distributors of modifications have ?

Generational Limitation
Once a copy of the source code has been modified, what limitations apply to the license for distributed, changed copies? Specifically, can code be changed from an open license to a less-open license (“go closed”), or not? Must the brand / origin be displayed ? Is a party that modifies the project required to publish their changes ?

Source Code License Parts

The licensor asserts explicitly or implicitly, that they have the right to license this source code

licensor states warrenties and disclaimers
warranty of merchantability
warranty of fitness for a particular purpose
warranty against infringement
either grants protection against patent infringement or not

licensor explicitly states rights granted to the licensee
including what grants the licensee may make to further licensees


 II  Source Code Licenses

We do not want to define a custom license – there are enough to choose from!

Google on Licenses Blog  ·  OSI The Open Source Initiative

Mozilla Public License – http://www.mozilla.org/MPL/

“The MPL fills a useful space in the spectrum of free and open source software licenses, sitting between the Apache license, which does not require modifications to be shared, and the GNU family of licenses, which requires modifications to be shared under a much broader set of circumstances than the MPL.” – http://www.mozilla.org/MPL/2.0/FAQ.html

(from Wikipedia)
… The MPL allows covered source code to be mixed with other
files under a different, even proprietary license. However, code files licensed under
the MPL must remain under the MPL and freely available in source form. This makes
the MPL a compromise between the MIT or BSD licenses, which permit all derived works
to be relicensed as proprietary, and the GPL, which requires the whole of a derived work, even new components, to remain under the GPL. By allowing proprietary modules in derived projects while requiring core files to remain open source, the MPL is designed to motivate both businesses and the open-source community to help develop core software.

The rights granted by the Mozilla Public License are primarily defined as passing from “contributers,” who create or modify source code, to the licensee. In the absence of patents, MPL-licensed code can be freely used, altered, and redistributed. Versions with patented code can still be used, transferred, and even sold, but cannot be altered withoutspecial permission. In addition, the MPL does not grant the licensee any rights to a contributor’s trademarks.

To fulfill the terms of the MPL, the licensee must meet certain “responsibilities,”
mostly concerning the distribution of licensed software. The licensee must ensure access to or provide all source code files covered by the MPL, even if the software is offered as an executable or combined with other code under a proprietary license. The one exception to covered files remaining under the MPL occurs when they are combined with code under the GPL, Lesser GPL (LGPL), or Affero GPL (AGPL). In this case, the creator of the combined software can choose to provide the entire work under the stricter GPL-based licenses.

The MPL has been approved as both a free software license (albeit one with a
weak copyleft) by the Free Software Foundation[3] and an open-source software license by the Open Source Initiative.[4]

[3] “Mozilla Public License (MPL) version 2.0”. Various Licenses and Comments about Them.
Free Software Foundation. Retrieved 2012-01-03.
[4] “Open Source Licenses”. Open Source Initiative. Retrieved 2012-01-07.

MIT, BSD, Apache Licenses

“… allow code to be used in proprietary software and do not require that open
source versions of the code be distributed. Code created under these licenses, or
derived from such code, may go ‘closed’ and developments can be made under that
proprietary license, which are lost to the open source community.

It is worth considering the Apache v2 license, or the Academic Free License here, since they are more recent that the original BSD and MIT/X, and contain some refinements

Non-lawyer Brian says :
These licenses DO NOT prevent a competitor to FIRM from taking a complete project, making source code changes that are not published, and advertising the product as their own, as long as certain notices are retained within the code.

“In short, research-style licenses, like the BSD and MIT Licenses, are ideal for
situations in which you want wide deployment of your ideas and do not care whether
this results in open source software or proprietary software.”

Apache Software License (ASL)
see ArsTechnica on Google GPL ASL HERE

“ASL, which is widely used in the open-source software community and has been
approved by the Open Source Initiative, is a permissive license that is conducive to
commercial development and proprietary redistribution. Code that is distributed
under the ASL and other permissive licenses can be integrated into closed-source
proprietary products and redistributed under a broad variety of other terms.
Unlike permissive open-source licenses, “copyleft” licenses (such as the GPL)
generally impose restrictions on redistribution of code in order to ensure that
modifications and derivatives are kept open and distributed under similar terms.

Permissive licenses like the ASL and BSD license are preferred by many companies
because such licenses make it possible to use open-source software code without
having to turn proprietary enhancements back over to the open source software
community. These licenses encourage commercial adoption of open-source software
because they make it possible for companies to profit from investing in enhancements
made to existing open-source software solutions.”

OSI 4
The license must explicitly permit distribution of software built from modified
source code. The license may require derived works to carry a different name or
version number from the original software.

GPL, GPLv2, LGPL

These licenses are the most prescriptive on the rights of those that modify and
adopt the project software.

GPL

Software can be distributed and modified without additional permission of the licensor. This imposes a mirror-image restriction on the licensee: while the licensee has free access to the licensed work, the licensee must distribute any derivative works subject to the same limitations and restrictions as the licensed work. Derivative works must be licensed under the GPL and be subject to all of its conditions.

see GNU Software -LINK-

GPLv3

Introduced in 2007 – GPL Definitions -LINK- An essay discusses the differences in GPLv3. -LINK- The primary goal appears to prevent certain kinds of modification (“Tivoization”)

Brian says: I do not believe that any open source license can restrict the use cases of the software, including commercial use. So there is no such thing as “GPLv3 non-commercial” see-LINK- ; “No OSI-certified Open Source license (or FSF-approved Free Software license) will restrict commercial use…” David Thornley, 30dec10

GPLv2+ is modified v2 to be compatible with v3

LGPL

A key difference in the LGPL is that is does not restrict the ability of a library to “link” to othe libraries.. (referring to compiled-code architecture) The GPL wording is such that a GPL licensed code project cannot be linked to non-GPL code, but an LGPL library can.. Stallman discusses an implication of this here
see GNU Why not LGPL -LINK-

Python Software Foundation License

The license for python, itself. -LINK-

from wikipedia:
The Python Software Foundation License (PSFL) is a BSD-style, permissive free software license which is compatible with the GNU General Public License (GPL). Its primary use is for distribution of the Python project software. Unlike the GPL the Python license is not a copyleft license, and allows modifications to the source code, as well as the construction of derivative works, without making the code open-source. The PSFL is listed as approved on both FSF’s approved licenses list, and OSI’s approved licenses list.

Source code written in python can not be hidden while it is running. -LINK-


 III  Data Licenses

This topic remains contentious and is still evolving rapidly. Drivers of change include new technology, new social norms and new markets.

Creative Commons
A quick web tool to assist in choosing a license -LINK-

Example — given that modifications are permissible as long as they are published, and

if commerical use of the datasets are permissable
Creative Commons Attribution-ShareAlike 3.0 United States License -LINK-

or, if commercial use of the datasets is not permissable
Creative Commons Attribution-NonCommercial-ShareAlike 3.0 United States License -LINK-

An introduction to an open data in general HERE

Open Data Commons.org
The Open Data Commons appears to be expanding -LINK-. Their
license appears to be an approximation of the GPL for data. HERE


 IV  Components Inventory

GUI
The sketching GUI will immediately be identified by some as “the product.”
Internal architecture and components are not visible to those without knowledge of software tech.

However, the GUI as written is only one possible interface to the underlying engines, by design.

GUI includes some data, for example, visual style definitions in Geoserver, but does not include base-load and DOCUMENT definitions.

Some GUI-specific data can be repurposed in other implementations e.g. geoserver style definitions.

Framework
The technology mix used to initiate, process and retrieve. The backbone of the project, to which all parts are connected.

DOCUMENTS are data in a certain form. Currently the only practical way to create DOCUMENTS is through the GUI, but a DOCUMENT in the defined form is NOT part of the GUI, but part of the framework.

Engines
Engines are discrete components, and may carry a separate license. An example is a specialized CALC engine that is re-written for performance, or to express a different model. Engines may perform only on base data, only on DOCUMENT data, or both.

Base Load Data
The ability to load base data – “base load scripts” must have a license
The data products must have a license

Documentation
Documentation is a “value add” and can be considered its own component. Docs can be in the form of “help” with context, often embedded, or as a book, to be read, studied, referred to, etc..

Components

Each of the above are written in programming languages which are different. The most stark example is the Web GUI, written in Javascript and the Django python framework.

The Framework is the collection of python and Postgresql/PostGIS SQL.

Engines are particular, discrete expressions of models,
currently in python and SQL but could be written in even higher performance code.

Base Load scripts.. the ability to take public and private data sets, well defined, and transform them into the particular database tables operated upon by the Framework.


 IV  Strategy Discussion for Choice of License

Brian’s Thoughts on “Why”

there are several axiis upon which to measure characteristics and likely trajectories of OSS. The following names some of those and asks, what are the goals of the project along these axiis ?

What are the goals of FIRM? It may be said that “if you want to hack, try it out yourself, if you want to initiate a paid study, contact FIRM”

Community versus Poker
Poker is a game where all participants are known to each other as fighting for their own best interests at all times. Paradoxically it does form communities around the activities, but the goals are clearly self-serving. There is mostly “poker” in business relationships. Community on the other hand, implies considerations of group benefits at all times, perhaps expectations of individuals to take losses for the benefit of the whole.
Local government is allegedly a community office. Particularly high quality or enabling software is known to draw community. Science is sometimes termed community, although paradoxically, science is often highly competitive and therfore encourages self-serving decisions.

This project spreads both technical capacity, and brand. Control of the brand, with implied quality, is a crucial consideration in the permissiveness of the modification license. The license may state that derived works MUST NOT carry the original brand, or MUST, there is an argument for either.

Speed of Adoption, Size of Adoption
How many organizations might use this tool set, now or in a few years from now?
At what rates are first contact, first trial and first real usage desirable
or even possible ?

There are well-understood characteristics of the patterns of new product adoption
see Wikipedia on Diffusion of Innovation HERE

Encourage and enable adoption versus hold back powerful competitors
In a networked environment, and a high quality product of specialized interest,
the dynamics change quite a bit. Technical consulting can be very difficult
endeavor, and for the successful, holds importance and money. While keeping a goal of
new comers enabled by tech to do great analysis, the fact is that a small number of
powerful technical consulting groups may find this package useful to compete with the
others, to provide services to those unable or uninterested in doing it themselves.

This package is so enabling, that it could be said to further the state of the art
in the field.

Data Driven Analysis
this environment is data driven, that is, extensive and diverse data is required to
start, and the output is largely data. Quality and quantity of data is immediately an
issue. Is the goal to spread the technology widely, while playing poker with the data?
Is it to distribute the data widely, and play poker with the client relationships ?

There is an unending desire by knowledgeable parties for data, both inputs and outputs. The work to clean, organize and report the data is enabled by the software, but it is not free – resources must come from somewhere.. The Federal, State and County governments are huge sources of raw data, but it is not enough.

What is the desired net effect of the release of this project with respect to the data, and the ability to produce it, integrate new sources, produce new reports? This topic is almost as large as the Framework and GUI itself – consider that the Framework and GUI are not easily written, but far fewer parties would have interest in modifications; while the data is of interest to virtually all who would be interested in this project.

End Notes
The decision to mark software work products with an Open Source license, and how to handle data associated with the project and its use, is complicated and deserves attention. Here we have reviewed a collection of the most popular Open Source licenses. Data must be licensed separately from code, and some attention is shown to emerging data licenses. The motivation to write this is that a full-stack software project is considered for licensing decisions. Customers, community motivations and strategy are briefly discussed. With this short post, several large and different approaches can be seen, compared and contrasted.

references
https://code.gov/about/overview/introduction
EU eGovernment Services OSS-procurement-guideline HERE

Climate Aware 2019

OSGeo.org collaborated to show this banner prominently
on September 20th, 2019

 
climateAware2019
 
 

New Year 2019

.. the hands of time move slowly sometimes. I just got this email:

Dear Dav Clark, Aaron Culich, Brian M Hamlin, and Ryan Lovett,

Your paper from the 13th Python in Science Conference titled “BCE: Berkeley’s Common Scientific Compute Environment for Research and Education” has been assigned the DOI 10.25080/Majora-14bd3278-002. Please use this DOI when providing citations for your paper, following the guidelines here: https://www.crossref.org/display-guidelines/#full

Yours,
Dillon Niederhut, on behalf of:
Stéfan van der Walt
James Bergstra

BCE_SciPy_2014

AGU Goes to NOL

best to all in the next week

e.g. Oak Ridge National Labs research group LINK

NAIP CA 2016 Processing

Running 11,000 DOQQs through a processing pipeline – so far, so good !
 

 

 
Details at 0.6 meters per pixel:
 
 

Prioritize LA, for Today

auth_buildings=# 
  update doqq_processing as a 
    set priority=6 from tl_2016_us_county b, naip_3_16_1_1_ca c 
      where 
        b.statefp='06' and b.countyfp='037' and 
        st_intersects(b.geom, c.geom) and a.doqqid=c.gid;

OSM Fresno

In Openstreetmap US, California Fresno area, a controversial [0] series of imports of legal property records (aka PARCEL) are mixed in with other POLYGONS. Many various POLYGON in Fresno now share the tag landuse=residential, both the PARCEL legal records and real building footprint POLYGON, as well as various others. After reviewing the wiki talk page, relevant discussions, and discussing online briefly, this post looks at the OSM context; estimates the extent of these imports by examining similar, nearby areas; compares the OSM records to actual current PARCEL records; proposes a deletion criteria and finally, examines the extent of the proposed deletion.

[0] changeset/26356220 * changeset/26357831
OSM Wiki on Parcels -LINK- -TALK-


Context: Fresno County is big — but the real-world residential areas are confined almost entirely to the City of Fresno.

fresno_ccd_context

fresno_landuse_context

nlcd_06_legend
NLCD 06 mrlc.gov


Q. What tag 'landuse' values are present in County Subdivision Fresno?

 151670 | residential
   6644 | commercial
   6463 | NULL
   3859 | industrial
    706 | farm
    574 | vineyard
    498 | orchard
    453 | meadow
    109 | garages

less than 100: 
  basin,farmyard,recreation_ground,grass,farmland,religious,cemetery,retail,
  quarry,reservoir,railway,landfill,construction,institutional

Next, expand the query to the entire five-county region

Q. What tag 'landuse' values are present in the five county area 
-- Kings, Madera, Tulare, Kern, Fresno

 207902 | NULL
 203000 | residential
  11697 | commercial
   7054 | farm
   6679 | orchard
   5941 | industrial
   5251 | vineyard
   5029 | meadow
   2475 | farmland
   1980 | farmyard
    885 | grass
    
less than 300: 
   garages,cemetery,recreation_ground,basin,quarry,reservoir,religious,retail
  forest,scrub,military,landfill,railway,pond,greenhouse_horticulture,construction

So, 150,000 of the 200,000 landuse=residential tagged POLYGONs in a five-county area, are in just the Fresno City CCD.

Attribution On inspection, a large number of likely PARCEL records in Fresno, carry an attribution tag with one of several recognizable values: Caltrans (4), FMMP (3) and Fresno_County_GIS.

example data:
 "type"=>"multipolygon", "landuse"=>"vineyard", "attribution"=>"Fresno_County_GIS"
 "crop"=>"field_cropland", "type"=>"multipolygon", "landuse"=>"farm", "attribution"=>"Fresno_County_GIS"
 "crop"=>"field_cropland", "type"=>"multipolygon", "landuse"=>"farm", "attribution"=>"Fresno_County_GIS"
 "crop"=>"native_pasture", "type"=>"multipolygon", "landuse"=>"meadow", "attribution"=>"Fresno_County_GIS"
 "crop"=>"native_pasture", "type"=>"multipolygon", "landuse"=>"meadow", "attribution"=>"Fresno_County_GIS"
 "type"=>"multipolygon", "landuse"=>"vineyard", "attribution"=>"Fresno_County_GIS"
 "crop"=>"field_cropland", "type"=>"multipolygon", "landuse"=>"farm", "attribution"=>"Fresno_County_GIS"
 "type"=>"multipolygon", "landuse"=>"vineyard", "attribution"=>"Fresno_County_GIS"
 "crop"=>"field_cropland", "type"=>"multipolygon", "landuse"=>"farm", "attribution"=>"Fresno_County_GIS"
 "type"=>"multipolygon", "trees"=>"orange_trees", "landuse"=>"orchard", "attribution"=>"Fresno_County_GIS"
 "type"=>"multipolygon", "landuse"=>"residential", "lot_type"=>"single family residential properties", "other_use"=>"S", "attribution"=>"Fresno_County_GIS", "primary_use"=>"000", "secondary_use"=>"VLM"
 "type"=>"multipolygon", "wood"=>"mixed", "landuse"=>"farm", "natural"=>"wood", "attribution"=>"Fresno_County_GIS"
 "type"=>"multipolygon", "landuse"=>"vineyard", "attribution"=>"Fresno_County_GIS"
 "type"=>"multipolygon", "landuse"=>"orchard", "attribution"=>"Fresno_County_GIS"

Detailed counts in Fresno County and the Fresno CCD

-- Fresno County:  geoid  06019 / tl_2016_us_county
241860 - all multipolygons
231624 - tag landuse
196017 - tag landuse = 'residential'
230685 - tag 'attribution'
230612 - tag 'attribution' ~* 'GIS'
----------------------------------------------------
-- Fresno CCD:   geoid   0601991080
171200 - all multipolygons
164737 - tag landuse
151670 - tag landuse = 'residential'
166163 - tag 'attribution'
166147 - tag 'attribution' ~* 'GIS'
----------------------------------------------------
-- Fresno County outside of Fresno CCD (derived)
 70660 - all multipolygons   (241860 - 171200)
 66887 - tag landuse         (231624 - 164737)
 44347 - tag landuse = 'residential'  (196017 - 151670)
 64465 - tag 'attribution' ~* 'GIS'   (230612 - 166147)
Qry - count the occurances of attribution 'GIS'   AND
  landuse = 'residential'; area Fresno County, by cousub
           name           | count  
--------------------------+--------
 Caruthers-Raisin City    |   1400
 Fresno                   | 150681
 Kerman                   |   4093
 Reedley                  |   5967
 Mendota                  |   1779
 San Joaquin-Tranquillity |   1030
 Coalinga                 |   2528
 Firebaugh                |   1152
 Orange Cove              |   1579
 Kingsburg                |   3557
 Huron                    |     87
 Fowler                   |   1527
 Sierra                   |    963
 Parlier-Del Rey          |   2633
 Sanger                   |   7796
 Riverdale                |   1208
 Laton                    |    599
 Selma                    |   6221


Compare current parcel data (670 records) to OSM multipolygon with tag landuse=residential (350 records), in a sample Fresno blockgroup ('060190045051')
BBOX="-119.7994,36.8084,-119.7903,36.8229"

bg_fresno_0

m_p_fresno_bg_0

p_fresno_bg_0

m_fresno_bg_0


This looks promising: take all OSM multipolygons marked landuse=residential, then remove WHERE tag attribution exists AND tag building does not exist …

osm_fresnocs_mpoly_landuse_res_attr0

osm_fresnocs_mpoly_landuse_res1

osm_fresnocs_mpoly_landuse_res0

Some Links:
https://help.github.com/articles/mapping-geojson-files-on-github/

fresno_parcels_osm_ex1

-- County of Fresno, subdivision Fresno geoid = '0601991080'
--  multipolygons m is a raw dot-pbf import of OSM

-- Qry - Show all landuse tags and a count of occurances
--   area: Fresno CCD
--
select count(*), all_tags -> 'landuse'  
FROM multipolygons m,  tl_2016_06_cousub cs
WHERE
    cs.geoid = '0601991080'  AND
    st_intersects( m.wkb_geometry, cs.geom) 
GROUP BY all_tags -> 'landuse' 
ORDER BY  all_tags -> 'landuse';

/* count |   landuse tag    
--------+-------------------
     48 | basin
     11 | cemetery
   6644 | commercial
      1 | construction
    706 | farm
     24 | farmland
     43 | farmyard
    109 | garages
     28 | grass
   3859 | industrial
      1 | institutional
      1 | landfill
    453 | meadow
    498 | orchard
      2 | quarry
      1 | railway
     37 | recreation_ground
     19 | religious
      2 | reservoir
 151670 | residential
      6 | retail
    574 | vineyard
   6463 | 
*/

--=====================================================
--
--  Kern County - FIPS 029
--  Fresno County - FIPS 019
--

-- Qry - Show CCDs and a count of tag landuse = 'residential'
--   area: Fresno County, Kern County
--
select count(*), (cs.geoid, cs.name, cs.countyfp)
FROM multipolygons m, tl_2016_06_cousub cs
WHERE
    cs.countyfp IN ( '019', '029' )  AND
    all_tags -> 'landuse' = 'residential'  AND
    st_intersects( m.wkb_geometry, cs.geom) 
GROUP BY  (cs.geoid, cs.name, cs.countyfp)
ORDER BY   (cs.geoid, cs.name, cs.countyfp) ;

/*
   1408 | (0601990390,"Caruthers-Raisin City",019)
   2558 | (0601990530,Coalinga,019)
   1170 | (0601991000,Firebaugh,019)
   1541 | (0601991060,Fowler,019)
 151670 | (0601991080,Fresno,019)
           ...............
     60 | (0602990130,Arvin-Lamont,029)
    724 | (0602990180,Bakersfield,029)
          ................
   1096 | (0602993320,Tehachapi,029)
    188 | (0602993570,Wasco,029)
    715 | (0602993635,"West Kern",029)
*/

--==================================================
--
-- Qry - Show all landuse tags and a count of occurances
--   area: Fresno County, Kern County
----
select count(*), all_tags -> 'landuse'  
FROM multipolygons m, tl_2016_06_cousub cs
WHERE
    cs.countyfp IN ( '019', '029' )  AND
    st_intersects( m.wkb_geometry, cs.geom) 
GROUP BY all_tags -> 'landuse' 
ORDER BY  all_tags -> 'landuse';

/* count |  landuse tag        
--------+-------------------------
      1 | aerodrome
     83 | basin
     54 | cemetery
  11107 | commercial
      1 | conservation
      1 | construction
   5160 | farm
   2426 | farmland
   1034 | farmyard
      5 | forest
    268 | garages
    885 | grass
      1 | greenhouse_horticulture
   5830 | industrial
      1 | institutional
      3 | landfill
   3318 | meadow
      4 | military
   6519 | orchard
     45 | quarry
      3 | railway
     86 | recreation_ground
     19 | religious
     19 | reservoir
 201341 | residential
     13 | retail
     16 | scrub
   5225 | vineyard
 203195 | 
*/

--===================================================
--
-- Qry - Show all landuse tags and a count of occurances
--   area: Bakersfield city, Kern County (similar to Fresno city )
--

select count(*), all_tags -> 'landuse'  
FROM multipolygons m, tl_2016_06_place p
WHERE
    p.namelsad = 'Bakersfield city'  AND
    st_intersects( m.wkb_geometry, p.geom) 
GROUP BY all_tags -> 'landuse' 
ORDER BY  all_tags -> 'landuse';

/* count |   landuse tag
--------+-------------------
      4 | cemetery
    687 | commercial
     78 | farm
      3 | farmland
     23 | farmyard
    836 | grass
    261 | industrial
     52 | meadow
     18 | orchard
      1 | railway
      8 | recreation_ground
    710 | residential
     16 | scrub
 119669 | 
*/

--===================================================
--
-- Qry - Show all landuse tags and a count of occurances
--   area: Fresno City
--
--
select count(*), all_tags -> 'landuse'  
FROM multipolygons m, tl_2016_06_place p
WHERE
    p.namelsad = 'Fresno city'  AND
    st_intersects( m.wkb_geometry, p.geom) 
GROUP BY all_tags -> 'landuse' 
ORDER BY  all_tags -> 'landuse';

/* count |   landuse tag
--------+-------------------
     25 | basin
      5 | cemetery
   5523 | commercial
      1 | construction
     67 | farm
      4 | farmland
      4 | farmyard
     65 | garages
     12 | grass
   2410 | industrial
      1 | landfill
    268 | meadow
     45 | orchard
      1 | railway
     26 | recreation_ground
     19 | religious
      1 | reservoir
 105930 | residential
      5 | retail
     15 | vineyard
   5192 | 
*/