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