Category: SQL

Database, PostGIS, PostgreSQL, SQL

ST_Buffer for those who want really want all buffer features in geography

My previous post on improving buffering geography in PostGIS has three disadvantages: It doesn’t do this at a low enough level to automatically use the best available local coordinate system. This leaves it to the user to choose the best available local coordinate system. I should fix this (but I probably won’t just now…). It uses a different function name than you otherwise use for buffering. … Continue reading ST_Buffer for those who want really want all buffer features in geography

Unknown's avatarsmathermather1 Comment
Database, PostGIS, PostgreSQL, SQL

ST_Buffer for those who want really round geographic circles

A little functionality request from @antoniolocandro on twitter: ST_buffer for geography has no option for quad_segs, alternative so buffer looks nice not jagged? cc @smathermather — Antonio Locandro (@antoniolocandro) January 8, 2016 Ask and ye might receive. Let’s build a function that will take your input geography, how far you want to buffer (in local coordinates) number of segments you want in a quarter, and … Continue reading ST_Buffer for those who want really round geographic circles

Unknown's avatarsmathermather6 Comments
Analysis, Database, PostGIS, PostgreSQL, SQL

ST_ApproximateMedialAxis(geom)

Trying out PostGIS 2.2’s ST_ApproximateMedialAxis capabilities today. I’m going to use it to label parcels. So here is my parcel fabric: And here’s what the skeleton of that fabric looks like: It get’s pretty interesting where the parcels are more complicated: more: Oh, the code you say? Well, it couldn’t be much easier: SELECT gid, ST_ApproximateMedialAxis(geom) AS geom FROM cuy_parcel_2015; Or the full version for … Continue reading ST_ApproximateMedialAxis(geom)

Unknown's avatarsmathermather2 Comments
Database, Database Optimization, Fulcrum, PostGIS, PostgreSQL, SQL

Normalizing tables in PostgreSQL: experiments with Fulcrum

Fulcrum by Spatial Networks is an interesting and very useful hosted service for collecting geo data in the field with whatever smartphone or tablet you already own.  It works well, so long as you just want to collect points, and the field building capabilities are quite extraordinary. Also, for many applications, it’s cheaper than custom solutions, so it hits that sweet spot well. If you’ve … Continue reading Normalizing tables in PostgreSQL: experiments with Fulcrum

Unknown's avatarsmathermatherLeave a comment
3D, Cartography, Database, Drone, PostGIS, PostgreSQL, SQL, UAS

Airspace — A deep rabbit hole

In previous maps we looked at Class B, C, and D airspace. Let’s add in Class E0 and E5… (not yet in 3D): (Map tiles by Stamen Design, under CC BY 3.0. Data by OpenStreetMap, under ODbL) Previous posts: https://smathermather.wordpress.com/2014/10/25/airspace-is-complicated-and-so-i-abuse-postgis-once-again/ and https://smathermather.wordpress.com/2014/10/25/airspace-is-complicated-and-so-i-abuse-postgis-once-again-reprise/ Continue reading Airspace — A deep rabbit hole

Unknown's avatarsmathermatherLeave a comment
3D, Cartography, Database, Drone, PostGIS, PostgreSQL, SQL, UAS

Airspace is complicated — and so I abuse PostGIS once again — Reprise…

In the previous post: https://smathermather.wordpress.com/2014/10/25/airspace-is-complicated-and-so-i-abuse-postgis-once-again/ we explore the 3D shape and complexity of controlled airspace. Now here’s the rest of the code. We’ll add our affine transformation ala Seth Fitsimmons: And integrate that into our original function: And voila! Let’s take a look at Washington, DC and surrounds, another nice complicated example: And again with map tiles by Stamen Design, under CC BY 3.0. Data … Continue reading Airspace is complicated — and so I abuse PostGIS once again — Reprise…

Unknown's avatarsmathermather3 Comments
3D, Cartography, Database, Drone, PostGIS, PostgreSQL, SQL, UAS

Airspace is complicated — and so I abuse PostGIS once again

Let’s ignore for a moment the drone hobbiest / enthusiast. What is the shape of airspace for airplanes and commercial and government unmanned aircraft flying under Certificates of Authorization, and how can we visualize it? Thanks to Anita in the last post, we have the Class B,C,D,E Airspace Shape Files which helps us define the overall shape of controlled airspace. But, these are 3D things. … Continue reading Airspace is complicated — and so I abuse PostGIS once again

Unknown's avatarsmathermather4 Comments
Analysis, Database, pgRouting, PostGIS, PostgreSQL, SQL

Drivetime analyses, pgRouting

We’ve got some quick and dirty pgRouting-based code up on github. I say quick and dirty because it directly references the table names in both of the functions. I hope to fix this in the future. The objective with this code is to input a point, use a nearest neighbor search to find the nearest intersection, and from that calculate a drive time alpha shape. … Continue reading Drivetime analyses, pgRouting

Unknown's avatarsmathermather4 Comments
Analysis, Database, PostGIS, PostgreSQL, QGIS, SQL, TileStache

Plugin-free QGIS TMS tiles via GDAL

Want to load your favorite tiles into QGIS? How about a plugin-free QGIS TMS tiles via GDAL: http://www.3liz.com/blog/rldhont/index.php?post/2012/07/17/OpenStreetMap-Tiles-in-QGIS Really awesome… . Needs but one change: epsg:900913 should be epsg:3857 or QGIS (GDAL?) throws an error. Presumably you could also define epsg:900913 in some config file, but barring that create an XML file as follows, and load as a raster in QGIS: Now I can use … Continue reading Plugin-free QGIS TMS tiles via GDAL

Unknown's avatarsmathermather1 Comment
Analysis, Database, PostGIS, PostgreSQL, SQL

Cleaning animal tracking data — throwing away extra points

Much the problem of the modern era– too much data, uneven data, and yet, should we keep it all? Here’s the problem space: attach GPS collar to a coyote, send that data home, and you have a recipe for gleaning a lot of information about the movement of that animal across the landscape. In order to maximize the data collected while also maximizing the battery … Continue reading Cleaning animal tracking data — throwing away extra points

Unknown's avatarsmathermather4 Comments