Skip to content
Archive of posts filed under the Postgres category.

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, […]

JSONb First Looks

PostgreSQL 9.4 beta 3 on Linux — Simple JSON/JSONb compare, by Oleg — json: text storage, as is — jsonb: whitespace dissolved, no duplicate keys (last in wins), keys sorted SELECT ‘{“c”:0, “a”:2, “a”:1}’::json, ‘{“c”:0, “a”:2, “a”:1}’::jsonb; json | jsonb ————————-+—————— {“c”:0, “a”:2, “a”:1} | {“a”: 1, “c”: 0} (1 row) — emit JSON text […]

ACS 5yr Viz Processing

A systematic way to choose, extract and visualize data from the massive American Community Survey 5 Year census product is a challenge. I have written python code to ingest raw inputs into tables, and a small relational engine to handle the verbose naming. An extraction and visualization process is underway… something like the following: 0) […]

Numeric Stats on Bay Area Intersection Counts

In preparing for an upcoming Datathon, a column of data in PostgreSQL numeric format needed formatting for presentation. “Intersection Count” intersection_density_sqkm is a count of street intersections per unit area – a quick way to measure density of the built environment. A table of grid cells (covering the nine-county San Francisco Bay Area) that the […]

Distance to Nearest OSM Road

osm_ca_20=# SELECT   osm_id,   geometry < -> st_geomfromEWKT( ‘SRID=900913;POINT(-13145550 4045137)’ ) as the_distance FROM   osm_new_mainroads ORDER BY   geometry < -> st_geomfromEWKT( ‘SRID=900913;POINT(-13145550 4045137)’ ) limit 1; osm_id | the_distance ———-+—————— 59339590 | 268.205611425265 (1 row) UPDATE distance of all museums less than 30km away from a school, by school. Thanks to the geonames project for the […]

Five Colors for Stats

I am building some visualization layers in Geoserver from PostGIS, which requires .sld files (until Geoserver catches up with the CSS styling world – oh wait, look here). It is convenient to show ranges using ColorBrewer2 colors in a set of one plus five.. a color for NoValue, then what I call little0, little1, central […]

PostgreSQL 9.3 plus Hadoop File System

It seems that things just got a little bit more interesting with the release of Pg 9.3

Data Characterization and the Live

People may already know about the OSGeo Live project. Its a great base as a VM since a) it is stable and very well tested, and b) it has much software installed, but in a way that is transparent through install scripts, so customization is as straightforward as it gets.. I was faced with a […]

PostGIS 2.1 on Debian 7

Newly released PostGIS 2.1 rocks.. How can I install it on a fresh Debian 7 ‘wheezy’ to try it out? Here is a recipe that worked for me: — Final Results debian_version 7.1 PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu ———————————————– myrole=# select postgis_full_version(); POSTGIS=”2.1.1dev r11823″ GEOS=”3.4.2dev-CAPI-1.8.0 r3897″ PROJ=”Rel. 4.7.1, 23 September 2009″ GDAL=”GDAL 1.11dev, released 2013/04/13″ LIBXML=”2.8.0″ […]