Category: PostGIS

Database, PostGIS, SQL

Cleaning PostGIS Geometries — Revised

In a previous post: “Another short post. Got self intersecting geometries? Want to fix them inside PostGIS? Me too. Fortunately, someone has already written the code, so you and I don’t have to: http://linfiniti.com/2010/07/cleaning-geometries-inside-postgis/ Now, let’s see how long it takes to run on my 760,000 record landcover table… .”   As usual, there’s a better way.  With PostGIS 2.0, you can (will be able … Continue reading Cleaning PostGIS Geometries — Revised

Unknown's avatarsmathermatherLeave a comment
Database, GeoServer, OpenLayers, PostGIS, SQL

PostgreSQL Views within GeoServer, GetFeatureInfo with Freemarker Templates, etc.

GeoServer now has the ability to consume database views from PostGIS, not just raw tables. I say it “now” has that ability– I think that came online with GeoServer 2.x series, but I’m just “now” starting to take advantage of it. You can also create views on the fly within GeoServer, but I prefer to apply the logic at the database level, just in case … Continue reading PostgreSQL Views within GeoServer, GetFeatureInfo with Freemarker Templates, etc.

Unknown's avatarsmathermather5 Comments
Database, Database Optimization, LiDAR Shootout, PostGIS, SQL

Dialog– What qualifies as a benchmark? Part 1

Normally, my blog is a bit of a monologue.  It’s not a bad thing, but can be a little lonely.  Every now and then I get a great (and often doubtful) comments, which enhances things considerably. What follows is some dialog about the LiDAR shootout series, largely between Etienne and Pierre, posted with their permission: Pierre: “Etienne, Stephen, “I really appreciate the benchmark work you … Continue reading Dialog– What qualifies as a benchmark? Part 1

Unknown's avatarsmathermatherLeave a comment
Database, Database Optimization, LiDAR Shootout, PostGIS, SQL

LiDAR Shootout! — New Chart, Final Results

In reviewing the final numbers and charts from Etienne and Pierre, above are the results we see.  The only revision is a moderate increase in speed for the PG Raster query. Final results in speed for lastools– ~350,000 points per second.  In other words– off-the-charts fast.  And the initial RMSE of ~25 feet was a mistake– it is probably closer to 0.2 feet. Stay tuned … Continue reading LiDAR Shootout! — New Chart, Final Results

Unknown's avatarsmathermatherLeave a comment
Database, Database Optimization, LiDAR Shootout, PostGIS, SQL

LiDAR Shootout!

For a couple of months now I’ve been corresponding with Etienne Racine and Pierre Racine out of Montreal Laval University in Quebec City.  They decided to take on the problem of finding the speed and accuracy of a number of different techniques for extracting canopy height from LiDAR data.  They have been kind enough to allow me to post the results here.  This will be … Continue reading LiDAR Shootout!

Unknown's avatarsmathermather9 Comments
Database, Database Optimization, GeoServer, PostGIS

Contours– Symbolized from a single table

In a previous post, I restructured the contour data for display in GeoServer, e.g.: UPDATE base.contours_2 SET div_10 = CAST( contours_2.elevation % 10 AS BOOLEAN ), div_20 = CAST( contours_2.elevation % 20 AS BOOLEAN ), div_50 = CAST( contours_2.elevation % 50 AS BOOLEAN ), div_100 = CAST( contours_2.elevation % 100 AS BOOLEAN ), div_250 = CAST( contours_2.elevation % 250 AS BOOLEAN ); The SLD styling … Continue reading Contours– Symbolized from a single table

Unknown's avatarsmathermatherLeave a comment
Database, PostGIS, SQL

Multi-Ring Buffers in PostGIS– Prep for Landuse Analysis

If we want to understand the factors affecting the quality of an ecological resource, adjacency is important. Adjacent land use and the fossil fuel inputs relative to keeping a land use in its current use seem to have strong correlation with, e.g. wetland quality.  I’ll have a deeper post on this with some citations, but this relatively simple process of analysis is called Landscape Development … Continue reading Multi-Ring Buffers in PostGIS– Prep for Landuse Analysis

Unknown's avatarsmathermather2 Comments
Database, Database Optimization, GeoServer, PostGIS, SQL

Contours– Structuring PostGIS data for viewing with GeoServer

Naively structured data is my bane– the desire (and need) to get stuff done so often overtakes the time needed to do things the better way. So, we bootstrap. A long time ago, we managed to load in a few tens of gigs of contour data into PostGIS, partitioned it into 2ft, 10ft, 20ft, 50ft, 100ft and 250ft tables using select queries with a modulus … Continue reading Contours– Structuring PostGIS data for viewing with GeoServer

Unknown's avatarsmathermatherLeave a comment
PostGIS

PostgreSQL and Fuzzy Hashes– a natural combo?

A while back, I had the idea of creating a hash of geometry in order to create a unique constraint on geometry to avoid duplicate geometries in a PostGIS table. It works. It’s slow, but it works. I haven’t used it since. What I really wanted to do though was be able to detect if geometries were similar as well (not just detect identical geometries), … Continue reading PostgreSQL and Fuzzy Hashes– a natural combo?

Unknown's avatarsmathermather2 Comments